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

KHOA HTTT KINH TẾ & TMĐT

 Mục đích của học phần:

Trang bị các kiến thức cơ bản về CSDL quan hệ, ngôn ngữ thao

tác trên CSDL.

Cung cấp các kỹ năng cơ bản về tổ chức CSDL: thiết kế CSDL,

BÀI GIẢNG HỌC PHẦN

ngôn ngữ truy vấn.  Yêu cầu đạt được:

Bộ môn: Tin học

Nắm vững các kiến thức cơ bản về CSDL Hiểu các phương pháp thiết kế một CSDL Nhận thức được vai trò và sự cần thiết của hệ thống quản lý dữ liệu

với một tổ chức kinh tế xã hội

Biết thiết kế và tạo lập những CSDL dựa trên một HQT CSDL

thông dụng kết hợp với NNLT để lập trình quản lý CSDL

Cấu trúc học phần: 24,6 – Số TC: 02 (30 tiết)

 Chương 1: Tổng quan về CSDL

Nghe giảng: 24 tiết Thảo luận (bài tập): 6 tiết

 Chương 2: CSDL quan hệ

 Chương 3: Ngôn ngữ khai thác CSDL quan hệ

1 2

sds

3 4

1. TS Nguyễn Thị Thu Thủy. Giáo trình CSDL1. NXB Thống kê,

2015

2. Hồ Thuần, Hồ Cẩm Hà, Các hệ cơ sở dữ liệu lý thuyết và thực

hành, Nhà xuất bản giáo dục, 2009.

3. Nguyễn Kim Anh, Nguyên lý của các hệ cơ sở dữ liệu, Nhà

 1.1. Các khái niệm cơ bản về CSDL  1.2. Mô hình dữ liệu  1.3. Kiến trúc cơ sở dữ liệu  1.4. Ngôn ngữ quản trị cơ sở dữ liệu

xuất bản Đại học Quốc gia Hà Nội, 2008

4. Phương Lan, Giáo trình Cơ sở dữ liệu, nhà xuất bản lao động xã

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.

 CSDL (Database):

 Hệ QT CSDL (Database Management System - DBMS): là một tập hợp các chương trình cho phép người dùng định nghĩa, tạo lập, bảo trì các CSDL và cung cấp các truy cập có điều khiển đến các CSDL này.

◦ Là một tập hợp các dữ liệu có liên quan với nhau chứa thông tin về một tổ chức nào đó (trường đại học, công ty,…) được lưu trữ trên các thiết bị nhớ để đáp ứng nhu cầu khai thác thông tin của nhiều người sử dụng với nhiều mục đích khác nhau.

 Hệ QT CSDL cung cấp các phương tiện sau:

 Một số đặc tính của CSDL

◦ Ngôn ngữ định nghĩa dữ liệu (Data Denifition Language - DDL) ◦ Ngôn ngữ thao tác dữ liệu (Data Manipulation Language - DML) ◦ Các kiểm soát, các điều khiển đối với việc truy cập vào CSDL

◦ Tính tự mô tả ◦ Tính độc lập giữa chương trình và dữ liệu ◦ Tính trừu tượng dữ liệu ◦ Tính nhất quán ◦ Các cách nhìn dữ liệu

5 6

sds

7 8

 Thuật ngữ hệ CSDL để chỉ một CSDL và một hệ QT

Người sử dụng/Lập trình viên

CSDL để truy cập vào CSDL đó

 Hệ CSDL cung cấp cho người dùng một cách nhìn

Chương trình ứng dụng/Truy vấn

trừu tượng của DL, che giấu những chi tiết phức tạp về cách thức DL được lưu trữ và bảo trì.

HQT CSDL

Xử lý truy vấn

 Hệ CSDL là một hệ thống gồm 4 thành phần sau:

Truy xuất dữ liệu

◦ Cơ sở dữ liệu ◦ Người sử dụng ◦ Phần mềm hệ QT CSDL ◦ Phần cứng

CSDL

Định nghĩa CSDL

Hệ CSDL

 Một lược đồ được viết trong ngôn ngữ định nghĩa dữ liệu của một hệ QT CSDL cụ thể. Để mô tả các yêu cầu dữ liệu của một tổ chức sao cho mô tả đó dễ hiểu đối với nhiều NSD khác nhau thì ngôn ngữ này lại ở mức quá thấp  cần có một mô hình dữ liệu

 Khái niệm: Mô hình dữ liệu là một tập các khái niệm và các ký pháp dùng để mô tả dữ liệu, các mối quan hệ của dữ liệu, các ràng buộc trên dữ liệu của một tổ chức.

9 10

sds

11 12

 Mô hình dữ liệu logic trên cơ sở đối tượng

 Phần mô tả cấu trúc của CSDL

 Phần mô tả các thao tác, định nghĩa các phép toán được

phép trên dữ liệu.

◦ Mô hình thực thể - liên kết ◦ Mô hình hướng đối tượng ◦ Mô hình dữ liệu ngữ nghĩa ◦ Mô hình dữ liệu chức năng

 Mô hình dữ liệu logic trên cơ sở bản ghi

 Phần mô tả các ràng buộc toàn vẹn để đảm bảo sự

chính xác của dữ liệu.

◦ Mô hình quan hệ ◦ Mô hình mạng ◦ Mô hình phân cấp

 Mô hình dữ liệu logic vật lý

◦ Mô hình hợp nhất ◦ Mô hình bộ nhớ khung

 Mức trong

◦ Mô tả dữ liệu được thực sự lưu trữ như thế nào trong CSDL. ◦ Là mức thể hiện các cài đặt có tính chất vật lý của CSDL để đạt được tối ưu trong các thao tác tìm kiếm, lưu trữ (tận dụng các vùng nhớ còn trống)

13 14

◦ Là mức phản ánh các CTDL, các tổ chức tệp được dùng cho lưu

…

Khung nhìn 1

Khung nhìn n

trữ dữ liệu trên các thiết bị nhớ thứ cấp.

 Mức khái niệm

Người dùng 1 Người dùng n Mức ngoài (mức khung nhìn)

CSDL mức khái niệm

◦ Mô tả những dữ liệu nào được lưu trữ trong CSDL và có những mối quan hệ nào giữa các dữ liệu này (biểu diễn các thực thể, các thuộc tính và các mối quan hệ đó)

CSDL Mức vật lý

 Mức ngoài

Mức khái niệm (mức logic)

◦ Mô tả chỉ một phần của toàn bộ CSDL, phần thích hợp với một

NSD nhất định.

◦ Hệ thống có thể cung cấp nhiều khung nhìn đối với cùng một

CSDL.

Mức trong (mức vật lý)

sds

15 16

Thể hiện

 Lược đồ CSDL (Database Schema)

 Thể hiện CSDL (Database Instance)

◦ Là dữ liệu hiện thời được lưu trữ trong CSDL ở một thời điểm

◦ Là các mô tả về cấu trúc và ràng buộc trên CSDL

nào đó

SVien

Ten MaSV Nam Khoa

◦ Tình trạng của CSDL

TenHP MaHP TinChi Khoa

Hphan

MaHP MaHP_Truoc

DKien

MaKH MaHP

HocKy Nam GV

KHoc

Hphan TenHP MaHP TinChi Khoa KQua MaSV MaKH Diem Tin học đại cương INFO0111 3 HTTTKT CSDL1 INFO6111 2 HTTTKT

17 17 8 8 8 8 112 119 85 92 102 135 8 6 10 9 8 10

MaKH MaSV

Diem

KQua

 Ứng với 3 mức của một hệ CSDL có 3 loại lược đồ

SVien Ten MaSV Nam Khoa DKien MaHP MaHP_Truoc Son Bao 17 8 1 2 HTTTKT TMĐT INFO0111 INFO6111 MAT1234 INFO0111 17 18

◦ Lược đồ ngoài (lược đồ con) ◦ Lược đồ logic ◦ Lược đồ vật lý

…

Lược đồ ngoài 1

Lược đồ ngoài n

 Phân biệt được lược đồ CSDL với bản thân CSDL

Độc lập dữ liệu mức logic

Mức ngoài Người dùng Người dùng

Lược đồ khái niệm

Ánh xạ ngoài/ Ánh xạ khái niệm Mức khái niệm

Lược đồ trong

Độc lập dữ liệu mức vật lý

Ánh xạ khái niệm/ Ánh xạ trong

Mức trong

sds

19 20

Khung nhìn 1

Khung nhìn 2

TenHP Khoa

MaHP TenHP TinChi

 Ngôn ngữ định nghĩa dữ liệu (Data Denifition

TenHP MaHP TinChi Khoa

Mức ngoài

Struct Hphan{

Char MaHP;

Language - DDL): cho phép người dùng định nghĩa CSDL (đặc tả các kiểu và các CTDL, đặc tả các ràng buộc trên các DL được lưu trữ trong CSDL) ◦ VD: Create table Co(Sohieu C(2), Ten C(10), Von N(4,0))

Mức logic

Char Ten[15];

 Ngôn ngữ thao tác dữ liệu (Data Manipulation

Int TinChi;

Char Khoa[10];

Struct Hphan next;

};

Index MaHP;

Language - DML): cho phép người dùng thêm, xóa, cập nhật dữ liệu và truy xuất DL trong CSDL ◦ VD: Update… Insert… Delete… Select…From…Where…

Mức vật lý

 2.1. Các khái niệm cơ bản

 2.2. Ràng buộc toàn vẹn

 2.3. Phụ thuộc hàm

 2.4. Chuẩn hóa các lược đồ quan hệ

 2.1.1 Thuộc tính  2.1.2 Miền  2.1.3 Quan hệ  2.1.4 Lược đồ quan hệ  2.1.5 Các tính chất của quan hệ  2.1.6 Khóa

 2.5. Tách các lược đồ quan hệ

 2.6. Thiết kế CSDL quan hệ

21 22

sds

23 24

 Quan hệ gồm

 Tên các cột của quan hệ  Mô tả ý nghĩa cho các giá trị tại cột đó

◦ Tên ◦ Tập hợp các cột

Thuộc tính

 Cố định  Được đặt tên  Có kiểu dữ liệu ◦ Tập hợp các dòng

 Thay đổi theo thời gian

HONV NS DIACHI GT LUONG PHG Nguyen 12/08/1955 Hà Nội Nam 40000 5 Bui 07/19/1968 Bắc Ninh Nu 25000 4 Le 06/20/1951 Bắc Giang Nu 43000 4 Nguyen 09/15/1962 Hà Nội Nam 38000 5

TENNV Tung Hang Nhu Hung

 Tất cả các dữ liệu trong cùng 1 một cột đều có dùng

 Một dòng ~ Một thực thể  Quan hệ ~ Tập thưc thể

kiểu dữ liệu

 Lược đồ CSDL

◦ Gồm nhiều lược đồ quan hệ

26 25

Lược đồ CSDL

 Lược đồ quan hệ ◦ Tên của quan hệ ◦ Tên của tập thuộc tính

Lược đồ quan hệ

NHANVIEN(MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG)

PHONGBAN(MAPHG, TENPHG, TRPHG, NG_NHANCHUC)

NHANVIEN(MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG)

DIADIEM_PHG(MAPHG, DIADIEM)

THANNHAN(MA_NVIEN, TENTN, GT, NS, QUANHE)

Là tập hợp

DEAN(TENDA, MADA, DDIEM_DA, PHONG)

sds

27 28

 Là tập các giá trị nguyên tố gắn liền với một thuộc tính

 Là các dòng của quan hệ (trừ dòng tiêu đề - tên của các

◦ Kiểu dữ liệu cơ sở

thuộc tính)

 Chuỗi ký tự (string)  Số (integer)

 Thể hiện dữ liệu cụ thể của các thuộc tính trong quan

◦ Các kiểu dữ liệu phức tạp

hệ

Không được chấp nhận

 Tập hợp (set)  Danh sách (list)  Mảng (array)  Bản ghi (record)

Dữ liệu cụ thể của thuộc tính

 Ví dụ

◦ TENNV: string ◦ LUONG: integer

 Quan hệ (hay thể hiện quan hệ)

 Lược đồ quan hệ

◦ Một quan hệ r của lược đồ quan hệ R(A1, A2, …, An), ký hiệu

r(R), là một tập các bộ r = {t1, t2, …, tk}

◦ Trong đó mỗi ti là 1 danh sách có thứ tự của n giá trị ti=

◦ Cho A1, A2, …, An là các thuộc tính ◦ Có các miền giá trị D1, D2, …, Dn tương ứng ◦ Ký hiệu R(A1:D1, A2:D2, …, An:Dn) là một lược đồ quan hệ

