Tìm hiểu cơ sở dữ liệu

Hồ Cẩm Hà - ĐH Sư phạm Hà nội 1 Môc tiªu ¾Cung cÊp nh÷ng kiÕn thøc c¬ b¶n vÒ nguyªn lý tæ chøc vµ khai th¸c ®óng ®¾n mét hÖ CSDL, ®Æc biÖt trong m« h×nh quan hÖ. ¾Cung cÊp vµ rÌn cho sinh viªn kh¶ n¨ng thiÕt kÕ (logic) mét hÖ CSDL quan hÖ. ¾Thùc hµnh cµi ®Æt c¸c rµng buéc toµn vÑn vµ tèi −u hãa biÓu thøc t×m kiÕm trªn SQL Server C¬ së d÷ liÖu Hồ Cẩm Hà - ĐH Sư phạm Hà nội 2 Tµi liÖu tham kh¶o 1.Hå ThuÇn, Hå CÈm Hµ, C¸c hÖ c¬ së d÷ liÖu: LÝ thuyÕt vµ thùc hµnh, 2 tËp, NXBGD 2004-2005. 2 NguyÔn Kim Anh, Nguyªn lÝ c¸c hÖ CSDL, NXB §¹i häc quèc gia, 2004 3. Jeffrey D. Ullman, Nguyªn lý c¸c hÖ c¬ së d÷ liÖu vµ c¬ së tri thøc, Biªn dÞch : TrÇn §øc Quang tËp 1, tËp 2, NXB Thèng kª, 1999. 4. §ç Trung TuÊn, C¬ së d÷ liÖu quan hÖ, NXB Gi¸o dôc, 1998 Hồ Cẩm Hà - ĐH Sư phạm Hà nội 3 Tµi liÖu tham kh¶o 5. Date C.J., “An introduction to database systems”, seventh edition, Addison. Wesley, 2000. 6. Hector Garcia - Monila, Ullman J.D., Jennifer Widom “Database Systems: The complete Book", Prentice Hal, 2002 . 7. Thomas Connolly, Carolyn Begg, Anne Strachan, "Database systems. A Practical Approach to Design, Implementation, and Management", Second Edition, Addison Wesley Longman Limited 1999. 8. Ullman J.D., J. Widom “A first course in Database Systems", Prentice - Hall, 1997. HỆ THỐNG THÔNG TIN Hồ Cẩm Hà - ĐH Sư phạm Hà nội 5 Chương1. Khái quát về các hệ CSDL 1.CSDL là gì? „ tích hợp các dữ liệu phản ánh hoạt động (operational data) của một tổ chức „ lưu trữ trên các thiết bị nhớ „ nhiều người dùng với các mục đích khác nhau Tại sao cần đến các hệ CSDL HÖ qu¶n trÞ c¬ së d÷ liÖu lµ g×? PhÇn mÒm cho phÐp ng−êi dïng giao tiÕp víi CSDL, cung cÊp m«i tr−êng thuËn lîi vµ hiÖu qu¶ ®Ó t×m kiÕm vµ l−u tr÷ th«ng tin cña CSDL 2. KiÕn tróc ba møc cña mét hÖ CSDL Môc ®Ých : sù t¸ch biÖt quan niÖm vÒ CSDL cña nhiÒu ng−êi sö dông víi nh÷ng chi tiÕt biÓu diÔn vÒ vËt lý cña CSDL Hồ Cẩm Hà - ĐH Sư phạm Hà nội 8 3. L−îc ®å vµ thÓ hiÖn cña CSDL „ l−îc ®å CSDL (database schema) „ thÓ hiÖn cña CSDL (database instance) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 9 4. §éc lËp d÷ liÖu Lược đồ ngoài Lược đồ ngoài Lược đồ ngoài Lược đồ khái niệm Lược đồ trong Độc lập dữ liệu mức logíc Độc lập dữ liệu mức vật lý Ánh xạ mức ngoài / mức khái niệm Ánh xạ mức khái niệm / mức trong Hồ Cẩm Hà - ĐH Sư phạm Hà nội 10 §éc lËp d÷ liÖu „ §éc lËp vËt lý (lµ g×? v× sao ph¶i thay ®æi l−îc ®å vËt lý) „ §éc lËp logic (lµ g×? v× sao ph¶i thay ®æi l−îc ®å logic) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 11 The Range of Database Applications „ Personal Database – standalone desktop database „ Workgroup Database – local area network (<25 users) „ Department Database – local area network (25-100 users) „ Enterprise Database – wide-area network (hundreds or thousands of users) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 12 Typical data from a personal computer database Hồ Cẩm Hà - ĐH Sư phạm Hà nội 13 Workgroup database with local area network Hồ Cẩm Hà - ĐH Sư phạm Hà nội 14 An enterprise data warehouse Hồ Cẩm Hà - ĐH Sư phạm Hà nội 15 Components of the Database Environment „ CASE Tools – computer-aided software engineering „ Repository – centralized storehouse of metadata „ Database Management System (DBMS) – software for managing the database „ Database – storehouse of the data „ Application Programs – software using the data „ User Interface – text and graphical displays to users „ Data Administrators – personnel responsible for maintaining the database „ System Developers – personnel responsible for designing databases and software „ End Users – people who use the applications and databases Hồ Cẩm Hà - ĐH Sư phạm Hà nội 16 Components of the database environment Hồ Cẩm Hà - ĐH Sư phạm Hà nội 17 5.Nh÷ng c¸ch tiÕp cËn mét CSDL M« h×nh d÷ liÖu lµ mét tËp c¸c kh¸i niÖm vµ ký ph¸p dïng ®Ó m« t¶ d÷ liÖu, c¸c mèi quan hÖ cña d÷ liÖu, c¸c rµng buéc trªn d÷ liÖu cña mét tæ chøc. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 18 M« h×nh d÷ liÖu „ phÇn m« t¶ cÊu tróc cña CSDL; „ phÇn m« t¶ c¸c thao t¸c, ®Þnh nghÜa c¸c phÐp to¸n ®−îc phÐp trªn d÷ liÖu; „ phÇn m« t¶ c¸c rµng buéc toµn vÑn ®Ó ®¶m b¶o sù chÝnh x¸c cña d÷ liÖu. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 19 M« h×nh d÷ liÖu „ C¸c m« h×nh logic trªn c¬ së ®èi t−îng (Object-Based Data Models) „ C¸c m« h×nh logic trªn c¬ së b¶n ghi (Record-Based Data Models) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 20 M« h×nh d÷ liÖu (Object-Based Data Models) „ M« h×nh thùc thÓ - mèi quan hÖ „ M« h×nh h−íng ®èi t−îng „ M« h×nh d÷ liÖu ng÷ nghÜa „ M« h×nh d÷ liÖu chøc n¨ng Hồ Cẩm Hà - ĐH Sư phạm Hà nội 21 6. HÖ qu¶n trÞ c¬ së d÷ liÖu Mét hÖ qu¶n trÞ c¬ së d÷ liÖu ( Database Management System, viÕt t¾t lµ DBMS) lµ mét tËp hîp c¸c ch−¬ng tr×nh cho phÐp ng−êi dïng ®Þnh nghÜa, t¹o lËp, b¶o tr× c¸c CSDL vµ cung cÊp c¸c truy cËp cã ®iÒu khiÓn ®Õn c¸c CSDL nµy (a)Ng«n ng÷ ®Þnh nghÜa d÷ liÖu (Data Definition Language, DDL): (b)Ng«n ng÷ thao t¸c d÷ liÖu (Data Manipulation Language, DML) (c)C¸c kiÓm so¸t, c¸c ®iÒu khiÓn ®èi víi viÖc truy cËp vµo CSDL Hồ Cẩm Hà - ĐH Sư phạm Hà nội 22 6.Database Management System DBMS manages data resources like an operating system manages hardware resources DBMS Databasecontaining centralized shared data Application #1 Application #2 Application #3 Hồ Cẩm Hà - ĐH Sư phạm Hà nội 23 Ng«n ng÷ ®Þnh nghÜa d÷ liÖu (DDL): Cho phÐp ng−êi dïng ®Þnh nghÜa CSDL: cho phÐp ng−êi dïng ®Æc t¶ c¸c kiÓu vµ c¸c cÊu tróc d÷ liÖu, ®Æc t¶ c¸c rµng buéc trªn c¸c d÷ liÖu l−u tr÷ trong CSDL. „ Tõ ®iÓn d÷ liÖu lµ mét tÖp c¸c d÷ liÖu vÒ d÷ liÖu. „ CÊu tróc l−u tr÷ vµ c¸c ph−¬ng ph¸p truy cËp cña hÖ CSDL sÏ ®−îc ®Æc t¶ bëi mét tËp ®Þnh nghÜa viÕt b»ng mét DDL x¸c ®Þnh. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 24 Ng«n ng÷ thao t¸c d÷ liÖu (DML) „ T×m kiÕm th«ng tin l−u tr÷ trong CSDL; „ ChÌn thªm th«ng tin míi vµo CSDL; „ Xo¸ bá th«ng tin khái CSDL; „ Söa ®æi th«ng tin ®−îc l−u tr÷ trong CSDL. TÝnh thñ tôc/phi thñ tôc (procedure/nonprocedure) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 25 C¸c kiÓm so¸t, c¸c ®iÒu khiÓn ®èi víi viÖc truy cËp vµo CSDL „ hÖ thèng an ninh (security) „ hÖ thèng rµng buéc toµn vÑn duy tr× tÝnh nhÊt qu¸n cña d÷ liÖu „ hÖ thèng ®iÒu khiÓn t−¬ng tranh „ hÖ thèng ®iÒu khiÓn kh«i phôc c¬ së d÷ liÖu „ tõ ®iÓn d÷ liÖu (catalog). Hồ Cẩm Hà - ĐH Sư phạm Hà nội 26 ChÊt l−îng DBMS „ VÒ c¬ b¶n hÖ qu¶n trÞ CSDL cung cÊp c¸c ph−¬ng tiÖn nªu trªn, nh−ng c¸c hÖ kh¸c nhau sÏ kh¸c nhau vÒ chÊt l−îng vµ kh¶ n¨ng ®¸p øng nhu cÇu thùc tÕ. „ C¸c hÖ qu¶n trÞ CSDL lu«n ph¸t triÓn theo h−íng ®¸p øng c¸c ®ßi hái ngµy cµng cao h¬n cña ng−êi dïng, bëi vËy c¸c chøc n¨ng cña DBMS ngµy cµng ®−îc më réng h¬n. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 27 Vµi nÐt vÒ qu¸ tr×nh ph¸t triÓn DBMSs „ 1960: Mét trong nh÷ng hÖ qu¶n trÞ ®Çu tiªn lµ IMS (Information Management System) cña h·ng IBM (m« h×nh d÷ liÖu ph©n cÊp). „ Gi÷a nh÷ng n¨m 60 (thÕ kû 20), IDS (Integrated Data Store) ra ®êi ®¸nh dÊu sù xuÊt hiÖn ®Çu tiªn cña mét hÖ qu¶n trÞ CSDL dùa trªn m« h×nh d÷ liÖu m¹ng. ThÕ hÖ thø nhÊt. „ N¨m 1976, hÖ qu¶n trÞ CSDL ®Çu tiªn dùa trªn m« h×nh quan hÖ cña h·ng IBM mang tªn System-R ra ®êi. Tõ nh÷ng n¨m 1980 ®Õn nay hµng tr¨m hÖ qu¶n trÞ CSDL kiÓu quan hÖ ra ®êi cho c¶ m«i tr−êng m¸y tÝnh lín vµ c¶ m¸y tÝnh c¸ nh©n. ThÕ hÖ thø hai. „ Tõ nh÷ng n¨m 1990, b¾t ®Çu x©y dùng DBMS kiÓu h−íng ®èi t−îng vµ h−íng ®èi t−îng-quan hÖ. ThÕ hÖ thø ba. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 28 Evolution of DB Systems „ Flat files - 1960s - 1980s „ Hierarchical – 1970s - 1990s „ Network – 1970s - 1990s „ Relational – 1980s - present „ Object-oriented – 1990s - present „ Object-relational – 1990s - present „ Data warehousing – 1980s - present „ Web-enabled – 1990s - present Hồ Cẩm Hà - ĐH Sư phạm Hà nội 29 C¸c chøc n¨ng cña mét hÖ qu¶n trÞ CSDL (1) Cung cÊp cho ng−êi dïng kh¶ n¨ng l−u tr÷, truy xuÊt vµ cËp nhËt d÷ liÖu. (2) Cung cÊp cho ng−êi dïng mét tõ ®iÓn d÷ liÖu (catalog) (3) Hç trî c¸c giao t¸c (transaction) (4) Cung cÊp c¸c dÞch vô ®iÒu khiÓn t−¬ng tranh ®Ó ®¶m b¶o tÝnh nhÊt qu¸n d÷ liÖu khi cã nhiÒu phiªn lµm viÖc víi CSDL (5) Cung cÊp mét c¬ chÕ ®Ó kh«i phôc d÷ liÖu (6) Cung cÊp c¸c dÞch vô b¶n quyÒn (7) Hç trî cho truyÒn th«ng d÷ liÖu (8) Cung cÊp c¸c dÞch vô ®¶m b¶o tÝnh toµn vÑn d÷ liÖu. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 30 C¸c chøc n¨ng cña mét hÖ qu¶n trÞ CSDL (tiÕp) Ngoµi 8 chøc n¨ng trªn ng−êi ta cßn mong muèn hÖ qu¶n trÞ CSDL cung cÊp thªm hai dÞch vô n÷a: (9) Cung cÊp c¸c dÞch vô hç trî cho tÝnh ®éc lËp d÷ liÖu. (10) Cung cÊp mét sè dÞch vô tiÖn Ých gióp DBA. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 31 KiÕn tróc cña mét hÖ qu¶n trÞ CSDL Bộ tiền xử lí ngôn ngữ thao tác dữ liệu Bộ xử lí truy vấn Chương trình dịch ngôn ngữ khai báo dữ liệu Mã chương trình Bộ quản lí cơ sở dữ liệu Bộ quản lí từ điển DBMS Phương thức truy cập Bộ quản lí tệp Bộ đệm của hệ thống Cơ sở dữ liệu và từ điển dữ liệu Trình ứng dụng Truy vấn Lược đồ cơ sở dữ liệu Hồ Cẩm Hà - ĐH Sư phạm Hà nội 32 Trình kiểm tra tính toàn vẹn Trình quản lý quyền truy cập Trình tối ưu hóa câu hỏi Trình quản lý các giao tác Trình xử lý câu lệnh Trình lập lịch DM Bộ quản lí tệp Cơ sở dữ liệu và thư mục hệ thống/ từ điển dữ liệu Mã đích chương trình Bộ xử lý yêu cầu Bộ quản lý từ điển dữ liệu Trình quản lý bộ nhớ đệm Trình quản lý khôi phục dữ liệu DM Phương pháp truy cập Các vùng nhớ đệm của hệ thống Hồ Cẩm Hà - ĐH Sư phạm Hà nội 33 Ho¹t ®éng cña mét ch−¬ng tr×nh øng dông th«ng qua c¸c thµnh phÇn cña DBMS 10 8b 8a 6 5 9 2 3 7 4 4 a b c Chương trình ứng dụng A Hệ quản trị cơ sở dữ liệu Hệ điều hành Hệ quản lí Nhập / xuất (I/O) Lược đồ ngoài (External Scheme) Lược đồ khái niệm (logical Scheme) Lược đồ vật lý (Physical Scheme) Vùng đệm 1àm việc của chương trình ứng dụng A Vùng đệm 1àm việc của hệ quản trị cơ sơ dữ liệu CSDL Hồ Cẩm Hà - ĐH Sư phạm Hà nội 34 S¬ l−îc vÒ c¸c kiÕn tróc hÖ qu¶n trÞ CSDL ®a ng−êi dïng H×nh 1.11 KiÕn tróc hÖ xö lý tõ xa (teleprocessing) Hồ Cẩm Hà - ĐH Sư phạm Hà nội 35 S¬ l−îc vÒ c¸c kiÕn tróc hÖ qu¶n trÞ CSDL ®a ng−êi dïng H×nh 1.12 KiÕn tróc tÖp-m¸y chñ (file - server) File - server l−u gi÷ c¸c tÖp d÷ liÖu mµ c¸c øng dông vµ hÖ QTCSDL cÇn ®Õn. Tuy nhiªn c¸c øng dông vµ hÖ QTCSDL ch¹y trªn mçi tr¹m lµm viÖc (workstation) vµ yªu cÇu c¸c tÖp d÷ liÖu ë file - server khi cÇn ®Õn LAN Các tệp được gửi về trạm làm việcCác yêu cầu về dữ liệu Cơ sở dữ liệuFile-server Trạm làm việc 1 Trạm làm việc 2 Trạm làm việc 3 Hồ Cẩm Hà - ĐH Sư phạm Hà nội 36 S¬ l−îc vÒ c¸c kiÕn tróc hÖ qu¶n trÞ CSDL ®a ng−êi dïng LAN Dữ liệu được chọn gửi về máy kháchCác yêu cầu về dữ liệu Cơ sở dữ liệu M¸y chñ (với DBMS) Máy khách 1 Máy khách 2 Máy khách 3 C¸c tiÕn tr×nh t−¬ng t¸c víi nhau t¹o nªn hÖ thèng: tiÕn tr×nh m¸y kh¸ch yªu cÇu cung cÊp tµi nguyªn nµo ®ã vµ tiÕn tr×nh m¸y chñ cung cÊp tµi nguyªn ®ã. Hai tiÕn tr×nh nµy kh«ng nhÊt thiÕt ph¶i n»m trªn cïng mét m¸y tÝnh. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 37 KiÕn tróc m¸y kh¸ch-m¸y chñ (client - server) „ Kh¶ n¨ng truy cËp réng r·i ®Õn c¸c CSDL. „ N©ng cao kh¶ n¨ng thùc hiÖn: nÕu tiÕn tr×nh server vµ c¸c tiÕn tr×nh client ë trªn c¸c m¸y tÝnh kh¸c nhau th× c¸c CPU kh¸c nhau cã thÓ cïng ch¹y song song, mçi CPU thùc hiÖn tiÕn tr×nh cña nã. „ Chi phÝ cho phÇn cøng cã thÓ ®−îc gi¶m do chØ cÇn server cã cÊu h×nh ®ñ m¹nh ®Ó l−u tr÷ vµ qu¶n trÞ c¬ së d÷ liÖu. „ Chi phÝ cho truyÒn th«ng ®−îc gi¶m do mét phÇn trong c¸c thao t¸c cña øng dông ®−îc gi¶i quyÕt trªn client, truyÒn th«ng trªn m¹ng chØ gåm: yªu cÇu vÒ truy cËp c¬ së d÷ liÖu cña client göi ®Õn server vµ d÷ liÖu kÕt qu¶ tõ server göi cho client. „ N©ng cao ®−îc kh¶ n¨ng ®¶m b¶o tÝnh nhÊt qu¸n cña d÷ liÖu. Server cã thÓ kiÓm so¸t ®−îc tÝnh toµn vÑn bëi c¸c rµng buéc nµy ®−îc ®Þnh nghÜa vµ kiÓm tra chØ t¹i ®ã. „ KiÕn tróc nµy phï hîp víi viÖc x©y dùng c¸c hÖ thèng cã tÝnh më. Hồ Cẩm Hà - ĐH Sư phạm Hà nội 38 Vai trß cña con ng−êi trong hÖ CSDL „ Ng−êi qu¶n trÞ CSDL „ Ng−êi thiÕt kÕ CSDL „ Ng−êi lËp tr×nh øng dông „ Ng−êi sö dông ®Çu cuèi Hồ Cẩm Hà - ĐH Sư phạm Hà nội 39 Tãm t¾t ch−¬ng 1 „ Kh¸i niÖm CSDL, hÖ qu¶n trÞ CSDL „ Chøc n¨ng, vai trß, kiÕn tróc cña DBMS „ KiÕn tróc cña mét hÖ CSDL „ Kh¸i niÖm m« h×nh d÷ liÖu „ M« h×nh quan hÖ „ KiÕn tróc Clien-Server Xin cảm ơn ! Hồ Cẩm Hà Ch−¬ng 2 CƠ SỞ DỮ LIỆU QUAN HỆ Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Miền (Domain) Để đặc tả một miền người ta chỉ ra một tên miền, một kiểu dữ liệu và khuôn dạng dữ liệu. Ví dụ 3.2: Miền Ma_Sv là tập hợp các dãy ký tự có độ dài từ 5 đến 8, bắt đầu là một chữ cái. Miền Ho_ten (Họ tên) là tập các dãy chữ cái có độ dài không quá 35. Miền Đien_thoai (Số điện thoại) là tập hợp các dãy 7 chữ số hợp lệ đối với số điện thoại. Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Quan hệ (Relation) Cho một quan hệ r xác định trên tập thuộc tính Ω = {A1, A2, , An }. Khi đó r ⊆ Dom(A1) × Dom(A2) × Dom(A3) × × Dom(An) „ Như vậy quan hệ r là một tập hợp các n_bộ có dạng „ r = { (a1, a2 , . . ., an)⎮ ai ∈ Dom(Ai), i = 1, 2,,n }. Hồ Cẩm Hà Chương 2 Ví dụ Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Lược đồ quan hệ (Relation scheme) R = Ω là tập hữu hạn các thuộc tính của quan hệ, F là tập các điều kiện giữa các thuộc tính(ràng buộc toàn vẹn). Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Với lược đồ quan hệ R, theo thời gian, nhiều quan hệ có cấu trúc và ràng buộc toàn vẹn được mô tả bởi lược đồ này. Mỗi quan hệ như vậy còn được gọi là một thể hiện của lược đồ R. „ ký hiệu r(R) „ ký hiệu t[X], t[Ai] với X ⊆ Ω Hồ Cẩm Hà Chương 2 Các tính chất đặc trưng của một quan hệ „ Một quan hệ có một tên phân biệt với tên các quan hệ khác „ Mỗi ô trong bảng (quan hệ) chứa một giá trị nguyên tố „ Mỗi thuộc tính trong quan hệ có một tên phân biệt „ Các giá trị của một thuộc tính thuộc cùng một miền Hồ Cẩm Hà Chương 2 Các tính chất đặc trưng của một quan hệ „ Thứ tự các thuộc tính là không quan trọng vì quan hệ là một tập hợp. „ Các bộ trong quan hệ là phân biệt, nghĩa là không có hai bộ giống hệt nhau trong một quan hệ „ Thứ tự các bộ không quan trọng về mặt lý thuyết Hồ Cẩm Hà Chương 2 Ví dụ Hồ Cẩm Hà Chương 2 Gi¸ trÞ null „ Ch−a biÕt „ Kh«ng c㠄 Kh«ng biÕt cã hay kh«ng Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Khóa của quan hệ Siêu khóa của một lược đồ quan hệ R là một tập hợp gồm một hay nhiều thuộc tính của lược đồ R có tính chất xác định duy nhất một bộ trong mỗi thể hiện của R. „ Khóa của một lược đồ quan hệ là một siêu khóa của lược đồ này sao cho mọi tập con thực sự của nó không là siêu khóa Hồ Cẩm Hà Chương 2 Các khái niệm cơ bản „ Khóa dự tuyển (candidate key) „ Khóa chính (primary key) „ thuộc tính khóa, thuộc tính không khóa „ Khóa ngoài của một lược đồ quan hệ là một tập hợp gồm một hay nhiều thuộc tính là khóa của một lược đồ quan hệ khác. Hồ Cẩm Hà Chương 2 Hồ Cẩm Hà Chương 2 C¸c rµng buéc toµn vÑn „ Rµng buéc thùc thÓ (khãa) „ Rµng buéc tham chiÕu „ Rµng buéc ng÷ nghÜa Hồ Cẩm Hà Chương 2 Các thao tác cập nhật dữ liệu trên các quan hệ „ Phép thêm bộ (Insert) INSERT <NHAN-VIEN; “Nguyễn Hương”, ”Lan”, “943775”, “11- 06-56”, “3 Nguyễn Chí Thanh”, 58000, “886655”,3> INSERT <NHAN-VIEN; “Nguyễn Hương”, ”Lan”, “943775”, “11- 06-56”, “3 Nguyễn Chí Thanh”, ‘bac 1’, “886655”,1 > Hồ Cẩm Hà Chương 2 Các thao tác cập nhật dữ liệu trên các quan hệ „ Phép xóa (Delete) DELETE DELETE Hồ Cẩm Hà Chương 2 Các thao tác cập nhật dữ liệu trên các quan hệ „ Phép sửa đổi (Update) MODIFY MODIFY Hồ Cẩm Hà Chương 2 Tãm t¾t ch−¬ng 2 „ lược đồ quan hệ (cấu trúc của quan hệ và các ràng buộc) „ quan hệ là một bảng với tất cả các bộ đang tồn tại trong đó, bảng này là một thể hiện của lược đồ quan hệ. „ siêu khóa là một tập thuộc tính xác định các bộ một cách duy nhất trong quan hệ. „ khóa dự tuyển là các siêu khóa nhỏ nhất. „ Khóa chính là một khóa dự tuyển được chọn dùng để nhận diện các bộ. „ Khóa ngoài của một quan hệ là một tập thuộc tính trong một quan hệ có vai trò làm khóa dự tuyển trong một quan hệ khác. „ Ràng buộc toàn vẹn: thực thể, tham chiếu, ngữ nghĩa Hồ Cẩm Hà 19 Xin cảm ơn ! Ch−¬ng 3 Ngôn ngữ thao tác dữ liệu Hồ Cẩm Hà Chương 3 2 Ngôn ngữ đại số quan hệ „ Ngôn ngữ đại số quan hệ là cơ sở quan trọng của một ngôn ngữ bậc cao được sử dụng để thao tác trên các quan hệ. „ Các phép toán của đại số quan hệ chia thành hai nhóm (tập hợp và đặc trưng) Hồ Cẩm Hà Chương 3 3 Các phép toán tập hợp „ Phép hợp: r ∪s = {t⏐ t∈r hoặc t∈s} „ Phép giao: r ∩ s = {t⏐ t ∈ r và t ∈ s} „ Phép hiệu: r – s = {t⏐ t ∈ r và t ∉ s} r ∪ (s ∪ t) = (r ∪ s) ∪ t r ∩ (s ∩ t) = (r ∩ s) ∩ t Hồ Cẩm Hà Chương 3 4 Các phép toán tập hợp „ Phép tích Descartes: r × s = {t⏐t=(a1, a2,..,an, b1, b2,..,bm), (a1, a2,..,an) ∈ r và (b1, b2,..,bm)∈s } Như vậy nếu r có k1 bộ, s có k2 bộ thì r × s có k1× k2 bộ. Hồ Cẩm Hà Chương 3 5 Các phép toán tập hợp „ Phép chia: r r÷s ={t⏐t=(am+1,am+2, ..,an): ∀(a1, a2,..,am) ∈ s, (a1, a2,..,am, am+1, ..,an) ∈ r } Hồ Cẩm Hà Chương 3 6 Các phép toán đặc biệt trên quan hệ Phép chọn σC(r) = {t/ t∈r, C(t) = TRUE} Ví du σ ((MA_DU_AN= D1) ∨ (LUONG ≥ 3500)) (PHAN_CONG) Hồ Cẩm Hà Chương 3 7 Các phép toán đặc biệt trên quan hệ Phép chiếu ΠX(r) = {t[X]/ t ∈ r} Ví du ΠHOTEN, LUONG(PHAN_CONG) Hồ Cẩm Hà Chương 3 8 Các phép toán đặc biệt trên quan hệ Phép kết nối (join) Khái niệm ” xếp cạnh nhau”: Cho bộ p =(p1, p2 , .., pn) và bộ q =(q1, q2 , .., qm). Xếp cạnh nhau của p và q : (p,q) = {p1, p2 , .., pn, q1, q2 , .., qm } r ⋈ Ai θ Bj s = {(t, u)/ t ∈ r, u ∈ s và t[Ai] θ u[Bj]} Hồ Cẩm Hà Chương 3 9 Các phép toán đặc biệt trên quan hệ Phép kết nối (join) r ⋈ Ai θ Bj s = {(t, u)/ t ∈ r, u ∈ s và t[Ai] θ u[Bj]} Trường hợp θ là “=” gọi là kết nối bằng kết nối tự nhiên sử dụng kí hiệu r * s (hoặc r ⋈ s ) Ví dụ NHA_CUNG_CAP ⋈DIACHI≠NOI-SXUATSAN_PHAM Hồ Cẩm Hà Chương 3 10 Các phép toán đặc biệt trên quan hệ Phép kết nối (join) kết nối tự nhiên sử dụng kí hiệu r * s (hoặc r ⋈ s ) Ví dụ NHA_CUNG_CAP * CUNG_UNG Hồ Cẩm Hà Chương 3 11 Các phép toán đặc biệt trên quan hệ Phép kết nối nửa (semi join) r ⊲F s = ΠA(r ⋈ F s ) Ví du CUNG_UNG ⊲ F SAN_PHAM với F là điều kiện: (CUNG_UNG.MA_SP = SAN_PHAM.MA_SP) AND (SOLUONG>1500). Hồ Cẩm Hà Chương 3 12 Các phép toán đặc biệt trên quan hệ Phép kết nối ngoài trái (left outer join) kết nối ngoài phải (right outer join) (Đọc tài liệu) Hồ Cẩm Hà Chương 3 13 Các phép toán quan hệ bổ sung (Additional Relational Operations) Các hàm kết tập „ hàm tính tổng (SUM) „ hàm tính trung bình cộng (AVERAGE) „ hàm tính giá trị lớn nhất (MAX) „ hàm tính giá trị nhỏ nhất (MIN) „ hàm đếm các bộ giá trị (COUNT) „ hàm tính tổng (SUM) Hồ Cẩm Hà Chương 3 14 Các phép toán quan hệ bổ sung (Additional Relational Operations) Các phép gộp nhóm f (r) Ví dụ MA_PHONG f COUNT MANV, AVERAGE LUONG (NHAN_VIEN) Hồ Cẩm Hà Chương 3 15 Chú ý (1) Kết quả áp dụng một hàm kết tập là một quan hệ chứ không phải là một đại lượng vô hướng kể cả khi nó chỉ gồm một giá trị. Các bộ trùng lặp trong mỗi nhóm không bị loại bỏ khỏi nhóm khi thực hiện một hàm kết tập. (2) Một số phép toán có thể được biểu diễn theo các phép toán khác. Hồ Cẩm Hà Chương 3 16 Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 1 Tìm tên của dự án có mã số D4 ΠTEN_DA(σ(MA_DA=”D4” )(DU_AN)) „ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên làm việc ở phòng “Nghiên cứu và phát triển”. ΠHOTEN, LUONG(NHAN_VIEN* (ΠMA-DV(σ(TEN-PHONG=”Nghiên cứu và phát triển )(PHONG)))) Hồ Cẩm Hà Chương 3 17 Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên, ngày sinh của trưởng phòng quản lý dự án này. KQTG ← (ΠMA-DA, MA-DV(σ(DIA-DIEM-DA=”TT Khí tượng thủy văn HN’ )(DU_AN)) KETQUA ← ΠMA-DA, HO-TEN, NG-SINH(NHAN_VIEN⋈MA-NV=MA-TP (PHONG*KQTG)) Hồ Cẩm Hà Chương 3 18 Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 4 Tìm tên những nhân viên làm việc cho tất cả các dự án do phòng có mã số P4 quản lý. ΠHO-TEN((ΠMANV,MA-DA(CHAM_CONG)÷ ΠMA-DA(σ(MA-DV=P4)(DU_AN)))*NHAN_VIEN). Hồ Cẩm Hà Chương 3 19 Dùng các biểu thức đại số quan hệ biểu diễn câu hỏi „ Câu hỏi 5 Tìm mã số những dự án có sự tham gia của một người là lãnh đạo phòng trực tiếp quản lý dự án này. KQTG ← ΠMA-DA, MA-DV(DU_AN) * ΠMA-DV, MA-TP(PHONG) KETQUA ← KQTG ⋈ ((KQTG.MA-DA=CHAM_CONG.MA-DA) AND (KQTG.MA-TP=CHAM_CONG.MA-NV)) CHAM_CONG Hồ Cẩm Hà Chương 3 20 Ngôn ngữ tân từ (Các phép tính quan hệ) „ Cơ sở quan trọng của phần thao tác trong mô hình cơ sở dữ liệu quan hệ. „ Cơ sở toán học của phép tính quan hệ là logic tân từ cấp một. Có hai loại: ngôn ngữ tân từ biến bộ và ngôn ngữ tân từ biến miền. Hồ Cẩm Hà Chương 3 21 Ngôn ngữ tân từ biến bộ (Tuple relational calculus) Một câu hỏi đơn giản trong ngôn ngữ tân từ biến bộ có dạng {t ⏐ ĐK(t) } Hồ Cẩm Hà Chương 3 22 Ngôn ngữ tân từ biến bộ (Tuple relational calculus) {t ⏐ NHÂN_VIÊN (t) AND t.LUONG > 1700 000} {t.HO_TEN, t.MA_DA⏐NHÂN_VIÊN(t) AND t.LUONG > 1700 000} Hồ Cẩm Hà Chương 3 23 Biểu thức của phép tính biến bộ Một cách tổng quát, một biểu thức của phép tính biến bộ có dạng {ti.A1, tj.A2,, tk.An ⏐ ĐK (t1, t2,, tn, tn+1, tn+2, , tn+m)} Hồ Cẩm Hà Chương 3 24 Biểu thức của phép tính biến bộ Công thức của phép tính biến bộ được tạo nên từ các công thức nguyên tố. Một công thức nguyên tố thuộc một trong những dạng sau: (1) r(t) nói một cách khác đó là phát biểu (t ∈ r). (2) ti.A θ tj.B (3) ti.A θ c Mỗi công thức nguyên tố sẽ nhận một trong hai giá trị TRUE và FALSE đối với một tổ hợp các bộ (giá trị chân lý) Hồ Cẩm Hà Chương 3 25 Biểu thức của phép tính biến bộ Các công thức nguyên tố tạo nên công thức của phép tính bộ theo các luật: (L1) Mỗi công thức nguyên tố là một công thức (L2) F, F1, F2 là công thức thì (F1 AND F2), (F1 OR F2), NOT(F) là công thức. (L3) F là một công thức thì (∃ t)(F) với t là một biến bộ cũng là một công thức (L4) F là một công thức thì (∀t)(F) với t là một biến bộ cũng là một công thức Hồ Cẩm Hà Chương 3 26 Ví dụ Câu hỏi 1 Tìm tên của dự án có mã số D4 {t.TEN_DA⏐ (DU_AN(t) AND t.MA_DA=”D4”} Hồ Cẩm Hà Chương 3 27 Ví dụ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên thuộc phòng “Nghiên cứu và phát triển” {t.HO_TEN, t.LUONG ⏐∃u ((NHAN_VIEN(t) AND PHONG(u) AND u.TEN_PHONG = ”Nghiên cứu và phát triển” AND t.MA_DV = u.MA_DV } Hồ Cẩm Hà Chương 3 28 Ví dụ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên trưởng phòng quản lý dự án này {t.HO_TEN, t.LUONG ⏐∃u ((NHAN_VIEN(t) AND PHONG(u) AND u.TEN_PHONG = ”Nghiên cứu và phát triển” AND t.MA_DV = u.MA_DV } Hồ Cẩm Hà Chương 3 29 Ví dụ Câu hỏi 4 Tìm tên những nhân viên làm việc cho tất cả các dự án do phòng có mã số P4 quản lý {t.HO_TEN ⏐ NHAN_VIEN(t) AND (∀u ((DU_AN(u) AND u.MA_DV = “P4”) ⇒ (∃v (CHAM_CONG(v) AND v.MANV = t.MANV AND t.MA_DA = u.MA_DA)))} Hồ Cẩm Hà Chương 3 30 Ví dụ Câu hỏi 5 Tìm mã số của những dự án có sự tham gia của một người là lãnh đạo của phòng trực tiếp quản lý dự án này. t.MA_DA⏐∃p∃q ((DU_AN(t) AND PHONG(p) AND CHAM_CONG(q) AND (t.MA_DV = p.MA_DV) AND (p.MA_TP = q.MANV) AND (q.MA_DA = t.MA_DA))}. Hồ Cẩm Hà Chương 3 31 Luật chuyển đổi công thức tương đương liên quan đến các lượng từ „ (∀x) (P(x)) ≡ (∃ x))(NOT(P(x))) „ (∃ x) (P(x)) ≡ NOT(∀x)(NOT(P(x))) „ (∀x) (P(x) AND Q(x)) ≡ (∃ x))(NOT(P(x)) OR NOT(Q(x))) „ (∀x) (P(x) OR Q(x)) ≡ (∃ x))(NOT(P(x)) AND NOT(Q(x))) „ (∃ x) (P(x) OR Q(x)) ≡ NOT(∀x) (NOT(P(x)) AND NOT(Q(x))) „ (∃ x) (P(x) AND Q(x)) ≡ NOT(∀x) (NOT(P(x)) OR NOT(Q(x))) „ (∀x) (P(x)) ⇒ (∃ x) (P(x)) „ (∃ x) (P(x))⇒ (∀x)(NOT (P(x))) Hồ Cẩm Hà Chương 3 32 Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 1 Tìm tên của dự án có mã số D4 {tenX ⏐ ∃ diadiemX ∃ma_dvX (DU_AN(“D4”,tenX, diadiemX, ma_dvX))} Hồ Cẩm Hà Chương 3 33 Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 2 Cho biết họ tên và lương của những nhân viên thuộc phòng “Nghiên cứu và phát triển” {ho_tenX, luongX⏐ ∃ma_nvX∃ngay_sinhX∃gioi_tinhX ∃ ma_dvX ∃ ma_tpX (PHONG (ma_dvX, “Nghiên cứu và phát triển”, ma_tpX) AND NHAN_VIEN(ma_nvX,ho_tenX,ngay_sinhX,gioi_tinhX,ma_dvX,lu ongX))} Hồ Cẩm Hà Chương 3 34 Ngôn ngữ tân từ biến miền Ví dụ Câu hỏi 3 Với mỗi dự án thực hiện ở “TT khí thượng thủy văn HN” hãy cho biết mã số dự án, đồng thời cho biết họ tên trưởng phòng quản lý dự án này {ma_daX, ho_tenX⏐ ∃ ten_daX ∃ ma_dvX ∃ten_phongX ∃ma_tpX ∃ngay_sinhX ∃gioi_tinhX ∃luongX ( DU_AN (ma_daX, ten_daX, “TT khí tượng thủy văn HN”, ma_dvX) AND PHONG (ma_dvX, ten_phongX, ma_tpX) AND NHAN_VIEN (ma_tpX, ho_tenX, ngay_sinhX, gioi_tinhX, ma_dvX, luongX)) } Hồ Cẩm Hà Chương 3 35 Tính đầy đủ của NN hỏi „ Khả năng biểu thị câu hỏi của đại số quan hệ và ngôn ngữ tân từ là tương đương nhau về mặt này. „ Một ngôn ngữ hỏi L (của mô hình quan hệ) được gọi là đầy đủ nếu với L, chúng ta có thể biểu diễn được bất cứ một câu hỏi nào mà phép tính quan hệ biểu diễn được. „ Tính đầy đủ trở thành một tiêu chuẩn quyết định để so sánh khả năng biểu diễn câu hỏi của các ngôn ngữ hỏi bậc cao Hồ Cẩm Hà Chương 3 36 NGÔN NGỮ SQL „ SQL đang được xem là ngôn ngữ chuẩn của các hệ cơ sở dữ liệu quan hệ. „ Ngôn ngữ SQL gồm nhiều phần. Nó cho phép định nghĩa dữ liệu (gồm DDL), cho phép cập nhật và tìm kiếm dữ liệu (gồm DML). „ cung cấp các khả năng khác như: định nghĩa khung nhìn (view), kiểm soát tính toàn vẹn dữ liệu, cấp phát quyền truy cập, điều khiển các giao tác, thao tác dữ liệu nhúng. Hồ Cẩm Hà Chương 3 37 SQL Phần thao tác dữ liệu sẽ có đông đảo người dùng hơn, do chúng ta xem xét phần này trước phần định nghĩa dữ liệu của SQL. SELECT FROM WHERE dùng để truy vấn dữ liệu trong CSDL 39 NGÔN NGỮ SQL SELECT ten_da, ma_dv FROM du_an SELECT ma_da, ten_da, dia_diem_da, ma_dv FROM du_an hoặc SELECT * FROM du_an 40 NGÔN NGỮ SQL Cho biết họ tên của mỗi nhân viên và lương mới của mỗi người nếu lương họ được tăng 10% SELECT ho_ten, luong + luong*0.10 FROM nhan_vien Trong bảng kết quả, nếu muốn có thể đặt tên cho cột thứ ba, chẳng hạn cột thứ ba được đặt tên là luong_moi, với câu lệnh sau SELECT ho_ten, luong + luong*0.10 AS luong_moi FROM nhan_vien 41 NGÔN NGỮ SQL Cho biết danh sách nhân viên có lương trên 2 300 000, với mỗi nhân viên như vậy các thông tin đưa ra gồm: mã nhân viên, họ tên, mã đơn vị và lương. SELECT manv, ho_ten, ma_dv, luong FROM nhan_vien WHERE luong > 2 300 000 42 NGÔN NGỮ SQL Tìm tên các nhân viên có mã đơn vị là 'P4' và lương thấp hơn 2 500 000. Câu truy vấn đó là SELECT ho_ten, ma_dv, luong FROM nhan_vien WHERE ma_dv = ‘P4’ AND luong < 2 500 000 43 NGÔN NGỮ SQL Có thể tìm biết tên và mã các dự án mà địa điểm có chứa từ ‘UBND’ bằng câu sau: SELECT ma_da, ten_da, dia_diem_da FROM du_an WHERE dia_diem_da LIKE ‘%UBND%’ 44 NGÔN NGỮ SQL Để liệt kê các nhân viên nữ theo thứ tự tăng dần của lương, có thể dùng: SELECT * FROM nhan_vien WHERE gioi_tinh = ’nu’ ORDER BY luong 45 NGÔN NGỮ SQL Ví dụ 4.10 SELECT * FROM cham_cong WHERE ma_da IN (‘D1’, ‘D2’, ‘D4’) ORDER BY so_gio DESC, ma_da ASC 46 NGÔN NGỮ SQL Câu hỏi: “Có bao nhiêu nhân viên lương cao hơn 3 000 000 ?” có thể viết như sau: SELECT COUNT(*) AS vip FROM nhan_vien WHERE luong > 3000 000 47 NGÔN NGỮ SQL Ví dụ 4.13 Câu hỏi: “ Tìm lương cao nhất, lương thấp nhất và trung bình cộng lương các nhân viên” được viết như sau: SELECT MIN(luong)AS min, MAX(luong) AS max, AVG(luong) AS tb FROM nhan_vien 48 NGÔN NGỮ SQL Câu hỏi: “ Cho biết lương cao nhất và tổng lương của mỗi đơn vị” sẽ được viết là SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv 49 NGÔN NGỮ SQL Để đưa ra lương cao nhất và tổng lương của những đơn vị có tổng lương trên 4 triệu, câu truy vấn là SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv HAVING SUM(luong) > 4 000 000 50 NGÔN NGỮ SQL Để tìm mã số và tên những nhân viên phòng ‘Kỹ thuật’ có thể dùng câu truy vấn sau SELECT manv, ho_ten FROM nhan_vien WHERE ma_dv = (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’); 51 NGÔN NGỮ SQL Xét câu hỏi: ‘Tìm tên của những nhân viên có lương đạt trên mức lương trung bình của tất cả các nhân viên, đồng thời cho biết luôn mức chênh lệch này’. Chúng ta có thể dùng truy vấn sau: SELECT ho_ten , luong – (SELECT AVG(luong) FROM nhan_vien) AS hieu FROM nhan_vien WHERE luong > (SELECT AVG(luong) FROM nhan_vien); 52 NGÔN NGỮ SQL Câu ORDER BY không được sử dụng trong một câu truy vấn con mặc dù nó có thể được dùng trong câu truy vấn ngoài cùng. Danh sách các mục được liệt kê bởi câu truy vấn con SELECT phải chứa tên của một cột hoặc một biểu thức trừ phi câu truy vấn con này dùng từ khóa EXISTS. 53 NGÔN NGỮ SQL Theo ngầm định, các tên cột trong câu truy vấn con tham chiếu đến tên bảng trong câu FROM của truy vấn con này, hoặc có thể tham chiếu đến bảng trong câu FROM của truy vấn ngoài bằng việc xác định tên cột đó. Khi một câu truy vấn con là một trong hai toán hạng của một biểu thức so sánh thì truy vấn con này phải xuất hiện ở vế phải của so sánh. 54 NGÔN NGỮ SQL Muốn biết thông tin về những nhân viên làm việc cho đơn vị có địa điểm là ’23 Lý Thường Kiệt’, có thể dùng truy vấn như sau SELECT * FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM dia_diem_dv WHERE dia_diem = ’23 Lý Thường Kiệt); 55 NGÔN NGỮ SQL Cần phân biệt mục đích của câu WHERE và câu HAVING. Mặc dù có cú pháp tương tự: WHERE lọc lấy một số bộ trong bảng đưa vào bảng kết quả, HAVING lọc lấy một số nhóm nào đó để đưa vào bảng kết quả 56 NGÔN NGỮ SQL Điều kiện chọn (lọc) trong câu HAVING luôn chứa ít nhất một hàm gộp, nếu không thì điều kiện này có thể chuyển vào câu WHERE. Nên nhớ rằng hàm gộp không được dùng trong câu WHERE 57 NGÔN NGỮ SQL Trường hợp câu truy vấn có cả câu WHERE và câu HAVING (dĩ nhiên có cả câu GROUP BY), thì trước tiên điều kiện chọn ở câu WHERE được áp dụng. các bộ thỏa điều kiện này được tạo thành các nhóm do câu GROUP BY. Tiếp theo, câu HAVING loại đi những nhóm không thỏa điều kiện đặt sau từ HAVING. Các nhóm còn lại được câu SELECT sử dụng để tạo ra các bộ là kết quả của câu truy vấn. SELECT ma_dv, MAX(luong) AS max_dv, SUM(luong) AS tong_dv FROM nhan_vien GROUP BY ma_dv HAVING SUM(luong) > 4 000 000 NGÔN NGỮ SQL Các truy vấn lồng nhau Để tìm mã số và tên những nhân viên phòng ‘Kỹ thuật’ có thể dùng câu truy vấn sau SELECT manv, ho_ten FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’); 58 59 NGÔN NGỮ SQL Các truy vấn lồng nhau Xét câu hỏi: ‘Tìm tên của những nhân viên có lương đạt trên mức lương trung bình của tất cả các nhân viên, đồng thời cho biết luôn mức chênh lệch này’ SELECT ho_ten , luong – (SELECT AVG(luong) FROM nhan_vien) AS hieu FROM nhan_vien WHERE luong > (SELECT AVG(luong) FROM nhan_vien); 60 NGÔN NGỮ SQL Các truy vấn lồng nhau Câu ORDER BY không được sử dụng trong một câu truy vấn con mặc dù nó có thể được dùng trong câu truy vấn ngoài cùng. Danh sách các mục được liệt kê bởi câu truy vấn con SELECT phải chứa tên của một cột hoặc một biểu thức trừ phi câu truy vấn con này dùng từ khóa EXISTS. 61 NGÔN NGỮ SQL Các truy vấn lồng nhau Theo ngầm định, các tên cột trong câu truy vấn con tham chiếu đến tên bảng trong câu FROM của truy vấn con này, hoặc có thể tham chiếu đến bảng trong câu FROM của truy vấn ngoài bằng việc xác định tên cột đó. Khi một câu truy vấn con là một trong hai toán hạng của một biểu thức so sánh thì truy vấn con này phải xuất hiện ở vế phải của so sánh. 62 NGÔN NGỮ SQL Các truy vấn lồng nhau Muốn biết thông tin về những nhân viên làm việc cho đơn vị có địa điểm là ’23 Lý Thường Kiệt’, có thể dùng truy vấn như sau SELECT * FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM dia_diem_dv WHERE dia_diem = ’23 Lý Thường Kiệt); 63 NGÔN NGỮ SQL Các truy vấn lồng nhau “ Tìm tên và lương của những người có lương cao hơn lương mọi nhân viên thuộc đơn vị mã P2 “. SELECT ho_ten FROM nhan_vien WHERE luong > ALL (SELECT luong FROM nhan_vien WHERE ma_dv = ’P2’); 64 NGÔN NGỮ SQL Các truy vấn lồng nhau Câu hỏi “ Cho biết thông tin về những nhân viên có lương cao hơn ít nhất một nhân viên làm ở phòng kỹ thuật” SELECT ho_ten FROM nhan_vien WHERE luong > SOME (SELECT luong FROM nhan_vien WHERE ma_dv IN (SELECT ma_dv FROM phong WHERE ten_phong = ‘Kỹ thuật’) ); 65 NGÔN NGỮ SQL Các truy vấn lồng nhau “ Cho biết tên phòng có trung bình lương cao nhất” sẽ được viết là SELECT ten_phong FROM phong WHERE ma_dv IN (SELECT ma_dv FROM nhan_vien GROUP BY ma_dv HAVING AVG(luong) >= ALL (SELECT AVG(luong) FROM nhan_vien GROUP BY ma_dv) ); 66 NGÔN NGỮ SQL Dùng các biến bộ Để tìm thông tin về tên và lương của trưởng phòng nghiên cứu phát triển SELECT nv.ho_ten, nv.luong FROM nhan_vien nv, phong p WHERE nv.manv = p.ma_tp AND p.ten_phong = ‘Nghiên cứu và phát triển’ Các biến bộ rất hữu ích trong việc so sánh hai bộ trong cùng một quan hệ. 67 NGÔN NGỮ SQL Dùng các biến bộ Có thể tìm nhân viên có lương cao hơn ít nhất lương của một nữ nhân viên bằng câu lệnh sau : SELECT DISTINCT nv.ho_ten, nv.luong FROM nhan_vien a, nhan_vien b WHERE a.luong > b.luong AND b.gioi_tinh = ‘nu’ 68 NGÔN NGỮ SQL Các quan hệ được dẫn xuất và khung nhìn SQL-92 cho phép dùng câu hỏi con trong câu FROM, trong đó bảng kết quả của câu hỏi con phải được đặt tên và các cột được phép đổi tên. Một kết quả như vậy được gọi là một quan hệ (bảng) được dẫn xuất. 69 NGÔN NGỮ SQL Các quan hệ được dẫn xuất và khung nhìn “Tìm lương trung bình của các phòng có lương trung bình lớn hơn 2 100 000”, trong SQL (chuẩn 92) có thể viết như sau: SELECT ma_phong, luong_tb FROM (SELECT ma_dv, AVG(luong) FROM nhan_vien GROUP BY ma_dv) AS ket_qua (ma_phong, luong_tb) WHERE luong_tb > 2 100 000 Xin cảm ơn ! Hồ Cẩm Hà- ĐHSP HN CHƯƠNG 4 THIẾT KẾ CƠ SỞ DỮ LIỆU QUAN HỆ “Làm thế nào để có một cơ sở dữ liệu tốt?” Hồ Cẩm Hà- ĐHSP HN Quá trình thiết kế CSDL Thế giới thực Tập hợp các yêu cầu và phân tích Các yêu cầu CSDL Thiết kế khái niệm Lược đồ logic (trong một mô hình dữ liệu bậc cao) Ánh xạ mô hình dữ liệu Lược đồ khái niệm (trong mô hình dữ liệu của một DBMS cụ thể ) Thiết kế vật lý Lược đồ trong (đối với cùng một DBMS cụ thể đó) Không phụ thuộc DBMS DBMS cụ thể Hồ Cẩm Hà- ĐHSP HN Cần loại bỏ dư thừa dữ liệu Khi dư thừa dữ liệu dẫn đến những khó khăn khi cập nhật dữ liệu Hồ Cẩm Hà- ĐHSP HN Phụ thuộc hàm. Dạng dư thừa dữ liệu thường gặp Có X→Y trên R(U): ∀r(R) ∀ t1, t2 ∈ r, t1[X] = t2[X] ⇒ t1[Y]=t2[Y]. Hồ Cẩm Hà- ĐHSP HN Hệ qui tắc suy diễn Amstrong A1. Phản xạ (Reflexivity). Nếu Y ⊆ X thì X→Y A2. Tăng trưởng (Augmentation). Nếu X→Y thì mọi Z⊆U, XZ→YZ A3. Bắc cầu (Transitivity). Nếu X→Y và Y→Z thì X→Z Hồ Cẩm Hà- ĐHSP HN Hệ tiên đề Armstrong là đúng và đủ Hồ Cẩm Hà- ĐHSP HN Các qui tắc suy diễn bổ sung Quy tắc hợp (the union rule) Nếu {X→Y, X→Z} đúng thì X→YZ đúng Quy tắc giả bắc cầu (the pseudotransitivity rule) {X→Y, WY→Z} đúng thì WX→Z đúng Quy tắc tách (the decomposition rule) Nếu (X→Y) đúng và Z⊆Y thì X→Z đúng. Hồ Cẩm Hà- ĐHSP HN Tập phụ thuộc hàm tối tiểu F và G là tương đương nếu F+=G+, ký hiệu F~G. Có thể kiểm tra được F và G, tập nào phủ tập nào và chúng có tương đương hay không (tính X+) Định lí 7.9: Cho tập phụ thuộc hàm F luôn tìm được phủ tối tiểu của F Hồ Cẩm Hà- ĐHSP HN Tập phụ thuộc hàm tối tiểu Tập PTH F là tối tiểu nếu: 1. Vế phải của mỗi phụ thuộc trong F gồm đúng một thuộc tính. 2. Không thể bỏ đi một phụ thuộc nào trong F mà vẫn thu được một tập phụ thuộc tương đương với nó. 3. Không thể bỏ đi bất kỳ một thuộc tính nào ở vế trái của một phụ thuộc nào trong F mà vẫn thu được một tập phụ thuộc tương đương với nó. Hồ Cẩm Hà- ĐHSP HN Tập phụ thuộc hàm tối tiểu Cho F = {A→B, B→A, A→C, C→A, B→C}. Có thể tìm được hai tập phụ thuộc tối tiểu tương đương với F F1 = {A→B, B→C, C→A} F2 = {A→B, B→A, A→C, C→A} Hồ Cẩm Hà- ĐHSP HN Phép tách các lược đồ quan hệ Việc tách một lược đồ quan hệ trước hết là thay thế tập U các thuộc tính bằng những tập con U1, U2,, Uk của nó sao cho U = U1 ∪ U2 ∪∪ Uk. Chú ý rằng ở đây, ta không đòi hỏi U1, U2,, Uk phải rời nhau. Hồ Cẩm Hà- ĐHSP HN Phép tách các lược đồ quan hệ Khi đó, việc thay thế lược đồ R = 〈U, F〉 bằng các lược đồ con R1 = 〈U1, F1〉, R2 = 〈U2, F2〉,, Rk = 〈Uk, Fk〉 được gọi là một phép tách lược đồ quan hệ đã cho 〈U, F〉. ký hiệu là ρ = (R1, R2,, Rk). Đôi khi, kí hiệu ρ = (U1, U2,, Uk). Hồ Cẩm Hà- ĐHSP HN Phép tách các lược đồ quan hệ Ta sử dụng một số ký hiệu sau: Dấu hoa thị (*) ký hiệu phép kết nối tự nhiên trên giao của hai tập thuộc tính. ρ = (R1, R2,, Rk) hay ρ = (U1, U2,, Uk) là phép tách lược đồ quan hệ trên U thành các lược đồ con tương ứng với các tập con thuộc tính U1, U2,, Uk. ri = là hình chiếu của quan hệ r lên tập con thuộc tính Ui mρ(r) = r1 * r2 * * rk là kết quả của phép kết nối tự nhiên của các hình chiếu của r lên các tập con thuộc tính trong phép tách ρ. Hồ Cẩm Hà- ĐHSP HN Phép tách các lược đồ quan hệ Phép tách U thành {U1, U2,, Uk} được gọi là kết nối không thất thoát (hay ngắn gọn là LJ) nếu với mỗi quan hệ r của lược đồ này, ta đều có r = r1 * r2 * * rk = mρ(r) Phép tách bảo toàn các phụ thuộc của F Hồ Cẩm Hà- ĐHSP HN Tách kết nối không mất thông tin Kiểm tra được tính kết nối không thất thoát của một phép tách (thuật toán 3.2) Ví dụ U = ABCDE, U1 = AD, U2 = AB, U3 = BE, U4 = CDE, U5 = AE Tập các phụ thuộc hàm là: A→C, B→C, C→D, DE→C, CE→A. A B C D E U1 a1 b12 b13 a4 b15 U2 a1 a2 b23 b24 b25 U3 b31 a2 b33 b34 a5 U4 b41 b42 a3 a4 a5 U5 a1 b52 b53 b54 a5 Hồ Cẩm Hà- ĐHSP HN Tách kết nối không mất thông tin Tập các phụ thuộc hàm là: A→C, B→C, C→D, DE→C, CE→A. A B C D E U1 a1 b12 b13 a4 b15 U2 a1 a2 b13 b24 b25 U3 b31 a2 b13 b34 a5 U4 b41 b42 a3 a4 a5 U5 a1 b52 b13 b54 a5 A B C D E U1 a1 b12 b13 a4 b15 U2 a1 a2 b23 b24 b25 U3 b31 a2 b33 b34 a5 U4 b41 b42 a3 a4 a5 U5 a1 b52 b53 b54 a5 Hồ Cẩm Hà- ĐHSP HN Tách kết nối không mất thông tin Tập các phụ thuộc hàm là: A→C, B→C, C→D, DE→C, CE→A. A B C D E U1 a1 b12 b13 a4 b15 U2 a1 a2 b13 b24 b25 U3 b31 a2 b13 b34 a5 U4 b41 b42 a3 a4 a5 U5 a1 b52 b13 b54 a5 A B C D E U1 a1 b12 b13 a4 b15 U2 a1 a2 b23 a4 b25 U3 a1 a2 a3 a4 a5 U4 a1 b42 a3 a4 a5 U5 a1 b52 a3 a4 a5 Hồ Cẩm Hà- ĐHSP HN Phép tách các lược đồ Mặc dù là những tính chất quan trọng của phép tách lược đồ quan hệ nhưng một phép tách có thể thoả mãn tính chất này nhưng lại không thoả mãn tính chất kia. Chẳng hạn, phép tách lược đồ quan hệ 〈ABCD, {A→B, C→D}〉 thành hai lược đồ 〈AB, {A→B}〉 và 〈CD, {C→D}〉 là phép tách bảo toàn phụ thuộc nhưng không phải là phép tách với kết nối không thất thoát. Hồ Cẩm Hà- ĐHSP HN phép tách các lược đồ Ta xét lược đồ CSZ với ba thuộc tính C (City), S (Street) và Z (Zip code) và tập phụ thuộc hàm F = {CS→Z, Z→C}. Từ phụ thuộc hàm Z→C hay CS∩CZ→CS−CZ suy ra rằng phép tách CSZ thành hai lược đồ CS và CZ có tính chất kết nối không mất thông tin nhưng không có tính chất bảo toàn phụ thuộc. Hồ Cẩm Hà- ĐHSP HN 1NF Hồ Cẩm Hà- ĐHSP HN 2NF Cho lược đồ quan hệ R = 〈U, F〉 với khoá K. R được gọi là thuộc dạng chuẩn thứ hai (2NF) nếu nó thuộc dạng chuẩn thứ nhất và mọi thuộc tính A∉K đều phụ thuộc đầy đủ vào K. Hồ Cẩm Hà- ĐHSP HN 3NF Lược đồ quan hệ R = 〈U, F〉 được gọi là thuộc dạng chuẩn thứ ba (3 NF) nếu không có thuộc tính không khóa phụ thuộc bắc cầu vào khóa Nghĩa là: nếu không tồn tại một khoá X, một tập thuộc tính Y⊆U và một thuộc tính A∉XY làm cho các điều kiện sau được thoả mãn: (X→Y), (Y→A), và không có (Y→X). Hồ Cẩm Hà- ĐHSP HN BCNF Lược đồ quan hệ R = 〈U, F〉 được gọi là thuộc dạng chuẩn Boyce-Codd (BCNF) nếu từ (X→A) đúng trong R và A∉X kéo theo X là siêu khoá. Định lý 7.12 Nếu lược đồ quan hệ R = 〈U, F〉 thuộc dạng chuẩn Boyce-Codd (BCNF) thì nó thuộc dạng chuẩn thứ ba. Hồ Cẩm Hà- ĐHSP HN Chuẩn hoá lược đồ quan hệ Bổ đề 7.7: Giả sử R = 〈U, F〉 là một lược đồ quan hệ và ρ = (R1, R2, Ri,... Rk) là một phép tách của R, trong đó ∀i, Ri = 〈Ui, Fi〉. Giả sử ρ là kết nối không thất thoát. Khi đó, nếu thay thế lược đồ Ri trong ρ bởi S1, S2,, Sm, với σ = (S1, S2,, Sm) là phép tách kết nối không thất thoát của Ri thì phép tách τ = (R1, R2,, Ri-1, S1, S2,, Sm, Ri+1,, Rk) thu được cũng là kết nối không thất thoát. Giả sử ρ là kết nối không thất thoát. Nếu bổ sung vào ρ một số lược đồ quan hệ trên U (Rk+1,,Rn) thì phép tách τ = (R1, R2,, Rk, Rk+1,,Rn) thu được cũng là kết nối không thất thoát. Hồ Cẩm Hà- ĐHSP HN Chuẩn hoá lược đồ quan hệ Ví dụ Tách để đưa lược đồ về chuẩn BCNF (trang 33) Hồ Cẩm Hà- ĐHSP HN Ví dụ U1 = CSG F1 = {CS→G} Kho¸ CS U = CTHRSG F = {C→T, HR→C, TH→R, CS→G, HS→R} Khãa HS V1 = CTHRS FV1 = {C→T, TH→R, HR→C, HS→R} Khãa HS U2 = CT F2 = {C→T} Kho¸ C V2 = CHRS FV2 = {CH→R, HR→C, HS→R} Kho¸ HS U3 = CHR F3 = {CH→R, HR→C} Khãa CH hoÆc HR U4 = CHS F4 = {HS→C} Khãa HS Hồ Cẩm Hà- ĐHSP HN Phép tách bảo toàn phụ thuộc thành 3NF Nếu có những thuộc tính không xuất hiện trong bất kỳ một phụ thuộc hàm nào của F, ở cả vế trái lẫn vế phải thì ta xác định một lược đồ quan hệ gồm những thuộc tính này rồi xoá chúng khỏi U. Nếu một trong các phụ thuộc hàm của F chứa toàn thể các thuộc tính của U thì phép tách cần tìm chỉ gồm R. Trường hợp còn lại, phép tách kết quả gồm các lược đồ ứng với các tập thuộc tính có dạng XA, trong đó mỗi phụ thuộc hàm X→A là thuộc F. Tuy vậy, nếu xảy ra tình huống X→A1, X→A2,, X→Ak cùng thuộc F thì thay cho các lược đồ với tập thuộc tính dạng XAi, ta sử dụng lược đồ ứng với tập thuộc tính XA1A2Ak vì rõ ràng sự thay thế này cho kết quả gọn hơn. Hồ Cẩm Hà- ĐHSP HN Tách vừa là LJ vừa bảo toàn phụ thuộc §Þnh lý 7.14 Cho R(U) lµ mét l−îc ®å quan hÖ, trong ®ã tËp thuéc tÝnh U = {A1, A2,...,An} vµ F lµ tËp c¸c phô thuéc hµm x¸c ®Þnh trªn R. Kh«ng gi¶m tæng qu¸t, gi¶ sö F lµ mét phñ tèi tiÓu cã d¹ng: F = {Yj→ Aij ⏐j=1, 2,..., m}. Gäi X lµ mét kho¸ cña l−îc ®å R(U, F). Khi ®ã phÐp t¸ch: ρ = (Y1Ai1, Y2Ai2,..., YmAim , X) lµ mét phÐp t¸ch cña R, tháa m·n ba tÝnh chÊt sau: z ρ lµ mét phÐp t¸ch b¶o toµn th«ng tin; z ρ lµ mét phÐp t¸ch b¶o toµn tËp F; z C¸c l−îc ®å con trong ρ ®Òu ë 3NF. Hồ Cẩm Hà- ĐHSP HN Phụ thuộc đa trị Cho R(U); X vµ Y lµ hai tËp con cña U, Z = U \ XY. X→→Y Khi mäi quan hÖ r ∈ R(U) víi hai bé bÊt kú t1, t2 ∈ r: t1[X] = t2[X] ⇒ ∃ t3 ∈ r sao cho t3[X] = t1[X], t3[Y] = t1[Y] vµ t3[Z] = t2[Z]. (v× t1, t2 b×nh ®¼ng nªn ∃ t4 sao cho t4[X] = t1[X], t4[Y] = t2[Y] vµ t4[Z] = t1[Z] ). Chóng ta cã thÓ kÝ hiÖu X→→ Y | Z. Hồ Cẩm Hà- ĐHSP HN Phụ thuộc đa trị NÕu X→ Y tho¶ trªn r th× X→→ Y còng tho¶ trªn r. Do vËy mỗi phô thuéc hµm ®Òu lµ phô thuéc ®a trÞ. Hồ Cẩm Hà- ĐHSP HN 4NF L−îc ®å quan hÖ R ®−îc gäi lµ thuéc d¹ng chuÈn thø bèn (4NF) nÕu cã phô thuéc ®a trÞ kh«ng tÇm th−êng X→→ Y ®óng trªn R th× X lµ siªu khãa. Nãi mét c¸ch kh¸c, R lµ 4NF nÕu cã phô thuéc ®a trÞ X→→ Y trªn R , trong ®ã Y ≠ ∅, Y ⊄ X vµ XY kh«ng chøa tÊt c¶ c¸c thuéc tÝnh cña R th× X lµ mét siªu kho¸* cña R Hồ Cẩm Hà- ĐHSP HN Phụ thuộc đa trị và 4NF VÝ dô 7.20 XÐt l¹i l thuéc hµm vµ C HR HT CS HS C kh«ng lÖ −îc ®å quan hÖ TBK(CTHRSG) cho ë vÝ dô 7.17 cïng víi tËp D c¸c phô phô thuéc ®a trÞ nh− sau: →T Mçi líp häc phÇn do mét gi¶ng viªn chÞu tr¸ch nhiÖm. →C T¹i mçi phßng häc, trong mçi giê häc chØ cã mét líp häc phÇn. →R T¹i mçi giê häc, mçi gi¶ng viªn chØ cã thÓ d¹y ®−îc ë mét phßng häc. →G §èi víi mçi líp häc phÇn, mçi sinh viªn chØ cã mét ®iÓm ®¸nh gi¸. →R T¹i mçi giê häc, mçi sinh viªn chØ cã mÆt ë mét phßng häc. →→HR TËp c¸c cÆp phßng-giê häc ®−îc x¸c ®Þnh theo mçi häc phÇn mµ thuéc g× vµo bÊt cø thuéc tÝnh nµo kh¸c. Hồ Cẩm Hà- ĐHSP HN VÝ dô t¸ch LJ ®−a vÒ 4NF z XÐt thÊy C→→ HR vi ph¹m 4NF, do vËy t¸ch TBK thµnh (CHR) vµ (CTSG). L−îc ®å con (CHR) cã khãa lµ HR ®· ë d¹ng chuÈn bèn. L−îc ®å (CTSG) cã khãa lµ CS ch−a ë d¹ng chuÈn bèn v× cã C→→ T ( suy ra tõ C→ T) vi ph¹m chuÈn bèn. z T¸ch (CTSG) thµnh hai l−îc ®å con ®Òu ë d¹ng chuÈn bèn lµ (CT) vµ (CSG). z PhÐp t¸ch ρ = {CHR, CT, CSG} lµ phÐp t¸ch - kÕt nèi kh«ng tæn thÊt vµ mçi l−îc ®å trong nã ®Òu ë d¹ng chuÈn bèn.