Cơ sở dữ liệu quan hệ - Chương III: Mô hình quan hệ - Nguyễn Thị Tâm

CHƯƠNG III MÔ HÌNH QUAN HỆ 1 08/10/2012 2 08/10/2012 Nội dung chi tiết Giới thiệu Các khái niệm của mô hình quan hệ Quan hệ (Relation) Thuộc tính (Attribute) Lược đồ (Schema) Bộ (Tuple) Miền giá trị (Domain) Ràng buộc toàn vẹn Các đặc trưng của quan hệ Các phép toán đại số quan hệ Chuyển lược đồ E/R sang thiết kế quan hệ Thiết kế ER Ý tưởng HQT CSDL quan hệ CSDL Lược đồ Quan hệ Chuẩn hóa 3 08/10/2012 I. Giới thiệu mô hình quan hệ? Do tiến sĩ E. F. Codd đưa ra năm 1970 Mô hình dữ liệu quan hệ cung cấp một cấu trúc dữ liệu đơn giản, đồng bộ có nền tảng lý thuyết vững chắc là Lý thuyết tập hợp Là cơ sở của các HQT CSDL thương mại hiện nay: Oracle, MySQL, SQL Server Mô hình dữ liệu quan hệ cùng với mô hình thực thể liên kết được sử dụng rộng rãi trong việc phân tích và thiết kế CSDL hiện nay. 4 08/10/2012 II. Các khái niệm của mô hình quan hệ 5 08/10/2012 1. Quan hệ  Các thông tin lưu trữ trong CSDL được tổ chức thành bảng (table) 2 chiều và gọi là quan hệ  Mỗi bảng 2 chiều là một quan hệ  Ví dụ: TENNV HONV NS DIACHI GT LUONG PHG Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 Ba Ria VT Nam 38000 5 6 08/10/2012  Mỗi một Quan hệ gồm:  Tên quan hệ  phân biệt với các quan hệ khác  Tập hợp các cột  các thuộc tính của quan hệ  Cố định  Được đặt tên  Có kiểu dữ liệu xác định, các giá trị trong một cột phải cùng kiểu  Mô tả ý nghĩa của cột đó  Tập hợp các dòng  các bản ghi của quan hệ  Thay đổi theo thời gian 7 08/10/2012 Quan hệ ~ Tập đối tượng thực thể Một dòng ~ Một đối tượng thực thể cụ thể Mỗi cột ~ Một thuộc tính, một đặc tính của đối tượng 8 08/10/2012 2. Lược đồ quan hệ - Lược đồ CSDL  Lược đồ quan hệ: nhằm mục đích mô tả cấu trúc của một quan hệ và các mối liên hệ giữa các thuộc tính trong quan hệ đó  Lược đồ quan hệ gồm:  Tên của quan hệ và tên của các thuộc tính  Các mô tả để xác định ý nghĩa và mối quan hệ giữa thuộc tính (nếu có)  Ký hiệu lược đồ quan hệ  Cho một bộ gồm (U,F), trong đó:  U là tập các thuộc tính  F là tập các ràng buộc dữ liệu trên U thì R(U) gọi là lược đồ quan hệ.  Bậc của lược đồ quan hệ là số lượng thuộc tính trong lược đồ  NHANVIEN là một lược đồ bậc 6 mô tả đối tượng nhân viên 9 08/10/2012 Lược đồ quan hệ Phong Ban PHONGBAN(MaPHG,TenPHG,TrPHG,NG_NhanChuc) Mô tả: Mỗi phòng ban được cấp một mã số duy nhất để phân biệt với các phòng ban khác trong công ty và có một tên phòng ban phân biệt, một trưởng phòng cùng với ngày nhận chức của trưởng phòng Lược đồ quan hệ NHANVIEN NHANVIEN( MaNV, HoTen, Phai, Luong, PHG) Mô tả: mỗi nhân viên được cấp một mã số duy nhât để phân biệt với các nhân viên khác trong công ty và có một họ tên nhân viên, một giới tinh, một mức lương và thuộc về một phòng ban nhất định 10 08/10/2012 Lược đồ quan hệ PHANCONG PHANCONG( MaNV, MaDA, Thoigian) Mô tả: mỗi nhân viên làm việc ở một phòng ban nhưng có thể tham gia nhiều đề án với thời gian tham gia đề án trong tuần của nhân viên ứng với đề án mà nhân viên đó tham gia 11 08/10/2012 12 08/10/2012 Lược đồ CSDL  Gồm nhiều lược đồ quan hệ cùng nằm trong một hệ thống quản lý  tập hợp các bảng được quản lý trong một file CSDL  Ví dụ: Lược đồ CSDL NHANVIEN( MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG) PHONGBAN( MAPHG, TENPHG, TRPHG, NG_NHANCHUC) DIADIEM_PHG( MAPHG, DIADIEM) THANNHAN( MA_NVIEN, TENTN, GT, NS, QUANHE) DEAN( TENDA, MADA, DDIEM_DA, PHONG) 13 08/10/2012 3.Bộ  Là các dòng của quan hệ (trừ dòng tiêu đề - tên của các thuộc tính)  Bộ là thông tin của một thực thể cụ thể 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ệ  Thể hiện dữ liệu cụ thể của các thuộc tính trong quan hệ bộ TENNV HONV NS DIACHI GT LUONG PHG Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 Ba Ria VT Nam 38000 5 14 08/10/2012 4. Miền giá trị  Miền giá trị của thuộc tính A, Ký hiệu: Dom(A), là tập tất cả các giá trị mà thuộc tính A có thể nhận được  Miền giá trị có các kiểu:  Liệt kê  Khoảng con  Ví dụ  Dom(Gioitinh) = {nam, nữ}  Dom(Mauda) = {da trắng, da vàng, da đen, da đỏ}  Dom(Diemthi) = {0 -> 10}  Dom(Luong) = { float }  15 08/10/2012 Quan hệ Quan hệ r xác định trên tập thuộc tính U = {A1, ., An}, ký hiệu r(U), 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 TENNV HONV NGSINH DCHI PHAI LUONG PHG Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 null Nam 38000 5 t1 t2 t3 t4 vj 16 08/10/2012 Chú ý: Khi thành lập một lược đồ thường gắn lược đồ đó một ý nghĩa nhất định  ý nghĩa được gọi là tân từ Ví dụ: “Mỗi sinh viên có một mã sinh viên 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, giới tính, ngày sinh, lớp theo học, học bổng” 17 08/10/2012 5. Liên kết: Liên kết là thể hiện mối quan hệ giữa các bảng (thực thể) Các loại liên kết giữa các bảng: Liên kết một – một Liên kết một – nhiều Liên kết nhiều – nhiều Liên kết đệ quy 18 08/10/2012 V. Ràng buộc toàn vẹn  Ràng buộc là những quy tắc bắt buộc dữ liệu phải tuân theo nhằm đảm bảo tính tin cậy và độ chính xác của dữ liệu.  Ràng buộc toàn vẹn là các điều kiện bất biến không được vi phạm trong một CSDL.  Các loại ràng buộc:  Ràng buộc khóa (Key Constraints)  Ràng buộc miền giá trị (Domain Constraints)  Ràng buộc toàn vẹn tham chiếu (Referential Integrity Constraints). 19 08/10/2012 1.Khóa chính  Khi cài đặt một quan hệ phải xác định thuộc tính làm khóa  Thuộc tính được chọn gọi là khóa chính (PK - primary key)  Ý nghĩa: Khóa chính dùng để xác định duy nhất một bộ trong quan hệ.  Đặc điểm thuộc tính khóa:  Xác định duy nhất 1 bộ trong quan hệ  Các thuộc tính khóa chính phải có giá trị khác null  Các thuộc tính khóa chính thường được ký hiệu gạch dưới  Ví dụ: NHANVIEN( MANV, TENNV, HONV, NS, DCHI, GT, LUONG, PHG) 20 08/10/2012 2. Khóa ngoại  Khóa ngoại là Khóa chính của một quan hệ được đưa sang làm thuộc tính của một quan hệ khác.  Dùng để thể hiện mối quan hệ giữa các bảng, giúp tham chiếu dữ liệu hay kết nối các quan hệ trong một cơ sở dữ liệu với nhau.  Yêu cầu với khóa ngoại:  không cần cùng tên với khóa chính  bắt buộc cùng kiểu với khóa chính của bảng kia  VD: NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, PHG) PHONGBAN(TENPHG, MAPHG) Khóa chính Khóa ngoại Quan hệ tham chiếu Quan hệ bị tham chiếu 21 08/10/2012 3. Tham chiếu  Một bộ trong quan hệ R, tại thuộc tính A nếu nhận một giá trị từ một thuộc tính B của quan hệ S, ta gọi R tham chiếu S  Yêu cầu: Bộ được tham chiếu đến phải tồn tại trước TENNV HONV NS DCHI GT LUONG PHG Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 Ba Ria VT Nam 38000 5 TENPHG MAPHG Nghien cuu 5 Dieu hanh 4 Quan ly 1 R S 22 08/10/2012 4. Miền giá trị  mỗi thuộc tính khi nhập dữ liệu thì giá trị phải đảm bảo phù hợp với miền của thuộc tính quy định  Ràng buộc miền giá trị thỏa mãn:  giá trị của mỗi thuộc tính A phải là một giá trị nguyên tử thuộc miền giá trị dom(A) đã xác định. Gồm:  Kiểu số nguyên: short integer, integer, long integer  Kiểu số thực: float, double, float.  Kiểu dữ liệu ký tự - text: dãy ký tự với độ dài cố định, thay đổi  Kiểu ngày, thời gian – date/time và tiền tệ - currency  giá trị chỉ được nhận các giá trị cho phép  Ví dụ: Name: Tập hợp các dãy chữ cái có độ dài <= 30. Tuổi: Tập các số nguyên nằm trong khoảng từ 1 đến 80 23 08/10/2012 IV.Các đặc trưng của quan hệ  Thứ tự các bộ trong quan hệ là không quan trọng  Thứ tự giữa các giá trị trong một bộ là quan trọng  Mỗi giá trị trong một bộ  Hoặc là một giá trị nguyên tố  Hoặc là một giá trị rỗng (null)  Không có bộ nào trùng nhau: giống nhau về giá trị trên thuộc tính khóa Tung Nguyen 12/08/1955 638 NVC Q5 Nam 40000 5 TENNV HONV NGSINH DCHI PHAI LUONG PHG Hang Bui 07/19/1968 332 NTH Q1 Nu 25000 4 Nhu Le 06/20/1951 291 HVH QPN Nu 43000 4 Hung Nguyen 09/15/1962 null Nam 38000 5 Bộ: khác Bộ: Cơ sở dữ liệu 24 Tóm tắt các ký hiệu Lược đồ quan hệ R bậc n R(A1, A2, , An) Tập thuộc tính của R U Quan hệ r Bộ t, v Miền giá trị của thuộc tính A Dom(A) Giá trị của bộ t tại thuộc tính A t.A hay t[A] 25 08/10/2012 V. Chuyển đổi mô hình ER sang quan hệ Các quy tắc chuyển đổi Ví dụ cụ thể 26 08/10/2012 1. Các quy tắc chuyển đổi Qui tắc 1: Chuyển kiểu thực thể Chuyển đổi mỗi kiểu thực thể bình thường thành một lược đồ quan hệ  Thuộc tính của quan hệ là thuộc tính của kiểu thực thể.  Khóa chính của quan hệ là thuộc tính khóa của KTT  Quan hệ không chứa các thuộc tính đa trị 27 08/10/2012 CUSTOMER entity type with composite attribute CUSTOMER relation with address detail 28 08/10/2012 * Ví dụ: Nếu có thuộc tính phức hợp được biểu diễn bởi các thuộc tính đơn thì trong quan hệ sẽ chứa các thuộc tính đơn thành phần EMPLOYEE Bdate SSN Name Sex Address Salary Fname Midn Lname Quan hệ: EMPLOYEE (Ssn, fname, midn, lname, bdate, sex, address, salary) 29 08/10/2012  Nếu có thuộc tính đa trị Mỗi thuộc tính đa trị sinh ra quan hệ mới Thuộc tính khóa của quan hệ mới sẽ bao gồm thuộc tính khóa của quan hệ ban đầu và thuộc tính gây ra sự đa trị 30 08/10/2012 Multivalued attribute becomes a separate relation with foreign key 1–to–many relationship between original entity and new relation 31 08/10/2012 DEPARTMENT Name Number Locations DEPARTMENT(Dnumber, Dname) DEPT_LOCATIONS( DNumber, DLocation) 32 08/10/2012 Qui tắc 2: Thực thể yếu (Weak Entity) Mỗi thực thể yếu trong mô hình ER, tạo thành một quan hệ mới có tên cùng tên với thực thể yếu Tất cả thuộc tính của thực thể yếu trở thành thuộc tính của quan hệ mới Thêm thuộc tính khóa của quan hệ chủ vào làm khóa ngoại của quan hệ mới 33 08/10/2012 Ví dụ THÂN NHÂN( MNV-TEN, Giới tính, Ngày sinh, Quan hệ, Mã NV) NHÂN VIÊN THÂN NHÂN Có Tên Giới tính Ngày sinh Quan hệ Mã NV 1 n 34 08/10/2012  Qui tắc 3: quan hệ nhiều - nhiều Một quan hệ sẽ được chuyển thành một lược đồ quan hệ và tên của mối kết hợp sẽ là tên của lược đồ quan hệ  Quan hệ chứa các khóa của các kiểu thực thể tham gia vào mối liên kết. Khóa của quan hệ gồm cả hai khóa của hai kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của mối liên kết.  Ví dụ: 35 08/10/2012 Ví dụ:  EMPLOYEE( ESSN, ) PROJECT( Pnumber, ) WORKS_ON( PNumber, ESSN, Hours) EMPLOYEE PROJECT WORKS_ON ESSN HOURS M N PNumber 36 08/10/2012 Bill-of-materials relationships (M:N) ITEM and COMPONENT relations 37 08/10/2012 New intersection relation Foreign key Foreign key Composite primary key The Supplies relationship will need to become a separate relation 38 08/10/2012  Qui tắc 4: quan hệ một - nhiều Đặt khóa của kiểu thực thể bên phía một và các thuộc tính của mối liên kết vào quan hệ của kiểu thực thể bên phía nhiều. Ví dụ: NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG , MAPHG ) A B NHANVIEN TENNV NS DCHI GT LUONG HONV MANV Lam_viec PHONGBAN MAPHG TENPHG (1,1) (1,n) PHONGBAN( MAPHG, TENPHG ) 39 08/10/2012 EMPLOYEE relation with recursive foreign key EMPLOYEE entity with Manages relationship 40 08/10/2012 Note the mandatory one Again, no null value in the foreign keythis is because of the mandatory minimum cardinality 41 08/10/2012  Qui tắc 5: quan hệ một – một Đặt khóa của kiểu thực thể bên phía tùy chọn đưa sang làm thuộc tính của phía bắt buộc và nếu liên kiết có thuộc tính thì nó là thuộc tính của phía bắt buộc.  Ví dụ: EMPLOYEE DEPARTMENT MANAGES SSN StartDate 1 1 DEPARTMENT(, MGRSSN, MGRSTARTDATE) Khóa ngoại Thuộc tính của mối liên kết EMPLOYEE( SSN, ) 42 08/10/2012 EMPLOYEE relation with recursive foreign key EMPLOYEE entity with Manages relationship 43 08/10/2012 44 08/10/2012 Qui tắc 6: Cho mối liên kết có bậc >2, tạo ra mối quan hệ (R) mới Mối liên kết có bao nhiêu kiểu thực thể bên phía một thì quan hệ có bấy nhiêu khóa: đối với một kiểu thực thể bên phía một thì khóa của quan hệ gồm cả hai khóa của hai kiểu thực thể còn lại. Nếu không có kiểu thực thể bên phía một thì khóa của quan hệ bao gồm cả ba khóa của ba kiểu thực thể. Thuộc tính của quan hệ là thuộc tính của mối liên kết. Ví dụ: SUPPLIER PROJECT SUPPLY SNAME QUANTITY PROJNAME PART PARTNO SUPPLIER( SNAME, ) PROJECT( PROJNAME, ) PART( PARTNO, ) SUPPLY (SNAME, PROJNAME, PARTNO, QUANTITY) 45 08/10/2012 46 08/10/2012 Qui tắc 7: Chuyển liên kết ‘is-a’ Chuyển mỗi kiểu thực thể con thành một quan hệ Thuộc tính khóa của các quan hệ này là thuộc tính khóa của kiểu thực thể cha Các thuộc tính của mỗi kiểu thực thể con sẽ là thuộc tính của quan hệ. 47 08/10/2012 48 08/10/2012 2. Ví dụ áp dụng Ví dụ 1: Xây dựng các lược đồ quan hệ từ mô hình ER NHÀ PHÒNG SINH VIÊN Có Chứa 1 (1,12) n (1,1) 1 n (1,n) (1,1) Làm trưởng phòng (0,1) 1 (1,1) 1 Số nhà Số tầng Mã phòng Tên phòng Mã SV Tên SV Giới tính Đối tượng Tên lớp 49 08/10/2012 Ví dụ 2: Xây dựng các lược đồ quan hệ từ mô hình ER DỰ ÁN Tên Mã số Địa điểm THÂN NHÂN Tên Ngày sinh Giới tính ĐƠN VỊ Tên Mã số Địa điểm NHÂN VIÊN Mã số Họ tên Họ đệm Tên Ngày sinh Giới tính HSL Địa chỉ Có (0,n) (1,1) Ngày bắt đầu Quản lý (1,1) (0,1) Làm việc cho (1,1) (1,n) Làm việc Số giờ (1,n) (1,n) Kiểm soát (1,n) (1,1) Giám sát (1,n) (1,1) 50 08/10/2012 Mô hình quan hệ NHANVIEN( MNV, HoTen, Ten, NS, GT, HSL, DC, MĐV) DONVI( MĐV, Tên ) DUAN( MDA, Ten, DiaDiem ) DIADIEM_DV( MDV, DiaDiem ) THANNHAN( Ten, NS, GT, MNV ) LAMVIEC( MNV, MDA, SoGio) Ví dụ 3: Cho mô hình quan niệm dữ liệu của hệ thống quản lý bán hàng của công ty ABC. Mô tả về hệ thống như sau: Công ty kinh doanh nhiều loại mặt hàng gia dụng theo phƣơng thức bán sỉ và giao hàng tận nơi, thông qua điện thoại, mạng Internet, hoặc trực tiếp đặt hàng từ khách mua. Thông qua nhu cầu, khách hàng yêu cầu đặt hàng, nhân viên phụ trách của công ty sẽ lập phiếu đặt hàng cho khách với các sản phẩm yêu cầu. Thông tin đơn đặt hàng bao gồm số đơn đặt hàng, ngày, giờ, mã sản phẩm, số lượng, đơn giá cùng với thông tin của khách hàng nhƣ họ tên khách hàng, địa chỉ, điện thoại, email. Mỗi sản phẩm có một mã số, một mô tả sản phẩm, công dụng, giá bán thống nhất. Bộ phận phụ trách các đơn đặt hàng gồm 5 nhân viên, mỗi nhân viên có mã số, tên nhân viên, điện tthoại, chức vụ đƣợc ghi nhận trên đơn đặt hàng. Khi đơn đặt hàng đƣợc giải quyết, chính nhân viên đó sẽ ghi hoá đơn cho khách và hoá đơn sẽ đƣợc chuyển xuống bộ phận kho để đóng gói và giao hàng cho khách. Khi có vài sản phẩm bị thiếu, cùng với hoá đơn của sản phẩm đƣợc gởi, nhân viên sẽ có thêm ghi chú cho khách rõ. Đối với đơn đặt hàng chƣa giải quyết đƣợc hết thì phải xếp chờ. Ngay khi bộ phận kho nhập vào sản phẩm mới thì đơn hàng sẽ đƣợc giải quyết ngay theo thứ tự. 51 08/10/2012