Bài giảng Nhập môn cơ sở dữ liệu - Nguyễn Thị Mỹ Linh

277 trang phuongnguyen 8810

Download

Bạn đang xem 20 trang mẫu của tài liệu "Bài giảng Nhập môn cơ sở dữ liệu - Nguyễn Thị Mỹ Linh", để tải tài liệu gốc về máy bạn click vào nút DOWNLOAD ở trên

Tài liệu đính kèm:

bai_giang_nhap_mon_co_so_du_lieu_nguyen_thi_my_linh.ppt

Nội dung text: Bài giảng Nhập môn cơ sở dữ liệu - Nguyễn Thị Mỹ Linh

NHẬP MÔN CƠ SỞ DỮ LIỆU GV: Nguyễn Thị Mỹ Linh
NỘI DUNG MÔN HỌC • Chương 1: Giới thiệu chung về lý thuyết CSDL • Chương 2: Mô hình thực thể kết hợp • Chương 3: Mô hình quan hệ • Chương 4: Chuyển từ mô hình thực thể kết hợp sang mô hình quan hệ • Chương 5: Ngôn ngữ truy vấn SQL • Chương 6: Chuẩn hóa dữ liệu • Chương 7: Thiết kế vật lý cơ sở dữ liệu • Chương 8: Thủ tục lưu trữ và trigger • Chương 9: Bảo mật và quyền của người dùng • Tổng cộng: 45 LT+60TH
NỘI DUNG MÔN HỌC CHƯƠNG TÊN CHƯƠNG LÝ THUYẾT THỰC HÀNH Giới thiệu chung về lý 1 3 0 thuyết CSDL 2 Mô hình thực thể kết hợp 3 0 3 Mô hình quan hệ 6 0 Chuyển từ mô hình thực 4 thể kết hợp sang mô hình 3 0 quan hệ 5 Ngôn ngữ truy vấn SQL 12 30 6 Chuẩn hóa dữ liệu 6 0 Thiết kế vật lý cơ sở dữ 7 3 0 liệu 8 Thủ tục lưu trữ và trigger 6 24 Bảo mật và quyền của 9 3 6 người dùng Tổng cộng: 45 60
TÀI LIỆU THAM KHẢO • [1]Concepts of Database management – Philip J. Pratt & Joseph J. Adamski – Thomson , 5th edition • [2] Database Systems: Design, Implementation & Management – Peter Rob & Carlos Coronel – Thomson, 6th edition • [3] Programming Logic and Design – Joyce Farrell – Thomson, 3rd Edition • [4] Giáo trình cơ sở dữ liệu - Trường ĐH Công nghiệp TP HCM [5] Các bài thực hành môn cơ sở dữ liệu- Trường ĐH Công nghiệp TP HCM • [6] Principles of database and knowledge base systems - JEFFREY D. ULLMAN
Chương 1 Giới thiệu chung về lý thuyết Cơ Sở Dữ Liệu (CSDL)
Nội dung ❖ Các khái niệm cơ bản và định nghĩa ❖ Hệ thống xử lý tập tin truyền thống ❖ Cách tiếp cận cơ sở dữ liệu ❖ Các thành phần của môi trường hệ quản trị cơ sở dữ liệu ❖ Các chức năng của hệ quản trị cơ sở dữ liệu ❖ Kiến trúc cơ sở dữ liệu ba lược đồ
1. Các khái niệm cơ bản và định nghĩa ❖ Dữ liệu và thông tin Dữ liệu (data): • Là một mô tả hình thức thích hợp về những sự kiện(event), khái niệm (concept) hay chỉ thị để giúp con người giao tiếp, diễn giải hay xử lý hoặc thực hiện 1 cách tự động • Dữ liệu được mô tả bất kỳ dạng nào, chẳng hạn ký tự hay số gắn liền với ngữ nghĩa Thông tin (Information): • Là dữ liệu đã được qua xử lý nhằm đáp ứng yêu cầu của người sử dụng trong những hoàn cảnh cụ thể • Dữ liệu trong ngữ cảnh • Dữ liệu được tổng hợp / xử lý
1. Các khái niệm cơ bản và định nghĩa Dữ liệu 1 10273 Nguyễn Văn Hoà CDTH7 20 2 00298 Nguyễn Minh Tâm CDTH7 19 151 50542 Hồ Xuân Phương TCTH33 18 152 50075 Lê Việt Dũng CNTH34 20 Thông tin: dữ liệu trong ngữ cảnh STT Mã sinh viên Họ và tên sinh viên Lớp Tuổi 1 10273 Nguyễn Văn Hoà CDTH7 20 2 00298 Nguyễn Minh Tâm CDTH7 19 151 50542 Hồ Xuân Phương TCTH33 18 152 50075 Lê Việt Dũng CNTH34 20
1. Các khái niệm cơ bản và định nghĩa
1. Các khái niệm cơ bản và định nghĩa ❖ Siêu dữ liệu (metadata) Siêu dữ liệu là dữ liệu dùng để mô tả các tính chất / đặc tính của dữ liệu khác (dữ liệu về dữ liệu). Các đặc tính: định nghĩa dữ liệu, cấu trúc dữ liệu, qui tắc / ràng buộc.
2. Quá trình quản lý dữ liệu 2.1 Hệ thống xử lý tập tin theo lối cũ ❖ Hệ thống xử lý tập tin(file processing system) Hệ thống xử lý tập tin là tập hợp các chương trình dùng để lưu trữ, thao tác và truy xuất các tập tin dữ liệu có kích thước lớn. Các tập tin dữ liệu được lưu trữ trong các thư mục (folder).
2. Quá trình quản lý dữ liệu ❖ Các thành phần của hệ thống xử lý tập tin  Phần cứng: các máy tính.  Phần mềm:  Hệ điều hành  Các tiện ích  Các tập tin  Các chương trình quản lý tập tin  Các chương trình ứng dụng tạo các báo cáo từ các dữ liệu được lưu trữ trong các tập tin.  Con người: người quản lý, chuyên gia, người lập trình, người sử dụng cuối cùng.  Các thủ tục: các lệnh và các qui tắc chi phối việc thiết kế và sử dụng các thành phần của phần mềm.  Dữ liệu: tập hợp các sự kiện.
2. Quá trình quản lý dữ liệu ❖ Quản lý dữ liệu của hệ thống tập tin  Ngôn ngữ lập trình: 3GL (third-Generation Language).  Làm gì? Làm như thế nào?  Các ngôn ngữ: COBOL (COmmon Business-Oriented Language) BASIC (Beginner’s All-purpose Symbolic Instruction Code) FORTRAN (FORmula TRANslation)  Các chương trình xử lý tập tin  Tạo cấu trúc tập tin.  Thêm dữ liệu vào tập tin.  Xóa dữ liệu của tập tin  Sửa dữ liệu của tập tin.  Liệt kê dữ liệu của tập tin.
Hệ thống xử lý tập tin theo lối cũ ❖ Nhược điểm của hệ thống xử lý tập tin  Phụ thuộc dữ liệu – chương trình (Program-Data Dependence)  Tất cả các chương trình ứng dụng phải duy trì siêu dữ liệu (phần mô tả) của các tập tin mà chúng sử dụng.  Dư thừa dữ liệu / Trùng lặp dữ liệu (Data Redundancy / Duplication of Data)  Các hệ thống / chương trình khác nhau có các bản dữ liệu riêng biệt của cùng dữ liệu.  Hạn chế việc dùng chung dữ liệu  Mỗi ứng dụng có các tập tin riêng biệt, ít sử dụng chung dữ liệu với các ứng dụng khác.
Hệ thống xử lý tập tin theo lối cũ ❖ Nhược điểm của hệ thống xử lý tập tin  Thời gian phát triển lâu  Người lập trình phải thiết kế các dạng tập tin dữ liệu riêng và viết cách truy xuất tập tin cho mỗi ứng dụng mới.  Chi phí bảo trì chương trình cao  Các nhược điểm nêu trên làm cho việc bảo trì chương trình gặp nhiều khó khăn, thường chiếm khoảng 80% ngân sách phát triển HTTT.
Phụ thuộc dữ liệu ❖ Mỗi người lập trình phải duy trì dữ liệu riêng biệt. ❖ Mỗi chương trình ứng dụng phải có mã lệnh cho siêu dữ liệu của mỗi tập tin. ❖ Mỗi chương trình ứng dụng phải có các chương trình con xử lý để đọc, thêm, sửa và xóa dữ liệu. ❖ Không có các điều khiển chung và phối hợp. ❖ Các dạng thức tập tin không có cùng chuẩn.
Dư thừa dữ liệu ❖ Tốn vùng nhớ để lưu trữ dữ liệu dư thừa. ❖ Gây ra các vấn đề khó về bảo trì dữ liệu. ❖ Vấn đề chính:  Việc cập nhật dữ liệu của một tập tin có thể dẫn đến các mâu thuẫn dữ liệu.  Vi phạm tính toàn vẹn dữ liệu.
Dư thừa dữ liệu Duplicate Data
2. Quá trình quản lý dữ liệu 2.2 Cách tiếp cận cơ sở dữ liệu
2. Quá trình quản lý dữ liệu 2.2 Cách tiếp cận cơ sở dữ liệu Software 1 USER l e a s c a i b Software 2 s a y t h Application a P developer d Software 3 Database DBMS administrator The DBMS provides an interface between the user and the data
3.Các khái niệm về hệ quản trị cơ sở dữ liệu ❖ Cơ sở dữ liệu (CSDL) Cơ sở dữ liệu là sự tập hợp có tổ chức các dữ liệu có liên quan luận lý với nhau được lưu trữ trong các tập tin Tập dữ liệu này sẽ được lưu trữ trên các thiết bị thông tin lưu trữ như băng từ, đĩa nhằm thỏa mãn yêu cầu khai thác thông tin đồng thời của nhiều người dùng hay nhiều chương trình ứng dụng với nhiều mục đích sử dụng khác nhau. Một hệ CSDL= CSDL+ hệ QTCSDL
3.Các khái niệm về hệ quản trị cơ sở dữ liệu ❖ Hệ quản trị CSDL DBMS – DataBase Management System Hệ quản trị CSDL là tập hợp các chương trình, phần mềm dùng để quản lý cấu trúc và dữ liệu của CSDL và điều khiển truy xuất dữ liệu trong CSDL. Cho phép người sử dụng định nghĩa, tạo lập và bảo trì CSDL và cung cấp các truy xuất dữ liệu. Cung cấp một giao diện giưã người sử dụng và dữ liệu.
3.1 Các cách nhìn khác nhau về dữ liệu View 1 View 2 View n Logical (or Conceptal) Level Cấp độ trừu tượng trừu độ Cấp Physical Level Các mức độ trừu tượng trong một DBMS Sự trừu tượng đạt được thông qua mô tả mỗi mức dưới dạng một lược đồ bằng cách dùng một mô hình dữ liệu cụ thể
3.2 Mô hình dữ liệu, lược đồ và thể hiện ❖Mô hình Dữ liệu Mô hình Dữ liệu – Data Model: Tập hợp các khái niệm mô tả: Dữ liệu và mối kết hợp giữa dữ liệu Ngữ nghĩa dữ liệu và ràng buộc dữ liệu Các thao tác trên Mô hình Dữ liệu: Các thao tác rút trích và cập nhật CSDL bằng cách tham chiếu đến các khái niệm của Mô hình Dữ liệu
3.2 Mô hình dữ liệu, lược đồ và thể hiện ❖Các Mô hình Dữ liệu: Mô hình Quan hệ- Relational Model Mô hình Mạng-Network Model Mô hình Dữ liệu Phân cấp-Hierarchical Data Model Mô hình Hướng đối tượng-Object-oriented Data Models Mô hình Quan hệ Đối tượng-Object- Relational Models
3.2 Mô hình dữ liệu, lược đồ và thể hiện ❖Lược đồ và thể hiện: Lược đồ CSDL-Database Schema: Mô tả về CSDL. Bao gồm mô tả về cấu trúc CSDL và các ràng buộc trên CSDL đó Thể hiện CSDL-Database Instance: Dữ liệu hiện thời được lưu trong một CSDL ở một thời điểm nào đó, hay còn gọi là trạng thái CSDL- database state(hay thể hiện- occurrence) Lược đồ CSDL rất ít khi thay đổi. Trạng thái CSDL thay đổi mỗi khi CSDL được cập nhật
3.3 Các đặc điểm của Cách tiếp cận cơ sở dữ liệu Một kho chứa dữ liệu được định nghĩa 1 lần, được duy trì và truy xuất bởi nhiều người dùng. Bản chất tự mô tả của 1 hệ CSDL  Một DBMS catalog lưu trữ mô tả về CSDL(mô tả này gọi là meta-data).  CSDL+ mô tả về cấu trúc và ràng buộc của CSDL Phần mềm DBMS làm việc với nhiều ứng dụng CSDL Sự tách biệt giữa chương trình và dữ liệu, sự trừu tượng của dữ liệu
3.3 Các đặc điểm của Cách tiếp cận cơ sở dữ liệu Mô hình dữ liệu Dữ liệu. Hỗ trợ nhiều cách nhìn về dữ liệu Chia sẽ dữ liệu và xử lý giao dịch nhiều người dùng
3.4 Các đối tượng dùng Cơ sở dữ liệu ❖ Những người lập trình: Những người này phải hiểu rõ cấu trúc CSDL, cách lưu trữ dữ liệu. ❖ Những người sử dụng không chuyên: Chỉ cần biết nội dung CSDL và cách truy xuất ❖ Những người quản trị CSDL: Đối tượng này xuất hiện do tính chất quá phức tạp của hệ quản trị CSDL, không phải ai cũng có toàn quyền đối với CSDL. Những người thuộc đối tượng này có nhiệm vụ cài đặt cấu trúc CSDL vào hệ quản trị CSDL, nhập dữ liệu ban đầu, giải quyết các biến cố xảy ra, backup , restore dữ liệu, sửa đối cấu trúc CSDL theo yêu cầu của người thiết kế, nắm các quyền ưu tiên, lập cơ chế bảo mật của hệ thống CSDL.
3.5 Sự cần thiết của CSDL Dữ liệu tạo thành một tài sản của tổ chức: Điều khiển tích hợp Giảm tối thiểu sự dư thừa dữ liệu (data redundancy). Nâng cao tính nhất quán (data consistency) / toàn vẹn dữ liệu (data integrity). Nâng cao việc dùng chung dữ liệu (data sharing).  Những người sử dụng khác nhau có những cái nhìn khác nhau về dữ liệu.
3.5 Sự cần thiết của CSDL Nâng cao an toàn dữ liệu Tăng hiệu suất phát triển ứng dụng  Độc lập dữ liệu – chương trình (data - program independence).  DBMS chứa siêu dữ liệu (metadata), do đó các ứng dụng không cần quan tâm đến các dạng thức của dữ liệu.  DBMS quản lý các truy vấn và cập nhật dữ liệu, do đó ứng dụng không cần xử lý việc truy xuất dữ liệu.
3.6 Các chức năng của hệ quản trị CSDL ❖ Các chức năng của hệ quản trị CSDL Lưu trữ, truy xuất và cập nhật dữ liệu  Ngôn ngữ định nghĩa dữ liệu (DDL - Data Definition Language)  Ngôn ngữ thao tác dữ liệu (DML - Data Manipulation Language). Quản lý giao tác (transaction management). Điều khiển tương tranh (concurrency control) Chép lưu(backup) và phục hồi dữ liệu(rescovery). Bảo mật dữ liệu(security)  Ngôn ngữ điều khiển dữ liệu (DCL - Data Control Language). Hỗ trợ truyền thông dữ liệu. Duy trì tính toàn vẹn / nhất quán dữ liệu. Cung cấp các tiện ích.
4. Nội dung và kiến trúc của DBMS ❖ Các yêu cầu của 1 DBMS Khả năng đáp ứng cao Độ tin cậy cao Lưu lượng cao Thời gian phản hồi thấp
4. Nội dung và kiến trúc của DBMS ❖ Các yêu cầu của 1 DBMS Chu kỳ sống lâu An toàn Xác thực (authentication), chứng thực (authorization), mã hóa (encryption)
4. Nội dung và kiến trúc của DBMS ❖ Phân loại DBMS: Dựa trên mô hình dữ liệu được dùng: Truyền thống: Quan hệ, mạng, phân cấp Đang thịnh hành: hướng đối tượng, Quan hệ Đối tượng Các sự phân loại khác: Người sử dụng đơn(single-user), đa người dùng(multi user) Tập trung và phân tán Chi phí phần mềm DBMS 
4. Nội dung và kiến trúc của DBMS ❖ Các thành phần của DBMS: Ngôn ngữ định nghĩa dữ liệu: Tạo cấu trúc của bảng SinhVien CREATE TABLE SinhVien (MaSV NUMBER(7,0) NOT NULL, HoTen VARCHAR(25) NOT NULL, DiaChi VARCHAR(30), NoiSinh VARCHAR(20), CONSTRAINT PK_SinhVien PRIMARY KEY (MaSV)); Ngôn ngữ thao tác dữ liệu: Liệt kê mã, tên và địa chỉ của các sinh viên thuộc thành phố ‘HCM’ SELECT MaSV, HoTen, DiaChi FROM SinhVien WHERE NoiSinh = ‘HCM’; Ngôn ngữ điều khiển dữ liệu: Cho phép người sử dụng A và B được phép xem và thêm dữ liệu vào bảng SinhVien GRANT SELECT, INSERT ON SinhVien TO A,B;
Chương 2 Mô hình thực thể kết hợp (Mô hình liên kết thực thể)
1. Quá trình thiết kế Cơ sở dữ liệu Thu thập và phân tích các yêu cầu Thiết kế mức quan niệm Ánh xạ thiết kế logic/mô hình dữ liệu Thiết kế mức vật lý
Thế giới thực Thu thập và phân tích yêu cầu Các yêu cầu chức năng Các yêu cầu của CSDL Phân tích chức năng Phân tích quan niệm Các đặc tả giao tác Lược đồ quan niệm cấp cao (trong một mô hình dữ liệu cấp cao) Thiết kế logic Phụ thuộc DBMS cụ thể Lược đồ quan niệm (trong mô hình dữ liệu của một DBMS cụ thể) Thiết kế ctr. ứng dụng Thiết kế mức vật lý Thực hiện giao tác Lược đồ trong Các chương trình ứng dụng
2. Mô hình thực thể kết hợp • Hiện nay mô hình dữ liệu quan hệ thường được dùng trong các hệ quản trị CSDL, tuy nhiên mô hình này không được trực quan. • Để thuận lợi trong việc thiết kế mô hình dữ liệu quan hệ, người ta thường dùng một mô hình trung gian đó là mô hình thực thể kết hợp.
2. Mô hình thực thể kết hợp • Hiện nay mô hình thực thế kết hợp được coi là mô hình chuẩn để thiết kế hệ thống thông tin. • Moâ hình ER ñoâi khi coøn ñöôïc goïi laø moâ hình yù nieäm döõ lieäu (Conceptual Data Model) hay ñôn giaûn laø moâ hình döõ lieäu (data model)
Vấn đề khi thiết kế CSDL? • Các thực thể và mối kết hợp nào cần quan tâm? • Thông tin nào về thực thể và mối quan hệ giữa các thực thể cần được lưu trữ trong CSDL? • Các ràng buộc nào cần được bảo đảm đối với thực thể và mối kết hợp?
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Thực thể(entity, instance, entity instance, entity occurence): Là một đối tượng hay sự vật của thế giới thực tồn tại cụ thể hay tồn tại quan niệm mà có thể phân biệt được với các đối tượng khác Ví dụ : Thực thể SINHVIEN Nguyễn Thị Lan Anh, LOP TCTH36D, .
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Thực thể(entity, instance, entity instance, entity occurence):  Thực thể nên là  đối tượng có nhiều thể hiện trong CSDL.  đối tượng có nhiều thuộc tính.  đối tượng cần được mô hình hóa.  Thực thể không nên là  người sử dụng của hệ CSDL.  kết xuất của hệ CSDL (ví dụ bản báo cáo).  Đặc điểm của thực thể là tính phân biệt (distinctness): có thể phân biệt giữa thực thể này với thực thể khác.
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Thuộc tính (Attributes): Là các đặc tính riêng dùng để mô tả 1 thực thể Ví dụ : Thực thể SINHVIEN có các thuộc tính MASV,TENSV, DCHI, PHAI, NGAYSINH Ví dụ : Thực thể sinh viên cụ thể có các thuộc tính: • MASV=‘06028071’ • TENSV=‘Nguyen Thi Lan Anh’, • DCHI=‘580 CMT8, TP HCM’ • PHAI=‘Nu’ • NGAYSINH=’05/06/1991’,
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: ❖ Thuộc tính đơn và thuộc tính phức hợp Thuộc tính đơn (simple attribute) là thuộc tính không bị phân rã thành nhiều thuộc tính khác. Thuộc tính phức hợp (composite attribute) là thuộc tính bị phân rã thành nhiều thuộc tính khác.
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: Thuoäc tính ñôn Thuoäc tính phức hôïp
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: ❖ Thuộc tính đơn trị và thuộc tính đa trị Thuộc tính đơn trị (single-valued attribute) là thuộc tính chỉ chứa một giá trị. Thuộc tính đa trị (multivalued attribute) là thuộc tính chứa nhiều giá trị khác nhau thuộc một miền trị, được biểu diễn bằng hình bầu dục nét đôi.
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: ❖ Thuộc tính chứa và thuộc tính dẫn xuất Thuộc tính chứa (stored attribute) là thuộc tính mà giá trị của nó không được suy dẫn từ các thuộc tính khác. Thuộc tính dẫn xuất (derived attribute) là thuộc tính mà giá trị của nó được suy dẫn từ các thuộc tính khác, được biểu diễn bằng hình bầu dục nét đứt.
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: ❖ Giá trị của tập thuộc tính Còn được gọi là “Miền giá trị” của thuộc tính Tập các giá trị có thể được gán cho thuộc tính đó đối với mỗi loại thực thể riêng Không được thể hiện trên giản đồ ER
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Các loại thuộc tính: Thuộc tính đơn trị Thuộc tính dẫn xuất Thuộc tính đa trị Thuộc tính chứa
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Loại thöïc theå (entity type, regular entity type,entity class, generic entity) : Là tập hợp các thực thể thuộc cùng một loại (thường tương ứng với một bảng). Loại Thực thể thường được biểu diễn bởi một hình chữ nhật, và tên của thực thể được đặt trong hình chữ nhật Tên của các loại thực thể (trong một hệ thống) phải khác nhau trong một mô hình thực thể kết hợp
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị ❖Tập thöïc theå (entity set): Là tập hợp các thực thể thuộc cùng một loại nào đó ở một thời điểm bất kỳ
2.1 Thực thể, Thuộc tính, Loại Thực thể, Tập Giá trị NHANVIEN CONGTY ENTITY TYPE: (Ten, Tuoi, Luong) (Ten, Diachi, Giamdoc) N1: Tran A, 24, 2Tr C1: ABC, HCMC, Nguyen B N2: Nguyen B, 34, 3Tr ENTITY SET: C2: MNO, HNC, Tran A (Extension) N3: Le C, 56, 4Tr
2.2 Khóa của loại Thực thể ❖ Khóa / thuộc tính xác định key / identifier Khóa là một thuộc tính hoặc tổ hợp các thuộc tính dùng để xác định duy nhất một thể hiện của một kiểu thực thể. ❖ Thuộc tính khóa và thuộc tính không khóa Thuộc tính khóa là thuộc tính ở trong khóa.  key attribute / prime attribute / identifier attribute  Thuộc tính khóa được gạch dưới. Thuộc tính không khóa (non-key attribute) là thuộc tính không ở trong khóa.  Thuộc tính không khóa còn được gọi là thuộc tính mô tả (descriptor).
2.2 Khóa của loại Thực thể ❖ Khóa đơn và khóa phức hợp Khóa đơn (simple key) là khóa chỉ có một thuộc tính. Khóa phức hợp (composite key) là khóa có nhiều hơn một thuộc tính. ❖ Khóa dự tuyển candidate key Khóa dự tuyển là khóa của một kiểu thực thể. Một kiểu thực thể có ít nhất một khóa dự tuyển.
2.2 Khóa của loại Thực thể ❖ Khóa chính primary key Khóa chính là một khóa tiêu biểu trong các khóa dự tuyển của một kiểu thực thể. primary key Khóa chính dùng để liên kết giữa các thực thể.
2.2 Khóa của loại Thực thể Khóa đơn Thuộc tính không khóa Khóa phức hợp
2.3 Mối kết hợp và loại mối kết hợp Mối kết hợp (relationship): liên hệ với hai hay nhiều thực thể khác nhau mang một ý nghĩa nào đó Tập mối kết hợp (relationship set): những mối kết hợp có cùng loại kết hợp Loại mối kết hợp (relationship type): các thực thể được nhóm lại hay phân loại thành một loại mối kết hợp
2.3 Mối kết hợp và loại mối kết hợp Loại mối kết hợp và tập mối kết hợp có cùng tên Có nhiều hơn một loại mối kết hợp có thể có trong cùng loại thực thể Ví dụ: QUANLY và PHANCONG là các mối kết hợp khác nhau giữa NHANVIEN và PHONGBAN
2.3 Mối kết hợp và loại mối kết hợp (0,N) 1 (1,1) (1,N) Lamviec Theo mô hình cuả N 1 Peter Chen Giamsat Nhanvien Phongban 1 1 N Quanly (1,1) (0,1) (1,1) Bi_giamsat Duoc_lamviec Lamviec Theo mô hình cuả Crow’s Foot Nhanvien Phongban Giamsat Duoc_quanly Quanly
2.3 Mối kết hợp và loại mối kết hợp ❖ Bậc / ngôi của mối liên kết (degree / arity of relationship) Bậc của mối liên kết là số lượng kiểu thực thể tham gia đồng thời vào mối liên kết này. ❖ Các loại mối liên kết Mối liên kết 1-ngôi (unary relationship) Mối liên kết 2-ngôi (binary relationship) Mối liên kết 3-ngôi (ternary relationship): 3 kiểu thực thể đồng thời tham gia vào mối liên kết.  . Mối liên kết n-ngôi(n-ary): .
2.3 Mối kết hợp và loại mối kết hợp ❖ Bậc / ngôi của mối liên kết (degree / arity of relationship) Entities of two different types related to each other One entity related to another of the same Entities of three different entity type types related to each other
2.3 Mối kết hợp và loại mối kết hợp ❖ Bậc / ngôi của mối liên kết (degree / arity of relationship):Relationship Degree 1 1 M N EMPLOYEE PROFESSOR CONTRIBUTOR CFR RECIPIENT 1 teaches P M FUND is married CLASS Terary Unary Binary or recursive relationship Theo mô hình của Peter Chen
2.3 Mối kết hợp và loại mối kết hợp ❖ Thuộc tính của mối kết hợp (Relationship Attributes) Một loại mối kết hợp có thể có các thuộc tính để mô tả tính chất của một mối kết hợp Các thuộc tính này không thể gắn với các thực thể tham gia, i.e., nó chỉ có ý nghĩa trong ngữ cảnh của một mối kết hợp Mối kết hợp không có thuộc tính khóa chính. Việc xác định một mối kết hợp nào đó trong một tập các mối kết hợp thông qua các khóa của các thực thể tham gia
2.3 Mối kết hợp và loại mối kết hợp ❖ Thuộc tính của mối kết hợp (Relationship Attributes) Thoigian (1,N) (1,M) Nhanvien Phancong Phongban Thoigian Nhanvien Phancong Phongban (1,M) (1,N) (1,1) (1,1) Composite entity Hoặc bridge entity Hoặc assocciative entity Hoặc intersection entity
2.4 Các ràng buộc trên loại mối kết hợp ❖ Loại mối kết hợp luôn có một số ràng buộc về cấu trúc nhằm giới hạn khả năng có thể kết hợp của các thực thể tham gia trong một tập hợp mối kết hợp tương ứng Xuất phát từ các ràng buộc của thế giới thực ❖ Có 2 loại ràng buộc mối kết hợp chính: • cardinality ratio constraint • participation constraint
2.4 Các ràng buộc trên loại mối kết hợp ❖ Lượng số của mối liên kết (cardinality of relationship) Lượng số của mối liên kết là số lượng thể hiện thực thể tham gia vào mối liên kết này.
2.4 Các ràng buộc trên loại mối kết hợp ❖ Các loại lượng số Many-to-Many,N:M (mặc định) Many-to-One, N:1 One-to-Many, 1:N One-to-One, 1:1 1-to-1 1-to Many Many-to-1 Many-to-Many
2.4 Các ràng buộc trên loại mối kết hợp ❖ Many-to-Many, N:M N M NHANVIEN LAMVIEC PHONGBAN Ý nghĩa: Một nhân viên có thể làm việc ở nhiều phòng ban (≥0) và một phòng ban có thể có nhiều nhân viên (N, M là bất kỳ)
2.4 Các ràng buộc trên loại mối kết hợp ❖ Many-to-One, N:1 N 1 NHANVIEN LAMVIEC PHONGBAN Ý nghĩa: Một nhân viên có thể làm việc ở nhiều nhất là một phòng ban (≤1) và một phòng ban có thể có nhiều nhân viên (N là bất kỳ)
2.4 Các ràng buộc trên loại mối kết hợp ❖ One-to-Many, 1:N 1 N NHANVIEN LAMVIEC PHONGBAN Ý nghĩa: Một nhân viên có thể làm việc ở nhiều phòng ban (≥0) và một phòng ban chỉ có nhiều nhất một nhân viên (N là bất kỳ)
2.4 Các ràng buộc trên loại mối kết hợp ❖ One-to-One, 1:1 1 1 NHANVIEN LAMVIEC PHONGBAN Ý nghĩa: Một nhân viên có thể làm việc ở nhiều nhất là một phòng ban (≤1) và một phòng ban chỉ có nhiều nhất một nhân viên
2.4 Các ràng buộc trên loại mối kết hợp ❖ Participation Constraint (Ràng buộc về sự tham gia) Cho biết sự tồn tại của một thực thể e E có phụ thuộc vào một thực thể khác thông qua loại kết hợp R hay không Có hai loại: • Toàn phần (total) – phụ thuộc tồn tại: mỗi thực thể e E phải tham gia vào một mối kết hợp, nó không thể tồn tại mà không tham gia • Từng phần (partial): một thực thể e E trong toàn bộ tập thực thể có tham gia vào một mối kết hợp
2.4 Các ràng buộc trên loại mối kết hợp ❖ Toàn phần (total) – phụ thuộc tồn tại: mỗi thực thể e E phải tham gia vào một mối kết hợp ❖ Ví dụ: N 1 NHANVIEN LAMVIEC PHONGBAN Ý nghĩa: Mọi nhân viên bắt buộc phải làm việc ở một phòng ban nào đó
2.4 Các ràng buộc trên loại mối kết hợp ❖ Riêng phần (partial): một số thực thể e E trong toàn bộ tập thực thể có tham gia vào một mối kết hợp ❖ Ví dụ: (0,1) (1,1) NHANVIEN QUANLY PHONGBAN Ý nghĩa: Trong mối kết hợp QUANLY, không phải mọi nhân viên đều quản lý phòng ban, ta nói, sự tham gia của NHANVIEN trong loại mối kết hợp QUANLY là riêng phần
2.4 Các ràng buộc trên loại mối kết hợp ❖ Ký hiệu (min,max) cho các ràng buộc cấu trúc trên mối kết hợp Chỉ định mỗi thực thể e E tham gian ít nhất min và nhiều nhất max thể hiện quan hệ trong R (min1, max1) (min2, max2) E1 R E2 Ý nghĩa: Mỗi thực thể e1 E phải tham gia trong tập mối kết hợp R ít nhất min1 và nhiều nhất max1 lần (tương tự cho e2 E )
2.4 Các ràng buộc trên loại mối kết hợp ❖ Ký hiệu (min,max) cho các ràng buộc cấu trúc trên mối kết hợp Min=0 là tùy chọn (optional) Nếu Min=1 hoặc nhiều là bắt buộc (mandatory). (min1, max1) (min2, max2) E1 R E2
2.4 Các ràng buộc trên loại mối kết hợp (0,N) 1 (1,1) (1,N) Lamviec Theo mô hình cuả N 1 Peter Chen Giamsat Nhanvien Phongban 1 1 N Quanly (1,1) (0,1) (1,1) Bi_giamsat Duoc_lamviec Lamviec Theo mô hình cuả Crow’s Foot Nhanvien Phongban Giamsat Duoc_quanly Quanly
2.5 Loại thực thể yếu (weak entity type) Loại thực thể không có thuộc tính khóa Phải tham gia trong một loại mối kết hợp xác định (identifying relationship type) trong đó có một loại thực thể chủ hay lọai thực thể xác định (owner entity type) Các thực thể được xác định bằng sự kết hợp của: •Khóa riêng phần (partial key) của loại thực thể yếu •Loại thực thể mà chúng có quan hệ với trong loại thực thể xác định Lọai thực thể yếu luôn có ràng buộc tham gia toàn phần(phụ thuộc tồn tại) trong mối kết hợp xác định tương ứng, bởi vì thực thể yếu không thể xác định mà không có thực thể chủ tương ứng
3. Thiết kế quan niệm ER: ►Có thể định nghĩa thực thể và mối kết hợp theo các cách khác nhau với cùng một mô hình! ►Thiết kế giản đồ là một việc lặp đi lặp lại và có thể cần có sự tinh chế •Các thuộc tính của loại mối kết hợp 1:1 có thể được ghép vào một trong các thực thể tham gia •Các thuộc tính của loại mối kết hợp 1:N (or N:1) có thể được ghép vào loại thực thể của nhánh N •Các thuộc tính của loại mối kết hợp N:M không thể được ghép chung
3.1 Các bước thiết kế ER 1. Xác định các loại thực thể (loại thực thể hay thuộc tính) 2. Xác định loại mối kết hợp 3. Xác định và gắn thuộc tính với loại thực thể và loại mối kết hợp 4. Quyết định miền giá trị của thuộc tính 5. Quyết định các thuộc tính khóa cho loại thực thể 6. Gắn (tinh chế) cardinality ratio(s) vào loại mối kết hợp 7. Thiết kế phân cấp chuyên biệt hóa/tổng quát hóa (specialization/generalization) trong các ràng buộc
3.2 Chuyển đổi lược đồ ER thành Bảng (table) ► Một lược đồ ER có thể được thể hiện dưới dạng tập hợp các bảng thể hiện nội dung của CSDL (instance) ► Các khóa chính cho phép các loại thực thể và loại mối kết hợp được diễn đạt dưới dạng bảng ► Đối với mỗi loại thực thể và loại mối kết hợp, một bảng duy nhất có thể được phát sinh được gán cho một tên tương ứng với tên của loại thực thể hay loại mối kết hợp ► Mỗi bảng có một số cột tương ứng với các thuộc tính (atomic) và có một tên duy nhất. Một thuộc tính của một bảng có thể có cùng miền giá trị với thuộc tính trong lược đồ ER ► Việc chuyển đổi một lược đồ ER thành một tập hợp các bảng là cơ bản để phát sinh một lược đồ quan hệ từ một giản đồ ER
Các ký hiệu của mô hình ER Entity Type Composite Attribute Weak Entity Type Derived Attribute Relationship Type E1 R E2 Identifying Relationship Type Total Participation of E2 in R Attribute E1 R E2 Cardinality Ratio 1:N for E1:E2in R Key Attribute (min, max) R E Multivalued Attribute Structural Constraint (min, max) on Participation of E in R
4. Ví dụ về Cơ sở dữ liệu quản lý điểm của sinh viên Ví dụ: Bài toán quản lý điểm của sinh viên được phát biểu đơn giản như sau: "Trường có nhiều khoa, mỗi khoa có một mã khóa duy nhất, mỗi mã khoa xác định tên khoa, số điện thoại khoa. Một khoa có nhiều lớp, mỗi lớp có một mã lớp duy nhất, mỗi mã lớp xác định tên lớp. Mỗi sinh viên có một mã số sinh viên duy nhất, mỗi mã số sinh viên xác định họ tên, ngày sinh, địa chỉ, lớp mà sinh viên đó theo học, giới tính của sinh viên đó, Mỗi sinh viên có thể đăng ký học nhiều môn và với mỗi môn học thì chỉ có một kết quả nhất định. Mỗi môn học có một mã môn học nhất định, mỗi mã môn học xác định tên môn học, số tiết của môn học đó".
Với bài toán trên thì cần quản lý các đối tượng như: SINH VIÊN, MÔN HỌC, KHOA, LỚP. • Mối kết hợp • Mối kết hợp "thuộc" sau biểu diễn mối liên hệ giữa hai thực thể LỚP và KHOA. LỚP thuộc KHOA
• Bản số của mối kết hợp (1,1) (1,n) LỚP thuộc KHOA
MASV NU MAMH (1,n) (0,n) HOTEN SINH VIEÂN MOÂN HOÏC TENMH NGAYSINH học HOCBONG (1,1) SOTIET DIEMTHI TINH thuộc (1,n) MAKHOA MALOP (1,1) (1,n) LÔÙP KHOA TENKHOA TENLOP thuộc SOCBGD
QUAÛN LYÙ LAO ÑOÄNG Ñeå quaûn lyù vieäc phaân coâng caùc nhaân vieân tham gia vaøo xaây döïng caùc coâng trình. Coâng ty xaây döïng ABC toå chöùc quaûn lyù nhö sau: Cuøng luùc coâng ty coù theå tham gia xaây döïng nhieàu coâng trình, moãi coâng trình coù moät maõ soá coâng trình duy nhaát (MACT), moãi maõ soá coâng trình xaùc ñònh caùc thoâng tin nhö: Teân goïi coâng trình (TENCT), ñòa ñieåm(ÑIAÑIEM), ngaøy coâng trình ñöôïc caáp giaáy pheùp xaây döïng (NGAYCAPGP), ngaøy khôûi coâng (NGAYKC), ngaøy hoaøn thaønh (NGAYHT) Moãi nhaân vieân cuûa coâng ty ABC coù moät maõ soá nhaân vieân(MANV) duy nhaát, moät maõ soá nhaân vieân xaùc ñònh caùc thoâng tin nhö: Hoï teân (HOTEN), ngaøy sinh(NGAYSINH), phaùi (PHAI), ñòa chæ (ÑIACHI),phoøng ban, Coâng ty phaân coâng caùc nhaân vieân tham gia vaøo caùc coâng trình, moãi coâng trình coù theå ñöôïc phaân cho nhieàu nhaân vieân vaø moãi nhaân vieân cuøng luùc cuõng coù theå tham gia vaøo nhieàu coâng trình. Vôùi moãi coâng trình moät nhaân vieân coù moät soá löôïng ngaøy coâng (SLNGAYCONG) ñaõ tham gia vaøo coâng trình ñoù. Coâng ty coù nhieàu phoøng ban(Phoøng keá toaùn, phoøng kinh doanh, phoøng kyõ thuaät, phoøng toå chöùc, phoøng chuyeân moân, Phoøng phuïc vuï, ). Moãi phoøng ban coù moät maõ soá phoøng ban(MAPB) duy nhaát, moät phoøng ban öùng vôùi moät teân phoøng ban(TENPB)
Moâ hình ER maCT maNV ñöôïc tham gia bôûi tenCT COÂNG NHAÂN hoTen diaDiem TRÌNH ñöôïc phaân coâng vaøo VIEÂN ngaySinh ngayCapGP coù phai ngayKhoiCong diaChi ngayHT maNV maNV thuoäc veà NHAÂN thuoäc veà COÂNG hoTen maCT VIEÂN maPB sLNgayCong coù ngaySinh PHOØNG BAN tenPB coù coù phai diaChi thuoäc veà thuoäc veà maCT COÂNG PHOØNG maPB tenCT tenPB diaDiem TRÌNH BAN ngayCapGP Löôïc ñoà CSDL ngayKhoiCong ngayHT NHANVIEN(maNV,hoTen,ngaySinh,phai,diaChi,maPB) PHONGBAN(maPB,tenPB) CONG(maNV,maCT,sLNgayCong) CONGTRINH(maCT,tenCT,diaDiem,ngayCapGP,ngayKhoiCong,ngayHT) Moâ hình ER vaø löôïc ñoà CSDL
QUAÛN LYÙ THÖ VIEÄN Moät thö vieän toå chöùc vieäc cho möôïn saùch nhö sau: Moãi quyeån saùch ñöôïc ñaùnh moät maõ saùch (MASH) duøng ñeå phaân bieät vôùi caùc quyeån saùch khaùc (giaû söû neáu moät taùc phaåm coù nhieàu baûn gioáng nhau hoaëc coù nhieàu taäp thì cuõng xem laø coù maõ saùch khaùc nhau), moãi maõ saùch xaùc ñònh caùc thoâng tin khaùc nhö : teân saùch (TENSACH), teân taùc giaû (TACGIA), nhaø xuaát baûn (NHAXB), naêm xuaát baûn (NAMXB). Moãi ñoïc giaû ñöôïc thö vieân caáp cho moät theû thö vieän, trong ñoù coù ghi roõ maõ ñoïc giaû (MAÑG), cuøng vôùi caùc thoâng tin khaùc nhö : hoï teân (HOTEN), ngaøy sinh (NGAYSINH), ñòa chæ (ÑIACHI), ngheà nghieäp(NGHENGHIEP). Cöù moãi löôït möôïn saùch, ñoïc giaû phaûi ñaêng kyù caùc quyeån saùch caàn möôïn vaøo moät phieáu möôïn, moãi phieáu möôïn coù moät soá phieáu möôïn (SOPM) khaùc nhau, moãi phieáu möôïn xaùc ñònh caùc thoâng tin nhö: ngaøy möôïn saùch (NGAYMUON), maõ ñoïc giaû. Caùc caùc quyeån saùch trong cuøng moät phieáu möôïn khoâng nhaát thieát phaûi traû trong moät laàn.
Moâ hình ER maDG soPM laø chuû theå PHIEÁU ngayMuon hoTen ÑOÄC GIAÛ ngaySinh thuoäc veà MÖÔÏN diaChi ngheNghiep thuoäc veà chöùa maDauSach soPM ñöôïc keâ bôûi CHI TIEÁT maDauSach ban ÑAÀU SAÙCH MÖÔÏN ngayTra tap keâ laø töïa cuûa Löôïc ñoà CSDL coù DOCGIA(maDG,hoTen,ngaySinh,diaChi,ngheNghiep) maSH PHIEUMUON(soPM,ngayMuon,maDG) tenSach TÖÏA SAÙCH CHITIETMUON(soPM,maDauSach,ngayTra,maDauSach) tacGia DAUSACH(maDauSach,ban,tap,maSH) nhaXB TUASACH(maSH,tenSach,tacGia,nhaXB,namXB) namXB Moâ hình ER vaø löôïc ñoà CSDL
QUAÛN LYÙ BAÙN HAØNG Moãi khaùch haøng coù moät maõ khaùch haøng (MAKH) duy nhaát, moãi MAKH xaùc ñònh ñöôïc caùc thoâng tin veà khaùch haøng nhö : hoï teân khaùch haøng (HOTEN), ñòa chæ (ÑIACHI), soá ñieän thoaïi (ÑIENTHOAI). Caùc maët haøng ñöôïc phaân loaïi theo töøng nhoùm haøng, moãi nhoùm haøng coù moät maõ nhoùm (MANHOM) duy nhaát, moãi maõ nhoùm haøng xaùc ñònh teân nhoùm haøng (TENNHOM), taát nhieân moät nhoùm haøng coù theå coù nhieàu maët haøng. Moãi maët haøng ñöôïc ñaùnh moät maõ soá (MAHANG) duy nhaát, moãi maõ soá naøy xaùc ñònh caùc thoâng tin veà maët haøng ñoù nhö : teân haøng (TENHANG), ñôn giaù baùn (ÑONGIA), ñôn vò tính (ÑVT). Moãi hoùa ñôn baùn haøng coù moät soá hoùa ñôn (SOHÑ) duy nhaát, moãi hoùa ñôn xaùc ñònh ñöôïc khaùch haøng vaø ngaøy laäp hoùa ñôn (NGAYLAPHÑ), ngaøy baùn haøng (NGAYBAN). Vôùi moãi maët haøng trong moät hoùa ñôn cho bieát soá löôïng baùn (SLBAN) cuûa maët haøng ñoù.
Moâ hình ER maKH KHAÙCH NHOÙM maNhom hoTen HAØNG HAØNG tenNhom diaChi thuoäc veà dienThoai thuoäc veà soHÑ maHang bao goàm coù sLBan chöùa maHang soHÑ HOÙA DOØNG keâ MAËT tenHang ngayLapHÑ ÑÔN HOÙA ÑÔN HAØNG ngayBan thuoäc veà ñöôïc keâ bôûi donGia ÑVT Löôïc ñoà CSDL HOÙA ÑÔN BAÙN HAØNG KHACH HANG(maKH,hoTen,diaChi,dienThoai) Ngaøy 1 thaùng 3 naêm 2003 HOADON(soHD,ngayLapHD,ngayBan,maKH) Ngöôøi mua haøng: Traàn Thaùi Huøng DONGHOADON(soHD,maHang,sLBan) Ñòa chæ: 125 Nguyeãn Thò Minh Khai MATHANG(maHang,tenHang,donGia,DVT,maNhom) HAØNG MS ÑÔN GIAÙ SOÁ LÖÔÏNG Giaáy A4 VP001 35.000 1 Baêng keo VP005 20.000 2 Moâ hình ER vaø löôïc ñoà CSDL
QUAÛN LYÙ DAÏY HOÏC Ñeå quaûn lyù lòch daïy cuûa caùc giaùo vieân vaø lòch hoïc cuûa caùc lôùp, moät tröôøng toå chöùc nhö sau: Moãi giaùo vieân coù moät maõ soá giaùo vieân (MAGV) duy nhaát, moãi MAGV xaùc ñònh caùc thoâng tin nhö: hoï vaø teân giaùo vieân (HOTEN), soá ñieän thoaïi (DTGV). Moãi giaùo vieân coù theå daïy nhieàu moân cho nhieàu khoa nhöng chæ thuoäc söï quaûn lyù haønh chaùnh cuûa moät khoa naøo ñoù. Moãi moân hoïc coù moät maõ soá moân hoïc (MAMH) duy nhaát, moãi moân hoïc xaùc ñònh teân moân hoïc(TENMH). Öùng vôùi moãi lôùp thì moãi moân hoïc chæ ñöôïc phaân cho moät giaùo vieân. Moãi phoøng hoïc coù moät soá phoøng hoïc (PHONG) duy nhaát, moãi phoøng coù moät chöùc naêng (CHUCNANG); chaúng haïn nhö phoøng lyù thuyeát, phoøng thöïc haønh maùy tính, phoøng nghe nhìn, xöôûng thöïc taäp cô khí, Moãi khoa coù moät maõ khoa (MAKHOA) duy nhaát, moãi khoa xaùc ñònh caùc thoâng tin nhö: teân khoa (TENKHOA), ñieän thoaïi khoa(DTKHOA). Moãi lôùp coù moät maõ lôùp (MALOP) duy nhaát, moãi lôùp coù moät teân lôùp (TENLOP), só soá lôùp (SISO). Moãi lôùp coù theå hoïc nhieàu moân cuûa nhieàu khoa nhöng chæ thuoäc söï quaûn lyù haønh chính cuûa moät khoa naøo ñoù. Haøng tuaàn, moãi giaùo vieân phaûi laäp lòch baùo giaûng cho bieát giaùo vieân ñoù seõ daïy nhöõng lôùp naøo, ngaøy naøo (NGAYDAY), moân gì?, taïi phoøng naøo, töø tieát naøo (TUTIET) ñeán tieát naøo (ÑENTIET),töïa ñeà baøi daïy (BAIDAY), nhöõng ghi chuù (GHICHU) veà caùc tieát daïy naøy, ñaây laø giôø daïy lyù thuyeát (LYTHUYET) hay thöïc haønh - giaû söû neáu LYTHUYET=1 thì ñoù laø giôø daïy thöïc haønh vaø neáu LYTHUYET=2 thì ñoù laø giôø lyù thuyeát, moät ngaøy coù 16 tieát, saùng töø tieát 1 ñeán tieát 6, chieàu töø tieát 7 ñeán tieát 12, toái töø tieát 13 ñeán 16.
Moâ hình ER maLich LÒCH BAÙO ñöôïc baùo bôûi GIAÙO maGV hoTen ngayDay GIAÛNG baùo VIEÂN dTGV thuoäc veà quaûn lyù maLich thuoäc veà tuTiet maKhoa KHOA denTiet tenKhoa dTKhoa baiDay quaûn lyù ghiChu chöùa lyThuyet thuoäc veà maLop MOÂN baùo DOØNG BAÙO LOP tenLop HOÏC GIAÛNG ñöôïc baùo bôûi ñöôïc baùo bôûi baùo siSo maMH ñöôïc baùo bôûi tenMH baùo soPhong PHOØNG chucNang
Chương 3 Mô hình quan hệ
Moâ hình quan heä (Relational Model) Moâ hình Cô sôû döõ lieäu Quan heä (goïi taét laø Moâ hình Quan heä) do E.F Codd ñeà xuaát naêm 1970. Moâ hình naøy bao goàm: – caùc khaùi nieäm lieân quan ñeán caáu truùc döõ lieäu döôùi daïng doøng vaø coät nhö quan heä, boä, thuoäc tính, khoùa chính, khoaù ngoaïi, – pheùp toaùn taäp hôïp vaø pheùp toaùn quan heä. – raøng buoäc toaøn veïn quan heä. Caùc HQTCSDLQH ñöôïc xaây döïng döïa theo lyù thuyeát moâ hình quan heä
MOÂ HÌNH QUAN HEÄ MA HOSV TEN MONHOC TEN DIEM SV SV KHOA THI 99001 TRAN DAN THU CO SO DU LIEU CNTT 3.0 99002 NGUYEN HA DA THAO CO SO DU LIEU CNTT 8.0 99001 TRAN DAN THU TIN HOC VAN P. CNTT 6.0 99005 LE THANH TRUNG TIN HOC VAN P. ANH VAN 5.0 Baøi toaùn quaûn lyù ñieåm thi ñöôïc löu tröõ khoâng ñuùng vôùi moâ hình quan heä seõ gaây ra dö thöøa döõ lieäu. Dö thöøa döõ lieäu daãn ñeán caäp nhaät dò thöôøng
MOÂ HÌNH QUAN HEÄ MASV MAMH MAKHOA DIEMTHI MASV HOSV TENSV 99001 CSDL CNTT 3.0 99001 TRAN DAN THU 99002 CSDL CNTT 8.0 99002 NGUYEN HA DA THAO 99001 THVP CNTT 6.0 99005 LE THANH TRUNG 99005 THVP AV 5.0 MAKHOA TENKHOA CNTT CONG NGHE THONG TIN AV ANH VAN MAMH TENMH SOTIET CSDL CO SO DU LIEU 90 THVP TIN HOC VAN PHONG 90
THUOÄC TÍNH (Attribute, arity) – Thoâng tin chi tieát söï vaät, söï vieäc hay ñoái töôïng – Thuoäc tính cuûa söï vaät ñöôïc phaân bieät qua teân vaø khoâng ñöôïc coù hai thuoäc tính cuøng teân – Thuoäc tính phaûi thuoäc moät kieåu döõ lieäu nhaát ñònh nhö soá, chuoãi, ngaøy
LÖÔÏC ÑOÀ QUAN HEÄ (Relation schema) – Laø taäp taát caû caùc thuoäc tính cuûa moät ñoái töôïng, söï vaät hay söï vieäc – Löôïc ñoà quan heä Q vôùi taäp thuoäc tính A1,A2, An ñöôïc vieát laø Q(A1,A2, An) – Moãi löôïc ñoà quan heä luoân keøm moät taân töø ñeå dieãn taû yù nghóa cuûa noù. Chaúng haïn LÑQH Kh(MAKHOA,TENKHOA,SOCBGD) coù taân töø: Moãi khoa coù maõ khoa ñeå phaân bieät khoa naøy vôùi khoa kia, teân khoa vaø soá giaûng vieân cuûa khoa
QUAN HEÄ (Relation, instances of relation) – Söï theå hieän cuûa löôïc ñoà quan heä Q ôû moät thôøi ñieåm naøo ñoù ñöôïc goïi laø quan heä. – Löôïc ñoà quan heä Q coù quan heä r. – Ví duï quan heä khoa rKh MAKHOA TENKHOA SOCBGD CNTT CONG NGHE THONG TIN 60 AV ANH VAN 60 HOA HOA CHAT 20 MAÙY TÍNH MOI TRUONG 10
QUAN HEÄ (relation) là một bảng dữ liệu hai chiều bao gồm nhiều hàng (mẩu tin-record) và nhiều cột (thuộc tính hoặc vùng tin(field)).  Mỗi hàng là duy nhất: không thể có hai hàng có cùng các giá trị ở tất cả vùng tin.  Ngầm định là khóa chính luôn tồn tại  Giá trị trong một bộ: Tất cả các giá trị được xem là atomic (không thể phân chia được nữa).  Thứ tự của các hàng là không quan trọng. Một bảng chỉ là một đại diện của một quan hệ Tuy nhiên, lưu trữ vật lý của một quan hệ phải có thứ tự.
 Thứ tự của các cột là không quan trọng.  Không phải mọi bảng đều là quan hệ. Quan hệ là một bảng không chứa các hàng giống hệt nhau.  Giá trị không biết cũng phải được thể hiện: Chúng được thay thế bằng giá trị đặc biệt null. Null có nhiều ý nghĩa: “không biết”, “không quan tâm”, “không thể áp dụng một bộ đó Quan hệ: Supplier Snum Name City S1 Nguyễn Trung Tiến SF S2 Trần Thị Yến LA S3 Nguyễn Văn An SF
