- Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 3 Chương 1 . - III MÔ HÌNH QUAN HỆ (RELATIONAL MODEL) 1 Mô hình quan hệ là gì ? Mô hình Cơ sở dữ liệu Quan hệ (gọi tắt là Mô hình Quan hệ) do E.F Codd đề xuất năm 1971. - Một tập hợp các phép toán thao tác trên dữ liệu như phép toán tập hợp, phép toán quan hệ. - ràng buộc toàn vẹn quan hệ. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 6 Các hệ HQTCSDLQH ngày nay được xây dựng dựa vào lý thuyết của mô hình quan hệ. - Mục đích của môn học này giúp cho sinh viên nắm được kiến trúc tổng quát về mô hình quan hệ và áp dụng nó để lập mô hình dữ liệu quan hệ có hiệu quả trong lưu trữ và khai thác. - Việc lưu trữ dữ liệu như trên không đúng với mô hình quan hệ. - Để lưu trữ đúng với mô hình quan hệ ta phải thay MONHOC bằng MAMH, thay TENKHOA bằng MAKHOA, tách một bảng dữ liệu lớn đó ra thành nhiều bảng con, như mô hình dưới. - Kiểu dữ liệu ở đây là kiểu đơn. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 7 ii Lược Đồ Quan Hệ (Relation Schema) Tập tất cả các thuộc tính cần quản lý của một đối tượng cùng với mối liên hệ giữa chúng được gọi là lược đồ quan hệ. - Lược đồ quan hệ Q với tập thuộc tính {A1,A2,...,An} được viết là Q(A1,A2,...,An). - Chẳng hạn lược đồ quan hệ sinh viên (Đặt tên là Sv) với các thuộc tính như trên là: Sv(MASV, HOSV,TENSV,NU, NGAYSINH, MALOP, HOCBONG, TINH) Thường khi thành lập một lược đồ, người thiết kế luôn gắn cho nó một ý nghĩa nhất định, ý nghĩa đó gọi là tân từ của lược đồ quan hệ đó. - Dựa vào tân từ người ta xác định được tập thuộc tính khóa của lược đồ quan hệ (khái niệm khoá sẽ được trình bày ở phần sau). - Chẳng hạn tân từ của lược đồ quan hệ trên là:”mỗi sinh viên có một mã sinh viên (MASV) duy nhất, mỗi mã sinh viên xác định tất cả các thuộc tính của sinh viên đó như họ tên (HOTEN), nữ (NU) ,ngày sinh (NGAYSINH), lớp theo học (MALOP), học bổng (HOCBONG), tỉnh cư trú (TINH). - Nhiều lược đồ quan hệ cùng nằm trong một hệ thống quản lý được gọi là một lược đồ cơ sở dữ liệu. - Ví dụ lược đồ cơ sở dữ liệu để quản lý điểm sinh viên có thể gồm các lược đồ quan hệ sau: Sv(MASV, HOSV,TENSV, NU, NGAYSINH, MALOP, TINH, HOCBONG) Lop(MALOP,TENLOP,SISO,MAKHOA) Kh(MAKHOA,TENKHOA, SOCBGD) Mh(MAMH, TENMH, SOTIET) Kq(MASV, MAMH, DIEMTHI) Phần giải thích các thuộc tính: MASV Mã sinh viên HOTEN Tên sinh viên NU Nữ NGAYSINH ngày sinh LOP lớp TENLOP tên lớp SISO sỉ số lớp MAKHOA mã khoa HOCBONG học bổng TINH tỉnh TENKHOA tên khoa SOCBGD số cán bộ giảng dạy MAMH mã môn học TENMH tên môn học SOTIET số tiết DIEMTHI điểm thi iii Quan Hệ (Relation) Sự thể hiện của lược đồ quan hệ Q ở một thời điểm nào đó được gọi là quan hệ, rõ ràng là trên một lược đồ quan hệ có thể định nghĩa rất nhiều quan hệ. - Thường ta dùng các ký hiệu như R, S, Q để chỉ các lược đồ quan hệ, còn quan hệ được định nghĩa trên nó tương ứng được ký hiệu là là r, s, q. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 8 iv Bộ (Tuple) Bộ là tập mỗi giá trị liên quan của tất cả các thuộc tính của một lược đồ quan hệ. - Chẳng hạn quan hệ sau có 2 bộ. - Chẳng hạn để nói bộ t thuộc quan hệ r ta viết: t ∈ r. - S được gọi là một siêu khóa (superkey) của lược đồ quan hệ R nếu với hai bộ tùy ý trong quan hệ R thì giá trị của các thuộc tính trong S là khác nhau. - Một lược đồ quan hệ có thể có nhiều siêu khoá. - Siêu khoá chứa ít thuộc tính nhất được gọi là khóa chỉ định, trong trường hợp lược đồ quan hệ có nhiều khóa chỉ định, thì khóa được chọn để cài đặt gọi là khóa chính (Primary key) (trong các phần sau khóa chính được gọi tắt là khóa) Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 10 Các thuộc tính tham gia vào một khóa được gọi là thuộc tính khóa (prime key), ngược lại được gọi là thuộc tính không khóa (non prime key). - Một thuộc tính được gọi là khóa ngoại nếu nó là thuộc tính của một lược đồ quan hệ này nhưng lại là khóa chính của lược đồ quan hệ khác. - Ví dụ: Ta hãy xem lược đồ quan hệ sau: Xe(SODANGBO,QUICACH, INHDANG,MAUSAC,SOSUON,SOMAY,MAXE,QUOCGIA) Siêu khóa: (SOSUON,QUICACH. - Khóa chỉ định: (SODANGBO,QUOCGIA), (SOSUON), (SOMAY), (MAXE) Khóa chính: MAXE Thuộc tính khóa: SODANGBO,QUOCGIA, SOSUON, SOMAY, MAXE Thuộc tính không khóa: QUICACH, HINHDANG, MAUSAC Khóa của Sv là (MASV), Khoá của Mh là (MAMH), khoá của Kh là (MAKHOA), khóa của Kq là (MASV,MAMH) khóa của Lop là MALOP, trong Lop thuộc tính MAKHOA là khóa ngoại 3 Các phép toán tập hợp (set operation) i Phép hợp (Union operation) Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1,A2,..,An}. - r1 và r2 lần lượt là hai quan hệ trên Q1 và Q2. - Phép hợp của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3. - Q3 được xác định như sau: Q3+= {A1,A2,..,An} r3= r1+r2 = {t | t ∈ r1 hoặc t ∈ r2} Ví dụ: r1 r2 r3 = r 1 + r 2 MASV MAMH DIEMTHI MASV MAMH DIEMTHI MASV MAMH DIEMTHI 99001 CSDL 5.0 99002 CTDL 2.0 99001 CSDL 5.0 99002 CTDL 2.0 99001 TTNT 5.0 99002 CTDL 2.0 99003 MANG 8.0 99003 CSDL 6.0 99003 MANG 8.0 99001 TTNT 5.0 99003 CSDL 6.0 ii Phép Giao (Intersection): Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1,A2,..,An}. - Phép giao của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3+={A1,A2,..,An} r3 = r1*r2= {t | t ∈ r1 và t ∈ r2} Ví dụ: r1 r2 r3 = r1* r2 MASV MAMH DIEMTHI MASV MAMH DIEMTHI MASV MAMH DIEMTHI 99001 CSDL 5.0 99002 CTDL 2.0 99002 CTDL 2.0 99002 CTDL 2.0 99001 TTNT 5.0 99003 MANG 8.0 99003 CSDL 6.0 Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 11 iii Phép Trừ (Minus, difference) Cho hai lược đồ quan hệ Q1 và Q2 có cùng tập thuộc tính {A1,A2,..,An}. - Tích Descartes của hai lược đồ quan hệ Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3. - r là quan hệ trên Q. - Phép chiếu của Q lên tập thuộc tính X sẽ tạo thành lược đồ quan hệ Q’= Q[X], trong đó Q’+ chính là X và r’ chính là r nhưng chỉ lấy các thuộc tính của X. - r.{MAMH} MASV MAMH DIEMTHI MAMH 99001 CSDL 5.0 CSDL 99002 CTDL 2.0 CTDL 99003 MANG 8.0 MANG ii Phép Chọn (Selection) Cho lược đồ quan hệ Q(A1,A2,..,An), r là một quan hệ trên Q. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 12 Phép chọn từ r theo điều kiện E sẽ tạo thành một lược đồ quan hệ Q’ như sau: Q. - 5 MASV MAMH DIEMTHI MASV MAMH DIEMTHI 99001 CSDL 5.0 99001 CSDL 5.0 99002 CTDL 2.0 99003 MANG 8.0 99003 MANG 8.0 iii Phép kết, Phép Kết Tự Nhiên (join, natural join): Cho hai lược đồ quan hệ Q1(A1,A2,..,An), Q2(B1,B2,..,Bm). - Phép kết giữa Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3. - Ta được kết quả là quan hệ sau: B≥ F r1 r2 r3 = r1 |>m. - Phép chia Q1 và Q2 sẽ tạo thành một lược đồ quan hệ Q3 như sau: Q3. - (r:E)+(s:E) (r*s):E = (r:E)*(s:E) Chứng minh: (r*s):E = {t|t∈(r*s) và t(E)} Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 14 = {t|t∈{t’|t’∈r và t’∈s} và t(E. - 2 CÂU LỆNH SQL SQL là ngôn ngữ truy vấn dựa trên đại số quan hệ. - Câu lệnh của SQL dùng để rút trích dữ liệu của một một hay nhiều quan hệ. - Kết quả của một câu lệnh SQL (truy vấn) là một quan hệ. - i Truy vấn định nghĩa dữ liệu (data definition query) Tạo lược đồ quan hệ rSV. - Create Table rSV (MASV Text (10) CONSTRAINT khoaChinh PRIMARY KEY,HOTEN Text (30), NU YesNo, NGAYSINH Date, MALOP Text (10), TINH Text (50), HOCBONG Double ) Tạo lược đồ quan hệ rKQ. - Chú ý: Hai cách tạo trên cho ta thấy hai cách tạo khóa: khóa chỉ gồm một thuộc tính và khóa có nhiều thuộc tính Thêm cột DTHOAI có kiểu dữ liệu text 20 ký tự vào lược đồ quan hệ rSV. - Distinct: loại bỏ các bộ trùng trong quan hệ đích Top n. - điều kiện: Biểu thức mà dữ liệu mẫu tin phải thỏa mãn Chú ý: Khi nêu rõ thuộc tính đó thuộc về quan hệ nào ta viết theo cú pháp tênQuanHệ.tênThuộcTính. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 27 From table1 [Inner Join table2 On table1.field1 θ table2.field2. - ALL Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 28 (SELECT DIEMTHI FROM KQ WHERE MAMH='CSDL. - Hãy thực hiện các câu truy vấn sau a) Lập danh sách những sinh viên nam của tỉnh “LONG AN” học khoa “CNTT”, danh sách cần tất cả các thuộc tính của quan hệ Sv. - d) Tổng số tiền học bổng của mỗi khoa e) Những sinh viên nào đăng ký học nhiều hơn 3 môn học, danh sách cần MASV,HOTEN,SOLAN_DANGKY f) Lập danh sách sinh viên có điểm trung bình cao nhất, danh sách cần MASV, HOTEN, NGAYSINH, DIEMTRUNGBINH 2/ Cho lược đồ CSDL dùng để quản lý lao động bao gồm các lược đồ quan hệ sau: Nhanvien(MANV,HOTEN,NGAYSINH,PHAI,DIACHI,MAPB) Tân từ: Mỗi nhân viên có một mã số nhân viên (MANV) duy nhất. - yêu cầu các thông tin: MANV, TENNV, NGAYSINH, ĐIACHI,TENPB, sắp xếp quan hệ kết quả theo thứ tự tuổi giảm dần. - (SOLUONG là thuộc tính tự đặt.) 3/ Cho các quan hệ sau: Monhoc(MSMH ,TENMH,SOTINCHI ,TINHCHAT) MSMH mã số môn học, TENMH tên môn học SOTINCHI số lượng tín chỉ, TÍNH CHẤT bằng 1 nếu đó là môn học bắt buộc, bằng 0 nếu đó là môn học không bắt buộc Sinhvien(MSSV,HOTEN,NGAYSINH,LOP) MSSV mã số sinh viên, HOTEN họ tên sinh viên NGAYSINH ngày sinh, LOP(C,4,0) lớp Diem(MSSV,MSMH,DIEMTHI) DIEMTHI điểm thi Hãy dùng lệnh SQL để thực hiện các câu lệnh sau: a) Hãy cho biết những môn học bắt buộc có SOTINCHI cao nhất. - 4/ Dựa vào lược đồ cơ sở dữ liệu Docgia(MADG,HOTEN,NGAYSINH,DIACHI,NGHENGHIEP) Phieumuon(SOPM,NGAYMUON,MADG) Chitietmuon(SOPM,MADAUSACH,NGAYTRA) Dausach(MADAUSACH,BAN,TAP,MASH) Sach(MASH,TENSACH,TACGIA,NHAXB,NAMXB) Hãy thực hiện các câu hỏi sau đây bằng SQL a) Danh sách các đọc giả đã đăng ký mượn sách trong ngày d. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 30 b) Các quyển sách của phiếu mượn có SOPM là x. - 5/ Dựa vào lược đồ cơ sở dữ liệu Khach(MAKH,HOTEN,DIACHI,DIENTHOAI) Hoadon(SOHD,NGAYLAPHD,NGAYBAN,MAKH) DongHoaDon(SOHD,MAHANG,SLBAN) Hang(MAHANG,TENHANG,DONGIA,DVT,MANHOM) Nhom(MANHOM,TENNHOM) Hãy thực hiện các câu hỏi sau bằng SQL a) Danh sách các khách hàng đã mua hàng trong ngày d. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 32 Chương 3 . - RÀNG BUÔC TOÀN VẸN QUAN HỆ (entegrity constraint) I RÀNG BUỘC TOÀN VẸN - CÁC YẾU TỐ CỦA RÀNG BUỘC TOÀN VẸN 1 Ràng Buộc Toàn Vẹn Trong mỗi CSDL luôn tồn tại nhiều mối liên hệ giữa các thuộc tính, giữa các bộ. - Sự liên hệ này có thể xảy ra trong một lược đồ quan hệ hoặc trong các lược đồ quan hệ của một cơ sở dữ liệu. - Công việc kiểm tra ràng buộc toàn vẹn thường được tiến hành vào thời điểm cập nhật dữ liệu ( thêm, sửa, xóa). - Chẳng hạn, với lược đồ quan hệ SV thì có một ràng buộc toàn vẹn như sau: Với r là một quan hệ của Sv ta có ràng buộc toàn vẹn sau ∀ t1,t2 ∈ r t1.MASV ≠ t2.MASV cuối ∀ ii Bối cảnh Bối cảnh của một ràng buộc toàn vẹn là những quan hệ mà ràng buộc đó có hiệu lực hay nói một cách khác, đó là những quan hệ cần phải được kiểm tra ràng buộc toàn vẹn. - Bối cảnh của một ràng buộc toàn vẹn có thể là một hoặc nhiều quan hệ. - Chẳng hạn với ràng buộc toàn vẹn trên thì bối cảnh là một quan hệ Sv Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 33 iii Tầm ảnh hưởng Trong quá trình phân tích thiết kế một CSDL, người phân tích cần lập bảng tầm ảnh hưởng cho một ràng buộc toàn vẹn nhằm xác định thời điểm cần phải tiến hành kiểm tra các ràng buộc toàn vẹn đó. - Ràng buộc toàn vẹn trên phạm vi là một quan hệ bao gồm :Ràng buộc toàn vẹn miền giá trị, ràng buộc toàn vẹn liên thuộc tính, ràng buộc toàn vẹn liên bộ. - Ràng buộc toàn vẹn trên phạm vi nhiều quan hệ bao gồm :Ràng buộc toàn vẹn phụ thuộc tồn tại, ràng buộc toàn vẹn liên bộ - liên quan hệ, ràng buộc toàn vẹn liên thuộc tính - liên quan hệ. - Lược đồ CSDL C gồm các lược đồ quan hệ như sau: Q1: Khach (MAKH,TENKH,DCKH,DT) Tân từ: Mỗi khách hàng có một mã khách hàng (MAKH) duy nhất, mỗi MAKH xác định một tên khách hàng (TENKH), một địa chỉ (DCKH), một số điện thoại (DT). - 1 Ràng buộc toàn vẹn liên bộ Ràng buộc toàn vẹn liên bộ là sự ràng buộc toàn vẹn giữa các bộ trong cùng một quan hệ . - Đây là loại ràng buộc toàn vẹn rất phổ biến, nó có mặt trong mọi lược đồ quan hệ của CSDL và thường được các hệ quản trị CSDL tự động kiểm tra. - Ví dụ: Với r là một quan hệ của Khach ta có ràng buộc toàn vẹn sau R1. - Cũng giống như ràng buộc toàn vẹn về khóa chính, ràng buộc toàn vẹn về phụ thuộc tồn tại rất phổ biến trong CSDL Ví dụ: Với r, s lần lượt là một quan hệ của Dathang, Khach ta có ràng buộc toàn vẹn sau R2: r[MAKH. - 3 Ràng buộc toàn vẹn về miền giá trị Ràng buộc toàn vẹn có liên quan đến miền giá trị của các thuộc tính trong một quan hệ. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 35 Ví dụ: Với r là một quan hệ của Hoadon ta có ràng buộc toàn vẹn sau R3. - 4 Ràng buộc toàn vẹn liên thuộc tính Ràng buộc toàn vẹn liên thuộc tính là mối liên hệ giữa các thuộc tính trong một lược đồ quan hệ. - Ví dụ: Với r là một quan hệ của Hoadon ta có ràng buộc toàn vẹn sau R4. - t ∈ r t.NGAYLAP < r.Q2 Tức là r được tạo nên từ phép kết nối tự nhiên của các hình chiếu của nó trên các Q1,Q2 ii Tính chất Nếu Q là một lược đồ quan hệ, Q1,Q2 là hai lược đồ quan hệ con có Q1. - SAIP = Q+ S → SA = Q1+ Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 64 S Theo tính chất trên, với mọi quan hệ r của Q ta luôn có r = r.Q1. - CE→G} Vậy lược đồ quan hệ Q được tách thành lược đồ cơ sở dữ liệu đạt chuẩn BC sau: Q1(C,A,D) F1={C→AD} đạt chuẩn BC Q2(E,B,H) F2={E→BH} đạt chuẩn BC Q3(E,K) F3={E→K} đạt chuẩn BC Q4(C,E,G) F4={CE→G} đạt chuẩn BC ----oOo. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 85 Đề 2 Cho một lược đồ cơ sở dữ liệu C dùng để quản lý hoạt động sửa chữa, bảo trì xe của một gara xe hơi. - Lược đồ cơ sở dữ liệu C gồm các lược đồ quan hệ như sau: Q1: Tho(MATHO,TENTHO,NHOM,NHOM_TRUONG) Tân từ: Mỗi người thợ đều có mã số là MATHO để nhận diện. - Xác định khóa cho từng lược đồ quan hệ. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 86 2/ Mô tả tất cả các ràng buộc toàn vẹn của lược đồ cơ sở dữ liệu C. - 4/ Lược đồ cơ sở dữ liệu C ở dạng chuẩn mấy (cao nhất). - Hãy dùng thuật toán phân rã để nâng cấp lược đồ cơ sở dữ liệu trên. - Vậy lược đồ cơ sở dữ liệu C được phân rã thành các lược đồ Q1,Q2,Q31,Q32,Q4,Q51 ----oOo. - Lược đồ cơ sở dữ liệu C gồm các lược đồ quan hệ như sau : Q1: Nguyen_te(LOAINT,NGAY,TIGIA,TILE_HH) Tân từ : Mỗi nguyên tệ được nhận diện duy nhất qua LOAINT. - Lưu ý : Quan hệ này chỉ chứa các nguyên tệ mà công ty có chi trả kiều hối. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 91 Câu hỏi : 1. - Xác định tập F gồm tất cả các phụ thuộc hàm suy ra từ tân từ của các lược đồ quan hệ. - Mô tả tất cả các ràng buộc toàn vẹn của lược đồ cơ sở dữ liệu. - Lược đồ cơ sở dữ liệu C ở dạng chuẩn mấy (cao nhất. - Hãy dùng thuật toán phân rã để nâng cấp cơ sở dữ liệu trên. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 92 Đề 4 Cho một lược đồ cơ sở dữ liệu C dùng để quản lý việc thuê mướn phòng tại một khách sạn. - Lược đồ cơ sở dữ liệu C gồm các lược đồ quan hệ nhu sau : Q1: Phong(MAPH,SO_NGUOI,DACDIEM,GIA_PHONG) Tân từ: Các phòng của khách sạn được phân biệt với nhau qua MAPH. - Xác định các khóa cho từng lược đồ quan hệ. - Mô tả tất cả các ràng buộc toàn vẹn của lược đồ cơ sở dữ liệu C. - Hãy dùng thuật toán phân rã để nâng cấp lược đồ cơ sở dữ liệu C. - Bộ mơn CSDL Trường CĐCN 4 Giáo trình CƠ SỞ DỮ LIỆU Trang 94 Đề 5 Cho một lược đồ cơ sở dữ liệu C dùng để quản lý việc việc cho mượn sách tại một thư viện (xem tại chỗ hoặc mang về nhà). - Lược đồ cơ sở dữ liệu C gồm các lược đồ quan hệ như sau : Q1 : The_loai(MATL,TENTL) Tân từ : Sách của thư viện được phân chia theo thể loại để bạn đọc dễ dàng tra cứu. - ULLMAN [5].Lý thuyết thiết kế cơ sở dữ liệu JEFFREY D