…, vn>  Mỗi vj là một phần tử của miền giá trị DOM(Aj) hoặc giá trị rỗng

◦ Bậc của lược đồ quan hệ là số lượng thuộc tính trong lược đồ

29 30

◦ NHANVIEN(MANV:integer, TENNV:string, HONV:string, NGSINH:date, DCHI:string, GT:string, LUONG:integer, DONVI:integer)  NHANVIEN là một lược đồ bậc 8 mô tả đối tượng nhân viên  MANV là một thuộc tính có miền giá trị là số nguyên  TENNV là một thuộc tính có miền giá trị là chuỗi ký tự

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 t1 t2 t3 t4 Hung Nguyen 09/15/1962 Null Nam 38000 5

sds

vi 32 31

 Khái niệm: Ràng buộc toàn vẹn là tập các qui tắc, qui

 Ràng buộc về miền (là loại ràng buộc thấp nhất): mô tả tính chất của các thuộc tính khi tạo lập CSDL (thuộc kiểu gì, độ dài là bao nhiêu,…) ◦ VD: Thuộc tính “số điện thoại” là kiểu chuỗi ký tự đúng bằng

định yêu cầu DL trong CSDL phải thỏa mãn.

7 ký tự trong xâu

 Mục đích: nhằm đảm bảo tính độc lập và tính toàn vẹn

 Ràng buộc giải tích: là những ràng buộc giữa các thuộc

dữ liệu.

 Các loại ràng buộc:

tính được biểu diễn bằng các biểu thức toán học ◦ VD: Khi nhập “số lượng” và “đơn giá” của 1 mặt hàng, hệ

thống sẽ tự động tính giá trị của thuộc tính “thành tiền” theo công thức “số lượng” x “đơn giá” = “thành tiền”

 Ràng buộc về khóa (logic):

◦ Ràng buộc về miền (kiểu) ◦ Ràng buộc giải tích ◦ Ràng buộc khóa (logic) ◦ ….

◦ Mối quan hệ giữa các thuộc tính với nhau không phải là các

ràng buộc giải tích, được gọi là phụ thuộc hàm.

◦ Thuộc tính Y phụ thuộc hàm vào thuộc tính X  mỗi 1 giá trị

của X xác định giá trị của Y

 Khái niệm:

 Xét lược đồ quan hệ

 Cho R(U) là 1 lược đồ quan hệ, U ={A1,A2,…,An} là tập các thuộc

33 34

Phim(Tênphim, Nămsx, Thờilượng, Loạiphim, Xưởngsx, Diễnviên)

tính và X,Y U

 Ta nói X xác định hàm Y (hay Y phụ thuộc hàm vào X) nếu với mọi

 Và thể hiện

Tênphim

Nămsx

Thờilượng

Loạiphim

Xưởngsx

Diễnviên

quan hệ r xác đị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]

Star Wars

1977

124 color

Fox

Carrie Fisher

Star Wars

1977

124 color

Fox

Mark Hamill

Star Wars

1977

124 color

Fox

Harrison Ford

 Nói cách khác thì thuộc tính Y của quan hệ R được gọi là phụ thuộc hàm vào thuộc tính X của quan hệ R nếu với mỗi giá trị của X xác định duy nhất một giá trị của Y. X được gọi là xác định hàm của Y

Mighty Ducks

1991

104 color

Disney

Emilio Esteves

 Ký hiệu: XY

Wayne’s World

1992

95 color

Paramount

Dana Carvey

 Ví dụ: Phụ thuộc hàm của thuộc tính HoTenSV và MaSV được biểu

Wayne’s World

1992

95 color

Paramount

Mike Meyers

diễn như sau:

MaSV HoTenSV

sds

35 36

 Hệ tiên đề cho phụ thuộc hàm:

 Tìm được nhiều PTH

Tênphim, Nămsx  Thờilượng

Tênphim, Nămsx  Loại

Tênphim, Nămsx  Xưởngsx

Không phải PTH

Tênphim, Nămsx  Diễnviên

Cho X  Y, X  Z  X  YZ

Cho X  Y,WY Z  XW  Z

Tênphim Nămsx Thờilượng Loạiphim Xưởngsx Diễnviên Star Wars 1977 color Fox Carrie Fisher 124 Star Wars 1977 color Fox Mark Hamill 124 Star Wars 1977 color Fox Harrison Ford 124 color Mighty Ducks 1991 Disney Emilio Esteves 104 Wayne’s World 1992 color Paramount Dana Carvey 95

Hệ tiên đề Armstrong: 1. Luật phản xạ (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ả của hệ tiên đề Armstrong: 4. Luật hợp (the union rule) 5. Luật tựa bắc cầu (the pseudotransitivity rule) 6. Luật tách (the decomposition rule):

Cho X  Y, Z  Y  X  Z

Wayne’s World 1992 color Paramount Mike Meyers 95

 2.3.1. Bao đóng của tập phụ thuộc hàm  2.3.2. Bao đóng của tập thuộc tính

 Cho R=ABC và tập phụ thuộc hà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 của (1) thêm B) 3. ABC (gt) 4. ABABC (Luật tăng trưởng của (3) thêm AB) 5. BCABC (Luật bắc cầu từ (2) và (4))

37 38

sds

39 40

 Suy diễn logic

 Định nghĩa:

◦ Gọi F là tập các PTH trên tập thuộc tính U, XU. ◦ X+ là bao đóng của X đối với F là tập tất cả các thuộc tính A mà

◦ Gọi F là tập các PTH trên sơ đồ quan hệ R(U) và XY là 1 PTH, X,YU. Khi đó ta nói XY được suy diễn logic từ F nếu với mỗi quan hệ r xác định trên R(U) thoả các phụ thuộc hàm trong F thì cũng thoả X  Y

◦ VD: F={AB, BC} thì AC được suy diễn logic từ F

 Bao đóng của tập phụ thuộc hàm

◦ Gọi F+ là bao đóng của F, tức là tập tất cả các phụ thuộc hàm

có thể được suy diễn logic từ F

PTH XA có thể được suy diễn logic từ F nhờ hệ tiên đề Armstrong. ◦ Ký hiệu là X+ ◦ X+ = { A  U | X  A  F+}

◦ Ký hiệu là F+ ◦ F là họ đầy đủ (full family) nếu F = F+

41 42

Thuật toán:

 R(A, B, C, D, E, G)  F = { ABC, CA, BCD, ACDB, DEG, BEC,

◦ Input: tập hữu hạn các thuộc tính U, tập các PTH F trên U và

CGBD, CE AG}

X U

◦ Output: X+, bao đóng của X đối với F

Phương pháp: lần lượt tính các tập X0, X1, X2,…theo các bước sau:

◦ Bước 0: Đặt X0 = X. ◦ Bước i: Tính Xi từ Xi-1

 Tìm (BD)+ Giải:  X0 = BD  Tìm các PTH có VT là B, D hoặc BD: ◦ Có DEG X1=X0  {EG}= BDEG

Nếu Y Z F và Y Xi-1 và A Z và A không thuộc Xi-1 thì Xi=Xi-

 Tương tự, ta có:

ngược lại, Xi = Xi-1 .

1 A

Nếu Xi ≠ Xi-1 thì lặp Bước i

ngược lại, chuyển Bước n

◦ BEC  X2=X1  {C}= BCDEG ◦ CA  X3=X2  {A}= ABCDEG ◦ X4=X3  Ngừng

Vậy BD+

F = ABCDEG

o Bước n X+ = Xi

sds

43 44

 Nhận xét về PTH:

◦ Từ một tập các phụ thuộc hàm có thể suy diễn ra các phụ thuộc

hàm khác

 R(A, B, C, D, E, F)  F = { ABC, BCAD, DE, CFB }  Tìm AB+

◦ Trong một tập phụ thuộc hàm cho sẵn có thể có các phụ thuộc

hàm dư thừa

F = AB

 Làm thế nào để có được một phụ thuộc hàm tốt ???

AB+

F = {A, B, C, D, E}

Giải:  AB+  ABC: ABC  BCAD: ABCD  DE: ABCDE  Ngừng

 Tương đương

◦ Hai tập PTH F1 và F2 gọi là tương đương

45 46

F1  F2  F1+  F2+

 R(A, B, C, D, E)  F1 = { ABC, AD, CDE }  F2 = { ABCE, AABD, CDE }  F1  F2 ?



Mọi thể hiện của quan hệ thỏa F1

Mọi thể hiện của quan hệ thỏa F2

 Chứng minh

 Bổ đề

◦ F1 là hệ quả của F2  F1 được suy dẫn từ F2 ◦ F2 là hệ quả của F1  F2 được suy dẫn từ F1

F1  F2  F1 là hệ quả của F2 và

F2 là hệ quả của F1

sds

47 48

 {ABCE, AABD, CDE }  {ABC, AD,

CDE } ◦ Ta thấy F1  F2, hiển nhiên F1 là hệ quả của F2

 R(A, B, C, D, E)  F1 = { ABC, AD, CDE }  F2 = { ABCDE }  F1  F2 ?

 {ABC, AD, CDE }  {ABCE, AABD,

 Chứng minh

CDE } ◦ Xét F2 có AE, tìm xem F1 có AE ?

◦ F1 là hệ quả của F2  F1 được suy dẫn từ F2 ◦ F2 là hệ quả của F1  F2 được suy dẫn từ F1

 ABC AC (luật tách)  AD (gt)  ACD (luật hợp)  CDE  AE (luật bắc cầu)

 Tập phụ thuộc hàm tương đương (equivalent functional dependancy)

 {ABCDE}  {ABC, AD, CDE}

Cho F và G là hai tập phụ thuộc hàm trên tập thuộc tính U, ta nói F và G tương đương (hay F phủ G hoặc G phủ F) và ký hiệu là F+ = G+ nếu và chỉ nếu mỗi phụ thuộc hàm thuộc F đều thuộc G + và mỗi phụ thuộc hàm thuộc G đều thuộc F + .

◦ Xét CDE không thuộc trong F2 ◦ F1 không được suy dẫn từ F2 ◦ F1 không là hệ quả của F2

 Ký hiệu: F ≈ G

 Ví dụ

 {ABC, AD, CDE}  {ABCDE}

Cho R(A,B,C)

◦ Xét F2 có AE?

F={AB;AC;BA;CA;BC}

G={AB; CA; BC}

Chứng minh F và G tương đương

49 50

sds

51 52

 Tập phụ thuộc hàm không dư thừa

 Định nghĩa phủ tối thiểu F được gọi là một tập phụ thuộc hàm tối thiểu nếu F thoả đồng thời ba

điều kiện sau:

Vế phải của F chỉ có một thuộc tính.

Định nghĩa: Tập phụ thuộc hàm F là không dư thừa nếu trong F không tồn tại một phụ thuộc hàm nào mà khi loại bỏ đi phụ thuộc hàm đó, cho ta một tập phụ thuộc hàm tương đương với F.

Không  XAF mà F + = (F  (X  A))+

 Thuật toán tìm phủ không dư thừa của một tập phụ thuộc hàm Lần lượt tính các tập F0, F1,…, Fm theo các bước sau:

a) b) Không  f: XAF và Z  X mà F+=(F(XA)(ZA))+ c) Trong đó - vế phải của mỗi phụ thuộc hàm ở điều kiện a) chỉ có một thuộc tính,

nên bảo đảm không có thuộc tính nào ở vế phải là dư thừa.

Bước 0: F0 = F. Bước i: Tính Fi từ Fi-1, cụ thể: Nếu (Fi-1\{LiRi})≈Fi thì Fi=Fi-1\{LiRi}

ngược lại , Fi = Fi-1 .

- điều kiện b) bảo đảm không có một thuộc tính nào tham gia vế trái

của phụ thuộc hàm là dư thừa.

Nếu Fi ≠ Fi-1 thì lặp Bước i

ngược lại, chuyển Bước n

- điều kiện c)bảo đảm cho tập F không có một phụ thuộc hàm nào là

Bước n: F‟= Fi

dư thừa.

 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 là PTH không đầy đủ (có VT dư thừa)

BC là PTH đầy đủ

 VD2:

 VD: Ở VD2, PTH ABD có A+=ABCD  AD A+

◦ Cho R(A,B,C,D) và F = {A → BC,B → C,AB → D}

◦ Trong F ta thay ABD bằng AD

◦ Ta có: F ≡ F - {AB D}{(AB-B) D}

 Vậy F ≡ {A → BC,B → C,A → D}

