Cơ sở dữ liệu - Chương III: Mô hình cơ sở dữ liệu quan hệ

Chủ động – Tích cực Học tập 1"25/2/16 CHƯƠNG III: MÔ HÌNH CSDL QUAN HỆ Chủ động – Tích cực Học tập 3"25/2/16 I. Giới thiệu mô hình quan hệ? ! Do tiến sĩ E. F. Codd đưa ra đầu 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ệ và 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. Chủ động – Tích cực Học tập 4"25/2/16 II. Các thành phần của mô hình quan hệ Chủ động – Tích cực Học tập 5"25/2/16 1. Quan hệ !  Là tập hợp các thông tin lưu trữ trong CSDL được tổ chức dưới dạng bảng 2 chiều "  Mỗi bảng 2 chiều gồm các cột – hàng chứa các thông tin liên quan nhau là một quan hệ 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 Chủ động – Tích cực Học tập 6"25/2/16 !  Mỗi một Quan hệ xác định: "  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 Chủ động – Tích cực Học tập 7"25/2/16 ! Quan hệ ~ Tập các đối tượng của thực thể ! Một dòng ~ Một đối tượng cụ thể của thực thể ! Mỗi cột ~ Một thuộc tính, một đặc tính của đối tượng Chủ động – Tích cực Học tập 8"25/2/16 *Lược đồ quan hệ !  Lược đồ quan hệ: nhằm mục đích mô tả ngắn gọn cấu trúc của một quan hệ và mối liên hệ giữa các thuộc tính trong quan hệ đó !  Lược đồ quan hệ được xác định gồm: "  Tên của quan hệ và tên của các thuộc tính "  Bậc: là số thuộc tính của lược đồ "  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ệ: Tên Quan hệ (Ds các thuộc tính) !  VD: "  NHANVIEN( MNV, Hoten, NS, QueQuan, HSL) !  NHANVIEN là lược đồ bậc 5 mô tả đối tượng nhân viên Chủ động – Tích cực Học tập ! 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"25/2/16 Chủ động – Tích cực Học tập 11"25/2/16 *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) Chủ động – Tích cực Học tập 12"25/2/16 2. Bộ !  Bộ: "  Là các dòng biểu diễn của quan hệ (trừ dòng tiêu đề - tên của các thuộc tính) # Bộ là tập hợp thông tin của một thực thể cụ thể "  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ệ !  Bộ là 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 Chủ động – Tích cực Học tập 13"25/2/16 3. Miền giá trị !  Miền giá trị của thuộc tính A, "  Ký hiệu: Dom(A) hoặc D(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ê hoặc 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 } "  Chủ động – Tích cực Học tập 15"25/2/16 4. Liên kết ! Liên kết là thể hiện mối quan hệ ràng buộc giữa các bảng (quan hệ) ! loại liên kết: " Liên kết một – một " Liên kết một – nhiều Chủ động – Tích cực Học tập 16"25/2/16 III.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ộ: Chủ động – Tích cực Học tập 17"25/2/16 IV. Ràng buộc lược đồ quan hệ !  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 (RBTV) là các điều kiện bất biến không được vi phạm trong một CSDL # phải đảm bảo khi nhập dữ liệu cho quan hệ !  Các loại ràng buộc cơ bản: "  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). Chủ động – Tích cực Học tập 18"25/2/16 1.Ràng buộc Khóa !  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ệ. !  Ràng buộc thuộc tính khóa – khóa chính: "  Các giá trị trong thuộc tính khóa không được trùng nhau, "  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) Chủ động – Tích cực Học tập 19"25/2/16 *. Khóa ngoại !  Khóa ngoại là Khóa chính của một quan hệ được đưa sang làm một 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. !  Các yêu cầu ràng buộc 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 "  Chỉ nhận giá trị đã có tương ứng của khóa chính !  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 Chủ động – Tích cực Học tập 20"25/2/16 2. Ràng buộc tham chiếu !  Là thuộc tính của một quan hệ này lấy giá trị trong một thuộc tính của quan hệ khác. ! 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 Chủ động – Tích cực Học tập 21"25/2/16 3. Ràng buộc 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 giá trị 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 phải là một giá trị nguyên tử, 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ập trong các giá trị cho phép (dom(A)) !  Ví dụ: Name: Text(30) Tuổi: >=1 and <=80 NgayGiaoHang: >now( ) Chủ động – Tích cực Học tập Cơ sở dữ liệu - Khoa CNTH 22 4. Biểu diễn Ràng buộc ! RBTV của CSDL được xác định bởi 3 thành phần: • Nội dung: phát biểu của RBTV • Bối cảnh: là tên các quan hệ có liên quan khi xảy ra RBTV • Bảng tầm ảnh hưởng: $  Xác định thao tác cập nhật nào cần phải kiểm tra RBTV khi được thực hiện trên quan hệ bối cảnh $  Xác định thời điểm cần thiết phải tiến hành kiểm tra RBTV (thêm/sửa/xóa) Chủ động – Tích cực Học tập Cơ sở dữ liệu - Khoa CNTH 23 Bảng tầm ảnh hưởng Tên_RB Quan hệ n Quan hệ 1 Quan hệ 2 Thêm Xóa Sửa + -(*) + (Thuộc tính) + - - + - - (+) Xảy ra Vi phạm RBTV (-) Không xảy ra vi phạm RBTV +(Thuộc tính): xảy ra RBTV khi thao tác trên thuộc tính -(*): không thể gây ra vi phạm RBTV do thao tác không thực hiện được Chủ động – Tích cực Học tập Cơ sở dữ liệu - Khoa CNTH 24 Ví dụ 1 ! Xét lược đồ quan hệ " NHANVIEN (MANV, HT, NS, GT, DC, LUONG) ! RBTV: Giới tính của nhân viên là Nam hoặc Nữ ! Thực hiện: " Nội dung: GT của nhân viên chỉ có thể là Nam hoặc Nữ " Bối cảnh: NHANVIEN " Bảng tầm ảnh hưởng: Thêm Xóa Sửa NHANVIEN + - +(GT) Chủ động – Tích cực Học tập Cơ sở dữ liệu - Khoa CNTH 25 Ví dụ 2 ! Xét lược đồ quan hệ: PHANCONG( MNV, MDA, SoGio) ! RBTV: Thời gian tham gia đề án của một nhân viên không quá 15 giờ ! Thực hiện: " Nội dung: Thời gian tham gia dự án của nhân viên không quá 60 giờ " Bối cảnh: LAMVIEC " Bảng tầm ảnh hưởng: Thêm Xóa Sửa LAMVIEC +(SoGio) - +(SoGio) Chủ động – Tích cực Học tập Cơ sở dữ liệu - Khoa CNTH 26 ! Xét các lược đồ quan hệ DATHANG(MADH, MAKH, NGAYDH) GIAOHANG(MAGH, MADH, NGAYGH) ! RBTV: Với mỗi một đơn hàng, phải có ngày đặt hàng trước ngày giao hàng ! Thực hiện: ! Nội dung: Với mỗi một đơn hàng, phải có ngày đặt hàng trước ngày giao hàng ! Bối cảnh: DATHANG, GIAOHANG ! Bảng tầm ảnh hưởng: Thêm Xóa Sửa DATHANG - + + (MGAYDH) GIAOHANG + - + (NGAYGH) Ví dụ 3 Chủ động – Tích cực Học tập 27"25/2/16 IV. Chuyển đổi mô hình ER sang quan hệ ! Các quy tắc chuyển đổi ! Ví dụ cụ thể Chủ động – Tích cực Học tập 28"25/2/16 1. Các quy tắc chuyển đổi ! Qui tắc 1: Chuyển kiểu thực thể mạnh " Chuyển đổi mỗi kiểu thực thể mạnh thành một lược đồ quan hệ (tên trùng hoặc đặt mới) " Quan hệ mới gồm: $ Các thuộc tính của kiểu thực thể ban đầu $ Quan hệ chứa các thuộc tính lưu trữ và thuộc tính thành phần của thuộc tính phức hợp. $ Thuộc tính khóa giữ nguyên Chủ động – Tích cực Học tập 30"25/2/16 * Ví dụ: DEPARTMENT Name Number Locations Quan hệ: DEPARTMENT ( Dnumber, Dname) Lưu ý: Thuộc tính Locations không có trong quan hệ vì nó là thuộc tính đa trị. PROJECT Number Name Location Quan hệ: PROJECT (Pnumber, Pname, Plocation) EMPLOYEE Bdate SSN Name Sex Address Salary Fname Midn Lname He SL Quan hệ: EMPLOYEE (Ssn, fname, midn, lname, bdate, sex, address, HeSL) Chủ động – Tích cực Học tập 31"25/2/16 ! Qui tắc 2: Chuyển thuộc tính đa trị " Mỗi thuộc tính đa trị sinh ra một quan hệ mới ! Tên đặt mới phù hợp " Quan hệ mới gồm: $ thuộc tính khóa của quan hệ ban đầu $ thuộc tính gây ra sự đa trị $ Khóa của quan hệ mới là cả 2 thuộc tính Chủ động – Tích cực Học tập 33"25/2/16 DEPARTMENT Name Number Locations DEPARTMENT( Dnumber, Dname ) DEPT_LOCATIONS( DNumber, DLocation ) Chủ động – Tích cực Học tập 34"25/2/16 ! Qui tắc 3: Chuyển 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 (tên cùng tên hoặc đổi mới) " Quan hệ mới gồm: $ Tất cả thuộc tính của thực thể yếu $ Thêm thuộc tính khóa của quan hệ chủ $ Khóa của quan hệ mới gồm thuộc tính khóa của quan hệ chủ và thuộc tính khóa của thực thể yếu Chủ động – Tích cực Học tập 35"25/2/16 Ví dụ ! NHÂN VIÊN( Mã NV, Họ tên, Ngày sinh, HSL) ! THÂN NHÂN( 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 Ho Tên Ngày sinh HSL 1 n Chủ động – Tích cực Học tập 36"25/2/16 !  Qui tắc 4: Chuyển liên kết quan hệ nhiều - nhiều " Một liên kết quan hệ sẽ được chuyển thành một lược đồ quan hệ mới (tên của mối kết hợp sẽ là tên của lược đồ quan hệ hoặc tùy ý) " Quan hệ mới gồm: ! Hai khóa của kiểu thực thể tham gia vào mối liên kết. ! Thuộc tính của mối liên kết (nếu có) ! Khóa của quan hệ mới là 2 thuộc tính khóa của thực thể ban đầu !  Ví dụ: Chủ động – Tích cực Học tập 37"25/2/16 ! Ví dụ: % EMPLOYEE( ESSN, ) PROJECT( Pnumber, ) WORKS_ON( PNumber, ESSN, Hours) EMPLOYEE PROJECT WORKS_ON ESSN HOURS M N PNumber Chủ động – Tích cực Học tập 40"25/2/16 !  Qui tắc 5: Chuyển quan hệ một - nhiều "  Đặt khóa của 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 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,n) (1,1) PHONGBAN( MAPHG, TENPHG ) Chủ động – Tích cực Học tập 43"25/2/16 !  Qui tắc 6: Chuyển quan hệ một – một "  Đặt khóa của thực thể bên phía tùy chọn vào làm thuộc tính ở bên phía bắt buộc "  Nếu liên kết có thuộc tính thì thuộc tính đặt trong quan hệ bên phía bắt buộc !  Ví dụ: NHÂN VIÊN ĐƠN VỊ Quản lý MNV StartDate

