Bài giảng Cơ sở dữ liệu - Chương 4: Đại số quan hệ - Trịnh Hoàng Nam

11/22/2018 1 ĐẠI SỐ QUAN HỆ CƠ SỞ DỮ LIỆU Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u NỘI DUNG • Các phép toán một ngôi1 • Các phép toán hai ngôi2 • Các phép toán khác3 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN MỘT NGÔI Phép chọn Phép chiếu Phép đổi tên Chọn từ quan hệ R các bộ dữ liệu thỏa mãn điều kiện C. Ký hiệu: σC(R) Chia quan hệ R thành hai phần: phần được chọn chứa các thuộc tính chiếu, phần không được chọn chứa các thuộc tính còn lại. Ký hiệu: L(R) Đổi tên quan hệ R và các thuộc tính của nó. Ký hiệu: S(R) 11/22/2018 2 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN MỘT NGÔI • Điều kiện chọn là một biểu thức lôgic kết hợp các phép so sánh bởi các phép nối lôgic AND, OR, và NOT • Phép chiếu được áp dụng lên các thuộc tính và các biểu thức tính toán dựa trên các thuộc tính đó • Phép chọn và phép chiếu làm giảm kích thước của quan hệ kết quả so với quan hệ gốc Một số chú ý Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN MỘT NGÔI • Liệt kê nhân viên trong đơn vị số 1 • Liệt kê nhân viên làm việc trong đơn vị số 1 và có mức lương trên $85,000/năm Các ví dụ DNum = 1(EMPLOYEE) DNum = 1 AND ESalary>85000 (EMPLOYEE) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN MỘT NGÔI • Liệt kê tên và mức lương cơ bản của tất cả nhân viên trong công ty • Liệt kê tên và lương thực lãnh của nhân viên, biết lương thực lãnh bằng hai lần mức lương cơ bản Các ví dụ EName, ESalary (EMPLOYEE) EName, 2*ESalary (EMPLOYEE) 11/22/2018 3 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN MỘT NGÔI • Đổi tên thuộc tính PName của PROJECT thành ‘proName’ • Đổi tên quan hệ PROJECT thành quan hệ PROS (giữ nguyên tên các thuộc tính Các ví dụ PNumber, proName, dNum, PLocation (PROJECT) PROS (PROJECT) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 1  Liệt kê tất cả nhân viên nữ trong công ty  Liệt kê tất cả nhân viên nữ có mức lương trên 30.000  Liệt kê tất cả nhân viên nữ ở độ tuổi từ 25 đến 40  Liệt kê tất cả nhân viên nữ vừa được tuyển dụng trong năm nay DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 1  Liệt kê tên, tuổi của các nhân viên có từ 5 năm kinh nghiệm trở lên  Liệt kê tên, số năm công tác của các nhân viên có mức lương trên 45.000 DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours 11/22/2018 4 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN HAI NGÔI Phép toán tập hợp Phép nhân chéo Phép kết theta Quan hệ là một tập hợp, giữa hai quan hệ có các phép toán tập hợp như phép hợp, phép giao, và phép trừ Tổ hợp các bộ từ hai quan hệ một cách ngẫu nhiên Tổ hợp các bộ từ hai quan hệ sao cho thỏa mãn điều kiện kết Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP TOÁN TẬP HỢP Điều kiện khả hợp Phép hợp Phép giao Phép trừ Hai quan hệ được gọi là khả hợp, nếu có cùng số thuộc tính, và cùng miền giá trị cho từng cặp thuộc tính tương ứng R  S = {t | t  R  t  S} R  S = {t | t  R  t  S} R  S = {t | t  R  t  S} Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP TOÁN TẬP HỢP • Các bộ dữ liệu trùng nhau bị loại bỏ ra khỏi kết quả của các phép toán tập hợp • Lược đồ của quan hệ kết quả là lược đồ của quan hệ đứng trước trong phép toán Một số chú ý 11/22/2018 5 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP TOÁN TẬP HỢP • Liệt kê nhân viên làm việc trong đơn vị số 1 và có mức lương trên $85,000/năm Các ví dụ DNum = 1 AND ESalary>85000 (EMPLOYEE) R1=DNum = 1 (EMPLOYEE) R2=ESalary>85000 (EMPLOYEE) R = R1  R2 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 2  Liệt kê mã số dự án do đơn vị mã số 001 quản lý hoặc có sự tham gia của nhân viên mã số 00001 DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 2  Liệt kê mã số dự án do đơn vị mã số 001 quản lý và không có sự tham gia của nhân viên mã số 00001 DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours 11/22/2018 6 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP NHÂN CHÉO • Mỗi bộ trong quan hệ kết quả Q=RS là tổ hợp của một bộ trong R với một bộ trong S Định nghĩa • Hai quan hệ tham gia không cần khả hợp • Nếu R có nR thuộc tính, mR bộ, S có nS thuộc tính, mS bộ thì RS có nR+nS thuộc tính và mR*mS bộ Một số đặc trưng Phép nhân chéo không thực sự có nhiều ý nghĩa Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP NHÂN CHÉO Ví dụ Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT THETA • Mỗi bộ trong quan hệ kết quả Q=R⋈CS là tổ hợp của một bộ trong R với một bộ trong S sao cho thỏa mãn điều kiện kết C Định nghĩa • Hai quan hệ tham gia không cần khả hợp • Phép kết theta là phép chọn điều kiện C của một phép nhân chéo Một số đặc trưng R⋈CS =C(RS) 11/22/2018 7 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT THETA Ví dụ R⋈A<CS ? Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT TỰ NHIÊN • Kết tự nhiên là một dạng kết theta trong đó điều kiện kết được kiểm tra đối với mọi cặp thuộc tính cùng tên có giá trị giống nhau trong cả hai quan hệ Định nghĩa • (Các) cặp thuộc tính được dùng để kết tự nhiên chỉ xuất hiện một lần duy nhất trong quan hệ kết quả • Điều kiện kết liên quan đến tất cả các cặp thuộc tính trùng tên của hai quan hệ • Không có thuộc tính trùng tên không thể sử dụng phép kết tự nhiên Một số đặc trưng Q = R*S Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT TỰ NHIÊN Ví dụ R * S ? 11/22/2018 8 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 3  Liệt kê tên, tuổi, mức lương của các nhân viên trong phòng Kinh doanh  Liệt kê các dự án do phòng Kinh doanh quản lý  Liệt kê tên, tuổi của các nhân viên đã từng tham gia các dự án do phòng Kinh doanh quản lý DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 3  Liệt kê tên, tuổi, mức lương, đơn vị công tác của các trưởng đơn vị  Liệt kê tên, tuổi, mức lương của các nhân viên trong phòng Kinh doanh nhưng chưa từng tham gia bất kỳ dự án do phòng này quản lý DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u TẬP ĐẦY ĐỦ CÁC PHÉP TOÁN ĐẠI SỐ QUAN HỆ • Tập đầy đủ các phép toán là tập hợp gồm ít phép toán nhất mà có thể dùng chúng để biểu diễn tất cả những phép toán còn lại • Tập đầy đủ các phép toán đại số quan hệ {, , , , } Định nghĩa 11/22/2018 9 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u CÁC PHÉP TOÁN KHÁC Phép gom nhóm Hàm kết hợp Phép kết ngoài Gom các bộ dữ liệu trong quan hệ thành từng nhóm theo giá trị của một (số) thuộc tính. Kết nối một bộ quan hệ này với một bộ của quan hệ kia (nếu có), cho dù thỏa hay không thỏa điều kiện kết. Các hàm kết hợp MAX, MIN, AVG, SUM, COUNT được áp dụng cho các nhóm sau khi đã gom. Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP GOM NHÓM VÀ CÁC HÀM KẾT HỢP • Cho biết số lượng, mức lương trung bình của nhân viên trong từng đơn vị • Cho biết số giờ làm việc của mỗi dự án trong công ty Các ví dụ DNum(COUNT ESSN),(AVERAGE ESalary) (EMPLOYEE) PNum(SUM WorkHours) (WORKSON) Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT NGOÀI • Kết mỗi bộ r của quan hệ R với các bộ s của quan hệ S sao cho thỏa mãn điều kiện C • Điều gì xảy ra nếu r không thể kết với bất kỳ bộ nào của S (do không thỏa điều kiện C)? Phép kết theta Ví dụ R⋈A>CS ? 11/22/2018 10 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT NGOÀI • Kết mỗi bộ r của quan hệ R với các bộ s của quan hệ S sao cho thỏa mãn điều kiện C • Nếu không tìm thấy bộ s như thế, r sẽ kết với một bộ chứa toàn giá trị NULL ứng với các thuộc tính của S Phép kết ngoài Ví dụ R ⋉ A>CS ? Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u PHÉP KẾT NGOÀI • Kết mỗi bộ r của quan hệ R với các bộ s của quan hệ S sao cho thỏa mãn điều kiện C • Nếu không tìm thấy bộ s như thế, r sẽ kết với một bộ chứa toàn giá trị NULL ứng với các thuộc tính của S Phép kết ngoài trái R ⋉ CS • Kết mỗi bộ r của quan hệ R với các bộ s của quan hệ S sao cho thỏa mãn điều kiện C • Nếu không tìm thấy bộ s như thế, r sẽ kết với một bộ chứa toàn giá trị NULL ứng với các thuộc tính của S Phép kết ngoài phải R ⋊ CS • Kết hợp cả phép kết ngoài trái lẫn kết ngoài phải Phép kết ngoài hai bên R ><C S Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 4  Cho biết tên đơn vị, tên dự án do đơn vị đó quản lý (nếu có)  Cho biết tên đơn vị, tên trưởng đơn vị, tên dự án do đơn vị đó quản lý (nếu có) DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours 11/22/2018 11 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP 4  Cho biết tên, mã số và mức lương trung bình của mỗi đơn vị  Cho biết tên, mã số và số giờ tham gia dự án của từng nhân viên DEPARTMENT DNumber DName mgrSSN mgrStartdate DEPENDENT DName ESSN DSex DBirthdate DRelationship DEPLOCATION DNum DLocation EMPLOYEE ESSN EName ESalary ESex EBirthdate EStartdate DNum supervisorSSN PROJECT PNumber PName PLocation DNum WORKSON ESSN PNum workHours Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u NỘI DUNG • Các phép toán một ngôi1 • Các phép toán hai ngôi2 • Các phép toán khác3 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Product(maker, model, type) • PC(model, speed, ram, hd, price) • Laptop(model, speed, ram, hd, screen, price) • Printer(model, color, type, price) Cơ sở dữ liệu • Liệt kê các máy tính xách tay có RAM từ 2.0GB trở lên. • Liệt kê các nhà cung cấp máy tính để bàn có ổ cứng dưới 160GB • Liệt kê những sản phẩm (mã sản xuất, giá) do công ty SS sản xuất • Liệt kê những công ty sản xuất máy tính để bàn nhưng không sản xuất máy tính xách tay. Yêu cầu 11/22/2018 12 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Product(maker, model, type) • PC(model, speed, ram, hd, price) • Laptop(model, speed, ram, hd, screen, price) • Printer(model, color, type, price) Cơ sở dữ liệu • Liệt kê những cặp máy tính để bàn có cùng tốc độ xử lý và kích thước ổ cứng. • Liệt kê những công ty sản xuất máy tính, nhưng không sản xuất máy tính có tốc độ dưới 1.6GHz • Liệt kê những máy để bàn có tốc độ xử lý nhanh nhất • Liệt kê những công ty sản xuất ra ít nhất ba sản phẩm máy tính để bàn có tốc độ xử lý khác nhau Yêu cầu Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Hotel(hotelNo, hotelName, city) • Room(roomNo, hotelNo, type, price) • Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo) • Guest(guestNo, guestName, guestAddress) Cơ sở dữ liệu • Liệt kê danh sách tất cả khách sạn trong hệ thống. • Liệt kê danh sách phòng đơn có giá từ 300.000 đến 450.000 • Liệt kê số lượng phòng đơn của từng khách sạn • Liệt kê giá và loại phòng của những phòng còn trống tại khách sạn Yasaka trong khoảng thời gian từ 01/09/2011 đến 08/09/2011. Yêu cầu Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Hotel(hotelNo, hotelName, city) • Room(roomNo, hotelNo, type, price) • Booking(hotelNo, guestNo, dateFrom, dateTo, roomNo) • Guest(guestNo, guestName, guestAddress) Cơ sở dữ liệu • Liệt kê các khách sạn có phòng trống vào ngày 01/09/2011 • Liệt kê danh sách khách hàng đăng ký phòng tại khách sạn Yasaka trong khoảng thời gian từ 01/09/2011 đến 08/09/2011. • Thống kê số lượng khách trong ngày 01/09/2011 tại từng khách sạn. Yêu cầu 11/22/2018 13 Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Tour(ID, Name, Days, Nights, Vehicle, Price) • Location (ID, Name) • Place(ID, Name, locationID, Description) • Detail(tourID, placeID, Order, Days, Nights) Cơ sở dữ liệu • Liệt kê những tỉnh/thành phố không có điểm du lịch. • Liệt kê những tour du lịch bắt đầu từ TP. Hồ Chí Minh. • Tìm điểm khởi hành của những tour có điểm dừng tại Phố cổ Hội An. Yêu cầu Ths. Trịnh Hoàng Nam, namth@buh.edu.vn C ơ s ở d ữ l i ệ u – T ổ n g q u a n h ệ t h ố n g c ơ s ở d ữ l i ệ u BÀI TẬP TỔNG HỢP • Tour(ID, Name, Days, Nights, Vehicle, Price) • Location (ID, Name) • Place(ID, Name, locationID, Description) • Detail(tourID, placeID, Order, Days, Nights) Cơ sở dữ liệu • Tìm các tour dừng tại Phố cổ Hội An trong hai ngày và một đêm. • Tìm các tour khởi hành từ Hà Nội, có giá dưới $500, có nghỉ đêm tại Đà Nẵng và Nha Trang. • Tìm các tên điểm du lịch mà tour Xuyên Việt 365 ghé qua. Yêu cầu