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

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

1. BỘ LAO ĐỘNG - THƯƠNG BINH VÀ XÃ HỘI TỔNG CỤC DẠY NGHỀ Dự án giáo dục kỹ thuật và dạy nghề (VTEP) GIÁO TRÌNH Mơn học: CƠ SỞ DỮ LIỆU NÂNG CAO Mã số: ITPGR3_02 NGHỀ: LẬP TRÌNH MÁY TÍNH Trình độ : Cao đẳng nghề NĂM 2012
2. Tuyên bố bản quyền : Tài liệu này thuộc loại sách giáo trình Cho nên các nguồn thơng tin cĩ thể được phép dùng nguyên bản hoặc trích dùng cho các mục đích về đào tạo và tham khảo . Mọi mục đích khác cĩ ý đồ lệch lạc hoặc sử dụng với mục đích kinh doanh thiếu lành mạnh sẽ bị nghiêm cấm. Tổng Cục Dạy nghề sẽ làm mọi cách để bảo vệ bản quyền của mình. Tổng Cục Dạy Nghề cám ơn và hoan nghênh các thơng tin giúp cho việc tu sửa và hồn thiện tốt hơn tài liệu này. Địa chỉ liên hệ: Dự án giáo dục kỹ thuật và nghề nghiệp Tiểu Ban Phát triển Chương trình Học liệu 2
3. LỜI TỰA Đây là tài liệu được xây dựng theo chương trình của dự án giáo dục kỹ thuật và dạy nghề, để cĩ đươc giáo trình này dự án đã tiến hành theo hai giai đoạn. Giai đoạn 1 : Xây dựng chương trình theo phương pháp DACUM, kết quả của gian đoạn này là bộ khung chương trình gồm 230 trang cấp độ 2 và 170 trang cấp độ 3. Giai đoạn 2 : 29 giáo trình và 29 tài liệu hướng dẫn giáo viên cho nghề lập trình máy tính 2 cấp độ. Để cĩ được khung chương trình chúng tơi đã mời các giáo viên, các chuyên gia đang làm việc trong lĩnh vực cơng nghệ thơng tin cùng xây dựng chương trình. Trong giai đoạn viết giáo trình chúng tơi cũng đã cĩ những sự điều chỉnh để giáo trình cĩ tính thiết thực và phù hợp hơn với sự phát triển của lĩnh vực cơng nghệ thơng tin. Cơ sở dữ liệu là một bộ mơn cơ sở cho sinh viên ngành cơng nghệ thơng tin, trong giai đoạn một các sinh viên đã được tìm hiểu về một số khái niệm về hệ thống cơ sở dữ liệu cũng như biết thiết kế một hệ thống cơ sở dữ liệu phục cho một ứng dụng cụ thể, tuy nhiên để hịa nhập được với mơi trường phát triển phần mềm chuyên nghiệp của thị trường trong nước và thế giới, sinh viên cần cĩ một hệ thống cơ sở dữ liệu chuyên nghiệp hơn, an tồn hơn và hiệu quả hơn, đĩ cũng là mục đích của mơn học này. Trong quá trình biên soạn, mặc dù đã cố gắng tham khảo nhiều tài liệu và giáo trình khác nhưng tác giả khơng khỏi tránh được những thiếu sĩt và hạn chế. Tác giả chân thành mong đợi những nhận xét, đánh giá và gĩp ý để cuốn giáo trình ngày một hồn thiện hơn. Tài liệu này được thiết kế theo từng mơ đun/ mơn học thuộc hệ thống mơ đun/mơn học của một chương trình, để đào tạo hồn chỉnh nghề Lập trình máy tính ở cấp trình độ bậc cao và được dùng làm Giáo trình cho học viên trong các khố đào tạo, cũng cĩ thể được sử dụng cho đào tạo ngắn hạn hoặc cho các cơng nhân kỹ thuật, các nhà quản lý và người sử dụng nhân lực tham khảo. Đây là tài liệu thử nghiệm sẽ được hồn chỉnh để trở thành giáo trình chính thức trong hệ thống dạy nghề. 3
4. MỤC LỤC ĐỀ MỤC TRANG 1. LỜI TỰA 3 2. MỤC LỤC 4 3. GIỚI THIỆU VỀ MƠN HỌC 6 4. CÁC HÌNH THỨC HỌC TẬP CHÍNH TRONG MƠN HỌC 10 BÀI 1 GIỚI THIỆU HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU 11 1.1 Giới thiệu hệ thống cơ sở dữ liệu 12 1.2 So sánh hệ thống tập tin và hệ quản trị cơ sở dữ liệu (DBMS). 12 1.3 Các ưu điểm của DBMS. Mơ tả và lưu trữ dữ liệu trong DBMS 14 1.4 Ngơn ngữ giao tiếp giữa người sử dụng (NSD) và CSDL 15 1.5 Cấu trúc của DBMS 16 1.6 Những người liên quan đến cơ sở dữ liệu 21 BÀI 2: MƠ HÌNH THỰC THỂ LIÊN KẾT 22 2.1 Giới thiệu mơ hình thực thể kết hợp 23 2.2 Mơ hình thực thể - kết hợp, thực thể, thuộc tính và tập thực thể. Mối kết hợp và tập mối kết hợp. 26 2.3 Các tính năng bổ sung của mơ hình thực thể kết hợp (mơ hình E-R). 27 2.4 Chuyển từ mơ hình thực thể kết hợp sang lược đồ CSDL 29 BÀI 3: MƠ HÌNH QUAN HỆ 35 3.1 Giới thiệu mơ hình quan hệ 36 3.2 Ràng buộc tồn vẹn trên các quan hệ. Hiệu lực ràng buộc tồn vẹn 37 3.3 Truy vấn dữ liệu quan hệ 44 3.6 Thiết kế cơ sở dữ liệu mức logic: chuyển sơ đồ E-R thành lược đồ cơ sở dữ liệu quan hệ 57 BÀI 4: NGƠN NGỮ TRUY VẤN SQL 61 4.1 Giới thiệu SQL 62 4.2 Dạng thức căn bản của một truy vấn SQL 63 4.2.1. Các lệnh hỏi - tìm kiếm dữ liệu: (Data Retrieval SQL) 63 4.2.2 Phép tốn UNION 73 4.3 Truy vấn lồng nhau 76 4.4 Các phép tốn gộp. Các giá trị rỗng 78 4.4.1 Các phép tốn gộp 78 4.4.2 Các giá trị rỗng 78 4.5 Giới thiệu về khung nhìn 80 4.5.1 Tạo khung nhìn 81 4.5.2 Cập nhật, bổ sung và xố dữ liệu thơng qua khung nhìn 83 4.5.3 Sửa đổi khung nhìn 86 4.5.4 Xố khung nhìn 87 BÀI 5: NGƠN NGỮ TRUY VẤN QBE 88 5.1 Giới thiệu QUERY 89 5.1.1 Chức năng 89 5.1.2 Dạng thức 89 4
5. 5.1.3 Các tốn tử 90 5.2 Thiết kế QUERY 94 5.2.1 QUERY đơn (SELECT QUERY ) 94 5.2.2 Lập tiêu chuẩn lựa chọn trong query 94 5.2.3 Sử dụng Field tính tốn trong query 98 5.2.4 Dùng dịng total để nhĩm các dịng 99 5.3 QUERY tổng hợp 103 5.3.1 Query cĩ thơng số (PARAMETER QUERY) 103 5.3.2 CROSSTAB QUERY 103 5.3.3 SUB QUERY 105 5.3.4 MAKE TABLE QUERY 106 5.3.5 UPDATE QUERY 107 5.3.6 DELETE QUERY 108 5.3.7 UNION QUERY 109 Bài 6: THIẾT KẾ CƠ SỞ DỮ LIỆU VỚI CÁC DẠNG CHUẨN 113 6.1 Dạng chuẩn của lược đồ quan hệ (normal forms for relation schemes) 114 6.1.1 Định nghĩa các dạng chuẩn 114 6.2 Phép tách kết nối bảo tồn 121 6.2.1 Phép tách kết nối bảo tồn thơng tin (lossless-join decomposition) 121 6.2.1.1 Định nghĩa phép tách Q thành 2 lược đồ con 122 6.2.1.2 Tính chất 122 6.2.1.3 Phép tách Q thành n lược đồ con 123 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 124 6.2.2 Phép tách bảo tồn phụ thuộc hàm 127 6.2.2.1 Tập phụ thuộc hàm Fi của Qi 127 6.2.2.2 Định nghĩa 127 6.2.2.3 Ý nghĩa của phân rã cĩ bảo tồn phụ thuộc hàm 130 6.2.2.4 Thuật tốn kiểm tra bảo tồn phụ thuộc hàm 131 6.3 Thiết kế csdl bằng cách phân rã 132 6.3.1 Phân rã thành dạng chuẩn BC (hay chuẩn 3) bảo tồn thơng tin 132 6.3.1.1 Cách thơng thường 132 6.3.1.2 Bổ đề 137 6.3.1.3 Thuật tốn 137 6.3.1.4 Chú ý 139 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 140 5
6. GIỚI THIỆU VỀ MƠN HỌC Vị trí, ý nghĩa, vai trị Mơn học: Đây là mơn học cơ sở và bắt buộc đối với cơng nhân lành nghề bậc cao, để học tốt mơn học này học viên cần cĩ kiến thức về Kỹ năng Tin học văn phịng, Lập trình cơ bản và Nhập mơn Cơ sở dữ liệu. Giáo trình này là một thành phần của hệ thống giáo trình của Tổng cục dạy nghề. Giáo trình được biên soạn theo chương trình chính quy cơng nhân lành nghề bậc cao ngành Cơng nghệ thơng tin. Mục tiêu của Mơn học: Sau khi học xong mơn học này này học viên cĩ khả năng: - Tự thiết kế được một cơ sở dữ liệu phục vụ cho một ứng dụng chuyên nghiệp. - Tự thiết kế được hệ thống tác nghiệp dựa vào các thơng tin phỏng vấn được từ khách hàng. Mục tiêu thực hiện của mơn học : Học xong mơn học này học viên cĩ khả năng:  Định nghĩa mơ hình dữ liệu và phân biệt sự khác nhau giữa lược đồ khái niệm, lược đồ vật lý và lược đồ ngồi. Định nghĩa sự độc lập dữ liệu và phân biệt sự khác nhau giữa độc lập dữ liệu logic và độc lập dữ liệu vật lý. Định nghĩa giao tác và giải thích sự thực hiện đồng thời của các giao tác, giao tác khơng đầy đủ và sự vỡ hệ thống.  Định nghĩa mơ hình dữ liệu thực thể - kết hợp (mơ hình E-R). Giải thích các bước phân tích yêu cầu, thiết kế cơ sở dữ liệu mức khái niệm, thiết kế cơ sở dữ liệu logic. Giải thích ràng buộc khĩa, ràng buộc tham gia, thực thể yếu, phân cấp lớp, sự gộp lại. Mơ tả một số chọn lựa nảy sinh trong quá trình thiết kế cơ sở dữ liệu mức khái niệm dùng mơ hình E-R.  Định nghĩa chính xác các thuật ngữ trong cơ sở dự liệu. Nhận biết được khi nào một ràng buộc tồn vẹn cĩ thể bị vi phạm. Biết cách chuyển đổi sơ đồ E- R thành lược đồ cơ sở dữ liệu quan hệ. Định nghĩa khung nhìn. Giải thích các vấn đề liên quan đến sự nhìn bao gồm: độc lập dữ liệu, an tồn, cập nhật.  Giải thích các khía cạnh liên quan đến ngơn ngữ SQL. Định nghĩa chốt chuyển và cơ sở dữ liệu hoạt động. Phân biệt sự khác nhau giữa ràng buộc và chốt chuyển.  Sử dụng QBE dạng căn bản để tìm thơng tin trong một quan hệ. Sử dụng QBE tìm thơng tin trong nhiều quan hệ.  Mơ tả bốn vấn đề bất thường về dữ liệu. Phát biểu và áp dụng sáu luật của hệ tiên đề Armstrong. 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. Định nghĩa dạng chuẩn và phân loại các dạng chuẩn. 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ã. 6
7. Nội dung chính của mơn học: Bài 1: Giới thiệu hệ thống cơ sở dữ liệu Giới thiệu tổng quát. So sánh hệ thống tập tin và hệ quản trị cơ sở dữ liệu (DBMS). Các ưu điểm của DBMS. Mơ tả và lưu trữ dữ liệu trong DBMS. Truy vấn tin trong DBMS. Quản lý giao tác. Cấu trúc của DBMS. Những người liên quan đến cơ sở dữ liệu. Bài 2: Mơ hình thực thể - kết hợp Tổng quát về thiết kế cơ sở dữ liệu. Thực thể, thuộc tính và tập thực thể. Mối kết hợp và tập mối kết hợp. Các tính năng bổ sung của mơ hình thực thể kết hợp (mơ hình E-R). Thiết kế cơ sở dữ liệu mức khái niệm dùng mơ hình E-R. Bài 3: Mơ hình quan hệ Giới thiệu mơ hình quan hệ. Ràng buộc tồn vẹn trên các quan hệ. Hiệu lực ràng buộc tồn vẹn. Truy vấn dữ liệu quan hệ. Thiết kế cơ sở dữ liệu mức logic: chuyển sơ đồ E-R thành lược đồ cơ sở dữ liệu quan hệ. Giới thiệu về khung nhìn. Bài 4: Ngơn ngữ truy vấn SQL Giới thiệu SQL. Dạng thức căn bản của một truy vấn SQL. Các phép tốn UNION. Truy vấn lồng nhau. Các phép tốn gộp. Các giá trị rỗng. SQL nhúng. Ràng buộc tồn vẹn trong SQL. Bài 5: Ngơn ngữ truy vấn QBE Giới thiệu QBE. Các truy vấn QBE căn bản. Truy vấn trên nhiều quan hệ. Phủ định trong cột tên của quan hệ. Các phép tốn gộp. Hộp điều kiện. Cột khơng đặt tên. Cập nhật. Bài 6: Thiết kế cơ sở dữ liệu với các dạng chuẩn Giới thiệu sự làm mịn lược đồ. Phụ thuộc hàm. Các ví dụ thúc đẩy sự làm mịn lược đồ. Lý luận phụ thuộc hàm. Các dạng chuẩn. Phân rã. Chuẩn hĩa. 7
8. SƠ ĐỒ MỐI LIÊN HỆ GIỮA CÁC MƠ ĐUN VÀ MƠN HỌC TRONG CHƯƠNG TRÌNH Học kỳ V Học kỳ VI Tiếng Anh chuyên ngành Lập trình nâng cao hướng .NET Phát triển phần mềm ứng dụng Phân tích và thiết kế giải thuật Lý thuyết về ngơn ngữ lập trình Kho dữ liệu Mơ hình client-server trên 3 độ Cấp SQL server I. Cơ sở trí tuệ nhân tạo và Phân tích hệ chuyên gia hướng đối tượng UML Tích hợp các ứng dụng trên Lập trình logic mạng An tồn thơng tin Cơ sở dữ liệu nâng cao Chuyên đề tự chọn Cơ sở dữ liệu nâng cao là một mơn học nâng cao đối với hệ cơng nhân lành nghề bậc cao. Để học được mơn học này, học viên cần phải học qua các mơn học cơ bản của cấp độ 2 như cơ sở dữ liệu, hệ quản trị cơ sở dữ liệu, Những học viên qua kiểm tra và thi mà khơng đạt phải thu xếp cho học lại những phần chưa đạt ngay và phải đạt điểm chuẩn mới được phép học tiếp các mơ đun/ mơn học tiếp theo. Học viên, khi chuyển trường, chuyển ngành.nếu đã học ở một cơ sở đào tạo khác rồi thì phải xuất trình giấy chứng nhận; Trong một số trường hợp cĩ thể vẫn phải qua sát hạch lại. 8
9. CÁC HÌNH THỨC HỌC TẬP CHÍNH TRONG MƠN HỌC 1 - Học trên lớp về : - Lý thuyết chuẩn hĩa dạng chuẩn. - Kiến trúc các mơ hình cơ sở dữ liệu. - Kiến thức về cơ sở dữ liệu quan hệ. - Cú pháp truy vấn SQL 2 - Học tại phịng học thực hành trường về: - Thiết kế cơ sở dữ liệu trên máy tính. - Sử dụng câu lệnh SQL. YÊU CẦU VỀ ĐÁNH GIÁ HỒN THÀNH MƠN HỌC Kỹ năng thực hành: - Sử dụng thành thạo ngơn ngữ SQL server. - Sử lý được các lỗi xảy ra. - Xây dựng thành cơng hệ thống cơ sở dữ liệu vừa và lớn. Thái độ học viên: - Cẩn thận lắng nghe ý kiến và thảo luận trong nhĩm thiết kế. - Học viên cần tuân thủ các bài tập thực hành theo thứ tự các chương, từ dễ đến khĩ. Đánh giá thơng qua kiểm tra trắc nghiệm: Đánh giá học viên thực hiện 1 trong 2 cách sau: 1- Làm một ứng dụng nhỏ cụ thể, giải quyết một vấn đề cụ thể. 2- Thi trắc nghiệm, cĩ thể thi trên giấy hoặc trên máy bẳng phần mềm thi trắc nghiệm. Thang điểm: 0-49: Khơng đạt 50-69 : Đạt trung bình 70-85 : Đạt khá 86-100: Đạt Giỏi 9
10. BÀI 1 GIỚI THIỆU HỆ QUẢN TRỊ CƠ SỞ DỮ LIỆU Mã bài : ITPRG3_02.1 Giới thiệu : Trong những năm gần đây, thuật ngữ "CƠ SỞ DỮ LIỆU" (Tiếng Anh là DataBase, viết tắt tiếng Việt là CSDL) đã trở nên khá quen thuộc khơng chỉ riêng với những người làm Tin học mà cịn đối với cả những người làm trong nhiều lĩnh vực khác như Thống kê, Kinh tế, Quản lý Doanh nghiệp v.v Các ứng dụng của Tin học vào cơng tác quản lý ngày càng nhiều hơn và càng đa dạng hơn. Cĩ thể nĩi hầu hết các lĩnh vực kinh tế, xã hội, giáo dục, y tế v.v đều đã ứng dụng các thành tựu mới của Tin học vào phục vụ cơng tác chuyên mơn của mình. Chính vì lẽ đĩ mà ngày càng nhiều người quan tâm đến lĩnh vực thiết kế và xây dựng các CSDL. 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ả chức năng của hệ quản trị cơ sở dữ liệu (DBMS). - So sánh hai cách tiếp cận: hệ thống tập tin truyền thống và DBMS. - Định nghĩa một số thuật ngữ liên quan đến mơ hình dữ liệu. Giải thích và phân loại ngơn ngữ truy vấn. - Định nghĩa giao tác và một số vấn đề liên quan. - Vẽ và mơ tả vắn tắt cấu trúc của một DBMS. Giải thích về các con người liên quan đến cơ sở dữ liệu. Nội dung chính: 1.1 Giới thiệu hệ thống cơ sở dữ liệu 1.2 So sánh hệ thống tập tin và hệ quản trị cơ sở dữ liệu (DBMS). 1.3 Các ưu điểm của DBMS. Mơ tả và lưu trữ dữ liệu trong DBMS 1.4 Ngơn ngữ giao tiếp giữa người sử dụng (NSD) và CSDL 1.5 Cấu trúc của DBMS 1.6 Những người liên quan đến cơ sở dữ liệu 1.1 Giới thiệu hệ thống cơ sở dữ liệu Cơ sở dữ liệu là một hệ thống các thơng tin cĩ cấu trúc được lưu trữ trên các thiết bị lưu trữ thơng tin thứ cấp (như băng từ, đĩa từ ) để cĩ thể thỏa mãn yêu cầu khai thác thơng tin đồng thời của nhiều người sử dụng hay nhiều chương trình ứng dụng với nhiều mục đích khác nhau. 10
11. Trong định nghĩa này cần nhấn mạnh những khía cạnh của định nghĩa được lưu ý qua các từ gạch chân. Trước hết, CSDL phải là một tập hợp các thơng tin mang tính hệ thống chứ khơng phải là các thơng tin rời rạc, khơng cĩ mối quan hệ với nhau. Các thơng tin này phải cĩ cấu trúc và tập hợp các thơng tin này phải cĩ khả năng đáp ứng các nhu cầu khai thác của nhiều người sử dụng một cách đồng thời. Đĩ cũng chính là các đặc trưng của CSDL. 1.2 So sánh hệ thống tập tin và hệ quản trị cơ sở dữ liệu (DBMS). Hệ thống các tập tin cổ điển (File System) Cho đến nay vẫn cịn một số đơn vị kinh tế, hành chính sự nghiệp v.v sử dụng mơ hình hệ thống các tập tin cổ điển: chúng được tổ chức riêng rẽ, phục vụ cho một mục đích của một đơn vị hay một đơn vị con trực thuộc cụ thể. Chẳng hạn, hãy xét ví dụ sau: Ví dụ : Tại một cơng ty người ta trang bị máy vi tính cho tất cả các phịng, ban nghiệp vụ. Bộ phận Văn phịng sử dụng máy tính để soạn thảo các văn bản báo cáo bằng MicroSoft Word do thủ trưởng yêu cầu về tình hình hoạt động của đơn vị trong đĩ cĩ chỉ tiêu về tổng số cơng nhân viên chức chia theo trình độ chuyên mơn được đào tạo. Phịng Kế tốn sử dụng máy tính để tính lương và in danh sách lương của từng bộ phận trong đơn vị dựa trên danh sách cán bộ viên chức cùng hệ số lương và các hệ số phụ cấp của họ do phịng Tổ chức cung cấp. Thơng tin mà phịng Kế tốn quản lý và khai thác là: Họ và Tên, Hệ số lương, Hệ số phụ cấp, Phụ cấp khác của các cơng nhân viên chức (CNVC) xếp theo từng phịng ban và sử dụng cơng cụ văn phịng là MicroSoft Excel. Phịng Tổ chức quản lý thơng tin lý lịch của CNVC chi tiết hơn gồm Họ CNVC, Tên CNVC (để riêng thành một cột "Tên" để tiện sắp xếp theo vần Alphabet), Bí danh, Giới tính, Ngày sinh, Ngày tuyển dụng, Hồn cảng gia đình, Quá trình được đào tạo, Hệ số lương, Hệ số phụ cấp, Ngày xếp lương trên nhưng thiếu thơng tin về Phục cấp khác của CNVC. Phần mềm được sử dụng để quản lý là FoxPro for Windows. 11
12. Trong khi đĩ, tại Tổng cơng ty của họ, các phịng ban nghiệp vụ cũng được trang bị vi tính. Phịng Tổ chức cán bộ tại Tổng cơng ty sử dụng phần mềm MicroSoft Access để quản lý CNVC gồm các cán bộ chủ chốt từ trường phĩ phịng, quản đốc và phĩ quản đốc xí nghiệp trở lên của các cơng ty con trực thuộc. Thơng tin quản lý tại đây cũng giống như thơng tin quản lý tại phịng tổ chức của cơng ty con. Nhận xét : Ưu điểm: - Việc xây dựng hệ thống các tập tin riêng tại từng đơn vị quản lý ít tốn thời gian bởi khối lượng thơng tin cần quản lý và khai thác là nhỏ, khơng địi hỏi đầu tư vật chất và chất xám nhiều, do đĩ triển khai ứng dụng nhanh. - Thơng tin được khai thác chỉ phục vụ cho mục đích hẹp nên khả năng đáp ứng nhanh chĩng, kịp thời. Nhược điểm: - Do thơng tin được tổ chức ở mỗi phịng ban mỗi khác, cũng như phần mềm cơng cụ để triển khai mỗi nơi cũng rất khác nhau nên sự phối hợp tổ chức và khai thác ở các phịng ban là khĩ khăn. Thơng tin ở phịng ban này khơng sử dụng được cho phịng ban khác, tại đơn vị con với đơn vị cấp trên. Cùng một thơng tin được nhập vào máy tại nhiều nơi khác nhau gây ra lãng phí cơng sức nhập tin và khơng gian lưu trữ trên các vật mang tin. Sự trùng lắp thơng tin cĩ thể dẫn đến tình trạng khơng nhất quán dữ liệu. Chẳng hạn, nhân viên Nguyễn Văn Quang được ghi đầy đủ ở phịng Tổ chức, nhưng tại phịng Kế tốn chỉ ghi tắt là Nguyễn v Quang. Thơng tin được tổ chức ở nhiều nơi nên việc cập nhật cũng dễ làm mất tính nhất quán dữ liệu. Một cán bộ chủ chốt của cơng ty cĩ thay đổi về hồn cảnh gia đình (mới cưới vợ / lấy chồng, sinh thêm con ) cĩ thể được cập nhật ngay tại đơn vị nhưng sau một thời gian mới được cập nhật tại Tổng cơng ty. Do hệ thống được tổ chức thành các hệ thống file riêng lẻ nên thiếu sự chia sẻ thơng tin giữa các nơi. Việc kết nối các hệ thống này hay việc nâng cấp ứng dụng sẽ là rất khĩ khăn. Qua phân tích trên chúng ta nhận thấy việc tổ chức dữ liệu theo hệ thống các tập tin cĩ nhiều nhược điểm. Việc xây dựng một hệ thống tin đảm bảo được tính chất nhất quán dữ liệu, khơng trùng lặp thơng tin mà vẫn đáp ứng được nhu cầu khai thác đồng thời của tất cả các phịng ban ở cơng ty và tổng cơng ty là thực sự cần thiết. 1.3 Các ưu điểm của DBMS. Mơ tả và lưu trữ dữ liệu trong DBMS. Rõ ràng, ưu điểm nổi bật của CSDL là: Giảm sự trùng lặp thơng tin xuống mức thấp nhất và do đĩ bảo đảm được tính nhất quán và tồn vẹn dữ liệu. Đảm bảo dữ liệu cĩ thể được truy xuất theo nhiều cách khác nhau. Khả năng chia sẻ thơng tin cho nhiều người sử dụng và nhiều ứng dụng khác nhau. 12
13. Tuy nhiên, để đạt được các ưu điểm trên, CSDL đặt ra những vấn đề cần phải giải quyết. Đĩ là: 1- Tính chủ quyền của dữ liệu. Do tính chia sẻ của CSDL nên tính chủ quyền của dữ liệu cĩ thể bị lu mờ và làm mờ nhạt tinh thần trách nhiệm, được thể hiện trên vấn đề an tồn dữ liệu, khả năng biểu diễn các mối liên hệ ngữ nghĩa của dữ liệu, và tính chính xác của dữ liệu. Điều này cĩ nghĩa là người khai thác CSDL phải cĩ nghĩa vụ cập nhật các thơng tin mới nhất của CSDL. 2- Tính bảo mật và quyền khai thác thơng tin của người sử dụng. Do cĩ nhiều người được phép khai thác CSDL một cách đồng thời nên cần phải cĩ một cơ chế bảo mật và phân quyền hạn khai thác CSDL. Các hệ điều hành nhiều người sử dụng hay hệ điều hành mạng cục bộ (Novelll Netware, Windows For WorkGroup, WinNT, ) đều cĩ cung cấp cơ chế này. 3- Tranh chấp dữ liệu. Nhiều người được phép truy nhập vào cùng một tài nguyên dữ liệu (Data Source) của CSDL với những mục đích khác nhau: Xem, thêm, xĩa hoặc sửa dữ liệu. Cần phải cĩ một cơ chế ưu tiên truy nhập dữ liệu cũng như cơ chế giải quyết tình trạng khĩa chết (DeadLock) trong quá trình khai thác cạnh tranh. Cơ chế ưu tiên cĩ thể được thực hiện bằng việc cấp quyền (hay mức độ) ưu tiên cho từng người khai thác - người nào được cấp quyền hạn ưu tiên cao hơn thì được ưu tiên truy nhập dữ liệu trước; theo biến cĩ hoặc loại truy nhập - quyền đọc được ưu tiên trước quyền ghi dữ liệu; dựa trên thời điểm truy nhập - ai cĩ yêu cầu truy xuất trước thì cĩ quyền truy nhập dữ liệu trước; hoặc theo cơ chế lập lịch truy xuất hay các cơ chế khĩa 4- Đảm bảo dữ liệu khi cĩ sự cố. Việc quản lý dữ liệu tập trung cĩ thể làm tăng khả năng mất mát hoặc sai lệch thơng tin khi cĩ sự cố như mất điện đột xuất, một phần đĩa lưu trữ CSDL bị hư v.v Một số hệ điều hành mạng cĩ cung cấp dịch vụ sao lưu ảnh đĩa cứng (cơ chế sử dụng đĩa cứng dự phịng - RAID), tự động kiểm tra và khắc phục lỗi khi cĩ sự cố, tuy nhiên, bên cạnh dịch vụ của hệ điều hành, để đảm bảo CSDL luơn luơn ổn định, một CSDL nhất thiết phải cĩ một cơ chế khơi phục dữ liệu khi các sự cố bất ngờ xảy ra. 1.4 Ngơn ngữ giao tiếp giữa người sử dụng (NSD) và CSDL Ngơn ngữ giao tiếp giữa người sử dụng (NSD) và CSDL, bao gồm: · Ngơn ngữ mơ tả dữ liệu (Data Definition Language - DDL) để cho phép khai báo cấu trúc của CSDL, khai báo các mối liên hệ của dữ liệu (Data RelationShip) và các quy tắc (Rules, Constraint) quản lý áp đặt lên các dữ liệu đĩ. · Ngơn ngữ thao tác dữ liệu (Data Manipulation Language - DML) cho phép người sử dụng cĩ thể thên (Insert), xĩa (Delete), sửa (Update) dữ liệu trong CSDL. · Ngơn ngữ truy vấn dữ liệu, hay ngơn ngữ hỏi đáp cĩ cấu trúc (Structured Query Language - SQL) cho phép những người khai thác CSDL (chuyên nghiệp hoặc khơng chuyên) sử dụng để truy vấn các thơng tin cần thiết trong CSDL. 13
14. · Ngơn ngữ quản lý dữ liệu (Data Control Language - DCL) cho phép những người quản trị hệ thống thay đổi cấu trúc của các bảng dữ liệu, khai báo bảo mật thơng tin và cấp quyền hạn khai thác CSDL cho người sử dụng. Từ điển dữ liệu (Data Dictionary) dùng để mơ tả các ánh xạ liên kết, ghi nhận các thành phần cấu trúc của CSDL, các chương trình ứng dụng, mật mã, quyền hạn sử dụng v.v Cĩ biện pháp bảo mật tốt khi cĩ yêu cầu bảo mật. Cơ chế giải quyết vấn đề tranh chấp dữ liệu. Mỗi hệ quản trị CSDL cũng cĩ thể cài đặt một cơ chế riêng để giải quyết các vấn đề này. Một số biện pháp sau đây được sử dụng: · Cấp quyền ưu tiên cho từng người sử dụng (người quản trị CSDL thực hiện). · Đánh dấu yêu cầu truy xuất dữ liệu, phân chia thời gian, người nào cĩ yêu cầu trước thì cĩ quyền truy xuất dữ liệu trước. Hệ quản trị CSDL cũng phải cĩ cơ chế sao lưu (Backup) và phục hồi (Restore) dữ liệu khi cĩ sự cố xảy ra. Điều này cĩ thể được thực hiện bằng cách: · Định kỳ kiểm tra CSDL, sau một thời gian nhất định hệ quản trị CSDL sẽ tự động tạo ra một bản sao CSDL. Cách này hơi tốn kém, nhất là đối với các CSDL lớn. · Tạo nhật ký (LOG) thao tác CSDL. Mỗi thao tác trên CSDL đều được hệ thống ghi lại, khi cĩ sự cố xảy ra thì tự động lần ngược lại (RollBack) để phục hồi CSDL. Hệ quản trị CSDL phải cung cấp một giao diện (Interface) tốt, dễ sử dụng, dễ hiểu cho những người sử dụng khơng chuyên. Ngồi ra, một hệ quản trị CSDL phải đáp ứng được một yêu cầu rất quan trọng, đĩ là bảo đảm tính độc lập giữa dữ liệu và chương trình: Khi cĩ sự thay đổi dữ liệu (như sửa đổi cấu lưu trữ các bảng dữ liệu, thêm các chỉ mục (Index) ) thì các chương trình ứng dụng (Application) đang chạy trên CSDL đĩ vẫn khơng cần phải được viết lại, hay cũng khơng làm ảnh hưởng đến những NSD khác. 1.5 Cấu trúc của DBMS Theo kiến trúc ANSI- PARC, một CSDL cĩ 3 mức biểu diển: Mức trong (cịn gọi là mức vật lý - Physical), mức quan niệm (Conception 14
15. hay Logical) và mức ngồi. Mức trong: Đây là mức lưu trữ CSDL. Tại mức này, vấn đề cần giải quyết là, dữ liệu gì và được lưu trữ như thế nào? ở đâu (đĩa từ, băng từ, track, sector nào)? Cần các chỉ mục gì? Việc truy xuất là tuần tự (Sequential Access) hay ngẫu nhiên (Random Access) đối với từng loại dữ liệu. Những người hiểu và làm việc với CSDL tại mức này là người quản trị CSDL (Administrator), những người sử dụng (NSD) chuyên mơn. Mức quan niệm: Tại mức này sẽ giải quyết cho câu hỏi CSDL cần phải lưu giữ bao nhiêu loại dữ liệu? đĩ là những dữ liệu gì? Mối quan hệ giữa các loại dữ liệu này như thế nào? Từ thế giới thực (Real Universe) các chuyên viên tin học qua quá trình khảo sát và phân tích, cùng với những người sẽ đảm nhận vai trị quản trị CSDL, sẽ xác định được những loại thơng tin gì được cho là cần thiết phải đưa vào CSDL, đồng thời mơ tả rõ mối liên hệ giữa các thơng tin này. Cĩ thể nĩi cách khác, CSDL mức quan niệm là một sự biểu diễn trừu tượng CSDL mức vật lý; hoặc ngược lại, CSDL vật lý là sự cài đặt cụ thể của CSDL mức quan niệm. Ví dụ: Người ta muốn xây dựng một hệ quản trị CSDL để quản lý các nhân viên của một cơng ty. Mơi trường (thế giới thực) của cơng ty ở đây gồm cĩ các phịng ban (Department) - mỗi phịng ban cĩ một tên gọi khác nhau, một địa chỉ trụ sở chính (Location), các số điện thoại (Telephone) để liên lạc, cĩ một người làm trưởng phịng ban, hàng năm được cấp một khoản kinh phí để hoạt động (Expense Budget), và phải đạt một doanh thu (Revenue Budget). Để tránh viết tên phịng ban dài dễ dẫn đến viết sai, người ta thường đặt cho mỗi phịng ban một giá trị số (gọi là số hiệu phịng ban - Department Number) và sử dụng số hiệu này để xác định tên và các thơng tin khác của nĩ. Cơng ty cĩ một số cơng việc cĩ thể sắp xếp cho các nhân viên trong cơng ty. Để thuận lợi cho việc theo dõi cơng việc cũng như trong cơng tác tuyển chọn nhân viên mới, người ta lập thành một bảng các cơng việc (JOBS) gồm các thơng tin: tên tắt cơng việc (Job), tên cơng việc (Job Name), mức lương tối thiểu (Min Salary) và tối đa (Max Salary) của cơng việc này và cho biết cơng việc này cần cĩ người lãnh đạo khơng. Một cơng việc cĩ thể cĩ nhiều người cùng làm. 15
16. Mỗi phịng ban cĩ thể cĩ từ 1 đến nhiều nhân viên (Employee). Mỗi nhân viên cĩ một tên gọi, một cơng việc làm (Job), một khoản tiền lương hàng tháng (Salary), số hiệu phịng ban mà anh ta đang cơng tác. Nếu muốn, người ta cĩ thể theo dõi thêm các thơng tin khác như ngày sinh (Birth Day), giới tính (Sex) v.v Để tránh viết tên nhân viên dài dễ dẫn đến sai sĩt, mỗi nhân viên cĩ thể được gán cho một con số duy nhất, gọi là mã số nhân viên (EmpNo). Nếu yêu cầu quản lý của cơng ty chỉ dừng ở việc theo dõi danh sách nhân viên trong từng phịng ban cùng các cơng việc của cơng ty thì cần 3 loại thơng tin: Phịng ban (DEPARTMENT), Cơng việc (JOBS) và Nhân viên (EMPLOYEE) với các thơng tin như trên là đủ. Cĩ thể cơng ty cĩ thêm yêu cầu quản lý cả quá trình tuyển dụng và nâng lương thì cần cĩ thêm một (hoặc một số) loại thơng tin về quá trình: Mã số nhân viên, lần thay đổi, thời gian bắt đầu và kết thúc sự thay đổi, mức lương, .v.v Từ mơi trường thế giới thực, xuất phát từ nhu cầu quản lý, việc xác định các loại thơng tin cần lưu trữ và các mối quan hệ giữa các thơng tin đĩ như thế nào đĩ chính là cơng việc ở mức quan niệm. Mức ngồi. Đĩ là mức của người sử dụng và các chương trình ứng dụng. Làm việc tại mức này cĩ các nhà chuyên mơn, các kỹ sư tin học và những người sừ dụng khơng chuyên. Mỗi người sử dụng hay mỗi chương trình ứng dụng cĩ thể được "nhìn" (View) CSDL theo một gĩc độ khác nhau. Cĩ thể "nhìn" thấy tồn bộ hay chỉ một phần hoặc chỉ là các thơng tin tổng hợp từ CSDL hiện cĩ. Người sử dụng hay chương trình ứng dụng cĩ thể hồn tồn khơng được biết về cấu trúc tổ chức lưu trữ thơng tin trong CSDL, thậm chí ngay cả tên gọi của các loại dữ liệu hay tên gọi của các thuộc tính. Họ chỉ cĩ thể làm việc trên một phần CSDL theo cách "nhìn" do người quản trị hay chương trình ứng dụng quy định, gọi là khung nhìn (View). Ví dụ : Cũng ví dụ trên, Phịng Tổ chức nhân sự giờ đây cịn quản lý thêm cả các thơng tin chi tiết trong lý lịch của nhân viên trong cơng ty: quá trình đào tạo chuyên mơn kỹ thuật - kinh tế - chính trị - quản lý Nhà nước, quá trình được khen thưởng, các lần bị kỷ luật, quá trình hoạt động Cách mạng bị địch bắt - bị tù đày, quá trình cơng tác, quá trình nâng lương, sơ lược tiểu sử cha mẹ - anh chị em ruột - vợ chồng - con v.v Rõ ràng rằng, Phịng Kế tốn cĩ thể chỉ được nhìn thấy CSDL là danh sách nhân viên đang làm các cơng việc cụ thể trong từng Phịng ban với các mức lương thỏa thuận, mà khơng được thấy lý lịch của các nhân viên. Lãnh đạo cơng ty cĩ thể chỉ cần "nhìn" thấy số lượng nhân viên, tổng số lương phải trả và ai là người lãnh đạo của từng Phịng ban. Trong khi đĩ ngay cả những người trong Phịng Tổ chức nhân sự cũng cĩ thể cĩ người được xem lý lịch của tất cả cán bộ, cơng nhân viên của 16
17. cơng ty, nhưng cũng cĩ thể cĩ người chỉ được xem lý lịch của những cán bộ, cơng nhân viên với mức lương từ xx đồng trở xuống Như vậy, cấu trúc CSDL vật lý (mức trong) và mức quan niệm thì chỉ cĩ một; nhưng tại mức ngồi, mức của các chương trình ứng dụng và người sử dụng trực tiếp CSDL, thì cĩ thể cĩ rất nhiều cấu trúc ngồi tương ứng. Sơ đồ tổng quát của một hệ quản trị CSDL Hình 1.5.2 Sơ đồ tổng quát của một hệ quản trị CSDL Hình 1.5.2 minh họa sơ đồ tổng quát của một hệ quản trị CSDL. Chúng ta thấy cĩ 3 mức: mức chương trình khai báo cấu trúc và chương trình ứng dụng; mức mơ tả CSDL, thao tác CSDL và các từ điển dữ liệu; và mức CSDL. Mỗi hệ quản trị CSDL cĩ một ngơn ngữ khai báo (hay mơ tả: Data Definition Language - DDL) cấu trúc CSDL riêng. Những người thiết kế và quản trị CSDL thực hiện các cơng việc khai báo cấu trúc CSDL. Các chương trình khai báo cấu trúc CSDL được viết bằng ngơn ngữ mà hệ quản trị CSDL cho phép. Hai cơng việc khai báo là khai báo cấu trúc lơgic (đĩ là việc khai báo các loại dữ liệu và các mối liên hệ giữa các loại dữ liệu đĩ, cùng các ràng buộc tồn vẹn dữ liệu - RBTV) và khai báo vật lý (dữ liệu được lưu trữ theo dạng nào?, cĩ bao nhiêu chỉ mục?). Các chương trình ứng dụng được viết bằng ngơn ngữ thao tác CSDL (Data Manipulation Language - DML) với mục đích: · Truy xuất dữ liệu · Cập nhật dữ liệu (thêm, xĩa, sửa dữ liệu) · Khai thác dữ liệu Ngơn ngữ thao tác CSDL cịn được sử dụng cho những NSD thao tác trực tiếp với CSDL. Từ điển dữ liệu (Data Dictionary - DD) là một CSDL của hệ quản trị CSDL sử dụng để lưu trữ cấu trúc CSDL, các thơng tin bảo mật, bảo đảm an tồn dữ liệu và các cấu trúc ngồi. Những người đã làm quen với hệ quản trị CSDL của MicroSoft Access cĩ thể thấy các từ điển dữ liệu này thơng qua các bảng (Table) cĩ tên bắt đầu bằng chữ MSys như MSysACEs, MSysColumn, MSysIMEXColumn, MSysIMEXSpecs, MSysIndexes, 17
18. MSysMacros, MSysObjects, MSysQueries, MSysRelationShips Từ điển dữ liệu cịn được gọi là Siêu CSDL (Meta-DataBase). (*) Quá trình hoạt động của một chương trình ứng dụng thơng qua các tầng của CSDL: Quá trình hoạt động của một chương trình ứng dụng thơng qua các tầng của CSDL. Hình trên cho chúng ta một cách nhìn về quá trình hoạt động của một chương trình ứng dụng thơng qua các tầng của CSDL: Các yêu cầu của chương trình ứng dụng được chuyển tới hệ quản trị CSDL (theo con đường số 2). Tại đây hệ quản trị CSDL sẽ tham khảo các từ điển dữ liệu (Meta DataBase) để tìm kiếm các ánh xạ cấu trúc ngồi với cấu trúc quan niệm và cấu trúc vật lý (các ngõ a, b và c). Tại đây hệ quản trị CSDL cĩ thể sẽ tham khảo tới vùng đệm của nĩ để xác định xem câu trả lời đã cĩ sẵn ở đĩ chưa, nếu cĩ thì trả lại cho chương trình ứng dụng thơng qua con đường số 9; ngược lại sẽ yêu cầu hệ điều hành truy xuất thơng tin theo con đường số 3. Tới đây hệ điều hành sẽ gửi yêu cầu truy xuất thơng tin trong CSDL thơng qua hệ thống xuất nhập của HĐH (các con đường số 4 và 5). Nếu việc truy xuất khơng thành cơng nĩ sẽ trả lại yêu cầu về cho hệ quản trị CSDL (cĩ thể thơng qua các mã lỗi) qua con đường số 6; nếu thành cơng thì dữ liệu sẽ được chuyển vào vùng đệm của hệ quản trị CSDL. Qua xử lý, hệ quản trị CSDL sẽ chuyển dữ liệu vào vùng đệm của chương trình ứng dụng đề nĩ xử lý (qua con đường 8a) và cho ra kết quả trả lời của chương trình ứng dụng qua con đường số 10. Theo sơ đồ trên cĩ thể nhận thấy các trục trặc cĩ thể xảy ra tại các con đường (2a), (3), (4), (5), (6) và (8). Lỗi tại 2 con đường số (6) và (8) cĩ thể là do tràn vùng làm việc. 1.6 Những người liên quan đến cơ sở dữ liệu · Những người sử dụng CSDL khơng chuyên về lĩnh vực tin học và CSDL, do đĩ CSDL cần cĩ các cơng cụ để cho những người sử dụng khơng chuyên cĩ thể sử dụng để khai thác CSDL khi cần thiết. · Các chuyên viên tin học biết khai thác CSDL. Những người này cĩ thể xây dựng các ứng dụng khác nhau phục vụ cho các mục đích khác nhau trên CSDL. · Những người quản trị CSDL, đĩ là những người hiểu biết về tin học, về các hệ quản trị CSDL và hệ thống máy tính. Họ là người tổ chức CSDL (khai báo cấu trúc CSDL, ghi nhận các yêu cầu bảo mật cho các dữ liệu cần bảo vệ ) do đĩ họ phải nắm rõ các vấn 18
19. đề kỹ thuật về CSDL để cĩ thể phục hồi dữ liệu khi cĩ sự cố. Họ là những người cấp quyền hạn khai thác CSDL, do vậy họ cĩ thể giải quyết được các vấn đề tranh chấp dữ liệu, nếu cĩ. BÀI TẬP: Bài 1: Hãy so sánh hệ thống tập tin cổ điển và hệ quản trị cơ sở dữ liệu. Bài 2: Từ điển dữ liệu cĩ chức năng gì trong hệ quản trị cơ sở dữ liệu. BÀI 2 MƠ HÌNH THỰC THỂ LIÊN KẾT Mã bài : ITPRG3_02.2 Giới thiệu : Trong bài này chúng ta sẽ tìm hiểu về mơ hình thực thể kết hợp, cụ thể sẽ đi xâu vào việc phân tích thiết kế mơ hình E-R, một giai đoạn của việc thiết kế cơ sở dữ liệu logic. Mục tiêu thực hiện: Học xong bài này học viên sẽ cĩ khả năng: - Định nghĩa mơ hình dữ liệu thực thể - kết hợp (mơ hình E-R) - Ứng dụng E-R trong quá trình thiết kế cơ sở dữ liệu. Định nghĩa chính xác các cấu trúc xây dựng mơ hình E-R. - Giải thích các ràng buộc biểu diễn trong mơ hình E-R. - Mơ tả một số chọn lựa trong quá trình thiết kế cơ sở dữ liệu mức khái niệm dùng mơ hình E-R) Nội dung chính: 2.1 Giới thiệu mơ hình thực thể kết hợp 2.2 Mơ hình thực thể - kết hợp, thực thể, thuộc tính và tập thực thể. Mối kết hợp và tập mối kết hợp. 2.3 Các tính năng bổ sung của mơ hình thực thể kết hợp (mơ hình E-R). 2.4 Chuyển từ mơ hình thực thể kết hợp sang lược đồ CSDL 2.1 Giới thiệu mơ hình thực thể kết hợp Các nhà phân tích thiết kế hệ thống thơng tin thường xây dựng lược đồ cơ sở dữ liệu từ mơ hình thực thể kết hợp và mơ hình này lại được xây dựng từ phần đặc tả vấn đề của một bài tốn thực tế. 19
20. Đặc tả vấn đề Phân tích đặc tả để xác định dữ liệu yêu cầu và mối liên quan giữa chúng để xây dựng mô hình thực thể kết hợp Mô hình thực thể kết hợp Áp dụng qui tắc biến đổi mô hình thực thể kết hợp thành lược đồ cơ sở dữ liệu Lược đồ cơ sở dữ liệu Hình 1.4.1 - Lược đồ CSDL xây dựng theo hướng Phân tích thiết kế Lược đồ cơ sở dữ liệu xây dựng theo hướng này thơng thường đạt tối thiểu dạng chuẩn 3 (3NF: third normal form) nghĩa là ở dạng cĩ sự dư thừa dữ liệu ở mức tối thiểu, cịn mơn CSDL xây dựng lược đồ CSDL đạt dạng chuẩn 3 từ lược đồ cơ sở dữ liệu chưa đạt dạng chuẩn cĩ kèm các tân từ (C1.III.2.ii). Ta hãy xem ví dụ sau: a) Ví dụ – Mối quan hệ một-nhiều (1) Đặc tả vấn đề Những người phụ trách đào tạo của Trường cao đẳng cộng đồng núi Ayers mong muốn tạo lập một CSDL về các mơn đào tạo của trường (như: chứng chỉ leo núi, cơng nghệ bay) và học viên ghi danh vào những mơn học này. Trường cũng cĩ qui định là cùng một lúc, học viên chỉ cĩ thể ghi danh vào một mơn học. Họ chỉ quan tâm về dữ liệu của đợt ghi danh hiện tại. Một khi học viên kết thúc mơn học thì nhà trường sẽ khơng cịn quan tâm đến họ và những học viên này phải được xĩa khỏi CSDL. Thơng tin cần lưu trữ về một học viên bao gồm: mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại, ngày nhập học Thơng tin về mơn học gồm mã mơn học, tên mơn học, thời lượng Phân tích: - phần đặc tả vấn đề chứa đựng các qui tắc quản lý và dữ liệu yêu cầu của vấn đề. - dữ liệu của vấn đề là: chi tiết về học viên cĩ mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại và ngày nhập học chi tiết về mơn học cĩ mã mơn học, tên mơn học và thời lượng. - qui tắc quản lý gồm: + Cùng một lúc, một học viên chỉ cĩ thể ghi danh vào một mơn học. + Nhiều học viên cĩ thể ghi danh vào một mơn học. + Nhà trường chỉ quan tâm đến những học viên của mơn học hiện tại. 20
21. (2) Mơ hình thực thể kết hợp (Mơ hình ER) một một hay nhiều mã học viên ghi danh vào mã môn học tên học viên HỌC VIÊN MÔN HỌC tên môn học địa chỉ thời lượng ngày sinh được ghi danh bởi số điện thoại có thể ngày nhập học phải Hình 1.4.2 - Mô hình ER của vấn đề học viên/môn học Trường cao đẳng cộng đồng núi Ayers Các tính chất trong mơ hình thực thể kết hợp: - Hình chữ nhật được gọi là tập thực thể. Tên của tập thực thể được ghi bên trong hình chữ nhật và dùng danh từ để đặt tên cho tập thực thể. - Đường nối giữa hai tập thực thể được gọi là mối quan hệ (mối kết hợp). Mối quan hệ trong vấn đề trên là mối quan hệ một-nhiều (1:M). Nội dung của mối quan hệ được diễn tả theo hai chiều: “ghi danh vào”, “được ghi danh bởi” và chúng diễn tả hai nội dung sau: + Mỗi HỌC VIÊN cĩ thể ghi danh vào một MƠN HỌC + Mỗi MƠN HỌC phải được ghi danh bởi một hay nhiều HỌC VIÊN - Các dữ liệu ghi bên cạnh tập thực thể được gọi là thuộc tính. Chúng cung cấp thơng tin chi tiết về tập thực thể. Cĩ hai loại thuộc tính: - Thuộc tính nhận diện là thuộc tính để phân biệt thực thể này với thực thể kia trong tập thực thể. - Thuộc tính mơ tả là thuộc tính cung cấp thơng tin chi tiết hơn về thực thể trong tập thực thể. - Mối quan hệ của vấn đề trên là mối quan hệ một-nhiều. Tính chất này của mối quan hệ gọi là tính kết nối của mối quan hệ. Tính kết nối một-nhiều rất phổ biến trong mơ hình thực thể kết hợp. Hai loại kết nối cịn lại ít phổ biến hơn nhưng khơng kém phần quan trọng là mối quan hệ một-một và mối quan hệ nhiều-nhiều. b) Ví dụ – mối quan hệ một-một (1) Đặc tả vấn đề Phịng cảnh sát mong muốn quản lý lý lịch cá nhân những người lái xe và bằng lái của họ. Một người chỉ lấy được một bằng lái và một bằng lái chỉ thuộc về một người. Thơng tin về lái xe mà phịng cảnh sát quan tâm là: mã người lái xe, tên, địa chỉ, ngày sinh Thơng tin về bằng lái cần lưu trữ là: mã bằng lái, loại bằng lái, ngày hết hạn (2) Mơ hình thực thể kết hợp mã người lái xe sở hữu mã bằng lái NGƯỜI LÁI tên BẰNG LÁI loại bằng lái XE địa chỉ được sở hữu bởi ngày hết hạn ngày sinh Hình 1.4.3 - Mô hình ER của vấn đề người lái xe và bằng lái - mỗi NGƯỜI LÁI XE phải sở hữu một BẰNG LÁI 21
22. - mỗi BẰNG LÁI phải được sở hữu bởi một NGƯỜI LÁI XE c) Ví dụ – mối quan hệ nhiều-nhiều (1) Đặc tả vấn đề Người phụ trách đào tạo Trường cao đẳng cộng đồng núi xanh mong muốn thiết lập một csdl về các mơn học mà họ cung cấp (như chứng chỉ leo núi, cử nhân cơng nghệ bay) và các học viên ghi danh vào các mơn học này. Nhà trường qui định là một học viên được ghi danh học tối đa ba mơn học trong cùng một lúc. Họ chỉ quan tâm đến dữ liệu của mơn học hiện tại. Một khi học viên kết thúc mơn học, họ sẽ khơng cịn thuộc diện quản lý của nhà trường và phải được xĩa khỏi csdl trừ khi học viên này ghi danh học tiếp mơn mới. Thơng tin về một học viên gồm: mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại, ngày nhập học Thơng tin về mơn học gồm: mã mơn học, tên mơn học, thời lượng (2) Mơ hình ER mã học viên ghi danh vào mã môn học tên học viên HỌC VIÊN MÔN HỌC tên môn học địa chỉ được ghi danh bởi thời lượng ngày sinh số điện thoại ?ngày nhập học Hình 1.4.4 - Mô hình ER của vấn đề Trường cao đẳng cộng đồng núi xanh + Mỗi HỌC VIÊN cĩ thể ghi danh vào một hay nhiều MƠN HỌC + Mỗi MƠN HỌC phải được ghi danh bởi một hay nhiều HỌC VIÊN Mơ hình ER trên cĩ mối quan hệ nhiều nhiều. (3) Loại bỏ tính kết nối nhiều nhiều (nếu được) Mơ hình trên gặp phải khuyết điểm sau: - Ngày nhập học là thuộc tính gắn liền với tập thực thể HỌC VIÊN sẽ khơng hợp lý vì khơng diễn tả được trường hợp học viên học cùng lúc nhiều mơn học. - Cịn nếu ngày nhập học là thuộc tính của MƠN HỌC thì khơng diễn tả được tình trạng cùng mơn học nhưng cĩ các ngày nhập học khác nhau. có ghi nhận đào tạo về HỌC VIÊN BIÊN NHẬN MÔN HỌC thuộc về được ghi nhận đào tạo bởi mã học viên mã học viên mã môn học tên học viên mã môn học tên môn học địa chỉ ngày nhập học thời lượng ngày sinh số điện thoại Hình 1.4.5 - Mô hình ER của vấn đề Trường Cao Đẳng Cộng Đồng Núi Xanh Để giải quyết vấn đề này ta phải đưa vào: - một tập thực thể làm trung gian giữa HỌC VIÊN và MƠN HỌC gọi là tập kết hợp PHIẾU GHI DANH. - Thuộc tính nhận diện của tập kết hợp là sự kết hợp giữa thuộc tính nhận diện của tập thực thể HỌC VIÊN và MƠN HỌC - thuộc tính mơ tả của tập kết hợp PHIẾU GHI DANH là ngày nhập học - tính kết nối của tập kết hợp với tập thực thể là một-nhiều 22
23. Nội dung của mối quan hệ giữa các tập thực thể là: - mỗi HỌC VIÊN cĩ thể cĩ một hay nhiều PHIẾU GHI DANH - mỗi PHIẾU GHI DANH phải thuộc về một HỌC VIÊN - mỗi PHIẾU GHI DANH phải ghi nhận đào tạo về một MƠN HỌC - mỗi MƠN HỌC cĩ thể được ghi nhận đào tạo bởi một hay nhiều PHIẾU GHI DANH Các qui tắc phải tuân thủ khi thêm tập kết hợp làm trung gian để loại bỏ tính kết nối nhiều nhiều: - Phải nhận diện được thuộc tính mơ tả của tập kết hợp. - Nếu cĩ thuộc tính mơ tả thì tạo tập kết hợp làm trung gian giữa hai tập thực thể. - Nếu khơng cĩ thuộc tính mơ tả thì vẫn giữ nguyên mơ hình như hình 1.4.4 2.2 Mơ hình thực thể - kết hợp, thực thể, thuộc tính và tập thực thể. Mối kết hợp và tập mối kết hợp. Mơ hình dữ liệu thực thể - kết hợp (Entity - RelationShip Model) do P.P.Chen đề xuất vào năm 1976. Các khái niệm chủ yếu được sử dụng trong lý thuyết của mơ hình này là: Loại thực thể (Entity Type): Là một loại đối tượng cần quản lý trong CSDL, chẳng hạn, KHOA, LỚP-HỌC, MƠN-HỌC, GIẢNG-VIÊN, HỌC-VIÊN, tức là, cũng tương tự như khái niệm về loại mẫu tin trong mơ hình mạng và mơ hình phân cấp. Thực thể (Entity): Là một thể hiện hoặc một đối tượng của một loại thực thể. Khái niệm này tương tự như khái niệm mẫu tin trong mơ hình dữ liệu mạng và mơ hình dữ liệu phân cấp. Thuộc tính của loại thực thể (Entity Attribute): Là các đặc tính riêng biệt cơ bản của loại thực thể, tương tự khái niệm thuộc tính (Attribute) trong mơ hình dữ liệu quan hệ sẽ trình bày trong Chương III. Ví dụ, loại thực thể KHOA cĩ các thuộc tính Mã-Khoa, Tên-Khoa. Loại thực thể LỚP-HỌC cĩ một số thuộc tính Mã-Lớp, Tên-Lớp, Niên-Khĩa, Số-Học-Viên. Loại thực thể MƠN-HỌC cĩ một số thuộc tính Mã-Mơn, Tên-Mơn, Số-Đv-Học-Trình. Loại thực thể HỌC-VIÊN cĩ một số thuộc tính Mã-Học-Viên, Tên-Học-Viên, Ngày-Sinh, Quê-Quán. Loại thực thể GIẢNG-VIÊN cĩ một số thuộc tính Mã-Giảng-Viên, Tên-Giảng-Viên, Cấp-Học-Vị, Chuyên-Ngành. v.v Khĩa của loại thực thể (Entity Key): Đĩ là các thuộc tính nhận diện của loại thực thể. Căn cứ vào các giá trị của các thuộc tính nhận diện này người ta cĩ thể xác định một thực thể duy nhất của một loại thực thể. Ví dụ, khĩa của loại thực thể LỚP-HỌC cĩ thể là Mã- Lớp; khĩa của loại thực thể HỌC-VIÊN cĩ thể là Mã-Học-Viên; khĩa của loại thực thể MƠN- HỌC cĩ thể là Mã-Mơn-Học Khái niệm này cũng tương tực như khái niệm khĩa (Key) trong mơ hình dữ liệu quan hệ sẽ trình bày trong Chương III. Loại mối kết hợp (Entity Relationship): Tương tự như loại mối liên hệ trong mơ hình dữ liệu mạng. Trong đồ thị biểu diễn của mơ hình này người ta cũng sử dụng hình elíp để thể 23
24. hiện một mối kết hợp giữa các thực thể. Giữa 2 loại thực thể cĩ thể tồn tại nhiều hơn một mối kết hợp. Số ngơi của mối kết hợp (RelationShip Degree): Là tổng số loại thực thể tham gia vào mối kết hợp. Ví dụ, giữa loại thực thể SINH-VIÊN và KHOA tồn tại mối kết hợp "trực thuộc" - đĩ là mối kết hợp 2 ngơi. KẾT-QUẢ-THI (hoặc KIỂM-TRA) của sinh viên là mối kết hợp giữa 3 thực thể SINH-VIÊN, MƠN-THI và ĐIỂM-THI - đĩ là mối kết hợp 3 ngơi. Thuộc tính của mối kết hợp (RelationShip Attribute): Mối kết hợp cĩ thể cĩ các thuộc tính của riêng nĩ. Thơng thường mối kết hợp cĩ các thuộc tính là khĩa của các loại thực thể tham gia vào mối kết hợp, ngồi ra cịn cĩ thêm những thuộc tính bổ sung khác. Ví dụ, trong mối kết hợp 3 ngơi kể trên, thuộc tính của mối kết hợp này cĩ thể bao gồm Mã-Học-Viên, Mã-Mơn-Học, Điểm-Thi; và cĩ thể cĩ thêm các thuộc tính bổ sung khác như Lần-Thi-Thứ, Ngày-Thi, Ghi-Chú v.v Bản số của mỗi nhánh của mối kết hợp (RelationShip Cardinal): Mỗi nhánh (hay mỗi chiều) của mối kết hợp là mối kết hợp nối một loại thực thể với một mối kết hợp. Trong nhánh này cần xác định số lượng tối thiểu và số lượng tối đa các thực thể của nhánh đĩ sẽ tham gia vào một thực thể của mối kết hợp. Hai đại lượng này - đặt trong cặp dấu ngoặc trịn - được gọi là bản số của mối kết hợp. Ví dụ, trong mối kết hợp 3 ngơi nêu trên, tại nhánh nối loại thực thể HỌC-VIÊN với mối kết hợp KẾT-QUẢ-THI là (1,n), bởi vì sẽ cĩ ít nhất một học viên tham gia kỳ thi và nhiều nhất là tất cả số học viên học mơn đĩ cùng dự thi. * Ví dụ về mơ hình dữ liệu thực thể - kết hợp của CSDL quản lý học viên: 2.3 Các tính năng bổ sung của mơ hình thực thể kết hợp (mơ hình E-R). Hình 2.1 Mơ hình dữ liệu thực thể - kết hợp của CSDL quản lý học viên. Hình 2.1 trình bày một mơ hình thực thể - kết hợp cho CSDL quản lý học viên gồm các loại thực thể KHOA, LỚP-HỌC, MƠN-HỌC, GIẢNG-VIÊN, HỌC-VIÊN. Mối kết hợp HỌCVIÊNLỚPHỌC giữa 2 loại thực thể HỌC-VIÊN và LỚP-HỌC. Tại nhánh HỌC-VIÊN bản số của nĩ là (1,1), điều này nhà phân tích và thiết kế đã khẳng định tình trạng thực tế là một học viên phải theo học (hay cĩ tên trong danh sách) ít nhất là một lớp và cũng chỉ thuộc 24
25. tối đa một lớp. Bản số của nhánh LỚP-HỌC là (1,n) nghĩa là một lớp (nếu đã cĩ tên trong danh sách lớp học) thì cĩ ít nhất 1 học viên và tối đa cĩ thể là nhiều (n) học viên theo học. Vào khoảng năm 1980, mơ hình dữ liệu thực thể-kết hợp đã được mở rộng thêm một số khái niệm mới như "loại thực thể chuyên biệt hĩa" (Specialized Entity) và "mối kết hợp đệ quy" (Recursive RelationShip). Mơ hình này cùng với mơ hình dữ liệu quan hệ và mơ hình hướng đối tượng được sử dụng khá phổ biến trong việc thiết kế các CSDL hiện nay, bởi sự kết hợp này làm cho mơ hình dữ liệu thể hiện được nhiều ngữ nghĩa của những loại dữ liệu trong CSDL hơn. Hình 2.2. Mơ hình thực thể kết hợp với các thực thể chuyên biệt hĩa. Nếu yêu cầu quản lý địi hỏi phải phân tích rạch rịi thành 2 loại lớp học chính quy và tại chức, và các lớp học tại chức cĩ thể sẽ khơng phải học một số mơn học hoặc số đơn vị học trình ít hơn so với các lớp chính quy, khi đĩ mơ hình dữ liệu thực thể - kết hợp được thể hiện như trong sơ đồ 2.2. Thực thể chuyên biệt hĩa được thể hiện trong mơ hình bằng hình chữ nhật cĩ khung (Frame) đơi; một đường kết hợp bằng nét đơi cĩ dấu gạch ngang thể hiện mối quan hệ là chuyên biệt hố thành các loại thực thể con chi tiết hơn. Các tên trong các hình hộp chữ nhật thể hiện các thuộc tính của loại thực thể đĩ. 2.4 Chuyển từ mơ hình thực thể kết hợp sang lược đồ CSDL. a) Qui tắc chung Khi biến đổi mơ hình ER thành các mơ hình quan hệ ta áp dụng các qui tắc sau: - Mỗi tập thực thể trong mơ hình ER được chuyển thành một lược đồ quan hệ. - Mỗi thuộc tính trong mơ hình ER được chuyển thành thuộc tính trong lược đồ quan hệ tương ứng 25
26. - Mỗi thuộc tính nhận diện trong mơ hình ER được chuyển thành khĩa chính trong lược đồ quan hệ tương ứng. - Mỗi mối quan hệ trong ER được chuyển thành khĩa ngoại theo qui tắc sau b) Qui tắc chuyển mối quan hệ thành khĩa ngoại 1) Mối quan hệ một-một Chuyển khĩa chính từ quan hệ 1 sang quan hệ 2 hay ngược lại. Ví dụ vấn đề người lái xe và bằng lái sẽ cĩ mơ hình quan hệ là một trong hai mơ hình quan hệ sau Mô hình ER mã người lái xe sở hữu mã bằng lái NGƯỜI LÁI tên BẰNG LÁI loại bằng lái XE địa chỉ được sở hữu bởi ngày hết hạn ngày sinh Lược đồ CSDL NGƯỜI LÁI XE (mã người lái xe, tên, địa chỉ, ngày sinh) BẰNG LÁI(mã bằng lái, loại bằng lái, ngày hết hạn, mã người lái xe) hay NGƯỜI LÁI XE (mã người lái xe, tên, địa chỉ, ngày sinh, mã bằng lái) BẰNG LÁI(mã bằng lái, loại bằng lái, ngày hết hạn) Hình 1.4.6 - Mô hình ER và mô hình quan hệ của vấn đề Người lái xe và bằng lái 2) Mối quan hệ một-nhiều Chuyển khĩa chính từ bên một sang bên nhiều. Mô hình ER mã học viên tên học viên mã môn học ghi danh vào địa chỉ HỌC VIÊN MÔN HỌC tên môn học ngày sinh được ghi danh bởi thời lượng số điện thoại ngày nhập học khóa ngoại Lược đồ CSDL HỌC VIÊN (mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại, ngày nhập học, mã môn học) MÔN HỌC (mã môn học, tên môn học, thời lượng) Hình 1.4.7 - Mô hình ER và mô hình quan hệ của vấn đề Trường cao đẳng cộng đồng núi Ayers 3) Mối quan hệ nhiều nhiều đến tập kết hợp Mô hình ER có ghi nhận đào tạo về HỌC VIÊN PHIẾU GHI DANH MÔN HỌC thuộc về mã học viên mã học viên được ghi nhận đào tạo bởi mã môn học tên học viên mã môn học tên môn học địa chỉ ngày nhập học thời lượng ngày sinh số điện thoại Lược đồ CSDL HỌC VIÊN (mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại) MÔN HỌC (mã môn học, tên môn học, thời lượng) PHIẾU GHI DANH(mã học viên, mã môn học, ngày nhập học) Hình 1.4.8 - Mô hình ER và mô hình quan hệ của vấn đề Trường Cao Đẳng Cộng Đồng Núi Xanh Trong quan hệ PHIẾU GHI DANH cĩ các khĩa chính khĩa ngoại như sau: + mã học viên là khĩa ngoại + mã mơn học là khĩa ngoại + mã học viên và mã mơn học là khĩa chính 26
27. 4) Mối quan hệ nhiều-nhiều Tạo một quan hệ mới cĩ khĩa chính là sự kết hợp các khĩa chính của hai quan hệ cĩ tính kết nối nhiều nhiều. Ví dụ giả sử Trường Cao Đẳng Cộng Đồng Núi Xanh khơng quan tâm đến ngày nhập học của học viên thì mơ hình ER sẽ cĩ mối quan hệ nhiều nhiều như sau: Mô hình ER mã học viên tên học viên ghi danh vào mã môn học địa chỉ HỌC VIÊN MÔN HỌC tên môn học ngày sinh được ghi danh bởi thời lượng số điện thoại Mô hình quan hệ HỌC VIÊN(mã học viên, tên học viên, địa chỉ, ngày sinh, số điện thoại) MÔN HỌC(mã môn học, tên môn học, thời lượng) HỌC VIÊN MÔN HỌC(mã học viên, mã môn học) Hình 1.4.9 - Mô hình ER và mô hình quan hệ của vấn đề Trường cao đẳng cộng đồng núi xanh không có thuộc tính ngày nhập học 27
28. BÀI TẬP THỰC HÀNH 1) Phép tốn tập hợp và phép tốn quan hệ Cho lược đồ cơ sở dữ liệu dùng để quản lý hồ sơ sinh viên bao gồm các quan hệ Sv(sinh viên), Lop(Lớp), kh(khoa), Mh(mơn học), Kq(kết quả) được mơ tả bởi các lược đồ quan hệ như sau: Sv(MASV,HOTEN,NU,NGAYSINH,MALOP,TINH,HOCBONG) Tân từ: Mỗi sinh viên cĩ mỗi MASV duy nhất. Mỗi MASV xác định tất cả các thuộc tính cịn lại của sinh viên đĩ. Lop(MALOP,TENLOP,SISO,MAKHOA) Tân từ: Mỗi lớp cĩ một mã lớp duy nhất, mỗi lớp chỉ thuộc về một khoa nào đĩ. Kh(MAKHOA,TENKHOA,SOCBGD) Tân từ: Mỗi khoa cĩ mỗi MAKHOA duy nhất. Mỗi MAKHOA xác định tất cả các thuộc tính cịn lại của khoa đĩ. Mh(MAMH,TENMH,SOTIET) Tân từ: Mơi Mơn học cĩ một MAMH duy nhất. Mỗi MAMH xác định tất cả các thuộc tính cịn lại của mơn học đĩ. Kq(MASV,MAMH,DIEMTHI) Tân từ: Mỗi sinh viên cùng với một mơn học xác dịnh duy nhất một điểm thi YÊU CẦU: 1. Tìm khĩa cho mỗi lược đồ quan hệ trên. 2. Hãy thực hiện các câu hỏi sau bằng ngơn ngữ đại số quan hệ a. Lập danh sách sinh viên gồm MASV, HOTEN, HOCBONG b. Lập danh sách sinh viên nữ khoa ‘CNTT’,danh sách cần MASV, HOTEN, HOCBONG c. Lập bảng điểm cho tất cả sinh viên khoa ‘CNTT’, bảng điểm gồm các cột MASV, HOTEN, TENMH, DIEMTHI d. Lập phiếu điểm cho sinh viên cĩ MASV=”99001” e. Lập danh sách sinh viên gồm MASV,HOTEN,TENLOP, TENKHOA f. Lập bảng điểm mơn học cĩ mã mơn học là CSDL cho tất cả sinh viên cĩ mã lớp là “CĐTH2B” g. Lập danh sách sinh viên của lớp cĩ mã lớp là “CĐTH2B” và cĩ điểm thi mơn học lớn hơn hay bằng 8. 2) Mơ hình thực thể kết hợp Dựa vào các phân tích sơ bộ dưới đây, hãy lập mơ hình thực thể kết hợp cho mỗi bài tốn quản lý sau: 28
29. QUẢN LÝ LAO ĐỘNG Để quản lý việc phân cơng các nhân viên tham gia vào xây dựng các cơng trình. Cơng ty xây dựng ABC tổ chức quản lý như sau: Cùng lúc cơng ty cĩ thể tham gia xây dựng nhiều cơng trình, mỗi cơng trình cĩ một mã số cơng trình duy nhất (MACT), mỗi mã số cơng trình xác định các thơng tin như: tên gọi cơng trình (TENCT), địa điểm(ĐIAĐIEM), ngày cơng trình được cấp giấy phép xây dựng (NGAYCAPGP), ngày khởi cơng (NGAYKC), ngày hồn thành (NGAYHT). Mỗi nhân viên của cơng ty ABC cĩ một mã số nhân viên(MANV) duy nhất, một mã số nhân viên xác định các thơng tin như: Họ tên (HOTEN), ngày sinh (NGAYSINH), phái (PHAI), địa chỉ (ĐIACHI). Mỗi nhân viên phải chịu sự quản lý hành chánh bởi một phịng ban. Tất nhiên một phịng ban quản lý hành chánh nhiều nhân viên. Cơng ty cĩ nhiều phịng ban (Phịng kế tốn, phịng kinh doanh, phịng kỹ thuật, phịng tổ chức, phịng chuyên mơn, Phịng phục vụ, ). Mỗi phịng ban cĩ một mã số phịng ban(MAPB) duy nhất, mã phịng ban xác định tên phịng ban (TENPB). Cơng ty phân cơng các nhân viên tham gia vào các cơng trình, mỗi cơng trình cĩ thể được phân cho nhiều nhân viên và mỗi nhân viên cùng lúc cũng cĩ thể tham gia vào nhiều cơng trình. Với mỗi cơng trình một nhân viên cĩ một số lượng ngày cơng (SLNGAYCONG) đã tham gia vào cơng trình đĩ. QUẢN LÝ THƯ VIỆN Một thư viện tổ chức việc cho mượn sách như sau: Mỗi quyển sách được đánh một mã sách (MASH) dùng để phân biệt với các quyển sách khác (giả sử nếu một tác phẩm cĩ nhiều bản giống nhau hoặc cĩ nhiều tập thì cũng xem là cĩ mã sách khác nhau), mỗi mã sách xác định các thơng tin khác như : tên sách (TENSACH), tên tác giả (TACGIA), nhà xuất bản (NHAXB), năm xuất bản (NAMXB). Mỗi đọc giả được thư viên cấp cho một thẻ thư viện, trong đĩ cĩ ghi rõ mã đọc giả (MAĐG), cùng với các thơng tin khác như : họ tên (HOTEN), ngày sinh (NGAYSINH), địa chỉ (ĐIACHI), nghề nghiệp(NGHENGHIEP). Cứ mỗi lượt mượn sách, đọc giả phải ghi các quyển sách cần mượn vào một phiếu mượn, mỗi phiếu mượn cĩ một số phiếu mượn (SOPM) duy nhất, mỗi phiếu mượn xác định các thơng tin như: ngày mượn (NGAYMUON), đọc giả mượn, các quyển sách mượn và ngày trả (NGAYTRA). Các các quyển sách trong cùng một phiếu mượn khơng nhất thiết phải trả trong trong cùng một ngày. QUẢN LÝ BÁN HÀNG Mỗi khách hàng cĩ một mã khách hàng (MAKH) duy nhất, mỗi MAKH xác định được các thơng tin về khách hàng như : họ tên khách hàng (HOTEN), địa chỉ (ĐIACHI), số điện thoại (ĐIENTHOAI). Các mặt hàng được phân loại theo từng nhĩm hàng, mỗi nhĩm hàng cĩ một mã nhĩm (MANHOM) duy nhất, mỗi mã nhĩm hàng xác định tên nhĩm hàng (TENNHOM), tất nhiên một nhĩm hàng cĩ thể cĩ nhiều mặt hàng. Mỗi mặt hàng được đánh một mã số 29
30. (MAHANG) duy nhất, mỗi mã số này xác định các thơng tin về mặt hàng đĩ như : tên hàng (TENHANG), đơn giá bán (ĐONGIA), đơn vị tính (ĐVT). Mỗi hĩa đơn bán hàng cĩ một số hĩa đơn (SOHĐ) duy nhất, mỗi hĩa đơn xác định được khách hàng và ngày lập hĩa đơn (NGAYLAPHĐ), ngày bán hàng (NGAYBAN). Với mỗi mặt hàng trong một hĩa đơn cho biết số lượng bán (SLBAN) của mặt hàng đĩ. QUẢN LÝ LỊCH DẠY - HỌC Để quản lý lịch dạy của các giáo viên và lịch học của các lớp, một trường tổ chức như sau: Mỗi giáo viên cĩ một mã số giáo viên (MAGV) duy nhất, mỗi MAGV xác định các thơng tin như: họ và tên giáo viên (HOTEN), số điện thoại (DTGV). Mỗi giáo viên cĩ thể dạy nhiều mơn cho nhiều khoa nhưng chỉ thuộc sự quản lý hành chánh của một khoa nào đĩ. Mỗi mơn học cĩ một mã số mơn học (MAMH) duy nhất, mỗi mơn học xác định tên mơn học (TENMH). Ưng với mỗi lớp thì mỗi mơn học chỉ được phân cho một giáo viên. Mỗi phịng học cĩ một số phịng học (SOPHONG) duy nhất, mỗi phịng cĩ một chức năng (CHUCNANG); chẳng hạn như phịng lý thuyết, phịng thực hành máy tính, phịng nghe nhìn, xưởng thực tập cơ khí, Mỗi khoa cĩ một mã khoa (MAKHOA) duy nhất, mỗi khoa xác định các thơng tin như: tên khoa (TENKHOA), điện thoại khoa(DTKHOA). Mỗi lớp cĩ một mã lớp (MALOP) duy nhất, mỗi lớp cĩ một tên lớp (TENLOP), sĩ số lớp (SISO). Mỗi lớp cĩ thể học nhiều mơn của nhiều khoa nhưng chỉ thuộc sự quản lý hành chính của một khoa nào đĩ. Hàng tuần, mỗi giáo viên phải lập lịch báo giảng cho biết giáo viên đĩ sẽ dạy những lớp nào, ngày nào (NGAYDAY), mơn gì?, tại phịng nào, từ tiết nào (TUTIET) đến tiết nào (DENTIET),tựa đề bài dạy (BAIDAY), ghi chú (GHICHU) về các tiết dạy này, đây là giờ dạy lý thuyết (LYTHUYET) hay thực hành - giả sử nếu LYTHUYET=1 thì đĩ là giờ dạy thực hành và nếu LYTHUYET=2 thì đĩ là giờ lý thuyết, một ngày cĩ 16 tiết, sáng từ tiết 1 đến tiết 6, chiều từ tiết 7 đến tiết 12, tối từ tiết 13 đến 16. oOo BÀI 3 MƠ HÌNH QUAN HỆ Mã bài : ITPRG3_02.3 Giới thiệu : Mơ hình Cơ sở dữ liệu Quan hệ (gọi tắt là Mơ hình Quan hệ) do E.F Codd đề xuất năm 1971. Mơ hình này bao gồm: - Một hệ thống các ký hiệu để mơ tả dữ liệu dưới dạng dịng và cột như quan hệ, bộ, thuộc tính, khĩa chính, khố ngoại, - Một tập hợp các phép tốn thao tác trên dữ liệu như phép tốn tập hợp, phép tốn quan hệ. 30
31. - Ràng buộc tồn vẹn quan hệ. Mục tiêu thực hiện: Học xong bài này học viên sẽ cĩ khả năng: - Nắm được chính xác các thuật ngữ liên quan mơ hình quan hệ. - Giải thích các ràng buộc. Nhận biết được khi nào một ràng buộc tồn vẹn cĩ thể bị vi phạm. - Biết cách chuyển đổi sơ đồ E-R thành lược đồ cơ sở dữ liệu quan hệ. - Nắm được định nghĩa sự nhìn. Giải thích các vấn đề liên quan đến sự nhìn. Nội dung chính: 3.1 Giới thiệu mơ hình quan hệ. 3.2 Ràng buộc tồn vẹn trên các quan hệ. Hiệu lực ràng buộc tồn vẹn. 3.3 Truy vấn dữ liệu quan hệ. 3.4 Các phép tốn trên cơ sở dữ liệu 3.5 Các phép tốn khác 3.6 Thiết kế cơ sở dữ liệu mức logic: chuyển sơ đồ E-R thành lược đồ cơ sở dữ liệu quan hệ. 3.1 Giới thiệu mơ hình quan hệ. Các hệ HQTCSDLQH ngày nay được xây dựng dựa vào lý thuyết của mơ hình quan hệ. Mục đích của mơn học này giúp cho sinh viên nắm được kiến trúc tổng quát về mơ hình quan hệ và áp dụng nĩ để lập mơ hình dữ liệu quan hệ cĩ hiệu quả trong lưu trữ và khai thác. MAS HOTENH MONHOC TENKHOA DIEMTH V I 9900 TRAN DAN THU CO SO DU LIEU CƠNG NGHỆ THỘNG 3.0 1 TIN 9900 NGUYEN HA DA CO SO DU LIEU CƠNG NGHỆ THỘNG 8.0 2 THAO TIN 9900 TRAN DAN THU TIN HOC VAN CƠNG NGHỆ THỘNG 6.0 1 PHONG TIN 9900 LE THANH TRUNG TIN HOC VAN ANH VAN 5.0 5 PHONG Chẳng hạn với bài tốn quản lý điểm thi của sinh viên; nếu lưu trữ dữ liệu theo dạng bảng với các cột MASV, HOTEN, MONHOC,TENKHOA, DIEMTHI thì các gía trị của các cột HOTEN, MONHOC, TENKHOA sẽ bị trùng lắp. Sự trùng lắp này gây nên một số vấn đề: 31
32. - Ta khơng thể lưu trữ một sinh viên mới khi sinh viên này chưa cĩ điểm thi - Khi cần sửa đổi họ tên sinh viên thì ta phải sửa tất cả các dịng cĩ liên quan đến sinh viên này. Điều này dễ gây ra tình trạng dữ liệu thiếu nhất quán. - Khi cĩ nhu cầu xĩa điểm thi của một sinh viên kéo theo khả năng xĩa luơn họ tên sinh viên đĩ. Việc lưu trữ dữ liệu như trên khơng đúng với mơ hình quan hệ. Để lưu trữ đúng với mơ hình quan hệ ta phải thay MONHOC bằng MAMH, thay TENKHOA bằng MAKHOA, tách một bảng dữ liệu lớn đĩ ra thành nhiều bảng con, như mơ hình dưới. MASV MAMH MAKHOA DIEMTHI MASV HOTEN 99001 CSDL CNTT 3.0 99001 TRAN DAN THU 99002 CSDL CNTT 8.0 99002 NGUYEN HA DA THAO 99001 THVP CNTT 6.0 99005 LE THANH TRUNG 99005 THVP AV 5.0 MAMH TENMH SOTIET MAKHOA TENKHOA CSDL CO SO DU LIEU 90 CNTT CONG NGHE THONG TIN THVP TIN HOC VAN 90 AV ANH VAN PHONG 3.2 Ràng buộc tồn vẹn trên các quan hệ. Hiệu lực ràng buộc tồn vẹn. Trong một CSDL cĩ thể phát hiện nhiều RBTV, tuy nhiên cĩ thể chia chúng thành hai loại chính theo bối cảnh RBTV: i) Ràng buộc tồn vẹn trong bối cảnh là một quan hệ cơ sở. ii) Ràng buộc tồn vẹn cĩ bối cảnh trên nhiều quan hệ cơ sở. Chúng ta sẽ xem xét chi tiết các loại RBTV chi tiết trong từng bối cảnh nêu trên. 3.2.1 Ràng buộc tồn vẹn cĩ bối cảnh là 1 quan hệ cơ sở. Trên một quan hệ cở sở cĩ thể tồn tại nhiều RBTV thuộc các loại: RBTV về miền giá trị của thuộc tính, RBTV về giá trị giữ thuộc tính này với (các) thuộc tính khác (gọi là liên thuộc tính) và giữa các giá trị của các bộ giá trị khác nhau (gọi là liên bộ - liên thuộc tính) 3.2.1.1 Ràng buộc tồn vẹn về miền giá trị của thuộc tính. Trong hầu hết các CSDL quan hệ, loại RBTV này là rất phổ biến. Như chúng ta đã biết, thuộc tính được đặc trưng khơng chỉ bởi kiểu giá trị mà nĩ cịn bị giới hạn bởi miền giá trị trong kiểu dữ liệu đĩ. Do đĩ, khi thực hiện các thao tác cập nhật Thêm / Sửa bộ giá trị mới cho quan hệ đều phải kiểm tra RBTV này. Ví dụ 3.2.1: Trong quan hệ KQUẢ-THI(MaSinhVien, MaMonHoc, LanThi, DiemThi), với quy định mỗi học viên chỉ được thi một mơn học tối đa là 3 lần, hiển nhiên là điểm thi của mỗi mơn 32
33. học trong mọi lần thi khơng bị âm và khơng vượt quá 10. Cĩ 2 ràng buộc tồn vẹn về miền giá trị trong quan hệ này: R1: kq KQUẢ-THI thì 0 kq.Lanthi 3 R2: kq KQUẢ-THI thì 0 kq.DiemThi 10 Giả sử các giảng viên cĩ “châm chước” thêm rằng điểm thi lần sau khơng nhỏ hơn điểm thi lần trước đĩ. Chúng ta cĩ thêm ràng buộc tồn vẹn về miền giá trị: R3: kq KQUẢ-THI / kq. DiemThi (lần trước) kq. DiemThi 10.0 3.2.1.2 Ràng buộc tồn vẹn liên thuộc tính. Đĩ là loại RBTV cĩ liên quan tới nhiều thuộc tính của một quan hệ. Thơng thường đĩ là các phụ thuộc tính tốn, hoặc một suy diễn từ giá trị của một hay nhiều thuộc tính trong cùng một bộ giá trị. Ví dụ 3.2.2: Quan hệ CHITIẾT_HĐ trong CSDL quản lý hĩa đơn bán hàng cho trong ví dụ trên, cĩ RBTV liên thuộc tính là: cthđ CHITIẾT_HĐ / cthđ.Trị-giá = cthđ.Số-lượng-đặt * cthđ.Đơn-giá. Ví dụ 3.2.3: Quan hệ danh sách cán bộ - cơng chức Nhà nước CBCC với tập các thuộc tính: { Mã- đơn-vị, Mã-CBCC, Họ-tên, Giới-tính, Ngày-sinh, Ngày-tuyển-dụng, Ngạch-CBCC, Bậc, Hệ- số-lương, Ngày-xếp-lương }. Với quy định nam từ 18 đến 60 và nữ từ 18 đến 55 tuổi và phải từ 18 tuổi trở lên mới được tuyển vào làm cơng chức Nhà nước. Chúng ta cĩ các RBTV về miền giá trị liên thuộc tính như sau: R1: cc CBCC / nếu cc.Giới-tính = Nam thì (Now() - cc.Ngay_sinh) / 365 trong khoảng 18 và 60. Nếu cc.Giới-tính = Nữ thì (Now() - cc.Ngay-sinh) / 365 trong khoảng 18 và 55. R2: cc CBCC / (cc.Ngay-tuyen-dung - cc.Ngay-sinh) / 365 18 va cc.Ngay-tuyen- dung Now(). Ghi chú: Now() là lấy ngày tháng năm hiện tại và một năm trung bình cĩ 365 ngày; Hiệu 2 giá trị ngày tháng là số ngày trơi qua giữa 2 ngày đĩ. 3.2.1.3 Ràng buộc tồn vẹn liên bộ, liên thuộc tính. Đây là loại RBTV cĩ liên quan tới nhiều bộ và cĩ thể tới nhiều thuộc tính của (các) bộ giá trị trong một quan hệ. Ví dụ 3.2.4: Trong ví dụ 3.2.1 vừa nêu, chúng ta thấy điểm thi khơng chỉ liên quan đến thuộc tính Lần-thi mà cịn liên quan tới điểm thi của lần thi trước đĩ nếu đã thi 1 hay 2 lần rồi. RBTV đầy đủ phải được diễn đạt bằng thuật tốn như sau: 33
34. R3: kq KQUẢ-THI / Nếu kq.Lần-thi = 1 thì 0 kq.Điểm-thi 10.0 hoặc: Nếu kq.Lần thi > 1 thì kq’ KQUẢ-THI sao cho kq’.Lần-thi = kq.Lần-thi - 1 và kq.Điểm-thi kq’.Điểm-thi. Ví dụ 3.2.5: Giả thiết thêm rằng, để xác định hệ số lương của một cán bộ - cơng chức Nhà nước căn cứ vào Ngạch cơng chức và Bậc được xếp, chúng ta cĩ thêm bảng quy định Ngạch, bậc và hệ số lương cán bộ cơng chức Nhà nước theo Nghị định 25CP của Thủ tướng Chính phủ: NGẠCH-BẬC-LƯƠNG (Mã-ngạch, Bậc, Hệ-số-lương). Tân từ: Ứng với một Ngạch cơng chức và một Bậc cụ thể thì cĩ một hệ số lương tương ứng (từ 1.0 đến 10.0). Việc biểu diễn ràng buộc tồn vẹn Hệ-số-lương phụ thuộc vào Ngạch và Bậc của quan hệ CBCC nêu trong ví dụ 3.2.3 nêu trên bằng thuật giải cĩ thể trở nên rắc rối. Người ta đã đưa thêm một cách biểu diễn mới để làm cho RBTV trở nên rõ ràng hơn, đĩ là cách biểu diễn RBTV bằng phụ thuộc hàm mà chúng ta sẽ trình bày rõ hơn trong mục sau của chương này. 3.2.2 Ràng buộc tồn vẹn định nghĩa trên nhiều quan hệ cơ sở. 3.2.2.1 Ràng buộc tồn vẹn về phụ thuộc tồn tại. Ràng buộc tồn vẹn về phụ thuộc tồn tại (Existential Dependency hay Referential Dependency) cịn được gọi là phụ thuộc về khĩa ngoại. Đây là loại RBTV khá phổ biến trong các CSDL bởi các quan hệ trong một CSDL luơn luơn cĩ mối quan hệ mật thiết với nhau. Bộ giá trị của quan hệ này được thêm vào một cách hợp lệ nếu tồn tại một bản ghi tương ứng của một quan hệ khác. Phụ thuộc tồn tại xảy ra nếu cĩ một trong hai trường hợp sau: (i) Cĩ sự hiện diện của khĩa ngoại. (ii) Cĩ sự lồng khĩa giữa các quan hệ. Ví dụ 3.2.6: Trong thể hiện của quan hệ CHITIẾT-HĐ, sự tồn tại của mỗi bộ giá trị cthđ đều phụ thuộc vào sự tồn tại của một bộ giá trị hđ trong thể hiện của quan hệ HĨAĐƠN sao cho hđ.Số-hĩa-đơn = cthđ.Số-hĩa-đơn, và phụ thuộc cả vào sự tồn tại của một bộ giá trị mh trong thể hiện của quan hệ DM_HÀNG sao cho mh.Mã-hàng = cthđ.Mã-hàng. Biểu diễn các RBTV này như sau: RBTV1 : “Mỗi bộ của CHITIẾT_HĐ phải cĩ một hĩa đơn với Số-hĩa-đơn tương ứng”: cthđ CHITIẾT_HĐ, hđ HĨAĐƠN sao cho cthđ.Số-hĩa-đơn = hđ.Số-hĩa-đơn. hoặc biểu diễn bằng cách khác: CHITIẾT_HĐ [ Số-hĩa-đơn ]  HĨAĐƠN [ Số-hĩa-đơn ] 34
35. RBTV2 : “Mỗi bộ của CHITIẾT_HĐ phải cĩ mã hàng thuộc về danh mục hàng”: cthđ CHITIẾT_HĐ, hh DM_HÀNG sao cho cthđ.Mã-hàng=hh.Mã-hàng hoặc biểu diễn bằng cách khác: CHITIẾT_HĐ [ Mã-hàng ]  DM_HÀNG [ Mã-hàng ] Ví dụ 3.2.7 : Trong CSDL về quản lý CBCC nêu trong ví dụ 3.2.3 và 3.2.5 ở trên, RBTV về phụ thuộc tồn tại giữa 2 quan hệ CBCC và NGẠCH-BẬC-LƯƠNG được xác định: cbcc CBCC, ng NGẠCH-BẬC-LƯƠNG sao cho (cbcc.Mã-ngạch = ng.Mã-ngạch)  (cbcc.Bậc = ng.Bậc) hoặc biểu diễn bằng cách khác: CBCC [ Mã-ngạch, Bậc ]  NGẠCH-BẬC-LƯƠNG [ Mã-ngạch, Bậc ] Ví dụ 3.2.8 : Trong CSDL về quản lý học viên đã nêu trong ví dụ trên, các RBTV về phụ thuộc tồn tại gồm: RBTV1 : “Mỗi LỚP-HỌC đều phải thuộc một KHOA nhất định”: lh LỚP-HỌC, kh KHOA sao cho lh.Mã-khoa = kh.Mã-khoa. hoặc biểu diễn qua phép chiếu quan hệ: LỚP-HỌC [ Mã-khoa ]  KHOA [ Mã-khoa ]. RBTV2 : “Mỗi HỌC-VIÊN đều phải thuộc một LỚP-HỌC nhất định”: hv HỌC-VIÊN, lh LỚP-HỌC sao cho hv.Mã-lớp = lh.Mã-lớp. hoặc biểu diễn qua phép chiếu quan hệ: HỌC-VIÊN [ Mã-lớp ]  LỚP-HỌC [ Mã-lớp ]. RBTV3 : “Mỗi KQUẢ-THI đều phải là của một HỌC-VIÊN nhất định”: kq KQUẢ-THI, hv HỌC-VIÊN sao cho kq.Mã-học-viên = hv.Mã-học-viên. hoặc biểu diễn qua phép chiếu quan hệ: KQUẢ-THI [ Mã-học-viên ]  HỌC-VIÊN [ Mã-học-viên ]. RBTV4 : “Mỗi mơn thi trong KQUẢ-THI đều phải cĩ tên trong danh sách các mơn học” : kq KQUẢ-THI, mh MƠN-HỌC sao cho kq.Mã-mơn = mh.Mã-mơn hoặc biểu diễn qua phép chiếu quan hệ: KQUẢ-THI [ Mã-mơn ]  MƠN-HỌC [ Mã-mơn ] Chúng ta cĩ thể thấy vế phải của phép tốn tập con (  ) là phép chiếu trên thuộc tính khĩa nội của một quan hệ, cịn vế trái là phép chiếu trên tập các thuộc tính khĩa ngoại của một quan hệ khác. Chính vì lẽ đĩ mà người ta cịn gọi RBTV loại này là RBTV về khĩa ngoại. Phát biểu tổng quát về loại RBTV này là như sau: + + + R và S là hai quan hệ định nghĩa trên các tập thộc tính R và S . KR  R là tập các + thuộc tính khĩa nội của quan hệ R; KS  S là tập các thuộc tính khĩa nội của quan hệ S; và 35
36. W  S+ là tập các thuộc tính khĩa ngoại của S đối với R. Khi đĩ ta cĩ phụ thuộc tồn tại của S vào R và được biểu diễn thơng qua phép chiếu: S [W]  R [W]. Nếu W  KS, thì ta nĩi rằng cĩ sự lồng khĩa giữa hai quan hệ R và S. Trong bảng tầm ảnh hưởng của loại RBTV này, các thao tác Thêm và Sửa một bộ giá trị của quan hệ R (vế phải của phụ thuộc tồn tại) khơng gây ra sự vi phạm RBTV (trừ khi cĩ sự lồng khĩa giữa R với một quan hệ khác), chỉ cĩ thao tác Xĩa bỏ một bộ giá trị của R mới cần cĩ sự kiểm tra RBTV. Ngược lại, thao tác Xĩa một bộ giá trị của S khơng gây ra sự vi phạm RBTV (trừ khi cĩ sự lồng khĩa của một quan hệ khác vào S), thao tác Thêm một bộ giá trị mới vào S luơn luơn phải được kiểm tra RBTV này; nếu W là các thuộc tính khĩa ngoại của S thì việc Sửa bộ giá trị của S trên các thuộc tính khĩa ngoại W vẫn phải kiểm tra RBTV; nếu cĩ sự lồng khĩa thì việc sửa khơng địi hỏi kiểm tra RBTV vì theo quy ước là khơng được sửa giá trị của thuộc tính khĩa. Bảng tầm ảnh hưởng cĩ 2 dạng ứng với 2 trường hợp trên như sau: a. Ứng với trường hợp khĩa ngoại: Quan hệ Thêm Sửa Xĩa R - - (*) + S + + (W) - a. Ứng với trường hợp lồng khĩa: Quan hệ Thêm Sửa Xĩa R - - (*) + S + - (*) - 3.2.2.2 Ràng buộc tồn vẹn liên bộ - liên quan hệ. Khi cĩ sự hiện diện của 1 thuộc tính mang tính chất tổng hợp (tức là giá trị của thuộc tính cĩ thể được tính tốn từ giá trị của các thuộc tính khác trên một hay nhiều bộ giá trị của các quan hệ trong CSDL), hay phụ thuộc tồn tại lồng khĩa thì cĩ RBTV liên quan hệ - liên bộ. Ví dụ 3.2.9 : Xét CSDL về quản lý học viên nêu trong ví dụ trên, RBTV liên quan hệ - liên bộ cĩ thể được xác định: “Với mọi bộ giá trị của LỚP-HỌC, nếu Số lượng học viên lớn hơn 0 thì số lượng này phải lớn hơn hay bằng tổng số bộ giá trị đếm được của các học viên cĩ cùng Mã lớp”. Đây chính là RBTV R6 đã nêu trong ví dụ trên. Biểu diễn hình thức của RBTV này như sau:  lh LỚP-HỌC thì: nếu lh.Số-học-viên > 0 thì: h.Số-học-viên = COUNT (hv HỌC-VIÊN, hv.Mã-lớp = lh.Mã-lớp). 36
37. Ví dụ 3.2.10 : Xét CSDL quản lý hĩa đơn bán hàng đã cho trong ví dụ trên với 3 quan hệ: 1) HĨAĐƠN (Số-hĩa-đơn, Số_chủng_loại_mặt_hàng, Tổng-trị-giá). 2) CHITIẾT_HĐ (Số-hĩa-đơn, Mã-hàng, Số-lượng-đặt, Đơn-giá, Trị-giá). 3) DM_HÀNG (Mã-hàng, Tên-hàng, Đơn-vị-tính). RBTV1 : “Số_chủng_loại_mặt_hàng = số bộ của CHITIẾT_HĐ cĩ cùng Số-hĩa-đơn” :  hđ HĨAĐƠN thì: hđ.Số-chủng-loại-mặt-hàng = COUNT (cthđ CHITIẾT_HĐ, cthđ.Số-hĩa-đơn = hđ.Số- hĩa-đơn) RBTV2 : “Tổng tất cả các Trị-giá của các mặt hàng trong CHITIẾT_HĐ cĩ cùng Số-hĩa- đơn phải bằng Tổng-trị-giá của hĩa đơn đĩ trong HĨAĐƠN”:  hđ HĨAĐƠN thì hđ.Tổng-trị-giá = SUM (cthđ.Trị-giá) đối với các cthđ CHITIẾT_HĐ sao cho : cthđ. Số-hĩa-đơn= hđ. Số-hĩa-đơn. Chúng ta cĩ thể nhận thấy trong CSDL này cĩ sự dư thừa thơng tin một cách cố ý. Đĩ là thuộc tính tính tốn Trị-giá của các mặt hàng trong chi tiết hố đơn bán hàng. Một trong những phương pháp kiểm định tính đúng đắn của dữ liệu được nhập vào là tổ chức nhập “thừa” dữ liệu tính tốn được (Computable Value) rồi so sánh với cơng thức tính tốn. Nếu cĩ sự sai sĩt nào trong các thành phần cĩ liên quan trong cơng thức thì lơgíc biểu thức sẽ khơng cịn phù hợp nữa. Bây giờ để đạt được dạng chuẩn (Normal Form) tốt hơn cho quan hệ CHITIẾT-HĐ, chúng ta cĩ thể loại bỏ thuộc tính Trị-giá. Khi đĩ RBTV2 được viết lại là:  hđ HĨAĐƠN thì hđ.Tổng-trị-giá = SUM (cthđ.Số-lượng-đặt * cthđ.Đơn-giá) đối với các cthđ CHITIẾT_HĐ sao cho : cthđ. Số-hĩa-đơn= hđ. Số-hĩa-đơn. 3.2.2.3 RBTV do sự hiện diện của chu trình. Bây giờ chúng ta biểu diễn cấu trúc CSDL dưới dạng đồ thị như sau: Mỗi nút của đồ thị biểu diễn một quan hệ hoặc một thuộc tính. (i) Quan hệ được biểu diễn bằng nút trịn trắng ( ), và (ii) Thuộc tính được biểu diễn bởi một nút trịn đen nhỏ hơn ( ). (iii) Tất cả các nút đều được chỉ rõ bằng tên của quan hệ hoặc thuộc tính. Thuộc tính thuộc một quan hệ được biểu diễn bởi một cung nối giữa nút trịn trắng và nút trịn đen đĩ. Nếu trên đồ thị chúng ta thấy xuất hiện một đường khép kín thì ta nĩi rằng trong lược đồ CSDL cĩ sự hiện diện của chu trình. Sự hiện diện này làm nảy sinh một vấn đề mới: Xác định khả năng đảm bảo tính nhất quán của dữ liệu, đĩ là một RBTV, do sự hiện diện của chu trình. 37
38. Ví dụ 3.2.11: Hãy xét lược đồ CSDL với các lược đồ quan hệ con: Q1 (A, B), Q2 (B, C), Q3 (C, D) và Q4 (A, C). Biểu diễn lược đồ CSDL bằng đồ thị như sau: Hình 3.2.1 Biểu diễn lược đồ CSDL bằng đồ thị để phát hiện chu trình Với a A thì qua quan hệ Q1 ta cĩ thể xác định được một giá trị b B. Vẫn với giá trị a A đĩ nhưng xác định qua con đường Q4 (để cĩ c C) rồi qua Q2 để cĩ b’. b’ và b cĩ nhất thiết phải giống nhau hay khơng? Một CSDL là nhất quán nếu qua các cách khác nhau chỉ xác định được 1 giá trị duy nhất của thuộc tính liên quan. Trong trường hợp này cĩ 3 vấn đề nảy sinh: Hai cách xác định (hoặc 2 con đường) mang ý nghĩa hồn tồn giống nhau. Cĩ một con đường phụ thuộc vào con đường kia, nghĩa là tập hợp một số thuộc tính thơng qua một con đường là tập con của các thuộc tính đĩ thơng qua con đường kia. Hai con đường độc lập nhau thì chu trình ở đây là chu trình giả, và như vậy khơng cĩ ràng buộc tồn vẹn do sự hiện diện của chu trình. Ví dụ 3.2.12: Giả sử các quan hệ Q1, Q2, Q3, và Q4 được dịnh nghĩa trên các thuộc tính như sau: Q1 (Mã-nhân-viên, Mã-phịng) Q2 (Mã-phịng, Mã-đề-án) Q3 (Mã-đề-án, Tên-đề-án) Q4 (Mã-đề-án, Mã-nhân-viên) Đồ thị biểu diễn CSDL này như trong hình 3.2.2: Giả thiết 1: Với tân từ sau: Mỗi nhân viên (thể hiện qua Mã-nhân-viên) được phân cơng vào tất cả các đề án do phịng đĩ (thể hiện qua Mã-phịng) phụ trách. Hình 3.2.2. Lược đồ CSDL được biểu diễn dưới dạng đồ thị 38
39. (i) Hai con đường mang ý nghĩa hồn tồn giống nhau. Đường dài hơn Q1 kết nối với Q2 (ký hiệu là Q1 I> <I Q2 [Mã-nhân-viên, Mã-đề-án] Giả thiết 3: Với tân từ sau: Mỗi nhân viên (thể hiện qua Mã-nhân-viên) được phân cơng vào những đề án (thể hiện qua Mã-phịng) bất kỳ. Hai con đường hồn tồn độc lập nhau, khơng liên quan gì tới nhau, mang ý nghĩa hồn tồn khác nhau, do đĩ khơng cĩ RBTV. 3.3 Truy vấn dữ liệu quan hệ. Sáu phép tốn cơ bản trên tập hợp được áp dụng trên tập các bộ giá trị của các quan hệ, đĩ là: Hợp (Union), Hiệu (Minus), Giao (Intersection), Tích Đề-các (Cartesian), phép chia (Division) và phép bù (Complement). Giả thiết: U = { A1, A2, A3, An } là tập các thuộc tính. R và S là 2 quan hệ được định nghĩa trên U cĩ cùng thứ tự của các thuộc tính. Và ở đây chúng ta luơn luơn giả thiết là R và S cĩ số lượng hữu hạn các bộ giá trị. 3.3.1. Phép hợp 2 quan hệ (Union). Hợp của hai quan hệ R và S - được ký hiệu là R S - là một quan hệ Q xác định trên tập thuộc tính U, cĩ cùng thứ tự thuộc tính như trong quan hệ R và S, được định nghĩa như sau: Q = R S = { t / t Ỵ R hoặc t Ỵ S } Nĩi một cách nơm na, hợp của 2 quan hệ R và S là một quan hệ cĩ cùng ngơi với quan hệ R và S với các bộ giá trị bằng gộp các bộ giá trị của cả R và S; những bộ giá trị trùng nhau chỉ được giữ lại 1 bộ. Ví dụ 3.3.1: Quan hệ ĐơnvịA cĩ các bộ giá trị sau: 39
40. MãSố Họ-tên Phái Chức- Lương MãĐV danh 100 Nguyễn Văn Nam Nam Giám đốc 2.500.000 10 101 Hồng Thị Xuân Nữ Kế tốn 1.700.000 10 trưởng 103 Đặng Ngọc Chiến Nữ Thư ký 1.000.000 10 105 Phan Kỳ Nhân Nam Lái xe 700.000 10 Quan hệ ĐơnvịB cĩ các bộ giá trị sau: MãSố Họ-tên Phái Chức-danh Lương MãĐV 210 Nguyễn Thị Cao Nữ Trưởng phịng 1.200.000 30 101 Hồng Thị Xuân Nữ Kế tốn trưởng 1.700.000 10 221 Đỗ Hữu Ngọc Nam Phĩ phịng 1.000.000 30 233 Hồng Thao Nam Chuyên viên 1.000.000 30 Hợp của hai quan hệ trên cho kết quả là quan hệ NV-CTy cĩ 7 bộ giá trị sau: MãSố Họ-tên Phái Chức-danh Lương MãĐV 100 Nguyễn Văn Nam Nam Giám đốc 2.500.000 10 101 Hồng Thị Xuân Nữ Kế tốn 1.700.000 10 trưởng 103 Đặng Ngọc Chiến Nữ Thư ký 1.000.000 10 105 Phan Kỳ Nhân Nam Lái xe 700.000 10 210 Nguyễn Thị Cao Nữ Trưởng phịng 1.200.000 30 221 Đỗ Hữu Ngọc Nam Phĩ phịng 1.000.000 30 233 Hồng Thao Nam Chuyên viên 1.000.000 30 Bộ giá trị cĩ mã số nhân viên là 101 xuất hiện 2 lần trong 2 quan hệ ĐơnvịA và ĐơnvịB, nhưng trong quan hệ NV-Cty bộ này chỉ được giữ lại 1 lần, do đĩ chỉ cịn 7 bộ giá trị. 3.3.2. Phép trừ hai quan hệ (Minus). Hiệu của hai quan hệ R và S, được ký hiệu là R - S, là một quan hệ Q xác định trên tập thuộc tính U, cĩ cùng thứ tự thuộc tính như trong quan hệ R và S, được định nghĩa như sau: Q = R - S = { t / t Ỵ R và t S } 40
41. Nĩi một cách nơm na, hiệu của 2 quan hệ R và S là một quan hệ cĩ cùng ngơi với quan hệ R và S với các bộ giá trị là các bộ giá trị của R sau khi đã loại bỏ đi các bộ cĩ mặt trong quan hệ S. Ví dụ 3.3.2: Với hai quan hệ như trên, hiệu của ĐơnvịA và ĐơnvịB là quan hệ NV-CtyA với các bộ sau: MãSố Họ-tên Phái Chức- Lương MãĐV danh 100 Nguyễn Văn Nam Nam Giám 2.500.000 10 đốc 103 Đặng Ngọc Chiến Nữ Thư ký 1.000.000 10 105 Phan Kỳ Nhân Nam Lái xe 700.000 10 3.3.3. Giao của 2 quan hệ (Intersection). Giao của hai quan hệ R và S, được ký hiệu là R Ç S, là một quan hệ Q xác định trên tập thuộc tính U, cĩ cùng thứ tự thuộc tính như trong quan hệ R và S, được định nghĩa như sau: Q = R Ç S = { t / t Ỵ R và t Ỵ S } Nĩi một cách nơm na, giao của 2 quan hệ R và S là một quan hệ cĩ cùng ngơi với quan hệ R và S với các bộ giá trị là các bộ giống nhau của cả hai quan hệ R và S. Ví dụ 3.3.3: Với hai quan hệ như trên, hiệu của ĐơnvịA và ĐơnvịB là quan hệ NV-CtyA với các bộ sau: MãSố Họ-tên Phái Chức-danh Lương MãĐV 101 Hồng Thị Xuân Nữ Kế tốn trưởng 1700.000 10 3.3.4. Tích Đề-các của 2 quan hệ (Cartesian). R (A1, A2, , An) và S (B1, B2, , Bm) là hai quan hệ cĩ số bộ giá trị hữu hạn. Tích Đề- các của hai quan hệ R và S, được ký hiệu là R x S, là một quan hệ Q xác định trên tập thuộc tính của R và S (với n + m thuộc tính) và được định nghĩa như sau: Q = R x S = { t / t cĩ dạng (a1, a2, , an, b1, b2, , bm) trong đĩ (a1, a2, , an) Ỵ R và (b1, b2, , bm) Ỵ S } Nĩi một cách nơm na, tích Đề-các của 2 quan hệ R và S là một quan hệ Q cĩ số ngơi bằng tổng số ngơi của R và S, với các bộ giá trị gồm 2 phần: phần bên trái là một bộ giá trị của R và phần bên phải là một bộ giá trị của S. Như vậy, nếu R cĩ n1 bộ giá trị và S cĩ n2 bộ giá trị, thì Q sẽ cĩ n1 x n2 bộ giá trị. Ví dụ 3.3.4: R (A, B, C) R x S = Q (A, B, C, D, E, F) a1, b1 c1 a1 b1 c1 d1 e1 f1 41
42. a2, b2 c2 a1 b1 c1 d2 e2 f2 a3, b3 c3 a2 b2 c2 d1 e1 f1 và S (D, E, F) a2 b2 c2 d2 e2 f2 d1 e1 f1 a3 b3 c3 d1 e1 f1 d2 e2 f2 a3 b3 c3 d2 e2 f2 3.3.5. Phép chia hai quan hệ (Division). R là quan hệ n ngơi và S là quan hệ m ngơi (n > m và S ¹ Ỉ ), cĩ m thuộc tính chung (giống nhau về mặt ngữ nghĩa, hoặc các thuộc tính cĩ thể so sánh được) giữa R và S. Phép chia 2 quan hệ R và S, ký hiệu là R¸ S, là một quan hệ Q cĩ n - m ngơi được định nghĩa như sau: Q = R¸ S = { t / sc: " uỴ S, (t,u) Ỵ R } Sử dụng định nghĩa phép tích Đề-các, cĩ thể định nghĩa phép chia hình thức hơn như sau: R¸ S = Q sao cho Q x S Í R (với giả thiết thêm là thứ tự thuộc tính của R, S, Q là khơng quan trọng). Ví dụ 3.3.5: R (A, B, C, D) S (C, D) R¸ S= (A, B) a b c d c d a b a b e f e f c d b c e f c d c d c d e f a b d e Ví dụ 3.3.6: Cho quan hệ về khả năng lái các loại máy bay của các phi cơng: KHẢ-NĂNG (Số-hiệu-phi-cơng, Số-hiệu-máy-bay) Số-hiệu-phi- Số-hiệu-máy- cơng bay 32 102 30 101 30 103 32 103 33 100 30 102 42
43. 31 102 30 100 31 100 Câu hỏi: Cho biết các phi cơng cĩ khả năng lái được cả 3 loại máy bay 100, 101, và 103 ? Trả lời: Đĩ là thương của phép chia quan hệ KHẢ-NĂNG cho quan hệ MÁY-BAY (Số-hiệu- máy-bay): 100 101 103 Và kết quả là quan hệ PHI-CƠNG (Số-hiệu-phi-cơng) cĩ 1 bộ giá trị (30). 3.3.6. Phép bù của một quan hệ (Complement). Cho quan hệ R (A1, A2, , An) với các miền giá trị của thuộc tính Ai là MGT(Ai). Phép bù của quan hệ R là quan hệ Q xác định trên tập thuộc tính R+, ký hiệu là R, được định nghĩa như sau: Q = R = { t (a1, a2, , an) và ai Ỵ MGT(Ai) " i=1 n / t R } Nghĩa là tập tất cả các bộ giá trị cĩ thể cĩ của tích Đề-các miền giá trị MGT(Ai) nhưng chưa cĩ mặt trong thể hiện của quan hệ R. Quan hệ bù của một quan hệ cĩ số lượng bộ giá trị là rất lớn, vì vậy trong thực tế rất ít hệ quản trị CSDL cài đặt phép tốn này. Ví dụ 3.3.7: Quan hệ CUNG-CẤP (Mã-NCC, Hàng-hĩa) với Mã-NCC = { S1, S2, S3 } và các Hàng-hĩa cung cấp là { Đinh, Ốc, Vít } Mã-NCC Hàng-hĩa S1 Đinh S1 Vít S2 Ốc S2 Đinh S3 Vít Quan hệ bù của quan hệ CUNG-CẤP cĩ các bộ giá trị sau: 43
44. Mã-NCC Hàng-hĩa S1 Ốc S2 Vít S3 Ốc S3 Đinh 3.4 Các thao tác cơ sở trên các quan hệ Ở mục trên chúng ta đã trình bày một cách tiếp cận trong việc hình thành ngơn ngữ truy vấn dữ liệu (Data Query Language) qua các phép tốn tập hợp trên các bộ giá trị của các quan hệ. Các phép tốn đặc biệt trên các quan hệ sau đây là một cách tiếp cận khác trong việc biểu diễn câu hỏi của ngơn ngữ đại số quan hệ, chúng cũng cĩ liên quan chặt chẽ tới những thao tác cơ bản Thêm (Insert), Sửa (Update) và Xĩa (Delete) các bộ giá trị trên quan hệ. 3.4.1. Phép chiếu (Projection) Giả sử R là một quan hệ xác định trên tập thuộc tính U = { A1, A2, , An }. X Í U. Phép chiếu quan hệ R trên tập con các thuộc tính X là một quan hệ Q xác định trên tập thuộc tính X, ký hiệu là R [X], được định nghĩa như sau: Q = R [X] = { q / $ t Ỵ R: q = t.X } Đơi khi người ta cịn ký hiệu phép chiếu quan hệ R trên tập thuộc tính X bằng P X(R). Nếu X = { Ai1, Ai2, Aim } thì cĩ thể viết cụ thể là P Ai1Ai2 Aim(R) [3]. Ngữ nghĩa: Trích từ R một số thuộc tính nào đĩ để tạo thành một quan hệ mới. Số ngơi của quan hệ mới này bằng số thuộc tính của tập con X. Các bộ giá trị của các cột được trích nếu giống nhau sẽ được loại bỏ để chỉ giữ lại một bộ duy nhất (trong thể hiện của quan hệ mới khơng cĩ 2 bộ nào giống nhau). Phép chiếu được biểu diễn bằng sơ đồ như sau: Hình 3.4.1. Sơ đồ biểu diễn phép chiếu Ví dụ 3.4.1: Cho quan hệ về các nhân viên đang làm việc tại một Cơng ty (EMPLOYEE) như sau: Empno Name Job Salary Comm Deptno Sex 100 Wilson Clrk 1.700 10 M 44
45. 101 Smith Slsm 2.500 1.300 40 F 103 Reed Anlt 3.500 30 M 105 Watson Mngr 4.500 0 30 M 109 Allen Mngr 3.800 8.000 40 F 110 Turner Clrk 1.800 50 M 200 Chen Mngr 2.900 10 F 210 Ramirez Mngr 3.600 50 M 213 McDonnel Clrk 1.625 60 M 214 Simpson Drvr 825 60 M 215 Di Salvo Spvr 2.700 60 M 220 Schwartz Slsm 4.200 5.300 40 F Câu hỏi: Hãy cho biết các loại cơng việc mà các nhân viên của Cty đang làm? Câu trả lời: Chiếu quan hệ EMPLOYEE lên thuộc tính Job, chỉ giữ lại các giá trị khác nhau. Kết quả là một quan hệ JOBEMPLOYEE = EMPLOYEE [JOB] gồm 1 cột 6 dịng với các giá trị { Clrk, Slsm, Anlt, Mngr, Drvr, và Spvr }. 3.4.2. Phép chọn (Selection) Phép chọn cho phép chọn lựa chỉ những bản ghi thỏa mãn một điều kiện Đ nào đĩ để đưa vào quan hệ kết quả. Điều kiện Đ chính là một biểu thức lơgíc cho kết quả hoặc là đúng (True) hoặc là sai (False) khi đánh giá trên các bộ giá trị của quan hệ nguồn; nĩ là tổ hợp của các biểu thức lơgic cơ sở. Mỗi biểu thức cơ sở chứa một phép so sánh: nhỏ hơn ( ), lớn hơn hay bằng (>=), bằng nhau (=) và khác (¹ hoặc <>) cĩ dạng: Thuộc tính so sánh với thuộc tính. Thuộc tính so sánh với hằng (literal) Các biểu thức lơgic cơ sở được tổ hợp với nhau bởi các phép tốn lơgic: phép "và" lơgic - hay cịn gọi là phép nối liền ( - conjunction), phép "hoặc" lơgic - hay cịn gọi là phép nối rời ( - disjunction) và phép phủ định ( - not). Giả sử R (A1, A2, An) là một quan hệ, và Đ là một điều kiện (hoặc cịn gọi là một phát biểu) dựa trên tập con thuộc tính R+. Đánh giá điều kiện Đ trên bộ giá trị t Ỵ R được ký hiệu là E 45
46. (tĐ) hoặc để đơn giản, ta cĩ thể viết Đ(t). Phép chọn các bản ghi của R thỏa mãn điều kiện Đ là một quan hệ Q cĩ cùng ngơi với R, ký hiệu là R:Đ, được định nghĩa hình thức như sau: Q = { t Ỵ R / Đ (t) = đúng } Phép chọn cũng cĩ thể được ký hiệu theo C.J.Date [3] bởi dấu sigma (s ): s F(R). Phép chọn được biểu diễn bằng sơ đồ như sau: Hình 3.4.2. Sơ đồ biểu diễn phép chọn Ví dụ 3.4.2: Cũng với quan hệ EMPLOYEE như trong ví dụ 3.4.1, điều kiện chọn là: "những người làm lãnh đạo các phịng ban và cĩ mức lương tháng từ 3.500 $ trở lên". Bằng ngơn ngữ đại số qua hệ với phép tốn chọn, câu hỏi được diễn đạt như sau: EMPLOYEE : (Job = Mngr) (Salary >= 3500) . Kết quả là một quan hệ gồm 3 dịng sau: Empno Name Job Salary Comm Deptno Sex 105 Watson Mngr 4.500 30 M 109 Allen Mngr 3.800 8.000 40 F 210 Ramirez Mngr 3.600 50 M 3.5 Các phép tốn khác. Mục này trình bày 3 phép tốn kết nối mở rộng khác đặc biệt quan trọng, mà bản chất của chúng vẫn là kết nối. Chúng đã được cài đặt trong một số hệ quan trị CSDL như MicroSoft Access, SQL-Server, Oracle. Các phép kết nối đĩ là: Kết nối nội (Inner Join), Kết nối trái (Left Join) và Kết nối phải (Right Join). 3.5.1. Phép kết nối nội (Inner Join). Thực chất là phép kết nối bằng đã trình bày trên. Tuy nhiên, ngay cả trong trường hợp hai thuộc tính so sánh cĩ cùng tên thì kết quả phép kết nối vẫn giữ lại 2 tên thuộc tính đĩ. Ví dụ 3.5.1.1: Cho 2 quan hệ R (A, B, C) và S (A, D, E, F) với các bộ giá trị như dưới đây. Kết quả của phép kết nối nội được cho trong bảng phía bên phải. 46
47. R (A B C) S (A D E F) R S =Q (A B C A D E F) a1 b1 c1 a1 d1 e1 f1 a1 b1 1 a1 d1 e1 f1 a2 b2 c2 a2 d2 e2 f2 a2 b2 c2 a2 d2 e2 f2 a3 b3 c3 a4 d4 e4 f4 a7 b7 c7 a7 d7 e7 f7 a5 b5 c5 a6 d6 e6 f6 a7 b7 c7 a7 d7 e7 f7 Ví dụ 3.5.1.2: Phép kết nối nội 2 quan hệ ĐƠN-VỊ và LOẠI-ĐV cho kết quả là một bảng sau: Mã- Tên-đơn-vị Loại Loại Tên-loại-hình ĐV 10002 Trường K.thuật nghiệp vụ máy tính 3 3 SN hồn tồn 10003 Phịng quản lý hành chánh 2 2 Hành chánh 10022 Văn phịnh đại diện Thanh niên 2 2 Hành chánh 10070 Ban quản lý vốn sinh viên 5 5 Hạch tốn đ.lập 10071 Lực lượng quản lý thị trường 5 5 Hạch tốn đ.lập 3.5.2. Phép kết nối trái (Left Join) Giả sử cĩ 2 quan hệ R (A1, A2, , An) và S (B1, B2, , Bm). t = (a1, a2, , an) và u = (b1, b2, , bm) là hai bộ giá trị của R và S. Gọi v là bộ ghép nối u vào t (hay bộ giá trị t và u được "xếp cạnh nhau") và ký hiệu là: v = (t, u) = (a1, a2, , an, b1, b2, , bm). Bộ tNULL = (NULL, NULL, , NULL) là một bộ đặc biệt của R gồm n giá trị của các thuộc tính A1, A2, , An đều là khơng xác định và uNULL = (NULL, NULL, , NULL) là một bộ đặc biệt của S gồm m giá trị của các thuộc tính B1, B2, , Bm đều là khơng xác định. A Ỵ R+ và B Ỵ S+ là hai thuộc tính cĩ thể so sánh được. Phép kết nối trái hai quan hệ R với S trên các thuộc tính A và B với phép so sánh bằng ( = ), với giả thiết là giá trị cột R[A] cĩ thể so sánh tương đương được với mỗi giá trị của cột S[B], được định nghĩa là: R S = { v = (t, u) ½ (t Ỵ R , u Ỵ S và t.A q u.B) hoặc (t Ỵ R, u = uNULL với t.A S[B]) } nghĩa là, tất cả các bộ v cĩ được nhờ cách đặt bộ giá trị của R và S xếp cạnh nhau, nếu cĩ giá trị giống nhau trên 2 thuộc tính kết nối; và các bộ v cĩ được nhờ cách đặt bộ của R với các bộ NULL của S, nếu khơng tìm được giá trị tương ứng của thuộc tính kết nối trên quan hệ S. Ví dụ 3.5.2.1: 47
48. Với hai quan hệ R và S cùng các bộ giá trị của chúng đã được cho trong ví dụ 3.5.1, kết quả của phép kết nối trái của R và S là: R S = Q (A, B, C, A, D, E, F) a1 b1 1 a1 d1 e1 f1 a2 b2 c2 a2 d2 e2 f2 a3 b3 c3 - - - - a5 b5 c5 - - - - a7 b7 c7 a7 d7 e7 f7 Ký hiệu dấu trừ (-) trong các thuộc tính của S được hiểu là giá trị khơng xác định (giá trị Null). Các dịng cĩ giá trị thuộc tính A của R là a3 và a5 khơng tìm được giá trị của thuộc tính A tương ứng trong quan hệ S, nên phần cịn lại của nĩ được để là khơng xác định. Qua bảng kết quả trình bày trên, chúng ta thấy ý nghĩa của phép tốn này là nhằm xác định các bộ giá trị của quan hệ bên trái nhưng khơng cĩ bộ giá trị tương ứng trong quan hệ phía bên phải. Ví dụ 3.5.2.2: Giả sử chúng ta thêm một bộ mới cho bảng ĐƠN-VỊ gồm cĩ Mã-ĐV là 10090, Tên-đơn-vị là Hội khuyến nơng Quận X và loại hình đơn vị là 7. Khi đĩ phép kết nối trái (Left Join) hai quan hệ ĐƠN-VỊ và LOẠI-ĐV cho kết quả là quan hệ cĩ thể hiện như sau: Mã- Tên-đơn-vị Loại Loại Tên-loại-hình ĐV 10002 Trường K.thuật nghiệp vụ máy tính 3 3 SN hồn tồn 10003 Phịng quản lý hành chánh 2 2 Hành chánh 10022 Văn phịnh đại diện Thanh niên 2 2 Hành chánh 10070 Ban quản lý vốn sinh viên 5 5 Hạch tốn đ.lập 10071 Lực lượng quản lý thị trường 5 5 Hạch tốn đ.lập 10090 Hội khuyến nơng Quận X 7 Null Null Bởi vì trong quan hệ LOẠI-ĐV khơng cĩ bộ nào cĩ giá trị ở cột Loại là 7, do đĩ ở dịng cuối cùng của quan hệ trên, một bộ gồm tồn giá trị NULL ở cả hai cột Loại và Tên-loại-hình được đặt cạnh bộ giá trị mới được bổ sung trong thể hiện của quan hệ ĐƠN-VỊ. Ứng dụng của phép kết nối này cĩ thể thấy rõ trong bài tốn quản lý CBVC nêu trên: Trước hết cần xác định những đơn vị cĩ loại hình tổ chức khơng cĩ trong danh mục Loại-ĐV. Câu trả lời rất đơn giản: chỉ việc chọn ra các dịng của bảng kết nối cĩ giá trị NULL ở cột Loại trong phần đuơi của bộ giá trị là NULL. Cũng trong bài tốn quản lý CBVC nêu trên: Theo yêu cầu quản lý, mỗi CBVC cĩ tên trong danh sách của đơn vị phải cĩ một bản khai lý lịch, tức là một bộ giá trị về lý lịch chi tiết. Mỗi 48
49. CBVC cĩ một mã số CBVC để phân biệt và nhận dạng đồng thời khĩa của quan hệ lý lịch cũng là mã số này. Cĩ thể ứng dụng phép kết nối trái để xác định những CBVC nào cĩ tên trong danh sách mà chưa cĩ lý lịch trong CSDL. 3.5.3. Phép kết nối phải (Right Join) Vẫn với các quan hệ R, S; các thuộc tính A, B; và các bộ giá trị v, t, u, tNULL, uNULL được xác định như trên. Phép kết nối phải hai quan hệ R với S trên các thuộc tính A và B với phép so sánh =, với giả thiết là giá trị cột R[A] cĩ thể so sánh tương đương được với mỗi giá trị của cột S[B], được định nghĩa là: R S = { v = (t, u) ½ (t Ỵ R , u Ỵ S và t.A q u.B) hoặc (t = tNULL , u Ỵ S, với t.B R[A]) } nghĩa là, tất cả các bộ v cĩ được nhờ cách đặt bộ giá trị của R và S xếp cạnh nhau nếu chúng cĩ giá trị giống nhau trên 2 thuộc tính kết nối, và các bộ NULL của R với các bộ của S, nếu khơng tìm được giá trị tương ứng của thuộc tính kết nối trên quan hệ R. Ví dụ 3.5.3.1: Giả sử với các quan hệ R và S cùng các bộ giá trị của chúng được xác định như trong ví dụ 5.4.2 nêu trên. Kết quả của phép kết nối phải R với S là quan hệ với các bộ giá trị sau: R S = Q (A, B, C, A, D, E, F) a1 b1 1 a1 d1 e1 f1 a2 b2 c2 a2 d2 e2 f2 - - - a4 d4 e4 f4 - - - a6 d6 e6 f6 a7 b7 c7 a7 d7 e7 f7 Ký hiệu dấu trừ (-) trong các thuộc tính của R được hiểu là giá trị khơng xác định (giá trị Null). Các dịng cĩ giá trị tại thuộc tính A của S là a4 và a6 khơng tìm được giá trị của thuộc tính A tương ứng trong quan hệ R, do đĩ phần đầu của nĩ được để là khơng xác định. Qua bảng kết quả trình bày trên, chúng ta thấy ý nghĩa của phép tốn này là nhằm xác định các bộ giá trị của quan hệ bên phải khơng cĩ bộ giá trị tương ứng trong quan hệ phía bên trái. Ví dụ 3.5.3.2: Giả sử chúng ta thêm một bộ mới cho quan hệ LOẠI_ĐV gồm cĩ mã Loại là 6, Tên- loại-hình là Cơ quan Đảng / Đồn. Khi đĩ phép kết nối phải (Right Join) hai quan hệ ĐƠN-VỊ và LOẠI-ĐV cho kết quả là quan hệ cĩ thể hiện như sau: Mã- Tên-đơn-vị Loại Loại Tên-loại-hình ĐV 10002 Trường K.thuật nghiệp vụ máy tính 3 3 SN hồn tồn 10003 Phịng quản lý hành chánh 2 2 Hành chánh 49
50. 10022 Văn phịnh đại diện Thanh niên 2 2 Hành chánh 10070 Ban quản lý vốn sinh viên 5 5 Hạch tốn đ.lập 10071 Lực lượng quản lý thị trường 5 5 Hạch tốn đ.lập 10090 Hội khuyến nơng Quận X 7 Null Null Null Null Null 6 Cơ quan Đảng Vì khơng cĩ đơn vị nào cĩ loại hình tổ chức đơn vị là 6, nên ở dịng cuối cùng trong bảng trên, cả 3 cột thuộc phần của quan hệ ĐƠN-VỊ đã bị để trống bằng các giá trị Null khơng xác định. Một ứng dụng của phép kết nối này là: xác định những CBVC nào cĩ lý lịch trong CSDL (do nhập sai mã số CBVC) nhưng khơng cĩ tên trong danh sách (tức là các lý lịch vơ chủ). Hoặc: xác định các loại hình đơn vị (Loại) mà khơng cĩ đơn vị nào thuộc vào. Trong một số ngơn ngữ truy vấn CSDL (MicroSoft SQL-Server, Oracle ) người ta gọi 2 phép tốn kết nối trái (Left Join) và kết nối phải (Right Join) bằng chung một từ là "phép kết nối ngồi" (Outer Join). Mục đích của phép kết nối này là cho phép giữ lại tất cả các bản ghi (hoặc bộ giá trị) của 2 quan hệ cĩ cùng giá trị của các thuộc tính kết nối và những bộ giá trị của cả 2 quan hệ khơng tìm được bộ giá trị giống nhau trên các thuộc tính kết nối thuộc quan hệ đối ứng. 3.6 Thiết kế cơ sở dữ liệu mức logic: chuyển sơ đồ E-R thành lược đồ cơ sở dữ liệu quan hệ. Chuẩn hĩa - Ta tiến hành chuẩn hĩa chỉ với các quan hệ được thêm vào ở trường hợp 2. * Chuẩn hĩa: Xét quan hệ lần lượt cĩ phải là chuẩn 1, chuẩn 2, chuẩn 3 hay khơng. Trường hợp 1) Nếu quan hệ khơng phải là chuẩn 1 - Phân rã quan hệ được xét thành 2 quan hệ: * Quan hệ 1: + Gồm các thuộc tính lặp + Phần khĩa chính xác định nĩ * Quan hệ 2: + Các thuộc tính cịn lại + Tồn bộ khĩa khơng chứa thuộc tính lặp Ví dụ: HĨA ĐƠN (SốHĐ, NgàyHĐ, MaK, MaH*, TênH*, ĐơnvịH*, Sốlượngđơn*) (SốHĐ, MãH) (TênH, ĐơnvịH, Sốlượngđơn) Quan hệ 1: DỊNGĐƠN (SốHĐ, MãH, TênH, ĐơnvịH, Sốlượngđơn) Quan hệ 2: HĨAĐƠN (SốHĐ, NgàyHĐ, MãK) Trường hợp 2) Nếu quan hệ chuẩn 1, chưa chuẩn 2: 50
51. - Phân rã nĩ thành 2 quan hệ: * Quan hệ 1, Gồm: + Các thuộc tính phụ thuộc phần khĩa + Phần khĩa đĩ * Quan hệ 2, gồm: + Các thuộc tính cịn lại + Tồn bộ khĩa Ví dụ: HĨAĐƠN (SốHĐ, NgàyHĐ, MãK, TênK, ĐịachỉK) - Khĩa chính: (SốHĐ, MãK) (MãK) (TênK, ĐịachỉK) Quan hệ 1: KHÁCH (MãK, TênK, ĐịachỉK) Quan hệ 2: HĨAĐƠN (SốHĐ, NgàyHĐ, MãK) Trường hợp 3) Nếu quan hệ là chuẩn 2, chưa chuẩn 3: - Phân rã thành 2 quan hệ: * Quan hệ 1, gồm: + Các thuộc tính phụ thuộc bắt cầu + Cầu Quan hệ 2, gồm: + Các thuộc tính cịn lại + Cầu Ví dụ: THỰCTẬP (MãHV, MãGVHD, MơnThựchành) - Quy tắc nghiệp: + Mỗi sinh viên được phân cơng một giáo viên hướng dẫn. + Mỗi giáo viên hướng dẫn hướng dẫn thực tập những mơn xác định. Ta thấy quan hệ là chuẩn 2, chưa chuẩn 3 vì cĩ phụ thuộc bắt cầu, cầu là MãGVHD. Quan hệ 1: GVHƯỚNGDẪN (MãGVHD, MơnThựchành) Quan hệ 2: THỰCTẬP (MáV, MãGVHD) Hợp nhất: * Bỏ đi các quan hệ lặp. * Hợp nhất các quan hệ khác nhau chút ít, cùng khĩa. Vẽ sơ đồ: - Mỗi quan hệ vẽ hình chữ nhật gồm 2 phần: + Phần trên: Tên quan hệ + Phần dưới: Để đơn giản chỉ ghi tên các khĩa chính và khĩa ngoại. - Để phân biệt, trước khĩa chính đặt dấu #, thuộc tính khĩa ngoại thì gạch chân bằng nét đứt. 51
52. - Vẽ đường hình chữ nhật kết gĩc quan hệ, theo nguyên tắc: + Hai quan hệ được nối với nhau nếu 1 quan hệ chứa khĩa chính của quan hệ kia (là khĩa ngoại của nĩ). - Xác định các bản số: Dựa vào ngữ nghĩa, dựa vào khĩa ngoại. Ví dụ: MãK TênK ĐịachỉK KHÁCH Ngàygiao SốHĐ Sốphiếugiao Ngàyđặt Đặt Giao Sốlượnggiao Sốlượngđặt HÀNG Đơnvị MãH TênH Mơtả - Thiết kế mơ hình Logic (mơ hình quan hệ) từ mơ hình quan niệm (Mơ hình E-R) đã cho. * Bước a: Biểu diễn các thực thể KHÁCH (MãK, TênK, ĐịachỉK) HÀNG (MãH, TênH, MơtảH, ĐơnvịH) * Bước b: Biểu diễn các mối quan hệ: - Đối với mối quan hệ “Đặt” ta thêm 1 quan hệ mới: ĐƠNHÀNG (MãK, MãH, SốHĐ, Ngàyđặt, sốlượngđặt) - Đối với mối quan hệ “Giao” tương tự ta cĩ: PHIẾUGIAO (MãK, MãH, Sốphiếugiao, Ngàygiao, Đơnvịgiao, Sốlượnggiao) * Bước c: Chuẩn hĩa: 1. ĐƠNHÀNG (SốHĐ, MãK, MãH*, Ngàyđặt, Sốlượngđặt) - Khĩa chính: (SốHĐ, MãH) Chưa chuẩn 1 vì cĩ thuộc tính lặp Quan hệ 1: DỊNGĐƠN (SốHĐ, MãH, Sốlượngđặt) Quan hệ 2: ĐƠNHÀNG (SốHĐ, MãK, Ngàyđặt) 2. PHIẾUGIAO (Sốphiếu, MãK, MãH*, Ngàygiao, Đơngiágiao*, Sốlượnggiao*) * Quan hệ 1: DỊNGPHIẾU (Sốphiếu, MãH, Đơngiágiao, Sốlượnggiao) * Quan hệ 2: PHIẾUGIAO (Sốphiếu, MãK, Ngàygiao) * Bước d: Vẽ sơ đồ mơ hình dữ liệu: 52
53. KHÁCH ĐƠNHÀNG DỊNG ĐƠN #MãK || | | || #MãH MãK #MãH Bài tập: Khảo sát hệ thống quản lý sinh viên của trường, hãy vẽ mơ hình thực thể quan hệ (mơ hình E-R) và chuyển đổi mơ hình đĩ sang mơ hình Logic (mơ hình quan hệ). BÀI 4 NGƠN NGỮ TRUY VẤN SQL Mã bài : ITPRG3_02.4 Giới thiệu : Học viên học xong bài này sẽ nắm được các cách khác nhau để khai thác thơng tin trong CSDL thơng qua một lệnh mạnh nhất của ngơn ngữ CSDL. Hầu hết các Hãng cung cấp phần mềm quản trị CSDL đều cài đặt câu lệnh này theo kỹ thuật đặc biệt, được gọi là Rushmore, do đĩ một CSDL cĩ thể lớn tới hàng Giga bytes nhưng việc truy vấn thơng tin cĩ thể đạt được kết quả đơi khi gần như tức thời. Mục tiêu thực hiện: Học xong bài này học viên sẽ cĩ khả năng: - Giải thích các khía cạnh: ngơn ngữ định nghĩa dữ liệu (DDL), ngơn ngữ thao tác dữ liệu (DML), SQL động và được nhúng, an tồn, quản lý giao tác, truy cập cơ sở dữ liệu từ xa và thi hành Client-Server. - Phát biểu dạng thức căn bản của một truy vấn SQL. Sử dụng SQL dạng căn bản để tìm thơng tin trong cơ sở dữ liệu quan hệ. - Sử dụng các phép tốn UNION, INTERSECT, và EXCEPT trong SQL, và SQL dạng lồng nhau để tìm thơng tin phức tạp hơn. - Biết cách áp dụng các tốn tử gộp trong SQL để tính tốn các giá trị gộp, và cách đặt điều kiện trên các giá trị gộp theo nhĩm. 53
54. - Định nghĩa giá trị rỗng trong SQL và nhận biết những vấn đề liên quan đến giá trị rỗng. - Mơ tả những ràng buộc tồn vẹn đơn giản trong SQL. Định nghĩa chốt chuyển và cơ sở dữ liệu hoạt động. - Phân biệt sự khác nhau giữa ràng buộc và chốt chuyển. Nội dung chính: 4.1 Giới thiệu SQL 4.2 Dạng thức căn bản của một truy vấn SQL 4.2.1. Các lệnh hỏi - tìm kiếm dữ liệu: (Data Retrieval SQL) 4.2.2 Phép tốn UNION 4.3 Truy vấn lồng nhau 4.4 Các phép tốn gộp. Các giá trị rỗng 4.4.1 Các phép tốn gộp 4.4.2 Các giá trị rỗng 4.5 Giới thiệu về khung nhìn 4.1 Giới thiệu SQL. Như trong Chương I đã trình bày, một hệ quản trị CSDL phải cĩ ngơn ngữ giao tiếp giữa người sử dụng với CSDL (hoặc cũng cịn gọi là ngơn ngữ truy nhập CSDL). Ngơn ngữ giao tiếp CSDL gồm các phạm trù: Ngơn ngữ mơ tả dữ liệu (Data Definition Language - DDL) để cho phép khai báo cấu trúc các bảng của CSDL, khai báo các mối liên hệ của dữ liệu (Data RelationShip) và các quy tắc (Rules, Constraint) quản lý áp đặt lên các dữ liệu đĩ. Ngơn ngữ thao tác dữ liệu (Data Manipulation Language - DML) cho phép người sử dụng cĩ thể thên (Insert), xĩa (Delete), sửa (Update) dữ liệu trong CSDL. Ngơn ngữ truy vấn dữ liệu, hay ngơn ngữ hỏi đáp cĩ cấu trúc (Structured Query Language - SQL) cho phép những người khai thác CSDL (chuyên nghiệp hoặc khơng chuyên) sử dụng để truy vấn các thơng tin cần thiết trong CSDL. Ngơn ngữ quản lý dữ liệu (Data Control Language - DCL) cho phép những người quản trị hệ thống thay đổi cấu trúc của các bảng dữ liệu, khai báo bảo mật thơng tin và cấp quyền hạn khai thác CSDL cho người sử dụng. Những năm 1975-1976, IBM lần đầu tiên đưa ra hệ quản trị CSDL kiểu quan hệ mang tên SYSTEM-R với ngơn ngữ giao tiếp CSDL là SEQUEL (Structured English QUEry Language), đĩ một ngơn ngữ con để thao tác với CSDL. Năm 1976 ngơn ngữ SEQUEL được cải tiến thành SEQUEL2. Khoảng năm 1978-1979 SEQUEL2 được cải tiến và đổi tên thành Ngơn Ngữ Truy Vấn Cĩ Cấu Trúc (Structured Query Language - SQL) và cuối năm 1979 hệ quản trị CSDL được cải tiến thành SYSTEM- R*. Năm 1986 Viện Tiêu Chuẩn Quốc Gia Mỹ (American National Standards Institute - ANSI) đã cơng nhận và chuẩn hĩa ngơn ngữ SQL, và sau đĩ Tổ chức Tiêu chuẩn Thế giới 54
55. (International Standards Organization - ISO) cũng đã cơng nhận ngơn ngữ này. Đĩ là chuẩn SQL-86. Tới nay SQL đã qua 3 lần chuẩn hĩa lại (1989, 1992, 1996) để mở rộng các phép tốn và tăng cường khả năng bảo mật và tính tồn vẹn dữ liệu. Tài liệu này trình bày Ngơn ngữ truy vấn CSDL dựa trên chuẩn SQL-92 và cĩ tham khảo với SQL, SQL*PLUS, PL/SQL của Oracle Server Release 7.3 (1996) và MicroSoft SQL Server 7.1 với các phạm trù nêu trên. Để việc trình bày cú pháp các câu lệnh SQL được gọn gàng và dễ hiểu, tài liệu này cĩ đưa ra một số quy ước ký pháp (Typographic Conventions) như sau: Các từ khĩa (KeyWords), các hàm (Functions), tên bảng (quan hệ - Table Names) của các câu lệnh được viết bằng chữ in hoa (UpperCase). Các tên thuộc tính (Column Names) của các bảng được viết đậm. Những tên thuộc tính cĩ dấu tiếng Việt hay cĩ khoảng trắng được viết trong dấu ngoặc vuơng ( [ ] ) theo ký pháp của SQL-Server. Ví dụ: SELECT Deptno, Deptname FROM DEPARTMENT; Các biến cú pháp (Syntax Variables), tức là các thành phần ngơn ngữ mà người sử dụng phải điền cụ thể vào khi viết lệnh, sẽ được viết bằng chữ thường (LowerCase), trong cặp dấu ( ) và nghiêng. Ví dụ: CREATE TABLE ( , , ); Các thành phần tùy chọn (Optional), tức là cĩ thể cĩ hoặc khơng được viết trong cặp dấu ngoặc vuơng đậm nét ([ ]). Ví dụ: UPDATE SET = , = , [ WHERE ]; Việc lựa chọn một trong các khả năng được thể hiện bởi dấu xổ đứng đậm (½ ). Thành phần bắt buộc phải chọn trong danh sách được viết trong cặp dấu mĩc đậm nét ( { } ). Giá trị mặc định (Default Value) được viết với dấu gạch chân (Underline). Ví dụ: SELECT { * ½ , , } FROM [ORDER BY ½ [ ASC ½ DESC ], ] Lệnh SQL cĩ thể được viết trên nhiều dịng và kết thúc lệnh bởi dấu chấm phảy ( ; ), tuy nhiên từ khĩa, tên hàm, tên thuộc tính, tên bảng, tên đối tượng (Objects) thì khơng được phép viết tách xuống hàng. Trong vận dụng thực tế, từ khĩa, tên thuộc tính, tên bảng, tên đối tượng được viết in hoa hoặc chữ thường là như nhau. Cho đến bây giờ chúng ta đã cĩ các CSDL với đầy đủ dữ liệu về quản lý học viên - được trình bày trong bài trước (gồm các quan hệ: KHOA, GIẢNG-VIÊN, LỚP-HỌC, MƠN- HỌC, HỌC-VIÊN, KQUẢ-THI), quản lý nhân sự của một cơng ty EMPLOYMENT – (gồm các quan hệ: DEPARTMENT, EMPLOYEE, JOBS, EMPLHIST) và CSDL quản lý cán bộ - cơng 55