ĐƠN VỊ (, MGRSSN, MGRSTARTDATE) Khóa ngoại Thuộc tính của mối liên kết NHÂN VIÊN ( MNV, ) Chủ động – Tích cực Học tập 46"25/2/16 ! Qui tắc 7: Chuyển cho mối liên kết có bậc >2, " Tạo ra mối quan hệ (R) mới (đặt tên tùy ý) " Quan hệ mới gồm: ! Thuộc tính khóa của các thực thể tham gia liên kết ! Thuộc tính của mỗi liên kết(nếu có) ! Khóa là tất cả các thuộc tính khóa ! Ví dụ: SUPPLIER PROJECT SUPPLY SNAME QUANTITY PROJNAME PART PARTNO SUPPLIER( SNAME, ) PROJECT( PROJNAME, ) PART( PARTNO, ) SUPPLY (SNAME, PROJNAME, PARTNO, QUANTITY) Chủ động – Tích cực Học tập 48"25/2/16 2. Ví dụ áp dụng ! Ví dụ : 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 n 1 n Làm trưởng phòng 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 Người quản ly Chủ động – Tích cực Học tập 49"25/2/16 Ví dụ : Xây dựng các lược đồ quan hệ từ mô hình ER DỰ ÁN Tên Mã số Địa điểm T/g bắt đầu ĐƠ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ỉ Lương 1 1 Làm việc cho n Làm việc Số giờ m n Kiểm soát 1 n Ngoại ngữ Chủ động – Tích cực Học tập 50"25/2/16 Ví dụ 3 ! Qua quá trình khảo sát, điều tra hoạt động của một trường đại học ta rút ra các quy tắc quản lý sau: " Một trường chia làm nhiều khoa, mỗi khoa gồm các thông tin: mã khoa, tên khoa và địa điểm. Mỗi khoa có thể đặt ở nhiều nơi khác nhau " Mỗi khoa cung cấp nhiều môn học. Mỗi môn học thuộc về 1 khoa (thuộc quyền quản lý của 1 khoa). Mỗi môn học gồm các thông tin: Mã môn học, tên môn học, học kỳ, số trình, số tiết (mỗt trình có 15 tiết) " Mỗi khoa thuê nhiều giáo viên giảng dạy. Nhưng mỗi giáo viên chỉ làm việc cho 1 khoa. Mỗi khoa có 1 chủ nhiệm khoa, đó là một giáo viên. Mỗi giáo viên có thể dạy nhiều nhất 4 môn học và có thể không dạy môn học nào. Mỗi giáo viên gồm: mã GV, họ tên, ngày sinh, giới tính, trình độ, chuyên môn. " Một khoa quản lý nhiều lớp, mỗi lớp có nhiều sinh viên, một sinh viên chỉ thuộc một lớp. Mỗi lớp gồm: mã lớp, tên lớp, sĩ số, năm bắt đâu, năm kết thúc. Mỗi sinh viên học 4 năm ra trường. " Mỗi sinh viên có thể học nhiều môn học, nhưng ít nhất là 1 môn. Khi SV học phải lưu lại điểm của SV đó. Mỗi môn học có thể có nhiều sinh viên học, có thể không có sinh viên nào. Mỗi sinh viên gồm các thông tin: mã sinh viên, họ tên, giới tính, quê quán, địa chỉ mail. Chủ động – Tích cực Học tập ! Yêu cầu: " Xác định thực thể và các thuộc tính " Xác định các mối quan hệ giữa các thực thể " Vẽ mô hình ER của bài toán " Áp dụng nguyên tắc để xây dựng mô hình CSDL quan hệ " Cài đặt bằng hệ quản trị CSDL cụ thể 51"25/2/16 Chủ động – Tích cực Học tập ! 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ự. 52"25/2/16 Chủ động – Tích cực Học tập Bài tập Áp dụng ! Thiết kế cơ sở dữ liệu từ tài liệu nguồn theo dõi bán hàng 55"25/2/16 CSDL