≡{A → BC,B → C,A → D}

 ABD là PTH không đầy đủ (có VT dư thừa)

53 54

sds

55 56

 Thuật toán tìm phủ tối thiểu

 Cho R(A,B,C,D) và F={AB, BA, BC, AC, CA}

 Xét AB

F – {AB} = AC

Vào: Tập thuộc tính U, F = {Li  Ri : i = 1..n} Ra: Phủ tối thiểu F của tập phụ thuộc hàm F  Thuật toán

không chứa VP

◦ A+ ◦ AB không là phụ thuộc hàm thừa

B.1. Tách các PTH có VP lớn hơn một thuộc tính thành các

PTH có VP một thuộc tính

 Xét BA

có chứa VP

B.2. Loại bỏ khỏi F các thuộc tính thừa trong vế trái của các

F-{BA} = BCA

phụ thuộc hàm

◦ B+ ◦ BA là phụ thuộc hàm thừa

B.3. Loại bỏ khỏi F các phụ thuộc hàm dư thừa

 VD 1: Cho F= {AC, BC, C D, DEC, CAB}

◦

Tìm phủ tối thiểu của F?

 Nhắc lại ◦ Khóa

 VD 2: Cho F= {AB→C, D→EG, C→A, BE→C, BC→D,

• Là một tập các thuộc tính dùng để xác định tính duy

CG→ BD, ACD→B, CE→AG }

nhất của mỗi bộ trong quan hệ

◦

Tìm phủ tối thiểu của F?

 Các bộ trong quan hệ khác nhau từng đôi một ◦ Gồm

• Siêu khóa • Khóa • Khóa chính

57 58

sds

59 60

 Tìm tất cả các khóa của lược đồ quan hệ và tập PTH sau:

 B1: Xác định tất cả các tập con khác rỗng của U. Kết quả tìm

R(U)=CSZ; F = {f1:CS → Z; f2:Z → C}

được giả sử là các tập thuộc tính Xi, i= 1..n

Siêu khóa

Khóa

Xi

 B2: Tìm bao đóng của các Xi

Xi+ C

 B3: Xác định tập siêu khóa Si (i=1..m) là các Xi có bao đóng

đúng bằng U

 B4: Xây dựng tập chứa tất cả các khóa của U từ tập S bằng cách

CSZ

xét mọi Si, Sj là con của S (i ≠j), nếu Si  Sj thì loại Sj (i,j=1..n). Tập S còn lại là các khóa của lược đồ

SCZ

CSZ

Vậy lược đồ quan hệ R có 2 khóa là CS và SZ

 Một số khái niệm:

◦ Tập nguồn (TN) chứa tất cả các thuộc tính có xuất hiện ở vế trái và không xuất hiện ở vế phải của tập phụ thuộc hàm. Những thuộc tính không tham gia vào bất kỳ một phụ thuộc hàm nào thì cũng đưa vào tập nguồn.

◦ Tập đích (TD) chứa tất cả các thuộc tính có xuất hiện ở vế phải và không xuất hiện ở vế trái của tập phụ thuộc hàm.

◦ Tập trung gian (TG) chứa tất cả các thuộc tính vừa tham gia

vào vế trái vừa tham gia vào vế phải.

 Hệ quả: nếu K là khóa của R thì TN  K và TD ∩ K = ∅

61 62

sds

63 64

 Giải lại VD trên  Ta có: TN={S}, TG={C,Z}  Gọi Xi là các tập con của TG

Xi

 2.4.1. Dạng chuẩn 1 (1NF - First Normal Form)  2.4.2. Dạng chuẩn 2 (2NF - Second Normal Form)  2.4.2. Dạng chuẩn 3 (3NF - Third Normal Form)  2.4.4. Dạng chuẩn Boye-Codd (BCNF-Boye-Codd Normal

(TN  Xi) S

(TN  Xi)+ Siêu khóa Khóa S

Form)

CSZ

CS

 2.4.5. Phương pháp xác định dạng chuẩn cao nhất.

CSZ

SZ

CSZ

Vậy lược đồ quan hệ R có 2 khóa là CS và SZ

 Định nghĩa 1:

 Định nghĩa 2:

• Cho R(U) là một lược đồ quan hệ với U là tập các thuộc tính, F là

• Cho R(U) là một lược đồ quan hệ với U là tập các thuộc tính, F là tập

tập các phụ thuộc hàm trên R và AU

các phụ thuộc hàm trên R và X  U, AU

• Chúng ta nói: A là phụ thuộc bắc cầu vào X nếu tồn tại một tập

• Chúng ta nói: A là thuộc tính khóa (prime) nếu A thuộc một khóa tối thiểu nào đó của R. Ngược lại A được gọi là thuộc tính không khóa (nonprime).

 Ví dụ:

thuộc tính Y, Y  U sao cho X  Y, Y  A thuộc F+ nhưng Y  X không thuộc F+ . Ngược lại A không phụ thuộc bắc cầu vào X hay A phụ thuộc trực tiếp vào X

R(ABCD)

F= {AB  C , BC  A,B  D}

 Lược đồ trên có 2 khóa tối thiểu AB, BC

 A, B, C : thuộc tính khóa; D : thuộc tính không khóa

65 66

sds

67 68

 Định nghĩa : Dạng chuẩn là tập các tiêu chuẩn để đánh giá độ

tốt & xấu (của một lược đồ Quan hệ).



Phân loại : Có 4 mức dạng chuẩn :

2.4.1 Dạng chuẩn 1 (1NF)  Định nghĩa : • Một sơ đồ quan hệ R được gọi là ở dạng chuẩn 1 nếu tất cả các miền giá trị của các thuộc tính trong R đều chỉ chứa giá trị nguyên tố

1. Dạng chuẩn 1

2. Dạng chuẩn 2

• Giá trị nguyên tố là giá trị mà không thể chia nhỏ ra được nữa  Ví dụ: Quan hệ không ở 1NF và quan hệ sau khi chuẩn hóa về

3. Dạng chuẩn 3

1NF

4. Dạng chuẩn Boyce-Codd (BC)

5. Dạng chuẩn 4, Dạng chuẩn 5 : không được đề cập trong bài

giảng này

69 70

Thuộc tính PRODUCT là đa thuộc tính Bộ 1 có ITEM, PRICE không nguyên tố

 Không là 1NF

sds

71 72

Quan hệ đạt 1NF nếu toàn bộ các thuộc tính của mọi

bộ đều mang giá trị đơn (giá trị nguyên tố).

 Vẫn còn trùng lặp thông tin

Biến đổi thành các giá trị nguyên tố

73 74

