›
›

Giáo trình môn học Cơ sở dữ liệu nâng cao (Phần 2) - Trình độ: Cao đẳng nghề

pdf 49 trang Gia Huy 17/05/2022 2890
Download
Bạn đang xem 20 trang mẫu của tài liệu "Giáo trình môn học Cơ sở dữ liệu nâng cao (Phần 2) - Trình độ: Cao đẳng nghề", để 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:

  • pdfgiao_trinh_mon_hoc_co_so_du_lieu_nang_cao_phan_2_trinh_do_ca.pdf

Nội dung text: Giáo trình môn học Cơ sở dữ liệu nâng cao (Phần 2) - Trình độ: Cao đẳng nghề

  1. 5.2 Thiết kế QUERY 5.2.1 QUERY đơn (SELECT QUERY ) 5.2.2 Lập tiêu chuẩn lựa chọn trong query 5.2.3 Sử dụng Field tính tốn trong query 5.2.4 Dùng dịng total để nhĩm các dịng 5.3 QUERY tổng hợp 5.3.1 Query cĩ thơng số (PARAMETER QUERY) 5.3.2 CROSSTAB QUERY 5.3.3 SUB QUERY 5.3.4 MAKE TABLE QUERY 5.3.5 UPDATE QUERY 5.3.6 DELETE QUERY 5.3.7 UNION QUERY 5.1 Giới thiệu QUERY 5.1.1 Chức năng - Query Là một cơng cụ dùng để xử lý dữ liệu trong Access, nĩ cĩ thể đáp ứng nhu cầu tra cứu dữ liệu từ nhiều bảng của CƠ SỞ DỮ LIỆU. Từ cách thức đặt câu hỏi và các mối quan hệ được thiết lập. Access sẽ rút ra những thơng tin cần thiết theo yêu cầu. - Bảng kết quả của một Query gọi là Dynaset. Một Dynaset khơng phải là một bảng dữ liệu thực sự nhưng trong một số trường hợp nĩ cĩ thể tác động thay đổi dữ liệu ngược lại lên Table 5.1.2 Dạng thức Cĩ rất nhiều kiểu Query trong Access. Tùy từng trường hợp cụ thể mà ta chọn loại Query phù hợp 1) Select query (Query truy vấn dữ liệu): loại Query này được sử dụng rất phổ biến, nĩ được dùng nhằm để chọn lựa ra các mẫu tin thoả mãn một điều kiện nào đĩ, ngồi ra cịn cho phép tạo thêm các field tính tốn và kết nhĩm số liệu 2) Update Query (Query cập nhật số liệu): dùng để thay đổi dữ liệu đồng loạt trên một hay nhiều dịng của một hay nhiều bảng. Ví dụ muốn sửa điểm anh văn cho các sinh viên cĩ tham dự kỳ thi học kỳ 2 thuộc lớp tin học 3) Delete Query (Query xố dữ liệu ): xố một hay nhiều mẩu tin từ nhiều bảng. Ví dụ: ta cĩ thể dùng delete Query để xố danh sách các sinh viên đã nghỉ học của một lớp hay tồn trường 4) Find Duplicate Query : Query tìm các mẩu tin trùng dữ liệu với nhau trong một bảng. Ví dụ: tìm tất cả các sinh viên cĩ họ tên trùng nhau thì sử dụng Find Duplicate Query. 5) Find Unmatched Quer (Query tìm các mẫu tin khơng khớp với nhau): tìm trong bảng này những mẫu tin mà mà giá trị của một field nào đĩ mà giá trị khơng trùng khớp giá trị với bất cứ mẫu tin nào khác trong bảng thứ hai. Giả sử vì một lý do nào đĩ mà khi ta nhập điểm mơn học của sinh viên, ta nhập khơng đúng mã sinh viên của một sinh viên. Do đĩ, khi in ra bảng điểm vì khơng tìm được mã sinh viên tương ứng trong Table sinh viên nên cột họ 78
  2. và tên bị trống. Để biết được dữ liệu gõ sai thuộc sinh viên nào, ta sử dụng Find Unmatched Query. 6) Crosstab Query (Query Tham chiếu chéo): dùng để kết nhĩm dữ liệu theo một chỉ tiêu nào đĩ. Nĩ giúp ta tổng hợp số liệu từ một đến nhiều chỉ tiêu theo hàng, trên mỗi hàng lại tổng hợp một (chỉ một) chỉ tiêu khác theo cột, vùng giao nhau giữa cột và hàng thì thể hiện trị số tổng hợp của một (Chỉ một) chỉ tiêu thứ 3 Ví dụ: yêu cầu cho biết số lượng vật tư nhập xuất trong mỗi ngày cụ thể là bao nhiêu, bên cạnh số tổng vật tư đĩ cịn phải cho biết đĩ là những vật tư nào. Kết quả của Crosstab Query trả về như sau: Ngay TotalOFSOL M MH MH MH G H1 2 3 4 01/05/97 32 1 20 2 02/05/97 60 60 03/05/97 20 10 10 07/05/97 2 2 08/05/97 27 7 20 09/05/97 10 10 7) Append Query (Query nối số liệu): nối thêm số liệu từ một hoặc nhiều bảng 8) Make Table Query (Query tạo bảng): dùng để lập các bảng dự phịng, trích rút dữ liệu từ một Table để lưu trữ trước khi xố các mẫu tin này ra khỏi Table hiện hành. 9) Union Query (Query hợp số liệu): gom, nối đuơi số liệu từ hai hay nhiều bảng thành một danh sách để xem chung 10) Data Define Query (Query định nghĩa dữ kiện): sử dụng lệnh của ngơn ngữ SQL để tạo hoặc sửa đổi cấu trúc của một bảng. 5.1.3 CÁC TỐN TỬ 1) Tốn tử số học To Ý nghĩa Ví dụ Kết quả án tử ^ Lũy Thừa 5^3 125 * Nhân 5*3 15 / Chia 7/2 3.5 \ Chia lấy phần nguyên 7\2 3 Mod Chia lấy phần dư 7Mod2 1 + Cộng 7+2 9 - Trừ 7-2 5 79
  3. 2) Tốn tử ghép To Ý nghĩa Ví dụ Kết quả án tử & Ghép chuổi hoặc “Tin”&”Học” Tin Học số với nhau 100 & “năm cơ đơn” 100 nămcơ đơn “hơm nay” &Date() hơm nay:21/11/2004 “phim” &202/2 &”chĩ đốm phim 101 chĩ đốm + Cộng hoặc ghép 1234 +5678 6912cuốn theo chiều chuổi “cuốn theo” + “ Chiều giĩ” giĩ 1001 + “tình huống” Type miss match 3) Tốn tử so sánh Tố Ý nghĩa Ví dụ Kết quả n tử Lớn hơn 2>3 False >= Lớn hơn hoặc 3>=3 True bằng = Bằng 5=2 False Khác 5 True 1997 like :”##9#” -> True 80
  4. 5) Tốn tử logic To Ý nghĩa Giải thích án tử Not Phủ định And Và Tất cả điều kiện đúng thì kết quảnhận GT đúng Or Hoặc Chỉ cần 1 điều kiện đúng thì kết quả nhận GTđúng Xor Loại trừ A Xor B: A, B khác nhau thì đúng A đúng B sai thì đúng II. A sai B đúng thì đúng A đúng B đúng thì sai A sai B sai thì sai Eqv Tương hợp A Eqv B : A, B cho kết quả giống nhau thì đúng A đúng B sai thì Sai A sai B đúng thì sai A đúng B đúng thì đúng A sai B sai thì đúng Lưu ý: Muốn truy cập đến một đối tượng thì ta dùng tốn tử: ! Ví dụ: [Form]![Nhap DSNV]![MANV]: truy cập đến Object MANV của Form Nhap DSNV Muốn truy xuất tới một thuộc tính của Object ta dùng dấu chấm ‘.’ Ví dụ: [MANV].Visible : thuộc tính Visible của đối tượng MANV Các dấu rào : ” ” : Rào cho giá trị chuỗi [ ] : Rào tên biến (tên Control) # .# : Rào giá trị ngày. Ví dụ : Ngaysinh = #11/02/71# BÀI TẬP THỰC HÀNH Dựa trên cơ sở dữ liệu bán hàng 1. Thiết kế một query cĩ tên QBT1 liệt kê danh sách các đơn đặt hàng được lập ra trong tháng 2 năm 1992 2. Thiết kế một query cĩ tên QBT2 liệt kê danh sách các đơn đặt hàng được lập cho khách hàng cĩ mã là AGROMAS 3. Thiết kế một query cĩ tên QBT3 thể hiện danh sách các đơn đặt hàng do nhân viên cĩ mã số là 4 lập ra 4. Thiết kế một query cĩ tên QBT4 liệt kê danh sách khách hàng ở cần thơ 5. Thiết kế một query cĩ tên QBT5 liệt kê danh sách các đơn đặt hàng của tất cả các khách hàng ở cần thơ 6. Thiết kế một query cĩ tên QBT6 liệt kê danh sách các đơn đặt hàng đặt mua mặt hàng rượu 81
  5. 7. Thiết kế một query cĩ tên QBT7 liệt kê danh sách các đơn đặt hàng cĩ ngày lập đơn đặt hàng và ngày nhận hàng cách nhau khơng quá 60 ngày 8. Thiết kế một query cĩ tên QBT8 liệt kê danh sách các đơn đặt hàng đặt mua mặt hàng rượu hoặc bơ 9. Thiết kế một query cĩ tên QBT9 liệt kê danh sách các khách hàng cĩ 2 ký tự cuối của mã KH là :”CO” ở thành phố Nha Trang 5.2 Thiết kế QUERY 5.2.1 QUERY đơn (SELECT QUERY ) . Dịng Field: chứa tên cột của bảng hoặc một biểu thức tính tốn . Dịng Table : chứa tên bảng. Đễ trống khi cột chứa biểu thức tính tốn . Dịng Show : quyết định các field nào hiển thị trong bảng kết quả . Dịng Short :Chỉ định thứ tự sắp xếp các Record kết quả theo nguyên tắc thứ tự sắp xếp các field từ trái sang phải . Dịng Criteria: chỉ định các tiêu chuẩn lựa chọn của Query . Dịng Or :dùng kết hợp các phép tuyển Logic trong lựa chọn . Dịng Sub Total: Dùng để nhĩm các dịng trong kết quả Query - Sefl Join : là kiểu liên kết một bảng dữ liệu với chính nĩ (Trong Query ta phải thêm một bảng vào 2 lần) trong đĩ một mẫu tin của bảng dữ liệu sẽ liên kết với những mẫu tin của chính bảng này. Khi ấy, tất cả các mẫu tin của bảng mà các giá trị của vùng liên kết bằng nhau đều được đưa vào kết quả của Query - Chú ý: nếu ta chọn 2 Table tham gia vào Query mà khơng khai báo mối quan hệ giữa chúng, rồi chọn các field từ 2 bảng đưa vào vùng lưới Access sẽ sẽ liên kết dữ liệu một cách khơng cĩ ý nghĩa ta nhận được kết quả sai. Cụ thể Access sẽ lấy tích Decard của 2 bảng này làm kết quả cho Query 5.2.2 Lập tiêu chuẩn lựa chọn trong query 1) Dùng kiểu Text làm tiêu chuẩn chọn lựa Field Expresstion Decription ShipCity “London” Trình bày các đơn hàng đến London ShipCity “London” Or “Hedge End” Trình bày các đơn hàng đến London hoặc Hedge End ShippedDate Between #1/5/95# and Trình bày các đơn hàng vận chuyển trong những ngày từ #1/10/65# 1/5 đến 1/10 năm 95 ShipCountry In(“Canada”,”UK”) Trình bày các đơn hàng đến Canada hoặc UK ShipCountry Not “USA” Các đơn hàng đến những nước khác Mỹ ShipName Like “S*” Các đơn hàng chuyển đến khách hàng cĩ tên bắt đầu là S CompanyName >=”N” Các đơn hàng đến cơng ty cĩ tên bắt đầu từ N -> Z OrderID Right([OrderID,2])=”99” Các đơn hàng cĩ mã kết thúc là 99 CompanyName Len([CompanyName])>Val(3 Các đơn hàng đến cơng ty cĩ tên dài hơn 30 kí tự 0) 82
  6. 2) Dùng biểu thức tính tốn kiểu ngày làm tiêu chuẩn lựa chọn : Field Expresstion Decription RequiredDate Between Date() And Trình bày các đơn hàng yêu cầu trong DateAdd(“m”,3,Date()) khoảng ngày hơm nay đến 3 tháng sau OrderDate Davg(“[Freight]”, “Orders”) Tìm đơn hàng cĩ phí chuyên chở lớn hơn giá trị trung bình của chúng Quatity >Daverage(“[Quatity]”, “OrderDetail”) Tìm các đơn hàng cĩ số lượng lớn hơn giá trị trung bình của chúng 83
  7. 6) Dùng SubQuery làm tiêu chuẩn lựa chọn Field Expresstion Decription UnitPrice (SELECT [UnitPrice] FROM Tìm những sản phẩm cĩ đơn giá = đơn [Product]WHERE giá sản phẩm tên là Aniseed Syrup [ProductName]=”Aniseed Syrup”) UnitPrice >(SELECT [UnitPrice] FROM Tìm những sản phẩm cĩ đơn giá lớn hơn [Product] đơn giá trung bình Salary > ALL(SELECT [Salary] FROM Tìm nhân viên cĩ lương lớn hơn tất cả các [Employees]WHERE [Tilte] nhân viên cĩ chức vụ là Manage hoặc like”*Manager*” OR ([Tilte] LIKE Vice President “*Vice President*”)) OrderTotal > ALL (SELECT Tìm cácđơn hàng cĩ giá trị lớn hơn giá trị : AVG([UnitPrice]*[Quatity]) trung bình [UnitPrice] FROM [OrderDetail] *[Quatity] 7) Dùng nhiều tiêu chuẩn lựa chọn trong Query - Một Field dùng Or: Lập danh sách các khách hàng ở vùng SP hoặc RJ - Một Field dùng And: lập danh sách khách hàng cĩ tên bắt đầu là “The” và cĩ từ “Box” 84
  8. - Hai Field dùng Or - Hai Field dùng Or - Ba Field dùng And và Or: 5.2.3 Sử dụng Field tính tốn trong query: Nhập biểu thức tính tốn vào vào ơ Field trong query 1) Sử dụng biểu thức Text làm Field tính tốn 85
  9. Biểu thức Mơ tả Fullname:[Firstname] &”” & [Lastname] Thêm cột Fullname là sự kết hợp giữa 2 cột FirstName và Last Name cách nhau 1 khoảng trống Address2: [City]&””&[Region] : ProductInitial: Left([ProductName],1) Tạo cột mới tên: ProductInitial lấy 1 ký tự từ cột Productname TypeCode: Right([AssetCode], 2) : AreaCode:Mid([Fone],2,3) : 2) Dùng biểu thức số học làm Field tính tốn Biểu thức Mơ tả PrimeFreight: [Freight]*0.1 Tăng 10% phímchuyên chở trong Field PrimeFreight LeadTime:[RequireDate] – Tính số ngày đáp ứng đơn hàng cho khách hàng [ShippedDate] TotalStock: Tính tổng giá trị của Field UnitsInStock và [UnitsInStock]+[UnitsOnOrder] UnitsOnOrder trong TotalStock 5.2.4 Dùng dịng total để nhĩm các dịng Trong lưới thiết kế của query dịng total khơng được hiển thị, muốn hiển thị ta chọn View -> Total. Trong mỗi ơ Total cĩ các phép thống kê sau: - Avg: tính giá trị trung bình - Count: Đếm số mẫu tin - First : Giá trị của vùng thuộc mẫu tin đầu tiên trong bảng dữ liệu - Last : Giá trị của vùng thuộc mẫu tin cuối trong bảng dữ liệu - Max : tính giá trị lớn nhất của những giá trị thuộc một vùng dữ liệu kiểu số - Min : tính giá trị nhỏ nhất của những giá trị thuộc một vùng dữ liệu kiểu số - StdDev: tính độ lệch chuẩn của những giá trị thuộc một vùng dữ liệu kiểu số Ngồi ra , khi cần cĩ thể chọn Group By, Where, Expression Cách sử dụng : a) Thống kê đối với tất cả các mẫu tin: trong trường hợp này khơng sử dụng Group By trên bất kỳ vùng nào trong lưới thiết kế b) Thống kê trên từng nhĩm mẫu tin Nhĩm một cấp, ví dụ: Tính tổng số lượng và giá trị từng sản phẩm Field ProductID Quatity SUMOFPRICE:[UnitPrice]*[Quatit y] Table OrderDatails OrderDetails Total Group By sum Sum 86
  10. Nhĩm nhiều cấp :Tính tổng số lượng và sản p[hẩm mà khách hàng đã mua đối với từng chủng loại sản phẩm Field CatergoryID ProductID Quatity SUMOFPRICE:[UnitPrice]*[Quatity] Table Products OrderDatails OrderDetails Total Group By Group By sum Sum Lưu ý :thứ tự thực hiện phép nhĩm là từ trái sang phải c) Tiêu chuẩn lựa chọn trong Group By Tương tự như select query ta cĩ thể lập điều kiện chọn lựa đối với vùng dữ liệu để lựa chọn và lọc ra các mẫu tin thoả điều kiện yêu cầu trong ơ Criteria Ví dụ : cho biết mỗi khách hàng đặt mua bao nhiêu sản phẩm cĩ chủng loại 4 OR 5 d. Lập tiêu chuẩn giới hạn những mẫu tin trước khi tính tổng - Tính tổng trước, giới hạn mẫu tin sau Trình bày những mẫu tin mà số lượng > 950 và giá trị của sản phẩm thuộc từng chủng loại khác 4 và 5 mà mỗi khách hàng đã đặt mua - Giới hạn mẫu tin sau, Tính tổng sau: sử dụng mục chọn Where 87
  11. BÀI TẬP THỰC HÀNH: SELECT QUERY 10. Thiết kế query cĩ tên QBT10 để liệt kê danh sách các đơn đặt hàng được lập ra trong 6 tháng đầu năm 11. Thiết kế query cĩ tên QBT11 liệt kê các danh sách đặt hàng trong tháng 1 và tháng 2 của khách hàng của khách hàng cĩ mã là AGROMAS 12. Thiết kế query cĩ tên QBT12 để tính thành tiền những mặt hàng trong đơn đặt hàng cĩ mã hố đơn là 10144. Ghi chú:Cơng thức tính thành tiền : Thanhtien: ([soluong]*[dongia]*(1-[chietkhau])/100)*100 13. Thiết kế query cĩ tên QBT13 hiển thị thơng tin chi tiết về mỗi đơn đặt hàng bao gồm mã hàng :mã đơn đặt hàng, tên các mặt hàng trong đơn đặt hàng , đơn giá, số lượng thành tiền, số ngày, tổng số tiền trả trước, tổng số tiền cịn lại, biết rằng : Thanhtien: ([soluong]*[dongia]*(1-[chietkhau])/100)*100 Số ngày: Ngày lập ĐĐH đến ngày nhận hàng Tổng số tiền trả trước được tính như sau : - Nếu số ngày 100 15. Thiết kế query cĩ tên QBT15 liệt kê những đơn đặt hàng đã lập cho các mặt hàng là gia vị trong năm 1992 với số tiền lớn nhất 16. Thiết kế query cĩ tên QBT16 :cho biết các khách hàng đã đặt bao nhiêu đơn đặt hàng, tổng số tiền của những đơn đặt hàng đĩ 88
  12. 17. Thiết kế query cĩ tên QBT17:tính tổng số tiền của các đơn đặt hàng mỗi tháng 18. Thiết kế query cĩ tên QBT18:cho biết mỗi khách hàng cĩ bao nhiêu đơn đặt hàng cĩ số tiền lớn hơn 500000 19. Thiết kế query cĩ tên QBT19 : tìm trị giá hàng hố của hố đơn mua hàng cĩ mã số là 1 20. Cho biết mỗi khách hàng đã đặt mua trên bao nhiêu hố đơn và tổng số tiền của các hố đơn đĩ 5.3 QUERY tổng hợp 5.3.1 Query cĩ thơng số (PARAMETER QUERY) : Một Parameter Query trình bày một hay nhiều hộp thoại yêu cầu nhập thơng số mỗi lần chạy Để tạo một Query thơng số ta làm như sau: - Tạo Query select - Trong cơ Criteria của các Field cần sử dụng thơng số, gõ vào dịng nhắc nhở bên trong cặp ngoặc vuơng []. Access sẽ trình bày dịng nhắc xnhở trước khi chạy và một hộp Text Box để nhập thơng số. Text trong dịng nhắc phải khác với tên Field nhưng cĩ thể chứa tên Field Chú ý : Ta phải khai báo kiểu của thơng số trong hộp thoại thơng số bằng cách chọn Query -> Parameter Ví dụ : Cách sử dụng Parameter trong SQL SELECT Orders.OrderDate FROM Orders WHERE Orders.OrderDate Between [NHAP NGAY BAT DAU:] And [NHAP NGAY KET THUC:]; 5.3.2 CROSSTAB QUERY : CrossTab Query tính tổng, trung bình, số lượng hoặc các kiểu tổng kết dữ liệu khác bằng cách nhĩm hai kiểu thơng tin : một theo chiều dọc ở cạnh trái theo chiều đứng. Thơng tin cịn lại theo chiều ngang Tạo CrossTab Query như sau : 89
  13. - Xác địnhCác Field nào mà giá trị sẽ là tiêu đề cột - Xác định các Field nào mà giá trị sẽ là tiêu đề dịng - Các Field chxứa giá trị dùng đểthống kê : count, sum, avg., Ví dụ : Từ CSDL Northwind, tạo ra một query nhĩm : tính tổng số tiền của mỗi nhân viên lặp trên các mặt hàng. Với thơng tin trình bày là kết quả của Query trên, tạo CrossTab Query trình bày lại thơng tin một cách trực quan với ý muốn tên các chủng loại sản phẩm trình bày theo dịng, cịn thơng tin tên nhân viên trìn h bày theo cột Với ví dụ này, trình tự thực hiện như sau: - Tạo Query thống kê tổng số tiền bán được của mổi sản phẩm do mỗi nhân viên lập - Lưu lại Query trên - Tạo một Query mới , vào Query -> CrossTab Query. Nguồn dữ liệu của CrossTab Query là Query thống kê đã tạo - Xác định dịng , cột, value hiển thị trong CrossTab Query 90
  14. - Click Run để xem kết quả vừa tạo 5.3.3 SUB QUERY 1) Sub Query trả về giá trị đơn - Cĩ thể đặt Sub Query bất kỳ nơi nào đặt biểu thức - Trong mệnh đề Where dùng Sub Query với tốn tử bằng - Nếu Sub Query trả về nhiều hơn một giá trị thì sẽ nhận lỗi Ví dụ : tìm tất cả các khách hàng cĩ hố đơn vào ngày gần nhất Cú Pháp SQL: SELECT Orders.OrderID, Orders.CustomerID, Orders.OrderDate FROM Orders WHERE Orders.OrderDate = (select max(orderDate) from Orders); 2) Sub Query trảvề danh sách các giá trị - Trong mệnh đề Where dùng tốn tử IN với Sub Query 5.3.4 MAKE TABLE QUERY 91
  15. 1) Cơng dụng : - Tạo một bảng để xuất (Export) đến một Database khác của Access - Tạo một báo cáo dữ liệu vào một thời điểm nhất định - Tạo một bản sao lưu dự phịng - Tạo một bảng để lưu trữ các Record khác - Tăng tốc độ thực hiện của From, Report 2) Cách tạo: - Tạo một select Query - Trong chế độ thiết kế chọn Query -> Make Table Query - Nhập tên bảng muốn tạo trong hộp thoại Table Name - Chọn Current Database nếu muốn lưu bảng mới tạo vào CSDL hiện hành hoặc Another và tên của Database muốn đặt bảng vào đĩ - Sau khi hồn chỉnh Query cĩ thể xem trước bảng trước khi tạo nĩ bằng cách Click vziew Button trên thanh Toobar - Để bắt đầu tạo bảng click Run trên thanh Toolbar Ghi chú : Dữ liệu trên bảng mới tạokhơng thừa hưởng các tính chất của Field hay khố chính trên bảng củ 5.3.5 UPDATE QUERY: Thay đổi một nhĩm các Record trong một hay nhiều bảng đang tồn tại Cách tạo: 92
  16. - Tạo 1 Query đơn, chọn các bảng hay Query chứa nhiều dịng cần cập nhật và chứa các Field dùng để chỉ định tiêu chuẩn - Trong chế độ thiết kế chọn Query -> Update Query - Kéo các Field cần thiết để xác lập điều kiện vào lưới thiết kế - Điền các tiêu chuẩn lựa chọn trong dịng Criteria - Trong ơ Update To của các Field muốn cập nhật, nhập biểu thức hay giá trị dùng để thay đối các Field - Sau khi hồn chỉnh Query ta cĩ thể xem trước bảng bằng cách click vào View Button trên Toolbar - Để bắt đầu tạo bảng click Ru n trên Toolbar Ví dụ: tạo Update Query tăng sản phẩm cĩ mã là 4 lên 10% 5.3.6 DELETE QUERY : Xố các mẫu tin từ một hoặc nhiều bảng Cách tạo:xố dịng từ một hoặc nhiều bảng cĩ quan hê( - Tạo 1 Query đơn, chọn các bảng hay Query chứa nhiều dịng cần cập nhật và chứa các Field dùng để chỉ định tiêu chuẩn - Trong chế độ thiết kế chọn Query -> Delete Query - Đối với các bảng cần xố record trong chúng Kéo dấu * vào lưới thiết kếtừ From sẽ xuất hiện trong ơ Delete dưới các Field này - Kéo các Field dùng làm tiêu chuẩn thiết kế, từ Where sẽ xuất hiện trong ơ Delete dưới những ơ này - Điền các tiêu chuẩn lựa chọn trong dịng Criteria - Sau khi hồn chỉnh Query ta cĩ thể xem trước bảng bằng cách click vào View Button trên Toolbar - Để bắt đầu tạo bảng click Ru n trên Toolbar 93
  17. Ghi chú : - Nếu ta thực hiện 1 Delete Query trên 1 bảng bên 1 trong mối quan hệ 1-n và tùy chọn Casede Delete Related Record cĩ hiệu lực, Access sẽ xố luơn các dịng của các bảng cĩ quan hệ bên nhiều - Nếu cần đính kèm cả bảng bên nhiều theo cùng với bảng bên một , để thêm cột tiêu chuẩn, phải thi hành Query hai lần vì Query khơng thể xố 2 bảng cùng lúc Đầu tiên thiết kế Query xố các Record trong bảng bên nhiều Gỏ bỏ các Fiel List nở bảng nhiều trong lưới thiết kế, saqu đĩ thi hành Query lần thứ 2 xố các Record trong bảng bên một Ví dụ: tạo Delete Query xố các đơn hàng lập từ năm 94 từ bảng Orders94 5.3.7 UNION QUERY 1. Tổ hợp các Field từ 2 hay nhiều bảng vào một Field Ví dụ: Union Query sau sẽ trả về tên cơng ty thành phố từ hai bảng Nhà cung cấp và khách hàng 2. Cách tạo 1 Union query: - Tạo một select Query - Khơng thyêm bảng vào Query , click New để đĩng hộp thoại - Chọn query -> SQL Specific -> Union - Nhập các lệnh SQl nối nhau bằng tốn tử UNION nếu ta khơng lấy các dịng trùng nhau từ hai bảng này. Ngược lại, lấy các dịng trùng nhau từ hai bảng chọn UNION ALL - Nếu cần chỉ ra trật tự sắp xếp trong 1 Unuio Query , thêm mệnh đề Order By vào cuối củalệnh SQL sau cùng. Trong mệnh đề nàyxác định tên field cần sắp xếp, tên Field này phải cĩ mặt trong lệnh SQL đầu tiên - Click nút lệnh View trên Toolbar để xem kết quả 94
  18. - 1) Sắp xếp trong Union Query SELECT City, CompanyName, ContactName, "Customers" AS [Relationship] FROM Customers UNION SELECT City, CompanyName, ContactName, "Suppliers" FROM Suppliers ORDER BY City, CompanyName 2) Trả về các record trùng nhau SELECT City, CompanyName, ContactName, "Customers" AS [Relationship] FROM Customers UNION ALL SELECT City, CompanyName, ContactName, "Suppliers" FROM Suppliers ORDER BY City, CompanyName; Chú ý : - Mỗi lệnh Select lấy số Field giống nhau, theo thứ tự, các field tương ứng phải cĩ kiểu phù hợp, trừ một Field kiểu Text nối với một Field kiểu Number - Nếu cần đổi tên Field dùng mệnh đề AS BÀI TẬP THỰC HÀNH Thiết kế group By Query thực hiện : 21. Tính tổng số tiền của các đơn đặt hàng mỗi tháng 22. Cho biết mỗi khách hàng cĩ bao nhiêu đơn đặt hàng 23. Cho biết mỗi khách hàng cĩ bao nhiêu đơn đặt hàng > 500000 24. Hãy cho biết những khách hàng cĩ số tiền mua lớn hơn 1.000.000 25. Tính giá trị hàng hố của hố đơn mua hàng cĩ mã hố đơn là 1 26. Cho biết mỗi khách hàng đã đặt mua bao nhiêu hố đơn và tổng tiền của các hố đơn đĩ 27. Tính tổng số tiền của các hố đơn mổi tháng trong năm 99 28. Hãy cho biết mặt hàng cĩ giá cao nhất 29. Hãy cho biết mặt hàng cĩ giá thấp nhất 30. Cho biết mỗi nhân viên đã lập bao nhiêu hố đơn mỗi ngày 95
  19. 31. Cho biết mỗi thành phố cĩ bao nhiêu khách hàng 32. Hãy cho biết tổng doanh thu trong mỗi năm 33. Cho biết mỗi năm cĩ bao nhiêu hố đơn và tổng doanh thu trên hố đơn đĩ Thiết kế Parameter Query thực hiện các yêu cầu sau : 34. Tham khảo các hố đơ.n mua hàng của khách hàng nào đĩ đã đặt hàng cho cơng ty. Thơng số yêu cầu [HAY CHO BIET MA KHACH HANG ?] 35. THAM khảo hố đơn mua hàng trong một thời gian nào đĩ. Thơng số yêu cầu: [NHAP NGAY DAU:], [NHAP NGAY CUOI] Thiết kế Query CrossTab thực hiện các yêu cầu sau: 36. Cho biết tổng số tiền mua mỗi sản phẩm của mỗi khách hàng bao gồm các thơng tinsau: MAKH, tên mỗi mặt hàng , mổi sản phẩm trình bày thành một cột 37. Thống kê những sản phẩm đã bán cho những khách hàng tại thành phố HCM gồm các thơng tin sau : số hố đơn lập cho từng sản phẩm, tổng số tiền thu được, t63ng số tiền thu trong từng quý. Mỗi quý trình bày 1 cột 38. Thống kê số liệu cho từng sản phẩm đã bán bao gồm các thơng tin sau: - tổng số hố đơn đã lập cho mỗi sản phẩm - tổng số lượng bán ra - tổng số tiền thu được Thiết kế Query vấn tin hành động (Make Table Query ) thực hiện các yêu cầu sau: 39. thiết kế Query tạo bảng chứa các dữ liệu MAHD, MAKH, NGAYHD, DONGIA của các hố đơn trong năm 99 từ bảng hố đơn 40. thiết kế Query thực hiện lư udanh sách của các khách hàng 41. thực hiện lưu danh sách của sản phẩm cĩ mã chủng loại là 1 42. thực hiện tạo bảng lưu những hố đơn đã lập trong năm 99 Thiết kế Query vấn tin xố dữ liệu (DeleteTable Query ) thực hiện các yêu cầu sau: Lưu ý: trước khi thực hiện Delete Query trên bảng nào , hãy dùng Query Make table tạo ra các bảng dự phịng đĩ 43. thiết kế Query xố những hố đơn lập vào năm 2000 44. thiết kế Query xố những sản phẩm mà cơng ty ngưng cung cấp Thiết kế Query vấn tin Nối dữ liệu (Append Table Query ) thực hiện các yêu cầu sau: 45. nối vào bảng đã tạo trong câu 42 những hố đơn trong năm 2000 lấy từ bảng hố đơn Thiết kế Query vấn tin cập nhật dữ liệu (UpdateTable Query ) thực hiện các yêu cầu sau: 46. nâng giá cho những sản phẩm cĩ mã là 1 đã tạo trong bài tập 41 lên 10% 96
  20. BÀI 6 THIẾT KẾ CƠ SỞ DỮ LIỆU VỚI CÁC DẠNG CHUẨN Mã bài : ITPRG3_02.6 Giới thiệu : Mục tiêu thực hiện: Học xong bài này học viên sẽ cĩ khả năng: - Mơ tả được bốn vấn đề bất thường về dữ liệu. - Phân rã lược đồ bao gồm: nối khơng mất thơng tin và duy trì các phụ thuộc. - Định nghĩa được chính xác phụ thuộc hàm, bao đĩng. - Biết cách áp dụng các luật của hệ tiên đề Armstrong để tìm các phụ thuộc hàm được suy ra logic từ tập các phụ thuộc hàm đã cho. - Biết cách áp dụng thuật tốn bao đĩng để tìm bao đĩng của tập các thuộc tính đã cho. - Phát biểu và giải thích các dạng chuẩn 1NF, 2NF, 3NF và BCNF. - Dùng các dạng chuẩn để phân rã một lược đồ quan hệ thành các lược đồ con ở các dạng chuẩn. - Phân tích các yêu cầu nối khơng mất thơng tin và duy trì các phụ thuộc trong quá trình chuẩn hĩa bằng sự phân rã Nội dung chính: 6.1 Dạng chuẩn của lược đồ quan hệ (normal forms for relation schemes) 6.2 Phép tách kết nối bảo tồn 6.2.1 Phép tách kết nối bảo tồn thơng tin (lossless-join decomposition) 6.2.2 Phép tách bảo tồn phụ thuộc hàm 6.3 Thiết kế csdl bằng cách phân rã 6.3.1 Phân rã thành dạng chuẩn BC (hay chuẩn 3) bảo tồn thơng tin 6.3.2 Phân rã thành dạng chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm 6.1 Dạng chuẩn của lược đồ quan hệ (normal forms for relation schemes) Trong thực tế, một ứng dụng cụ thể cĩ thể được thiết kế thành nhiều lược đồ cơ sở dữ liệu khác nhau, và tất nhiên chất lượng thiết kế của các lược đồ CSDL này cũng khác nhau. Chất lượng thiết kế của một lược đồ CSDL cĩ thể được đánh giá dựa trên nhiều tiêu chuẩn trong đĩ sự trùng lắp thơng tin và chi phí kiểm tra các ràng buộc tồn vẹn là hai tiêu chuẩn quan trọng. Sau đây là một số tiêu chuẩn để đánh giá độ tốt/xấu của một lược đồ quan hệ. Trước tiên ta tìm hiểu một số khái niệm liên quan: 6.1.1 Định nghĩa các dạng chuẩn a) Dạng Chuẩn Một (First Normal Form) Một lược đồ quan hệ Q ở dạng chuẩn 1 nếu tồn bộ các thuộc tính của mọi bộ đều mang giá trị đơn. Ví dụ 1: Xét quan hệ 97
  21. MASV HOVATEN KHOA TENMONHOC DIEMT HI 99023 NGUYENTHITHU CONG NGHE THONG KY THUAT LAP 6 TIN TRINH TOAN ROI RAC 8 CO SO DU LIEU 4 99030 LE VAN THANH DIEN TU VI XULY 4 Quan hệ này khơng đạt chuẩn 1 vì các thuộc tính TENMONHOC, DIEMTHI của bộ thứ nhất khơng mang giá trị đơn (chẳng hạn sinh viên NGUYEN THI THU cĩ thuộc tính TENMONHOC là KY THUAT LAP TRINH, TOAN ROI RAC, CO SO DU LIEU). Ta hồn tồn cĩ thể đưa quan hệ trên về dạng chuẩn 1 như sau: MASV HOVATEN KHOA TENMONHOC DIEMTHI 99023 NGUYENTHITHU CONG NGHE THONG KY THUAT LAP TRINH 6 TIN 99023 NGUYENTHITHU CONG NGHE THONG TOAN ROI RAC 8 TIN 99023 NGUYENTHITHU CONG NGHE THONG CO SO DU LIEU 4 TIN 99030 LE VAN THANH DIEN TU VI XULY 4 Chú ý ràng khi xét các dạng chuẩn, nếu ta khơng nĩi gì thêm, ta hiểu dạng chuẩn đang xét ít nhất là đạt dạng chuẩn 1. b) Dạng Chuẩn 2 (Second Normal Form) Một lược đồ quan hệ Q ở dạng chuẩn 2 nếu Q đạt chuẩn 1 và mọi thuộc tính khơng khĩa của Q đều phụ thuộc đầy đủ vào khĩa. Thuật tốn kiểm tra dạng chuẩn 2 Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn 2 hay khơng đạt chuẩn 2. Bước 1: Tìm tất cả khĩa của Q Bước 2: Với mỗi khĩa K, tìm bao đĩng của tất cả tập con thật sự S của K. Bước 3: Nếu cĩ bao đĩng S+ chứa thuộc tính khơng khĩa thì Q khơng đạt chuẩn 2 Ngược lại thì Q đạt chuẩn 2 Ví dụ 2: Cho lược đồ quan hệ Q(A,B,C,D) và tập phụ thuộc hàm F={AB C; B D; BC A}. Hỏi Q cĩ đạt chuẩn 2 khơng? Giải: TN={B}, TG={AC} 98
  22. + Xi (TN  Xi) (TN Xi) Siêu khĩa khĩa  B BD A AB ABCD AB AB C BC ABCD BC BC AC ABC ABCD ABC Khĩa là K1=AB và K2=BC. Ta thấy BK1, B D,D là thuộc tính khơng khĩa thuộc tính khơng khĩa khơng phụ thuộc đầy đủ vào khĩa Q khơng đạt chuẩn 2. Ví dụ 3: Quan hệ sau đạt chuẩn 2. Q(G,M,V,N,H,P) F={G M; G N; G H; G P; M V; NHP M} Giải: TN={G} TG={M,N,H,P} + Xi (TN  Xi) (TN Xi) Siêu khĩa khĩa  G Q+ G G M GM Q+ GM N GN Q+ GN MN GMN Q+ GMN H GH Q+ GH MH GMH Q+ GMH NH GNH Q+ GNH MNH GMNH Q+ GMNH P GP Q+ GP MP GMP Q+ GMP NP GNP Q+ GNP MNP GMNP Q+ GMNP HP GHP Q+ GHP MHP GMHP Q+ GMHP NHP GNHP Q+ GNHP MNHP GMNHP Q+ GMNHP Lược đồ quan hệ Q chỉ cĩ một khĩa và khĩa chỉ cĩ một thuộc tính nên mọi thuộc tính đều phụ thuộc đầy đủ vào khĩa Q đạt chuẩn 2 Hệ quả: Nếu Q đạt chuẩn 1 và tập thuộc tính khơng khĩa của Q bằng rỗng thì Q đạt chuẩn 2 Nếu tất cả khĩa của quan hệ chỉ gồm một thuộc tính thì quan hệ đĩ ít nhất đạt chuẩn 2. Ví dụ 4: Q(A,B,C,D,E,H) F={A E; C D; E DH} Giải: 99
  23. TN={ACB} TG={E} + Xi (TN  Xi) (TN Xi) Siêu khĩa khĩa  ACB ABCDEH ACB ACB E ACBE ABCDEH ACBE khĩa của Q là K = {ABC}.CK, C D, D là thuộc tính khơng khĩa D phụ thuộc khơng đầy đủ vào khĩa nên Q khơng đạt chuẩn 2. c) Dạng Chuẩn 3 (Third Normal Form) Thuộc tính phụ thuộc bắc cầu Q là lược đồ quan hệ, X,Y là hai tập con của Q+, A là một thuộc tính. Nĩi rằng A phụ thuộc bắc cầu vào X nếu cả ba điều sau thỏa: + X Y,Y A + Y X + A XY Định nghĩa 1: Lược đồ quan hệ Q ở dạng chuẩn 3 nếu mọi phụ thuộc hàm X A F+ với A X đều cĩ: Hoặc X là siêu khĩa Hoặc A là thuộc tính khĩa Định nghĩa 2: Lược đồ quan hệ Q ở dạng chuẩn 3 nếu mọi thuộc tính khơng khĩa của Q đều khơng phụ thuộc bắc cầu vào một khĩa bất kỳ của Q Hai định nghĩa trên là tương đương, tuy nhiên việc cài đặt thuật tốn kiểm tra dạng chuẩn 3 theo định nghĩa 1 thì hiệu quả hơn nhiều vì khơng phải kiểm tra tính phụ thuộc bắc cầu. Ta chứng minh hai định nghĩa tương đương bằng cách: Từ định nghĩa 1 khơng cĩ phụ thuộc bắc cầu vào một khĩa bất kỳ của Q. Thật vậy: Giả sử cĩ phụ thuộc bắc cầu vào khĩa nghĩa là cĩ K Y,Y A,Y K và A KY. Y A là một phụ thuộc hàm nên theo định nghĩa 1 cĩ hai trường hợp xảy ra cho Y: + Y là siêu khĩa Y K điều này mâu thuẫn với Y K. + Y khơng là siêu khĩa A là thuộc tính khĩa điều này trái với giả thiết A KY Từ định nghĩa 2 nếu X A F+ với A X thì X là siêu khĩa hoặc A là thuộc tính khĩa Nếu X A F+ với A X cĩ X khơng là siêu khĩa và A khơng là thuộc tính khĩa thì dẫn đến một số điều sau: A khơng là thuộc tính khĩa A K X khơng là siêu khĩa X K Tĩm lại ta cĩ K X, X A,X K và A KX A phụ thuộc bắc cầu vào K điều này mâu thuẫn với định nghĩa 2. 100
  24. Hệ quả 1: Nếu Q đạt chuẩn 3 thì Q đạt chuẩn 2 Hệ quả 2: Nếu Q khơng cĩ thuộc tính khơng khĩa thì Q đạt chuẩn 3. Chứng minh: Hệ quả 1: Giả sử Q đạt dạng chuẩn 3 và cĩ thuộc tính khơng khĩa A khơng phụ thuộc hàm đầy đủ vào khĩa K K’ K sao cho K’ A như vậy ta cĩ K K’,K’ A,K’ K, A KK’ Q cĩ phụ thuộc bắc cầu. Hệ quả 2: mọi phụ thuộc hàm trong Q đều cĩ vế phải là thuộc tính khĩa Q đạt dạng chuẩn 3 Định lý: Q là lược đồ quan hệ F là tập các phụ thuộc hàm cĩ vế phải một thuộc tính. Q đạt chuẩn 3 nếu và chỉ nếu mọi phụ thuộc hàm X A F với A X đều cĩ Hoặc X là siêu khĩa Hoặc A là thuộc tính khĩa Chứng minh: Q đạt dạng chuẩn 3 theo định nghĩa ta suy ra mọi phụ thuộc hàm X A F với A X cĩ X là siêu khĩa hoặc A là thuộc tính khĩa. Ngược lại ta phải chứng minh nếu mọi phụ thuộc hàm X A F với A X cĩ X là siêu khĩa hoặc A là thuộc tính khĩa thì mọi phụ thuộc hàm X A F+ với A X cũng cĩ X là siêu khĩa hoặc A là thuộc tính khĩa Giả sử cĩ phụ thuộc hàm X A F+ với A X sao cho X khơng là siêu khĩa và A khơng là thuộc tính khĩa sẽ dẫn đến A X+  X  {các thuộc tính khĩa} điều này mâu thuẫn với A K.Trước khi chứng minh A X+  X  {các thuộc tính khĩa} ta cĩ nhận xét sau: X khơng là siêu khĩa X+ cũng khơng là siêu khĩa. Theo thuật tốn tìm bao đĩng, X+ được hình thành từ các Xi ở mỗi bước Xi cũng khơng là siêu khĩa. Bước cơ sở: X0 = X X0  X  {các thuộc tính khĩa} Bước qui nạp: giả sử cĩ Xi-1  X  {các thuộc tính khĩa}. Bao đĩng Xi được hình thành do cĩ fj = Xj Yj để Xi-1  Xj và Xi = Xi-1  Yj fj = Xj Yj là phụ thuộc hàm cĩ Xj khơng là siêu khĩa fj = Xj Yj là phụ thuộc hàm cĩ Yj là thuộc tính khĩa Xi = Xi-1  Yj  X  {các thuộc tính khĩa} Qua chứng minh trên A X+  X  {các thuộc tính khĩa} A X{các thuộc tính khĩa} A {các thuộc tính khĩa} điều này nghịch lý với điều A K. Thuật tốn kiểm tra dạng chuẩn 3 Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn 3 hay khơng đạt chuẩn 3. Bước 1: Tìm tất cả khĩa của Q Bước 2: Từ F tạo tập phụ thuộc hàm tương đương F1tt cĩ vế phải một thuộc tính. 101
  25. Bước 3: Nếu mọi phụ thuộc hàm X A F1tt với A X đều cĩ X là siêu khĩa hoặc A là thuộc tính khố thì Q đạt chuẩn 3 ngược lại Q khơng đạt chuẩn 3 Ví dụ 5: Cho lược đồ quan hệ Q(A,B,C,D) F={AB C; D B; C ABD}.Hỏi Q cĩ đạt chuẩn 3 khơng? Giải: TN= TG={ABCD} + Xi (TN  Xi) (TN Xi) Siêu khĩa khĩa    A A A B B B AB AB ABCD AB AB C C ABCD C C AC AC ABCD AC BC BC ABCD BC ABC ABC ABCD ABC D D BD AD AD ABCD AD AD BD BD BD ABD ABD ABCD ABD CD CD ABCD CD ACD ACD ABCD ACD BCD BCD ABCD BCD ABCD ABCD ABCD ABCD K1 = {AB}; K2 = {AD}; K3={C} là các khĩa mọi phụ thuộc hàm X A F đều cĩ A là thuộc tính khĩa. Vậy Q đạt chuẩn 3 Ví dụ 6: Quan hệ sau đạt chuẩn 3. Q(N,G,P,M) F = {NGP M,M P} d) Dạng Chuẩn BC (Boyce-Codd Normal Form) Một quan hệ Q ở dạng chuẩn BC nếu mọi phụ thuộc hàm X A F+ với A X đều cĩ X là siêu khĩa. Hệ quả 1: Nếu Q đạt chuẩn BC thì Q đạt chuẩn 3 (hiển nhiên do định nghĩa) Hệ quả 2: Mỗi lược đồ cĩ hai thuộc tính đều đạt chuẩn BC (xét phụ thuộc hàm cĩ thể cĩ của Q ) Định lý: Q là lược đồ quan hệ 102
  26. F là tập các phụ thuộc hàm cĩ vế phải một thuộc tính. Q đạt chuẩn BC nếu và chỉ nếu mọi phụ thuộc hàm X A F với A X đều cĩ X là siêu khĩa Chứng minh: Q đạt dạng chuẩn BC theo định nghĩa ta suy ra mọi phụ thuộc hàm X A F với A X cĩ X là siêu khĩa. Ngược lại ta phải chứng minh nếu mọi phụ thuộc hàm X A F với A X cĩ X là siêu khĩa thì mọi phụ thuộc hàm Z B F+ với B Z cũng cĩ Z là siêu khĩa. Thật vậy, do Z B khơng là phụ thuộc hàm hiển nhiên nên theo thuật tốn tìm bao đĩng phải cĩ X A F sao cho ZX (X là siêu khĩa) Z là siêu khĩa. Thuật tốn kiểm tra dạng chuẩn BC Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn BC hay khơng đạt chuẩn BC. Bước 1: Tìm tất cả khĩa của Q Bước 2: Từ F tạo tập phụ thuộc hàm tương đương F1tt cĩ vế phải một thuộc tính Bước 3: Nếu mọi phụ thuộc hàm X A F1tt với A X đều cĩ X là siêu khĩa thì Q đạt chuẩn BC ngược lại Q khơng đạt chuẩn BC Ví dụ 7: Q(A,B,C,D,E,I) F={ACD EBI;CE AD}. Hỏi Q cĩ đạt chuẩn BC khơng? Giải: TN={C} TG={ADE} + Xi (TN  Xi) (TN Xi) Siêu khĩa khĩa  C C A AC AC D CD CD AD ACD ABCDEI ACD ACD E CE ABCDEI CE CE AE ACE ABCDEI ACE DE CDE ABCDEI CDE ADE ACDE ABCDEI ACDE F  F1tt={ACD E,ACD B,ACD I,CE A,CE D} Mọi phụ thuộc hàm của F1tt đều cĩ vế trái là siêu khĩa Q đạt dạng chuẩn BC Ví dụ 8: Q(SV,MH,THAY)F = {SV,MH THAY;THAY MH} Quan hệ trên đạt chuẩn 3 nhưng khơng đạt chuẩn BC Ví dụ 9: Chẳng hạn cho Q(A,B,C,D) và F={AB C; D B; C ABD} thì Q là 3NF nhưng khơng là BCNF Nếu F={B D,A C,C ABD} là 2 NF nhưng khơng là 3 NF 103
  27. Thuật tốn kiểm tra dạng chuẩn của một lược đồ quan hệ. Vào: lược đồ quan hệ Q, tập phụ thuộc hàm F Ra: khẳng định Q đạt chuẩn gì? Bước 1: Tìm tất cả khĩa của Q Bước 2: Kiểm tra chuẩn BC nếu đúng thì Q đạt chuẩn BC, kết thúc thuật tốn ngược lại qua bước 3 Bước 3: Kiểm tra chuẩn 3 nếu đúng thì Q đạt chuẩn 3, kết thúc thuật tốn ngược lại qua bước 4 Bước 4: Kiểm tra chuẩn 2 nếu đúng thì Q đạt chuẩn 2, kết thúc thuật tốn. ngược lại Q đạt chuẩn 1 Định nghĩa: Dạng chuẩn của một lược đồ cơ sở dữ liệu là dạng chuẩn thấp nhất trong các dạng chuẩn của các lược đồ quan hệ con. 6.2 PHÉP TÁCH KẾT NỐI BẢO TỒN 6.2.1 Phép tách kết nối bảo tồn thơng tin (lossless-join decomposition) Cho lược đồ quan hệ Q(TENNCC,DIACHI,SANPHAM,DONGIA) cĩ quan hệ tương ứng là r Đặt r1 là quan hệ cĩ được bằng cách chiếu r lên Q1(TENNCC,SANPHAM,DONGIA), Đặt r2 là quan hệ cĩ được bằng cách chiếu r lên Q2(TENNCC,DIACHI) Đặt r’là quan hệ cĩ được bằng cách kết tự nhiên giữa r1 và r2 qua TENNCC. chẳng hạn: r TENNC DIACHI SANPHA DONGI C M A Hung 12 Nguyễn Kiệm Gạch ống 200 Hung 12 Nguyễn Kiệm Gạch thẻ 250 Hung 40 Nguyễn Oanh Gạch ống 200 + + r2 = r.Q2 r1 = r.Q1 TENNC DIACHI TENNC SANPHA DONGI C C M A Hung 12 Nguyễn Kiệm Hung Gạch ống 200 Hung 40 Nguyễn Oanh Hung Gạch thẻ 250 TENNCC r’ = r1|><|r2 TENNC DIACHI SANPHA DONGI C M A 104
  28. Hung 12 Nguyễn Kiệm Gạch ống 200 Hung 12 Nguyễn Kiệm Gạch thẻ 250 Hung 40 Nguyễn Oanh Gạch ống 200 Hung 40 Nguyễn Oanh Gạch thẻ 250 Kết quả là r r’ hay r r.Q1|> <|r.Q2 ta nĩi phép tách (Q1,Q2) là tách-kết nối khơng mất mát thơng tin (tách kết nối bảo tồn thơng tin hay phân rã bảo tồn thơng tin). Vậy với điều kiện nào thì phép tách trở thành tách-kết nối khơng mất mát thơng tin? 6.2.1.1 Định nghĩa phép tách Q thành 2 lược đồ con Q là lược đồ quan hệ, Q1, Q2 hai lược đồ con cĩ: + + Q1  Q2 = X + + + Q1  Q2 = Q Nĩi rằng lược đồ quan hệ Q được tách thành hai lược đồ con Q1, Q2 theo phép tách (Q1,Q2) là phép tách kết nối khơng mất (hay phép tách bảo tồn thơng tin) nếu với r là quan hệ bất kỳ của Q ta cĩ: X r = r.Q1 r.Q2 Tức là r được tạo nên từ phép kết nối tự nhiên của các hình chiếu của nĩ trên các Q1,Q2 6.2.1.2 Tính chất Nếu Q là một lược đồ quan hệ, Q1,Q2 là hai lược đồ quan hệ con cĩ + + Q1  Q2 = X + + + Q1  Q2 = Q + X Q2 Thì r = r.Q1 r.Q2 Chứng minh: t r t r.Q1 r.Q2 t r t1 r1 t1 = t.Q1 t2 r2 t2 = t.Q2 t1.X = t2.X = t.X t r.Q1 r.Q2 (Theo định nghĩa) t r.Q1 r.Q2 t r t r.Q1 r.Q2 t1 r1 t1 = t.Q1 (1) mà t1 r1=r.Q1 nên theo định nghĩa phép chiếu ta lại cĩ t’ r t1 = t’.Q1 (2) (1) và (2) t’.Q1 = t.Q1 t’.X = t.X t’.Q2 = t.Q2 (do X Q2) t’ = t t r 105
  29. Ví dụ 10: cho Q(SAIP), Q1 =(SA) , Q2 =(SIP) F={S A,SI P}. Hỏi việc tách Q thành Q1 và Q2 cĩ gây ra mất mát thơng tin khơng? Áp dụng tính chất trên, ta cĩ + + Q1  Q2 = S + + + Q1  Q2 = SAIP = Q + S SA = Q1 S Theo tính chất trên, với mọi quan hệ r của Q ta luơn cĩ r = r.Q1 r.Q2. Suy ra phép tách trên là phép tách kết nối bảo tồn thơng tin. 6.2.1.3 Phép tách Q thành n lược đồ con Q Q1 Q12 Q21 Q2 Q Q 3 4 Q là một lược đồ quan hệ, F là tập phụ thuộc hàm. Q được tách thành các lược đồ con Q1, Q2, Q3 ,Qn theo từng bước mà ở mỗi bước một lược đồ được tách thành hai lược đồ con và thỏa mãn điều kiện của tính chất bảo tồn thơng tin thì với r là quan hệ bất kỳ của Q ta luơn cĩ: r = r.Q1|> <|r.Qk+1m 6.2.1.4 Thuật tốn kiểm tra phép tách kết nối bảo tồn thơng tin - Thuật tốn Dữ liệu vào: lược đồ quan hệ Q(A1,A2, An), tập phụ thuộc hàm F, phép tách =(Q1,Q2, ,Qk). Dữ liệu ra: kết luận phép tách cĩ phải là phép tách bảo tồn thơng tin ? 106
  30. 1. Thiết lập bảng với k+1 dịng, n+1 cột . Cột j ứng với thuộc tính Aj (j=1 n), hàng i ứng với lược đồ quan hệ Qi(i=1 k). Tại ví trí hàng i, cột j ta điền ký hiệu Aj nếu Aj Qi, nếu khơng ta đặt ký hiệu bt vào vị trí đĩ. (với t đầu tiên bằng 1) và sau đĩ tăng t lên một đơn vị. 2. Xét lần lượt các phụ thuộc hàm trong F, áp dụng cho bảng vừa mới thành lập ở trên. Giả sử xét (X Y) F, chúng ta tìm những hàng giống nhau ở tất cả các thuộc tính của X, nếu thấy những hàng như vậy ta sẽ làm cho các ký hiệu của hai hàng này bằng nhau ở tất cả các thuộc tính của Y. Khi làm cho 2 ký hiệu này bằng nhau, nếu một trong hai ký hiệu là aj thì cho ký hiệu kia trở thành aj, nếu hai ký hiệu là bk hoặc bl thì cĩ thể cho chúng trở thành bt hoặc bt (với t = min (k,l)). Bước này được tiếp tục cho các phụ thuộc hàm cịn lại của F cho đến khi khơng cịn áp dụng được nữa. 3. Xét bảng kết quả, nếu thấy trong bảng này cĩ một hàng chứa tồn aj (i=1 n) thì kết luận đĩ là phép kết nối bảo tồn thơng tin, ngược lại là phép kết nối mất mát thơng tin. Chú ý: một điều quan trọng cần phải nhớ là khi cho hai ký hiệu bằng nhau thì phải cho bằng nhau ở tất cả các xuất hiện của chúng trong bảng chứ khơng phải chỉ cho bằng nhau ở những ký hiệu trong phạm vi các phụ thuộc X Y F. Ví dụ 11: Với Q(ABCDE) Q1 = (AD),Q2 =(AB), Q3 =(BE), Q4 =(CDE), Q5 =(AE) F = {A C,B C,A D,DE C,CE A} Kiểm tra tính bảo tồn thơng tin của phép phân rã Q thành Q1,Q2,Q3,Q4,Q5. Bước 1: a1 a2 A3 a4 a5 Bước 2: Điền b1,b2,b3, A B C D E A B C D E Q1(AD) a1 a4 Q1(AD) a1 b1 b2 a4 b3 Q2(AB) a1 a2 Q2(AB) a1 a2 b4 b5 b6 Q3(BE) a2 a5 Q3(BE) b7 a2 b8 b9 a5 Q4(CDE) A3 a4 a5 Q4(CDE) b10 b11 a3 a4 a5 Q5(AE) a1 a5 Q5(AE) a1 b12 b13 b14 a5 Sửa bảng giá trị để nĩ thỏa A C Sửa bảng giá trị để nĩ thỏa B C Sửa b4,b13 thành b2 Sửa b8 thành b2 A B C D E A B C D E Q1(AD) a1 b1 b2 a4 b3 Q1(AD) a1 b1 b2 a4 b3 Q2(AB) a1 a2 b2 b5 b6 Q2(AB) a1 a2 b2 b5 b6 Q3(BE) b7 a2 b8 b9 a5 Q3(BE) b7 a2 b2 b9 a5 Q4(CDE) b10 b11 a3 a4 a5 Q4(CDE) b10 b11 a3 a4 a5 Q5(AE) a1 b12 b2 b14 a5 Q5(AE) a1 b12 b2 b14 a5 107
  31. Sửa bảng giá trị để nĩ thỏa A D Sửa bảng giá trị để nĩ thỏa DE C Sửa b5,b14 thành a4 sửa b2 thành a3 sửa tất cả b2 thành a3 A B C D E A B C D E Q1(AD) a1 b1 b2 a4 b3 Q1(AD) a1 b1 a3 a4 b3 Q2(AB) a1 a2 b2 a4 b6 Q2(AB) a1 a2 a3 a4 b6 Q3(BE) b7 a2 b2 b9 a5 Q3(BE) b7 a2 a3 b9 a5 Q4(CDE) b10 b11 a3 a4 a5 Q4(CDE) b10 b11 a3 a4 a5 Q5(AE) a1 b12 b2 a4 a5 Q5(AE) a1 b12 a3 a4 a5 Sửa bảng giá trị để nĩ thỏa CE A Lần lượt xét lại các phụ thuộc hàm Sửa b7,b10 thành a1. trong F, nếu bảng giá trị chưa thỏa phụ thuộc hàm nào thì tiếp tục làm cho nĩ thỏa. Sửa bảng giá trị để nĩ thỏa A D A B C D E A B C D E Q1(AD) a1 b1 a3 a4 b3 Q1(AD) a1 b1 a3 a4 b3 Q2(AB) a1 a2 a3 a4 b6 Q2(AB) a1 a2 a3 a4 b6 Q3(BE) a1 a2 a3 b9 a5 Q3(BE) a1 a2 a3 a4 a5 Q4(CDE) a1 b11 a3 a4 a5 Q4(CDE) a1 b11 a3 a4 a5 Q5(AE) a1 b12 a3 a4 a5 Q5(AE) a1 b12 a3 a4 a5 Dịng thứ Q3(BE) của bảng chứa tồn giá trị aj (j=1 n) nên phép phân rã trên là bảo tồn thơng tin. - Định lý Bảng kết quả của thuật tốn trên cho phép ta kết luận được tính bảo tồn hay khơng bảo tồn thơng tin của phép tách. Chứng minh: Ta chứng minh nếu bảng kết quả thuật tốn khơng cĩ hàng chỉ chứa tồn giá trị a thì phép tách khơng bảo tồn thơng tin. Thật vậy: Ta xây dựng một quan hệ r cĩ các giá trị như bảng kết quả của thuật tốn, các hàng là các bộ. Quan hệ r thỏa tập phụ thuộc F vì thuật tốn đã sửa các giá trị của r để nĩ khỏi vi phạm các phụ thuộc hàm trong F r là một quan hệ của lược đồ Q. Ta tách quan hệ r thành các quan hệ ri với ri = r.Qi và dùng phép kết tự nhiên để kết chúng lại. Nếu: + + + k Qk Qi =  i r1|> <|rk phép tách khơng bảo tồn thơng tin. 108
  32. Ta chứng minh nếu bảng kết quả thuật tốn cĩ hàng chỉ chứa tồn giá trị a thì phép tách bảo tồn thơng tin. Ta chứng minh điều này qua 2 bước: + Bước 1: chứng minh nếu t r t r1|> <|rn. Nĩi cách khác phép tách bảo tồn thơng tin. 6.2.2 Phép tách bảo tồn phụ thuộc hàm (decompositions that preserve dependencies) 6.2.2.1 Tập phụ thuộc hàm Fi của Qi Phần trên chỉ đề cấp vấn đề tách một lược đồ quan hệ Q(A1,A2, An)thành các lược đồ con Q1,Q2, ,Qk cịn khơng đề cập đến tập phụ thuộc hàm của các lược đồ con này. Nếu Q(A1,A2, An) là lược đồ quan hệ, F phụ thuộc hàm, =(Q1,Q2, ,Qk)là phép phân rã bảo tồn thơng tin, ri là quan hệ của Qi thì tính chất sau thỏa: + + + ri chỉ thỏa các phụ thuộc hàm X Y F với XYQi + + + Nĩi cách khác, tập phụ thuộc hàm của Qi chính là Fi cĩ Fi ={X Y F | XYQi }. Ta cĩ thể hiểu F được phân rã thành các F1, ,Fk 109
  33. Chứng minh tính chất trên: + + + Do ri được tách từ r mà r thỏa F ri thỏa các phụ thuộc hàm X Y F với XYQi .Theo + định nghĩa phụ thuộc hàm, đương nhiên ri khơng thỏa các phụ thuộc hàm X Y F với + + XYQi . Ngồi ra ri khơng thỏa bất kỳ một phụ thuộc hàm nào X Y F . Thật vậy nếu cĩ + X Y như vậy thì r = r1|> <|rn cũng phải thỏa X Y F . Điều này mâu thuẫn với định nghĩa của tập F+ . 6.2.2.2 Định nghĩa: Cho phân rã =(Q1,Q2, ,Qk) của một lược đồ quan hệ, và một tập phụ thuộc hàm F. Hình + chiếu của F trên một tập các thuộc tính Qi ký hiệu Qi(F) là tập các phụ thuộc hàm X Y F+ sao cho XY  Z. + + Qi(F)=Fi ={ X Y| X Y F và XY  Qi} Ta nĩi phân rã bảo tồn tập phụ thuộc hàm F nếu + + F   Qi(F) F = ( Qi(F)) với i=1 k + + Hệ quả: F  ( Qi(F)) với i=1 k Nhận xét: từ hệ quả trên ta suy ra: để xác định phép phân rã =(Q1,Q2, ,Qk) cĩ bảo tồn phụ thuộc hàm hay khơng, với mỗi phụ thuộc hàm X Y F ta xác định xem nĩ cĩ là thành viên của tập phụ thuộc hàm G =  Qi(F) hay khơng. Ta khơng cần xác định chiều ngược lại. Ví dụ12: Cho lược đồ quan hệ Q(A,B,C) và F={A B,B C,C A}. Phép phân rã =(Q1,Q2) + tách Q thành hai lược đồ quan hệ Q1(A,B) và Q2(B,C). Hãy tính hình chiếu của F trên Q1 và + Q2 .Phép phân rã cĩ bảo tồn phụ thuộc hàm F khơng? Giải: về nguyên tắc ta cĩ thể giải bài tốn theo các bước dưới đây Bước 1: Kê tất cả tập con của Q+ A B C  A B C A AC B BC ABC Bước 2: Tính bao đĩng của các tập con của Q+ += A+=ABC B+ =AB C+ =AB C C AB+ =AB AC+ =AB C C BC+ =AB C ABC =AB + C Bước 3: Tính F+ 110
  34. A B B A C A AB AB AC B BC A C A AB B AB C B AB C AC AB BC AB A C B C C AB AB BC AC BC BC AC A AC B AC C AC AB AB AC AB BC AB C C C A BC B BC C BC A AB B AB C AB C C C Bước 4: Tính Q1(F), Q2(F) + Q1(F)= F1 ={A B,A AB,B A,B AB}{A B,B A} (chỉ lấy pth cĩ vế phải 1 tt) + Q2(F)= F2 ={B C,B BC,C B,C BC}{B C,C B}(chỉ lấy pth cĩ vế phải 1 tt) Bước 5: G = Q1(F) Q2(F)={A B,A AB,B A,B AB,B C,B BC,C B,C BC} F={A B,B C,C A} cĩ A B, B C đều là thành viên của G, cịn C A cĩ là thành viên của + + G hay khơng ta tính CG . CG =ABC C A cũng là thành viên của G. Vậy phép phân rã trên bảo tồn phụ thuộc hàm. Bài tốn trên cĩ thể được giải theo các bước đơn giản sau cho từng lược đồ quan hệ con: Tính cho Q1 + Bước 1: Kê tất cả tập con của Q1 A B  A B AB + Bước 2: Tính bao đĩng của các tập con của Q1 += A+=ABC B+ =ABC AB+ =ABC + Bước 3: Tính F1 =Q1(F) A B B A A AB B AB Tính cho Q2 + Bước 4: Kê tất cả tập con của Q2 B C  B C BC + Bước 5: Tính bao đĩng của các tập con của Q2 += B+=ABC C+ =ABC BC =ABC + 111
  35. + Bước 6: Tính F2 =Q2(F) B C C B B BC C BC Bước 7: G=Q1(F)Q2(F)={A B,A AB,B A,B AB,B C,B BC,C B,C BC} F={A B,B C,C A} cĩ A B, B C đều là thành viên của G cịn C A cĩ là thành viên của + + G hay khơng ta tính CG . CG =ABC C A cũng là thành viên của G. Vậy phép phân rã trên bảo tồn phụ thuộc hàm. 6.2.2.3 Ý nghĩa của phân rã cĩ bảo tồn phụ thuộc hàm Ví dụ 13: Cho lược đồ quan hệ Q(C,S,Z) và F={CS Z,Z C}. Phép tách =(Q1,Q2) tách Q thành hai lược đồ Q1(S,Z) và Q2(C,Z). Hỏi phép tách cĩ bảo tồn phụ thuộc hàm khơng? Giải: Q1 cĩ các tập thuộc tính con: S Z  S Z SZ + Bao đĩng của các tập thuộc tính con Q1 += S+=S Z+ =ZC SZ+ =CSZ + F1 chỉ gồm các phụ thuộc hàm hiển nhiên vì tất cả các phụ thuộc hàm sau đều khơng thỏa: Z C SZ C Z ZC SZ CS SZ CZ SZ CSZ Q2 cĩ các tập thuộc tính con: C Z  C Z CZ + Bao đĩng của các tập thuộc tính con Q2 += C+=C Z+ =ZC CZ+ =CZ + F2 gồm các phụ thuộc: Z C Z ZC Q1(F)Q2(F)={Z C,Z ZC}{Z C} khơng tương đương với F = {CS Z,Z C} Vậy phép phân rã trên khơng bảo tồn phụ thuộc hàm, điều này cĩ nghĩa khi ta đưa dữ liệu vào Q1 và Q2 sao cho khơng vi phạm phụ thuộc hàm hình chiếu của nĩ, nhưng khi kết nối chúng lại thì dữ liệu kết quả của lược đồ quan hệ Q lại vi phạm phụ thuộc hàm CS Z 112
  36. Q1(F)={PTHHN} Q2(F)={Z C, Z ZC} F={CS Z,Z C} Q1 (S Z) Q2 (C Z) Q (C S Z) s1 z1 c1 z1 c1 s1 z1 s1 z2 c1 z2 c1 s1 z2 6.2.2.4 Thuật tốn kiểm tra bảo tồn phụ thuộc hàm Thuật tốn tìm bao đĩng của tập thuộc tính X đối với G =  Qi(F) Vào: =(Q1,Q2, ,Qk),F,X + Ra: XG Bước 1: Với mỗi phụ thuộc hàm X Y F ta thực hiện từ bước 2 đến bước 4 Bước 2: đặt Z’ = X + Bước 3: thế Z’ = Z’((Z’Qi )  ) Bước 4: nếu ở Qi, Z’thay đổi thì thực hiện lại bước 3 cho Qđầu tiên + Ngược lại kết thúc thuật tốn và trả về Z’(là bao đĩng XG ) Thuật tốn kiểm tra bảo tồn phụ thuộc hàm Vào: =(Q1,Q2, ,Qk),F Ra: kết luận phép tách bảo tồn hay khơng bảo tồn phụ thuộc hàm Bước 1: Với mỗi phụ thuộc hàm X Y F ta thực hiện từ bước 2 đến bước 3: + Bước 2: Tìm bao đĩng XG với G =  Qi(F) + + Bước 3: Nếu Y  XG thì X Y Qi(F) + Bước 4: Nếu tất cả phụ thuộc X Y F đều thuộc Qi(F) thì ta kết luận phân rã bảo tồn phụ thuộc hàm ngược lại khơng bảo tồn phụ hàm Ví dụ 14: thực hiện lại ví dụ 13, nghĩa là kiểm tra phép tách cĩ bảo tồn phụ thuộc hàm khơng? Vào: Q(C,S,Z),F={CS Z,Z C},Q1(S,Z) và Q2(C,Z) + Đương nhiên Z C G = Q1(F)Q2(F) Z C (Q1(F)Q2(F)) 1. Z’=CS + 2. gán Z’= Z’((Z’Q1 )  ): Z’ = CS(SSZ)=CS Bước 1 và 2 cĩ Z’ khơng thay đổi, ta sang lược đồ Q2 và tính tiếp Z’ + 3. gán Z’= Z’((Z’Q2 )  ): Z’ = CS(CCZ)=CS Z’khơng thay đổi và hết lược đồ quan hệ ngưng khơng tính tiếp Z’ + 4. Vậy CSG =CS CS Z (Q1(F)  Q2(F)) phép phân rã khơng bảo tồn phụ thuộc hàm. Ví dụ 15: thực hiện lại ví dụ 12 với nội dung kết luận phép tách cĩ bảo tồn phụ thuộc hàm khơng (khơng tính F+) Vào: Q(A,B,C),F={A B,B C,C A},Q1(A,B) và Q2(B,C) Hiển nhiên G = Q1(F)  Q2(F)  {A B,B C} 113
  37. + Ta xác định C A cĩ thuộc (Q1(F)  Q2(F)) 1. Z’=C + 2. gán Z’= Z’((Z’Q1 )  ): Z’ = C(AB)=C Bước 1 và 2 cĩ Z’ khơng thay đổi, ta sang lược đồ Q2 và tính tiếp Z’ + 3. gán Z’= Z’((Z’Q2 )  ): Z’ = C(ABCBC)=BC Z’thay đổi tính tiếp Z’bắt đầu từ lược đồ Q1 4. gán Z’= Z’((Z’ )+  ): Z’ = BC(ABCAB)=ABC do Z’=Q+ Z’ sẽ khơng bao giờ thay đổi. + 5. vậy CG =ABC C A (Q1(F)  Q2(F)) phép phân rã bảo tồn phụ thuộc hàm. 6.3 THIẾT KẾ CSDL BẰNG CÁCH PHÂN RÃ 6.3.1 Phân rã thành dạng chuẩn BC (hay chuẩn 3) bảo tồn thơng tin 6.3.1.1 Cách thơng thường Thuật tốn phân rã Q,F thành dạng chuẩn BC (hay chuẩn 3) bảo tồn thơng tin Bước 1:Tìm tất cả khĩa của Q Bước 2:Tìm phụ thuộc hàm X Y F cĩ X khơng là siêu khĩa và Y khơng chứa thuộc tính khĩa. Nếu tìm thấy thì tách Q thành Q1 và Q2 theo quy tắc sau: Q1=Q[XY]; F1Q1(F)tìm bao đĩng của tất cả tập con của XY để suy ra Q1(F) F1 + + Q2=Q[Q -Y] F2Q2(F)tìm bao đĩng của tất cả tập con của Q -Y để suy ra Q2(F) F2 thực hiện thuật tốn phân rã (Q1,F1) thực hiện thuật tốn phân rã (Q2,F2) Ngược lại nếu khơng tìm thấy thì cĩ hai trường hợp: Trường hợp 1: mọi phụ thuộc hàm trong Fi đều cĩvế trái là siêu khĩa thì Qi đạt chuẩn BC Trường hợp 2: nếu cĩ phụ thuộc hàm cĩ vế trái khơng là siêu khĩa và vế phải là thuộc tính khĩa thì Qi đạt chuẩn 3. Ví dụ 16: cho Q(S,D,I,M) F={SI D;SD M} hãy phân rã Q thành các lược đồ con đạt chuẩn BC bảo tồn thơng tin Giải: Bước 1: tìm tất cả khĩa của Q + Xi TNXi (TNXi) Siêu khĩa Khĩa  SI SDIM SI SI D SID SDIM SID Bước 2: phụ thuộc hàm SD M F cĩ SD khơng là siêu khĩa. 114
  38. F={SI->D;SD->M} Q(S,D,I,M) K = SI F1={SD->M} F2={SI->D} Q1(SDM) Q2(SDI) K1 = SD K2 = SI Chú ý: để tính được F1,F2,K1,K2 như hình trên, ta phải tính bao đĩng của tất cả tập con của{SDM} và {SDI} F1,F2 rồi tìm tất cả khĩa của Q1 và Q2. S+=S D+ =D M+ =M S+=S D+ =D I+ =I SD =SD SM+ =SM SD =SD SI+ =SDIM + M + M DM+ =DM DI+ =DI SDM =SD SDI+ =SDIM + M + F1 =Q1(F)={SD M,SD SM,SD DM,SD SDM}{SD M}= F1 + F2 =Q2(F)={SI D,SI SD,SI DI,SI SDI}{SI D}= F2 Q1 và Q2 đều đạt dạng chuẩn BC vì trong Qi chỉ cĩ phụ thuộc hàm cĩ vế trái là khĩa. F1 được tạo thành bằng cách lấy các phụ thuộc hàm của Q1(F)cĩ vế phải một thuộc tính. Tương tự cho F2 Ví dụ 17: cho Q(CTHRSG), F={C T;HR C;HT R;CS G;HS R} hãy phân rã Q thành các lược đồ con đạt chuẩn BC bảo tồn thơng tin. (giải như ví dụ trên) F={C->T;HR->C;HT->R;CS->G;HS->R} Q(CTHRSG) K = HS F1={C->T} F12={HR->C;CS->G;HS->CRG;CH->R} Q1(CT) Q12(CHRSG) K1 = C K12 = HS F2={HR->C;CH->R} F22={HS->RG} Q2(CHR) Q22(HRSG) K211=HR; K212=CH K22 = HS Tính chất: Theo thuật tốn trên, khi phân rã Q thành Q1(XY)với X Y và Q2 thì tập khĩa SQ của Q luơn luơn bằng với tập khĩa SQ2 của Q2. Chứng minh 115
  39. Thật vậy, K là một khĩa của Q K là một siêu khĩa của Q2. Giả sử cĩ K’ K và K’ là khĩa + + của Q2 K’ (Q -Y) mà X Y K’ Q . Điều này mâu thuẫn với K là khĩa của Q K là khĩa của Q2. Ngược lại cũng đúng. Dựa vào tính chất trên, ta cải tiến thuật tốn phân rã nhằm giảm bớt khối lượng tính các phụ thuộc hàm của tập F+ Thuật tốn phân rã Q,F thành dạng chuẩn BC (hay chuẩn 3) bảo tồn thơng tin Bước 1: Tìm tập tất cả khĩa SK của Q Bước 2: Tìm phụ thuộc hàm X Y F cĩ X khơng là siêu khĩa và Y khơng chứa thuộc tính khĩa. Nếu tìm thấy thì tách Q thành Q1 và Q2 theo quy tắc sau: Q1=Q[XY]; Tính F1 bằng cách tính bao đĩng tất cả tập con của XY + Q2=Q[Q -Y] SK cũng là tập khĩa của Q2 thực hiện bước 1 cho Q1 thực hiện bước 2 cho Q2 Ngược lại nếu khơng tìm thấy thì cĩ hai trường hợp: Trường hợp 1: mọi phụ thuộc hàm trong Fi đều cĩvế trái là siêu khĩa thì Qi đạt chuẩn BC Trường hợp 2: nếu cĩ phụ thuộc hàm cĩ vế trái khơng là siêu khĩa và vế phải là thuộc tính khĩa thì Qi đạt chuẩn 3. Chú ý: Thuật tốn này chỉ tiện trong trường hợp khối lượng tính tốn trong việc tìm tất cả khĩa của lược đồ quan hệ Q khơng lớn. Nĩi cách khác tập trung gian TG cĩ ít thuộc tính. Ngược lại ta phải dùng thuật tốn của phần tiếp theo. Ví dụ 18: phân rã lược đồ ở ví dụ trên thành các lược đồ con ở dạng chuẩn BC bảo tồn thơng tin. F={C->T;HR->C;HT->R;CS->G;HS->R} Q(C,T,H,R,S,G) K = HS Tính F1, K1 F1={C->T} F12={HR->C;CS->G;HS->R; } Q1(C,T) Q12(C,H,R,S,G) K1 = C K12 = HS Tính F3,K3 Tính F2,K F2={HR->C;CH->R} F3={HS->RG} Q2(C,H,R) Q3(H,R,S,G) K =HR; K = CH K = HS 21 22 3 Trong F cĩ 4 phụ thuộc hàm C T,HR C,HT R,CS G làm Q khơng đạt dạng chuẩn 3 hay BC và phép phân rã trên đã chọn ngẫu nhiên phụ thuộc hàm C T để phân rã thành Q1 và tập thuộc tính của Q12 chính là tập thuộc tính của Q bỏ thuộc tính T.Tập phụ thuộc hàm F12 sẽ chứa các phụ thuộc hàm của F bỏ đi các phụ thuộc hàm cĩ vế trái hay vế phải chứa thuộc tính T. Như vậy tùy theo cách chọn phụ thuộc hàm để phân rã thành Q1 mà số lượng 116
  40. phụ thuộc hàm mang xuống Q12 khác nhau và chất lượng phân rã cũng khác nhau. Kết quả của phép phân rã trên chính là Q1, Q2, Q3 của hình trên. Phép phân rã bảo tồn thơng tin, và các lược đồ con đạt chuẩn BC nhưng phép phân rã khơng bảo tồn phụ thuộc hàm vì G = + F1  F2  F3 = {C T; HR C; CH R; HS RG} khơng tương đương với F (HT R G và CS G G+). Ta hãy xem phép phân rã sau sẽ cho kết quả tốt hơn. F={C->T;HR->C;HT->R;CS->G;HS->R} Q(C,T,H,R,S,G) K = HS Tính F1, K1 F1={CS->G} F12={C->T,HR->C;HT->R;HS->R; } Q1(C,S,G) Q12(C,T,H,R,S) K = CS K = HS 1 12 bỏ C hay R trong Q12 để được Q22 giả sử bỏ R Tính F2,K F2={HR->C;CH->R} F22={C->T,HS->R, } Q2(C,H,R) Q22(C,T,H,S) K21=HR; K22= CH K22 = HS Tính F3,K3 F3={C->T} F4={HS->C} Q3(C,T) Q4(C,H,S) K = C K = HS 3 4 Phép phân rã cũng cho kết quả phép phân rã bảo tồn thơng tin, các lược đồ con Q1,Q2,Q3,Q4 đạt chuẩn BC và phép phân rã khơng bảo tồn phụ thuộc hàm vì G = F1  F2  F3  F4 ={CS G;HR C;CH R;C T;HS C} khơng tương đương với F (HT R G+).Phép phân rã này tốt hơn vì chỉ cĩ một phụ thuộc hàm HT R khơng thuộc G+ trong khi phép phân rã trên cĩ tới 2 phụ thuộc hàm HT R và CS G khơng thuộc G+.Sở dĩ phép phân rã thứ 2 tốt hơn vì ở bước chọn phụ thuộc hàm để phân rã thành Q1 phép phân rã đã chọn phụ thuộc hàm sao cho khi chiếu F xuống Q12 số phụ thuộc hàm mang xuống càng nhiều càng tốt. Ví dụ 19: cho Q(A,B,C,D,E,G), F={AE C;CG A;BD G;GA E} hãy phân rã Q thành các lược đồ con đạt chuẩn BC bảo tồn thơng tin. F={AE->C;CG->A;BD->G;GA->E} Q(A,B,C,D,E,G) K1 = ABD; K2=BCD Tính F12 Tính F1, K11 F1={BD->G} F12={ABD->E,BCD->A,AE->C} Q1(B,D,G) Q12(A,B,C,D,E) K11 = BD K1 = ABD; K2=BCD Tính F3,K3 Tính F2,K F2={AE->C} F3={ABD->E} Q2(A,C,E) Q3(A,B,D,E) K =AE K = ABD 21 3 Nếu Q được phân rã thành: (Q1(BDG), Q2(A,B,C,D,E)) lược đồ cơ sở dữ liệu đạt chuẩn 3 117
  41. (Q1(BDG), Q2(A,C,E), Q3(A,B,D,E)) lược đồ cơ sở dữ liệu đạt chuẩn BC 6.3.1.2 Bổ đề: Nếu Q khơng ở dạng chuẩn BC thì cĩ thuộc tính A,B thuộc Q+ sao cho (Q+-AB) A Chứng minh: Q khơng ở dạng chuẩn BC cĩ X A sao cho X khơng là siêu khĩa cĩ thuộc tính B XA (vì nếu khơng cĩ B XA thì X phải là siêu khĩa) (Q+-AB)  X (Q+-AB) A Nhận xét: + Một lược đồ Q ở dạng chuẩn BC vẫn cĩ thể cĩ AB sao cho (Q+-AB) A + Một lược đồ Q khơng cĩ AB sao cho (Q+-AB) A thì Q ở dạng chuẩn BC 6.3.1.3 Thuật tốn Thuật tốn phân rã sau khơng cần tìm tất cả khĩa của lược đồ quan hệ Q Thuật Tốn phân rã Q, F thành dạng chuẩn BC bảo tồn thơng tin Bước 1: Z’ = Q+ Bước 2: phân rã Z’ theo thuật tốn chi tiết để được 2 lược đồ Z’-A và XA trong đĩ XA ở dạng chuẩn BC và X A Nếu thuật tốn chi tiết cho kết quả thì qua bước 3 Ngược lại kết thúc thuật tốn Bước 3: nhận XA là một lược đồ con của các lược đồ kết quả Q1, ,Qk Bước 4: thực hiện phân rã Z’-A,F Thuật tốn chi tiết Bước 1: nếu Z’ khơng chứa AB sao cho (Z’-AB) A. thì báo khơng phân rã được. Ngược lại qua bước 2 Bước 2: đặt Y’ = Z’ Bước 3: nếu Y’ chứa AB sao cho (Y’-AB) A. thì gán Y’ = Y’–B thực hiện lại bước 2 Bước 4: bước 3 cho kết quả Y’ = XA với XA ở dạng chuẩn BC và X A. Trả về XA Nhận xét + + Ở mỗi bước 2 của thuật tốn phân rã Q,F ta thu được 2 lược đồ Qi =Z’-A,Q1 =XA với + + + Qi Q1 = (Z’-A)XA = X và X Q1 và Q1 là lược đồ ở dạng chuẩn BC. Thuật tốn lại tiếp tục phân rã Qi theo đúng cách đã làm thuật tốn phân rã bảo tồn thơng tin và các lược đồ con Qi đạt dạng chuẩn BC. + Thuật tốn chi tiết tìm Ql đạt chuẩn BC sao cho Ql chứa nhiều thuộc tính nhất. Để + + tìm được Ql như vậy thuật tốn chi tiết tìm hai thuộc tính AB Q sao cho (Q - AB) A. Nếu tìm thấy chứng tỏ Q chưa đạt chuẩn BC và thuật tốn giảm B trong Q + với hy vọng thu được lược đồ con Ql đạt chuẩn BC và thỏa phụ thuộc hàm (Q - 118
  42. AB) A. Thuật tốn chi tiết tiếp tục tìm và giảm cho tới khi thu được lược đồ con + khơng cĩ hai thuộc tính AB sao cho (Q -AB) A Ql là lược đồ con đạt chuẩn BC cần tìm. Ví dụ 19: Cho quan hệ Q(B,O,S,Q,I,D) và tập phụ thuộc hàm F F = {S D, I B IS Q B O} Hãy phân rã Q thành các lược đồ con đạt dạng chuẩn BC và bảo tồn thơng tin. Giải Đặt Z’= Q+= BOSQID Thực hiện thuật tốn chi tiết Y’= BOSQID Chọn 2 thuộc tính . Tìm bao đĩng của tập hợp thuộc tính cịn lại. Nếu bao đĩng chứa 1 trong 2 thuộc tính chọn chẳng hạn A, nghĩa là ta đã tìm được 2 thuộc tính AB sao cho (Y’-AB) A Chọn BO:(SQID)+  B Giảm O trong Y’ ta được Y’= BSQID Chọn BS:(QID)+  B Giảm S trong Y’ ta được Y’= BQID Chọn BQ:(ID)+  B Giảm Q trong Y’ ta được Y’= BID Chọn BD: I+  B Giảm D trong Y’ ta được Y’= BI Q1=(BI) và F1={I B} Để tính F1 ta phải tính bao đĩng của tất cả tập con của {BI} F1 Giảm B trong Z’ ta được Z’= OSQID Đặt Y’=OSQID Chọn OD: (SQI)+  D; Giảm O trong Y’ ta được Y’= SQID chọn QD: (SI)+  D giảm Q trong Y’ ta được Y’= SID chọn ID: S+  D; giảm I trong Y’ ta được Y’= SD Q2=(SD) và F2={S D} Để tính F2 ta phải tính bao đĩng của tất cả tập con của {SD} F2 Giảm D trong Z’ ta được Z’= OSQI Đặt Y’=OSQI chọn OQ: (SI)+  Q giảm O trong Y’ ta được Y’= SQI Q3=(SQI) và F3={SI Q} 119
  43. Ở bước trên khơng chọn AB để bao đĩng tập hợp thuộc tính cịn lại chứa A hay B Để tính F3 ta phải tính bao đĩng của tất cả tập con của {SQI} F3 Giảm Q trong Z’ ta được Z’= OSI Đặt Y’=OSI Chọn OS: I+=IBO  O giảm S trong Y’ ta được Y’= OI Q4=(OI) và F4={I O} Giảm O trong Z’ ta được Z’= SI Q5=(SI)và F5={PTHHN} Ta cĩ thể hiểu Q3(SQI)là tổ hợp của 2 lược đồ con Q5(SI) và Q3(SQI) Vậy kết quả phân rã là: 1:Q1(BI) F1={I B} 2:Q2(SD) F2={S D} 3:Q3(SQI) F3={SI Q} 4:Q4(OI) F4={I O} 6.3.1.4 Chú ý + Nên tránh phân rã nếu lược đồ đã ở dạng chuẩn mong muốn. + Nên xem xét tổ hợp các lược đồ quan hệ con thành lược đồ lớn hơn nếu lược đồ lớn hơn vẫn đạt dạng chuẩn mong muốn. + Một kết quả phân rã bảo tồn phụ thuộc hàm sẽ cĩ giá trị hơn kết quả phân rã khơng bảo tồn phụ thuộc hàm. Giữa hai kết quả phân rã đều khơng bảo tồn phụ thuộc hàm thì kết quả phân rã thỏa nhiều phụ thuộc hàm trong F sẽ cĩ giá trị hơn . + Khơng cĩ thuật tốn phân rã lược đồ Q thành các lược đồ con ở dạng chuẩn BC vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm. + Vẫn cĩ lược đồ Q được phân rã thành các lược đồ con ở dạng chuẩn BC vừa bảo tồn thơng tin vưa bảo tồn phụ thuộc hàm. Ví dụ 20: cho lược đồ Q(CSZ) cĩ F={CS Z,Z C}. Q khơng thể phân rã thành các lược đồ con ở dạng chuẩn BC vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm. Thật vậy: TN={S} TG={CZ} Tất cả khĩa của Q là: + Xi TNXi (TNXi) siêu Khĩ khĩa a  S S Z SZ SZC SZ SZ C SC SZC SC SC Z SZC SZC SZC C 120
  44. Vậy Q đạt dạng chuẩn 3 nhưng khơng ở dạng chuẩn BC vì cĩ Z C cĩ vế trái khơng là siêu khĩa. Nhưng nếu ta phân rã Q thành các lược đồ con cĩ ít hơn 3 thuộc tính thì phụ thuộc CS Z khơng suy ra được từ các phụ thuộc hình chiếu. 6.3.2 Phân rã thành dạng chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm Thuật Tốn phân rã Q, F thành dạng chuẩn 3, bảo tồn thơng tin, bảo tồn phụ thuộc hàm Dữ liệu vào: lược đồ quan hệ Q và tập phụ thuộc hàm F. Dữ liệu ra: một phân rã sao cho mỗi lược đồ quan hệ con đều đạt chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm. Tìm phủ tối thiểu Ftt của F Nếu cĩ một phụ thuộc hàm nào của Ftt mà liên quan đến tất cả các thuộc tính của Q thì kết quả phân rã chính là Q ( Q khơng thể phân rã) Nếu cĩ những thuộc tính của Q khơng nằm trong một phụ thuộc nào của Ftt - dù ở vế phải hay vế trái của F thì chúng tạo thành một lược đồ cần tìm. Cứ mỗi phụ thuộc hàm X A Ftt thì XA là một lược đồ cần tìm Nếu cĩ một lược đồ con chứa khĩa K của Q thì kết thúc thuật tốn Ngược lại tạo một lược đồ con K Ví dụ 21: cho lược đồ Q(CTHRSG),F={C T,HR C,TH R,CS G,HS R}.Hãy phân rã Q thành các lược đồ con đạt dạng chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm. Gỉai: + F=Ftt={C T,HR C,TH R,CS G,HS R} là phủ tối thiểu. + Áp dụng thuật tốn trên Q được phân rã thành các lược đồ con Q1(CT),Q2(HRC),Q3(THR),Q4(CSG),Q5(HSR) + Khĩa của Q + Xi TNXi (TNXi) siêu Khĩa khĩa  HS CTHRSG HS HS C HSC CTHRSG HSC T HST CTHRSG HST CT HSCT CTHRSG HSCT R HSR CTHRSG HSR CR HSCR CTHRSG HSCR TR HSTR CTHRSG HSTR CTR HSCTR CTHRSG HSCTR + Q5 chứa khĩa của Q nên Q1,Q2,Q3,Q4,Q5 là kết quả của phân rã. 121
  45. Định lý: Thuật tốn trên tạo ra một phân rã ở dạng chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc hàm Chứng minh: 1. Nếu Ftt cĩ phụ thuộc hàm fi liên quan đến tất cả thuộc tính thì Q đạt chuẩn 3. Thật vậy: fi Ftt fi là phụ thuộc hàm cĩ vế phải 1 thuộc tính fi cĩ dạng K A K là siêu khĩa. Nếu khĩa của Q là K’ K thì ta cĩ K’ A K A là phụ thuộc hàm cĩ vế trái dư thừa điều này mâu thuẫn với fi Ftt. Vậy K là khĩa của Q nếu Q cĩ thuộc tính khơng khĩa thì A là thuộc tính khơng khĩa duy nhất của Q và mọi phụ thuộc hàm cĩ vế phải là A phải cĩ vế trái là K lược đồ quan hệ Q khơng cĩ phụ thuộc hàm cĩ vế trái khơng là siêu khĩa và vế phải khơng là thuộc tính khĩa Q đạt chuẩn 3. 2. Nếu lược đồ Q’(W) gồm các thuộc tính khơng xuất hiện trong Ftt thì Q đạt chuẩn 3. Thật vậy: V là tập con bất kỳ của W ta cĩ V+=V F’ của Q’ chỉ gồm các phụ thuộc hàm hiển nhiên trong F’ khơng cĩ phụ thuộc hàm cĩ vế trái khơng là siêu khĩa và vế phải là thuộc tính khơng khĩa Q’ đạt chuẩn 3. 3. Ta chứng minh mỗi lược đồ con ở dạng chuẩn 3. Thật vậy: Theo thuật tốn thì mỗi lược đồ con Qi cĩ dạng YB với Y B Y là siêu khĩa. Giả sử trong Qi cĩ phụ thuộc hàm X A cĩ vế trái khơng là siêu khĩa và vế phải khơng là thuộc tính khĩa. Ta phân làm hai trường hợp: Trường hợp 1: A=B X B X  Y Y B là phụ thuộc cĩ vế trái dư thừa, điều này trái với Y B là phụ thuộc hàm trong phủ tối thiểu. Trường hợp 2: A B A Y (1). Gọi K là khĩa của Qi K  Y (2). A là thuộc tính khơng khĩa nên A K (3).(1)(2)(3) K  Y (4).K là khĩa nên K B Y B là phụ thuộc hàm cĩ vế trái dư thừa. Điều này trái với điều phụ thuộc hàm Y B là phụ thuộc hàm của phủ tối thiểu Ftt 4. Ta chứng minh phép phân rã bảo tồn phụ thuộc hàm. Thật vậy: + + Hiển nhiên Ftt  G = Qi(Ftt) Ftt  G (1) + ++ + + + Hơn nữa Ftt  G = Qi(Ftt) Ftt  G Ftt  G (2) + + (1)và (2) Ftt = G 5. Ta chứng minh phép phân rã bảo tồn thơng tin. Thật vậy: Lập bảng kiểm tra bảo tồn thơng tin. Ta lần lượt đồng nhất các giá trị của bảng trên theo các phụ thuộc hàm được phát hiện ở mỗi bước của thuật tốn tìm bao đĩng của tập + + thuộc tính Qi với Qi chứa khĩa K của lược đồ Q. Phụ thuộc hàm đầu tiên được phát + + hiện là Y Aj Ftt sao cho Qi Y và Aj Qi .Ở dịng của lược đồ Ql(YAj) cĩ giá trị aj ở cột Aj nên khi làm bằng giá trị kết quả là ở cột Aj của dịng cĩ lược Qi cĩ thêm giá trị aj. Tiếp tục cho các phụ thuộc hàm phát hiện tiếp theo ta sẽ cĩ thêm các giá trị a ở các cột khác của dịng Qi. Do Qi chứa khĩa nên các giá trị a mới thêm vào của dịng Qi sẽ xuất hiện ở tất cả các thuộc tính của lược đồ Q. Suy ra hàng của lược đồ Qi sẽ chứa tồn a là điều 122
  46. phải chứng minh. Để làm sáng tỏ ý tưởng của phần chứng minh này ta xét trường hợp cụ thể của ví dụ 21 : Bước 1: ta lập bảng kiểm tra bảo tồn thơng tin: C T H R S G Q1(CT) a1 a2 Q2(HRC) a1 a3 a4 Q3(THR) a2 a3 a4 Q4(CSG) a1 a5 a6 Q5(HSR) a3 a4 a5 Bước 2:Ta chứng minh dịng Q5 của bảng trên sẽ chứa tồn giá trị a. Thật vậy: ta lần lượt đồng nhất các giá trị của bảng trên theo các phụ thuộc hàm được phát hiện theo thuật tốn tìm bao đĩng của X={HSR}  K; F={C T,HR C,TH R,CS G,HS R} X0=HSR X1=HSRC do HR C. Đồng nhất các giá trị theo phụ thuộc hàm này. Trên dịng Q2 ở cột C chứa giá trị a nên trên dịng Q5 sẽ cĩ thêm giá trị a ở cột C X2=HSRCT do C T. Đồng nhất các giá trị theo phụ thuộc hàm này. X3=HSRCTG do CS G đồng nhất các giá trị theo phụ thuộc hàm này. C T H R S G Q1(CT) a1 a2 Q2(HRC) a1 a2 a3 a4 Q3(THR) a1 a2 a3 a4 Q4(CSG) a1 a2 a5 a6 Q5(HSR) a1 a2 a3 a4 a5 a6 + + Do X =Q nên dịng Q5 chứa tồn giá trị a Ví dụ 22: Cho Q(ABCDEGH), F={AB D; EH G; G C; D C} hãy phân rã Q thành các lược đồ con ở dạng chuẩn 3 vừa bảo tồn thơng tin vừa bảo tồn phụ thuộc. Giải: Tìm phủ tối thiểu Ftt của F Ftt=F={AB D; EH G; G C; D C} Áp dụng thuật tốn, Q được phân rã thành lược đồ CSDL sau: Q1{ABD), Q2(EHG), Q3(GC), Q4(DC) Tìm khĩa của Q TN={ABEH} TG={GD} 123
  47. + Xi TN Xi (TN Xi) Siêu khĩa Khĩa  ABEH ABCDEGH ABEH ABE H G ABEHG ABCDEGH ABEHG D ABEHD ABCDEGH ABEHD G ABEHG ABCDEGH ABEHGD D D Q1,Q2,Q3,Q4 khơng chứa khĩa để bảo tồn thơng tin ta cần cĩ Q5(A,B,E,H).Vậy kết quả của phân rã là Q1,Q2,Q3,Q4,Q5 BÀI TẬP 1/ Cho biết dạng chuẩn của các lược đồ quan hệ sau: a) Q(ABCDEG); F={A BC, C DE, E G} b) Q(ABCDEGH); F={C AB, D E, B G} c) Q(ABCDEGH) F={A BC, D E, H G} d) Q(ABCDEG); F={AB C, C B, ABD E, G A} e) Q(ABCDEGHI); F={AC B,BI ACD,ABC D,H I,ACE BCG,CG AE} 2/ Kiểm tra sự bảo tồn thơng tin ? Q(ABCDE) R1(AD);R2(AB);R3(BE); R4(CDE);R5(AE) F={A C; B C;C D;DE C;CE A} 3/ Cho lược đồ quan hệ Q(A,B,C,D) và tập phụ thuộc hàm F = {A B;B C;A D;D C} Và một lược đồ CSDL như sau: C ={Q1(AB);Q2(AC);Q3(BD)} a) C cĩ bảo tồn thơng tin đối với F b) C cĩ bảo tồn phụ thuộc hàm ? 4/ Kiểm tra dạng chuẩn Q(C,S,Z) F={CS Z;Z C} 5/ Phân rã Q(G,H,A,B,C,D) F={GH AD;AG B;CD GH; C A; BH C} 6/ Cho lược đồ CSDL Kehoach(NGAY,GIO,PHONG,MONHOC,GIAOVIEN) F={NGAY,GIO,PHONG MONHOC MONHOC,NGAY GIAOVIEN NGAY,GIO,PHONG GIAOVIEN MONHOC GIAOVIEN} a) Xác định dạng chuẩn cao nhất của Kehoach b) Nếu Kehoach chưa đạt dạng chuẩn 3, hãy phân rã Kehoach thành lược đồ CSDL dạng chuẩn 3 vừa bảo tồn phụ thuộc hàm vừa bảo tồn thơng tin. 124
  48. c) Nếu Kehoach chưa đạt dạng chuẩn BC, hãy phân rã KeHoach thành lược đồ CSDL dạng BC 7/ Cho lược đồ quan hệ Q(A,B,C,D) và tập phụ thuộc hàm F F = {A B;B C; D B} C = {Q1(A,C,D); Q2(B,D)} a) Xác định các Fi (những phụ thuộc hàm F được bao trong Qi) b) Lược đồ CSDL C cĩ đạt dạng chuẩn BC ? Nếu khơng cĩ thể phân rã tiếp các Qi của C để biến C thành dạng chuẩn BC ? 8/ Giả sử ta cĩ lược đồ quan hệ Q(C,D,E,G,H,K) và tập phụ thuộc hàm F như sau; F = {CK H; C D; E C; E G; CK E} a) Từ tập F, hãy chứng minh EK DH b) Tìm tất cả các khĩa của Q. c) Xác định dạng chuẩn của Q. d) Hãy tìm cách phân rã Q thành một lược đồ CSDL đạt dạng chuẩn BC (hoặc dạng chuẩn 3). tìm tập phụ thuộc hàm và khĩa cho mỗi lược đồ quan hệ con. 9/ Cho lược đồ quan hệ Q(S,I,D,M) F = {f1:SI DM; f2:SD M; f3:D M} a) Tính bao đĩng D+, SD+, SI+ b) Tìm tất cả các khĩa của Q c) Tìm phủ tối thiểu của F d) Xác định dạng chuẩn cao nhất của Q e) Nếu Q chưa đạt dạng chuẩn 3, hãy phân rã Q thành lược đồ CSDL dạng chuẩn 3 vừa bảo tồn phụ thuộc hàm vừa bảo tồn thơng tin. f) Nếu Q chưa đạt dạng chuẩn BCNF, hãy phân rã Q thành lược đồ CSDL dạng BCNF g) Kiểm tra phép tách Q thành các lược đồ con (SID,SIM) cĩ bảo tồn thơng tin ? h) Kiểm tra phép tách Q thành các lược đồ con (SID,SIM) cĩ bảo tồn phụ thuộc hàm ? 10/ Cho lược đồ quan hệ R(W,A,Z,Y,Q,P) R1(A,Z); R2(W,Y,Q,P) R3(Y,Q,P,A) F = {W AYQP, A Z, YQP A} Hãy kiểm tra tính kết nối khơng mất thơng tin. 11/ Cho lược đồ quan hệ Q(Mơn, GiảngViên,Giờ giảng, Phịng, SinhViên, Hạng) với F ={M GV; G,P M; G,GV P; M,SV H; G,SV P} C = {Q1(M,G,P); Q2(M,GV);Q3( M,SV,H)} Kiểm tra xem lược đồ cơ sở dữ liệu sau đây cĩ bảo tồn thơng tin đối với F ? 12/ Kiểm Tra Dang Chuẩn a) Q(A,B,C,D) F={CA D; A B} 125
  49. b) Q(S,D,I,M) F={SI D;SD M} c) Q(N,G,P,M,GV) F={N,G,P M;M GV} d) Q(S,N,D,T,X) F={S N; S D; S T; S X} 13/ Phân rã lược đồ thành dạng BCK a) Q(S,D,I,M) F={S,I D;S,D M} b) Q(A,B,C,D) F={A B;B C;D B} c) Q(C,S,Z) F={C,S Z; Z C} 14/ Phân rã lược đồ thành dạng 3NF vừa bảo tồn phụ thuộc hàm vừa bảo tồn thơng tin a) Q(A,B,C), F={A B;A C;B A;C A;B C} b) Q(MSCD,MSSV,CD,HG) F={MSCD CD; CD MSCD; CD,MSSV HG; MSCD,HG MSSV; CD,HG MSSV; MSCD,MSSV HG} c) Q(A,B,C,D) F={ AB C; C B} CÁC THUẬT NGỮ CHUYÊN MƠN 1. Database: Cơ sở dữ liệu 2. Server: Máy chủ 3. Client: Máy khách TÀI LIỆU THAM KHẢO 1. Giáo trình SQL Server – Trần Nguyên Phong – Trường Đại học Khoa học Huế 2. Nhập mơn cơ sở dữ liệu - Lê Tiến Vương - NXB Thống kê Hà nội, 2000. Tái bản lần 5. 3. ASP.NET- Kỹ thuật và ứng dụng – Nhà xuất bản thống kê 2002. 4. Nguyễn An Tế, Giáo trình Nhập mơn Cơ sở dữ liệu, Khoa Cơng nghệ Thơng tin, Đại học KHTN, Đại học quốc gia TP.HCM 1996. Chương 1 5. Đỗ Trung Tuấn, Cơ sở dữ liệu (DataBase), NXB Giáo dục Hà nội, 1998. 6. Lê Tiến Vương. Nhập mơn cơ sở dữ liệu. NXB Thống kê Hà nội, 2000. Tái bản lần 5. 126