Xây dựng mô hình cho cơ sở dữ liệu | Đại học Bách Khoa Hà Nội

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
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 D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b23 b24 b25 R3 = BE b31 a2 b33 b34 a5 R4 = CDE b41 b42 a3 a4 a5 R5 = AE a1 b52 b53 b54 a5
Bước 2 : Áp dụng các phụ thuộc hàm cho bảng A → C A B C D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b13 b24 b25 R3 = BE b31 a2 b33 b34 a5 R4 = CDE b41 b42 A3 a4 a5 R5 = AE a1 b52 b13 b54 a5 B → C A B C D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b13 b24 b25 R3 = BE b31 a2 b13 b34 a5 R4 = CDE b41 b42 a3 a4 a5 R5 = AE a1 b52 b13 b54 a5 C → D A B C D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b13 a4 b25 R3 = BE b31 a2 b13 a4 a5 R4 = CDE b41 b42 a3 a4 a5 R5 = AE a1 b52 b13 a4 a5 DE → C A B C D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b13 a4 b25 R3 = BE b31 a2 a3 a4 a5 R4 = CDE b41 b42 a3 a4 a5 R5 = AE a1 b52 a3 a4 a5 CE → A A B C D E R1 = AD a1 b12 b13 a4 b15 R2 = AB a1 a2 b13 a4 b25 R3 = BE a1 a2 a3 a4 a5 R4 = CDE a1 b42 a3 a4 a5 R5 = AE a1 b52 a3 a4 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 A B C D R1 = AB a1 a2 b13 b14 R2 = AC a1 a2 a3 b24 R3 = BD b31 a2 b33 a4 B→C A B C D R1 = AB a1 a2 a3 b14 R2 = AC a1 a2 a3 b24 R3 = BD b31 a2 a3 a4 A→D A B C D R1 = AB a1 a2 a3 a4 R2 = AC a1 a2 a3 a4 R3 = BD b31 a2 a3 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.