SUPPLIERS(S#,SNAME,ADD,ITEM,PRICE)

kí hiệu ngắn gọn SUPPLIERS(S,N,A,I,P)

2.4.2 Dạng chuẩn 2 (2NF) Định nghĩa : • Một sơ đồ quan hệ R được coi là ở dạng chuẩn 2 nếu sơ đồ

quan hệ này ở 1NF

• Tất cả các thuộc tính không khóa đều phụ thuộc hàm đầy đủ

  Tập PTH: F = {SNA,SIP}  Khóa tối thiểu duy nhất là: SI  Ta thấy:

vào khóa chính.

o Thuộc tính khóa: S,I o Thuộc tính không khóa: N,A,P  Xét thuộc tính không khóa A

Phụ thuộc hàm đầy đủ • Cho lược đồ quan hệ R(U), F là tập phụ thuộc hàm trên R. X, Y ⊆ U. Y được gọi là phụ thuộc đầy đủ vào X nếu:

- X  Y thuộc F+ - ! ∃ X’⊂ X : X’ ⟶ Y

F+

• Các phụ thuộc hàm không đầy đủ còn gọi là phụ thuộc bộ

1. A chỉ phụ thuộc vào S 2. S ⊂ SI  A không phụ thuộc đầy đủ vào khóa chính SI  SUPPLIERS không thuộc 2NF

phận.

sds

75 76

Tách SUPPLIERS thành S1 và S2

 Lược bỏ những PTH không đầy đủ  Quan hệ đạt 2NF

◦ S1 (S, N, A) và F1={SNA}

◦ S2 (S, I, P) và F2={SIP}

- Đạt 1NF - Các thuộc tính không khóa phụ thuộc đầy đủ vào

Kiểm tra S1, S2 có thuộc 2NF?

thuộc tính khóa

◦ Khóa của S1 là S

◦ Khóa của S2 là SI

S1, S2 đạt chuẩn 2

Cho lược đồ quan hệ Q(A,B,C,D)

Tập PTH F={ABC; BD; BCA}.

77 78

Vào: lược đồ quan hệ R, tập phụ thuộc hàm F

Hỏi Q có đạt 2NF không?

Giải:

Ra: khẳng định R đạt 2NF hay không đạt 2NF.

(TN  Xi) (TN  Xi)+ Siêu khóa Khóa

TN={B}, TG={AC}

Xi  B

 Bước 1: Tìm tất cả khóa của R

A AB

ABCD

 Bước 2: Với mỗi khóa K, tìm bao đóng của tất cả tập con

C BC

ABCD

thật sự S của K.

AC ABC

ABCD

ABC

 Bước 3: Nếu có bao đóng S+ chứa thuộc tính không khóa

thì R không đạt 2NF, ngược lại thì R đạt 2NF

Khóa là K1=AB và K2=BC. Ta thấy B  K1, BD, D là thuộc tính không khóa  thuộc tính không khóa không phụ thuộc đầy đủ vào khóa  Q không đạt chuẩn 2.

sds

79 80

Các dạng chuẩn đối với lược đồ quan hệ

2.4.3 Dạng chuẩn 3 (3NF)  Định nghĩa: Một sơ đồ quan hệ R được coi là ở dạng chuẩn 3 nếu

 Vẫn còn trùng lặp thông tin

Phòng

 Sơ đồ quan hệ này đã ở dạng 2NF  Mọi thuộc tính không khóa đều không phụ thuộc bắc cầu vào

khóa chính

phòngID

phòngĐChỉ

Giá

chủID

chủTên

PG04

6 NVC Q5

350 CO40

X

ItemInfo(item, price, discount).

PG16

5 NT Q10

450 CO93

Y

PG36

2 NTMK Q3

375 CO93

Y

 Ví dụ: F = {item ⟶ price, price ⟶ discount} thuộc tính không khóa discount phụ thuộc bắc cầu vào khóa chính

item.

ItemInfo(item, price) Discount(price, discount)

Vậy quan hệ này không ở dạng 3NF.  Chuẩn hoá

81 82

Xét R (SIDM) trong đó:

S1(S, N, A) và F1={SNA}

◦ S: kí hiệu cho cửa hàng (Store)

◦ Khóa chính S

◦ I: kí hiệu cho mặt hàng (Item)

◦ Thuộc tính không khóa: N,A

◦ D: kí hiệu cho gian hàng (Department)

◦ SNA  N,A không phụ

◦ M: kí hiệu cho người quản lý (Manager)

thuộc bắc cầu vào S

S2 (S, I, P) và F2={SIP}

◦ Khóa chính: SI

◦ Có PTH: SI D và SD M R (SIDM) và F={SID, SDM}

◦ Thuộc tính không khóa: P

◦ Khóa tối thiểu: SI ◦ Thuộc tính không khóa: D, M

◦ SI  P  P không phụ thuộc

◦ R đạt chuẩn 2

bắc cầu vào SI

S1, S2 đạt chuẩn 3

◦ Do: SID nên SI SD và SDM Vậy ta có: SIM

R không đạt chuẩn 3

sds

83 84

 Lược bỏ những PTH bắc cầu  Quan hệ đạt 3NF

 Tách R thành R1 và R2 với ◦ R1(SID) và F1={SID} ◦ R2(SDM) và F2={SDM}

- Đạ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

85 86

Vào: lược đồ quan hệ R, tập PTH F

 R là lược đồ quan hệ

Ra: khẳng định R đạt 3NF hay không đạt 3NF.

 F là tập các PTH có vế phải một thuộc tính.

 Bước 1: Tìm tất cả khóa của R

 R đạt 3NF nếu và chỉ nếu mọi PTH XA  F với

 Bước 2: Từ F tạo tập PTH tương đương F1tt có vế phải một

AX đều có:

thuộc tính.

◦ Hoặc X là siêu khóa.

◦ Hoặc A là thuộc tính khóa.

 Bước 3: Nếu mọi PTH X  A  F1tt với AX đều có X là siêu khóa hoặc A là thuộc tính khoá thì R đạt 3NF ngược lại R không đạt 3NF

sds

87 88

Cho lược đồ quan hệ Q(A,B,C,D) F={ABC; DB; CABD} Hỏi R có đạt chuẩn 3 không? Giải: TN =  TG = {ABCD}

 Vẫn còn trùng lặp thông tin

Xét các khóa

 K1 = {AB}

89 90

Thuê kháchID phòngID

ngàyThuê ngàyTrả nhânviênID

F={ABC; DB; CABD}

 K2 = {AD}

 K3={C}

Ta thấy mọi phụ thuộc hàm X  A  F đều có A là

thuộc tính khóa. Vậy Q đạt 3NF

CR76 PG04 1/6/03 31/8/04 SG37 CR76 PG16 1/9/04 1/9/05 SG24 CR56 PG04 1/9/02 10/6/03 SG37 CR56 PG36 10/10/03 1/12/04 SG05

nhânviênID  phòngID

sds

91 92

Các dạng chuẩn đối với lược đồ quan hệ

Xét lược đồ R (CSZ)

2.4.4 Dạng chuẩn Boye – Codd (BCNF)  Định nghĩa: Một sơ đồ quan hệ R được coi là ở dạng

 Lược đồ R (CSZ)  Tập PTH F={CSZ,ZC}  Khóa tối thiểu: CS, CZ  Xét ZC do Z không là

chuẩn BCNF nếu:  Sơ đồ quan hệ này đã ở dạng 3NF  Các PTH không hiển nhiên có VT chứa khóa

khóa  R không đạt chuẩn BCNF

 Hay nói cách khác: Một sơ đồ quan hệ R được coi là ở dạng chuẩn BCNF nếu với mọi XA thuộc F+ và AX thì X chứa một khóa của R

 Lược bỏ những PTH có VT không chứa khóa  Quan hệ đạt BCNF

 Phân tách R thành 2 lược đồ con  R1 (CZ)

◦ Đạt 3NF ◦ Các PTH không hiển nhiên có VT chứa khóa (hay VT là siêu

◦ Khóa: CZ ◦ R1 đạt chuẩn BCNF

khóa)

 R2 (SZ)

◦ Khóa là SZ ◦ R2 đạt chuẩn BCNF

93 94

sds

95 96

S1(S, N, A) và F1={SNA}

◦ Khóa chính S

◦ SNA

Vào: lược đồ quan hệ R, tập phụ thuộc hàm F

◦  S1 đạt chuẩn BCNF

Ra: khẳng định R đạt BCNF hay không đạt BCNF.

S2 (S, I, P) và F2={SIP}

◦ Khóa chính: SI

 Bước 1: Tìm tất cả khóa của R

◦ SI  P

 Bước 2: Từ F tạo tập PTH tương đương F1tt có vế phải một

◦  S2 đạt chuẩn BCNF

thuộc tính

Vậy S1, S2 đạt chuẩn BCNF

 Bước 3: Nếu mọi phụ thuộc hàm X  A  F1tt với AX đều có X là siêu khóa thì R đạt BCNF ngược lại R không đạt BCNF

97 98

Vào: lược đồ quan hệ R, tập PTH hàm F

Ra: khẳng định R đạt chuẩn gì?

Cho lược đồ quan hệ Q(A,B,C,D,E,I) Tập PTH F={ACDEBI;CEAD}. Hỏi Q có đạt chuẩn BC không? Giải: TN={C} TG={ADE}

 Bước 1: Tìm tất cả khóa của R

 Bước 2: Kiểm tra BCNF nếu đúng thì R đạt BCNF, kết thúc

thuật toán. Ngược lại qua bước 2.

 Bước 3: Kiểm tra 3NF nếu đúng thì R đạt 3NF, kết thúc thuật

toán. Ngược lại qua bước 4.

 Bước 4: Kiểm tra 2NF nếu đúng thì R đạt 2NF, kết thúc thuật

toán. Ngược lại R đạt 1NF.

F = F1tt = {ACDE, ACDB, ACDI, CEA, CED} Mọi PTH của F1tt đều có vế trái là siêu khóa  Q đạt dạng BCNF

sds

99 100

 Chuẩn 2NF

◦ Giản ước sự dư thừa dữ liệu

◦ Tránh dị thường khi cập nhật dữ liệu do sự dư thừa dữ liệu gây

 Chuẩn 3NF

◦ Tránh sự dị thường khi thêm và xóa các bộ trong quan hệ

 Chuẩn BCNF

◦ Tránh sự dị thường khi thêm và xóa các bộ trong quan hệ

◦ Chặt chẽ và mạnh hơn chuẩn 3

 Khái niệm

◦ Phép tách các lược đồ quan hệ là quá trình phân tách các

 2.5.1. Phép tách lược đồ quan hệ  2.5.2. Thuật toán kiểm tra phép tách - kết nối bảo toàn

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

thông tin

◦ Hay nói cách khác:Thay thế một lược đồ quan hệ

 2.5.2. Phép tách - kết nối bảo toàn thông tin

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 .

◦ Tách không mất mát về chuẩn BCNF ◦ Tách bảo toàn PTH về chuẩn 3

 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

101 102

sds

103 104

 Ví dụ phép kết

 * : là phép kết nối tự nhiên trên giao của 2 tập thuộc tính  ρ=(R1,R2,…,Rk) hay ρ=(U1,U2,…,Uk) là phép tách lược

R B
đồ trên U thành các lược đồ con trên U1,U2,…,Uk

 ri=Ui(r) là hình chiếu của quan hệ r lên tập thuộc tính Ui  mρ(r)=r1r2…rk là phép kết nối tự nhiên của các hình

chiếu của r lên các tập con trong phép tách ρ

R A B C S D E A B C D E 1 2 3 3 1 1 2 3 3 1 4 5 6 6 2 1 2 3 6 2 7 8 9 4 5 6 6 2

 Ví dụ phép kết tự nhiên

 Được dùng để lấy ra một vài cột của quan hệ R  Ký hiệu

105 106

A1, A2, …, Ak(R)

R S

 Kết quả trả về là một quan hệ

◦ Có k thuộc tính ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R

 Ví dụ

A B C D R S A B C C D 1 2 3 1 1 2 3 3 1 4 5 6 2 4 5 6 6 2 7 8 9

R A B C

A,C (R)

A,C (R)

sds

27

A C 10 1  1  20 1  1  30 1  2  40 2  107 108

NHANVIEN

PHG

HONV

TENLOT

TENNV

NGSINH DCHI

LUON G

MA_N QL

MAN V

30000 005

5

009

Đinh

Bá

Tiến

40000 006

5

Nguyễn

Thanh

Tùng

005

 Tách không mất mát (Lossless join)  Tách bảo toàn tập PTH

25000 001

4

Ngọc

Hằng

007

Bùi

PHA I Na m Na m Na m

Quỳnh

Như

001

Nữ

43000 006

4

Lê

38000 005

5

Nguyễn Mạnh

Hùng

004

25000 005

5

Trần

Thanh

Tâm

003

25000 001

4

Trần

Hồng

Quang

008

Na m Na m Na m

Nữ

55000

1

Văn

006

119 Cống Quỳnh, Tp HCM 222 Nguyễn Văn Cừ, Tp HCM 332 Nguyễn Thái Học, Tp HCM 291 Hồ Văn Huê, Tp HCM 95 Bà Rịa, Vũng Tàu 34 Mai Thị Lự, Tp HCM 80 Lê Hồng Phong, Tp HCM 45 Trưng Vương, Hà Nội

Phạm Vinh HONV, TENLOT, TENNV, LUONG(NHANVIEN) HONV

TENLOT

TENNV

Bá Đinh Thanh Nguyễn Ngọc Bùi Lê Quỳnh Nguyễn Mạnh Thanh Trần Hồng Trần Văn Phạm

Tiến Tùng Hằng Như Hùng Tâm Quang Vinh

LUON G 30000 40000 25000 43000 38000 25000 25000 55000

109 110

Phép tách - kết nối bảo toàn thông tin

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)

 Định nghĩa: Cho lược đồ quan hệ R(U), phép tách R thành các lược đồ con {R1, R2 , … , Rk} được gọi là phép tách không mất mát thông tin đối với một tập phụ thuộc hàm F nếu với mọi quan hệ r xác định trên R thỏa mãn F thì:

r= ∏R1 (r) ⋈ ∏R2 (r) ⋈ …. ⋈ ∏Rk (r)

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:

 Phép tách một lược đồ quan hệ thành các lược đồ con mà sau khi kết nối tự nhiên các lược đồ con này thì ta thu được lược đồ ban đầu

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

 Ví dụ: Tách SUPPLIERS thành S1 và S2

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.

◦ S1 (S, N, A) và F1={SNA}

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à

◦ S2 (S, I, P) và F2={SIP}

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.

sds

28

111 112

 Bước 2

 Xét SNA và cột S  Tại S có 2 hàng  b22=a2, b23=a3

 R (SNAIP) => R1(SNA) và R2(SIP)  Tập F={SNA,SIP}  Thực hiện thuật toán:

◦ Bước 1

 Ta có: k=2 và n=5

 Bước 3

 Khái niệm

◦ Là phép tách mà các PTH ban đầu có thể suy diễn được từ các PTH của các lược đồ con ◦ Các PTH ở lược đồ con không cần thuộc F

 Mục đích

113 114

Kết luận:

◦ Bảo đảm các ràng buộc toàn vẹn đối với lược

◦ Phép tách R thành R1 và R2 là không mất mát thông tin

đồ quan hệ

sds

29

115 116

Tách bảo toàn PTH (tt)

 Hình chiếu của tập phụ thuộc hàm

Cho sơ đồ quan hệ R, tập phụ thuộc hàm F, phép tách

 Xét phép tách lược đồ R(CSZ) thành R1(SZ) và R2(ZC)  Tập F={CSZ, ZC}  Ta thấy:

{R1, R2, … , Rk} của R trên F. Hình chiếu Fi của F trên Ri là tập tất cả X ⟶ Y  F+ : XY ⊆ Ri .  Phép tách sơ đồ quan hệ R thành {R1, R2, … , Rk} là một phép

tách bảo toàn tập phụ thuộc hàm F nếu (F1 ∪ F2 ∪ … ∪ Fk)+ = F+  Hay hợp của tất cả các phụ thuộc hàm trong các hình chiếu của F

◦ Với R1 chỉ có các PTH tầm thường ◦ Với R2 có ZC ◦ Cả R1 và R2 không có PTH nào có thể suy ra CSZ  Phép tách này không bảo toàn tập PTH

lên các sơ đồ con sẽ suy diễn ra các phụ thuộc hàm trong F.

 Một phép tách có thể

◦ Không mất mát thông tin và có bảo toàn tập phụ thuộc hàm

 Xét phép tách lược đồ R(SNAIP) thành R1(SNA) và R2(SIP)  Tập F={SNA, SIP}  Ta thấy:

 ((S, N,A,I,P)(S,N,A) và (S, I, P))

◦ Không mất mát thông tin và không bảo toàn tập phụ

 ((C,S,Z) (S,Z) và (Z,C))

◦ Mất mát thông tin và có bảo toàn tập phụ thuộc hàm

◦ Với R1 ngoài các PTH tầm thường còn có S NA ◦ Với R2 ngoài các PTH tầm thường còn có SI P  Phép tách trên là bảo toàn tập PTH

◦ Mất mát thông tin và không bảo toàn tập phụ thuộc hàm

117 118

sds

30

119 120

 Thuật toán

◦ Vào

 Tách không mất mát thông tin về chuẩn BCNF  Tách bảo toàn tập PTH về 3NF

 Lược đồ R

 Tập F trên R

◦ Ra

 P phép tách không mất mát R thành (R1, R2, .., Rk)

 Các Ri đều thỏa chuẩn BCNF

 Các Fi là hình chiếu của F lên Ri

 Lặp

 Vào: Cho R(CSZ) và F={CSZ, ZC}  Thực hiện:

◦ Đặt p=(R) ◦ Nếu S là một lược đồ trong p không thỏa BCNF

◦ p=R, khóa của R là CS ◦ Xét ZC. Z không là khóa của R và C không là con của Z

 S1=Z∪C =ZC

 Xét XA của S. Trong đó:  X không chứa khóa của S  A không phải là con của X

 S2={R}\C=CSZ\C=SZ

 Thay S=S1 và S2 với

 Xét S1 và S2

 S1: Có khóa là Z, phụ thuộc hàm ZZC (thỏa BCNF)

 S1= A ∪{X}  S2={S}\A

 S2: Có khóa là SZ, phụ thuộc hàm SZ SZ (thỏa BCNF)

