Bài giảng Cơ sở dữ liệu - Chương 3: Mô hình quan hệ - Nguyễn Như Hoa

Chương 3 Mô hình quan hệ (The relational model) Giáo trình & Tài liệu tham khảo: 1. Ramez Elmasri, Shamkant B. Navathe, 2011. Fundamentals of Database systems, 6th edition, Addison-Wesley. 2. Giáo trình Cơ sở dữ liệu , Trần Đắc Phiến, ĐH Công nghiệp TPHCM 3. Bộ slide bài giảng của Nguyễn Minh Thư, Khoa CNTT, ĐH KHTN TPHCM 4. Bộ slide bài giảng của Trần Thị Kim Chi, Khoa CNTT, ĐH Công nghiệp TPHCM Gv. Nguyễn Như Hoa 1 Nội dung • Mô hình quan hệ (Relational model) – Giới thiệu – Các Khái niệm • Thiết kế CSDL quan hệ bằng cách chuyển đổi từ mô hình ER sang mô hình quan hệ 2 Các giai đoạn thiết kế CSDL (nhắc lại) Phụ thuộc HQT cụ thể Độc lập HQT Thế giới thực Phân tích yêu cầu Thiết kế mức quan niệm Thiết kế mức logic Thiết kế mức vật lý Các yêu cầu về dữ liệu Lược đồ quan niệm ( ERD ) Lược đồ logic ( RDB shema ) Lược đồ trong ( Tables ) Giai đoạn 1 Giai đoạn 2 (*) Giai đoạn 3 (*) Giai đoạn 4 3 Giới thiệu Mô hình Quan hệ (Relational Model) • Do tiến sĩ E. F. Codd đưa ra – “A Relation Model for Large Shared Data Banks”, Communications of ACM, 6/1970 • Cung cấp một cấu trúc dữ liệu đơn giản và đồng bộ – Khái niệm quan hệ • Có nền tảng lý thuyết vững chắc – Lý thuyết tập hợp • Là cơ sở của các Hệ quản trị CSDL thương mại – Oracle, DB2, SQL Server 4 Mô hình Quan hệ Các khái niệm • Mô hình quan hệ đưa ra các khái niệm để mô tả dữ liệu và các ràng buộc trên dữ liệu • quan hệ (Relation) • thuộc tính (Attribute) • miền giá trị (domain) • bộ (tuple) • Khóa chính và khóa ngoại (Primary key, Foreign key) • Các toàn vẹn quan hệ (contraints) 5 Mô hình Quan hệ Các khái niệm • Quan hệ (relation) : là một table với các cột và dòng • Thuộc tính (attribute) : là tên của cột • Miền giá trị của một cột (domain) : là tập các giá trị nguyên tố mà dữ liệu của cột phải thuộc về • Bộ (tuple) : là một dòng chứa dữ liệu của một quan hệ 6 Lược đồ và thể hiện của một quan hệ Relation Schema & Relation Instance • Lược đồ quan hệ là một biểu diễn bao gồm – Tên của quan hệ – Tập thuộc tính của quan hệ • Thể hiện của quan hệ là tập các bộ của quan hệ ở một thời điểm STUDENT(Name, Ssn, Home_phone, Address, Office_phone, Age) 7 Lược đồ cơ sở dữ liệu quan hệ Relational Database Schema • Mô tả cấu trúc của một CSDL • Lược đồ CSDL gồm nhiều lược đồ quan hệ Lược đồ CSDL NHANVIEN(MANV, TENNV, HONV, NGSINH, DCHI, PHAI, LUONG, PHG) PHONGBAN(MAPHG, TENPHG, TRPHG, NG_NHANCHUC) DIADIEM_PHG(MAPHG, DIADIEM) THANNHAN(MA_NVIEN, TENTN, PHAI, NGSINH, QUANHE) DEAN(TENDA, MADA, DDIEM_DA, PHONG) 1 Lược đồ quan hệ 8 Các đặc tính của một quan hệ • Một quan hệ phải có một tên phân biệt với tất cả các quan hệ khác trong lược đồ CSDL quan hệ • Mỗi thuộc tính có một tên phân biệt • Các giá trị của một thuộc tính phải thuộc domain • Mỗi ô trong quan hệ chứa 1 giá trị nguyên tố • Mỗi bộ là phân biệt. Không tồn tại 2 bộ giống nhau • Thứ tự các thuộc tính không quan trọng • Thứ tự các bộ không quan trọng 9 Định nghĩa hình thức • Lược đồ quan hệ – Gọi A1, A2, , An là các thuộc tính – Có các miền giá trị D1, D2, , Dn tương ứng – Ký hiệu R(A1:D1, A2:D2, , An:Dn) là một lược đồ quan hệ – Bậc của lược đồ quan hệ là số lượng thuộc tính trong lược đồ 10 Định nghĩa hình thức • Quan hệ (hay thể hiện quan hệ) – Một quan hệ r của lược đồ quan hệ R(A1, A2, , An), ký hiệu r(R), là một tập các bộ r = {t1, t2, , tk} – Trong đó mỗi ti là 1 danh sách có thứ tự của n giá trị ti= • Mỗi vj là một phần tử của miền giá trị DOM(Aj) hoặc giá trị rỗng 11 Định nghĩa hình thức Tóm tắt ký hiệu • Lược đồ quan hệ R bậc n R(A1, A2, , An) hoặc R(A1:D1, A2:D2, , An:Dn) • Tập thuộc tính của R R+ • Quan hệ r, s, p, q • Bộ t, u, v • Miền giá trị của thuộc tính A DOM(A) hay MGT(A) • Giá trị tại thuộc tính A của bộ thứ t t.A hay t[A] 12 Định nghĩa hình thức Ví dụ NHANVIEN( TENNV:string, HONV:string, NGSINH:date, DCHI:string, PHAI:string, LUONG:integer, PHG:integer ) R ( A1 : D1 , A2 : D2 , . , An : Dn ) 13 Khóa của lược đồ quan hệ • Siêu khóa – Một/một tập thuộc tính cho phép nhận diện tính duy nhất của một bộ trong một lược đồ quan hệ – Một lược đồ quan hệ có nhiều siêu khóa • Khóa dự tuyển – Là một tập con (nhỏ nhất) của siêu khóa , cho phép nhận diện tính duy nhất của một bộ trong một lược đồ quan hệ – Một lược đồ quan hệ có thể có nhiều khóa dự tuyển • Khóa chính (primary key) – Khóa chính là một khóa dự tuyển được chọn để nhận diện tính duy nhất của một bộ trong lược đồ quan hệ 14 Khóa của lược đồ quan hệ STUDENT(StudentID, Name, Ssn, Home_phone, Address, Office_phone, Age) • {StudentID, Name} • {StudentID, Name, Address} • {Ssn, Home_phone} • {Ssn, Name, Home_phone} • • • Siêu khóa Khóa dự tuyển Khóa chính { Ssn } { StudentID } 15 Khóa của lược đồ quan hệ • Khóa ngoại (foreign key) – Một/một tập thuộc tính trong một lược đồ quan hệ có liên kết đến khóa chính của một lược đồ quan hệ khác • Thể hiện mối liên hệ giữa các bộ của hai lược đồ quan hệ • Một lược đồ quan hệ có thể có nhiều khóa ngoại – Khóa ngoại là thể hiện (biểu diễn) của mối quan hệ (relationship) giữa các tập thực thể 16 Khóa của lược đồ quan hệ MANV TENNV NGSINH PHGBAN 0111 Nguyen An 12/08/1955 5 0112 Bui Liem 07/19/1968 4 0201 Le Vân 06/20/1951 4 0202 Tran Mai 09/15/1962 5 MAPB TENPB Nghien cuu 5 Dieu hanh 4 Quan ly 1 s(PHONGBAN) r(NHANVIEN) NHANVIEN ( MANV, TENNV, NGSINH , PHGBAN) PHONGBAN ( MAPB , TENPB ) Khóa chính Khóa ngoại Khóa chính 17 Ràng buộc toàn vẹn • RBTV (Integrity Constraint) – Là những qui tắc, điều kiện, ràng buộc cần được thỏa mãn cho mọi thể hiện của CSDL quan hệ – nhằm đảm bảo tính đúng đắn của dữ liệu • RBTV được mô tả khi định nghĩa lược đồ quan hệ • RBTV được kiểm tra khi quan hệ hoặc các quan hệ có thay đổi 18 Các loại ràng buộc toàn vẹn • RBTV miền giá trị (Domain Integrity Constraint) • RBTV thực thể (Entity Integrity Constraint) • RBTV tham chiếu (Referential Integrity Constraint) • RBTV do người dùng định nghĩa (User-defined Integrity Constraint) 19 Các loại ràng buộc toàn vẹn RBTV miền giá trị • Giá trị của mỗi thuộc tính A là một giá trị nguyên tử thuộc miền giá trị Dom(A) • Với mọi bộ của quan hệ NHANVIEN – PHAI có kiểu chuỗi , có giá trị “nam” hoặc “nữ” – NGSINH có kiểu datetime – TENNV có kiểu chuỗi (tối đa 50 ký tự) 20 Các loại ràng buộc toàn vẹn RBTV thực thể • Toàn vẹn thực thể đòi hỏi khóa chính phải có giá trị xác định , không được phép null StudentID 100 10001 100 10002 100 10003 100 10004 100 10005 Primary key constraint Unique constraint STUDENT 21 Các loại ràng buộc toàn vẹn RBTV tham chiếu • Toàn vẹn tham chiếu đòi hỏi một khóa ngoại phải có giá trị khớp với giá trị của khóa chính ở quan hệ cha , hoặc null 22 Các loại ràng buộc toàn vẹn RBTV do user định nghĩa • Các ràng buộc cần thiết, xuất phát từ các qui tắc nghiệp vụ của tổ chức • Ví dụ – Ngày nhập học của học viên phải nhỏ hơn ngày kết thúc khóa học – Số lượng nhân viên của một phòng ban không quá 20 người 23 Chuyển đổi Từ mô hình ER sang mô hình quan hệ • Mục tiêu là tạo ra bản thiết kế CSDL quan hệ với đầu vào là mô hình ER • Ánh xạ khái niệm của 2 mô hình Mô hình quan hệ Mô hình ER Tập thưc thể Thực thể Thuộc tính của tập thực thể Khóa chính của tập thực thể Mối quan hệ giữa các tập thực thể •Quan hệ •Bộ •Thuộc tính của quan hệ •Khóa chính của quan hệ •Khóa ngoại của quan hệ 24 Các qui tắc chuyển đổi (1) Tập thực thể – Các tập thực thể (trừ tập thực thể yếu) chuyển thành các quan hệ có cùng tên và tập thuộc tính (2) Mối quan hệ (2a) Nhiều-Nhiều Tạo một quan hệ mới có – Tên quan hệ là : tên của mối quan hệ – Thuộc tính là : thuộc tính của mối quan hệ (nếu có) và những thuộc tính khóa của các tập thực thể liên quan (2b) Một-Nhiều Thêm vào quan-hệ-nhiều thuộc tính khóa của quan-hệ-một (2c) Một-Một Hoặc thêm vào quan hệ này thuộc tính khóa của quan hệ kia Hoặc thêm thuộc tính khóa vào cả 2 quan hệ (3) Thực thể yếu Chuyển thành một quan hệ – Có cùng tên với thực thể yếu – Thêm vào thuộc tính khóa của quan hệ cha – Thuộc tính khóa của quan hệ cha tham gia vào khóa chính của quan hệ 25 Ví dụ 1 Nhanvien(Manv, Tennv, Phai, Ngaysinh) Phongban(MaPB, TenPB) 26 Ví dụ 1 Nhanvien(Manv, Tennv, Phai, Ngaysinh, MaPB) Phongban(MaPB, TenPB) 27 Ví dụ 1 • Kết quả Nhanvien(Manv, Tennv, Phai, Ngaysinh, MaPB) Phongban(MaPB, TenPB) Mô hình ER Lược đồ CSDL quan hệ 28 Ví dụ 2 HOCVIEN(MaHV, TenHV, Điachi, Ngaysinh, SoĐT) MONHOC (MaMH, TenMH, Thoiluong) 29 Ví dụ 2 HOCVIEN_MONHOC(MaHV, MAMH, Ngaynhaphoc) HOCVIEN(MaHV, TenHV, Điachi, Ngaysinh, SoĐT) MONHOC (MaMH, TenMH, Thoiluong) 30 Ví dụ 2 • Kết quả HOCVIEN_MONHOC(MaHV, MAMH, Ngaynhaphoc) HOCVIEN(MaHV, TenHV, Điachi, Ngaysinh, SoĐT) MONHOC (MaMH, TenMH, Thoiluong) Mô hình ER Lược đồ CSDL quan hệ 31 Ví dụ 3 Phongban(MaPB, TenPB) Nhanvien(Manv, Hoten, Phai, Ngaysinh, MaPB) 32 Ví dụ 3 Phongban(MaPB, TenPB, MaTruongphong) Nhanvien(Manv, Hoten, Phai, Ngaysinh, MaPB) 33 Ví dụ 3 • Kết quả Phongban(MaPB, TenPB, MaTruongphong) Nhanvien(Manv, Hoten, Phai, Ngaysinh, MaPB) Mô hình ER Lược đồ CSDL quan hệ 34 Ví dụ 4 Tập Thực thể cha Tập Thực thể con (tập thực thể yếu) Thannhan(Hoten, Quanhe, Ngaysinh) Nhanvien(Manv, Hoten, Phai, Ngaysinh) 35 Ví dụ 4 Tập Thực thể cha Tập Thực thể con (tập thực thể yếu) Thannhan(Hoten, Manv , Quanhe, Ngaysinh) Nhanvien(Manv, Hoten, Phai, Ngaysinh) 36 Ví dụ 4 • Kết quả Tập Thực thể cha Tập Thực thể con (tập thực thể yếu) Thannhan(Hoten, Manv , Quanhe, Ngaysinh) Nhanvien(Manv, Hoten, Phai, Ngaysinh) Mô hình ER Lược đồ CSDL quan hệ 37 Ví dụ 5 • Cho mô hình ER 38 Ví dụ 5 • Lược đồ CSDL quan hệ tương ứng NHOMHANG( MaNhom, TenNhom ) MATHANG ( MaHang, TenHang, ĐVT, Đongia , MaNhom ) KHACHHANG (MaKH, Hoten, Điachi, SoĐt ) HOADON ( SoHD, NgayLapHD, NgayGiao , MaKH ) CHITIETHD ( SoHD, MaHang , SoLuong ) Cho biết các relationships trong mô hình ER của ví dụ 5 được thể hiện ở chỗ nào trong Lược đồ CSDL quan hệ ? 39 Ví dụ 6 • Cho mô hình ER 40 Ví dụ 6 • Lược đồ CSDL quan hệ tương ứng TACGIA ( MaTG, Ten, Email, Phone, Điachi ) SACH ( Masach, Tieude , LanXB, NamXB, Gia, ISBN, Sotrang, Theloai, Mota ) NGUOIMUON ( MaĐG, Ten, Email, Phone, Điachi ) Chitietmuon (MaĐG, Masach , Ngaymuon, Ngaytra ) Tacgia_muon_Sach (MaTG, Masach, Ngaymuon, Ngaytra ) Tacgia_viet_Sach (MaTG, Masach) 41 Tóm tắt 42 Các thuật ngữ • The relational data model • Relation • Tuble • Attribute • Relation Schema • DB schema • Relation Instance • Domain (DOM) • Relational Integrity • Super key, Primary key, Foreign key • Referential Integrity Constraint 43 Câu hỏi  Mô hình quan hệ dùng những khái niệm gì để mô tả dữ liệu ? Cho ví dụ ?  Phân biệt lược đồ quan hệ và thể hiện của quan hệ ? Cho ví dụ ?  Khóa chính của một quan hệ có đặc tính gì ?  Một quan hệ luôn chỉ có một khóa chính . Đúng hay sai ?  Phân biệt : siêu khóa, khóa dự tuyển, khóa chính của một quan hệ ?  Khóa ngoại của một quan hệ có vai trò gì ?  Các qui tắc chuyển đổi từ mô hình ER sang mô hình quan hệ ? 44 Bài tập 1 • Hãy chỉ ra: Lược đồ quan hệ ? Thể hiện quan hệ ? Thuộc tính ? Bộ ? Khóa chính ? 45 Bài tập 2 • Ssn là khóa chính của lược đồ quan hệ EMPLOYEE. Hãy chỉ ra một vài siêu khóa của lược đồ quan hệ này ? Ssn : Social Security Number 46 Bài tập 3 • Cho lược đồ CSDL 47 Bài tập 3 (tt) • Một thể hiện của CSDL 48 Bài tập 3 (tt) 49 Bài tập 3 (tt) • Câu hỏi : – Cho biết các khóa ngoại có trong các quan hệ ? – Chỉ ra các relationship giữa các quan hệ ? 50 Bài tập 4 • Chuyển đổi mô hình ER của bài tập 2, 3 , 4 (chương 2) sang mô hình quan hệ ? 51 Bài tập 5 - Quản lý thi tốt nghiệp PTCS giáo trình CSDL của Trần Đắc Phiến (1) Phòng giáo dục của một huyện muốn lập một hệ thống thông tin để quản lý việc tổ chức thi tốt nghiệp PTCS. Việc tổ chức thi như sau : Lãnh đạo phòng giáo dục thành lập nhiều hội đồng thi (mỗi hội đồng thi gồm một trường hoặc một số trường gần nhau). Mỗi hội đồng thi có một mã số duy nhất (MAHĐT). Mỗi mã số hội đồng thi xác định tên hội đồng thi(TENHĐT), họ tên chủ tịch hội đồng(TENCT), địa chỉ (ĐCHĐT),điện thoại(ĐTHĐT). Mỗi hội đồng thi được bố trí cho một số phòng thi, mỗi phòng thi có một số hiệu phòng(SOPT) duy nhất, một phòng thi xác định địa chỉ phòng thi (ĐCPT). Số hiệu phòng thi được đánh số khác nhau ở tất cả các hội đồng thi. 52 Bài tập 5 - Quản lý thi tốt nghiệp PTCS giáo trình CSDL của Trần Đắc Phiến (2) Giáo viên của các trường trực thuộc phòng được điều động đến các hội đồng để coi thi, mỗi trường có thể có hoặc không có thí sinh dự thi, mỗi trường có một mã trường duy nhất (MATR), mỗi mã trường xác định một tên trường(TENTR), địa chỉ (ĐCTR), loại hình đào tạo (LHĐT) (Công lập, chuyên, bán công, dân lập, nội trú,). Giáo viên của một trường có thể làm việc tại nhiều hội đồng thi. Một giáo viên có một mã giáo viên(MAGV), một mã giáo viên xác định tên giáo viên (TENGV), chuyên môn giảng dạy (CHUYENMON), chức danh trong hội đồng thi(CHUCDANH). 53 Bài tập 5 - Quản lý thi tốt nghiệp PTCS giáo trình CSDL của Trần Đắc Phiến (3) Các thí sinh dự thi có một số báo danh duy nhất(SOBD), mỗi số báo danh xác định tên thí sinh(TENTS), ngày sinh (NGSINH), giới tính (PHAI). Mỗi thí sinh được xếp thi tại một phòng thi nhất định cho tất cả các môn. Mỗi thí sinh có thể có chứng chỉ nghề (CCNGHE) hoặc không (thuộc tính CCNGHE kiểu chuỗi, CCNGHE=”x” nếu thí sinh có chứng chỉ nghề và CCNGHE bằng rỗng nếu thí sinh không có chứng chỉ nghề). Thí sinh của cùng một trường chỉ dự thi tại một hội đồng thi. Mỗi môn thi có một mã môn thi duy nhất(MAMT), mỗi mã môn thi xác định tên môn thi(TENMT). Giả sử toàn bộ các thí sinh đều thi chung một số môn do sở giáo dục quy định. Mỗi môn thi được tổ chức trong một buổi của một ngày nào đó. Ứng với mỗi môn thi, một thí sinh có một điểm thi duy nhất(ĐIEMTHI) 54 Bài tập 5 - Quản lý thi tốt nghiệp PTCS giáo trình CSDL của Trần Đắc Phiến Câu hỏi : 1. Thiết lập mô hình ER cho bài toán quản lý thi TN PTCS ? 2. Thiết lập mô hình CSDL quan hệ cho bài toán ? 3. Xác định khóa chính cho mỗi lược đồ quan hệ ? 4. Xác định các ràng buộc toàn vẹn trong lược đồ CSDL ? 55