Tài liệu Bài tập-thiết kế cơ sở dữ liệu

BÀI TẬP THIẾT KẾ CƠ SỞ DỮ LIỆU Xây dựng mô hình quan hệ cho cơ sở dữ liệu Bài 1 Có các mô tả sau về CSDL “THƯ VIỆN”: a) Thư viện được chia ra thành các nhánh. Thông tin về mỗi nhánh gồm có Mã nhánh, Tên nhánh và Địa chỉ. b) Mỗi cuốn sách trong thư viện có các thông tin về Mã sách, Tên sách Nhà xuất bản và Tác giả… c) Một tác giả có thể viết nhiều cuốn sách. Một cuốn sách có thể có nhiều tác giả viết. d) Một nhà xuất bản xuất bản nhiều cuốn sách. Một cuốn sách do một nhà xuất bản xuất bản. Thông tin về Nhà xuất bản gồm có Tên NXB, Địa chỉ NXB và Số điện thoại NXB. e) Một cuốn sách có thể có nhiều bản sao được lưu trữ tại các nhánh. Thông tin về bản sao sách gồm Mã sách, số các bản sao. f) Thư viện có những người mượn sách. Thông tin về những người mượn sách gồm có Số thẻ, Họ tên, Địa chỉ và Số điện thoại. g) Sách được cho các người mượn mượn tại các nhánh. Thông tin về một lần mượn gồm có Ngày mượn và ngày trả. Hãy xây dựng lược đồ quan hệ cho CSDL “THƯ VIỆN ” từ các mô tả nói trên. Kiểm tra dạng chuẩn của lược đồ quan hệ Bài 1 Cho biết các dạng chuẩn của lược đồ quan hệ sau a) R=ABCDEG; F={A→BC, C→DE, E→G} b) R=ABCDEG; F={C→AB, D→E, B→G} c) R=ABCDEGH; F={A→BC, D→E, H→G} d) R=ABCDEG; F={AB→C, C→B, ABD→E, G→A} Bài 2 Kiểm tra các dạng chuẩn a) R=ABCD; F={CA→D, A→B} b) R=SDIM; F={SI→D, SD→M} e) R=SNDTX; F={S→N, S→D, S→T, S→X} d) R=ABCDEGHI; F={AC→B, BI→ACD, ABC→D, H→I, ACE→BCG, CG→AE} Chuẩn hóa lược đồ quan hệ Bài 1 Chuẩn hóa lược đồ quan hệ sau về dạng 3NF vừa bảo toàn tập phụ thuộc hàm vừa bảo toàn thông tin. a) R=ABC; F={A→B, A→C, B→A, C→A, B→C} b) R=ABCD; F={A→C,C→A, CB→D, AD→B, CD→B, AB→D} c) R=ABCDGH; F={GH→AD, AG→B, CD→GH, C→A, BH→C} Bài 2 Chuẩn hóa lược đồ quan hệ sau về dạng BNF a) R=SDIM; F={SI→D, SD→M} b) R=ABCD; F={A→B, B→C, D→B} Kiểm tra bảo toàn tập phụ thuộc hàm và bảo toàn thông tin Bài 1 Cho lược đồ quan hệ R=ABCDE Tập phụ thuộc hàm F={A → C, B → C, C → D, DE → C, CE → A} Phép tách lược đồ thành các lược đồ con: R1=AD, R2=AB, R3=BE, R4=CDE, R5=AE. Kiểm tra phép tách có mất mát thông tin hay không ? Bài 2 Cho lược đồ quan hệ R=ABCD Tập phụ thuộc hàm F={A→B, B→C, A→D, D→C} Phép tách lược đồ thành các lược đồ con : R1=AB, R2=AC, R3=BD Kiểm tra phép tách có bảo toàn thông tin hay không? Kiểm tra phép tách có bảo toàn tập phụ thuộc hàm với F ? Bài 3 Cho lược đồ quan hệ R=ABCDEH Tập phụ thuộc hàm F={C →ADEH, A →B, DEH →A} Phép tách lược đồ thành các lược đồ con: R1=AB, R2 =CDEH, R3=DEHA Kiểm tra phép tách có mất mát thông tin hay không ? BÀI GIẢI THIẾT KẾ CƠ SỞ DỮ LIỆU Xây dựng mô hình quan hệ cho cơ sở dữ liệu Phần bài giải có trong phần bài tập SQL Kiểm tra các dạng chuẩn của lược đồ quan hệ Bài 1 Cho biết các dạng chuẩn của lược đồ quan hệ sau a) R=ABCDEG; F={A→BC, C→DE, E→G} Khóa của lược đồ A. Các thuộc tính không khóa B, C, D, E,G - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên ở dạng chuẩn 2 vì các thuộc tính không khóa phụ thuộc đầy đủ vào khóa chính - Lược đồ trên không ở dạng chuẩn 3 vì thuộc tính D, E,G không phụ thuộc trực tiếp vào khóa chính A Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 2 b) R=ABCDEG; F={C→AB, D→E, B→G} Khóa của lược đồ CD. Các thuộc tính không khóa A, B, E, G - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên không ở dạng chuẩn 2 vì các thuộc tính không khóa không phụ thuộc đầy đủ vào khóa chính. Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 1 c) R=ABCDEGH; F={A→BC, D→E, H→G} Khóa của lược đồ là ADH Tương tự câu b, dạng chuẩn cao nhất của lược đồ là chuẩn 1 d) R=ABCDEG; F={AB→C, C→B, ABD→E, G→A} Khóa của lược đồ là DGB, DGC. Các thuộc tính không khóa A,E. Chọn DBG là khóa chính - Lược đồ đã ở dạng chuẩn 1 - Lược đồ không ở dạng chuẩn 2 vì các thuộc tính không khóa không phụ thuộc đầy đủ vào khóa chính. Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 1. Bài 2 Kiểm tra các dạng chuẩn a) R=ABCD; F={CA→D, A→B} Khóa của lược đồ AC. Các thuộc tính không khóa B,D - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên không ở dạng chuẩn 2 vì các thuộc tính không khóa không phụ thuộc đầy đủ vào khóa chính Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 1 b) R=SDIM; F={SI→D, SD→M} Khóa của lược đồ SI. Các thuộc tính không khóa D,M - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên ở dạng chuẩn 2 vì các thuộc tính không khóa phụ thuộc đầy đủ vào khóa chính - Lược đồ trên không ở dạng chuẩn 3 vì thuộc tính D, M không phụ thuộc trực tiếp vào khóa chính SI Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 2 c) R=SNDTX; F={S→N, S→D, S→T, S→X} Khóa của lược đồ S. Các thuộc tính không khóa N, D, T, X - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên ở dạng chuẩn 2 vì các thuộc tính không khóa phụ thuộc đầy đủ vào khóa chính - Lược đồ trên ở dạng chuẩn 3 vì thuộc tính không khóa phụ thuộc trực tiếp vào khóa chính S. - Lược đồ trên ở dạng chuẩn Boye-Codd vì các xác định hàm đều là khóa Vậy dạng chuẩn cao nhất của lược đồ là chuẩn Boye-Codd. d) R=ABCDEGHI; F={AC→B, BI→ACD, ABC→D, H→I, ACE→BCG, CG→AE} Các thuộc tính chỉ có mặt ở vế phải của các phụ thuộc hàm: D, I è I là 1 thuộc tính không khóa Các thuộc tính chỉ có mặt ở vế trái của phụ thuộc hàm: H è H luôn thuộc vào 1 khóa tối thiểu của R. Các khóa tối thiểu của lược đồ BEH, BGH, CGH…Chọn BEH làm khóa chính - Lược đồ trên đã ở dạng chuẩn 1 - Lược đồ trên không thuộc dạng chuẩn 2 vì có ít nhất một phụ thuộc hàm (HèI) cho thấy thuộc tính không khóa I phụ thuộc hàm bộ phận vào khóa chính Vậy dạng chuẩn cao nhất của lược đồ là chuẩn 1 Chuẩn hóa lược đồ quan hệ Bài 1 Chuẩn hóa lược đồ quan hệ sau về dạng 3NF vừa bảo toàn tập phụ thuộc hàm vừa bảo toàn thông tin. a) R=ABC; F={A→B, A→C, B→A, C→A, B→C} Bước 1: Tìm phủ tối thiểu của F i.Loại các thuộc tính dư thừa trong các vế trái của phụ thuộc hàm có trong F. Không có phụ thuộc hàm nào trong F có vế trái dư thừa ii.Tách các phụ thuộc hàm có vế phải trên một thuộc tính thành các phụ thuộc hàm có vế phài có một thuộc tính. Vế phải của tất cả các phụ thuộc hàm trong F chỉ có một thuộc tính. iii. Loại khỏi F các phụ thuộc hàm dư thừa AèB dư thừa? F1=F\{AèB} = {A→C, B→A, C→A, B→C} (A)F1+ = AC không chứa B vậy AèB không dư thừa ; F1 = F AèC dư thừa? F2 = F1\{AèC} = {A→B, B→A, C→A, B→C} (A)F2+ = ABC chứa C vậy AèC dư thừa; F2 = F1\{AèC} BèA dư thừa? F3 = F2\{BèA} = {A→B, C→A, B→C} (B)+F3 = BCA chứa A vậy BèA dư thừa F3 = F2\{BèA} CèA dư thừa? F4 = F3\{CèA} = {A→B, B→C} (C)+F3 = C không chứa A vậy CèA không dư thừa F4 = F3 BèC dư thừa? F5 = F4\ {BèC} = {A→B, C→A} (B)+F5 = B không chứa C vậy BèC không dư thừa F5 = F4 Ftt = F5 = {A→B, C→A, B→C} Bước 2: Khóa của R là A Bước 3 : R được tách thành các quan hệ R1(AB), R2(CA), R3(BC) chứa khóa A nên phép tách này bảo toàn thông tin và bảo toàn tập phụ thuộc hàm. b) R=ABCD; F={A→C,C→A, CB→D, AD→B, CD→B, AB→D} Bước 1 : Tìm phủ tối thiểu của F i.Loại các thuộc tính dư thừa trong các vế trái của phụ thuộc hàm có trong F. (A)+ = AC, (B)+= B, (C)+= CA, (D)+= D : Không có phụ thuộc hàm có vế trái dư thừa. ii.Tách các phụ thuộc hàm có vế phải trên một thuộc tính thành các phụ thuộc hàm có vế phài có một thuộc tính. Vế phải của tất cả các phụ thuộc hàm trong F chỉ có một thuộc tính. iii. Loại khỏi F các phụ thuộc hàm dư thừa AèC dư thừa? F1=F\{A→C} = {C→A, CB→D, AD→B, CD→B, AB→D} (A)+F1=A không chứa C, vậy AèC không dư thừa F1=F CèA dư thừa? F2=F1\{CèA} = {AèC, CB→D, AD→B, CD→B, AB→D} (C)+F2=C không chứa A, vậy CèA không dư thừa F2=F1 CBèD dư thừa? F3 = F2\{CBèD} = {A→C, C→A, AD→B, CD→B, AB→D} (CB)+ = CBAD chứa D, vậy CBèD dư thừa F3 = F2\{CBèD} ADèB dư thừa? F4 = F3\{ADèB} = {A→C, C→A, CD→B, AB→D} (AD)+=ADCB chứa B, vậy ADèB dư thừa F4 = F3\{ADèB} CDèB dư thừa? F5 = F4\{CDèB} = {A→C, C→A, AB→D} (CD)+ = CDA không chứa B, vậy CDèB không dư thừa F5 = F4 ABèD dư thừa? F6=F5\{ABèD} = {A→C, C→A, CD→B} (AB)+=ABC không chứa D vậy ABèD không dư thừa F6 = F5 Ftt = F6 = {A→C, C→A, CD→B, AB→D} Bước 2: Khóa của R là AB Bước 3: R được tách thành các quan hệ R1(AC), R2(CDB), R3(ABD) chứa khóa AB nên phép tách này bảo toàn thông tin và bảo toàn tập phụ thuộc hàm. c) R=ABCDGH; F={GH→AD, AG→B, CD→GH, C→A, BH→C} Bước 1: Tìm phủ tối thiểu của F i.Loại bỏ các thuộc tính dư thừa ở vế trái của các phụ thuộc hàm (A)+= A, (B)+= B, (C)+= CA, (D)+= D, (G)+= G, (H)+= H. Vế trái của các phụ thuộc hàm không có thuộc tính dư thừa. ii.Tách các phụ thuộc hàm có vế phải trên một thuộc tính thành các phụ thuộc hàm có vế phải có một thuộc tính. F={GH→A, GH→D, AG→B, CD→H, CD→G, C→A, BH→C} iii.Loại bỏ các phụ thuộc hàm dư thừa trong F GH→A dư thừa ? F1=F\{GH→A} = {GH→D, AG→B, CD→H, CD→G, C→A, BH→C} (GH)F1+ = GHD không chứa A; GH→A không dư thừa F1=F GH→D dư thừa ? F2=F1\{GH→D} = {GH→A, AG→B, CD→H, CD→G, C→A, BH→C} (GH)F2+ = GHABC không chứa D; GH→D không dư thừa F2=F1 AG→B dư thừa? F3=F2\{AG→B} = {GH→A, GH→D , CD→H, CD→G, C→A, BH→C} (AG)F3+ = AG không chứa B; AG→B không dư thừa F3=F2 CD→H dư thừa? F4=F3\{CD→H} = {GH→A, GH→D , AG→B, CD→G, C→A, BH→C} (CD)F4+ = CDGAB không chứa H; CD→H không dư thừa F4=F3 CD→G dư thừa? F5=F4\{CD→G} = {GH→A, GH→D , AG→B, CD→H, C→A, BH→C} (CD)F5+ = CDHA không chứa H; CD→G không dư thừa F5=F4 C→A dư thừa? F6=F5\{C→A} = {GH→A, GH→D , AG→B, CD→H, CD→G, BH→C} (C)F6+ = C không chứa A; C→A không dư thừa F6=F5 BH→C dư thừa? F7=F6\{BH→C} = {GH→A, GH→D , AG→B, CD→H, CD→G, C→A} (BH)F7+ = BH không chứa C; BH→C không dư thừa F7=F6 Ftt = F7 = F Bước 2: Tìm khóa của lược đồ CD Bước 3: R được tách thành các quan hệ R1(GHAD), R2(AGB), R3(CDGH), R4(CA), R5(BHC) chứa khóa CD nên phép tách này bảo toàn thông tin và bảo toàn tập phụ thuộc hàm. Bài 2 Chuẩn hóa lược đồ quan hệ sau về dạng BNF a) R=SDIM; F={SI→D, SD→M} Bước 1: Khóa của lược đồ SI, thuộc tính không khóa D, M Bước 2: Xác định chuẩn cao nhất của lược đồ SI→D; SI→SD: Tăng trưởng (1) SD→M: Giả thiết (2) (1)(2): SI→M: Bắc cầu - Lược đồ đã ở dạng chuẩn 1 - Lược đồ ở dạng chuẩn 2: Các thuộc tính không khóa phụ thuộc đầy đủ vào khóa chính. - Lược đồ không ở dạng chuẩn 3: Thuộc tính không khóa M phụ thuộc bắc cầu vào khóa chính. Chuẩn cao nhất của lược đồ là chuẩn 2. Bước 3: Tách lược đồ về dạng chuẩn 3, chuẩn Boye-Codd i.Tách lược đồ về dạng chuẩn 3. R được tách thành R1(SID) với khóa chính là SI và R2(SDM) với khóa chính là SD. ii. Tách lược đồ về dạng chuẩn Boye-Codd 2 lược đồ con này đồng thời đã ở dạng chuẩn Boye-Codd vì có các xác định hàm đều là khóa. b) R=ABCD; F={A→B, B→C, D→B}. Bước 1: Khóa của lược đồ AD, thuộc tính không khóa B, C Bước 2: Xác định chuẩn cao nhất của lược đồ - Lược đồ đã ở dạng chuẩn 1 - Lược đồ không ở dạng chuẩn 2: Thuộc tính không khóa B phụ thuộc bộ phận vào khóa chính AD. Chuẩn cao nhất của lược đồ là chuẩn 1 Bước 3: Tách lược đồ về dạng chuẩn 2, 3, Boye-Codd i.Tách lược đồ về dạng chuẩn 2 R được tách thành R1(ABC) với khóa chính là A và R2(DB) với khóa chính là D. ii.Tách lược đồ về dạng chuẩn 3 R1 được tách thành R11(AB) với khóa chính là A và R12(BC) với khóa chính là B R2 đã thuộc dạng chuẩn 3. iii. Tách lược đổ về dạng chuẩn Boye-Codd 3 lược đồ con này đồng thời đã ở dạng chuẩn Boye-Codd vì có các xác định hàm đều là khóa. Kiểm tra bảo toàn tập phụ thuộc hàm và bảo toàn thông tin Bài 1 Cho lược đồ quan hệ R=ABCDE Tập phụ thuộc hàm F={A → C, B → C, C → D, DE → C, CE → A} Phép tách lược đồ thành các lược đồ con: R1=AD, R2=AB, R3=BE, R4=CDE, R5=AE. Kiểm tra phép tách có mất mát thông tin hay không ? Bước 1: Xây dựng bảng A B C R1 = AD a1 b12 b13 R2 = AB a1 a2 b23 R3 = BE b31 a2 b33 R4 = CDE b41 b42 a3 R5 = AE a1 b52 b53 Bước 2 : Áp dụng các phụ thuộc hàm cho bảng A→C A B C R1 = AD a1 b12 b13 R2 = AB a1 a2 b13 R3 = BE b31 a2 b33 R4 = CDE b41 b42 A3 R5 = AE a1 b52 b13 D a4 b24 b34 a4 b54 E b15 b25 a5 a5 a5 D a4 b24 b34 a4 b54 E b15 b25 a5 a5 a5 D a4 b24 E b15 b25 B→C R1 = AD R2 = AB A a1 a1 B b12 a2 C b13 b13 R3 = BE R4 = CDE R5 = AE C→D R1 = AD R2 = AB R3 = BE R4 = CDE R5 = AE DE → C R1 = AD R2 = AB R3 = BE R4 = CDE R5 = AE CE → A R1 = AD R2 = AB R3 = BE R4 = CDE R5 = AE b31 b41 a1 a2 b42 b52 b13 a3 b13 b34 a4 b54 a5 a5 a5 A a1 a1 b31 b41 a1 B b12 a2 a2 b42 b52 C b13 b13 b13 a3 b13 D a4 a4 a4 a4 a4 E b15 b25 a5 a5 a5 A a1 a1 b31 b41 a1 B b12 a2 a2 b42 b52 C b13 b13 a3 a3 a3 D a4 a4 a4 a4 a4 E b15 b25 a5 a5 a5 A a1 a1 a1 a1 a1 B b12 a2 a2 b42 b52 C b13 b13 a3 a3 a3 D a4 a4 a4 a4 a4 E b15 b25 a5 a5 a5 Tồn tại trong bảng một dòng chứa toàn ký hiệu a. Phép tách là bảo toàn thông tin. Bài 2 Cho lược đồ quan hệ R=ABCD Tập phụ thuộc hàm F={A→B, B→C, A→D, D→C} Phép tách lược đồ thành các lược đồ con : R1=AB, R2=AC, R3=BD a) Kiểm tra phép tách có bảo toàn thông tin hay không? Bước 1: Xây dựng bảng A B C D R1 = AB a1 a2 b13 b14 R2 = AC a1 b22 a3 b24 R3 = BD b31 a2 b33 a4 Bước 2 : Áp dụng các phụ thuộc hàm cho bảng A→B R1 = AB R2 = AC R3 = BD B→C R1 = AB R2 = AC R3 = BD A→D R1 = AB R2 = AC R3 = BD A a1 a1 b31 B a2 a2 a2 C b13 a3 b33 D b14 b24 a4 A a1 a1 b31 B a2 a2 a2 C a3 a3 a3 D b14 b24 a4 A a1 a1 b31 B a2 a2 a2 C a3 a3 a3 D a4 a4 a4 Tồn tại trong bảng một dòng chứa toàn ký hiệu a. Phép tách là bảo toàn thông tin. b) Kiểm tra phép tách có bảo toàn tập phụ thuộc hàm với F ? Bước 1: Tìm hình chiếu của F lên các lược đồ con Phụ thuộc hàm là hình chiếu của F lên R1 là : A→B Phụ thuộc hàm là hình chiếu của F lên R2 là : A→C (A→D; D→C => A→C) Phụ thuộc hàm là hình chiếu của F lên R3 là những phụ thuộc hàm tầm thường. Bước 2: Suy diễn các phụ thuộc hàm trong F từ tập các hình chiếu Từ các phụ thuộc hàm đã cho không suy diễn được tất cả những phụ thuộc hàm có trong F. Như vậy phép tách này không bảo toàn tập phụ thuộc hàm. Bài 3 Cho lược đồ quan hệ R=ABCDEH Tập phụ thuộc hàm F={C →ADEH, A →B, DEH →A} Phép tách lược đồ thành các lược đồ con: R1=AB, R2 =CDEH, R3=DEHA Kiểm tra phép tách có mất mát thông tin hay không ? Bước 1: Xây dựng bảng A B C D E H R1 = AB a1 a2 b13 b14 b15 b16 R2 = b21 b22 a3 a4 a5 a6 CDEH R3 = DEHA a1 b32 b33 a4 a5 a6 Bước 2 : Áp dụng các phụ thuộc hàm cho bảng A →B A B C D E H R1 = AB a1 a2 b13 b14 b15 b16 R2 = b21 b22 a3 a4 a5 a6 CDEH R3 = DEHA a1 a2 b33 a4 a5 a6 DEH →A A B C D E H R1 = AB a1 a2 b13 b14 b15 b16 R2 = a1 b22 a3 a4 a5 a6 CDEH R3 = DEHA a1 a2 b33 a4 a5 a6 A →B A B C D E H R1 = AB a1 a2 b13 b14 b15 b16 R2 = a1 a2 a3 a4 a5 a6 CDEH R3 = DEHA a1 a2 b33 a4 a5 a6 Tồn tại trong bảng một dòng chứa toàn ký hiệu a. Phép tách là bảo toàn thông tin.