◦ Lặp cho đến khi mọi Si đều thỏa chuẩn BC

◦ Kết thúc: p=(S1,S2)

121 122

sds

31

123 124

 Vào: Cho R(SNAIP) và F={SNA, SIP}  Thực hiện:

 Thuật toán

◦ Vào

◦ p=R, khóa của R là SI ◦ Xét SNA. S không là khóa của R và N, A không là con của S

 Lược đồ R  Tập F trên R (F là phủ tối thiểu)

◦ Ra

 S1=S ∪ {N,A} =SNA  S2={R}\NA=SNAIP\NA=SIP  Xét S1 và S2

 S1: Có khóa là S, phụ thuộc hàm SNA (thỏa BCNF)  S2: Có khóa là SI, phụ thuộc hàm SI P (thỏa BCNF)

 Phép tách R thành (R1, R2, .., Rk)  Bảo toàn F

◦ Kết thúc: p=(S1,S2)

 B1: Xét các thuộc tính trên R

◦ Nếu có các Ai không xuất hiện trong F

 Vào: Cho R(SNAIP) và F={SNA, SIP}  Thực hiện:

◦ Tách R=R1 và R2

◦ F là phủ tối thiểu ◦ Xét các thuộc tính S, N, A, I, P

 R1={Ai} và R2=R\R1

 Không có thuộc tính nào không xuất hiện trong F

 B2: Xét các phụ thuộc hàm

◦ Xét các F

◦ Nếu có Fi chứa tất cả các thuộc tính của R

 Kết luận: p=R, kết thúc

 Không có F nào chứa toàn bộ S, N,A,I,P  Xét SNA=> R1={A,N,A} (Thỏa chuẩn 3)  Xét SIP => R2={S,I,P} (Thỏa chuẩn 3)

◦ Ngược lại:

◦ Kết thúc

 Mỗi XA =>Ri={X}∪{A}

 Nếu có XA1, …,XAi =>Ri={X}∪{A1,..,Ai}

125 126

sds

32

127 128

 Vào:

Kiểm tra các phép tách sau đây xem có mất mát thông tin

◦ Cho R(ABCDEG) ◦ F = { ABC, CA,BCD, ACDB, DEG, BEC, CGBD,

hay không?

1. Cho phép tách R(CSZ) thành R1(SZ) và R2(CZ) và tập PTH

CE AG}  Thực hiện:

F={CSZ, ZC}

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}

◦ F chưa phải là phủ tối thiểu nên không áp dụng trực tiếp thuật toán ◦ F‟={AB→C, D→E, CE → G, C→A, D→ G, BC→ D, BE→C, CG→D} ◦ Xét các F

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}

 Không có F nào chứa toàn bộ R(ABCDEG)  Áp dụng thuật toán ta thu được

 Tách lược đồ trên để đạt dạng chuẩn BCNF?

p={ABC,DEG,CEG,CA,BCD,BEC,CGD}  Do CA là con của ABC nên loại CA khỏi p

◦ Kết thúc p= {ABC,DEG,CEG,BCD,BEC,CGD}

 Mô hình hoá 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ệ

129 130

Ý tưởng

Lược đồ quan hệ

HQT CSDL quan hệ

thiết kế E/R

sds

33

131 132

Thế giới thực

 Quá trình thiết kế CSDL  Mô hình thực thể - liên kết

Các yêu cầu về chức năng

Phân tích yêu cầu Các yêu cầu về dữ liệu

Phân tích chức năng

TK quan niệm

Các đặc tả chức năng Lược đồ quan niệm

Độc lập HQT

◦ Thực thể ◦ Thuộc tính ◦ Rà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ế mức logic

Lược đồ logic

Phụ thuộc HQT cụ thể

 Thiết kế  Ví dụ

Thiết kế chương trình ứng dụng

Thiết kế mức vật lý Lược đồ trong

133 134

Chương trình ứng dụng

 Quá trình thiết kế CSDL  Mô hình thực thể - liên kết  Thiết kế

◦ Các bước thiết kế ◦ Nguyên lý thiết kế

 Ví dụ

sds

34

135 136

 Xác định tập thực thể  Xác định mối quan hệ  Xác định thuộc tính và gắn thuộc tính cho tập thực thể

và mối quan hệ

 Chính xác  Tránh trùng lặp  Dễ hiểu  Chọn đúng mối quan hệ  Chọn đúng kiểu thuộc tính

 Quyết định miền giá trị cho thuộc tính  Quyết định thuộc tính khóa  Quyết định (min, max) cho mối quan hệ

137 138

sds

35

139 140

 CSDL đề án công ty theo dõi các thông tin liên quan

đến nhân viên, phòng ban và đề án ◦ Cty có nhiều đơn vị, mỗi đơn vị có tên duy nhất, mã đơn vị

duy nhất, một trưởng phòng và ngày nhận chức. Mỗi đơn vị có thể ở nhiều địa điểm khác nhau.

 Quá trình thiết kế CSDL  Mô hình thực thể - liên kết  Thiết kế  Ví dụ

◦ Dự án có tên duy nhất, mã duy nhất, do 1 một phòng ban chủ

trì và được triển khai ở 1 địa điểm.

◦ Quản lý đề án công ty

◦ Nhân viên có mã số, tên, địa chỉ, ngày sinh, giới tính và lương. Mỗi nhân viên làm việc ở 1 phòng ban, tham gia vào các đề án với số giờ làm việc khác nhau. Mỗi nhân viên đều có một người quản lý trực tiếp.

◦ Một nhân viên có thể có những người con được hưởng bảo

hiểm theo nhân viên. Mỗi người con của nhân viên có tên, giới tính, ngày sinh.

141 142

1

 Chuyển lược đồ E/R sang thiết kế quan hệ

1

Số nhân viên

N

1

1

1

1

N

M

N

N

N

sds

36

143 144

 (1) Tập thực thể

◦ Các tập thực thể (trừ tập thực thể yếu) chuyển thành các quan

 (2) Mối quan hệ ◦ (2a) Một-Một

hệ có cùng tên và tập thuộc tính

 Hoặc thêm vào quan hệ này thuộc tính khóa của quan hệ kia  Hoặc thêm thuộc tính khóa vào cả 2 quan hệ

MANV NS DCHI MAPHG LUON G TENPH G MANV HONV NS DCHI MAPHG n 1 LUON G TENPH G

Lam_viec

NHANVIEN

PHONGBAN

HONV TENNV

NHANVIEN

PHONGBAN

NG_NHANCH UC TENNV GT 1 1 1 1 GT

La_truong_phon g

La_truong_pho ng

PHONGBAN(TENPHG, MAPHG)

NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG)

PHONGBAN(MAPHG, TENPHG, MANV, NG_NHANCHUC)

 (2) Mối quan hệ ◦ (2c) Nhiều-Nhiều

 Tạo một quan hệ mới có

 (2) Mối quan hệ ◦ (2b) Một-Nhiều

 Thêm vào quan-hệ-một thuộc tính khóa của quan-hệ-nhiều

 Tên quan hệ là tên của mối quan hệ  Thuộc tính là những thuộc tính khóa của các tập thực thể liên quan

145 146

MANV DCHI MANV NS DCHI NGSIN H LUON G MAPHG HONV LUON G TENPH G HONV DDIEM_D A MADA

NHANVIEN

DEAN

n 1 TENNV

Lam_viec

NHANVIEN

PHONGBAN

THOIGIA N TENNV TENDA n n PHAI

Phan_cong

GT

PHANCONG(MANV, MADA, THOIGIAN)

NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, MAPHG)

sds

37

147 148

 (4) Thuộc tính đa trị

 (3) Thực thể yếu

◦ Chuyển thành một quan hệ

 Có cùng tên với thuộc tính đa trị  Thuộc tính khóa của quan hệ này là khóa ngoài của quan hệ chứa

◦ Chuyển thành một quan hệ  Có cùng tên với thực thể yếu  Thêm vào thuộc tính khóa của quan hệ liên quan

thuộc tính đa trị

MANV NS DCHI LUON G MANV HONV NS

BANGCA P

1 LUON G HONV

NHANVIEN

TENNV

Co_than_nha n

QUANH E NS

NHANVIEN

TENNV

BANGCAP(MANV, BANGCAP)

n GT GT GT

THANNHAN

TENTN

NHANVIEN(MANV, TENNV, HONV, NS, DCHI, GT, LUONG, DCHI)

THANNHAN(MANV, TENTN, GT, NS, QUANHE)

 (5) Liên kết đa ngôi (n>2) ◦ Chuyển thành một quan hệ

 Có cùng tên với tên mối liên kết đa ngôi  Khóa chính là tổ hợp các khóa của tập các thực thể tham gia liên

149 150

3.1. Đại số quan hệ 3.2. Ngôn ngữ truy vấn có cấu trúc SQL

kết

TENDA MADA SOLUON G

Cung_cap

DEAN

MANC C

NHACUNGCA P

MATB

THIETBI

DEAN(MADA, …)

NHACUNGCAP(MANCC ,…)

THIETBI(MATB, …)

CUNGCAP(MANCC, MATB, MADA, SOLUONG)

sds

38

151 152

 Là loại ngôn ngữ thủ tục  Bao gồm tập hợp các phép toán được áp dụng trên các

thể hiện của quan hệ. Kết quả trả về của một câu truy vấn là một thể hiện của quan hệ

 Chuỗi các phép toán đạisố quan hệ hình thành nên biểu thức đại số quan hệ (câu truy vấn) mà kết quả của nó cũng trả về một thể hiện của quan hệ

 Có 3 nhóm phép toán

◦ Phép toán tập hợp (∪, ∩, −, x, ÷) ◦ Phép toán quan hệ (π, σ, ) ◦ Phép toán khác (←, ρ)

Nội dung chi tiết  Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

 Biến là các quan hệ

◦ Tập hợp (set)

 Hằng số là thể hiện của quan hệ  Biểu thức

 Toán tử là các phép toán (operations)

◦ Trên tập hợp

◦ Được gọi là câu truy vấn ◦ Là chuỗi các phép toán đại số quan hệ ◦ Kết quả trả về là một thể hiện của quan hệ

• Hội  (union) • Giao  (intersec) • Trừ  (difference)

◦ Rút trích 1 phần của quan hệ

• Chọn  (selection) • Chiếu  (projection)

◦ Kết hợp các quan hệ

• Tích Cartesian  (Cartesian product) • Kết (join)

153 154

sds

39

155 156

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)

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

 Quan hệ là tập hợp các bộ

 Ví dụ

◦ Phép hội R  S ◦ Phép giao R  S ◦ Phép trừ R  S

157 158

NHAN_VIEN

TENNV

NGSINH

GT

THAN_NHAN

TENTN

NG_SINH

GTTN

 Tính khả hợp (Union Compatibility)

12/08/1955 Nam

Trinh

04/05/1986 Nu

Tung

◦ ĐN1: Hai lược đồ quan hệ R(A1, A2, …, An) và S(B1, B2, …, Bn) là khả

Hang

07/19/1968 Nu

Khang

10/25/1983 Nam

Nhu

06/20/1951 Nu

Phuong

05/03/1958 Nu

Hung

09/15/1962 Nam

Minh

02/28/1942 Nam

hợp nếu • Cùng bậc n • Và có DOM(Ai)=DOM(Bi) , 1 i  n

Chau

12/30/1988 Nu

◦ ĐN 2: Cho 2 quan hệ r và s, r và s được gọi là khả hợp nếu chúng được xác định trên cùng tập thuộc tính và các thuộc tính cùng tên có cùng miền giá trị

Hai quan hệ NHANVIEN và THANNHAN là khả hợp

 Kết quả của , , và  là một quan hệ có cùng tên thuộc tính với

Bậc n=3 DOM(TENNV) = DOM(TENTN) DOM(NGSINH) = DOM(NG_SINH) DOM(GT) = DOM(GTTN)

quan hệ đầu tiên (R)

sds

40

159 160

 Cho 2 quan hệ R và S khả hợp  Phép hội của R và S

 Cho 2 quan hệ R và S khả hợp  Phép giao của R và S

◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R hoặc thuộc S,

hoặc cả hai (các bộ trùng lặp sẽ bị bỏ)

◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc cả 2 quan hệ R và S

R  S = { t  tR  tS }

R  S = { t  tR  tS }

 Ví dụ

 Ví dụ

 Giao hoán

 Cho 2 quan hệ R và S khả hợp  Phép giao của R và S