BOÄ (Tuple) – Boä laø taäp moãi giaù trò lieân quan cuûa taát caû caùc thuoäc tính cuûa moät löôïc ñoà quan heä. – Ví duï quan heä sau coù 2 boä MA NGAY MA HOC SV HOTEN NU SINH LOP TINH BONG 99001 TRAN AN TRUE 15-03-1977 CÑTH2B T GIANG 120000 99002 NGUYEN HA TRUE 25-04-1986 TCTH29C TPHCM 120000
Ràng buộc khóa chính • Super key (siêu khóa): SK của r là một thuộc tính hay tập hợp các thuộc tính để xác định duy nhất một bộ trong một quan hệ (không có hai bộ nào trong bất kỳ thể hiện quan hệ r(R) có cùng giá trị SK). • i.e., Với mọi bộ phân biệt t1 và t2 trong r(R), t1[SK] <> t2[SK]. • Mọi quan hệ đều có ít nhất một SK.
Candidate Key • Candidate key K của r(R) thỏa mãn 2 điều kiện: – K là một SK, và – K là SK bé nhất ( K’K, K’≠K, K’ không phải là SK). • Quan hệ r có thể có nhiều hơn một khóa K • Ex: – XE{TINH_TP, SOXE, SOHIEUXE, NOISX, KIEUDANG, NAM} – Có hai SK là K1 = {TINH_TP, SOXE}, K2 = {SOHIEUXE}, nhưng K1 không là Khóa.
Ràng buộc khóa ngoại và toàn vẹn tham chiếu KHO MAKHOA TENKHOA A CNTT Cong nghe Thong tin VT Vien thong DT Dien tu SVIEN TENSV MASV MAHK Nhat 11 CNTT Minh 16 DT • Tập hợp các thuộc tính trong một quan hệ (quan hệ con) được dùng để tham chiếu tới một bộ của quan hệ khác (quan hệ cha). Khóa ngoại phải tham chiếu tới khóa chính của quan hệ đựợc tham chiếu.
Foreign Key – Khóa ngoại • Cho FK  r1, FK ≠, FK được gọi là khóa ngoại của r1 (tham chiếu đến r2) khi: • Các thuộc tính trong FK phải có cùng miền giá trị với các thuộc tính khóa chính PK trong r2. • Giá trị tại FK của một bộ t1 r1 bằng giá trị tại PK của một bộ t2 r2 hoặc bằng giá trị rỗng null. Trường hợp đầu, ta nói t1 tham chiếu tới bộ t2. (t1[FK]=t2[PK]) • Nếu tất cả các ràng buộc khóa ngoại được bảo đảm cho một quan hệ, ta nói đã đạt được toàn vẹn tham chiếu - referential intergrity.
Primary Key – Khóa chính • Nếu có nhiều Khóa (candidate key) trong một quan hệ, người thiết kế hoặc người quản trị CSDL sẽ chọn ra một khóa là khóa chính để nhận biết các bộ trong một quan hệ. • Các thuộc tính khóa chính được gạch dưới. • Các thuộc tính khóa chính PK của quan hệ r không thể mang giá trị null ở bất kỳ bộ nào vì giá trị của khóa chính được dùng để xác định các bộ. – t[PK] <>null t r(R) • Các thuộc tính khác của r cũng có thể có ràng buộc quy định không được có giá trị null, cho dù chúng không tham gia vào khóa chính. • Ex: Sinhvien(MaSv, TenSv, Khoa)
Alternate Key • Là Candidate Key nhưng không được chọn là primary key. • Được khai báo ràng buộc duy nhất (uniqueness constraint)
A primary key in a table a) can be the same value as other primary key values in the same table b) must be different from foreign keys in another table c) must not contain NULL. d) must be a number andmust be different from a primary key in another table
Cơ sở dữ liệu quan hệ ❖ Cơ sở dữ liệu quan hệ (relational database) bao gồm các bảng (quan hệ) biểu diễn các thực thể và các khóa chính / khóa ngoại biểu diễn các mối liên kết.
Cơ sở dữ liệu quan hệ Primary Key Foreign Key (implements 1:N relationship between customer and order) Combined, these are a composite primary key (uniquely identifies the order line) individually they are foreign keys (implement M:N relationship between order and product)
Cơ sở dữ liệu quan hệ Cơ sở dữ liệu quan hệ.
Sự tương ứng với mô hình ER ❖ Mỗi quan hệ (bảng) tương ứng với một kiểu thực thể hoặc với một kiểu mối liên kết nhiều - nhiều. ❖ Mỗi hàng tương ứng với một thể hiện thực thể hoặc với một thể hiện mối liên kết nhiều - nhiều. ❖ Mỗi cột tương ứng với một thuộc tính. ❖ Từ quan hệ (relation) trong cơ sở dữ liệu quan hệ không có cùng nghĩa với từ mối quan hệ (relationship) trong mô hình ER.
Lược đồ cơ sở dữ liệu ❖ Lược đồ cơ sở dữ liệu  database schema  Lược đồ cơ sở dữ liệu là một tập hợp các lược đồ quan hệ.  Trong một lược đồ cơ sở dữ liệu, các tên lược đồ quan hệ là duy nhất. Lược đồ cơ sở dữ liệu: Emp (Empnum, Name, Sal, Tax, Mgrnum, Deptnum) Dept (Deptnum, Name, Area, Mgrnum) Supplier (Snum, Name, City) Supply (Snum, Pnum, Deptnum, Quan)
Ràng buộc toàn vẹn ❖ Ràng buộc toàn vẹn  integrity constraint  Ràng buộc toàn vẹn là một qui tắc mà tất cả các dữ liệu trong CSDL phải thỏa mãn qui tắc này. ❖ Ràng buộc miền giá trị  domain constraint  Các giá trị cho phép của một thuộc tính. ❖ Toàn vẹn thực thể  entity integrity  Thuộc tính khóa chính không có giá trị rỗng (null value)
Ràng buộc toàn vẹn ❖ Qui tắc hoạt động  action assertion  Các qui tắc nghiệp vụ (business rule).
Ràng buộc toàn vẹn Định nghĩa miền trị cho các thuộc tính
Ràng buộc toàn vẹn ❖ Ràng buộc toàn vẹn tham chiếu  referential integrity constraint  Ràng buộc toàn vẹn tham chiếu là một qui tắc mà tất cả các giá trị của khóa ngoại (nếu khác null) trong quan hệ bên phía nhiều phải có trong các giá trị của khóa chính trong quan hệ bên phía một.
Ràng buộc toàn vẹn ❖ Ràng buộc toàn vẹn tham chiếu  Qui tắc xóa các hàng dữ liệu  Hạn chế (restrict): không cho phép xóa các hàng bên phía cha (parent) nếu tồn tại các hàng liên quan bên phía phụ thuộc (dependent).  Tầng (cascade): tự động xóa các hàng bên phía phụ thuộc tương ứng với các hàng bên phía cha.  Gán null (set-to-null): gán null cho khóa ngoại của các hàng bên phía phụ thuộc tương ứng với các hàng bên phía cha. Không áp dụng cho các thực thể yếu.
Ràng buộc toàn vẹn Referential integrity constraints are drawn via arrows from dependent to parent table Ví dụ về ràng buộc toàn vẹn tham chiếu
Đại số Quan hệ
Ngôn ngữ truy vấn • Query Language (QL): ngôn ngữ cho phép user thực hiện và rút trích dữ liệu từ CSDL. • QL ≠PL (ngôn ngữ lập trình) – QL không nhằm mục đích dùng cho các ứng dụng phức tạp. – QL hỗ trợ truy xuất dễ dàng tới các tập tin dữ liệu lớn. • Ngôn ngữ truy vấn (toán học) là cơ sở của ngôn ngữ thực sự (vd., SQL). • Hiểu được đại số quan hệ là chìa khóa để hiểu cách xử lý và tối ưu hóa câu truy vấn.
Đại số Quan hệ • 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 quan hệ, kết quả của một câu truy vấn là một thể hiện quan hệ. • Chuỗi các phép toán đại số quan hệ hình thành nên biểu thức đại số quan hệ mà kết quả của nó cũng trả về một thể hiện quan hệ.
Các phép toán quan hệ • Phép chọn • Phép chiếu • Phép gán • Phép đổi tên • Các phép toán được hình thành từ lý thuyết tập hợp toán học: – Phép hội – Phép giao – Phép trừ – Phép tích Cartesian • Phép kết • Phép chiếu tổng quát • Các phép toán quan hệ khác: Phép chia, OUTER JOIN, AGGREGATE FUNCTIÓN và GROUPING.
Phép chọn (Selection Operator) • Dùng để trích chọn các bộ từ quan hệ r. Các bộ được trích chọn phải thỏa mãn điều kiện P. • Ký hiệu: P(r) = {t | t r  P(t)} • r: quan hệ được chọn • P: biểu thức mệnh đề điều kiện: • op hay • op: =, ≠, , , • Các điều kiện có thể liên kết với nhau thông qua , ,  • Kết quả trả về là một quan hệ, có cùng danh sách thuộc tính với quan hệ r. • Phép chọn có tính giao hoán: • P1(P2(r)) = P2(P1(r)) = P1  P2(r)
Example A B C D A B C D 1 7 1 7  5 7   23 10   12 3   23 10 A=B  D 5(r) Quan hệ r • Cho biết: – Những nhân viên thuộc phòng số 4. – Những nhân viên có lương > 3000 – Những nhân viên thuộc phòng số 4 và lương > 3000, hoặc những nhân viên thuộc phòng số 5.
Phép chiếu (Project Operation) • Dùng để trích chọn một vài cột của quan hệ r được chỉ ra trong danh sách thuộc tính. • Ký hiệu: A1,A2, ,Ak(r) • r: quan hệ. • A1,A2, ,Ak: các thuộc tính được chiếu. • Kết quả trả về là một quan hệ có k thuôc tính theo thứ tự được liệt kê. • Số lượng các bộ trong một quan hệ kết quả của phép chiếu luôn luôn ít hơn hoặc bằng số lượng các bộ trong r. Các dòng trùng nhau sẽ được loại bỏ khỏi quan hệ kết quả. • A1,A2, ,Ak( A1,A2, ,Al(r)) = A1,A2, ,Ak(r), với k l • Phép chiếu không có tính giao hoán
Example A B C A C A C 10 1 1 1 20 1 1  1  30 1  1  2  40 2  2 Quan hệ r A,C(r) • Cho biết: – Họ tên và lương của tất cả nhân viên. – Mã phòng và lương của tất cả nhân viên
Phép gán (Assignment Operation) • Cung cấp một cách thức để diễn tả câu truy vấn phức tạp. • Ý tưởng: viết câu truy vấn như là một chương trình tuần tự gồm một chuỗi các phép gán, theo sau là một biểu thức có giá trị được trình bày như là kết quả của câu truy vấn. • Ký hiệu:  • Việc gán luôn luôn thực hiện cho một biến quan hệ tạm. • Biến nằm bên trái của biểu thức gán có thể được sử dụng trong các biểu thức theo sau.
Phép đổi tên (Rename Operation) • Cho phép đặc tên, và do đó để tham chiếu tới kết quả của biểu thức đại số quan hệ. • Cho phép tham chiếu tới một quan hệ bằng nhiều tên (vd., nếu cùng một quan hệ được dùng hai lần trong một biểu thức đại số quan hệ). • Đổi tên quan hệ và tên thuộc tính: • Cho một biểu thức đại số quan hệ E có n thuộc tính, biểu thức (A1, A2, , An)(E) trả về kết quả biểu thức E dưới tên , và với các tên thuộc tính đã được thay đổi thành A1, A2, , An.
Đổi tên quan hệ và tên thuộc tính • Đổi tên quan hệ: – Cho một biểu thức đại số quan hệ E, biểu thức (E) trả về kết quả biểu thức E dưới tên . • Đổi tên thuộc tính: – Cho một biểu thức đại số quan hệ E có n thuộc tính, biểu thức (A1, A2, , An)(E) trả về kết quả biểu thức E với các tên thuộc tính đã được thay đổi thành A1, A2, , An.
Chuỗi các phép toán • Kết hợp các phép toán đại số quan hệ với nhau bằng một trong hai cách: – Tạo một biểu thức đại số quan hệ bằng cách lồng các phép toán với nhau. – Áp dụng từng phép toán một, ở mỗi lần áp dụng phép toán cho ra quan hệ kết quả.
Examle • Tìm họ tên và lương nhân viên làm việc ở phòng số 4. HONV,TENLOT,TENNV,LUONG(PHG=4(NHANVIEN )) • hoặc: NV_P4  PHG=4(NHANVIEN) KQ  HONV,TENLOT,TENNV,LUONG(NV_P4) • hoặc có thể đổi tên bằng cách liệt kê các tên thuộc tính mới trong dấu ngoặc: NV_P4  PHG=4(NHANVIEN) KQ(HO,LOT,TEN,LUONGCB)  HONV,TENLOT,TENNV,LUONG(NV_P4)
Các phép toán tập hợp • Các phép toán thực hiện trên 2 quan hệ xuất phát từ lý thuyết tập hợp của toán học – Phép hội: r  s – Phép giao: r  s – Phép trừ: r – s – Phép tích Cartesian: r s • Đối với các phép toán , , hay –, các quan hệ r và s phải khả hợp.
Quan hệ Khả hợp • Các quan hệ R(A1, A2, , An) và S(B1,B2, , Bn) gọi là khả hợp khi: • Số lượng thuộc tính của r, s phải bằng nhau (cùng bậc n) • Miền giá trị của thuộc tính phải tương thích (dom(Ai)=dom(Bi)), với 1 i n. • Quan hệ kết quả của , , hay – có cùng tên thuộc tính với quan hệ đầu tiên r (quy ước).
Phép hội (Union Operation) • Ký hiệu: r  s A B 1 A B • Định nghĩa: 2 2  1 r  s = {t | t r  t s}  3 • r và s là hai quan hệ khả hợp. Quan hệ r Quan hệ s A B • Ex1: 1 • Ex2: Mã nhân viên có tham gia 2 đề án hoặc có thân nhân hoặc  1 cả hai.  3 r  s
Phép Giao (Set-Intersection Operation) • Ký hiệu: r  s A B • Định nghĩa: 1 A B 2 2 r  s = {t | t r  t  1  3 s} Quan hệ r Quan hệ s • r và s là hai quan hệ khả hợp. A B • Ex1: 2 • Ex2: Mã nhân viên có r  s người thân và được phân công vào đề án.
Phép trừ (Set Difference Operation) • Ký hiệu: r – s. A B • Định nghĩa: 1 A B • r – s = {t | t r  t s} 2 2  1  3 • r và s là hai quan hệ khả Quan hệ r Quan hệ s hợp. • Ex1: A B • Ex2: Mã nhân viên 1 không có thân nhân  1 nào cả. r – s
Phép tích (Cartesian Product Operation) • Ký hiệu: r s • Định nghĩa: r s = {t.q | t r  q s} • Các thuộc tính của r(R) và s(S) khác nhau (i.e. r  s = ). • Nếu các thuộc tính của r(R) và s(S) không khác nhau thì phải đổi tên chúng. • Mỗi bộ t của tập kết quả là tổ hợp giữa 1 bộ t1 trong r và 1 bộ t2 trong s sao cho: t[A1, A2, , Anr] = t1 và t[B1, B2, , Bms] = t2 • KQ(A1, A2, , Anr, B1, B2, , Bms)  r(A1, A2, , Anr) s(B1, B2, , Bms) • Nếu r có nr bộ và s có ms bộ thì r s có nr ms bộ.
Example 1 C D E A B C D E A B 10 + 1 10 + 1  10 + 1  10 +  2  20 − 1  20 −  10 − 1  10 − Quan hệ r  2 10 + Quan hệ s  2  10 +  2  20 − • Nhận xét: Phép tích Cartersian là  2  10 − một phép tính vô nghĩa nếu đứng riêng. Tuy nhiên, nó có thể kết hợp r s các bộ liên quan từ hai quan hệ nếu tiếp theo sau phép toán chọn  phù hợp (xem ví dụ trang sau).
Example 2 A B C D E 1 10 + 1  10 + A B C D E 1  20 − 1 10 + 1  10 −  2  10 +  2 10 +  2  20 −  2  10 +  2  20 − A=C(r s)  2  10 − r s
Example 3 • Kết hợp từng bộ PHONGBAN với bộ NHANVIEN của người quản lý (trưởng phòng). Với mỗi phòng ban, cho biết thông tin về người trưởng phòng. PB_NV  PHONGBAN NHANVIEN PB_TRPHG_INFO  MANV=TRPHGPB_NV • Hoặc: MANV=TRPHG(PHONGBAN NHANVIEN)
Example 4 • Cho biết lương cao nhất trong công ty. – Quan hệ chứa những lương không phải là lớn nhất temp= NHANVIEN.LUONG(NHANVIEN.LUONG NV.LUONG(NHA NVIEN NV(NHANVIEN))) – Thực hiện trừ giữa quan hệ và quan hệ tạm LUONG(NHANVIEN) - temp
Example 5 • Cho biết những phòng ban có cùng địa điểm với phòng số 5. – Tìm địa điểm của phòng số 5. DIADIEM(MAPHG=5(DIADIEM_PHG). – Đổi tên thành PHG_5 và đổi tên thuộc tính DIADIEM thành DD. DIADIEM_PHG(DIADIEM_PHG.DIADIEM=PHG_5.DD(DIADIEM _PHG PHG_5(DD)( DIADIEM(MAPHG=5(DIADIEM_PH G))))).
Phép kết (Join Operation) • Natural Join • Condition Join (Theta Join) • Equi-Join
Natural Join • Ký hiệu: rs (hoặc r*s) • r, s là các quan hệ tương ứng trên các lược đồ R và S. Kết quả trả về là quan hệ trên lược đồ RS. các kết quả có được bằng cách xét đến từng cặp bộ tr r và ts s. • Nếu tr và ts có cùng giá trị tại các thuộc tính trong RS (cùng tên thuộc tính), thì một bộ t được thêm vào quan hệ kết quả sao cho: – t có cùng giá trị như tr trên R. – t có cùng giá trị như ts trên S.
Example • Cho các quan hệ R(A,B,C,D) và S(B,D,E) • Phép kết có thể áp dụng được vì RS . • Lược đồ kết quả là (A,B,C,D,E) • Và kết quả của rs được định nghĩa là: R.A,R.B,R.C,R.D,S.E((R.B=S.BR.D=S.D(r s)) A B C D E A B C D 1 a B D E 1 b 1 a  a 1  2  a 1  a 3 a   4  b 1  a  1 a  1  a  2  b   2  b 2 b  r  s r s
Condition Join • Ký hiệu: rcs • C: điều kiện tồn tại trên các thuộc tính trong RS, lược đồ kết quả cũng giống như trong phép tích Cartesian. Nếu RS  và điều kiện c tham chiếu tới các thuộc tính này, thì một số thuộc tính phải được đổi tên. • Đôi khi còn gọi là Theta join rs • Nguồn gốc: rcs = c(r s) • Chú ý: không giống như điều kiện chọn P trong , c là một điều kiện trên các thuộc tính của cả r và s.
Example A B C D E 1 2 3 3 1 4 5 6 6 2 7 8 9 A B C D E 1 2 3 3 1 s r 1 2 3 6 2 4 5 6 6 2 r B<D s
EQUI-JOIN • Nếu  là phép so sánh bằng “=” thì phép kết được gọi là phép kết bằng – Equi-Join. • Ex: • Tên phòng ban và tên trưởng phòng của mỗi phòng ban. A B C C D 4 5 6 6 8 7 8 9 10 12 A B C SC D 4 5 6 6 8 s r r C=SC( (SC,D)(s)) s
Một số lưu ý • Giữa 2 quan hệ có thể có nhiều hơn một tập thuộc tính kết mang ý nghĩa khác nhau. Vd: Thuộc tính kết Mối kết hợp NHANVIEN.MANV=PHONGBAN.TRPH NHANVIEN quản lý PHONGBAN G NHANVIEN.PHG=PHONGBAN.MAPH NHANVIEN làm việc ở G PHONGBAN • Ex: Tìm tên nhân viên và tên phòng ban mà nhân viên đó làm việc. (?) • Một quan hệ có thể có một tập thuộc tính kết để kết với chính quan hệ đó. Trong trường hợp này, phải sử dụng phép đổi Thuộctên. Vd:tính kết Mối kết hợp NHANVIEN(1).MA_NQL=NHANVIEN(2).MA NHANVIEN(2) giám sát NHANVIEN(1) NV • Ex: Tìm tên nhân viên và tên người giám sát nhân viên đó (?)
Tập đầy đủ các phép toán ĐSQH • Các phép tóan cơ sở: phép chọn, chiếu, hội, giao, trừ, tích. • Tập hợp {, ,,,−, } được gọi là tập đầy đủ các phép toán ĐSQH. • Nếu E1 và E2 là các biểu thức ĐSQH, thì các biểu thức sau đây cũng là các biểu thức ĐSQH: • E1  E2 • E1 − E2 • E1 E2 • P(E1), với P là một vị từ (điều kiện) trên các thuộc tính của E1 • A(E1), với A là danh sách các thuộc tính của E1 • (E1), với  là tên mới cho quan hệ kết quả [và các thuộc tính] được xác định bởi E1.
Phép chia (Division Operator) • Áp dụng cho câu truy vấn có từ “tất cả”. • Mục tiêu: Đưa ra các bộ trong một quan hệ, r, sao cho khớp với tất cả các bộ trong quan hệ khác, s. • Ký hiệu: r  s • Điều kiện: các thuộc tính trong S phải là một tập thuộc tính con của R, i.e., S  R. Cho r, s là các quan hệ tương ứng trên lược đồ R, S R = (A1, A2, , Am,B1, , Bn) S = (B1, , Bn) • Kết quả của phép chia r  s là một quan hệ trên lược đồ R-S = (A1, A2, , Am). • Kết quả của phép chia bao gồm tập hợp các bộ lấy từ r được định nghĩa trên các thuộc tính R-S sao cho khớp với việc kết hợp mọi bộ trong s. r  s = {t | t R-S(r)  u s : t.u r} • Nghĩa là, r  s, với các thuộc tính A1, A2, , Am, là tập hợp chứa tất cả các bộ t sao cho với mọi bộ u trong s, thì có một bộ t.u trong r.
Nguồn gốc • Cho 2 quan hệ r(Z), s(X), trong đó X  Z. • Gọi Y = Z – X (và do đó Z = X  Y). Y là tập hợp các thuộc tính của r nhưng không là thuộc tính của s. • Phép chia có thể được diễn đạt bằng phương pháp ĐSQH cơ sở: • t1  (r) • t2  ((s t1)-r) • t  t1 – t2.
Examples A B C D E A B a a 1 1 a  a 1 D E B 2 a  b 1 a 1 1 3  a  a 1 b 1 2  1  a  b 3 s  1 s  a  a 1  1  a  b 1 A B C  3 A  a  b 1 a   4 r  a   6   1 • Ex3: Mã nhân viên tham gia tất cả r  s r  s đề án.  2 • Ex4: Mã nhân viên tham gia tất cả đề án do phòng số 4 phụ trách. r
Phép chiếu tổng quát • Mở rộng phép chiếu bằng cách cho phép sử dụng các hàm số học trong danh sách chiếu. • F1, F2, , Fk(E) • E là biểu thức đại số quan hệ. • F1, F2, , Fk là các biểu thức số học có liên quan đến hằng và thuộc tính trong lược đồ E. • Ex: Cho quan hệ THETINDUNG(MSTHE, TRIGIATHE, SOTIENSD). Tìm số tiền còn lại trong thẻ: • MSTHE, TRIGIATHE – SOTIENSD(THETINDUNG)
Các phép toán khác • Hàm kết hợp – Aggregate Functions nhận vào tập hợp giá trị và trả về một giá trị đơn. – Avg (average value): giá trị trung bình – Min (minimum value): giá trị nhỏ nhất. – Max (maximum value): giá trị lớn nhất. – Sum (sum of values): tính tổng các giá trị – Count (number of values): đếm số mẫu tin • Phép toán gom nhóm – Grouping  trong ĐSQH: G1, G2, , GnF1(A1), F2(A2), , Fn(An)(E) • E là biểu thức đại số quan hệ • Gi là tên thuộc tính gom nhóm (có thể không có) • Fi là hàm gom nhóm • Ai là tên thuộc tính tính toán trong hàm gom nhóm Fi.
Examples Ex1 Ex2 A B C SUM_C • Tính số lượng nhân viên và 7 27 lương trung bình của nhân  7 viên.– Quan hệ NHANVIEN sau khi  (r) gom nhóm:   3 SUM(C)   10 MANV LUONG MA_NQL PHG 123456789 30000 333445555 5 r 333445555 40000 888665555 5 666884444 38000 333445555 5 453453453 25000 333445555 5 999887777 25000 987654321 4 987654321 43000 888665555 4 987987987 25000 987654321 4 888665555 55000 null 1
• Số lượng nhân viên và COUNT_MANV AVG_LUONG lương trung bình của cả 8 35125 công ty: COUNT(MANV),AVG(LUONG)(NHANVIEN) • Số lượng nhân viên và PHG COUNT_MAN AVG_LUONG lương trung bình của mỗi V phòng ban, không sử dụng 5 4 33250 phép đổi tên: 4 3 31000 1 1 55000 PHGCOUNT(MANV),AVG(LUONG)(NHANVIEN) • Số lượng nhân viên và MAPHONG SONV LUONGTB lương trung bình của mỗi 5 4 33250 phòng ban, có sử dụng 4 3 31000 phép đổi tên: 1 1 55000 MAPHONG,SONV,LUONGTB(PHGCOUNT(MANV),AVG(LUONG)(NHANVIEN))
Outer Join • Mở rộng phép kết để tránh sự mất mát thông tin • Thực hiện phép kết và sau đó thêm vào kết quả của phép kết các bộ của một quan hệ mà không phù hợp với các bộ trong quan hệ kia. • 3 hình thức: – Left outer join: _ – Right outer join: _ – Full outer join: __
Outer Join
HONV TENLOT TENNV TENPHG Example: Nguyen Thanh Tung Nghien cuu Tran Hong Quang Dieu hanh Pham Van Vinh Quan ly HONV,TENLOT,TENNV,TENPHG(NHANVIEN MANV=TRPHGPHONGBAN) HONV TENLOT TENNV TENPHG Dinh Ba Tien Null Nguyen Thanh Tung Nghien cuu Bui Ngoc Hang Null Le Quynh Nhu Null Nguyen Manh Hung Null Tran Thanh Tam Null Tran Hong Quang Dieu hanh Pham Van Vinh Quan ly HONV,TENLOT,TENNV,TENPHG(NHANVIEN _MANV=TRPHGPHONGBAN)
Các thao tác cập nhật trên quan hệ • Nội dung của CSDL có thể được cập nhật bằng cách dùng các thao tác: Thêm, Xóa, Sửa. • Tất cả các thao tác này được diễn đạt thông qua phép toán gán: • rnew  {Thêm, Xóa, Sửa}(rold)
Thêm (Insertion) • Hoặc nêu ra một bộ cần chèn, hoặc viết một câu truy vấn mà kết quả là một tập hợp các bộ cần chèn. • Trong ĐSQH, thao tác chèn được diễn đạt như sau: r  r  E – r là quan hệ và E là biểu thức ĐSQH. • Ex: Phân công cho nhân viên 123456789 làm thêm đề án số 20 với số giờ là 10. • PHANCONG  PHANCONG  {(‘123456789’,20,10)}
Xoá (Deletion) • Yêu cầu xóa được diễn đạt như câu truy vấn, chỉ khác ở chổ, thay vì hiển thị các bộ kết quả với người dùng, thì bộ được chọn bị xóa khỏi CSDL. • Chỉ có thể xóa toàn bộ bộ, không thể chỉ xóa một vài giá trị trên các thuộc tính nào đó. • Thao tác xóa được diễn đạt trong ngôn ngữ ĐSQH như sau: r  r – E – r là quan hệ và E là câu truy vấn ĐSQH. • Ex1: Xóa tất cả những phân công đề án cho nhân viên 123456789 • PHANCONG  PHANCONG – (MA_NVIEN=‘123456789’(PHANCONG)) • Ex2: Xóa tất cả những phân công đề án mà địa điểm đề án ở “HA NOI” • r1  DDIEM_DA=‘HANOI’(PHANCONG SODA=MADADEAN) • r2  MA_NV,SODA,THOIGIAN(r1) • PHANCONG  PHANCONG – r2.
Sửa (Updating) • Cơ chế làm thay đổi một giá trị trong một bộ mà không làm thay đổi tất cả giá trị trong bộ đó. • Để cập nhật, sử dụng phép chiếu tổng quát hóa như sau: r  F1, F2, , Fn(r) – Mỗi Fi có giá trị trả về là giá trị mới cho thuộc tính thứ i của r, thuộc tính thứ i có thể giữ nguyên (nếu không muốn cập nhật) hoặc sẽ được cập nhật với giá trị mới đó. – Fi là một biểu thức, bao gồm hằng và thuộc tính của r, để đưa ra giá trị mới cho thuộc tính đó. • Có thể được biểu diễn bằng một chuỗi các thao tác xóa và thêm. Phép toán xóa sẽ xóa các bộ có giá trị cũ và phép toán thêm sẽ chén thêm các bộ có giá trị mới. • Ex1: Tăng thời gian làm việc của tất cả nhân viên lên 1.5 lần • PHANCONG  MA_NV,SODA,THOIGIAN*1.5(PHANCONG) • Ex2: Trong quan hệ PHANCONG, các nhân viên làm việc trên 30 giờ sẽ tăng thời gian làm việc 1.5 lần, còn lại sẽ tăng thời gian làm việc 2 lần. • PHANCONG  MA_NV,SODA,THOIGIAN*1.5(THOIGIAN>30(PHANCONG))  MA_NV,SODA,THOIGIAN*2(THOIGIAN 30(PHANCONG))
BAØI TAÄP ÑAÏI SOÁ QUAN HEÄ Cho löôïc ñoà cô sôû döõ lieäu duøng ñeå quaûn lyù hoà sô sinh vieân bao goàm caùc löôïc ñoà quan heä Sv(sinh vieân), Lop(Lôùp), kh(khoa), Mh(moân hoïc), Kq(keát quaû) ñöôïc moâ taû bôûi caùc löôïc ñoà quan heä nhö sau: Sv(MASV, HOTEN,NU, NGAYSINH, MALOP, TINH, HOCBONG) Taân töø:Moãi sinh vieân coù moãi MASV duy nhaát. Moãi MASV xaùc ñònh taát caû caùc thuoäc tính coøn laïi cuûa sinh vieân ñoù. Lop(MALOP,TENLOP,SISO,MAKHOA) Taân töø:Moãi lôùp coù moät maõ lôùp duy nhaát, moãi lôùp chæ thuoäc veà moät khoa naøo ñoù. Kh(MAKHOA,TENKHOA, SOCBGD) Taân töø:Moãi khoa coù moãi MAKHOA duy nhaát. Moãi MAKHOA xaùc ñònh taát caû caùc thuoäc tính coøn laïi cuûa khoa ñoù. Mh(MAMH, TENMH, SOTIET) Taân töø:Moâi Moân hoïc coù moät MAMH duy nhaát. Moãi MAMH xaùc ñònh taát caû caùc thuoäc tính coøn laïi cuûa moân hoïc ñoù. Kq(MASV, MAMH, DIEMTHI) Taân töø:Moãi sinh vieân cuøng vôùi moät moân hoïc xaùc dònh duy nhaát moät ñieåm thi
BAØI TAÄP ÑAÏI SOÁ QUAN HEÄ Xaùc ñònh khoùa quan heä: Sv(MASV, HOTEN,NU, NGAYSINH, MALOP, TINH, HOCBONG) Lop(MALOP,TENLOP,SISO,MAKHOA) Kh(MAKHOA,TENKHOA, SOCBGD) Mh(MAMH, TENMH, SOTIET) Kq(MASV, MAMH, DIEMTHI) Xaùc ñònh caùc quan heä a) Ñaët rSv laø quan heä cuûa Sv: rSv.{MASV,HOTEN,HOCBONG} b) Ñaët rLop laø quan heä cuûa Lop MALOP ((rSv   rLop): NU vaø MAKHOA=’CNTT’).{MASV,HOTEN,HOCBONG} c) Ñaët rKq laø quan heä cuûa kQ, rMh laø quan heä cuûa Mh ((((rKq I> <I rLop):MAKHOA=’CNTT’).{MASV,HOTEN,TENMH,DIEMTHI} d) Ñaët rBangDiem laø quan heä cuûa caâu c) rPhieuDiem = rBangDiem:MASV=’99001’
Chương 4 Chuyển từ mô hình thực thể kết hợp sang mô hình quan hệ
Biến đổi ERD thành các quan hệ ❖ Qui tắc 1: Biến đổi một kiểu thực thể thành một quan hệ.  Đối với kiểu thực thể thông thường (regular entity type): khóa của quan hệ là khóa của kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của kiểu thực thể.  Quan hệ chỉ chứa các thuộc tính thành phần của thuộc tính phức hợp.  Quan hệ không chứa các thuộc tính đa trị.
Biến đổi ERD thành các quan hệ CUSTOMER entity type with simple attributes CUSTOMER relation Biến đổi kiểu thực thể thông thường
Biến đổi ERD thành các quan hệ CUSTOMER entity type with composite attribute CUSTOMER relation with address detail Biến đổi thuộc tính phức hợp
Biến đổi ERD thành các quan hệ ❖ Qui tắc 2: Biến đổi thuộc tính đa trị thành một quan hệ.  Quan hệ chứa khóa của kiểu thực thể và thuộc tính đa trị.  Khóa của quan hệ gồm khóa của kiểu thực thể và thuộc tính đa trị.
Biến đổi ERD thành các quan hệ Multivalued attribute becomes a separate relation with foreign key 1–to–many relationship between original entity and new relation Biến đổi thuộc tính đa trị
Biến đổi ERD thành các quan hệ ❖ Qui tắc 3: Biểu diễn mối liên kết 1-ngôi hoặc 2-ngôi có lượng số một-một.  Đặt khóa của kiểu thực thể bên phía bắt buộc và các thuộc tính của mối liên kết vào quan hệ của kiểu thực thể bên phía tùy chọn.
Biến đổi ERD thành các quan hệ EMPLOYEE entity with Manages relationship EMPLOYEE relation with recursive foreign key Biến đổi mối liên kết một ngôi có lượng số một - một
Biến đổi ERD thành các quan hệ Biến đổi mối liên kết hai ngôi có lượng số một - một
Biến đổi ERD thành các quan hệ ❖ Qui tắc 4: Biểu diễn mối liên kết 1-ngôi hoặc 2-ngôi có lượng số một-nhiều.  Đặt khóa của kiểu thực thể bên phía một và các thuộc tính của mối liên kết vào quan hệ của kiểu thực thể bên phía nhiều.
Biến đổi ERD thành các quan hệ EMPLOYEE entity with Manages relationship EMPLOYEE relation with recursive foreign key Biến đổi mối liên kết một ngôi có lượng số một - nhiều
Biến đổi ERD thành các quan hệ Note the mandatory one Again, no null value in the foreign key this is because of the mandatory minimum cardinality Biến đổi mối liên kết hai ngôi có lượng số một - nhiều
Biến đổi ERD thành các quan hệ ❖ Qui tắc 5: Biến đổi mối liên kết 1-ngôi hoặc 2- ngôi có lượng số nhiều-nhiều thành một quan hệ.  Quan hệ chứa các khóa của các kiểu thực thể tham gia vào mối liên kết.  Khóa của quan hệ gồm cả hai khóa của hai kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của mối liên kết.
Biến đổi ERD thành các quan hệ Bill-of-materials relationships (M:N) ITEM and COMPONENT relations Biến đổi mối liên kết một ngôi có lượng số nhiều - nhiều
Biến đổi ERD thành các quan hệ The Supplies relationship will need to become a separate Composite primary key relation New intersection Foreign key relation Foreign key Biến đổi mối liên kết hai ngôi có lượng số nhiều - nhiều
Biến đổi ERD thành các quan hệ ❖ Qui tắc 6: Biến đổi mối liên kết 3-ngôi thành một quan hệ.  Quan hệ chứa ba khóa của ba kiểu thực thể tham gia vào mối liên kết.  Mối liên kết có bao nhiêu kiểu thực thể bên phía một thì quan hệ có bấy nhiêu khóa: đối với một kiểu thực thể bên phía một thì khóa của quan hệ gồm cả hai khóa của hai kiểu thực thể còn lại. Nếu không có kiểu thực thể bên phía một thì khóa của quan hệ bao gồm cả ba khóa của ba kiểu thực thể.  Thuộc tính của quan hệ là thuộc tính của mối liên kết.
Biến đổi ERD thành các quan hệ Biến đổi mối liên kết ba ngôi
Biến đổi ERD thành các quan hệ Biến đổi mối liên kết ba ngôi
Chương 5 Ngôn ngữ truy vấn SQL
Literal value “New York”, 1056.25, #12/2/2001# Constant laø moät teân ñaïi dieän cho moät giaù trò khoâng thay ñoåi nhö :True, False,Null Toaùn töû soá hoïc +,-,*,/,\,^,Mod (ALGEBRA OPERATORS) Toaùn töû luaän lyù NOT,AND,OR (STANDART LOGICAL OPERATORS, BOOLEAN OPERATOR) Toaùn töû so saùnh ,>=,=, AN, AM, AC % Ñaïi dieän cho moät chuoãi kyù töï baát kyø. A? -> AI, ANH, # Ñaïi dieän cho moät kyù soá A## ->A13, A24, A35 [] Ñaïi dieän cho caùc kyù töï naèm trong A[IN] -> AI, AN - Ñaïi dieän cho caùc kyù töï naèm trong khoaûng A[M-O] -> AM, AN, AO ! Ñaïi dieän cho kyù töï khoâng phaûi kyù töï naèm sau ! A[!C] -> AA, AB, AD, Haøm IIf(ñieàu kieän, trò 1, trò 2),Date(),Now(bieåu thöùc ngaøy), Time(bieåu thöùc ngaøy),Day(bieåu thöùc ngaøy), Month(bieåu thöùc ngaøy),Year(bieåu thöùc ngaøy),Len( bieåu thöùc chuoãi),Chr(maõ Ascii), InStr(Start, s1, s2),LCase(s), UCase(s),Left(s, n), Right(s, n),Mid(s, i, n),Nz(v1, v2) For which task in SQL would you use an IN clause a) To query the database for unknown values b) To query the database for a range of values c) To query the database for a character pattern d) To query the database for values in a specified list
Ngoân ngöõ truy vaán ñöôïc chia thaønh ba loaïi: –Data definition query –Dynaset query (data retrieval query) –Action query (data modification query) Data definition query Taïo löôïc ñoà quan heä rSV: Create Table rSV (MASV Text (10) CONSTRAINT khoaChinh PRIMARY KEY,HOTEN Text (30), NU YesNo, NGAYSINH Date, MALOP Text (10), TINH Text (50), HOCBONG Double) MASV HOTEN NU NGAYSINH MALOP TINH HOCBONG Taïo löôïc ñoà quan heä rKQ: Create Table rKQ (MASV text (10),MAMH Text (10), DIEMTHI Double,CONSTRAINT khoaChinh PRIMARY KEY (MASV,MAMH) ) MASV MAMH DIEMTHI
Theâm coät DTHOAI vaøo löôïc ñoà quan heä rSV: Alter Table rSV Add Column DTHOAI Text (20) Söûa kieåu kieåu döõ lieäu cuûa moät coät: Alter Table rSV Alter Column DTHOAI double Xoùa coät cuûa moät löôïc ñoà ñaõ coù: Alter Table rSv Drop Column DTHOAI Xoùa raøng buoäc khoùa chính: Alter Table rSv Drop Constraint khoaChinh Theâm raøng buoäc khoùa chính: Alter Table rSv Add Constraint khoaChinh Primary Key (MASV) Theâm raøng buoäc mieàn giaù trò: ALTER TABLE rSV WITH NOCHECK ADD CONSTRAINT mgthb CHECK (hocBong <= 500000 ) Theâm khoùa ngoaïi: ALTER TABLE rSV ADD FOREIGN KEY REFERENCES rLop (maLop) Which of the following will actually describe the layout of a table to the DBMS? a) SELECT TABLE b) DROP TABLE c) CREATE TABLE d) DELETE FROM TABLE
Dynaset query (data retrieval query) Use the following tables and data. The primary keys are underlined. All of the fields are strings, except STATUS, WEIGHT, and QUANTITY, which are Integers, and Shipment Date, which is Date. The table names are Shipper, Parts, Shipment, respectively. PARTS COLOR PNUM PNAME COLOR WEIGHT Green P1 Nut Red 12 Blue 2 P2 Bolt Green 17 Red 1 2 P3 Screw Blue 17 Blue 2 P4 Screw Red 14 Red 1 P5 Cam Blue 12 2 P6 Cog Red 19 SELECT DISTINCT color FROM parts WHERE How many records does the following SQL example return? color = 'Blue' OR weight > 12; SELECT color COLOR FROM parts Green WHERE color = 'Blue' OR weight > 12; Blue 1: color=‘Blue’ Red 2: weight > 12
How many records does the following SQL example return? SELECT parts.COLOR, Shipment.Quantity FROM parts INNER JOIN Shipment ON parts.PNUM = Shipment.PNUM WHERE (((parts.COLOR)='red') AND ((parts.WEIGHT)>10)); PARTS SHIPMENT PNUM PNAME COLOR WEIGHT SNUM PNUM Shipment Date Quantity P1 Nut Red 12 S1 P1 9/23/2001 300 P2 Bolt Green 17 S1 P2 3/4/2000 200 P3 Screw Blue 17 S1 P6 8/30/1999 100 P4 Screw Red 14 S2 P1 4/12/1999 300 P5 Cam Blue 12 S2 P2 5/23/2000 400 P6 Cog Red 19 S3 P2 7/8/2001 200 S4 P2 9/21/2000 200 The result of the SQL S4 P4 10/1/2001 300 COLOR QUANTITY Select Distinct RED 300 RED 100 COLOR QUANTITY RED 300 RED 300 RED 300 RED 100
SHIPMENT SNUM PNUM Shipment Date Quantity PARTS S1 P1 9/23/2001 300 PNUM PNAME COLOR W S1 P2 3/4/2000 200 P1 Nut Red 12 S1 P6 8/30/1999 100 P2 Bolt Green 17 S2 P1 4/12/1999 300 P3 Screw Blue 17 S2 P2 5/23/2000 400 P4 Screw Red 14 S3 P2 7/8/2001 200 P5 Cam Blue 12 S4 P2 9/21/2000 200 P6 Cog Red 19 S4 P4 10/1/2001 300 WHERE (((parts.COLOR)='red') AND ((parts.WEIGHT)>10)) FROM parts INNER JOIN Shipment ON parts.PNUM = Shipment.PNUM SNUM PNUM Shipment Date Quantity PNUM PNAME COLOR WEIGHT 1 2 S1 P1 9/23/2001 300 P1 Nut Red 12 2 S1 P2 3/4/2000 200 P2 Bolt Green 17 1 2 S1 P6 8/30/1999 100 P6 Cog Red 19 1 2 S2 P1 4/12/1999 300 P1 Nut Red 12 2 S2 P2 5/23/2000 400 P2 Bolt Green 17 2 S3 P2 7/8/2001 200 P2 Bolt Green 17 2 S4 P2 9/21/2000 200 P2 Bolt Green 17 1 2 S4 P4 10/1/2001 300 P4 Screw Red 14
REGION STATION STATIONID RAINFALL SUNSHINE REGION REGIONID NAME 1 10 2 1 1 BORDERS 2 11 4 1 2 FIFE 3 55 0 3 3 LOTHIAN 4 23 1 3 5 17 6 2 6 11 4 2 7 41 3 2 The following database contains weather measurements for a number of stations around the UK Each station is in a region, each station records a value for rainfall in cm and sunshine in hours. region(regionid, name) station(stationid, rainfall, sunshine, region) Select the SQL statement which will list the station identity and rainfall for any station with more rainfall than station 6. a) SELECT stationid, rainfall FROM station WHERE rainfall > (SELECT stationid, rainfall FROM station WHERE stationid=6)
b) SELECT a.stationid, a.rainfall d) SELECT stationid, rainfall FROM station a, station b FROM station WHERE a.rainfall > b.rainfall AND b.stationid = 6; WHERE stationid=6 c) SELECT a.stationid, a.rainfall e) SELECT b.stationid, a.rainfall FROM station a, station b FROM station a, station b WHERE a.rainfall < b.rainfall AND b.stationid = 6 WHERE a.rainfall < b.rainfall AND a.stationid = 6 FROM station a, station b STATIONID RAINFALL SUNSHINE REGION STATIONID RAINFALL SUNSHINE REGION 1 10 2 1 1 10 2 1 1 10 2 1 6 11 4 2 2 11 4 1 6 11 4 2 x 3 55 0 3 6 11 4 2 x 4 23 1 3 6 11 4 2 x 5 17 6 2 6 11 4 2 6 11 4 2 6 11 4 2 x 7 41 3 2 6 11 4 2 1 10 2 1 7
SELECT Sum(parts.WEIGHT) AS [SUM] FROM (parts INNER JOIN Shipment ON parts.PNUM = Shipment.PNUM) INNER JOIN Shipper ON Shipment.SNUM = Shipper.SNUM GROUP BY Shipper.CITY, parts.COLOR HAVING (((Shipper.CITY)='London') AND ((parts.COLOR)='Red')); SHIPPER SHIPMENT SNUM SNAME STATUS CITY SNUM PNUM Shipment Date Quantity S1 Smith 20 London S1 P1 9/23/2001 300 S2 Jones 10 Paris S1 P2 3/4/2000 200 S3 Blake 30 Paris S1 P6 8/30/1999 100 S4 Clark 20 London S2 P1 4/12/1999 300 S5 Adams 30 Athens S2 P2 5/23/2000 400 PARTS S3 P2 7/8/2001 200 PNUM PNAME COLOR WEIGHT S4 P2 9/21/2000 200 P1 Nut Red 12 S4 P4 10/1/2001 300 P2 Bolt Green 17 Aggregate function, group function P3 Screw Blue 17 SUM () MAX() P4 Screw Red 14 MIN() AVG() P5 Cam Blue 12 COUNT() P6 Cog Red 19
FROM (parts INNER JOIN Shipment ON parts.PNUM = Shipment.PNUM) INNER JOIN Shipper ON Shipment.SNUM = Shipper.SNUM SNUM PNUM Quantity PNUM PNAME COLOR WEIGHT SNUM SNAME STATUS CITY S1 P1 300 P1 Nut Red 12 S1 Smith 20 London S1 P2 200 P2 Bolt Green 17 S1 Smith 20 London S1 P6 100 P6 Cog Red 19 S1 Smith 20 London S2 P1 300 P1 Nut Red 12 S2 Jones 10 Paris S2 P2 400 P2 Bolt Green 17 S2 Jones 10 Paris S3 P2 200 P2 Bolt Green 17 S3 Blake 30 Paris S4 P2 200 P2 Bolt Green 17 S4 Clark 20 London S4 P4 300 P4 Screw Red 14 S4 Clark 20 London GROUP BY Shipper.CITY, parts.COLOR SNUM PNUM Quantity PNUM PNAME COLOR WEIGHT SNUM SNAME STATUS CITY S1 P2 200 P2 Bolt Green 17 S1 Smith 20 London S4 P2 200 P2 Bolt Green 17 S4 Clark 20 London S1 P1 300 P1 Nut Red 12 S1 Smith 20 London S1 P6 100 P6 Cog Red 19 S1 Smith 20 London S4 P4 300 P4 Screw Red 14 S4 Clark 20 London S2 P2 400 P2 Bolt Green 17 S2 Jones 10 Paris S3 P2 200 P2 Bolt Green 17 S3 Blake 30 Paris S2 P1 300 P1 Nut Red 12 S2 Jones 10 Paris HAVING (((Shipper.CITY)='London') AND ((parts.COLOR)='Red')); COLOR WEIGHT CITY WEIGHT Which of the following is not a Group function? Green 34 London 45 a) AVG Red 45 London Green 34 Paris b) SQRT Red 12 Paris c) SUM d) MAX e) MIN
Which of the following statements will return the names of the products with Product ID 10, 11, or 42? a) SELECT ProductName FROM products WHERE ProductID IN (10,11,42) b) SELECT ProductName FROM products WHERE ProductID IN 10 OR 11 OR 42 c) SELECT ProductName FROM products WHERE ProductID = (10,11,42) d) SELECT ProductName FROM products WHERE ProductID IS (10,11,42) e) None of the above Truy vaán loàng nhau (nested query, sub query) Laø nhöõng caâu leänh truy vaán maø trong thaønh phaàn WHERE hay HAVING coù chöùa theâm moät caâu leänh Select khaùc. Caâu leänh select khaùc naøy goïi laø subquery. Ta loàng caâu Select vaøo phaàn Where hay Having theo cuù phaùp sau: bieuthuc toanTuSoSanh [ANY | ALL | SOME] (cauLenhSQL) ANY, SOME laø baát kyø, ALL laø taát caû Caùc maãu tin cuûa query chính thoûa maõn toaùn töû so saùnh vôùi baát kyø/ taát caû maãu tin naøo cuûa subquery bieuThuc [NOT] IN (cauLenhSQL) Caùc maãu tin cuûa query chính coù giaù trò baèng vôùi moät giaù trò trong subquery [NOT] EXISTS (cauLenhSQL). Caùc maãu tin cuûa query chính thoûa maõn khi subquery khoâng/coù maãu tin Which line in the following SQL statement contains an error? 1. SELECT title 2. FROM books 3. WHERE pubid EXISTS IN a) Line 1 4. (SELECT pubid b) Line 3 5. FROM books c) Line 4 6. WHERE retail > 41.95); d) Line 6
SUBJECT student(student_id, student_name, address, tutor_id) enrolment(student_id, subject_id, mark) subject_id subject_name department subject(subject_id, subject_name, department) CP1500 Database IT CP1600 Visual Basic IT SELECT subject_name, Count(*) FROM subject, enrolment WHERE subject.subject_id = enrolment.subject_id AND mark > 40 AND subject_id IN (SELECT subject_id FROM enrolment GROUP BY subject_id HAVING Count(*) > 4 ) GROUP BY subject_name SELECT subject_name, Count(*) FROM subject INNER JOIN enrolment ON subject.subject_id = enrolment.subject_id WHERE mark > 40 AND subject_id IN (SELECT subject_id FROM enrolment GROUP BY subject_id HAVING Count(*) > 4 ) GROUP BY subject_name ENROLMENT STUDENT student_id subject_id mark student_id student_name address tutor_id 9901 CP1500 45 9901 Jenny 45 Hay street T01 9902 CP1500 60 9902 Mun chan 7 Blacktown Road T01 9901 CP1600 50 9903 Alexander 281 Pilbara Way T02 9902 CP1600 30 9904 Sarah 25 Hay street T03 9903 CP1600 20 9905 Helga 30 Hay street T03 9904 CP1600 45 9905 CP1600 40
FROM subject, enrolment student_id subject_id mark subject_id subject_name department 1 2 9901 CP1500 45 CP1500 Database IT 1 2 9902 CP1500 60 CP1500 Database IT 9901 CP1600 50 CP1500 Database IT 9902 CP1600 30 CP1500 Database IT 9903 CP1600 20 CP1500 Database IT 9904 CP1600 45 CP1500 Database IT 9905 CP1600 40 CP1500 Database IT 9901 CP1500 45 CP1600 Visual Basic IT 9902 CP1500 60 CP1600 Visual Basic IT 1 2 3 9901 CP1600 50 CP1600 Visual Basic IT 1 9902 CP1600 30 CP1600 Visual Basic IT 1 9903 CP1600 20 CP1600 Visual Basic IT 1 2 3 9904 CP1600 45 CP1600 Visual Basic IT 1 9905 CP1600 40 CP1600 Visual Basic IT 1: subject.subject_id = enrolment.subject_id 2: mark > 40 3: subject_id IN (SELECT subject_id FROM enrolment GROUP BY subject_id HAVING Count(*) > 4 )
SELECT subject_id FROM enrolment GROUP BY subject_id HAVING Count(*) > 4 ENROLMENT subject_id Count(*) student_id subject_id mark CP1500 2 9901 CP1500 45 CP1600 5 9902 CP1500 60 The result 9901 CP1600 50 9902 CP1600 30 subject_id 9903 CP1600 20 CP1600 9904 CP1600 45 9905 CP1600 40 student_id subject_id mark subject_id subject_name department 9901 CP1600 50 CP1600 Visual Basic IT 9904 CP1600 45 CP1600 Visual Basic IT SELECT subject_name, Count(*) subject_name Count(*) GROUP BY subject_name Visual Basic 2
SELECT student_id SELECT student_id FROM student FROM enrolment WHERE student_id NOT IN WHERE NOT EXISTS (SELECT student_id FROM enrolment (SELECT student_id FROM enrolment WHERE subject_id = 'CP1500') WHERE subject_id = 'CP1500') STUDENT ENROLMENT student_id student_name address tutor_id student_id subject_id mark 9901 Jenny 45 Hay T01 x 9901 CP1500 45 9902 Mun chan 7 T01 x 9902 CP1500 60 x 9903 Alexander 281 T02 9901 CP1600 50 x 9904 Sarah 25 T03 9902 CP1600 30 x 9905 Helga 30 T03 9903 CP1600 20 9904 CP1600 45 student_id 9905 CP1600 40 9903 In a nested query, which query is evaluated first? student_id 9904 a) The innerrmost query 9905 b) the outermost query c) both are evaluated simultaneously d) whichever query is selected to execute first by the user
Truy vaán caäp nhaät döõ lieäu (action query, data modification query) Update table Set field1 = bieåuThöùc1, field2 = bieåuThöùc2 [Where ñieàuKieän] Delete From table [Where ñieàuKieän] Customer (cid, cname, city, discnt), Products (pid, pname, city, quantity, price), Agents (aid, aname, city, percent) and Oders (ordno, month, cid, aid, pid, qty, dollars). Raise the price of each product in the PRODUCTS table by 10% for all PRODUCTS that were ordered from customer ‘c01’ a) Update products set price = price * 1.1 where orders.cid = ‘c01’; b) Update products p, Orders o set Products.price = Products.price * 1.1 where orders.cid = ‘c01’; c) Update products set price = .10 * price where pid in (select pid from orders where cid = ‘c01’); d) update products set price = 1.1 * price where pid in (select pid from orders where cid = ‘c01’) ; Delete all customers that did not order product p01 a) Delete ALL Customers where cid not in (selest cid from orders where pid = ‘p01’); b) Delete from Customers where products.pid ‘p01’); d) delete from Customers where cid not in (select cid from orders where pid = ‘p01’); ordno month cid aid pid qty dollars H0001 5 C01 A01 P01 10 100 H0002 6 C01 A02 P02 20 150 H0003 5 C02 A01 P01 15 200 H004 6 C03 A01 P02 20 200
Truy vaán choïn Select [Distinct|Top n[%]] field1 [As alias1][,field2 [As alias2][, ]] From table1 [Inner Join table2 On table1.field1  table2.field2] [Where dieuKien] [Order By field1[Asc|Desc][,field2[Asc|Desc]][, ]]]; Ví duï SELECT MASV,HOTEN,HOCBONG FROM Sv; SELECT MASV,HOTEN,HOCBONG FROM Sv WHERE NU And MALOP='CÑTH2B' And HOCBONG>0; SELECT MASV,HOTEN,HOCBONG FROM Sv ORDER BY HOCBONG DESC; SELECT MASV,HOTEN,TENLOP FROM Sv Inner Join Lop On Sv.MALOP = Kh.LOP;
ñieåm >= 5 MAHV MAMH DIEM MAHV MAMH DIEM 99001 DB 5.0 99001 DB 5.0 99002 DS 2.0 99003 NW 8.0 99003 NW 8.0 INNER JOIN MAHV MAMH DIEM MAMH TENMH 99001 DB 5.0 DB CÔ SÔÛ DÖÕ LIEÄU 99002 NW 2.0 DS CAÁU TRUÙC DÖÕ LIEÄU 99002 DS 8.0 NW MAÏNG MAÙY TÍNH MAHV MAMH DIEM TENMH 99001 DB 5.0 CÔ SÔÛ DÖÕ LIEÄU 99002 NW 2.0 MAÏNG MAÙY TÍNH 99002 DS 8.0 CAÁU TRUÙC DÖÕ LIEÄU
Truy vaán nhoùm döõ lieäu Select [Distinct|Top n[%]] field1 [As alias1][,field2 [As alias2][, ]] From table1 [Inner Join table2 On table1.field1  table2.field2] [Where dieuKienLocMauTinNguon] [Group By fieldGroupBy[,fieldGroupBy[, ]] [Having dieuKienLocMauTinTongHop] [Order By field1 [Asc|Desc][,field2[Asc|Desc]][, ]]] Group functions (aggregate functions) COUNT,MIN,MAX,SUM, AVG Ví duï Laäp danh saùch sinh vieân coù ñaêng kyù ít nhaát laø ba moân hoïc SELECT Kq.MASV,HOTEN,NU,NGAYSINH,COUNT(MAMH) As SLMH FROM Sv Inner Join Kq On Sv.MASV = Kq.MASV GROUP BY Kq.MASV,HOTEN,NU,NGAYSINH HAVING COUNT(MAMH)>=3; Ñeám soá löôïng sinh vieân nöõ cuûa moãi khoa SELECT Kh.MAKHOA,TENKHOA,COUNT(Kh.MAKHOA) AS SOLUONG FROM (Sv Inner Join Lop On Sv.MALOP = LOP.MALOP) INNER JOIN Kh On Lop.MAKHOA=Kh.MAKHOA WHERE NU GROUP BY Kh.MAKHOA,TENKHOA;
Truy vaán loàng nhau (nested query, subquery) toanTuSoSanh [ANY | ALL | SOME] (cauLenhSQL) bieuThuc [NOT] IN (cauLenhSQL) [NOT] EXISTS (cauLenhSQL). Ví duï: Laäp danh saùch sinh vieân coù hoïc boång cao nhaát SELECT * FROM Sv WHERE HOCBONG>=ALL(SELECT HOCBONG FROM SV) Laäp danh saùch sinh vieân coù ñieåm thi moân CSDL cao nhaát SELECT SV.MASV,HOTEN,NU,NGAYSINH,DIEMTHI FROM sv Inner Join kq On Sv.MASV = Kq.MASV WHERE MAMH='CSDL' AND DIEMTHI >= ALL (SELECT DIEMTHI FROM KQ WHERE MAMH='CSDL') Truy vaán caäp nhaät döõ lieäu (action query, data modification query) Update table Set field1 = bieåuThöùc1, field2 = bieåuThöùc2 Where ñieàuKieän Delete From table Where ñieàuKieän Truy vaán hôïp Select Union Select Ví duï: Leänh sau ñaây gaáp ñoâi danh saùch sinh vieân SELECT MASV,HOTEN,NGAYSINH UNION SELECT MASV,HOTEN,NGAYSINH
Baøi taäp 2 Nhanvien(MANV,HOTEN,NGAYSINH,PHAI,DIACHI,MAPB) Phongban(MAPB,TENPB) Cong(MACT,MANV,SLNGAYCONG) Congtrinh(MACT,TENCT,DIADIEM,NGAYCAPGP,NGAYKHOICONG,NGAYHT) Baøi taäp 3: Monhoc(MSMH ,TENMH,SOTINCHI ,TINHCHAT) Sinhvien(MSSV,HOTEN,NGAYSINH,LOP) Diem(MSSV,MSMH,DIEMTHI) Baøi taäp 4: Docgia(MADG,HOTEN,NGAYSINH,DIACHI,NGHENGHIEP) Phieumuon(SOPM,NGAYMUON,MADG) Chitietmuon(SOPM,MADAUSACH,NGAYTRA) Dausach(MADAUSACH,BAN,TAP,MASH) Sach(MASH,TENSACH,TACGIA,NHAXB,NAMXB) Baøi taäp 5: Khach(MAKH,HOTEN,DIACHI,DIENTHOAI) Hoadon(SOHD,NGAYLAPHD,NGAYBAN,MAKH) DongHoaDon(SOHD,MAHANG,SLBAN) Hang(MAHANG,TENHANG,DONGIA,DVT,MANHOM) Nhom(MANHOM,TENNHOM)
Baøi taäp 6: Giaovien(MAGV,HOTEN,DTGV,MAKHOA) Khoa(MAKHOA,TENKHOA,DTKHOA) Lop(MALOP,TENLOP,SISO,MAKHOA) Monhoc(MAMH,TENMH) Phonghoc(SOPHONG,CHUCNANG) Lichbaogiang(MALICH,NGAYDAY,MAGV) Dongbaogiang(MALICH,TUTIET,DENTIET,BAIDAY,GHICHU,LYTHUYET, MAMH,MALOP,SOPHONG) Baøi taäp quaûn lyù thi Q1:HD(MAHDT,TENHDT, TENCT, DCHDT,DTHDT) Q2:PT(SOPT,DCPT,MAHDT) Q3:TS(SOBD, TENTS,NGSINH,PHAI,CCNGHE, MATR,SOPT) Q4:MT(MAMT,TENMT,BUOI,NGAY) Q5:GV(MAGV,TENGV,CHUYENMON,CHUCDANH,MAHDT,MATR) Q6:TR(MATR,TENTR,DCTR,LHDT) Q7:KQ(SOBD,MAMT,DIEMTHI)
MASV HOTEN NU NGAYSINH MALOP TINH HOCBONG 99001 TRAN DAN THU TRUE 15-03-1977 CÑTH2B TIEN GIANG 120000 99002 NGUYEN H DA THAO TRUE 25-04-1986 CÑTH2B TPHCM 120000 99003 PHAM ANH HUY FALSE 16-08-1977 CÑTH2B BAC LIEU 99004 NGUYEN NGOC THUAN FALSE 24-12-1980 CÑTH2B CA MAU 99005 LE THANH TRUNG FALSE 20-11-1978 CÑAV1 CA MAU 120000 99006 NGUYEN HONG VAN FALSE 19-09-1979 CÑAV1 SOC TRANG 99007 VU THI LOAN TRUE 15-03-1975 CÑAV1 CA MAU 99008 TRUONG KIM QUANGFALSE 15-05-1975 CÑTH2B HA NOI 99009 TON THAT QUYEN FALSE 26-06-1976 CÑTH2B VUNG TAU 60000 99010 HA VAN LONG FALSE 14-04-1973 CÑAV1 BAC LIEU 99011 BUI VAN ANH FALSE 22-12-1972 CÑAV1 AN GIANG 99012 LE HUU CHI FALSE 28-08-1977 CÑÑT2 CAN THO 60000 99013 VU THANH CONG FALSE 29-03-1979 CÑTH2B KIEN GIANG 60000 99014 TRAN QUANG CUONGFALSE 30-05-1981 CÑÑT2 DONG THAP 120000 99015 PHAM VAN HAI FALSE 30-06-1976 CÑÑT2 CA MAU 99016 HUYNH THANH HOANGFALSE 29-07-1982 CÑÑT2 TPHCM 80000 99017 TRAN MINH LAM FALSE 21-08-1977 CÑTH2B TRA VINH 99018 PHAN VAN SANG FALSE 19-05-1979 CÑDL1 DONG THAP 120000 99019 PHAM THI HUYEN FALSE 16-06-1982 CÑDL1 CAN THO 120000 99020 NGUYEN THI NGAN TRUE 11-11-1981 CÑTH2B CA MAU 120000 99021 PHAM TAN QUANGFALSE 01-01-1980 CÑDL1 CA MAU 99022 TRAN PHUOC QUYEN FALSE 12-12-1979 CÑTH2B BAC LIEU 60000 99023 LE THI THANH VAN TRUE 11-11-1980 CÑDL1 TPHCM 120000
RAØNG BUOÄC TOAØN VEÏN Raøng buoäc toaøn veïn laø gì? – laø caùc moái lieân heä giöõa caùc thuoäc tính, giöõa caùc boä – laø caùc ñieàu kieän maø caùc quan heä phaûi thoûa. – trong thöïc teá ta coù theå hieåu raøng buoäc toaøn veïn laø caùc qui taéc quaûn lyù. Caùc yeáu toá cuûa raøng buoäc toaøn veïn: – ñieàu kieän  t1,t2 r t1.MASV t2.MASV cuoái  – boái caûnh laø caùc quan heä maø ñieàu kieän raøng buoäc coù hieäu löïc – taàm aûnh höôûng (Teân RBTV) Theâm(T) Söûa(S) Xoùa(X) r1 + - - r2 rn
The type of integrity constraint SOLUONG DONGIA THANHTIEN – entity integrity constraint – derived integrity constraint 4 50 250 – referential integrity constraint – domain integrity constraint – data type integrity constraint derived integrity MASV MAMH DIEM MAMH TENMH 99001 DB 5.0 DB CÔ SÔÛ DÖÕ LIEÄU 99002 DS hai DS CAÁU TRUÙC DÖÕ LIEÄU 99003 NW 8.0 NW MAÏNG MAÙY TÍNH 99002 SQL 11.0 Entity Referential Domain Data type integrity integrity integrity integrity
Caùc loaïi raøng buoäc: – RBTV lieân boä R :t ,t rKhach 1 1 2 R1 Theâm Söûa Xoùa t1. MAKH t2. MAKH rKhach + + - Cuoái  – RBTV veà söï phuï thuoäc toàn taïi R Theâm Söûa Xoùa R : rDatHang[MAKH]  2 2 rDatHang + + - rKhach[MAKH] rKhach - + + – RBTV veà mieàn giaù trò R : t rHoaDon 3 R3 Theâm Söûa Xoùa t.TRIGIAHD > 0 rHoaDon + + - Cuoái  – RBTV lieân thuoäc tính R Theâm Söûa Xoùa R4: t rHoaDon 3 t.NGAYLAP <= t.NGAYXUAT rHoaDon + + - Cuoái 
Caùc loaïi raøng buoäc: – RBTV lieân thuoäc tính lieân quan heä R Theâm Söûa Xoùa R :  t rDatHang, t 5 5 1 2 rDatHang + + + rHoaDon rHoaDon + + + Neáu t1.SODH = t2.SODH thì t1.NGAYDH <= t2.NGAYXUAT Cuoái  – RBTV veà thuoäc tính toång hôïp laø giaù trò moät thuoäc tính ñöôïc tính toaùn töø caùc giaù trò khaùc nhö THANHTIEN=SOLUONG*DONGIA
Chương 6 Chuẩn hóa dữ liệu
PHUÏ THUOÄC HAØM phanCong (PHICONG, MAYBAY, NGAYKH, GIOKH) Cushing 83 9/8 10:15a Cushing 116 10/8 1:25p Clark 281 8/8 5:50a Clark 301 12/8 6:35p Clark 83 11/8 10:15a Chin 83 13/8 10:15a Chin 116 12/8 1:25p Copely 281 9/8 5:50a Copely 281 13/8 5:50a Copely 412 15/8 1:25p Moãi maùy bay coù moät giôø khôûi haønh duy nhaát. Neáu bieát phi coâng, bieát ngaøy giôø khôûi haønh thì bieát ñöôïc maùy bay do phi coâng aáy laùi. Neáu bieát maùy bay, bieát ngaøy khôûi haønh thì bieát phi coâng laùi chuyeán bay aáy.
o MAYBAY xaùc ñònh GIOKH o {PHICONG,NGAYKH,GIOKH} xaùc ñònh MABAY o {MAYBAY,NGAYKH} xaùc ñònh PHICONG hay o GIOKH phuï thuoäc haøm vaøo MAYBAY o MABAY phuï thuoäc haøm vaøo {PHICONG,NGAYKH,GIOKH} o PHICONG phuï thuoäc haøm vaøo {MAYBAY,NGAYKH} vaø ñöôïc kyù hieäu nhö sau: o {MAYBAY}→ GIOKH o {PHICONG,NGAYKH,GIOKH}→ MABAY o {MAYBAY,NGAYKH}→ PHICONG ÑÒNH NGHÓA: Q(A1,A2, ,An) laø löôïc ñoà quan heä. + X, Y laø hai taäp con cuûa Q ={A1,A2, ,An}. r laø quan heä treân Q. t1,t2 laø hai boä baát kyø cuûa r. X → Y (t1.X = t2.X t1.Y = t2.Y)
Heä quaû: Neáu X  Y thì X → Y. Chöùng minh: Giaû söû t1.X = t2.X do X  Y neân t1.Y = t2.Y theo ñònh nghóa suy ra X → Y Trong tröôøng hôïp naøy X → Y ñöôïc goïi laø phuï thuoäc haøm hieån nhieân. Ví duï phuï thuoäc haøm X → X laø phuï thuoäc haøm hieån nhieân. Vaäy vôùi r laø quan heä baát kyø, F laø taäp phuï thuoäc haøm thoûa treân r, ta luoân coù F  {caùc phuï thuoäc haøm hieån nhieân} phanCong (PHICONG, MAYBAY, NGAYKH, GIOKH) Cushing 83 9/8 10:15a Clark 83 11/8 10:15a Chin 83 13/8 10:15a Cushing 116 10/8 1:25p Chin 116 12/8 1:25p MAYBAY->GIOKH ? Clark 281 8/8 5:50a Copely 281 9/8 5:50a Copely 281 13/8 5:50a Clark 301 12/8 6:35p Copely 412 15/8 1:25p
SATIFIES(phanCong,GIOKH,MAYBAY) GIOKH→MAYBAY cho keát quaû false phanCong (PHICONG, MAYBAY, NGAYKH, GIOKH) Clark 281 8/8 5:50a Copely 281 9/8 5:50a Copely 281 13/8 5:50a Cushing 83 9/8 10:15a Clark 83 11/8 10:15a Chin 83 13/8 10:15a Cushing 116 10/8 1:25p Chin 116 12/8 1:25p Copely 412 15/8 1:25p Clark 301 12/8 6:35p
Phuï thuoäc haøm (functional dependency, FD) Q+ = {A,B,C,D} Q(A1,A2, ,An) laø löôïc ñoà quan heä. coù bao nhieâu taäp con? Q+={A1,A2, ,An} - coù 2n=24=16 X, Y laø hai taäp con cuûa Q+ coù bao nhieâu phuï thuoäc haøm coù theå coù? r laø quan heä treân Q. - coù 2nx2n=24x24=256 t1,t2 laø hai boä baát kyø cuûa r. X → Y (t1.X = t2.X t1.Y = t2.Y) A B C D  A B C D Kyù hieäu: X → Y ñoïc laø - X xaùc ñònh Y (X functional determines Y) →  AB AC AD -Y phuï thuoäc vaøo X (Y functional dependent on X ) →A BC BD →B -Neáu X  Y thì X → Y laø PTH hieån nhieân (trivial →AB ABC ABD dependence) CD A→ In the instance of the relation R(A,B,C,D,E) ACD shown below, which of the following A→A, functional dependencies (FD's) hold? A→B, BCD Briefly justify your answer. ABCD→ ABCD A B C D E I. AB → C ABCD→A II. B → D (T) 1 2 3 4 5 III. DE → A (T) ABCD→ABCD 1 4 3 4 5 1 2 4 4 1
Phuï thuoäc haøm ñöôïc suy dieãn logic töø F Noùi raèng phuï thuoäc haøm X → Y ñöôïc suy dieãn logic töø F neáu moät quan heä r baát kyø thoûa maõn taát caû caùc phuï thuoäc haøm cuûa F thì cuõng thoûa phuï thuoäc haøm X → Y. Kyù hieäu F|= X → Y. Bao ñoùng cuûa F Bao ñoùng cuûa F kyù hieäu F+ laø taäp taát caû caùc phuï thuoäc haøm ñöôïc suy dieãn logic töø F. Caùc tính chaát cuûa taäp F+ 1.Tính phaûn xaï: F  F+ 2.Tính ñôn ñieäu: Neáu F  G thì F+  G+ 3.Tính luõy ñaúng: (F+)+ = F+. Goïi G laø taäp taát caû caùc phuï thuoäc haøm coù theå coù cuûa r, phaàn phuï cuûa F kyù hieäu F- = G - F+ Chöùng minh 1.X → Y F r thoûa X → Y X → Y F+ 2.Neáu X → Y laø phuï thuoäc haøm thuoäc F+ ta phaûi chöùng minh X → Y thuoäc G+ Giaû söû r thoûa taát caû caùc phuï thuoäc haøm cuûa G (1) r thoûa taát caû phuï thuoäc haøm cuûa F vì F  G r thoûa phuï thuoäc haøm X → Y (2) vì X → Y F+ (1) vaø (2) X → Y G+ F+  G+
F  F+ (tính phaûn xaï) F+  (F+)+ (1) Neáu X → Y (F+)+ (2) X → Y F+ thaät vaäy: (3) Giaû söû r thoûa taát caû caùc phuï thuoäc haøm cuûa F (4) r thoûa taát caû caùc phuï thuoäc haøm cuûa F+ (theo ñònh nghóa) r thoûa taát caû caùc phuï thuoäc haøm cuûa (F+)+ (theo ñònh nghóa) r thoûa X → Y (vì (2)) X → Y F+ (1) vaø (3) (F+)+ = F+
Chöùng minh: Luaät theâm vaøo: giaû söû coù t1.XZ = t2.XZ (1) t1.X = t2.X t1.Y = t2.Y (do X → Y) (2) XZ → Y (do (1) (2)) Luaät hôïp: giaû söû coù t1.X = t2.X (1) t1.X = t2.X vaø t1.Z = t2.Z t1.XZ = t2.XZ (2) X → YZ (do (1) (2)) Luaät phaân raõ: gæa söû coù t1.X = t2.X (1) t1.YZ = t2.YZ (do X → YZ) t1.Y = t2.Y (2) X → Y (do (1) (2)) Luaät baéc caàu: giaû söû coù t1.X = t2.X (1) t1.Y = t2.Y t1.Z = t2.Z (2) X → Z (do (1) (2)) Luaät baéc caàu giaû: giaû söû coù: t1.XZ = t2.XZ (1) t1.X = t2.X vaø t1.Z = t2.Z (2) t1.Y = t2.Y (do X → Y) (3) t1.YZ = t2.YZ (Keát hôïp (2) vaø (3)) t1.W = t2.W (do YZ → W) (4) XZ → W
Noùi raèng X → Y laø phuï thuoäc haøm ñöôïc suy dieãn nhôø vaøo luaät daãn Armstrong neáu toàn taïi caùc taäp phuï thuoäc haøm F0  F1   Fn sao cho X → Y Fn vôùi F0,F1, ,Fn laàn löôït ñöôïc hình thaønh thoûa phöông phaùp sau: Böôùc 1: F0 = F Böôc 2:choïn moät soá phuï thuoäc haøm trong Fi aùp duïng heä luaät daãn Armstrong ñeå thu ñöôïc moät soá phuï thuoäc haøm môùi. Ñaët Fi+1= Fi  {caùc phuï thuoäc haøm môùi} Ví duï: Cho F = {AB → C,C → B,BC → A} thì coù F0  F1  F2 sao cho C → A F2 F0 = {AB → C,C → B, BC → A} aùp duïng luaät hôïp cho C → B vaø C → C F1 = {AB → C,C → B, BC → A, C → BC} aùp duïng luaät baéc caàu. F2 = {AB → C,C → B, BC → A, C →BC, C → A} Heä quaû: Heä luaät daãn Armstrong laø ñuùng nghóa laø neáu F laø taäp caùc phuï thuoäc haøm ñuùng treân quan heä r vaø X → Y laø moät phuï thuoäc haøm ñöôïc suy dieãn töø F nhôø heä luaät daãn Armstrong thì X → Y ñuùng treân quan heä r. Vaäy X → Y laø phuï thuoäc haøm ñöôïc suy dieãn logic töø F Ta seõ chöùng minh heä luaät daãn Armstrong laø ñuû nghóa laø moïi phuï thuoäc haøm X → Y ñöôïc suy dieãn logic töø F seõ ñöôïc suy dieãn töø F nhôø heä luaät daãn Armstrong
Bao ñoùng cuûa taäp thuoäc tính X ñoái vôùi F kyù hieäu laø X+ ñöôïc ñònh nghóa nhö sau: + X =  Ai vôùi X → Ai laø phuï thuoäc haøm ñöôïc suy dieãn töø F nhôø heä tieân ñeà Armstrong Tính chaát: bao ñoùng cuûa Q laø Q+ Caùc tính chaát cuûa bao ñoùng Neáu X,Y laø caùc taäp con cuûa taäp thuoäc tính Q+ thì ta coù caùc tính chaát sau ñaây: 1.Tính phaûn xaï: X  X+ 2.Tính ñôn ñieäu: Neáu X  Y thì X+  Y+ 3.Tính luõy ñaúng: X++ = X+ 4. (XY)+  X+Y+ 5. (X+Y)+ = (XY+)+ = (X+Y+)+ 6. X → Y Y+  X+ 7. X → X+ vaø X+ → X 8. X+ = Y+ X → Y vaø Y → X Chöùng minh: 1. X → X X+  X 2. A X+ X → A Y → A A Y+ 3. A X++ X+ → A vaø X → X+ (aùp duïng 8) X → A A X+ X++  X+. AÙp duïng 1 X++  X+ + 7. X → A1 vaø X→ A2 X → A1A2 X→Ai = X X+  X X+ → X (Phuï thuoäc haøm hieån nhieân)
Heä luaät daãn Armstrong (Armstrong inference rule) Heä luaät daãn laø moät phaùt bieåu cho bieát neáu moät quan heä r thoûa maõn moät vaøi phuï thuoäc haøm thì noù phaûi thoûa maõn phuï thuoäc haøm khaùc. Vôùi X,Y,Z,W laø taäp con cuûa Q+. r laø quan heä baát kyø cuûa Q. Ta coù 6 luaät daãn sau: Reflexive rule: X → X Augmentation rule : Cho X → Y XZ → Y Transitive rule : Cho X → Y, Y → Z X → Z ba luaät daãn treân laø heä tieân ñeà Armstrong (Armstrong’s Axioms) Union rule : Cho X → Y, X → Z X → YZ Decomposition rule : Cho X → YZ X → Y Pseudo transitive rule û: Cho X → Y, YZ → W XZ → W Bao ñoùng của taäp PTH F (closures of functional dependences sets) Bao ñoùng cuûa F kyù hieäu F+ laø taäp taát caû caùc phuï thuoäc haøm ñöôïc suy dieãn logic töø F. Bao ñoùng cuûa taäp thuoäc tính (closures of attributes sets) Bao ñoùng cuûa taäp thuoäc tính X ñoái vôùi F kyù hieäu laø X+ laø taäp caùc thuoäc tính phuï thuoäc vaøo X Given the function dependencies RH→J; J→M for the relation schema E(RHJM). The dependency R→M can be inferred by: a) The transitive rule c) The reflexive rule b) None of the above d) The augmentation rule
Tính lieân tieáp taäp caùc taäp thuoäc tính X0,X1,X2, theo phöông phaùp sau: Böôùc 1: X0 = X Böôùc 2: laàn löôït xeùt caùc phuï thuoäc haøm cuûa F Neáu Y→Z coù Y  Xi thì Xi+1 = Xi  Z Loaïi phuï thuoäc haøm Y → Z khoûi F Böôùc 3: Neáu ôû böôùc 2 khoâng tính ñöôïc Xi+1 thì Xi chính laø bao ñoùng cuûa X Ngöôïc laïi laëp laïi böôùc 2 Ví duï 1: Cho löôïc ñoà quan heä Q(ABCDEGH) vaø taäp phuï thuoäc haøm F F={f1: B→A; f2: DA→CE; f3: D→H; f4: GH→ C; f5: AC→D} Tìm bao ñoùng cuûa caùc taäp X = {AC} döïa treân F. Giaûi: Böôùc 1: X+ = AC + Böôùc 2: Do f1, f2, f3, f4 khoâng thoûa. f5 thoûa vì X  AC X+ = AC  D = ACD + Laäp laïi böôùc 2: f1 khoâng thoûa, f2 thoûa vì X  AD: X+ = ACD  CE = ACDE + f3 thoûa vì X  D X+ = ACDE  H = ACDEH f4 khoâng thoûa, f5 khoâng xeùt vì ñaõ thoûa Laäp laïi böôùc 2: f1,f2,f3 khoâng xeùt vì ñaõ thoûa, f4 khoâng thoûa,f5 khoâng xeùt vì ñaõ thoûa.Trong böôùc naøy X+ khoâng thay ñoåi => X+ ={ACDEH} laø bao ñoùng cuûa X