Cơ sở dữ liệu - Bài 2: Mô hình cơ sở dữ liệu quan hệ (ngôn ngữ mô hình hóa dữ liệu mức vật lý)

CƠ SỞ DỮ LIỆU Bài 2: MÔ HÌNH CƠ SỞ DỮ LIỆU QUAN HỆ (NGÔN NGỮ MÔ HÌNH HÓA DỮ LIỆU MỨC VẬT LÝ) 30/07/2012 1 HVĐ – THQL MỤC TIÊU • Kiến thức – Mô hình CSDL quan hệ – Ràng buộc toàn vẹn (RBTV) – Đại số quan hệ • Kỹ năng – Giải bài toán dùng đại số quan hệ – Chuyển các quy tắc quản lý thành RBTV – Cài đặt cơ sở dữ liệu trực tiếp trên DBMS – Cài đặt cơ sở dữ liệu dùng Power Designer • Yêu cầu: áp dụng thực tế 30/07/2012 HVĐ – THQL 2 NỘI DUNG • Tổng quan • Mô hình cơ sở dữ liệu quan hệ • Đại số quan hệ • Thảo luận tình huống 30/07/2012 HVĐ – THQL 3 TỔNG QUAN • Tổng quan – Các ví dụ – Khái niệm • Mô hình cơ sở dữ liệu quan hệ • Đại số quan hệ • Thảo luận tình huống 30/07/2012 HVĐ – THQL 4 VÍ DỤ • Xét hệ thống quản lý kho • Bài toán: tổ chức các bảng lưu dữ liệu đủ cho yêu cầu “biết số lượng và trị giá hàng tồn kho của từng mặt hàng tại bất kỳ thời điểm nào” • Rõ ràng ở mỗi lần nhập xuất ngày giao dịch, số lượng và đơn giá nhập là các dữ liệu vào ảnh hưởng đến dữ liệu ra theo yêu cầu. • Chúng ta có 2 thái cực lưu trữ – Tính sẵn dữ liệu ra, lưu để sẵn sàng đáp ứng yêu cầu – Lưu dữ liệu gốc, khi được yêu cầu sẽ tính rồi trả lời 30/07/2012 HVĐ – THQL 5 GIẢI PHÁP • Số tồn kho được tính qua số đầu kỳ và số phát sinh • Số đầu kỳ gắn với hàng hóa, lưu trong bảng h-hóa • Số phát sinh gắn với giao dịch, lưu trong các bảng nhập và xuất 30/07/2012 HVĐ – THQL 6 VÍ DỤ • Môn: kỹ thuật bàn phím (15 tiết), Internet, (20 tiết) • Quản lý Minh (hsl 3), giảng viên Lan (hsl 3), giảng viên Hùng (hsl 2), giảng viên mời Thảo. • Lan dạy 1 lớp kỹ thuật bàn phím và 1 lớp Internet, Hùng dạy 1 lớp kỹ thuật bàn phím, Thảo dạy 2 lớp Internet. • Phương thức tính lương: – Lương quản lý = lương cứng + 1800 – Lương giảng viên = lương cứng + tiền giảng – Lương giảng viên mời = tiền giảng * 1.5 Trong đó • lương cứng = hsl * 450 • tiền giảng = tổng số tiết x 50 30/07/2012 HVĐ – THQL 7 PHÂN TÍCH • Bám vào mục tiêu: tính lương • Theo công thức, cần biết – Hệ số lương – Số tiết mỗi môn – Số môn giảng • Hệ số lương lưu, cùng với thông tin khác của giảng viên, trong bảng giảng viên • Số tiết mỗi môn lưu, cùng với thông tin khác của môn, trong bảng môn học • Số môn giảng lưu trong bảng phân công giảng 30/07/2012 HVĐ – THQL 8 GIẢI PHÁP 30/07/2012 HVĐ – THQL 9 id Họ tên lương QL Minh 3150 G1 Lan 3100 G2 Hùng 1650 M1 Thào 3000 KHÁI NIỆM • Lược đồ quan hệ: cấu trúc bảng – Cột (thuộc tính): tên, miền giá trị – Cột định danh: khoá, siêu khoá • Quan hệ (tập các bộ): nội dung bảng 30/07/2012 HVĐ – THQL 10 MÔ HÌNH CSDL QUAN HỆ • Tổng quan • Mô hình cơ sở dữ liệu quan hệ – Lược đồ quan hệ – Lược đồ CSDL – Ràng buộc toàn vẹn – Cài đặt cơ sở dữ liệu • Đại số quan hệ • Thảo luận tình huống 30/07/2012 HVĐ – THQL 11 LƯỢC ĐỒ QUAN HỆ • Lược đồ quan hệ – Tập thuộc tính – Miền giá trị của mỗi thuộc tính • Quy tắc lưu trữ cơ bản – Mỗi dòng thể hiện một đơn vị thông tin – Giữa 2 dòng phải thể hiện sự phân biệt – Quan hệ giữa các dữ liệu trong cùng 1 dòng – Quan hệ giữa các dữ liệu trên nhiều dòng 30/07/2012 12 HVĐ – THQL VÍ DỤ • Quan hệ giảng viên (gv): – Mỗi dòng lưu thông tin của một giảng viên – Dữ liệu cụ thể được xác định bởi lược đồ cho trước • Ký hiệu gv (gvID char(6), gvTEN char(10), nSINH date) cho biết – Quan hệ giảng viên gv – Được định nghĩa trên lược đồ GV = {gvID char(6), gvTEN char(10), nSINH date} • Trong lược đồ GV ở trên: – Hai giảng viên khác nhau được phân biệt qua dữ liệu được lưu trong thuộc tính gvID – Miền giá trị của các thuộc tính phụ thuộc nhu cầu lưu trữ 30/07/2012 13 HVĐ – THQL VIEW • Quan hệ gồm hai loại – Quan hệ gốc: được lưu – Quan hệ dẫn xuất: kết quả xử lý • View là một quan hệ dẫn xuất được thiết kế gần gũi với người dùng để có: – Một cái nhìn đầy đủ trong ngữ cảnh sử dụng – Những thông tin riêng và các quy tắc riêng trong ngữ cảnh này – Cài đặt cơ chế hạn chế truy xuất 30/07/2012 HVĐ – THQL 14 THẢO LUẬN • Một cửa hàng bán đĩa CD muốn quản lý thông tin của khách hàng. Những thông tin này được thể hiện qua quan hệ khachHang • Hãy xác định: – Lược đồ (tập thuộc tính và miền giá trị) – Khóa – Quan hệ này là quan hệ lưu trữ hay là quan hệ dẫn xuất (view) – Nếu là view, cho biết mục tiêu của view 30/07/2012 HVĐ – THQL 15 LƯỢC ĐỒ CƠ SỞ DỮ LIỆU • Tập các lược đồ quan hệ • Tập các RBTV – Khoá (siêu khoá) – Miền giá trị – NULL, NOT NULL – Khoá ngoại – Ràng buộc trên 1 bộ giữa các thuộc tính – Ràng buộc trên nhiều bộ – Ràng buộc tồn tại – Ràng buộc trên nhiều bảng 30/07/2012 16 HVĐ – THQL VÍ DỤ • {gvID char(6), gvTEN char(10), nSINH date} • {monID char(6), monTEN char(10), soTC integer} • {lopID char(6), nganhID char(6), phongID char(6)} • {svID char(6), svTEN char(10), lopID char(6)} • {nganhID char(6), monID char(6), hocKY integer} • {lopID char(6), monID char(6), buoiHOC char(2)} • {gvID char(6), monID char(6), lopID char(6)} • {gvID char(6), monID char(6), lopID char(6), phongID char(6), buoiHOC char(2)} • {svID char(6), monID char(6), diemTHI integer} 30/07/2012 17 HVĐ – THQL THẢO LUẬN RBTV, VIEW • Bảng – gVien(gvID, gvTEN, nSINH ) – monHoc(monID , monTEN, soTC) – lopHoc(lopID, nganhID, phongID) – sVien(svID, svTEN, lopID) – chuongTrinh(nganhID , monID , hocKY ) – lichHoc(lopID, monID, buoiHOC) – phanCong(gvID, monID, lopID ) – ketQua(svID, monID, diemTHI) • View – thoiKhoaBieu(gvID, monID, lopID, phongID, buoiHOC) – khoiLuong(gvID, tongSoTiet) 30/07/2012 18 HVĐ – THQL VÍ DỤ RB MIỀN GÍA TRỊ • DOM(soTC) = {3, 4} • DOM(hocKY) = {1, 2, …, 8} • DOM(buoiHOC) = {“S2”, “C2”, …, “TN”} • DOM(diemTHI) = {0, 1, …, 10} 30/07/2012 19 HVĐ – THQL VÍ DỤ RB KHOÁ • gVien(gvID, gvTEN, nSINH ) • monHoc(monID , monTEN, soTC) • lopHoc(lopID , phongID) • sVien(svID, svTEN, lopID) • chuongTrinh(nganhID , monID , hocKY ) • lichHoc(lopID, monID, buoiHOC) • phanCong(gvID, monID, lopID) • ketQua(svID, monID, diemTHI) 30/07/2012 20 HVĐ – THQL VÍ DỤ RB TỒN TẠI • Khoá ngoại là ràng buộc tồn tại đặc biệt • Liên quan 3 quan hệ chuongTrinh, lopHoc và lichHoc: Xét một bộ (l, m, b) trong lichHoc phải tìm thấy bộ (l, n, p) trong lopHoc và bộ (n, m, k) trong chuongTrinh • Liên quan 3 quan hệ chuongTrinh, lopHoc và lichHoc: Xét một bộ (s, m, d) trong ketQua phải tìm thấy bộ (l, n, p) trong lopHoc và bộ (n, m, k) trong chuongTrinh 30/07/2012 21 HVĐ – THQL VÍ DỤ RB KHÁC • Ràng buộc “Tổng số tiết giảng của mỗi giảng viên không ít hơn 240 và không nhiều hơn 600” liên quan đến các bảng phanCong và monHoc • Ràng buộc “Mỗi lớp có không quá 50 sinh viên” là một ràng buộc liên bộ trên bảng sVien • Ràng buộc “Tại một phòng nào đó và ở một buổi bất kỳ, có duy nhất một lớp đang học và một giảng viên đang dạy” là một ràng buộc liên quan đến các bảng phanCong, lopHoc và lichHoc 30/07/2012 22 HVĐ – THQL BIỂU ĐỒ TRỰC QUAN 30/07/2012 HVĐ – THQL 23 • Khóa chính • Khóa ngoại CÀI ĐẶT CSDL TRỰC TIẾP • Tạo bảng – Thuộc tính, miền giá trị và RBTV trên thuộc tính – Khóa chính và các khóa khác – RBTV trên cùng dòng (giữa các thuộc tính) • RBTV khóa ngoại • Thực nghiệm với một hệ quản trị cụ thể và một lược đồ CSDL cụ thể 30/07/2012 HVĐ – THQL 24 DÙNG POWER DESIGNER • Lập mô hình – Cấu trúc – Ràng buộc toàn vẹn • Phát sinh cơ sở dữ liệu 30/07/2012 HVĐ – THQL 25 CHUYỂN QUY TẮC THÀNH RB • Phieáu nhaäp phaûi coù ngaøy nhaäp • Hoaù ñôn phaûi coù ngaøy laäp • Haøng hoaù phaûi coù teân haøng, ñôn vò tính, vaø coù giaù duy nhaát • Phieáu nhaäp cuøng maõ haøng xaùc ñònh löôïng nhaäp • Hoaù ñôn cuøng maõ haøng xaùc ñònh löôïng baùn • Ngaøy kieåm keâ cuøng maõ haøng xaùc ñònh löôïng toàn • Löôïng nhaäp, löôïng baùn, löôïng toàn cuõng nhö giaù laø caùc soá döông • Ngaøy laäp thöôøng laø ngaøy hieän taïi • Giaù tieàn moãi maët haøng trong khoaûng 10 (ngaøn) ñeán 100 (ngaøn) • Moãi hoaù ñôn baùn ít nhaát 1 vaø khoâng quaù 4 maët haøng • Khoâng baùn nhöõng maët haøng coù löôïng toàn hieän taïi nhoû hôn 5 • Maõ hoaù ñôn coù daïng HD*** (ví duï HD002) 30/07/2012 HVĐ – THQL 26 ĐẠI SỐ QUAN HỆ • Tổng quan • Mô hình cơ sở dữ liệu quan hệ • Đại số quan hệ – Logic giải bài toán – Biểu thức đại số quan hệ – Chiến lược giải bài toán • Thảo luận tình huống 30/07/2012 HVĐ – THQL 27 LOGIC GIẢI BÀI TOÁN • Xét bài toán giải phương trình ax = b – Với a, b, x là số: x = b/a (a khác không) – Với a, b, x là ma trận ? – Với a, b, x là tập con của tập E ? • Trong đại số quan hệ – Biến là quan hệ nhận giá trị là một tập con của tập E nào đó – Biểu thức gồm biến, trị và các phép toán trên các quan hệ 30/07/2012 HVĐ – THQL 28 CÁC PHÉP TOÁN QUAN HỆ • Phép tích: tích s với u • Phép chiếu: chiếu u lên cột C • Phép chọn: chọn từ u các dòng có A = 2 • Phép kết (nối): kết r1 với s thông qua B • Phép chia: chọn những A của r1 mà những giá trị B tương ứng đều có trong s 30/07/2012 HVĐ – THQL 29 CÁC PHÉP TOÁN TẬP CON 30/07/2012 HVĐ – THQL 30 • Phép hợp • Phép giao • Phép hiệu • Phép phủ định THẢO LUẬN • Xác định phép toán 30/07/2012 HVĐ – THQL 31 THẢO LUẬN • Xác định phép toán 30/07/2012 HVĐ – THQL 32 id Họ tên lương QL Minh 3150 G1 Lan 3100 G2 Hùng 1650 M1 Thào 3000 CHIẾN LƯỢC GIẢI BÀI TOÁN • Tính toán trực tiếp trên các bảng (có thể qua các bảng trung gian) • Mỗi bảng trung gian – Xác định lược đồ – Quyết định phép toán, đặc biệt lưu ý đến phép so sánh • Xây dựng biểu thức cuối cùng • Lưu ý đến phép toán so sánh 30/07/2012 HVĐ – THQL 33 THẢO LUẬN • Bài toán tính tiền điện • Các câu hỏi ở công ty Mỹ Gia 30/07/2012 HVĐ – THQL 34 KIỂM TRA RBTV • Chọn học phần (RBTV và các quan hệ) – ketQuaHP(MaSV, MaHP, Diem) – danhSachDKHP(MaSV, MaHP) – dieuKienTQ(MaHP, HPTQ) – Một sinh viên đăng ký một học phần trong danhSachDKHP phải chưa đạt học phần này và đã đạt tất cả các học phần tiên quyết của học phần này • Thi đại học (RBTV và các quan hệ) – ketQuaThi(SBD, Mon, Diem) – danhSachDau(SBD, TongDiem) – Một thí sinh trong danhSachDau phải thi đủ 3 môn, có tổng điểm từ 19 và không có điểm dưới 2 30/07/2012 HVĐ – THQL 35 TRỞ LẠI MỤC TIÊU • Kiến thức – Mô hình CSDL quan hệ – Ràng buộc toàn vẹn (RBTV) – Đại số quan hệ • Kỹ năng – Giải bài toán dùng đại số quan hệ – Chuyển các quy tắc quản lý thành RBTV – Cài đặt cơ sở dữ liệu trực tiếp trên DBMS – Cài đặt cơ sở dữ liệu dùng Power Designer • Yêu cầu: áp dụng thực tế 30/07/2012 HVĐ – THQL 36 THẢO LUẬN TÌNH HUỐNG • Tổng quan • Mô hình cơ sở dữ liệu quan hệ • Đại số quan hệ • Thảo luận tình huống – Xây dựng lược đồ – Cài đặt ràng buộc toàn vẹn – Tính tồn kho – Quản lý bán hàng 30/07/2012 HVĐ – THQL 37 XD LƯỢC ĐỒ CSDL • Khoa hiện có – 5 sinh viên a, b, c, d, e: tên Kha, Thi, Hùng, Định, Lan – 3 lớp: x đang ở học kỳ 2 hệ CĐ, y học kỳ 1 hệ ĐH và z học kỳ 2 hệ ĐH – 5 môn m, n, p, q, s: số tiết 45, 60, 60, 45, 45 tương ứng – Khoa xếp a, b vào x; c, d vào y và e vào z • Theo kế hoạch – Học kỳ 1: CĐ học m, n; ĐH học m, p – Học kỳ 2: CĐ học q, s; ĐH n, q • Điểm kỳ 1 các môn m, n của a là 6, 7 của b là 4, 8 • Điểm kỳ 1 các môn m, p của e là 4, 7 • Học kỳ 2 e học lại môn m với lớp y 30/07/2012 HVĐ – THQL 38 CÀI ĐẶT RBTV • Sinh viên phải được xếp lớp • Lớp được xếp là duy nhất • Số tiết chỉ có thể là 45 hoặc 60 30/07/2012 HVĐ – THQL 39 • Hệ CĐ được đào tạo trong 6 học kỳ, còn ĐH là 8 • Sinh viên chỉ được phép học lại khi điểm thi dưới 5 • Học lại theo lớp nào đó, phải cùng hệ và theo kế hoạch của lớp này TÌNH TỒN KHO 30/07/2012 HVĐ – THQL 40 TRAO ĐỔI • CSDL bán hàng: – hangHoa(…) – khachHang(…) – hoaDon(…) – chiTietHD(…) 30/07/2012 HVĐ – THQL 41 • CSDL thư viện: – sach(…) – docGia(…) – phieuMuonTra(…) – chiTietMT(…) • Câu hỏi thảo luận – Lược đồ – Ràng buộc toàn vẹn VẤN ĐỀ TÍNH TOÁN • Trên dòng – Dùng tên cột làm tên biến trong biểu thức tính toán – Kết quả tính toán diễn ra trong cùng dòng – Áp dụng cho tất cả các dòng • Trên cột – Các dòng được nhóm lại (phân hoạch) – Trên mỗi cột, dữ liệu là một tập – Các hàm tính toán trên tất cả dữ liệu của một tập, như SUM, MAX, AVG, …, được sử dụng 30/07/2012 HVĐ – THQL 42 KẾT LUẬN • Mô hình cơ sở dữ liệu quan hệ: – Dùng các lược đồ quan hệ để biểu diễn dữ liệu – Các quy tắc quản lý được cài đặt dưới dạng các ràng buộc toàn vẹn • Đại số quan hệ: – Biểu thức gồm các quan hệ và các phép toán – Quy trình ba bước: chiếu, kết và chọn • Lưu ý – Đồ thị hoá lược đồ cơ sở dữ liệu – Phép so sánh – Mệnh đề với lượng từ tồn tại – Nên có các biểu thức trung gian 30/07/2012 HVĐ – THQL 43