A B R  S R S A B A B R S A B A B B R  S A 1  1 2   1 2 2    2  2 3   2 3   1  1  1  2  3  161 162

R  S = S  R R  S = S  R

◦ Ký hiệu R  S ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R nhưng không thuộc S

R  S = { t  tR  tS }

 Kết hợp

 Ví dụ

R S A B A B R - S A B 1  1 2   1 

R  (S  T) = (R  S)  T R  (S  T) = (R  S)  T

2 3   1 

sds

41

163 164

 F (R)

 Được dùng để lọc ra một tập con các bộ của quan hệ R  Các bộ được chọn phải thỏa mãn điều kiện chọn F  Ký hiệu

 F là biểu thức logic gồm các mệnh đề có dạng

◦ ◦

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

• gồm  ,  ,  ,  ,  ,  • Các mệnh đề được nối lại nhờ các phép  ,  , 

 Cho R là một quan hệ và F là một biểu thức logic trên các thuộc

tính của R

 Phép chọn trên quan hệ R với biểu thức chọn F

 Phép chọn có tính giao hoán

◦ Ký hiệu: F(R) ◦ Là một quan hệ gồm tập tất cả các bộ thuộc R và thỏa F

 F1 ( F2 (R)) =  F2 ( F1 (R)) =  F1  F2 (R)

F(R) = { t  tR  F(t) đúng }

 Kết quả trả về là một quan hệ

◦ Có cùng danh sách thuộc tính với R ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R

 Ví dụ

165 166

R A B C D

 (A=B)(D>5) (R)

sds

42

A B C D 1 7   5 7 1 7     12 3 23 10     23 10   167 168

 Cho biết các nhân viên ở phòng số 4

 Tìm các nhân viên có lương trên 25000 ở phòng 4 hoặc

◦ Quan hệ: NHANVIEN ◦ Thuộc tính: PHG ◦ Điều kiện: PHG=4

các nhân viên có lương trên 30000 ở phòng 5 ◦ Quan hệ: NHANVIEN ◦ Thuộc tính: LUONG, PHG ◦ Điều kiện:

 F (R)

• LUONG>25000 và PHG=4 hoặc • LUONG>30000 và PHG=5

 PHG=4 (NHANVIEN)

 (LUONG>25000  PHONG=4)  (LUONG>30000  PHONG=5) (NHAN_VIEN)

 Được dùng để lấy ra một vài cột của quan hệ R  Cho R là một quan hệ n ngôi xác định tren tập thuộc tính U={A1,A2, …, An} và một tập con thuộc tính XU.

 Phép chiếu của quan hệ R trên tập thuộc tính X

◦ Ký hiệu: X(R) ◦ Là một quan hệ gồm tập tất cả các bộ của R xác định trên tập thuộc

tính X

169 170

X(R)={t[X]  tR}

 Kết quả trả về là một quan hệ

 Ví dụ

◦ Có k thuộc tính ◦ Có số bộ luôn ít hơn hoặc bằng số bộ của R A,C (R)

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

sds

43

A C R B C A 1  1  10 1  1  20 1  2  30 1  40 2  171 172

 Phép chiếu không có tính giao hoán

 Cho biết họ tên và lương của các nhân viên

◦ Quan hệ: NHANVIEN ◦ Thuộc tính: HONV, TENNV, LUONG

X,Y (R) = X (Y (R))

HONV, TENNV, LUONG(NHANVIEN)

A1, A2, …, An(A1, A2, …, Am(R)) =

A1, A2, …, An (R) , với n  m

 Cho biết mã nhân viên có người thân và có tham gia đề

 Cho biết mã nhân viên có tham gia đề án hoặc có thân

nhân ◦ Quan hệ: THANNHAN, DEAN ◦ Thuộc tính: MANV

MANV(DEAN) MANV(THANNHAN)

án ◦ Quan hệ: THANNHAN, DEAN ◦ Thuộc tính: MANV MANV(DEAN) MANV(THANNHAN)

173 174

MANV(DEAN)  MANV(THANNHAN)

MANV(DEAN) MANV(THANNHAN)

sds

44

175 176

 Cho biết mã nhân viên không có thân nhân nào

 Mở rộng phép chiếu bằng cách cho phép sử dụng các

phép toán số học trong danh sách thuộc tính  Ký hiệu F1, F2, …, Fn (E) ◦ E là biểu thức ĐSQH ◦ F1, F2, …, Fn là các biểu thức số học liên quan đến

◦ Quan hệ: NHANVIEN, THANNHAN ◦ Thuộc tính: MANV MANV(NHANVIEN) MANV(THANNHAN)

• Hằng số • Thuộc tính trong E

MANV(NHANVIEN) - MANV(THANNHAN)

 Cho biết họ tên của các nhân viên và lương của họ sau

 Được sử dụng để nhận lấy kết quả trả về của một phép

khi tăng 10%

toán ◦ Thường là kết quả trung gian trong chuỗi các phép toán

 Ký hiệu 

177 178

HONV, TENNV, LUONG1.1 (NHANVIEN)

 Ví dụ

S  P (R)

◦ THETINDUNG(MATHE, TRIGIATHE, SOTIENSD) ◦ Cho biết số tiền còn lại trong mỗi thẻ

 Ví dụ ◦ B1 ◦ B2

KQ  A1, A2, …, Ak (S)

MATHE, TRIGIATHE  SOTIENSD (THETINDUNG)

sds

45

179 180

 Được dùng để đổi tên

 Cho biết họ và tên nhân viên làm việc ở phòng số 4

◦ Quan hệ

Xét quan hệ R(B, C, D) S(R) : Đổi tên quan hệ R thành S

 C1:  C2:

◦ Thuộc tính

HONV, TENNV (PHG=4 (NHANVIEN)) NV_P4  PHG=4 (NHANVIEN) KQ  HONV, TENNV (NV_P4)

X, C, D (R) : Đổi tên thuộc tính B thành X

KQ(HO, TEN)  HONV, TENNV (NV_P4) KQ(HO, TEN) (HONV, TENNV (NV_P4))

Đổi tên quan hệ R thành S và thuộc tính B thành X

S(X,C,D)(R)

 Được dùng để kết hợp các bộ của các quan hệ lại với nhau  Cho quan hệ R, S tương ứng xác định trên tập thuộc tính {A1,A2,…,An}

và {B1,B2, …, Bm} .

 Phép tích Đề - các của 2 quan hệ R và S

◦ Ký hiệu: R x S ◦ Là một quan hệ gồm tập tất cả các (m+n) - bộ có n thành phần đầu tiên là

181 182

một bộ thuộc R và m thành phần sau là một bộ thuộc S R  S={t  t=(a1,..an,b1,..bm)  (a1,..an)  R  (b1,..bm)  S }

 Kết quả trả về là một quan hệ Q

◦ Mỗi bộ của Q là tổ hợp giữa 1 bộ trong R và 1 bộ trong S ◦ Nếu R có u bộ và S có v bộ thì Q sẽ có u  v bộ ◦ Nếu R có n thuộc tính và S có m thuộc tính thì Q sẽ có n + m thuộc tính

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

(R+  S+   )

sds

46

183 184

 Ví dụ

 Ví dụ

R  S

R  S

(X,C,D) (S)

R A B R A B A B C D E A B X C D 1 1   2 2   1 10 + 1 10 +     1 10 + 1 10 +     1 20 - 1 20 -     C D E S S S X B C C D D 1 10 - 1 10 -     2 10 + 2 10 +     10 + 10 10 + +    2 10 + 2 10 +     10 + 10 10 + +    2 20 - 2 20 -     20 - 20 20 - -    2 10 - 2 10 -     10 - 10 10 - -   

 Ví dụ

 Thông thường theo sau phép tích Cartesian là phép

185 186

Kí hiệu rõ ràng

chọn

R A B A C D R  S

R  S

1 

A=S.B (R  S)

R. B 1 10 +  S. B  2  1 10 +   A R.B S.B C D R.B S.B C A D 1 20 -   1 10 + 1 + 10     1 10 -   S B C D 2 10 + 1 + 10     2 10 +   10 + 2 20 - 1 - 20      2 10 +   10 + 1 - 10    2 20 -   20 - 2 + 10    2 10 -   10 - 2 + 10    2 - 20   2 - 10  

sds

47

187 188

◦ Quan hệ: PHONG_BAN, NHAN_VIEN ◦ Thuộc tính: TRPHG, MAPHG, TENNV, HONV, …

 Với mỗi phòng ban, cho biết thông tin của người trưởng phòng

PB_NV  PHONGBAN x NHANVIEN

 TRPHG=MANV (PB_NV)

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết

◦ Kết tự nhiên (Natural join) ◦ Kết có điều kiện tổng quát (Theta join) ◦ Kết bằng (Equi join)

 Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

TENPHG MAPHG TRPHG MANV TENNV HONV … NG_NHANCH UC 05/22/1988 Tung Nguyen 5 … 01/01/1995 Hung Nguyen Nghien cuu Dieu hanh 4 … 06/19/1981 Vinh Pham Quan ly 1 … 33344555 5 98798798 7 88866555 5 33344555 5 98798798 7 88866555 5

 Được dùng để tổ hợp 2 bộ có liên quan từ 2 quan hệ

 Phân loại

thành 1 bộ

◦ Kết theta (theta join) là phép kết có điều kiện

 Ký hiệu R S

• Ký hiệu R C S • C gọi là điều kiện kết trên thuộc tính

◦ R(A1, A2, …, An) và S(B1, B2, …, Bm)  Kết quả của phép kết là một quan hệ Q

◦ Kết bằng (equi join) khi C là điều kiện so sánh bằng

◦ Có n + m thuộc tính Q(A1, A2, …, An, B1, B2, …, Bm) ◦ Mỗi bộ của Q là tổ hợp của 2 bộ trong R và S, thỏa mãn một số

◦ Kết tự nhiên (natural join)

• Ký hiệu R S hay R  S • R+  S+   • Kết quả của phép kết bằng bỏ bớt đi 1 cột giống nhau

điều kiện kết nào đó • Có dạng Ai  Bj • Ai là thuộc tính của R, Bj là thuộc tính của S • Ai và Bj có cùng miền giá trị •  là phép so sánh , , , , , 

189 190

sds

48

191 192

 Ví dụ phép kết theta

 Ví dụ phép kết bằng

R S A B C D E

R C=D S B A

C D E

R B
1 2 3 3 1 1 2 3 3 1 R S A B C D E A B C D E 4 5 6 6 2 4 5 6 6 2 1 2 3 3 1 1 2 3 3 1 7 8 9 4 5 6 6 2 1 2 3 6 2 7 8 9 4 5 6 6 2

R C=S.C S

R S S A B C D D A B C D 1 2 3 C S. C 3 3 1 1 1 2 3 S. C 3 1

R C S = C(R  S)

(S.C,D) S

4 5 6 6 6 2 2 4 5 6 6 2 7 8 9

 Với mỗi nhân viên, hãy cho biết thông tin của phòng ban mà họ

 Ví dụ phép kết tự nhiên

193 194

R S

đang làm việc ◦ Quan hệ: NHANVIEN, PHONGBAN ◦ Thuộc tính: MANV,TENNV,…,PHG, TENPHG,…

A A B B C C D R S A B C C D 1 1 2 2 3 3 D S. C 1 3 1 1 2 3 3 1 4 4 5 5 6 6 2 6 2 4 5 6 6 2 7 8 9

sds

49

195 196

 Tập các phép toán , , , ,  được gọi là tập đầy đủ

các phép toán ĐSQH ◦ Nghĩa là các phép toán có thể được biểu diễn qua chúng ◦ Ví dụ

• RS = RS  ((RS)  (SR)) • R CS = C(RS)

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

 Được dùng để lấy ra một số bộ trong quan hệ R sao cho

 Ví dụ

197 198

R  S

thỏa với tất cả các bộ trong quan hệ S

 Ký hiệu R  S ◦ R(Z) và S(X)

• Z là tập thuộc tính của R, X là tập thuộc tính của S • X  Z

 Kết quả của phép chia là một quan hệ T(Y)

◦ Với Y=Z-X ◦ Có t là một bộ của T nếu với mọi bộ tSS, tồn tại bộ tRR thỏa

S D E A B C R A B C D E a 1 a a a 1     b 1 a a a 1     a b 1   a a 1   a b 3   a a 1   a b 1   a b 1  

2 điều kiện • tR(Y) = t • tR(X) = tS(X)

