Bài giảng Cơ sở dữ liệu 1

sds 1 1 KHOA HTTT KINH TẾ & TMĐT BÀI GIẢNG HỌC PHẦN Bô ̣ môn: Tin học 2 Mục đích của học phần: Trang bị các kiến thức cơ bản về CSDL quan hệ, ngôn ngữ thao tác trên CSDL. Cung cấp các kỹ năng cơ bản về tổ chức CSDL: thiết kế CSDL, ngôn ngữ truy vấn. Yêu cầu đạt được: Nắm vững các kiến thức cơ bản về CSDL Hiểu các phương pháp thiết kế một CSDL Nhận thức được vai trò và sự cần thiết của hệ thống quản lý dữ liệu với một tổ chức kinh tế xã hội Biết thiết kế và tạo lập những CSDL dựa trên một HQT CSDL thông dụng kết hợp với NNLT để lập trình quản lý CSDL 3 Cấu trúc học phần: 24,6 – Số TC: 02 (30 tiết) Nghe giảng: 24 tiết Thảo luận (bài tập): 6 tiết 4  Chương 1: Tổng quan về CSDL  Chương 2: CSDL quan hệ  Chương 3: Ngôn ngữ khai thác CSDL quan hệ DHTM_TMU sds 2 5 1. TS Nguyễn Thị Thu Thủy. Giáo trình CSDL1. NXB Thống kê, 2015 2. Hồ Thuần, Hồ Cẩm Hà, Các hệ cơ sở dữ liệu lý thuyết và thực hành, Nhà xuất bản giáo dục, 2009. 3. Nguyễn Kim Anh, Nguyên lý của các hệ cơ sở dữ liệu, Nhà xuất bản Đại học Quốc gia Hà Nội, 2008 4. Phương Lan, Giáo trình Cơ sở dữ liệu, nhà xuất bản lao động xã hội, 2007 5. Sumathi, Esakkirajan, S. Fundamentals of Relational Database Management Systems, Springer-Verlag, 2007 6. Elmasri, Navathe, Fundamentals of Database Systems, 6th Edition, 2010 7. Avi Silberchatz, Henry F. Korth, S. Sudarshan. Database concepts. 6  1.1. Các khái niệm cơ bản về CSDL  1.2. Mô hình dữ liệu  1.3. Kiến trúc cơ sở dữ liệu  1.4. Ngôn ngữ quản trị cơ sở dữ liệu 7  CSDL (Database): ◦ Là một tập hợp các dữ liệu có liên quan với nhau chứa thông tin về một tổ chức nào đó (trường đại học, công ty,) được lưu trữ trên các thiết bị nhớ để đáp ứng nhu cầu khai thác thông tin của nhiều người sử dụng với nhiều mục đích khác nhau.  Một số đặc tính của CSDL ◦ Tính tự mô tả ◦ Tính độc lập giữa chương trình và dữ liệu ◦ Tính trừu tượng dữ liệu ◦ Tính nhất quán ◦ Các cách nhìn dữ liệu 8  Hệ QT CSDL (Database Management System - DBMS): là một tập hợp các chương trình cho phép người dùng định nghĩa, tạo lập, bảo trì các CSDL và cung cấp các truy cập có điều khiển đến các CSDL này.  Hệ QT CSDL cung cấp các phương tiện sau: ◦ Ngôn ngữ định nghĩa dữ liệu (Data Denifition Language - DDL) ◦ Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML) ◦ Các kiểm soát, các điều khiển đối với việc truy cập vào CSDL DHTM_TMU sds 3 9  Thuật ngữ hệ CSDL để chỉ một CSDL và một hệ QT CSDL để truy cập vào CSDL đó  Hệ CSDL cung cấp cho người dùng một cách nhìn trừu tượng của DL, che giấu những chi tiết phức tạp về cách thức DL được lưu trữ và bảo trì.  Hệ CSDL là một hệ thống gồm 4 thành phần sau: ◦ Cơ sở dữ liệu ◦ Người sử dụng ◦ Phần mềm hệ QT CSDL ◦ Phần cứng 10 Xử lý truy vấn HQT CSDL Truy xuất dữ liệu CSDL Định nghĩa CSDL Người sử dụng/Lập trình viên Chương trình ứng dụng/Truy vấn Hệ CSDL 11 12  Một lược đồ được viết trong ngôn ngữ định nghĩa dữ liệu của một hệ QT CSDL cụ thể. Để mô tả các yêu cầu dữ liệu của một tổ chức sao cho mô tả đó dễ hiểu đối với nhiều NSD khác nhau thì ngôn ngữ này lại ở mức quá thấp  cần có một mô hình dữ liệu  Khái niệm: Mô hình dữ liệu là một tập các khái niệm và các ký pháp dùng để mô tả dữ liệu, các mối quan hệ của dữ liệu, các ràng buộc trên dữ liệu của một tổ chức. DHTM_TMU sds 4 13  Phần mô tả cấu trúc của CSDL  Phần mô tả các thao tác, định nghĩa các phép toán được phép trên dữ liệu.  Phần mô tả các ràng buộc toàn vẹn để đảm bảo sự chính xác của dữ liệu. 14  Mô hình dữ liệu logic trên cơ sở đối tượng ◦ Mô hình thực thể - liên kết ◦ Mô hình hướng đối tượng ◦ Mô hình dữ liệu ngữ nghĩa ◦ Mô hình dữ liệu chức năng  Mô hình dữ liệu logic trên cơ sở bản ghi ◦ Mô hình quan hệ ◦ Mô hình mạng ◦ Mô hình phân cấp  Mô hình dữ liệu logic vật lý ◦ Mô hình hợp nhất ◦ Mô hình bộ nhớ khung 15 Người dùng 1 Khung nhìn 1 Khung nhìn n CSDL mức khái niệm CSDL Mức vật lý Người dùng n Mức ngoài (mức khung nhìn) Mức khái niệm (mức logic) Mức trong (mức vật lý) 16  Mức trong ◦ Mô tả dữ liệu được thực sự lưu trữ như thế nào trong CSDL. ◦ Là mức thể hiện các cài đặt có tính chất vật lý của CSDL để đạt được tối ưu trong các thao tác tìm kiếm, lưu trữ (tận dụng các vùng nhớ còn trống) ◦ Là mức phản ánh các CTDL, các tổ chức tệp được dùng cho lưu trữ dữ liệu trên các thiết bị nhớ thứ cấp.  Mức khái niệm ◦ Mô tả những dữ liệu nào được lưu trữ trong CSDL và có những mối quan hệ nào giữa các dữ liệu này (biểu diễn các thực thể, các thuộc tính và các mối quan hệ đó)  Mức ngoài ◦ Mô tả chỉ một phần của toàn bộ CSDL, phần thích hợp với một NSD nhất định. ◦ Hệ thống có thể cung cấp nhiều khung nhìn đối với cùng một CSDL. DHTM_TMU sds 5 17  Lược đồ CSDL (Database Schema) ◦ Là các mô tả về cấu trúc và ràng buộc trên CSDL SVien Ten MaSV Nam Khoa TenHP MaHP TinChi Khoa Hphan MaHP MaHP_Truoc DKien MaKH MaHP KHoc HocKy Nam GV MaKH MaSV KQua Diem 18 Thể hiện  Thể hiện CSDL (Database Instance) ◦ Là dữ liệu hiện thời được lưu trữ trong CSDL ở một thời điểm nào đó ◦ Tình trạng của CSDL Ten MaSV Nam Khoa Son 17 1 HTTTKT Bao 8 2 TMĐT SVien TenHP Tin học đại cương CSDL1 Hphan MaHP INFO0111 INFO6111 TinChi Khoa 3 2 HTTTKT HTTTKT MaHP MaHP_Truoc DKien INFO0111 MAT1234 INFO6111 INFO0111 MaSV Diem 17 8 17 6 KQua MaKH 8 8 8 8 112 119 85 92 102 135 10 9 8 10 19  Ứng với 3 mức của một hệ CSDL có 3 loại lược đồ ◦ Lược đồ ngoài (lược đồ con) ◦ Lược đồ logic ◦ Lược đồ vật lý  Phân biệt được lược đồ CSDL với bản thân CSDL 20 Người dùng Lược đồ ngoài 1 Lược đồ ngoài n Lược đồ khái niệm Lược đồ trong Người dùng Ánh xạ ngoài/ Ánh xạ khái niệm Mức ngoài Mức khái niệm Mức trong Ánh xạ khái niệm/ Ánh xạ trong Độc lập dữ liệu mức vật lý Độc lập dữ liệu mức logic DHTM_T U sds 6 21 TenHP Khoa MaHP TenHP TinChi TenHP MaHP TinChi Khoa Mức ngoài Mức logic Mức vật lý Struct Hphan{ Char MaHP; Char Ten[15]; Int TinChi; Char Khoa[10]; Struct Hphan next; }; Index MaHP; Khung nhìn 1 Khung nhìn 2 22  Ngôn ngữ định nghĩa dữ liệu (Data Denifition Language - DDL): cho phép người dùng định nghĩa CSDL (đặc tả các kiểu và các CTDL, đặc tả các ràng buộc trên các DL được lưu trữ trong CSDL) ◦ VD: Create table Co(Sohieu C(2), Ten C(10), Von N(4,0))  Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép người dùng thêm, xóa, cập nhật dữ liệu và truy xuất DL trong CSDL ◦ VD: Update Insert Delete SelectFromWhere 23  2.1. Các khái niệm cơ bản  2.2. Ràng buộc toàn vẹn  2.3. Phụ thuộc hàm  2.4. Chuẩn hóa các lược đồ quan hệ  2.5. Tách các lược đồ quan hệ  2.6. Thiết kế CSDL quan hệ 24  2.1.1 Thuộc tính  2.1.2 Miền  2.1.3 Quan hệ  2.1.4 Lược đồ quan hệ  2.1.5 Các tính chất của quan hệ  2.1.6 Khóa DHTM_TMU sds 7 25  Quan hệ gồm ◦ Tên ◦ Tập hợp các cột  Cố định  Được đặt tên  Có kiểu dữ liệu ◦ Tập hợp các dòng  Thay đổi theo thời gian  Một dòng ~ Một thực thể  Quan hệ ~ Tập thưc thể 26  Tên các cột của quan hệ  Mô tả ý nghĩa cho các giá trị tại cột đó  Tất cả các dữ liệu trong cùng 1 một cột đều có dùng kiểu dữ liệu TENNV HONV NS DIACHI GT LUONG PHG Tung Nguyen 12/08/1955 Hà Nội Nam 40000 5 Hang Bui 07/19/1968 Bắc Ninh Nu 25000 4 Nhu Le 06/20/1951 Bắc Giang Nu 43000 4 Hung Nguyen 09/15/1962 Hà Nội Nam 38000 5 Thuộc tính 27 Lược đồ quan hệ  Lược đồ quan hệ ◦ Tên của quan hệ ◦ Tên của tập thuộc tính NHANVIEN(MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG) Là tập hợp 28  Lược đồ CSDL ◦ Gồm nhiều lược đồ quan hệ 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) DHTM_TMU sds 8 29 Dữ liệu cụ thể của thuộc tính  Là các dòng của quan hệ (trừ dòng tiêu đề - tên của các thuộc tính)  Thể hiện dữ liệu cụ thể của các thuộc tính trong quan hệ 30  Là tập các giá trị nguyên tố gắn liền với một thuộc tính ◦ Kiểu dữ liệu cơ sở  Chuỗi ký tự (string)  Số (integer) ◦ Các kiểu dữ liệu phức tạp  Tập hợp (set)  Danh sách (list)  Mảng (array)  Bản ghi (record)  Ví dụ ◦ TENNV: string ◦ LUONG: integer Không được chấp nhận 31  Lược đồ quan hệ ◦ Cho 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 đồ ◦ NHANVIEN(MANV:integer, TENNV:string, HONV:string, NGSINH:date, DCHI:string, GT:string, LUONG:integer, DONVI:integer)  NHANVIEN là một lược đồ bậc 8 mô tả đối tượng nhân viên  MANV là một thuộc tính có miền giá trị là số nguyên  TENNV là một thuộc tính có miền giá trị là chuỗi ký tự 32  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=<v1, v2, , vn>  Mỗi vj là một phần tử của miền giá trị DOM(Aj) hoặc giá trị rỗng t1 t2 t3 t4 vi TENNV HONV NS DIACHI GT LUONG PHG Tung Nguyen 12/08/1955 Hà Nội Nam 40000 5 Hang Bui 07/19/1968 Bắc Ninh Nu 25000 4 Nhu Le 06/20/1951 Bắc Giang Nu 43000 4 Hung Nguyen 09/15/1962 Null Nam 38000 5 DHTM_TMU sds 9 33  Khái niệm: Ràng buộc toàn vẹn là tập các qui tắc, qui định yêu cầu DL trong CSDL phải thỏa mãn.  Mục đích: nhằm đảm bảo tính độc lập và tính toàn vẹn dữ liệu.  Các loại ràng buộc: ◦ Ràng buộc về miền (kiểu) ◦ Ràng buộc giải tích ◦ Ràng buộc khóa (logic) ◦ . 34  Ràng buộc về miền (là loại ràng buộc thấp nhất): mô tả tính chất của các thuộc tính khi tạo lập CSDL (thuộc kiểu gì, độ dài là bao nhiêu,) ◦ VD: Thuộc tính “số điện thoại” là kiểu chuỗi ký tự đúng bằng 7 ký tự trong xâu  Ràng buộc giải tích: là những ràng buộc giữa các thuộc tính được biểu diễn bằng các biểu thức toán học ◦ VD: Khi nhập “số lượng” và “đơn giá” của 1 mặt hàng, hệ thống sẽ tự động tính giá trị của thuộc tính “thành tiền” theo công thức “số lượng” x “đơn giá” = “thành tiền”  Ràng buộc về khóa (logic): ◦ Mối quan hệ giữa các thuộc tính với nhau không phải là các ràng buộc giải tích, được gọi là phụ thuộc hàm. ◦ Thuộc tính Y phụ thuộc hàm vào thuộc tính X  mỗi 1 giá trị của X xác định giá trị của Y 35  Khái niệm:  Cho R(U) là 1 lược đồ quan hệ, U ={A1,A2,,An} là tập các thuộc tính và X,Y U  Ta nói X xác định hàm Y (hay Y phụ thuộc hàm vào X) nếu với mọi quan hệ r xác định trên R(U) và với 2 bộ t1 và t2 bất kỳ mà t1[X]=t2[X] thì t1[Y] = t2[Y]  Nói cách khác thì thuộc tính Y của quan hệ R được gọi là phụ thuộc hàm vào thuộc tính X của quan hệ R nếu với mỗi giá trị của X xác định duy nhất một giá trị của Y. X được gọi là xác định hàm của Y  Ký hiệu: XY  Ví dụ: Phụ thuộc hàm của thuộc tính HoTenSV và MaSV được biểu diễn như sau: MaSV HoTenSV 36  Xét lược đồ quan hệ  Và thể hiện Phim(Tênphim, Nămsx, Thờilượng, Loạiphim, Xưởngsx, Diễnviên) Tênphim Nămsx Thờilượng Loạiphim Xưởngsx Diễnviên Star Wars 1977 124 color Fox Carrie Fisher Star Wars 1977 124 color Fox Mark Hamill Star Wars 1977 124 color Fox Harrison Ford Mighty Ducks 1991 104 color Disney Emilio Esteves Wayne’s World 1992 95 color Paramount Dana Carvey Wayne’s World 1992 95 color Paramount Mike Meyers DHTM_TMU sds 10 37  Tìm được nhiều PTH Tênphim Nămsx Thờilượng Loạiphim Xưởngsx Diễnviên Star Wars 1977 124 color Fox Carrie Fisher Star Wars 1977 124 color Fox Mark Hamill Star Wars 1977 124 color Fox Harrison Ford Mighty Ducks 1991 104 color Disney Emilio Esteves Wayne’s World 1992 95 color Paramount Dana Carvey Wayne’s World 1992 95 color Paramount Mike Meyers Tênphim, Nămsx  Diễnviên Tênphim, Nămsx  Thờilượng Tênphim, Nămsx  Loại Tênphim, Nămsx  Xưởngsx Không phải PTH 38  Hệ tiên đề cho phụ thuộc hàm: Hệ tiên đề Armstrong: 1. Luật phản xạ (reflexivity) X  Y  XY 2. Luật tăng trưởng(augmentation) XY, UZ  XZ  YZ 2. Luật bắc cầu(transitivity) X  Y, Y  Z  X  Z Hệ quả của hệ tiên đề Armstrong: 4. Luật hợp (the union rule) Cho X  Y, X  Z  X  YZ 5. Luật tựa bắc cầu (the pseudotransitivity rule) Cho X  Y,WY Z  XW  Z 6. Luật tách (the decomposition rule): Cho X  Y, Z  Y  X  Z 39  Cho R=ABC và tập phụ thuộc hàm F={ABC,CA} CRM: BC  ABC  Thật vậy: 1. Từ CA (gt) 2. BCAB (Luật tăng trưởng của (1) thêm B) 3. ABC (gt) 4. ABABC (Luật tăng trưởng của (3) thêm AB) 5. BCABC (Luật bắc cầu từ (2) và (4)) 40  2.3.1. Bao đóng của tập phụ thuộc hàm  2.3.2. Bao đóng của tập thuộc tính DHTM_TMU sds 11 41  Suy diễn logic ◦ Gọi F là tập các PTH trên sơ đồ quan hệ R(U) và XY là 1 PTH, X,YU. Khi đó ta nói XY được suy diễn logic từ F nếu với mỗi quan hệ r xác định trên R(U) thoả các phụ thuộc hàm trong F thì cũng thoả X  Y ◦ VD: F={AB, BC} thì AC được suy diễn logic từ F  Bao đóng của tập phụ thuộc hàm ◦ Gọi F+ là bao đóng của F, tức là tập tất cả các phụ thuộc hàm có thể được suy diễn logic từ F ◦ Ký hiệu là F+ ◦ F là họ đầy đủ (full family) nếu F = F+ 42  Định nghĩa: ◦ Gọi F là tập các PTH trên tập thuộc tính U, XU. ◦ X+ là bao đóng của X đối với F là tập tất cả các thuộc tính A mà PTH XA có thể được suy diễn logic từ F nhờ hệ tiên đề Armstrong. ◦ Ký hiệu là X+ ◦ X+ = { A  U | X  A  F+} 43 Thuật toán: ◦ Input: tập hữu hạn các thuộc tính U, tập các PTH F trên U và X U ◦ Output: X+, bao đóng của X đối với F Phương pháp: lần lượt tính các tập X0, X1, X2,theo các bước sau: ◦ Bước 0: Đặt X0 = X. ◦ Bước i: Tính Xi từ Xi-1 Nếu Y Z F và Y Xi-1 và A Z và A không thuộc Xi-1 thì Xi=Xi- 1 A ngược lại, Xi = Xi-1 . Nếu Xi ≠ Xi-1 thì lặp Bước i ngược lại, chuyển Bước n o Bước n X+ = Xi 44  R(A, B, C, D, E, G)  F = { ABC, CA, BCD, ACDB, DEG, BEC, CGBD, CE AG}  Tìm (BD)+F Giải:  X0 = BD  Tìm các PTH có VT là B, D hoặc BD: ◦ Có DEG X1=X0  {EG}= BDEG  Tương tự, ta có: ◦ BEC  X2=X1  {C}= BCDEG ◦ CA  X3=X2  {A}= ABCDEG ◦ X4=X3  Ngừng Vậy BD+F = ABCDEG DHTM_TMU sds 12 45  R(A, B, C, D, E, F)  F = { ABC, BCAD, DE, CFB }  Tìm AB+F Giải:  AB+F = AB  ABC: ABC  BCAD: ABCD  DE: ABCDE  Ngừng AB+F = {A, B, C, D, E} 46  Nhận xét về PTH: ◦ Từ một tập các phụ thuộc hàm có thể suy diễn ra các phụ thuộc hàm khác ◦ Trong một tập phụ thuộc hàm cho sẵn có thể có các phụ thuộc hàm dư thừa  Làm thế nào để có được một phụ thuộc hàm tốt ??? 47  Tương đương ◦ Hai tập PTH F1 và F2 gọi là tương đương  Bổ đề Mọi thể hiện của quan hệ thỏa F1 Mọi thể hiện của quan hệ thỏa F2  F1  F2  F1+  F2+ F1  F2  F1 là hệ quả của F2 và F2 là hệ quả của F1 48  R(A, B, C, D, E)  F1 = { ABC, AD, CDE }  F2 = { ABCE, AABD, CDE }  F1  F2 ?  Chứng minh ◦ F1 là hệ quả của F2  F1 được suy dẫn từ F2 ◦ F2 là hệ quả của F1  F2 được suy dẫn từ F1 DHTM_TMU sds 13 49  {ABCE, AABD, CDE }  {ABC, AD, CDE } ◦ Ta thấy F1  F2, hiển nhiên F1 là hệ quả của F2  {ABC, AD, CDE }  {ABCE, AABD, CDE } ◦ Xét F2 có AE, tìm xem F1 có AE ?  ABC AC (luật tách)  AD (gt)  ACD (luật hợp)  CDE  AE (luật bắc cầu) 50  R(A, B, C, D, E)  F1 = { ABC, AD, CDE }  F2 = { ABCDE }  F1  F2 ?  Chứng minh ◦ F1 là hệ quả của F2  F1 được suy dẫn từ F2 ◦ F2 là hệ quả của F1  F2 được suy dẫn từ F1 51  {ABCDE}  {ABC, AD, CDE} ◦ Xét CDE không thuộc trong F2 ◦ F1 không được suy dẫn từ F2 ◦ F1 không là hệ quả của F2  {ABC, AD, CDE}  {ABCDE} ◦ Xét F2 có AE? 52  Tập phụ thuộc hàm tương đương (equivalent functional dependancy) Cho F và G là hai tập phụ thuộc hàm trên tập thuộc tính U, ta nói F và G tương đương (hay F phủ G hoặc G phủ F) và ký hiệu là F+ = G+ nếu và chỉ nếu mỗi phụ thuộc hàm thuộc F đều thuộc G + và mỗi phụ thuộc hàm thuộc G đều thuộc F + .  Ký hiệu: F ≈ G  Ví dụ Cho R(A,B,C) F={AB;AC;BA;CA;BC} G={AB; CA; BC} Chứng minh F và G tương đương DHTM_TMU sds 14 53  Tập phụ thuộc hàm không dư thừa Định nghĩa: Tập phụ thuộc hàm F là không dư thừa nếu trong F không tồn tại một phụ thuộc hàm nào mà khi loại bỏ đi phụ thuộc hàm đó, cho ta một tập phụ thuộc hàm tương đương với F.  Thuật toán tìm phủ không dư thừa của một tập phụ thuộc hàm Lần lượt tính các tập F0, F1,, Fm theo các bước sau: Bước 0: F0 = F. Bước i: Tính Fi từ Fi-1, cụ thể: Nếu (Fi-1\{LiRi})≈F i thì Fi=Fi-1\{LiRi} ngược lại , Fi = Fi-1 . Nếu Fi ≠ Fi-1 thì lặp Bước i ngược lại, chuyển Bước n Bước n: F‟= Fi 54  Định nghĩa phủ tối thiểu F được gọi là một tập phụ thuộc hàm tối thiểu nếu F thoả đồng thời ba điều kiện sau: a) Vế phải của F chỉ có một thuộc tính. b) Không  f: XAF và Z  X mà F+=(F(XA)(ZA))+ c) Không  XAF mà F + = (F  (X  A))+ Trong đó - vế phải của mỗi phụ thuộc hàm ở điều kiện a) chỉ có một thuộc tính, nên bảo đảm không có thuộc tính nào ở vế phải là dư thừa. - điều kiện b) bảo đảm không có một thuộc tính nào tham gia vế trái của phụ thuộc hàm là dư thừa. - điều kiện c)bảo đảm cho tập F không có một phụ thuộc hàm nào là dư thừa. 55  VD1: ◦ Cho R(A,B,C) và F={ABC,BC} ◦ Ta có: F ≡ F - {AB C} {(AB-A) C}={BC}  ABC là PTH không đầy đủ (có VT dư thừa) BC là PTH đầy đủ  VD2: ◦ Cho R(A,B,C,D) và F = {A → BC,B → C,AB → D} ◦ Ta có: F ≡ F - {AB D}{(AB-B) D} ≡{A → BC,B → C,A → D}  ABD là PTH không đầy đủ (có VT dư thừa) 56  VD: Ở VD2, PTH ABD có A+=ABCD  AD A+ ◦ Trong F ta thay ABD bằng AD  Vậy F ≡ {A → BC,B → C,A → D} DHTM_TMU sds 15 57  Cho R(A,B,C,D) và F={AB, BA, BC, AC, CA}  Xét AB ◦ A+F – {AB} = AC ◦ AB không là phụ thuộc hàm thừa  Xét BA ◦ B+F-{BA} = BCA ◦ BA là phụ thuộc hàm thừa không chứa VP có chứa VP 58  Thuật toán tìm phủ tối thiểu Vào: Tập thuộc tính U, F = {Li  Ri : i = 1..n} Ra: Phủ tối thiểu F của tập phụ thuộc hàm F  Thuật toán B.1. Tách các PTH có VP lớn hơn một thuộc tính thành các PTH có VP một thuộc tính B.2. Loại bỏ khỏi F các thuộc tính thừa trong vế trái của các phụ thuộc hàm B.3. Loại bỏ khỏi F các phụ thuộc hàm dư thừa 59  VD 1: Cho F= {AC, BC, C D, DEC, CAB} ◦ Tìm phủ tối thiểu của F?  VD 2: Cho F= {AB→C, D→EG, C→A, BE→C, BC→D, CG→ BD, ACD→B, CE→AG } ◦ Tìm phủ tối thiểu của F? 60  Nhắc lại ◦ Khóa • Là một tập các thuộc tính dùng để xác định tính duy nhất của mỗi bộ trong quan hệ  Các bộ trong quan hệ khác nhau từng đôi một ◦ Gồm • Siêu khóa • Khóa • Khóa chính DHTM_TMU sds 16 61  B1: Xác định tất cả các tập con khác rỗng của U. Kết quả tìm được giả sử là các tập thuộc tính Xi, i= 1..n  B2: Tìm bao đóng của các Xi  B3: Xác định tập siêu khóa Si (i=1..m) là các Xi có bao đóng đúng bằng U  B4: Xây dựng tập chứa tất cả các khóa của U từ tập S bằng cách xét mọi Si, Sj là con của S (i ≠j), nếu Si  Sj thì loại Sj (i,j=1..n). Tập S còn lại là các khóa của lược đồ 62  Tìm tất cả các khóa của lược đồ quan hệ và tập PTH sau: R(U)=CSZ; F = {f1:CS → Z; f2:Z → C} Xi Xi+ Siêu khóa Khóa C C S S Z ZC CS CSZ CS CS CZ CZ SZ SCZ SZ SZ CSZ CSZ CSZ Vậy lược đồ quan hệ R có 2 khóa là CS và SZ 63  Một số khái niệm: ◦ Tập nguồn (TN) chứa tất cả các thuộc tính có xuất hiện ở vế trái và không xuất hiện ở vế phải của tập phụ thuộc hàm. Những thuộc tính không tham gia vào bất kỳ một phụ thuộc hàm nào thì cũng đưa vào tập nguồn. ◦ Tập đích (TD) chứa tất cả các thuộc tính có xuất hiện ở vế phải và không xuất hiện ở vế trái của tập phụ thuộc hàm. ◦ Tập trung gian (TG) chứa tất cả các thuộc tính vừa tham gia vào vế trái vừa tham gia vào vế phải.  Hệ quả: nếu K là khóa của R thì TN  K và TD ∩ K = ∅ 64 DHTM_TMU sds 17 65  Giải lại VD trên  Ta có: TN={S}, TG={C,Z}  Gọi Xi là các tập con của TG Xi (TN  Xi) (TN  Xi)+ Siêu khóa Khóa Ø S S C CS CSZ CS CS Z SZ CSZ SZ SZ CZ CSZ CSZ CSZ Vậy lược đồ quan hệ R có 2 khóa là CS và SZ 66  2.4.1. Dạng chuẩn 1 (1NF - First Normal Form)  2.4.2. Dạng chuẩn 2 (2NF - Second Normal Form)  2.4.2. Dạng chuẩn 3 (3NF - Third Normal Form)  2.4.4. Dạng chuẩn Boye-Codd (BCNF-Boye-Codd Normal Form)  2.4.5. Phương pháp xác định dạng chuẩn cao nhất. 67  Định nghĩa 1: • Cho R(U) là một lược đồ quan hệ với U là tập các thuộc tính, F là tập các phụ thuộc hàm trên R và AU • Chúng ta nói: A là thuộc tính khóa (prime) nếu A thuộc một khóa tối thiểu nào đó của R. Ngược lại A được gọi là thuộc tính không khóa (nonprime).  Ví dụ: R(ABCD) F= {AB  C , BC  A,B  D}  Lược đồ trên có 2 khóa tối thiểu AB, BC  A, B, C : thuộc tính khóa; D : thuộc tính không khóa 68  Định nghĩa 2: • Cho R(U) là một lược đồ quan hệ với U là tập các thuộc tính, F là tập các phụ thuộc hàm trên R và X  U, AU • Chúng ta nói: A là phụ thuộc bắc cầu vào X nếu tồn tại một tập thuộc tính Y, Y  U sao cho X  Y, Y  A thuộc F+ nhưng Y  X không thuộc F+ . Ngược lại A không phụ thuộc bắc cầu vào X hay A phụ thuộc trực tiếp vào X DHTM_TMU sds 18 69  Định nghĩa : Dạng chuẩn là tập các tiêu chuẩn để đánh giá độ tốt & xấu (của một lược đồ Quan hệ).  Phân loại : Có 4 mức dạng chuẩn : 1. Dạng chuẩn 1 2. Dạng chuẩn 2 3. Dạng chuẩn 3 4. Dạng chuẩn Boyce-Codd (BC) 5. Dạng chuẩn 4, Dạng chuẩn 5 : không được đề cập trong bài giảng này 70 2.4.1 Dạng chuẩn 1 (1NF)  Định nghĩa : • Một sơ đồ quan hệ R được gọi là ở dạng chuẩn 1 nếu tất cả các miền giá trị của các thuộc tính trong R đều chỉ chứa giá trị nguyên tố • Giá trị nguyên tố là giá trị mà không thể chia nhỏ ra được nữa  Ví dụ: Quan hệ không ở 1NF và quan hệ sau khi chuẩn hóa về 1NF 71  Không là 1NF 72 Thuộc tính PRODUCT là đa thuộc tính Bộ 1 có ITEM, PRICE không nguyên tố DHTM_TMU sds 19 73 Quan hệ đạt 1NF nếu toàn bộ các thuộc tính của mọi bộ đều mang giá trị đơn (giá trị nguyên tố). Biến đổi thành các giá trị nguyên tố 74  Vẫn còn trùng lặp thông tin 75 2.4.2 Dạng chuẩn 2 (2NF) Định nghĩa : • Một sơ đồ quan hệ R được coi là ở dạng chuẩn 2 nếu sơ đồ quan hệ này ở 1NF • Tất cả các thuộc tính không khóa đều phụ thuộc hàm đầy đủ vào khóa chính. Phụ thuộc hàm đầy đủ • Cho lược đồ quan hệ R(U), F là tập phụ thuộc hàm trên R. X, Y ⊆ U. Y được gọi là phụ thuộc đầy đủ vào X nếu: - X  Y thuộc F+ - ! ∃ X’⊂ X : X’ ⟶ Y F+ • Các phụ thuộc hàm không đầy đủ còn gọi là phụ thuộc bộ phận. 76  SUPPLIERS(S#,SNAME,ADD,ITEM,PRICE) kí hiệu ngắn gọn SUPPLIERS(S,N,A,I,P)  Tập PTH: F = {SNA,SIP}  Khóa tối thiểu duy nhất là: SI  Ta thấy: o Thuộc tính khóa: S,I o Thuộc tính không khóa: N,A,P  Xét thuộc tính không khóa A 1. A chỉ phụ thuộc vào S 2. S ⊂ SI  A không phụ thuộc đầy đủ vào khóa chính SI  SUPPLIERS không thuộc 2NF DHTM_TMU sds 20 77  Lược bỏ những PTH không đầy đủ  Quan hệ đạt 2NF - Đạt 1NF - Các thuộc tính không khóa phụ thuộc đầy đủ vào thuộc tính khóa 78 Tách SUPPLIERS thành S1 và S2 ◦ S1 (S, N, A) và F1={SNA} ◦ S2 (S, I, P) và F2={SIP} Kiểm tra S1, S2 có thuộc 2NF? ◦ Khóa của S1 là S ◦ Khóa của S2 là SI S1, S2 đạt chuẩn 2 79 Vào: lược đồ quan hệ R, tập phụ thuộc hàm F Ra: khẳng định R đạt 2NF hay không đạt 2NF.  Bước 1: Tìm tất cả khóa của R  Bước 2: Với mỗi khóa K, tìm bao đóng của tất cả tập con thật sự S của K.  Bước 3: Nếu có bao đóng S+ chứa thuộc tính không khóa thì R không đạt 2NF, ngược lại thì R đạt 2NF 80 Cho lược đồ quan hệ Q(A,B,C,D) Tập PTH F={ABC; BD; BCA}. Hỏi Q có đạt 2NF không? Giải: TN={B}, TG={AC} Khóa là K1=AB và K2=BC. Ta thấy B  K1, BD, D là thuộc tính không khóa  thuộc tính không khóa không phụ thuộc đầy đủ vào khóa  Q không đạt chuẩn 2. Xi (TN  Xi) (TN  Xi) + Siêu khóa Khóa  B BD A AB ABCD AB AB C BC ABCD BC BC AC ABC ABCD ABC DHTM_TMU sds 21 81  Vẫn còn trùng lặp thông tin phòngID PG04 PG16 PG36 phòngĐChỉ 6 NVC Q5 5 NT Q10 2 NTMK Q3 Giá 350 450 375 chủID CO40 CO93 CO93 chủTên X Y Y Phòng 82 2.4.3 Dạng chuẩn 3 (3NF)  Định nghĩa: Một sơ đồ quan hệ R được coi là ở dạng chuẩn 3 nếu  Sơ đồ quan hệ này đã ở dạng 2NF  Mọi thuộc tính không khóa đều không phụ thuộc bắc cầu vào khóa chính  Ví dụ: ItemInfo(item, price, discount). F = {item ⟶ price, price ⟶ discount} thuộc tính không khóa discount phụ thuộc bắc cầu vào khóa chính item. Vậy quan hệ này không ở dạng 3NF.  Chuẩn hoá ItemInfo(item, price) Discount(price, discount) Các dạng chuẩn đối với lược đồ quan hệ 83 S1(S, N, A) và F1={SNA} ◦ Khóa chính S ◦ Thuộc tính không khóa: N,A ◦ SNA  N,A không phụ thuộc bắc cầu vào S S2 (S, I, P) và F2={SIP} ◦ Khóa chính: SI ◦ Thuộc tính không khóa: P ◦ SI  P  P không phụ thuộc bắc cầu vào SI S1, S2 đạt chuẩn 3 84 Xét R (SIDM) trong đó: ◦ S: kí hiệu cho cửa hàng (Store) ◦ I: kí hiệu cho mặt hàng (Item) ◦ D: kí hiệu cho gian hàng (Department) ◦ M: kí hiệu cho người quản lý (Manager) ◦ Có PTH: SI D và SD M R (SIDM) và F={SID, SDM} ◦ Khóa tối thiểu: SI ◦ Thuộc tính không khóa: D, M ◦ R đạt chuẩn 2 ◦ Do: SID nên SI SD và SDM Vậy ta có: SIM R không đạt chuẩn 3 DHTM_TMU sds 22 85  Lược bỏ những PTH bắc cầu  Quan hệ đạt 3NF - Đạt 2NF - Các thuộc tính không khóa không phụ thuộc bắc cầu vào thuộc tính khóa 86  Tách R thành R1 và R2 với ◦ R1(SID) và F1={SID} ◦ R2(SDM) và F2={SDM} 87  R là lược đồ quan hệ  F là tập các PTH có vế phải một thuộc tính.  R đạt 3NF nếu và chỉ nếu mọi PTH XA  F với AX đều có: ◦ Hoặc X là siêu khóa. ◦ Hoặc A là thuộc tính khóa. 88 Vào: lược đồ quan hệ R, tập PTH F Ra: khẳng định R đạt 3NF hay không đạt 3NF.  Bước 1: Tìm tất cả khóa của R  Bước 2: Từ F tạo tập PTH tương đương F1tt có vế phải một thuộc tính.  Bước 3: Nếu mọi PTH X  A  F1tt với AX đều có X là siêu khóa hoặc A là thuộc tính khoá thì R đạt 3NF ngược lại R không đạt 3NF DHTM_TMU sds 23 89 Cho lược đồ quan hệ Q(A,B,C,D) F={ABC; DB; CABD} Hỏi R có đạt chuẩn 3 không? Giải: TN =  TG = {ABCD} 90 91 Xét các khóa  K1 = {AB}  K2 = {AD}  K3={C} Ta thấy mọi phụ thuộc hàm X  A  F đều có A là thuộc tính khóa. Vậy Q đạt 3NF F={ABC; DB; CABD} 92  Vẫn còn trùng lặp thông tin kháchID phòngID PG04 PG16 CR56 PG04 CR56 PG36 ngàyThuê 1/6/03 1/9/04 1/9/02 10/10/03 ngàyTrả 31/8/04 1/9/05 10/6/03 1/12/04 CR76 CR76 Thuê nhânviênID SG37 SG24 SG37 SG05 nhânviênID  phòngID DHTM_TMU sds 24 93 2.4.4 Dạng chuẩn Boye – Codd (BCNF)  Định nghĩa: Một sơ đồ quan hệ R được coi là ở dạng chuẩn BCNF nếu:  Sơ đồ quan hệ này đã ở dạng 3NF  Các PTH không hiển nhiên có VT chứa khóa  Hay nói cách khác: Một sơ đồ quan hệ R được coi là ở dạng chuẩn BCNF nếu với mọi XA thuộc F+ và AX thì X chứa một khóa của R Các dạng chuẩn đối với lược đồ quan hệ 94  Lược đồ R (CSZ)  Tập PTH F={CSZ,ZC}  Khóa tối thiểu: CS, CZ  Xét ZC do Z không là khóa  R không đạt chuẩn BCNF Xét lược đồ R (CSZ) 95  Lược bỏ những PTH có VT không chứa khóa  Quan hệ đạt BCNF ◦ Đạt 3NF ◦ Các PTH không hiển nhiên có VT chứa khóa (hay VT là siêu khóa) 96  Phân tách R thành 2 lược đồ con  R1 (CZ) ◦ Khóa: CZ ◦ R1 đạt chuẩn BCNF  R2 (SZ) ◦ Khóa là SZ ◦ R2 đạt chuẩn BCNF DHTM_TMU sds 25 97 Vào: lược đồ quan hệ R, tập phụ thuộc hàm F Ra: khẳng định R đạt BCNF hay không đạt BCNF.  Bước 1: Tìm tất cả khóa của R  Bước 2: Từ F tạo tập PTH tương đương F1tt có vế phải một thuộc tính  Bước 3: Nếu mọi phụ thuộc hàm X  A  F1tt với AX đều có X là siêu khóa thì R đạt BCNF ngược lại R không đạt BCNF 98 S1(S, N, A) và F1={SNA} ◦ Khóa chính S ◦ SNA ◦  S1 đạt chuẩn BCNF S2 (S, I, P) và F2={SIP} ◦ Khóa chính: SI ◦ SI  P ◦  S2 đạt chuẩn BCNF Vậy S1, S2 đạt chuẩn BCNF 99 Cho lược đồ quan hệ Q(A,B,C,D,E,I) Tập PTH F={ACDEBI;CEAD}. Hỏi Q có đạt chuẩn BC không? Giải: TN={C} TG={ADE} F = F1tt = {ACDE, ACDB, ACDI, CEA, CED} Mọi PTH của F1tt đều có vế trái là siêu khóa  Q đạt dạng BCNF 100 Vào: lược đồ quan hệ R, tập PTH hàm F Ra: khẳng định R đạt chuẩn gì?  Bước 1: Tìm tất cả khóa của R  Bước 2: Kiểm tra BCNF nếu đúng thì R đạt BCNF, kết thúc thuật toán. Ngược lại qua bước 2.  Bước 3: Kiểm tra 3NF nếu đúng thì R đạt 3NF, kết thúc thuật toán. Ngược lại qua bước 4.  Bước 4: Kiểm tra 2NF nếu đúng thì R đạt 2NF, kết thúc thuật toán. Ngược lại R đạt 1NF. DHTM_TMU sds 26 101  Chuẩn 2NF ◦ Giản ước sự dư thừa dữ liệu ◦ Tránh dị thường khi cập nhật dữ liệu do sự dư thừa dữ liệu gây ra  Chuẩn 3NF ◦ Tránh sự dị thường khi thêm và xóa các bộ trong quan hệ  Chuẩn BCNF ◦ Tránh sự dị thường khi thêm và xóa các bộ trong quan hệ ◦ Chặt chẽ và mạnh hơn chuẩn 3 102 103  2.5.1. Phép tách lược đồ quan hệ  2.5.2. Thuật toán kiểm tra phép tách - kết nối bảo toàn thông tin  2.5.2. Phép tách - kết nối bảo toàn thông tin ◦ Tách không mất mát về chuẩn BCNF ◦ Tách bảo toàn PTH về chuẩn 3 104  Khái niệm ◦ Phép tách các lược đồ quan hệ là quá trình phân tách các lược đồ quan hệ R thành các lược đồ con nhỏ hơn dựa trên một quy tắc cho trước ◦ Hay nói cách khác:Thay thế một lược đồ quan hệ R(A1,A2,,An) bằng một tập các lược đồ con {R1,R2,,Rk } trong đó Ri ⊆ R và R= R1 ∪ R2 ∪ . ∪ Rk .  Mục đích ◦ Sử dụng phép tách để đưa các lược đồ về các dạng chuẩn ◦ Loại bỏ dư thừa và hạn chế dị thường ◦ Tăng hiệu quả lưu trữ, tìm kiếm, truy vấn  Yêu cầu phép tách ◦ Bảo toàn thuộc tính, ràng buộc ◦ Bảo toàn dữ liệu DHTM_TMU sds 27 105  * : là phép kết nối tự nhiên trên giao của 2 tập thuộc tính  ρ=(R1,R2,,Rk) hay ρ=(U1,U2,,Uk) là phép tách lược đồ trên U thành các lược đồ con trên U1,U2,,Uk  ri=Ui(r) là hình chiếu của quan hệ r lên tập thuộc tính Ui  mρ(r)=r1*r2**rk là phép kết nối tự nhiên của các hình chiếu của r lên các tập con trong phép tách ρ 106  Ví dụ phép kết D E 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 R B<D S 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2 A B C D E 107  Ví dụ phép kết tự nhiên R S C D 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 A B 1 2 C 3 4 5 6 D 1 2 108  Được dùng để lấy ra một vài cột của quan hệ R  Ký hiệu  Kết quả trả về là một quan hệ ◦ Có k thuộc tính ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R  Ví dụ A1, A2, , Ak(R) A B  R   10 20 30 C 1 1 1  40 2 A,C (R) A   C 1 1  2 A,C (R) DHTM_TMU sds 28 109 NHANVIEN HONV TENLOT TENNV MAN V NGSINH DCHI PHA I LUON G MA_N QL PHG Đinh Bá Tiến 009 119 Cống Quỳnh, Tp HCM Na m 30000 005 5 Nguyễn Thanh Tùng 005 222 Nguyễn Văn Cừ, Tp HCM Na m 40000 006 5 Bùi Ngọc Hằng 007 332 Nguyễn Thái Học, Tp HCM Na m 25000 001 4 Lê Quỳnh Như 001 291 Hồ Văn Huê, Tp HCM Nữ 43000 006 4 Nguyễn Mạnh Hùng 004 95 Bà Rịa, Vũng Tàu Na m 38000 005 5 Trần Thanh Tâm 003 34 Mai Thị Lự, Tp HCM Na m 25000 005 5 Trần Hồng Quang 008 80 Lê Hồng Phong, Tp HCM Na m 25000 001 4 Phạm Văn Vinh 006 45 Trưng Vương, Hà Nội Nữ 55000 1 HONV, TENLOT, TENNV, LUONG(NHANVIEN) HONV TENLOT TENNV LUON G Đinh Bá Tiến Nguyễn Thanh Tùng Bùi Ngọc Hằng Lê Quỳnh Như Nguyễn Mạnh Hùng Trần Thanh Tâm Trần Hồng Quang Phạm Văn Vinh 30000 40000 25000 43000 38000 25000 25000 55000 110  Tách không mất mát (Lossless join)  Tách bảo toàn tập PTH 111  Định nghĩa: Cho lược đồ quan hệ R(U), phép tách R thành các lược đồ con {R1, R2 , , Rk} được gọi là phép tách không mất mát thông tin đối với một tập phụ thuộc hàm F nếu với mọi quan hệ r xác định trên R thỏa mãn F thì: r= ∏R1 (r) ⋈ ∏R2 (r) ⋈ . ⋈ ∏Rk (r)  Phép tách một lược đồ quan hệ thành các lược đồ con mà sau khi kết nối tự nhiên các lược đồ con này thì ta thu được lược đồ ban đầu  Ví dụ: Tách SUPPLIERS thành S1 và S2 ◦ S1 (S, N, A) và F1={SNA} ◦ S2 (S, I, P) và F2={SIP} Phép tách - kết nối bảo toàn thông tin 112 Xác định bài toán  Vào: R(A1, A2, , An), F, phép tách {R1, R2, , Rk}  Ra: phép tách là mất mát thông tin hay không? Thuật toán B.1. Xây dựng một bảng k hàng, n cột (k đại diện cho Ri, n đại diện cho Ai) o Nếu Aj là thuộc tính của Ri thì điền aj vào ô (i,j). o Nếu không thì điền bij. B.i. Xét lần lượt các PTH f = X⟶Y  F, áp dụng cho bảng theo cách sau: o Nếu 2 hàng t1, t2 thuộc bảng: t1[X] = t2[X] thì làm bằng nhau trên các giá trị Y: t1[Y] = t2[Y] như sau: o Nếu có giá trị một hàng thuộc Y là aj thì các giá trị khác thuộc Y cũng gán bằng aj o Nếu không gán bằng một trong các giá trị bị o Lặp cho tới khi không thể thay đổi được giá trị nào trong bảng. B.n. Nếu bảng có 1 hàng gồm các kí hiệu a1, a2, , an thì phép tách là không mất mát thông tin. Ngược lại, phép tách không bảo toàn thông tin. D TM_TMU sds 29 113  R (SNAIP) => R1(SNA) và R2(SIP)  Tập F={SNA,SIP}  Thực hiện thuật toán: ◦ Bước 1  Ta có: k=2 và n=5 114  Bước 2  Xét SNA và cột S  Tại S có 2 hàng  b22=a2, b23=a3 115  Bước 3 Kết luận: ◦ Phép tách R thành R1 và R2 là không mất mát thông tin 116  Khái niệm ◦ Là phép tách mà các PTH ban đầu có thể suy diễn được từ các PTH của các lược đồ con ◦ Các PTH ở lược đồ con không cần thuộc F  Mục đích ◦ Bảo đảm các ràng buộc toàn vẹn đối với lược đồ quan hệ DHTM_TMU sds 30 117  Hình chiếu của tập phụ thuộc hàm Cho sơ đồ quan hệ R, tập phụ thuộc hàm F, phép tách {R1, R2, , Rk} của R trên F. Hình chiếu Fi của F trên Ri là tập tất cả X ⟶ Y  F+ : XY ⊆ Ri .  Phép tách sơ đồ quan hệ R thành {R1, R2, , Rk} là một phép tách bảo toàn tập phụ thuộc hàm F nếu (F1 ∪ F2 ∪ ∪ Fk) + = F+  Hay hợp của tất cả các phụ thuộc hàm trong các hình chiếu của F lên các sơ đồ con sẽ suy diễn ra các phụ thuộc hàm trong F. Tách bảo toàn PTH (tt) 118  Xét phép tách lược đồ R(CSZ) thành R1(SZ) và R2(ZC)  Tập F={CSZ, ZC}  Ta thấy: ◦ Với R1 chỉ có các PTH tầm thường ◦ Với R2 có ZC ◦ Cả R1 và R2 không có PTH nào có thể suy ra CSZ  Phép tách này không bảo toàn tập PTH 119  Xét phép tách lược đồ R(SNAIP) thành R1(SNA) và R2(SIP)  Tập F={SNA, SIP}  Ta thấy: ◦ Với R1 ngoài các PTH tầm thường còn có S NA ◦ Với R2 ngoài các PTH tầm thường còn có SI P  Phép tách trên là bảo toàn tập PTH 120  Một phép tách có thể ◦ Không mất mát thông tin và có bảo toàn tập phụ thuộc hàm  ((S, N,A,I,P)(S,N,A) và (S, I, P)) ◦ Không mất mát thông tin và không bảo toàn tập phụ  ((C,S,Z) (S,Z) và (Z,C)) ◦ Mất mát thông tin và có bảo toàn tập phụ thuộc hàm ◦ Mất mát thông tin và không bảo toàn tập phụ thuộc hàm DHTM_TMU sds 31 121  Tách không mất mát thông tin về chuẩn BCNF  Tách bảo toàn tập PTH về 3NF 122  Thuật toán ◦ Vào  Lược đồ R  Tập F trên R ◦ Ra  P phép tách không mất mát R thành (R1, R2, .., Rk)  Các Ri đều thỏa chuẩn BCNF  Các Fi là hình chiếu của F lên Ri 123  Lặp ◦ Đặt p=(R) ◦ Nếu S là một lược đồ trong p không thỏa BCNF  Xét XA của S. Trong đó:  X không chứa khóa của S  A không phải là con của X  Thay S=S1 và S2 với  S1= A ∪{X}  S2={S}\A ◦ Lặp cho đến khi mọi Si đều thỏa chuẩn BC 124  Vào: Cho R(CSZ) và F={CSZ, ZC}  Thực hiện: ◦ p=R, khóa của R là CS ◦ Xét ZC. Z không là khóa của R và C không là con của Z  S1=Z∪C =ZC  S2={R}\C=CSZ\C=SZ  Xét S1 và S2  S1: Có khóa là Z, phụ thuộc hàm ZZC (thỏa BCNF)  S2: Có khóa là SZ, phụ thuộc hàm SZ SZ (thỏa BCNF) ◦ Kết thúc: p=(S1,S2) DHTM_TMU sds 32 125  Vào: Cho R(SNAIP) và F={SNA, SIP}  Thực hiện: ◦ p=R, khóa của R là SI ◦ Xét SNA. S không là khóa của R và N, A không là con của S  S1=S ∪ {N,A} =SNA  S2={R}\NA=SNAIP\NA=SIP  Xét S1 và S2  S1: Có khóa là S, phụ thuộc hàm SNA (thỏa BCNF)  S2: Có khóa là SI, phụ thuộc hàm SI P (thỏa BCNF) ◦ Kết thúc: p=(S1,S2) 126  Thuật toán ◦ Vào  Lược đồ R  Tập F trên R (F là phủ tối thiểu) ◦ Ra  Phép tách R thành (R1, R2, .., Rk)  Bảo toàn F 127  B1: Xét các thuộc tính trên R ◦ Nếu có các Ai không xuất hiện trong F ◦ Tách R=R1 và R2  R1={Ai} và R2=R\R1  B2: Xét các phụ thuộc hàm ◦ Nếu có Fi chứa tất cả các thuộc tính của R  Kết luận: p=R, kết thúc ◦ Ngược lại:  Mỗi XA =>Ri={X}∪{A}  Nếu có XA1, ,XAi =>Ri={X}∪{A1,..,Ai} 128  Vào: Cho R(SNAIP) và F={SNA, SIP}  Thực hiện: ◦ F là phủ tối thiểu ◦ Xét các thuộc tính S, N, A, I, P  Không có thuộc tính nào không xuất hiện trong F ◦ Xét các F  Không có F nào chứa toàn bộ S, N,A,I,P  Xét SNA=> R1={A,N,A} (Thỏa chuẩn 3)  Xét SIP => R2={S,I,P} (Thỏa chuẩn 3) ◦ Kết thúc DHTM_TMU sds 33 129  Vào: ◦ Cho R(ABCDEG) ◦ F = { ABC, CA,BCD, ACDB, DEG, BEC, CGBD, CE AG}  Thực hiện: ◦ F chưa phải là phủ tối thiểu nên không áp dụng trực tiếp thuật toán ◦ F‟={AB→C, D→E, CE → G, C→A, D→ G, BC→ D, BE→C, CG→D} ◦ Xét các F  Không có F nào chứa toàn bộ R(ABCDEG)  Áp dụng thuật toán ta thu được p={ABC,DEG,CEG,CA,BCD,BEC,CGD}  Do CA là con của ABC nên loại CA khỏi p ◦ Kết thúc p= {ABC,DEG,CEG,BCD,BEC,CGD} 130 1. Cho phép tách R(CSZ) thành R1(SZ) và R2(CZ) và tập PTH F={CSZ, ZC} Kiểm tra các phép tách sau đây xem có mất mát thông tin hay không? 2. Cho R(ABCDE), R1(AD), R2(AB), R3(BE), R4(CDE), R5(AE) và F={AC, BC, CD, DEC, CEA} Tách các lược đồ quan hệ  Cho lược đồ R(CTHRSG) và F={CT, HRC, THR, CSG, HSR}  Tách lược đồ trên để đạt dạng chuẩn BCNF? 131  Mô hình hoá thế giới thực  Sơ đồ thực thể - liên kết (sơ đồ ER)  Biến đổi sơ đồ thực thể - liên kết sang mô hình quan hệ 132 Ý tưởng thiết kế E/R Lược đồ quan hệ HQT CSDL quan hệ DHTM_TMU sds 34 133 Phụ thuộc HQT cụ thể Độc lập HQT Thế giới thực Phân tích yêu cầu TK 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 Lược đồ logic Lược đồ trong Chương trình ứng dụng Thiết kế chương trình ứng dụng Phân tích chức năng Các yêu cầu về chức năng Các đặc tả chức năng 134  Quá trình thiết kế CSDL  Mô hình thực thể - liên kết ◦ Thực thể ◦ Thuộc tính ◦ Ràng buộc trên kiểu liên kết ◦ Lược đồ thực thể - liên kết ◦ Thực thể yếu  Thiết kế  Ví dụ 135  Quá trình thiết kế CSDL  Mô hình thực thể - liên kết  Thiết kế ◦ Các bước thiết kế ◦ Nguyên lý thiết kế  Ví dụ 136 DHTM_TMU sds 35 137 138 139  Xác định tập thực thể  Xác định mối quan hệ  Xác định thuộc tính và gắn thuộc tính cho tập thực thể và mối quan hệ  Quyết định miền giá trị cho thuộc tính  Quyết định thuộc tính khóa  Quyết định (min, max) cho mối quan hệ 140  Chính xác  Tránh trùng lặp  Dễ hiểu  Chọn đúng mối quan hệ  Chọn đúng kiểu thuộc tính DHTM_TMU sds 36 141  Quá trình thiết kế CSDL  Mô hình thực thể - liên kết  Thiết kế  Ví dụ ◦ Quản lý đề án công ty 142  CSDL đề án công ty theo dõi các thông tin liên quan đến nhân viên, phòng ban và đề án ◦ Cty có nhiều đơn vị, mỗi đơn vị có tên duy nhất, mã đơn vị duy nhất, một trưởng phòng và ngày nhận chức. Mỗi đơn vị có thể ở nhiều địa điểm khác nhau. ◦ Dự án có tên duy nhất, mã duy nhất, do 1 một phòng ban chủ trì và được triển khai ở 1 địa điểm. ◦ Nhân viên có mã số, tên, địa chỉ, ngày sinh, giới tính và lương. Mỗi nhân viên làm việc ở 1 phòng ban, tham gia vào các đề án với số giờ làm việc khác nhau. Mỗi nhân viên đều có một người quản lý trực tiếp. ◦ Một nhân viên có thể có những người con được hưởng bảo hiểm theo nhân viên. Mỗi người con của nhân viên có tên, giới tính, ngày sinh. 143 1 1 N 1 1 N 1 N M N 1 N Số nhân viên 144  Chuyển lược đồ E/R sang thiết kế quan hệ DHTM_TMU sds 37 145  (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 NHANVIEN TENNV NS DCHI GT LUON G HONV MANV Lam_viec La_truong_phon g PHONGBAN MAPHG TENPH G n 1 1 1 NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG) PHONGBAN(TENPHG, MAPHG) 146  (2) Mối quan hệ ◦ (2a) 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ệ NHANVIEN TENNV NS DCHI GT LUON G HONV MANV La_truong_pho ng PHONGBAN MAPHG TENPH G 1 1 NG_NHANCH UC PHONGBAN(MAPHG, TENPHG, MANV, NG_NHANCHUC) 147  (2) Mối quan hệ ◦ (2b) Một-Nhiều  Thêm vào quan-hệ-một thuộc tính khóa của quan-hệ-nhiều NHANVIEN TENNV NS DCHI GT LUON G HONV MANV Lam_viec PHONGBAN MAPHG TENPH G n 1 NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, MAPHG) 148  (2) Mối quan hệ ◦ (2c) 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à những thuộc tính khóa của các tập thực thể liên quan DEAN TENDA DDIEM_D A MADA NHANVIEN TENNV NGSIN H DCHI PHAI LUON G HONV MANV Phan_cong n n THOIGIA N PHANCONG(MANV, MADA, THOIGIAN) DHTM_TMU sds 38 149  (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ệ liên quan NHANVIEN TENNV NS DCHI GT LUON G HONV MANV THANNHAN(MANV, TENTN, GT, NS, QUANHE) THANNHAN TENTN GT NS QUANH E Co_than_nha n n 1 150  (4) Thuộc tính đa trị ◦ Chuyển thành một quan hệ  Có cùng tên với thuộc tính đa trị  Thuộc tính khóa của quan hệ này là khóa ngoài của quan hệ chứa thuộc tính đa trị NHANVIEN TENNV NS BANGCA P GT LUON G HONV MANV BANGCAP(MANV, BANGCAP) NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, DCHI) 151  (5) Liên kết đa ngôi (n>2) ◦ Chuyển thành một quan hệ  Có cùng tên với tên mối liên kết đa ngôi  Khóa chính là tổ hợp các khóa của tập các thực thể tham gia liên kết NHACUNGCAP(MANCC ,) DEAN(MADA, ) THIETBI(MATB, ) NHACUNGCA P MANC C Cung_cap DEAN MADA TENDA THIETBI MATB SOLUON G CUNGCAP(MANCC, MATB, MADA, SOLUONG) 152 3.1. Đại số quan hệ 3.2. Ngôn ngữ truy vấn có cấu trúc SQL DHTM_TMU sds 39 153  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ Nội dung chi tiết 154  Là loại ngôn ngữ thủ tục  Bao gồm tập hợp các phép toán được áp dụng trên các thể hiện của quan hệ. Kết quả trả về của một câu truy vấn là một thể hiện của quan hệ  Chuỗi các phép toán đạisố quan hệ hình thành nên biểu thức đại số quan hệ (câu truy vấn) mà kết quả của nó cũng trả về một thể hiện của quan hệ  Có 3 nhóm phép toán ◦ Phép toán tập hợp (∪, ∩, −, x, ÷) ◦ Phép toán quan hệ (π, σ, ) ◦ Phép toán khác (←, ρ) 155  Biến là các quan hệ ◦ Tập hợp (set)  Toán tử là các phép toán (operations) ◦ Trên tập hợp • Hội  (union) • Giao  (intersec) • Trừ  (difference) ◦ Rút trích 1 phần của quan hệ • Chọn  (selection) • Chiếu  (projection) ◦ Kết hợp các quan hệ • Tích Cartesian  (Cartesian product) • Kết (join) 156  Hằng số là thể hiện của quan hệ  Biểu thức ◦ Được gọi là câu truy vấn ◦ Là chuỗi các phép toán đại số quan hệ ◦ Kết quả trả về là một thể hiện của quan hệ DHTM_TMU sds 40 157 PHONGBAN(MAPHG, TENPHG, MANV, NG_NHANCHUC) NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, PHG) PHANCONG(MANV, MADA, THOIGIAN) THANNHAN(MANV, TENTN, GT, NS, QUANHE) 158  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 159  Quan hệ là tập hợp các bộ ◦ Phép hội R  S ◦ Phép giao R  S ◦ Phép trừ R  S  Tính khả hợp (Union Compatibility) ◦ ĐN1: Hai lược đồ quan hệ R(A1, A2, , An) và S(B1, B2, , Bn) là khả hợp nếu • Cùng bậc n • Và có DOM(Ai)=DOM(Bi) , 1 i  n ◦ ĐN 2: Cho 2 quan hệ r và s, r và s được gọi là khả hợp nếu chúng được xác định trên cùng tập thuộc tính và các thuộc tính cùng tên có cùng miền giá trị  Kết quả của , , và  là một quan hệ có cùng tên thuộc tính với quan hệ đầu tiên (R) 160  Ví dụ TENNV NGSINH GT Tung 12/08/1955 Nam Hang 07/19/1968 Nu Nhu 06/20/1951 Nu Hung 09/15/1962 Nam NHAN_VIEN TENTN NG_SINH GTTN Trinh 04/05/1986 Nu Khang 10/25/1983 Nam Phuong 05/03/1958 Nu Minh 02/28/1942 Nam THAN_NHAN Chau 12/30/1988 Nu Bậc n=3 DOM(TENNV) = DOM(TENTN) DOM(NGSINH) = DOM(NG_SINH) DOM(GT) = DOM(GTTN) Hai quan hệ NHANVIEN và THANNHAN là khả hợp DHTM_TMU sds 41 161  Cho 2 quan hệ R và S khả hợp  Phép hội của R và S ◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R hoặc thuộc S, hoặc cả hai (các bộ trùng lặp sẽ bị bỏ)  Ví dụ R  S = { t  tR  tS } A B  R   1 2 1 A B  S  2 3 A B  R  S   1 2 1  3  2 162  Cho 2 quan hệ R và S khả hợp  Phép giao của R và S ◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc cả 2 quan hệ R và S  Ví dụ R  S = { t  tR  tS } A B  R   1 2 1 A B  S  2 3 R  S A B  2 163  Cho 2 quan hệ R và S khả hợp  Phép giao của R và S ◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R nhưng không thuộc S  Ví dụ R  S = { t  tR  tS } A B  R   1 2 1 A B  S  2 3 R - S A B  1  1 164  Giao hoán  Kết hợp R  S = S  R R  S = S  R R  (S  T) = (R  S)  T R  (S  T) = (R  S)  T DHTM_TMU sds 42 165  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 166  Được dùng để lọc ra một tập con các bộ của quan hệ R  Các bộ được chọn phải thỏa mãn điều kiện chọn F  Ký hiệu  F là biểu thức logic gồm các mệnh đề có dạng ◦ ◦ • gồm  ,  ,  ,  ,  ,  • Các mệnh đề được nối lại nhờ các phép  ,  ,   F (R) 167  Cho R là một quan hệ và F là một biểu thức logic trên các thuộc tính của R  Phép chọn trên quan hệ R với biểu thức chọn F ◦ Ký hiệu: F(R) ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R và thỏa F  Kết quả trả về là một quan hệ ◦ Có cùng danh sách thuộc tính với R ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R  Ví dụ  (A=B)(D>5) (R) A B  R   C 1 5 12  23 D 7 7 3 10     A B  C 1  23 D 7 10   F(R) = { t  tR  F(t) đúng } 168  Phép chọn có tính giao hoán  F1 ( F2 (R)) =  F2 ( F1 (R)) =  F1  F2 (R) DHTM_TMU sds 43 169  Cho biết các nhân viên ở phòng số 4 ◦ Quan hệ: NHANVIEN ◦ Thuộc tính: PHG ◦ Điều kiện: PHG=4  F (R)  PHG=4 (NHANVIEN) 170  Tìm các nhân viên có lương trên 25000 ở phòng 4 hoặc các nhân viên có lương trên 30000 ở phòng 5 ◦ Quan hệ: NHANVIEN ◦ Thuộc tính: LUONG, PHG ◦ Điều kiện: • LUONG>25000 và PHG=4 hoặc • LUONG>30000 và PHG=5  (LUONG>25000  PHONG=4)  (LUONG>30000  PHONG=5) (NHAN_VIEN) 171  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 172  Được dùng để lấy ra một vài cột của quan hệ R  Cho R là một quan hệ n ngôi xác định tren tập thuộc tính U={A1,A2, , An} và một tập con thuộc tính XU.  Phép chiếu của quan hệ R trên tập thuộc tính X ◦ Ký hiệu: X(R) ◦ Là một quan hệ gồm tập tất cả các bộ của R xác định trên tập thuộc tính X  Kết quả trả về là một quan hệ ◦ Có k thuộc tính ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R  Ví dụ X(R)={t[X]  tR} A C  1 A B  R   10 20 30 C 1 1 1  40 2 A,C (R)  1  2  1 DHTM_TMU sds 44 173  Phép chiếu không có tính giao hoán A1, A2, , An(A1, A2, , Am(R)) = A1, A2, , An (R) , với n  m X,Y (R) = X (Y (R)) 174  Cho biết họ tên và lương của các nhân viên ◦ Quan hệ: NHANVIEN ◦ Thuộc tính: HONV, TENNV, LUONG HONV, TENNV, LUONG(NHANVIEN) 175  Cho biết mã nhân viên có tham gia đề án hoặc có thân nhân ◦ Quan hệ: THANNHAN, DEAN ◦ Thuộc tính: MANV MANV(DEAN) MANV(THANNHAN) MANV(DEAN)  MANV(THANNHAN) 176  Cho biết mã nhân viên có người thân và có tham gia đề án ◦ Quan hệ: THANNHAN, DEAN ◦ Thuộc tính: MANV MANV(DEAN) MANV(THANNHAN) MANV(DEAN) MANV(THANNHAN) DHTM_TMU sds 45 177  Cho biết mã nhân viên không có thân nhân nào ◦ Quan hệ: NHANVIEN, THANNHAN ◦ Thuộc tính: MANV MANV(NHANVIEN) MANV(THANNHAN) MANV(NHANVIEN) - MANV(THANNHAN) 178  Mở rộng phép chiếu bằng cách cho phép sử dụng các phép toán số học trong danh sách thuộc tính  Ký hiệu F1, F2, , Fn (E) ◦ E là biểu thức ĐSQH ◦ F1, F2, , Fn là các biểu thức số học liên quan đến • Hằng số • Thuộc tính trong E 179  Cho biết họ tên của các nhân viên và lương của họ sau khi tăng 10% HONV, TENNV, LUONG*1.1 (NHANVIEN)  Ví dụ ◦ THETINDUNG(MATHE, TRIGIATHE, SOTIENSD) ◦ Cho biết số tiền còn lại trong mỗi thẻ MATHE, TRIGIATHE  SOTIENSD (THETINDUNG) 180  Được sử dụng để nhận lấy kết quả trả về của một phép toán ◦ Thường là kết quả trung gian trong chuỗi các phép toán  Ký hiệu   Ví dụ ◦ B1 ◦ B2 S  P (R) KQ  A1, A2, , Ak (S) DHTM_TMU sds 46 181  Được dùng để đổi tên ◦ Quan hệ ◦ Thuộc tính S(R) : Đổi tên quan hệ R thành S Xét quan hệ R(B, C, D) X, C, D (R) : Đổi tên thuộc tính B thành X Đổi tên quan hệ R thành S và thuộc tính B thành X S(X,C,D)(R) 182  Cho biết họ và tên nhân viên làm việc ở phòng số 4  C1:  C2: HONV, TENNV (PHG=4 (NHANVIEN)) NV_P4  PHG=4 (NHANVIEN) KQ  HONV, TENNV (NV_P4) KQ(HO, TEN)  HONV, TENNV (NV_P4) KQ(HO, TEN) (HONV, TENNV (NV_P4)) 183  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 184  Được dùng để kết hợp các bộ của các quan hệ lại với nhau  Cho quan hệ R, S tương ứng xác định trên tập thuộc tính {A1,A2,,An} và {B1,B2, , Bm} .  Phép tích Đề - các của 2 quan hệ R và S ◦ Ký hiệu: R x S ◦ Là một quan hệ gồm tập tất cả các (m+n) - bộ có n thành phần đầu tiên là một bộ thuộc R và m thành phần sau là một bộ thuộc S  Kết quả trả về là một quan hệ Q ◦ Mỗi bộ của Q là tổ hợp giữa 1 bộ trong R và 1 bộ trong S ◦ Nếu R có u bộ và S có v bộ thì Q sẽ có u  v bộ ◦ Nếu R có n thuộc tính và S có m thuộc tính thì Q sẽ có n + m thuộc tính (R+  S+   ) R  S={t  t=(a1,..an,b1,..bm)  (a1,..an)  R  (b1,..bm)  S } DHTM_TMU sds 47 185  Ví dụ A B  R  1 2 C D  S  10 10 E + +  20 -  10 - R  S A B   1 2  2  1  1  1  2  2 C D   10 10  10  10  20  10  20  10 E + + + + - - - - 186  Ví dụ A B  R  1 2 B C  S  10 10 D + +  20 -  10 - X (X,C,D) (S) R  S A B   1 2  2  1  1  1  2  2 X C   10 10  10  10  20  10  20  10 D + + + + - - - - 187  Ví dụ A B  R  1 2 B C  S  10 10 D + +  20 -  10 - Kí hiệu rõ ràng A R. B   1 2  2  1  1  1  2  2 S. B C   10 10  10  10  20  10  20  10 D + + + + - - - - R  S 188  Thông thường theo sau phép tích Cartesian là phép chọn R  S A R.B   1 2  2  1  1  1  2  2 S.B C   10 10  10  10  20  10  20  10 D + + + + - - - - A=S.B (R  S) A R.B  1  2  2 S.B C  10  10  20 D + + - DHTM_TMU sds 48 189  TRPHG=MANV (PB_NV) PB_NV  PHONGBAN x NHANVIEN  Với mỗi phòng ban, cho biết thông tin của người trưởng phòng ◦ Quan hệ: PHONG_BAN, NHAN_VIEN ◦ Thuộc tính: TRPHG, MAPHG, TENNV, HONV, TENPHG MAPHG TRPHG NG_NHANCH UC Nghien cuu 5 33344555 5 05/22/1988 Dieu hanh 4 98798798 7 01/01/1995 Quan ly 1 88866555 5 06/19/1981 TENNV HONV Tung Nguyen Hung Nguyen 33344555 5 98798798 7 88866555 5 MANV Vinh Pham 190  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết ◦ Kết tự nhiên (Natural join) ◦ Kết có điều kiện tổng quát (Theta join) ◦ Kết bằng (Equi join)  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 191  Được dùng để tổ hợp 2 bộ có liên quan từ 2 quan hệ thành 1 bộ  Ký hiệu R S ◦ R(A1, A2, , An) và S(B1, B2, , Bm)  Kết quả của phép kết là một quan hệ Q ◦ Có n + m thuộc tính Q(A1, A2, , An, B1, B2, , Bm) ◦ Mỗi bộ của Q là tổ hợp của 2 bộ trong R và S, thỏa mãn một số điều kiện kết nào đó • Có dạng Ai  Bj • Ai là thuộc tính của R, Bj là thuộc tính của S • Ai và Bj có cùng miền giá trị •  là phép so sánh , , , , ,  192  Phân loại ◦ Kết theta (theta join) là phép kết có điều kiện • Ký hiệu R C S • C gọi là điều kiện kết trên thuộc tính ◦ Kết bằng (equi join) khi C là điều kiện so sánh bằng ◦ Kết tự nhiên (natural join) • Ký hiệu R S hay R  S • R+  S+   • Kết quả của phép kết bằng bỏ bớt đi 1 cột giống nhau DHTM_TMU sds 49 193  Ví dụ phép kết theta D E 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 R B<D S 1 2 3 3 1 1 2 3 6 2 4 5 6 6 2 A B C D E R C S = C(R  S) 194  Ví dụ phép kết bằng D E 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 R C=D S A B 1 2 C 3 4 5 6 D 3 E 1 6 2 C D 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 R C=S.C S A B 1 2 C 3 4 5 6 S. C 3 D 1 6 2 S. C (S.C,D) S 195  Ví dụ phép kết tự nhiên R S C D 3 S 6 1 2 A B 1 R 4 2 5 C 3 6 7 8 9 A B 1 2 C 3 4 5 6 S. C 3 D 1 6 2 D 1 2 196  Với mỗi nhân viên, hãy cho biết thông tin của phòng ban mà họ đang làm việc ◦ Quan hệ: NHANVIEN, PHONGBAN ◦ Thuộc tính: MANV,TENNV,,PHG, TENPHG, DHTM_TMU sds 50 197  Tập các phép toán , , , ,  được gọi là tập đầy đủ các phép toán ĐSQH ◦ Nghĩa là các phép toán có thể được biểu diễn qua chúng ◦ Ví dụ • RS = RS  ((RS)  (SR)) • R CS = C(RS) 198  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 199  Được dùng để lấy ra một số bộ trong quan hệ R sao cho thỏa với tất cả các bộ trong quan hệ S  Ký hiệu R  S ◦ R(Z) và S(X) • Z là tập thuộc tính của R, X là tập thuộc tính của S • X  Z  Kết quả của phép chia là một quan hệ T(Y) ◦ Với Y=Z-X ◦ Có t là một bộ của T nếu với mọi bộ tSS, tồn tại bộ tRR thỏa 2 điều kiện • tR(Y) = t • tR(X) = tS(X) X Y T(Y ) S(X) R(Z) 200  Ví dụ A B   a a  a  a  a  a  a  a C D   a b  a  a  b  a  b  b E 1 3 1 1 1 1 1 1 R D E a S b 1 1 R  S A B C  a   a  DHTM_TMU sds 51 201  Cho biết mã nhân viên tham gia tất cả các đề án ◦ Quan hệ: PHANCONG, DEAN ◦ Thuộc tính: MANV 202  Cho biết mã nhân viên tham gia tất cả các đề án do phòng số 4 phụ trách ◦ Quan hệ: PHANCONG, DEAN ◦ Thuộc tính: MANV ◦ Điều kiện: PHG=4 203  Biểu diễn phép chia thông qua tập đầy đủ các phép toán ĐSQH Q1  Y (R) Q2  Q1  S Q3  Y(Q2  R) T  Q1  Q3 204  Cho một CSDL gồm 3 quan hệ: ◦ NhaCC(MaNCC,TenNCC,Tinhtrang,Diachi) ◦ Hanghoa(MaHH,TenHH,Mausac,Trongluong,Diachi) ◦ Cungcap(MaNCC,MaHH,Soluong)  Biểu diễn các câu hỏi sau bằng ĐSQH 1. Đưa ra danh sách các hàng hóa có màu đỏ? 2. Đưa ra MaNCC của các hãng cung ứng mặt hàng P1 hoặc P2? 3. Đưa ra MaNCC của các hãng cung ứng cả 2 mặt hàng P1 và P2? 4. Đưa ra MaNCC của các hãng cung ứng ít nhất một mặt hàng màu đỏ? 5. Đưa ra MaNCC của các hãng cung ứng tất cả các mặt hàng? DHTM_TMU sds 52 205  Biểu diễn các câu hỏi sau bằng ĐSQH 1. Đưa ra danh sách các hàng hóa có màu đỏ?  Mausac=“Đỏ”(Hanghoa) 2. Đưa ra MaNCC của các hãng cung ứng mặt hàng P1 hoặc P2?  MaNCC(MaHH=“P1” v MaHH=“p2” (Cungcap)) 3. Đưa ra MaNCC của các hãng cung ứng cả 2 mặt hàng P1 và P2?  MaNCC(MaHH=“P1”(Cungcap))MaNCC(MaHH=“p2” (Cungcap)) 4. Đưa ra MaNCC của các hãng cung ứng ít nhất một mặt hàng màu đỏ?  MaNCC(Cungcap * (Mausac=“Đỏ” (Hanghoa)) 5. Đưa ra MaNCC của các hãng cung ứng tất cả các mặt hàng?  MaNCC, MaHH(Cungcap)  MaHH(Hanghoa) 206  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác ◦ Hàm kết hợp (Aggregation function) ◦ Phép gom nhóm (Grouping) ◦ Phép kết ngoài (Outer join)  Các thao tác cập nhật trên quan hệ 207  Nhận vào tập hợp các giá trị  Trả về một giá trị đơn  Gồm ◦ AVG ◦ MIN ◦ MAX ◦ SUM ◦ COUNT 208  Ví dụ A B 1 R 3 2 4 1 1 2 2 SUM(B) = 10 AVG(A) = 1.5 MIN(A) = 1 MAX(B) = 4 COUNT(A) = 4 DHTM_TMU sds 53 209  Được dùng để phân chia quan hệ thành nhiều nhóm dựa trên điều kiện gom nhóm nào đó  Ký hiệu ◦ E là biểu thức ĐSQH ◦ G1, G2, , Gn là các thuộc tính gom nhóm ◦ F1, F2, , Fn là các hàm ◦ A1, A2, , An là các thuộc tính tính toán trong hàm F G1, G2, , GnF1(A1), F2(A2), , Fn(An)(E) 210  Ví dụ SUM(C)(R) SUM_C 27 A B  R  2 4   2 2 C 7 7 3 10 ASUM(C)(R) SUM_C 14 3 10 A    211  Mở rộng phép kết để tránh mất mát thông tin ◦ Thực hiện phép kết ◦ Lấy thêm các bộ không thỏa điều kiện kết  Có 3 hình thức ◦ Mở rộng bên trái ◦ Mở rộng bên phải ◦ Mở rộng 2 bên 212 DHTM_TMU sds 54 213  Cho biết họ tên nhân viên và tên phòng ban mà họ là trưởng phòng nếu có R1  NHANVIEN MANV=TRPHG PHONGBAN KQ  HONV,TENNV, TENPHG (R1) TENNV HONV TENPHG Tung Nguyen Nghien cuu Hang Bui null Nhu Le null Vinh Pham Quan ly 214  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ 215  Nội dung của CSDL có thể được cập nhật bằng các thao tác ◦ Thêm (insertion) ◦ Xóa (deletion) ◦ Sửa (updating)  Các thao tác cập nhật được diễn đạt thông qua phép toán gán Rnew  các phép toán trên Rold 216  Được diễn đạt ◦ R là quan hệ ◦ E là một biểu thức ĐSQH  Ví dụ ◦ Phân công nhân viên có mã 123456789 làm thêm đề án mã số 20 với số giờ là 10 Rnew  Rold  E PHANCONG  PHANCONG  (‘123456789’, 20, 10) DHTM_TMU sds 55 217  Được diễn đạt ◦ R là quan hệ ◦ E là một biểu thức ĐSQH  Ví dụ ◦ Xóa các phân công đề án của nhân viên 123456789 Rnew  Rold  E PHANCONG  PHANCONG  MANV=‘123456789’(PHANCONG) 218  Được diễn đạt ◦ R là quan hệ ◦ Fi là biểu thức tính toán cho ra giá trị mới của thuộc tính  Ví dụ ◦ Tăng thời gian làm việc cho tất cả nhân viên lên 1.5 lần Rnew  F1, F2, , Fn (Rold) PHANCONG MANV, SODA, THOIGIAN*1.5(PHANCONG) 219 3.1. Đại số quan hệ 3.2. Ngôn ngữ truy vấn có cấu trúc SQL 220  Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index) DHTM_TMU sds 56 221  Một DBMS phải có ngôn ngữ giao tiếp giữa NSD với CSDL, bao gồm:  Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL): cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc.  Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML): cho phép thêm, xóa, sửa dữ liệu.  Ngôn ngữ truy vấn dữ liệu (Structured Query Language – SQL): cho phép truy vấn dữ liệu.  Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thông tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu. 222  Ngôn ngữ ĐSQH ◦ Cách thức truy vấn dữ liệu ◦ Khó khăn cho người sử dụng  SQL (Structured Query Language) ◦ Ngôn ngữ cấp cao ◦ Người sử dụng chỉ cần đưa ra nội dung cần truy vấn ◦ Được phát triển bởi IBM (1970s) ◦ Được gọi là SEQUEL (Structured English Query Language) ◦ Được ANSI (American National Standard Institute) công nhận và phát triển thành chuẩn • SQL-86 • SQL-92 • SQL-99 223  SQL gồm ◦ Định nghĩa dữ liệu (DDL) ◦ Thao tác dữ liệu (DML) ◦ Định nghĩa khung nhìn ◦ Ràng buộc toàn vẹn ◦ Phân quyền và bảo mật ◦ Điều khiển giao tác  SQL sử dụng thuật ngữ ◦ Bảng ~ quan hệ ◦ Cột ~ thuộc tính ◦ Dòng ~ bộ 224  Giới thiệu  Định nghĩa dữ liệu (DDL) ◦ Kiểu dữ liệu ◦ Các lệnh định nghĩa dữ liệu  Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index) HTM_TMU sds 57 225  Là ngôn ngữ mô tả ◦ Lược đồ cho mỗi quan hệ ◦ Miền giá trị tương ứng của từng thuộc tính ◦ Ràng buộc toàn vẹn ◦ Chỉ mục trên mỗi quan hệ  Gồm ◦ CREATE TABLE (tạo bảng) ◦ DROP TABLE (xóa bảng) ◦ ALTER TABLE (sửa bảng) ◦ CREATE DOMAIN (tạo miền giá trị) ◦ CREATE DATABASE (tạo cơ sở dữ liệu) ◦ 226  Số (numeric) ◦ INTEGER ◦ SMALLINT ◦ NUMERIC, NUMERIC(p), NUMERIC(p,s) ◦ DECIMAL, DECIMAL(p), DECIMAL(p,s) ◦ REAL ◦ DOUBLE PRECISION ◦ FLOAT, FLOAT(p) 227  Chuỗi ký tự (character string) ◦ CHARACTER, CHARACTER(n) ◦ CHARACTER VARYING(x)  Chuỗi bit (bit string) ◦ BIT, BIT(x) ◦ BIT VARYING(x)  Ngày giờ (datetime) ◦ DATE gồm ngày, tháng và năm ◦ TIME gồm giờ, phút và giây ◦ TIMESTAMP gồm ngày và giờ 228  Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML) ◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số kiểu truy vấn khác  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index) DHTM_TMU sds 58 229  Là ngôn ngữ rút trích dữ liệu ◦ Thường đi kèm với một số điều kiện nào đó  Dựa trên ◦ Cho phép kết quả trả về của bảng có nhiều dòng trùng nhau Phép toán ĐSQH Một số bổ sung  230  Gồm 3 mệnh đề ◦ • Tên các cột cần được hiển thị trong kết quả truy vấn ◦ • Tên các bảng liên quan đến câu truy vấn ◦ • Biểu thức boolean xác định dòng nào sẽ được rút trích • Nối các biểu thức: AND, OR, và NOT • Phép toán:  ,  , , , , , LIKE và BETWEEN SELECT FROM [WHERE] 231 SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 232 SELECT FROM WHERE DHTM_TMU sds 59 233 SELECT * FROM NHANVIEN WHERE PHG=5 Lấy tất cả các cột của quan hệ kết quả TENNV HONV NGSINH DCHI GT LUONG PHG Tung Nguyen 12/08/19 55 638 NVC Q5 Nam 40000 5 Hung Nguyen 09/15/19 62 Ba Ria VT Nam 38000 5 3334455 55 9879879 87 MANV MA_NQL 8886655 55 3334455 55 TENLO T Thanh Manh 234 SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ TENNV HONV Tung Nguye n Hung Nguye n TENLO T Thanh Manh 3334455 55 9879879 87 MANV 235 SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ TEN HO Tung Nguye n Hung Nguye n TEN LOT Thanh Manh 3334455 55 9879879 87 MANV Tên bí danh 236 SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ HO TEN Nguyen Thanh Tung Nguyen Manh Hung 3334455 55 9879879 87 MANV Mở rộng DHTM_TMU sds 60 237 SELECT MANV, LUONG*1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ LUONG10% 33000 27500 3334455 55 9879879 87 MANV Mở rộng 238 SELECT LUONG FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ Loại bỏ các dòng trùng nhau - Tốn chi phí - Người dùng muốn thấy LUONG 30000 25000 25000 38000 38 DISTINCT LUONG 239 SELECT FROM WHERE  Cho biết MANV và TENNV làm việc ở phòng „Nghien cuu‟ MANV, TENNV NHANVIEN, PHONGBAN TENPHG=‘Nghien cuu’ PHG=MAPH G AND 240 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG Biểu thức luận lý TRUE TRUE DHTM_TMU sds 61 241 SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=30000 BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000 242 NOT BETWEEN SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000 243 SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen _ _ _ _’ LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen %’ Chuỗi bất kỳ Ký tự bất kỳ 244 SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen’ NOT LIKE SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT LIKE ‘Nguyen’ DHTM_TMU sds 62 245 Ngày giờ SELECT MANV, TENNV FROM NHANVIEN WHERE NGSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’ YYYY-MM-DD MM/DD/YYY Y ‘1955-12-08’ ’12/08/1955’ ‘December 8, 1955’ HH:MI:SS ’17:30:00’ ’05:30 PM’ ‘1955-12-08 17:30:00’ 246 WHERE TRUE SELECT MANV, MAPHG FROM NHANVIEN, PHONGBAN Không sử dụng mệnh đề WHERE MAPHG 1 4 3334455 55 3334455 55 MANV 5 1 9879879 87 9879879 87 3334455 55 4 5 9879879 87 247 SELECT TENPHG, DIADIEM FROM PHONGBAN, DDIEM_PHG WHERE MAPHG=MAPHG Tên bí danh AS PB, DDIEM_PHG AS PB.MAPHG=DD.MAPHG SELECT TENNV, NGSINH, TENTN, NGSINH FROM NHANVIEN, THANNHAN WHERE MANV=MA_NVIEN V.NGSINH, TENTN, TN.NGSINH NV, THANNHAN TN 248  Dùng để hiển thị kết quả câu truy vấn theo một thứ tự nào đó  Cú pháp ◦ ASC: tăng (mặc định) ◦ DESC: giảm SELECT FROM WHERE ORDER BY <danh sách các cột> DHTM_TMU sds 63 249  Ví dụ SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA SODA 10 30 999887777 999887777 MA_NVIEN 10 30 987987987 987654321 987987987 10 20 987654321 30 987654321 250  Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML) ◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index) 251  SQL có cài đặt các phép toán ◦ Hội (UNION) ◦ Giao (INTERSECT) ◦ Trừ (EXCEPT hoặc MINUS)  Kết quả trả về là tập hợp ◦ Loại bỏ các bộ trùng nhau ◦ Để giữ lại các bộ trùng nhau • UNION ALL • INTERSECT ALL • EXCEPT ALL 252  Cú pháp SELECT FROM WHERE UNION [ALL] SELECT FROM WHERE SELECT FROM WHERE INTERSECT [ALL] SELECT FROM WHERE SELECT FROM WHERE EXCEPT [ALL] SELECT FROM WHERE HTM_TMU sds 64 253 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT FROM WHERE ( SELECT FROM WHERE ) Câu truy vấn cha (Outer query) Câu truy vấn con (Subquery) 254  Các câu lệnh SELECT có thể lồng nhau ở nhiều mức  Các câu truy vấn con trong cùng một mệnh đề WHERE được kết hợp bằng phép nối logic  Câu truy vấn con thường trả về một tập các giá trị  Mệnh đề WHERE của câu truy vấn cha ◦ ◦ So sánh tập hợp thường đi cùng với một số toán tử • IN, NOT IN • ALL • ANY hoặc SOME ◦ Kiểm tra sự tồn tại • EXISTS • NOT EXISTS 255  Có 2 loại truy vấn lồng ◦ Lồng phân cấp • Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện trước, 1 lần ◦ Lồng tương quan • Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi lần tương ứng với một bộ của truy vấn cha 256 SELECT MANV, TENNV , PHG FROM NHANVIEN, PHONGBAN WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG SELECT MANV, TENNV, PHG FROM NHANVIEN WHERE PHG IN ( SELECT MAPHG FROM PHONGBAN WHERE DIADIEM=‘TP HCM’ ) DHTM_TMU sds 65 257 SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS ( SELECT * FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG ) 258  Giới thiệu  Định nghĩa dữ liệu  Truy vấn dữ liệu ◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác  Cập nhật dữ liệu  Khung nhìn (view)  Chỉ mục (index) 259  COUNT ◦ COUNT(*) đếm số dòng ◦ COUNT() đếm số giá trị khác NULL của thuộc tính ◦ COUNT(DISTINCT ) đếm số giá trị khác nhau và khác NULL của thuộc tính  MIN  MAX  SUM  AVG  Các hàm kết hợp được đặt ở mệnh đề SELECT 260  Tìm tổng lương, lương cao nhất, lương thấp nhất và lương trung bình của các nhân viên SELECT SUM(Luong), MAX(LUONG), MIN(LUONG), avg(LUONG) FROM NHANVIEN DHTM_TMU sds 66 261  Cho biết số lượng nhân viên của phòng „Nghien cuu‟ SELECT COUNT(MANV) FROM NHANVIEN nv, PHONGBAN pb WHERE nv.PHG = pb.MAPHG AND pb.TENPHONG = 'Nghien Cuu' 262  Cú pháp  Sau khi gom nhóm ◦ Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom nhóm SELECT FROM WHERE GROUP BY <danh sách các cột gom nhóm> 263  Cho biết số lượng nhân viên của từng phòng ban SELECT PHG, COUNT(MANV) AS SL_NV FROM NHANVIEN GROUP BY PHG 264  Cú pháp SELECT FROM WHERE GROUP BY <danh sách các cột gom nhóm> HAVING DHTM_TMU sds 67 265  Cho biết những nhân viên tham gia từ 2 đề án trở lên SELECT MA_NVIEN, COUNT(*) AS SL_DA, FROM PHANCONG GROUP BY MA_NVIEN HAVING COUNT(SODA)>2 266  Cho biết những phòng ban (TENPHG) có lương trung bình của các nhân viên lớn lơn 20000 SELECT TENPHG, AVG(LUONG) AS LUONGTB_PB FROM PHONGBAN pb, NHANVIEN nv WHERE pb.MAPHG=nv.PHG GROUP BY PHG HAVING AVG(LUONG)>20000 267  Mệnh đề GROUP BY ◦ Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY  Mệnh đề HAVING ◦ Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra một số điều kiện nào đó ◦ Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên từng bộ ◦ Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện 268  Với mỗi phòng cho biêt tên phòng và số lượng nhân viên của phòng SELECT pb.TENPHONG, count(*) FROM NHANVIEN nv, PHONGBAN pb WHERE nv.phg = pb.MAPHG GROUP BY pb.MAPHG, pb.TENPHONG DHTM_TMU sds 68 269  Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY và HAVING ◦ (1) Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE ◦ (2) Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY ◦ (3) Áp dụng các hàm kết hợp cho mỗi nhóm ◦ (4) Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING ◦ (5) Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT 270  Tìm những phòng ban có lương trung bình cao nhất.  Tìm những nhân viên có lương cao nhất. SELECT * FROM NHANVIEN WHERE LUONG >= ALL ( SELECT LUONG FROM NHANVIEN) 271  Tìm những phòng ban có lương trung bình cao nhất. ◦ B1: Tìm lương trung bình của từng phòng ban SELECT PHG, AVG(LUONG) FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) >= ALL( SELECT AVG(LUONG) FROM NHANVIEN GROUP BY PHG ) 272  Tìm 3 nhân viên có lương cao nhất SELECT TOP 3 * FROM NHANVIEN ORDER BY LUONG DESC DHTM_TMU sds 69 273  Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML) ◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index) 274  Truy vấn con ở mệnh đề FROM  Điều kiện kết ở mệnh đề FROM ◦ Phép kết tự nhiên ◦ Phép kết ngoàI  Cấu trúc CASE 275 SELECT FROM [WHERE ] [GROUP BY ] [HAVING ] [ORDER BY ] 276  Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML) ◦ Thêm (insert) ◦ Xóa (delete) ◦ Sửa (update)  Khung nhìn (View)  Chỉ mục (Index) DHTM_TMU