sds

50

R(Z) S(X) T(Y ) X Y 199 200

 Cho biết mã nhân viên tham gia tất cả các đề án

 Cho biết mã nhân viên tham gia tất cả các đề án do

◦ Quan hệ: PHANCONG, DEAN ◦ Thuộc tính: MANV

phòng số 4 phụ trách ◦ Quan hệ: PHANCONG, DEAN ◦ Thuộc tính: MANV ◦ Điều kiện: PHG=4

 Cho một CSDL gồm 3 quan hệ:

◦ NhaCC(MaNCC,TenNCC,Tinhtrang,Diachi)

 Biểu diễn phép chia thông qua tập đầy đủ các phép toán

◦ Hanghoa(MaHH,TenHH,Mausac,Trongluong,Diachi)

◦ Cungcap(MaNCC,MaHH,Soluong)

 Biểu diễn các câu hỏi sau bằng ĐSQH

1. Đưa ra danh sách các hàng hóa có màu đỏ?

201 202

ĐSQH Q1  Y (R) Q2  Q1  S

2. Đưa ra MaNCC của các hãng cung ứng mặt hàng P1 hoặc P2?

3. Đưa ra MaNCC của các hãng cung ứng cả 2 mặt hàng P1 và P2?

4. Đưa ra MaNCC của các hãng cung ứng ít nhất một mặt hàng

Q3  Y(Q2  R) T  Q1  Q3

màu đỏ?

5. Đưa ra MaNCC của các hãng cung ứng tất cả các mặt hàng?

sds

51

203 204

 Biểu diễn các câu hỏi sau bằng ĐSQH

1. Đưa ra danh sách các hàng hóa có màu đỏ?

 Mausac=“Đỏ”(Hanghoa)

2. Đưa ra MaNCC của các hãng cung ứng mặt hàng P1 hoặc P2?

 MaNCC(MaHH=“P1” v MaHH=“p2” (Cungcap))

3. Đưa ra MaNCC của các hãng cung ứng cả 2 mặt hàng P1 và P2?

 MaNCC(MaHH=“P1”(Cungcap))MaNCC(MaHH=“p2” (Cungcap)) 4. Đưa ra MaNCC của các hãng cung ứng ít nhất một mặt hàng màu đỏ?

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác

 MaNCC(Cungcap (Mausac=“Đỏ” (Hanghoa))

5. Đưa ra MaNCC của các hãng cung ứng tất cả các mặt hàng?

◦ Hàm kết hợp (Aggregation function) ◦ Phép gom nhóm (Grouping) ◦ Phép kết ngoài (Outer join)

 MaNCC, MaHH(Cungcap)  MaHH(Hanghoa)

 Các thao tác cập nhật trên quan hệ

205 206

 Nhận vào tập hợp các giá trị  Trả về một giá trị đơn  Gồm

 Ví dụ

R A B 1 2 3 4 1 2

SUM(B) = 10 AVG(A) = 1.5 MIN(A) = 1 MAX(B) = 4 COUNT(A) = 4

◦ AVG ◦ MIN ◦ MAX ◦ SUM ◦ COUNT

1 2

sds

52

207 208

 Được dùng để phân chia quan hệ thành nhiều nhóm dựa

 Ví dụ

trên điều kiện gom nhóm nào đó

SUM(C)(R)

 Ký hiệu

G1, G2, …, GnF1(A1), F2(A2), …, Fn(An)(E)

R A B C SUM_C 27 2 7  4 7  2 3  2 10 

ASUM(C)(R)

◦ E là biểu thức ĐSQH ◦ G1, G2, …, Gn là các thuộc tính gom nhóm ◦ F1, F2, …, Fn là các hàm ◦ A1, A2, …, An là các thuộc tính tính toán trong hàm F

SUM_C 14 3 10 A   

 Mở rộng phép kết để tránh mất mát thông tin

◦ Thực hiện phép kết ◦ Lấy thêm các bộ không thỏa điều kiện kết

 Có 3 hình thức

◦ Mở rộng bên trái ◦ Mở rộng bên phải ◦ Mở rộng 2 bên

209 210

sds

53

211 212

 Cho biết họ tên nhân viên và tên phòng ban mà họ là

trưởng phòng nếu có

R1  NHANVIEN MANV=TRPHG PHONGBAN KQ  HONV,TENNV, TENPHG (R1)

 Giới thiệu  Đại số quan hệ  Phép toán tập hợp  Phép chọn  Phép chiếu  Phép tích Cartesian  Phép kết  Phép chia  Các phép toán khác  Các thao tác cập nhật trên quan hệ

TENNV HONV TENPHG Tung Nguyen Hang Bui Nghien cuu null Nhu Le null Vinh Pham Quan ly

 Nội dung của CSDL có thể được cập nhật bằng các

 Được diễn đạt

213 214

Rnew  Rold  E

thao tác ◦ Thêm (insertion) ◦ Xóa (deletion) ◦ Sửa (updating)

 Các thao tác cập nhật được diễn đạt thông qua phép

◦ R là quan hệ ◦ E là một biểu thức ĐSQH

toán gán

 Ví dụ

◦ Phân công nhân viên có mã 123456789 làm thêm đề án mã số

20 với số giờ là 10

Rnew  các phép toán trên Rold

PHANCONG  PHANCONG  (‘123456789’, 20, 10)

sds

54

215 216

 Được diễn đạt

 Được diễn đạt

Rnew  Rold  E

Rnew  F1, F2, …, Fn (Rold)

◦ R là quan hệ ◦ E là một biểu thức ĐSQH

◦ R là quan hệ ◦ Fi là biểu thức tính toán cho ra giá trị mới của thuộc tính

 Ví dụ

 Ví dụ

◦ Xóa các phân công đề án của nhân viên 123456789

◦ Tăng thời gian làm việc cho tất cả nhân viên lên 1.5 lần

PHANCONG  PHANCONG  MANV=‘123456789’(PHANCONG)

PHANCONG MANV, SODA, THOIGIAN1.5(PHANCONG)

217 218

3.1. Đại số quan hệ 3.2. Ngôn ngữ truy vấn có cấu trúc SQL

 Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index)

sds

55

219 220

 Một DBMS phải có ngôn ngữ giao tiếp giữa NSD với CSDL,

 Ngôn ngữ ĐSQH

bao gồm:

 Ngôn ngữ định nghĩa dữ liệu (Data Definition Language - DDL):

◦ Cách thức truy vấn dữ liệu ◦ Khó khăn cho người sử dụng

cho phép khai báo cấu trúc bảng, các mối quan hệ và các ràng buộc.

 SQL (Structured Query Language)

 Ngôn ngữ thao tá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 phép truy vấn dữ liệu.

◦ Ngôn ngữ cấp cao ◦ Người sử dụng chỉ cần đưa ra nội dung cần truy vấn ◦ Được phát triển bởi IBM (1970s) ◦ Được gọi là SEQUEL (Structured English Query Language) ◦ Được ANSI (American National Standard Institute) công nhận

 Ngôn ngữ điều khiển dữ liệu (Data Control Language – DCL): khai báo bảo mật thông tin, cấp quyền và thu hồi quyền khai thác trên cơ sở dữ liệu.

và phát triển thành chuẩn • SQL-86 • SQL-92 • SQL-99

 SQL gồm

 Giới thiệu  Định nghĩa dữ liệu (DDL)

◦ Kiểu dữ liệu ◦ Các lệnh định nghĩa dữ liệu

◦ Định nghĩa dữ liệu (DDL) ◦ Thao tác dữ liệu (DML) ◦ Định nghĩa khung nhìn ◦ Ràng buộc toàn vẹn ◦ Phân quyền và bảo mật ◦ Điều khiển giao tác  SQL sử dụng thuật ngữ

 Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index)

◦ Bảng ~ quan hệ ◦ Cột ~ thuộc tính ◦ Dòng ~ bộ

221 222

sds

56

223 224

 Là ngôn ngữ mô tả

 Số (numeric)

◦ Lược đồ cho mỗi quan hệ ◦ Miền giá trị tương ứng của từng thuộc tính ◦ Ràng buộc toàn vẹn ◦ Chỉ mục trên mỗi quan hệ

 Gồm

◦ INTEGER ◦ SMALLINT ◦ NUMERIC, NUMERIC(p), NUMERIC(p,s) ◦ DECIMAL, DECIMAL(p), DECIMAL(p,s) ◦ REAL ◦ DOUBLE PRECISION ◦ FLOAT, FLOAT(p)

◦ CREATE TABLE (tạo bảng) ◦ DROP TABLE (xóa bảng) ◦ ALTER TABLE (sửa bảng) ◦ CREATE DOMAIN (tạo miền giá trị) ◦ CREATE DATABASE (tạo cơ sở dữ liệu) ◦ …

 Chuỗi ký tự (character string)

◦ CHARACTER, CHARACTER(n) ◦ CHARACTER VARYING(x)

 Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)

 Chuỗi bit (bit string)

◦ BIT, BIT(x) ◦ BIT VARYING(x)

 Ngày giờ (datetime)

◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số kiểu truy vấn khác  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index)

◦ DATE gồm ngày, tháng và năm ◦ TIME gồm giờ, phút và giây ◦ TIMESTAMP gồm ngày và giờ

225 226

sds

57

227 228

 Gồm 3 mệnh đề

 Là ngôn ngữ rút trích dữ liệu

◦ Thường đi kèm với một số điều kiện nào đó

SELECT FROM [WHERE] <điều kiện>

◦

Phép toán ĐSQH

Một số bổ sung



• Tên các cột cần được hiển thị trong kết quả truy vấn

 Dựa trên

◦

◦ Cho phép kết quả trả về của bảng có nhiều dòng trùng nhau

• Tên các bảng liên quan đến câu truy vấn

◦ <điều kiện>

• Biểu thức boolean xác định dòng nào sẽ được rút trích • Nối các biểu thức: AND, OR, và NOT • Phép toán:  ,  , , , , , LIKE và BETWEEN

229 230

SELECT FROM WHERE <điều kiện>

SELECT FROM [WHERE <điều kiện>] [GROUP BY ] [HAVING <điều kiện trên nhóm>] [ORDER BY ]

sds

58

231 232

Lấy tất cả các cột của quan hệ kết quả

SELECT FROM NHANVIEN WHERE PHG=5

SELECT MANV, HONV, TENLOT, TENNV FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’

MANV HONV TENNV NGSINH DCHI GT LUONG MA_NQL PHG MANV HONV TENNV TENLO T Thanh Nguyen Tung Nam 40000 5 TENLO T Thanh Tung Nguyen Manh Hung 638 NVC Q5 Ba Ria VT Nam 38000 5 3334455 55 9879879 87 12/08/19 55 09/15/19 62 8886655 55 3334455 55 Manh Hung 3334455 55 9879879 87 Nguye n Nguye n

233 234

Tên bí danh Mở rộng

SELECT MANV, HONV + ‘ ’ + TENLOT + ‘ ’ + TENNV AS ‘HO TEN’ FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’

SELECT MANV, HONV AS HO, TENLOT AS ‘TEN LOT’, TENNV AS TEN FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’

MANV HO TEN LOT TEN MANV HO TEN Thanh Tung Manh Hung 3334455 55 9879879 87 Nguye n Nguye n 3334455 55 9879879 87 Nguyen Thanh Tung Nguyen Manh Hung

sds

59

235 236

Mở rộng Loại bỏ các dòng trùng nhau

SELECT DISTINCT LUONG SELECT LUONG FROM NHANVIEN FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’ WHERE PHG=5 AND GT=‘Nam’

SELECT MANV, LUONG1.1 AS ‘LUONG10%’ FROM NHANVIEN WHERE PHG=5 AND GT=‘Nam’

MANV LUONG10% LUONG LUONG 33000 30000 30000 27500 25000 25000 3334455 55 9879879 87 38000 25000

- Tốn chi phí - Người dùng muốn thấy

38000

 Cho biết MANV và TENNV làm việc ở phòng „Nghien

cuu‟

237 238

Biểu thức luận lý

MANV, TENNV

SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG

NHANVIEN, PHONGBAN

SELECT FROM WHERE

AND

TENPHG=‘Nghien cuu’

PHG=MAPH G

TRUE

TRUE

sds

60

239 240

BETWEEN NOT BETWEEN

SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG>=20000 AND LUONG<=30000

SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG NOT BETWEEN 20000 AND 30000

SELECT MANV, TENNV FROM NHANVIEN WHERE LUONG BETWEEN 20000 AND 30000

241 242

LIKE NOT LIKE

SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen _ _ _ _’

SELECT MANV, TENNV FROM NHANVIEN WHERE HONV LIKE ‘Nguyen’

Ký tự bất kỳ

SELECT MANV, TENNV FROM NHANVIEN WHERE DCHI LIKE ‘Nguyen %’

SELECT MANV, TENNV FROM NHANVIEN WHERE HONV NOT LIKE ‘Nguyen’

sds

61

Chuỗi bất kỳ 243 244

Ngày giờ Không sử dụng mệnh đề WHERE

SELECT MANV, MAPHG FROM NHANVIEN, PHONGBAN

SELECT MANV, TENNV FROM NHANVIEN WHERE NGSINH BETWEEN ‘1955-12-08’ AND ‘1966-07-19’

WHERE TRUE

MANV MAPHG 1

’17:30:00’

‘1955-12-08’

HH:MI:SS 4 YYYY-MM-DD 5

’12/08/1955’

’05:30 PM’

MM/DD/YYY Y 1

‘December 8, 1955’

4 5 3334455 55 3334455 55 3334455 55 9879879 87 9879879 87 9879879 87 … …

‘1955-12-08 17:30:00’

 Dùng để hiển thị kết quả câu truy vấn theo một thứ tự

245 246

nào đó  Cú pháp

Tên bí danh

SELECT TENPHG, DIADIEM SELECT TENPHG, DIADIEM FROM PHONGBAN, FROM PHONGBAN AS PB, DDIEM_PHG AS DDIEM_PHG DD WHERE PB.MAPHG=DD.MAPHG WHERE MAPHG=MAPHG

SELECT FROM WHERE <điều kiện> ORDER BY

◦ ASC: tăng (mặc định) ◦ DESC: giảm

SELECT TENNV, NGSINH, TENTN, SELECT TENNV, NV.NGSINH, TENTN, TN.NGSINH NGSINH FROM NHANVIEN, THANNHAN FROM NHANVIEN NV, THANNHAN TN WHERE MANV=MA_NVIEN WHERE MANV=MA_NVIEN

sds

62

247 248

 Ví dụ

 Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)

SELECT MA_NVIEN, SODA FROM PHANCONG ORDER BY MA_NVIEN DESC, SODA

◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác  Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index)

 SQL có cài đặt các phép toán

 Cú pháp

MA_NVIEN SODA 999887777 10 999887777 30 987987987 10 987987987 30 987654321 10 987654321 20 987654321 30 249 250

SELECT FROM WHERE <điều kiện> UNION [ALL] SELECT FROM WHERE <điều kiện>

◦ Hội (UNION) ◦ Giao (INTERSECT) ◦ Trừ (EXCEPT hoặc MINUS)

SELECT FROM WHERE <điều kiện> INTERSECT [ALL] SELECT FROM WHERE <điều kiện>

 Kết quả trả về là tập hợp ◦ Loại bỏ các bộ trùng nhau ◦ Để giữ lại các bộ trùng nhau

INTERSECT ALL

• UNION ALL • • EXCEPT ALL

SELECT FROM WHERE <điều kiện> EXCEPT [ALL] SELECT FROM WHERE <điều kiện>

sds

63

251 252

 Các câu lệnh SELECT có thể lồng nhau ở nhiều mức  Các câu truy vấn con trong cùng một mệnh đề WHERE

được kết hợp bằng phép nối logic

SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG

 Câu truy vấn con thường trả về một tập các giá trị  Mệnh đề WHERE của câu truy vấn cha

◦ ◦ So sánh tập hợp thường đi cùng với một số toán tử

IN, NOT IN

Câu truy vấn cha (Outer query)

• • ALL • ANY hoặc SOME ◦ Kiểm tra sự tồn tại

SELECT FROM WHERE (

Câu truy vấn con (Subquery)

SELECT FROM WHERE <điều kiện>)

• EXISTS • NOT EXISTS

 Có 2 loại truy vấn lồng

◦ Lồng phân cấp

• Mệnh đề WHERE của truy vấn con không tham chiếu đến thuộc

253 254

SELECT MANV, TENNV , PHG FROM NHANVIEN, PHONGBAN WHERE DIADIEM=‘TP HCM’ AND PHG=MAPHG

tính của các quan hệ trong mệnh đề FROM ở truy vấn cha • Khi thực hiện, câu truy vấn con sẽ được thực hiện trước, 1 lần

◦ Lồng tương quan

• Mệnh đề WHERE của truy vấn con tham chiếu ít nhất một thuộc

tính của các quan hệ trong mệnh đề FROM ở truy vấn cha

• Khi thực hiện, câu truy vấn con sẽ được thực hiện nhiều lần, mỗi

lần tương ứng với một bộ của truy vấn cha

SELECT MANV, TENNV, PHG FROM NHANVIEN WHERE PHG IN

( SELECT MAPHG FROM PHONGBAN WHERE DIADIEM=‘TP HCM’ )

sds

64

255 256

SELECT MANV, TENNV FROM NHANVIEN, PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG

 Giới thiệu  Định nghĩa dữ liệu  Truy vấn dữ liệu ◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác

SELECT MANV, TENNV FROM NHANVIEN WHERE EXISTS ( SELECT FROM PHONGBAN WHERE TENPHG=‘Nghien cuu’ AND PHG=MAPHG )

 Cập nhật dữ liệu  Khung nhìn (view)  Chỉ mục (index)

 COUNT

 Tìm tổng lương, lương cao nhất, lương thấp nhất và lương

◦ COUNT() đếm số dòng ◦ COUNT() đếm số giá trị khác NULL của

thuộc tính

◦ COUNT(DISTINCT ) đếm số giá trị khác

nhau và khác NULL của thuộc tính

trung bình của các nhân viên SELECT SUM(Luong), MAX(LUONG), MIN(LUONG), avg(LUONG) FROM NHANVIEN

 MIN  MAX  SUM  AVG

 Các hàm kết hợp được đặt ở mệnh đề SELECT

257 258

sds

65

259 260

 Cú pháp

 Cho biết số lượng nhân viên của phòng „Nghien cuu‟ SELECT COUNT(MANV) FROM NHANVIEN nv, PHONGBAN pb WHERE nv.PHG = pb.MAPHG AND pb.TENPHONG

SELECT FROM WHERE <điều kiện> GROUP BY

= 'Nghien Cuu'

 Sau khi gom nhóm

◦ Mỗi nhóm các bộ sẽ có cùng giá trị tại các thuộc tính gom

nhóm

 Cú pháp

 Cho biết số lượng nhân viên của từng phòng ban SELECT PHG, COUNT(MANV) AS SL_NV FROM NHANVIEN

261 262

SELECT FROM WHERE <điều kiện> GROUP BY HAVING <điều kiện trên nhóm>

GROUP BY PHG

sds

66

263 264

 Cho biết những nhân viên tham gia từ 2 đề án trở lên

 Cho biết những phòng ban (TENPHG) có lương trung

bình của các nhân viên lớn lơn 20000

SELECT MA_NVIEN, COUNT() AS SL_DA, FROM PHANCONG GROUP BY MA_NVIEN HAVING COUNT(SODA)>2

SELECT TENPHG, AVG(LUONG) AS LUONGTB_PB FROM PHONGBAN pb, NHANVIEN nv WHERE pb.MAPHG=nv.PHG GROUP BY PHG HAVING AVG(LUONG)>20000

 Mệnh đề GROUP BY

 Với mỗi phòng cho biêt tên phòng và số lượng nhân

viên của phòng

◦ Các thuộc tính trong mệnh đề SELECT (trừ những thuộc tính trong các hàm kết hợp) phải xuất hiện trong mệnh đề GROUP BY

 Mệnh đề HAVING

◦ Sử dụng các hàm kết hợp trong mệnh đề SELECT để kiểm tra

một số điều kiện nào đó

SELECT pb.TENPHONG, count() FROM NHANVIEN nv, PHONGBAN pb WHERE nv.phg = pb.MAPHG GROUP BY pb.MAPHG, pb.TENPHONG

◦ Chỉ kiểm tra điều kiện trên nhóm, không là điều kiện lọc trên

từng bộ

◦ Sau khi gom nhóm điều kiện trên nhóm mới được thực hiện

265 266

sds

67

267 268

 Thứ tự thực hiện câu truy vấn có mệnh đề GROUP BY

và HAVING ◦

◦

 Tìm những phòng ban có lương trung bình cao nhất.  Tìm những nhân viên có lương cao nhất. SELECT FROM NHANVIEN WHERE LUONG >= ALL (

SELECT LUONG FROM NHANVIEN)

◦ ◦

◦

(1) Chọn ra những dòng thỏa điều kiện trong mệnh đề WHERE (2) Những dòng này sẽ được gom thành nhiều nhóm tương ứng với mệnh đề GROUP BY (3) Áp dụng các hàm kết hợp cho mỗi nhóm (4) Bỏ qua những nhóm không thỏa điều kiện trong mệnh đề HAVING (5) Rút trích các giá trị của các cột và hàm kết hợp trong mệnh đề SELECT

 Tìm 3 nhân viên có lương cao nhất

 Tìm những phòng ban có lương trung bình cao nhất.

◦ B1: Tìm lương trung bình của từng phòng ban

SELECT TOP 3 FROM NHANVIEN ORDER BY LUONG DESC

269 270

SELECT PHG, AVG(LUONG) FROM NHANVIEN GROUP BY PHG HAVING AVG(LUONG) >= ALL(

SELECT AVG(LUONG) FROM NHANVIEN GROUP BY PHG )

sds

68

271 272

 Truy vấn con ở mệnh đề FROM

 Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)

 Điều kiện kết ở mệnh đề FROM

◦ Phép kết tự nhiên ◦ Phép kết ngoàI

◦ Truy vấn cơ bản ◦ Tập hợp, so sánh tập hợp và truy vấn lồng ◦ Hàm kết hợp và gom nhóm ◦ Một số dạng truy vấn khác

 Cấu trúc CASE

 Cập nhật dữ liệu (DML)  Khung nhìn (View)  Chỉ mục (Index)

 Giới thiệu  Định nghĩa dữ liệu (DDL)  Truy vấn dữ liệu (DML)  Cập nhật dữ liệu (DML)

273 274

SELECT FROM [WHERE <điều kiện>] [GROUP BY ] [HAVING <điều kiện trên nhóm>] [ORDER BY ]

◦ Thêm (insert) ◦ Xóa (delete) ◦ Sửa (update)

 Khung nhìn (View)  Chỉ mục (Index)

sds

69

275 276

Bài giảng Cơ sở dữ liệu 1 - ĐH Thương Mại

KHOA HTTT KINH TẾ & TMĐT

BÀI GIẢNG HỌC PHẦN

Bộ môn: Tin học

Người sử dụng/Lập trình viên

Chương trình ứng dụng/Truy vấn

Xử lý truy vấn

Truy xuất dữ liệu

CSDL

Định nghĩa CSDL

Thể hiện

Thuộc tính

TENNV Tung Hang Nhu Hung

Lược đồ CSDL

Lược đồ quan hệ

NHANVIEN(MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG)

PHONGBAN(MAPHG, TENPHG, TRPHG, NG_NHANCHUC)

NHANVIEN(MANV, TENNV, HONV, NS, DIACHI, GT, LUONG, PHG)

DIADIEM_PHG(MAPHG, DIADIEM)

THANNHAN(MA_NVIEN, TENTN, GT, NS, QUANHE)

Là tập hợp

DEAN(TENDA, MADA, DDIEM_DA, PHONG)

Không được chấp nhận

Dữ liệu cụ thể của thuộc tính

Phim(Tênphim, Nămsx, Thờilượng, Loạiphim, Xưởngsx, Diễnviên)

Tênphim

Nămsx

Thờilượng

Loạiphim

Xưởngsx

Diễnviên

t1[X]=t2[X] thì t1[Y] = t2[Y]

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  Thờilượng

Tênphim, Nămsx  Loại

Tênphim, Nămsx  Xưởngsx

Không phải PTH

Tênphim, Nămsx  Diễnviên

Thuật toán:

X U

Phương pháp: lần lượt tính các tập X0, X1, X2,…theo các bước sau:

 Tìm (BD)+ Giải:  X0 = BD  Tìm các PTH có VT là B, D hoặc BD: ◦ Có DEG X1=X0  {EG}= BDEG

Nếu Y Z F và Y Xi-1 và A Z và A không thuộc Xi-1 thì Xi=Xi-

ngược lại, Xi = Xi-1 .

1 A