數(shù)據(jù)庫原理與設(shè)計陶宏才

1、數(shù)據(jù)庫原理及設(shè)計Principle and Design of Database 主講: 朱 焱 博 士 SIST, SWJTU2009.101學(xué)習(xí)目的和要求從概念化數(shù)據(jù)庫設(shè)計 進入邏輯數(shù)據(jù)庫設(shè)計關(guān)系數(shù)據(jù)模型的基本概念和術(shù)語關(guān)系數(shù)據(jù)模型上的完整性約束關(guān)系代數(shù)關(guān)系運算第三章 關(guān)系數(shù)據(jù)模型 DBMS無關(guān) DBMS有關(guān) 數(shù)據(jù)庫設(shè)計過程概念模式內(nèi)模式外模式1外模式n邏輯設(shè)計模式優(yōu)化數(shù)據(jù)庫外模式2數(shù)據(jù)庫需求概念設(shè)計用戶組1用戶組n需求收集和分析使用高級數(shù)據(jù)模型ERM使用特定的DBMS的數(shù)據(jù)模型集成物理DB設(shè)計關(guān)系數(shù)據(jù)模型數(shù)據(jù)模型的三要素,是本章學(xué)習(xí)的路標靜態(tài)數(shù)據(jù)結(jié)構(gòu);動態(tài)數(shù)據(jù)操作;完整性約束這三要素體現(xiàn)

2、在如下問題中:如何表達數(shù)據(jù)本身？如何表達數(shù)據(jù)間的聯(lián)系？有哪些動態(tài)數(shù)據(jù)操作？能表達哪些完整性約束？完整性約束如何設(shè)定？完整性約束的效果？如何進行關(guān)系模型的DB設(shè)計？ER模式向關(guān)系數(shù)據(jù)模式邏輯映射概念模式與邏輯模式的對應(yīng)ER SchemaRDB Schema數(shù)據(jù)結(jié)構(gòu)實體集+屬性聯(lián)系集+屬性主鍵（候選鍵）？數(shù)據(jù)操作/？約束一般性約束（聯(lián)系類型約束）鍵約束參與約束？ ER 關(guān)系 - 表描述實體關(guān)系模式（relational schema)的關(guān)鍵：基本表（base table)，也簡稱關(guān)系表。表由“列”（Column）和“行”（Row）組成。行又叫元組（tuple），列又叫字段（field）。數(shù)據(jù)對象（

3、實體集）用關(guān)系（表）來定義。ER中實體集的屬性表中的列（字段、屬性）；ER中實體集中的每個實體實例表中的行（元組、記錄）。姓名學(xué)號D.O.BPeter Bach20001015-10-1982Lisa Baumann200010212-7-1982列行學(xué)生表ER關(guān)系 - 表描述聯(lián)系ER模式中聯(lián)系是單獨描述的。而在關(guān)系模式中，聯(lián)系也可以用表來描述。例如，選課聯(lián)系選課表。表中有來自學(xué)生和課程兩個實體的屬性，而成績是選課關(guān)聯(lián)自身的描述屬性。列的取值范圍稱為域（Domain）。例如，成績的范圍一般是：0-100。學(xué)生選課表姓名學(xué)號課程號課程名成績Peter Bach2000101A1033數(shù)據(jù)庫85L

4、isaBaumann2000102A1030數(shù)據(jù)倉儲化91關(guān)系模式、關(guān)系、主鍵、外鍵關(guān)系模式（Schema）由關(guān)系名、和各個列表示。 數(shù)學(xué)上表示為：R（A1，A2，An)。關(guān)系實例：由表中的各行構(gòu)成。常簡稱關(guān)系；行序不重要，而列序重要。每個元組的字段必須對應(yīng)關(guān)系模式中的字段。（物理存儲要求的） 關(guān)系實例表示：，ai為屬性Ai的值。ER模型中的候選鍵關(guān)系中的候選鍵ER模型中的主鍵關(guān)系中的主鍵 (Primary Key, PK)關(guān)系中的新概念-外鍵，描述表與表之間的關(guān)聯(lián)。表與表的聯(lián)系姓名學(xué)號D.O.B班號Peter Bach2000-1015-10-1986計算機200001主鍵班號班名人數(shù) 計算

5、機200001計1班50外鍵學(xué)生表班級表主鍵/候選鍵外鍵(Foreign Key,FK): A表中的某個屬性（組）是B表中的候選鍵或主鍵，則稱該屬性（組）為A表的外鍵。要使B表中的某個屬性成為A表的外鍵，必須確定該屬性為B表的主鍵或候選鍵。如何指定？完整性約束的任務(wù)。關(guān)系模型術(shù)語關(guān)系模式術(shù)語一般術(shù)語關(guān)系模式記錄類型屬性字段屬性值字段值元組1記錄1計算機2000015-10-19862000101Peter Bach班號D.O.B學(xué)號姓名R關(guān)系的性質(zhì)關(guān)系是一個集合。集合中的元素是元組，每個元組的屬性數(shù)目應(yīng)該相同。關(guān)系是一種規(guī)范化了的二維表格，不是一般的二維表。它的性質(zhì)是：關(guān)系中每一個屬性值都是不

6、可分解的；關(guān)系中不允許出現(xiàn)重復(fù)元組；關(guān)系是元組的集合，因此無行序；關(guān)系的列是有序的。邏輯映射之?dāng)?shù)據(jù)結(jié)構(gòu)映射概念模式與邏輯模式的對應(yīng)ER模型關(guān)系數(shù)據(jù)模型數(shù)據(jù)結(jié)構(gòu)實體集實體集屬性聯(lián)系集與屬性實體的實例主鍵/候選鍵?？數(shù)據(jù)操作/？約束聯(lián)系類型約束鍵約束完全參與約束？ 關(guān)系表表的主鍵/候選鍵表的列表和列表中的各行？SQL語言 SQL發(fā)音為Sequel，現(xiàn)在趨向于發(fā)音為S-Q-L。 最早在IBM System-R RDBMS 上使用的查詢語言； 第一個標準由ANSI于1986年制訂,稱為SQL-86； 1992年推出了SQL-92，是大多數(shù)RDBMS支持的版本； 1999年提出SQL:1999(SQL3

7、)，是SQL-92的擴展。 SQL在數(shù)據(jù)庫界之外也受到重視。在軟件工程、AI、萬維網(wǎng)數(shù)據(jù)管理、等領(lǐng)域顯示了潛力。 SQL成為國際標準后，由于各種類型的計算機和DBS都采用SQL作為其存取語言和標準接口，從而使數(shù)據(jù)庫世界有可能鏈接為一個整體。前景非常好。幾個重要的SQL語言命令SQL DDL針對表結(jié)構(gòu)的操作Create（創(chuàng)建） Drop（刪除）Alter（修改）SQL DML針對表中數(shù)據(jù)的操作Insert（插入） Delete（刪除）Update（修改）Select（查詢）關(guān)系模型中的數(shù)據(jù)操作 動態(tài)的數(shù)據(jù)操作：增加、刪除、修改和查詢，簡稱：增刪改查詢。增加：INSERT R VALUES （林海

8、，男）修改：UPDATE R SET name=張湖邏輯映射之?dāng)?shù)據(jù)操作概念模型與邏輯模型的對應(yīng)ER模型關(guān)系數(shù)據(jù)模型數(shù)據(jù)結(jié)構(gòu)實體集實體集屬性聯(lián)系集與屬性實體的實例主鍵/候選鍵?？數(shù)據(jù)操作/？約束聯(lián)系類型約束鍵約束完全參與約束？ 關(guān)系表表的主鍵/候選鍵表的列表和列表中的各行使用SQL語句對表進行操作？關(guān)系模型上完整性約束完整性約束（Integrity Constraints,ICs）可以幫助阻止非法數(shù)據(jù)的輸入。它要求存入DB的數(shù)據(jù)應(yīng)滿足一些條件。在定義一個關(guān)系模式的同時，或之后定義完整性約束。當(dāng)表中數(shù)據(jù)發(fā)生變化（如Insert、Delete、Update）時，DBMS即檢查更新的數(shù)據(jù)是否滿足完整性

9、約束指定的條件。幾種完整性約束：域約束、主鍵約束、唯一約束、外鍵約束等。幾種完整性約束（1）域限制（Domain Constraint）每個屬性A的值必須是來自域 dom（A）的原子值。例如，表中某列的數(shù)據(jù)類型為“整數(shù)型”，那么該列的各記錄值就不能是“字符串”。不必顯示指定。主鍵約束（Primary Key Constraint）概念：針對主鍵而言，保證主鍵的完整性。要求：主鍵值必須唯一，且不能為空值。例如：作者不能作主鍵。需要顯示指定。（如何指定，以后介紹） 幾種完整性約束（2）唯一約束（Unique Constraint）主鍵可以用主鍵約束來保證其值的完整性。因為候選鍵也能唯一標識關(guān)系實例

10、元組，因此，保證候選鍵的完整性，需要唯一約束。要求值唯一，允許有一個且僅有一個空值。以上是“表本身”的完整性約束。外鍵限制（Foreign Key Constraint）也被稱為“參照完整性約束”。目的：用來維護表與表之間的數(shù)據(jù)一致性，即其中一張表的改動，可能要求另一張表要作出某些改動，以保持數(shù)據(jù)一致。為使DBMS能做這樣的檢查，則應(yīng)指定這種涉及兩個表的ICs，此即外鍵限制。主表與從表主表：指外鍵在另一張表中作主/候選鍵的表。（例中的班級表）從表：指含有外鍵的表。（例中的學(xué)生表）姓名學(xué)號D.O.B班號Peter Bach2000-1015-10-1986計算機200001班號班名人數(shù) 計算機2

11、00001計1班50 計算機200010計10班45班級表（主表）學(xué)生表 （從表）外鍵約束的例子(主表從表）姓名學(xué)號D.O.B班號Peter Bach2000-1015-10-1986計算機200001班號班名人數(shù) 計算機200001計1班50 計算機200010計10班45學(xué)生表 （從表）班級表（主表）外鍵主鍵插入刪除修改分析外鍵約束（主表從表）對主表進行三種操作，觀察對表間完整性的影響： 對主表中的主鍵進行操作插入：要求插入值滿足主鍵限制即可，不影響其它表。修改：可能會影響與該主鍵相關(guān)的從表的外鍵值。當(dāng)相應(yīng)的外鍵值存在時，有兩個策略可用：一是改變對應(yīng)從表的所有外鍵值，使之與主鍵一致；其二是

12、不允許修改主表中的主鍵值。刪除：可能會影響與該主鍵相關(guān)的從表的外鍵值。若相應(yīng)的外鍵值存在時，策略有二：一是不允許刪除主表的主鍵值；二是級聯(lián)刪除從表中相應(yīng)外鍵值所在的行。外鍵約束的例子(從表主表）姓名學(xué)號D.O.B班號Peter Bach2000-1015-10-1986計算機200001主鍵班號班名人數(shù) 計算機200001計1班50學(xué)生表（從表）班級表（主表）外鍵主鍵/候選鍵插入刪除修改分析外鍵約束（從表主表）對從表中的外鍵操作插入：要求插入的外鍵值應(yīng)“參照”（Reference）主表中的主鍵值。 修改：要求修改的外鍵值“參照”主表中的主鍵值 。刪除：不需要參照主表中的主鍵值。外鍵約束小結(jié)維護

13、表間數(shù)據(jù)完整性從兩個方向上完成，即：主表從表：“主表”中的主鍵值在修改和刪除時，“從表”中與該主鍵值相同的外鍵值可“級聯(lián)”（CASCADE）修改和刪除，或“禁止”（NO ACTION）“主表”主鍵值的修改和刪除。從表主表：表示“從表”中的外鍵值在插入和修改時，其值應(yīng)“參照”（Reference）“主表”中的主鍵值。表間數(shù)據(jù)完整性維護的實現(xiàn)利用外鍵約束定義對從表定義外鍵限制（或稱參照完整性）完成主表和從表間兩個方向的數(shù)據(jù)完整性；利用觸發(fā)器主表的觸發(fā)器維護主表到從表方向的數(shù)據(jù)完整性，而從表的觸發(fā)器維護從表到主表方向的參照完整性。 SQL-92標準只支持外鍵約束方式。如果使用觸發(fā)器，必須檢查所用的R

14、DBMS是否支持。 其他的約束 檢查限制域限制、主鍵限制、唯一限制和外鍵限制是關(guān)系數(shù)據(jù)模型中最基本的限制，大多數(shù)商用系統(tǒng)都支持他們。其他還有更一般的限制，如：檢查限制(Check Constraint，亦稱“表限制”)檢查某一列值是否在某一取值范圍之內(nèi)；表中某幾列是否滿足指定的條件。與單個表有關(guān)。例如：中學(xué)生的年齡定義為兩位整數(shù)，范圍很大，用戶可以寫如下規(guī)則，將年齡限制在10-20歲之間CHECK(AGE BETWEEN 10 AND 20)其他的約束性 斷言限制 斷言限制（Assertion）檢查表中個別列、整個表或表與表之間是否滿足指定的條件。與多個表有關(guān)，是關(guān)系之間的約束。例如：希望限定

15、計算機圖書的作者一定是女性。這時，僅靠鍵約束、唯一約束和外鍵限制不能實現(xiàn)，因為這個要求涉及到兩張表（關(guān)系）：Book，Author。可以寫為：Create Assertion NoManComputerAuthorCheck （Book.計算機類.作者 Author.性別為男性 = 空集) 與主鍵約束和唯一限制的定義不同，定義表約束和斷言約束時要顯式定義。完整性約束的實施關(guān)系創(chuàng)建并指定了ICs后，當(dāng)關(guān)系“更新”時（指插入、刪除和修改）應(yīng)實施檢查。對域限制、主鍵限制和唯一限制的實施由于影響直接，故只要插入/刪除/修改命令違背了限制，即被拒絕。而其它ICs（如一般性限制）的違背檢查通常是在每個SQ

16、L語句之后。對參照完整性（外鍵）限制的實施外鍵限制的影響較復(fù)雜，要從兩個方向分別進行，但不是同時進行。完整性約束小結(jié)SQL中把完整性約束分成三大類：域約束基本表約束：主鍵約束；候選鍵約束（唯一約束）；外鍵約束; 檢查約束表間約束: 斷言邏輯映射之約束概念模式與邏輯模式的對應(yīng)ER模型關(guān)系數(shù)據(jù)模型數(shù)據(jù)結(jié)構(gòu)實體集實體集屬性聯(lián)系集與屬性實體的實例主鍵/候選鍵?？數(shù)據(jù)操作/？約束聯(lián)系類型約束鍵約束完全參與約束？ 關(guān)系表表的主鍵/候選鍵表的列表和列表中的各行使用SQL語句對表進行操作域約束、主鍵約束、唯一約束、外鍵約束、檢查約束、斷言SQL Server對數(shù)據(jù)完整性約束的支持不同的數(shù)據(jù)庫管理系統(tǒng)廠商開發(fā)不

17、同類型的SQL，被稱為數(shù)據(jù)庫方言。例如：Microsoft SQL Server 產(chǎn)品中的SQL方言叫Transact-SQL。 T-SQL，即事務(wù)SQL，是對標準SQL的擴展。T-SQL由SQL語句、函數(shù)和存儲過程三部分組成。T-SQL除了具有標準的子語言：DDL，DML，DCL之外，還具有過程控制能力和事務(wù)控制能力。 T-SQL有兩類變量：局部變量：如 name, price全局變量(系統(tǒng)定義，用戶只能引用，不能修改、定義）：如 error, rowcountSQL Server系統(tǒng)數(shù)據(jù)類型 （一）pp179(pp139) 表4-2Tinyint, 1 byte,數(shù)值范圍是028-1精確小

18、數(shù)是numeric(p,s)、decimal(p,s).近似小數(shù)是float和real。Datetime表示日期和時間。例如：0329/ 2006 10:05:00:00:00 PM SQL Server系統(tǒng)數(shù)據(jù)類型 （二）Char(n)和varchar(n)Char(10)可表示最多10個字符長的字符串,物理存儲空間是10個字符,不能改變.如果輸入字符少于10,實際存儲仍然占滿10個字符長,不足部分用前空格補足.varchar(10)則不同，10為最大字符串長度。實際輸入是幾個字符,存儲時只占滿幾個字符,是可變的。1234567890，1，650都是合法數(shù)據(jù)。但它們在上述兩種數(shù)據(jù)類型中所用空

19、間不同。Nchar,Nvarchar,ntext是Unicode 數(shù)據(jù)類型SQL Server對完整性限制的支持SQL Server中的數(shù)據(jù)完整性可粗分為兩大類，如下表。完整性分類現(xiàn)有實現(xiàn)方式備 注表本身的完整性Default（缺?。┲付械娜笔≈礡ule（規(guī)則）指定列的取值范圍Check Constraint（檢查限制）SQL語句中有列級（即只涉及一列）和表級（涉及表中多列）兩種定義方法。Primary Key（主鍵限制）Unique（唯一限制）表間的完整性Foreign Key（外鍵限制）參照完整性限制的體現(xiàn)，亦分列級與表級定義Trigger（觸發(fā)器）可利用觸發(fā)器來維護表間數(shù)據(jù)完整性 默認

20、值（1）默認值（default）：用于為列或用戶自定義數(shù)據(jù)類型指定缺省值，每一列或自定義類型只能有一個缺省值。當(dāng)用戶沒有給指定有默認值的列輸入數(shù)據(jù)時，RDBMS自動用該默認值代替。兩種方式：表定義時設(shè)定CREATE TABLE publishers(pub_id char(4)NOT NULL, pub_name varchar(40) NULL, city varchar(20)DEFAULT Pasadena, state char(2)DEFAULT CA)默認值驗證屬性Pub_idPub_namecitystate元組1 0001NULLPasadenaCACREATE table 是

21、創(chuàng)建表命令。 在SQL Server中，對字符型數(shù)據(jù)須用單引號括起來。利用兩條SQL語句來驗證默認的作用與效果。INSERT publishers (pub_id) VALUES (0001)SELECT * FROM publisherspublishers默認值（2）2. 用單獨的命令來創(chuàng)建Default當(dāng)多個表中的列，它們的缺省值相同時，這種方式很有用。 步驟：1）CREATE DEFAULT 缺省名 AS 缺省值 讓某個默認名的值為默認值。 2）sp_bindefault 默認名,表名.列名 將該默認名綁定到表中某個列上。例子：設(shè)定一個“全局”缺省值，可多次賦值（綁定）CREATE D

22、EFAULT dft_state AS CAsp_bindefault dft_state, publishers.state要求列名的類型與缺省值相同。綁定了缺省值后,并不會對綁定缺省值之前表中已存在的值產(chǎn)生影響,而只對綁定之后的值產(chǎn)生影響。取消缺省取消某列的缺省時，按下列步驟：sp_unbindefault 表名.列名-取消綁定DROP DEFAULT 缺省名 -刪除缺省注意：應(yīng)首先將默認值從綁定的列上都摘除之后，再刪除缺省，否則刪除不會成功。域約束規(guī)則（1）概念：針對表中的某一列，指明某列的取值范圍。在更新該列值時,RDBMS首先要檢查是否在該規(guī)則規(guī)定的范圍內(nèi)。 設(shè)定步驟 ：1）CREA

23、TE RULE 規(guī)則名 AS 規(guī)則2）sp_bindrule 規(guī)則名，表名.列名 例如： CREATE RULE state_rule AS state IN (CA,CO,WA) sp_bindrule state_rule, publishers.state規(guī)則可用IN()，BETWEEN ， AND ，關(guān)系式、=、= 和 LIKE 操作符描述 。創(chuàng)建規(guī)則時，應(yīng)注意AS后有一個以開頭的臨時變量。域約束規(guī)則（2）解除規(guī)則綁定：sp_unbindrule 表名.列名 刪除規(guī)則：DROP RULE 規(guī)則名例子：CREATE TABLE publishers (pub_id char(4)NOT

24、NULL, pub_name varchar(40) NULL, cityvarchar(20) DEFAULT Pasadena, statechar(2)CREATE RULE state_rule AS state IN(CA,CO,WA) sp_bindrule state_rule, publishers.state規(guī)則作用的驗證 INSERT publishers VALUES (0002,MIT Press,San Francisco,IL) 由于插入的state列的值“IL”不在該列所綁定規(guī)則規(guī)定的范圍之內(nèi)， 因此，SQL Server會返回錯誤信息，拒絕本信息的插入。如果將I

25、L改為CA，則該語句可成功執(zhí)行。擴展用法先創(chuàng)建好用戶定義類型，然后將創(chuàng)建好的缺省和規(guī)則綁定到用戶定義類型上 綁定： sp_bindrule 規(guī)則名，用戶定義類型 sp_bindefault 缺省名，用戶定義類型摘除： sp_unbindefault 用戶定義類型 sp_unbinderule 用戶定義類型查看創(chuàng)建規(guī)則和缺省的過程：sp_helptext 規(guī)則名或缺省名規(guī)則檢查屬性的取值范圍。默認值提供屬性在沒有設(shè)定值時的缺省。默認值從屬于規(guī)則，當(dāng)默認與規(guī)則都有時，必須同時滿足。一般，綁定一個新規(guī)則或默認時，應(yīng)先摘除舊規(guī)則或舊默認。如果沒有摘除，則自動用新規(guī)則或新默認替換舊的。SQL Serve

26、r 檢查約束（1）概念：類似于規(guī)則，要求用戶插入到列或表中的數(shù)據(jù)滿足限制條件。分為列級檢查(針對表中一列）和表級檢查（針對同一表中多列）。列級檢查限制用IN、BETWEEN、AND或LIKE表達，例如：CREATE TABLE publishers (pub_id char(4) NOT NULL, CONSTRAINT pub_id_constraint CHECK (pub_id IN(234,3344,564) OR pub_id LIKE 430-90-9 ), city varchar(20) NULL, state char(2)NULL)檢查約束名檢查約束命令關(guān)鍵字檢查約束（2）

27、表級檢查限制，例如：CREATE TABLE discounts( discounttype varchar(40) NOT NULL, store_id char(4) NULL, lowqtysmallint NULL, highqtysmallint NULL, discount float NOT NULL, CONSTRAINT low_high_check CHECK (lowqty highqty)列級檢查限制可用表級方式寫，表級限制也能用列級方式寫，但要求在第一個列定義后。缺省值須滿足檢查限制要求；檢查約束與規(guī)則的比較（1）檢查約束和規(guī)則都可以對一個表中的列的值進行約束。例如：

28、創(chuàng)建一個雇員關(guān)系。要求部門編號的值必須在10-100之內(nèi)，雇員的職務(wù)只能為下列之一：“Sales”、“Mgr”、“Clerk”檢查約束：CREATE TABLE emp( id smallint NOT NULL, name varchar(9), deptNo smallint， CONSTRAINT dept_constraint CHECK (deptNo BETWEEN 10 AND 100）， job char(5)， CONSTRAINT job_constraint CHECK ( job IN（Sales,Mgr,Clerk）檢查約束與規(guī)則比較（2）規(guī)則：CREATE TABL

29、E emp( id smallint NOT NULL, name varchar(9), dept smallint， job char(5)）CREATE RULE dept_rule AS dept BETWEEN 10 AND 100Sp_bindrule dept_rule, emp.deptCREATE RULE job_rule AS job IN（Sales,Mgr,Clerk) Sp_bindrule job_rule, emp.job完成同一個功能可以使用“檢查約束和”規(guī)則“兩種方法。注意兩者不同的語法形式。主鍵約束（1）主鍵不能出現(xiàn)空值，且所有的值都是唯一的。在定義了主鍵

30、限制后，系統(tǒng)自動為該表生成一個聚簇（Clustered）索引。 定義方式分為列級檢查和表級檢查。列級針對表中一列。列級主鍵約束：CREATE TABLE publishers ( pub_idchar(4) PRIMARY KEY,pub_namechar(30), cityvarchar(20) NULL,statechar(2) NULL)主鍵約束（2）表級主鍵約束：CREATE TABLE sales (stor_idchar(4) NOT NULL, datedatetimeNOT NULL, ord_numvarchar(20)NOT NULL, CONSTRAINT pk_sale

31、s_constr PRIMARY KEY (NONCLUSTERED) (stor_id,ord_num) pk_sales_constr為該主鍵約束的名稱，NONCLUSTERED表示非聚集索引。因為系統(tǒng)會自動生成聚集索引，但用戶可以利用命令來更改。該例子中主鍵是復(fù)合鍵，是stor_id和 ord_num的組合。唯一約束（1）主要是針對候選鍵的限制。在定義了惟一限制后，系統(tǒng)自動為該表生成一個非聚簇索引。用戶在定義時也可改成聚簇索引 。與主鍵約束的區(qū)別：所有值唯一，最多只能有一個空值。默認索引為非聚簇（NonClustered）索引。 分為列級檢查和表級檢查。列級唯一限制：CREATE TAB

32、LE publishers ( pub_idchar(4) UNIQUE,pub_namechar(30)UNIQUE是定義唯一約束的命令關(guān)鍵字。唯一約束（）表級唯一限制：CREATE TABLE sales ( stor_id char(4) NOT NULL, ord_num varchar(20) NOT NULL, date datetimeNOT NULL, CONSTRAINT uq_sales_constr UNIQUE CLUSTERED (stor_id,ord_num)其中，uq_sales_constr是該唯一約束的名稱， (stor_id,ord_num)構(gòu)成一個候選鍵

33、。上述命令的最后一句除了申明唯一約束，還定義了聚簇索引。 外鍵約束（）維護表間完整性實際上是從兩個方向上完成的。外鍵約束定義的條件：在含外鍵的表上定義；即是在從表上定義。定義外鍵限制的列必須是另一個表中的主鍵或候選鍵外鍵限制分類：列級和表級。列級針對表中一列，表級則針對同一表中多列。外鍵約束（）列級外鍵約束定義：（例3-14） CREATE TABLE titles ( title_id tid NOT NULL, title varchar(4) NULL, pub_id char(4)NULL CONSTRAINT pub_id_const REFERENCES publishers (p

34、ub_id) ON DELETE CASCADE ON UPDATE NO ACTION, notes varchar(23) NULL)定義了三個子約束：1。約束“從表主表”方向的參照完整性；2。約束“主表(刪除主鍵）從表”的數(shù)據(jù)完整性。3。約束“主表(修改主鍵）從表”的數(shù)據(jù)完整性。外鍵約束的名稱從表參照主表 主表如果有如下操作時，從表級聯(lián)操作或主表不做操作 外鍵外鍵約束（）表級外鍵限制：參看p107例。外鍵約束定義中的行動，表示當(dāng)對主表的主鍵值做某種操作（刪除或修改時），可采取的處理策略有二種：CASCADE（針對從表的策略）No Action（針對主表的策略）SQL-92定義有四種策略，

35、分別是：Cascade, No Action, Set Null 和 Set Default。但SQL SERVER 和SYBASE只支持以上兩種。如果不定義行動，則缺省的處理為NO ACTION。觸發(fā)器（1）利用主表和從表的觸發(fā)器，也可以保持主/從表間的數(shù)據(jù)完整一致性。 publishers表的主鍵是pub_id，它也出現(xiàn)在titles表中，即titles表中的pub_id是外鍵，也就是說，publishers為主表，而titles則為從表。要維護兩表間的完整性，可對titles用外鍵限制或參照限制來維護它們間的完整性，或?qū)ublishers和titles用觸發(fā)器來維護它們間的完整性。觸發(fā)

36、器的創(chuàng)建： CREATE TRIGGER 觸發(fā)器名 ON 表名 FOR INSERT|UPDATE|DELETE，INSERT|UPDATE|DELETE AS SQL語句組 RETURN要建立觸發(fā)器的表FOR Update，Insert最多只能列三個用戶編寫的一段SQL語句，要求該觸發(fā)器被觸發(fā)后應(yīng)做的事情觸發(fā)器（2）觸發(fā)器不帶參數(shù)，不被用戶和程序調(diào)用，只能由用戶對DB中的表進行操作時觸發(fā)（即：由操作激發(fā)）。一個表中最多有三個觸發(fā)器，即：插入、修改和刪除，分別對應(yīng)各自的操作，例如：插入操作觸發(fā)“插入”觸發(fā)器。 主表和從表應(yīng)分別建立各自的觸發(fā)器，主表的觸發(fā)器維護主表到從表方向的數(shù)據(jù)完整性，而從表

37、的觸發(fā)器維護從表到主表方向的參照完整性。這里，主表和從表不需要定義外鍵，但必須有共同的列。沿用“外鍵約束”中的“主表和從表”，“主鍵和外鍵”的說法。觸發(fā)器（3）系統(tǒng)為觸發(fā)器提供兩張表，即：inserted和deleted。當(dāng)操作為插入時，則新數(shù)據(jù)也會寫入inserted表中；當(dāng)操作為刪除時，刪除數(shù)據(jù)會保存在deleted表中；而當(dāng)操作為修改時，會同時用到這兩張表，先刪后寫。 由刪除操作激發(fā)的主表觸發(fā)器示例： CREATE TRIGGER pub_del ON publishers FOR DELETE ASIF rowcount=0RETURNDELETE titlesFROM titles

38、t, deleted dWHERE t.pub_id=d.pub_idRETURN 系統(tǒng)變量，表示 publishers表中有幾行記錄被刪除了。級聯(lián)刪除titles表中對應(yīng)的行,類似于外鍵約束中的CASCADE行動.觸發(fā)器（4）查看觸發(fā)器的創(chuàng)建過程: sp_helptext 觸發(fā)器名查看表依賴的觸發(fā)器或觸發(fā)器涉及的表:sp_depends 表名或觸發(fā)器名 刪除觸發(fā)器:DROP TRIGGER 觸發(fā)器名 注意不能對視圖和臨時表創(chuàng)建觸發(fā)器;如果建立觸發(fā)器的表被刪除,那么其上的觸發(fā)器將被自動刪除。視圖（1）視圖是一個從其他表中導(dǎo)出的表，其他表可以是DB中的基表也可以是預(yù)先定義的視圖。在數(shù)據(jù)庫中并不存

39、儲視圖（on-the-fly），因此又稱它為“虛表”。用視圖定義新的查詢或視圖，也可以通過視圖來更新基表。但在更新視圖時，應(yīng)記住視圖與基表的區(qū)別及對視圖更新的限制?？梢酝ㄟ^視圖維護基表的安全性，例如：設(shè)定讓用戶只操作視圖，而不能操作基表，來提高表中其他數(shù)據(jù)的安全性。視圖（2）視圖創(chuàng)建命令：CREATE VIEW 視圖名 屬性列表 AS SELECT-FROM-WHERE 語句例如：產(chǎn)生一張California州的作家表。CREATE VIEW ca_authors AS SELECT au_id, au_lname, au_fname, phone, state, contract FROM

40、authors WHERE state=CA查看視圖：sp_help 視圖名查看視圖的創(chuàng)建過程： sp_helptext 視圖名刪除視圖：DROP VIEW 視圖名視圖更新（1）視圖更新（update，insert，delete）是很復(fù)雜的操作，容易造成問題，因為視圖上的修改都必須反映到基表中。如果視圖屬性中包含基表的主鍵（或其他候選鍵）僅在單個基本表上定義沒有使用任何聚集函數(shù)的視圖 該視圖是可更新的。因為，每個視圖記錄都可以在基表中有對應(yīng)的記錄。從多個表組合導(dǎo)出的視圖是不能更新的。利用聚集函數(shù)定義的視圖是不可更新的。視圖更新（2）視圖的數(shù)據(jù)插入：對視圖插入一行也會在基表中插入一行。對基表中沒

41、有出現(xiàn)在視圖中的列，以NULL代替。由于主鍵不能用NULL值，如果視圖中沒有主鍵，那么對視圖的插入將被拒絕。如果插入的數(shù)據(jù)在基表中，而沒有在視圖中，SQL-92是允許的。例如，p115（89）,例3-25。如果要避免這樣的插入，可使用 With Check Option。書上的例3-26。ER模型映射到關(guān)系模型實體映射為表：直接映射。實體名即關(guān)系名。實體型中的每個屬性對應(yīng)表中的屬性，實體型中的每個主鍵和候選鍵即是表的主鍵和候選鍵。ER中復(fù)合屬性的簡單成員屬性直接成為表的屬性。如：姓名（現(xiàn)用名，曾用名)現(xiàn)用名，曾用名如表一，表二。主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱

42、課程課程名稱課程號n講授11教師證號姓名性別19880321T.Neuer女20040201A.Schwack男表一：教師表表二：課程課程號課程名稱3201243數(shù)據(jù)結(jié)構(gòu)3241220數(shù)據(jù)挖掘技術(shù)1：1聯(lián)系映射為單獨的表教師證號大學(xué)名稱任期19880321 SWJTU419770201XY-Uni. 4主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11教師證號大學(xué)名稱任期19880321 SWJTU419770201XY-Uni. 4主持大學(xué)表 主持大學(xué)表 教師（教師證號，姓名，性別）大學(xué)（大學(xué)名稱，大學(xué)地址）1：N聯(lián)系映射為單獨的表教師證號大學(xué)名稱

43、工作起始日期19880321 SWJTU419770201XY-Univ. 4主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11教師（教師證號，姓名，性別）大學(xué)（大學(xué)名稱，大學(xué)地址）工作M：N聯(lián)系映射為單獨的表教師證號課程號19880321 SWJTU19770201XY-Univ. 主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11教師（教師證號，姓名，性別）課程（課程號，課程名稱）講授既是復(fù)合主鍵，又分別是外鍵聯(lián)系的轉(zhuǎn)換 減少表數(shù)量方式（1）實體之間的聯(lián)系有1：1，1：n, m:n 三種，采取的策略是不

44、一樣的。（1）1：1的轉(zhuǎn)換兩個實體分別轉(zhuǎn)換成兩個關(guān)系模式可在任意一個關(guān)系的屬性中加入另一個關(guān)系的鍵和聯(lián)系類型的屬性。如表三。主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11大學(xué)名稱大學(xué)校長工作證號大學(xué)地址任期西南交通大學(xué)19760201四川、成都2005-2007表三：大學(xué)關(guān)系表聯(lián)系的轉(zhuǎn)換 減少表數(shù)量方式（2）（2）1：n的聯(lián)系類型轉(zhuǎn)換兩個實體轉(zhuǎn)換成兩個關(guān)系模式將聯(lián)系合并到n端實體中。即在n端實體屬性中加入1端的關(guān)系的鍵和聯(lián)系類型的屬性。如表四。主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11教師證號姓

45、名性別大學(xué)名稱工作起始日期19800201A.Schwack男TU Darm1980.3.22表四：教師工作表聯(lián)系的轉(zhuǎn)換 減少表數(shù)量方式（3）（3）M：N聯(lián)系類型轉(zhuǎn)換兩個實體轉(zhuǎn)換成兩個關(guān)系。m:n聯(lián)系不能由一個實體的主鍵唯一識別，必須由所關(guān)聯(lián)實體的主鍵共同識別。將聯(lián)系轉(zhuǎn)換為一個獨立的關(guān)系。聯(lián)系的屬性、兩個實體的主鍵都成為這個獨立關(guān)系的屬性。如表五。教師證號課程號200402013201243200402013241220198803213241220表五：講授關(guān)系主持任期教師性別教師證號姓名工作起始日期1mn大學(xué)大學(xué)地址大學(xué)名稱課程課程名稱課程號n講授11ER 聯(lián)系類型約束關(guān)系模型 表1：1鍵

46、約束參與實體關(guān)系表聯(lián)系聯(lián)系獨立的關(guān)系表一端實體的主鍵和聯(lián)系屬性移到另一端實體的關(guān)系表中1：n鍵約束參與實體關(guān)系表聯(lián)系聯(lián)系獨立的關(guān)系表“1”端實體的主鍵和聯(lián)系屬性移動到“n”端實體的關(guān)系表中m:n無鍵約束參與實體關(guān)系表聯(lián)系獨立的關(guān)系表，由參與實體的主鍵、聯(lián)系的屬性共同構(gòu)成。ER模型的轉(zhuǎn)換實例 旅游線路開 設(shè) 旅游班次有 旅游團組成 游客 載運 食宿 交通 工具賓館 參加 安全 保險陪同 導(dǎo)游 1nmnmnmn1n111n八個實體類型七個聯(lián)系類型實體和聯(lián)系的屬性旅游線路 （路線號，起點，終點）旅游班次 （班次號，出發(fā)日期，回程日期，報價）旅游團 （團號，團名，人數(shù)）游客（游客編號，姓名，年齡，性別

47、）導(dǎo)游（導(dǎo)游編號，姓名，年齡，性別，職稱）交通工具（交通工具編號）賓館（賓館編號，賓館名，星級，標準房價）保險單 （保險單編號，保險費，投保日期）開設(shè) （旅游班次季節(jié)時段）食宿（團體訂單折扣）ER模型的轉(zhuǎn)換實例分析旅游線路開設(shè)旅游班次有旅游團組成游客載運食宿交通工具賓館參加安全保險陪同導(dǎo)游1nmnmnmn1n111n13451179旅游線路 （路線號，起點，終點）旅游班次 （班次號，出發(fā)日期，回程日期，報價）開設(shè) （旅游班次季節(jié)時段）游客（游客編號，姓名，年齡，性別）旅游團 （團號，團名，人數(shù)）導(dǎo)游（導(dǎo)游編號，姓名，年齡，性別，職稱）交通工具（交通工具編號）賓館（賓館編號，賓館名，星級，標準房

48、價）保險單 （保險單編號，保險費，投保日期）286食宿（團體訂單折扣） 10轉(zhuǎn)換后的關(guān)系模式旅游線路 （路線號，起點，終點）旅游班次 （班次號，出發(fā)日期，回程日期，報價，路線號,旅游班次季節(jié)時段）交通工具（交通工具編號）旅游團 （團號，團名，人數(shù), 旅游班次號）游客（游客編號，姓名，年齡，性別,團號）導(dǎo)游（導(dǎo)游編號，姓名，年齡，性別，職稱）賓館（賓館編號，賓館名，星級，標準房價）安全保險 （保險單編號，保險費，投保日期,團號）食宿（旅游班次號，賓館編號，團體訂單折扣）載運（旅游班次號，交通工具編號）陪同 （團號，導(dǎo)游編號） 請為一家IT培訓(xùn)的公司建立一個概念數(shù)據(jù)模型以滿足該公司的數(shù)據(jù)需求。公司

49、有: 教師（教師編號、姓名、性別、年齡、職稱)； 學(xué)員（學(xué)員編號、姓名、年齡、單位）； 培訓(xùn)課程（課程編號、名稱、學(xué)時）； 教師應(yīng)分屬各教研組（組號、組名）； 學(xué)員分屬各培訓(xùn)班（班號、班名、人數(shù)）； 實體間的聯(lián)系及它們的屬性有：教師屬于教研組，一個組可有多名教師，但一個教師只屬一個教研組；學(xué)員屬于培訓(xùn)班，一個班可有多名學(xué)員，但一個學(xué)員只屬一個培訓(xùn)班；學(xué)員學(xué)習(xí)課程，為多對多聯(lián)系，含成績屬性；教師為培訓(xùn)班講授課程，為m:n:k聯(lián)系，含講授年度屬性。請畫出ER圖，要求標出實體的主鍵、聯(lián)系的約束類型和鍵約束 將此ER圖轉(zhuǎn)換為關(guān)系模型，要求標出各關(guān)系的主鍵，如果存在的話還應(yīng)指明外鍵。 教研組（組號，組名

50、）。培訓(xùn)班（班號，班名，人數(shù)）。教師（教師編號，姓名，性別，年齡，職稱，組號），組號為外鍵。學(xué)員（學(xué)員編號，姓名，年齡，單位，班號），班號為外鍵。課程（課程編號，名稱，學(xué)時）。講授（教師編號，班號，課程編號，年度），教師編號、班號和課程編號均各為外鍵。學(xué)習(xí)（課程編號，學(xué)員編號，成績），課程編號和學(xué)員編號均各為外鍵。關(guān)系數(shù)據(jù)庫模型關(guān)系（表），字段（列），元組（行），域，主鍵，外鍵關(guān)系模式的性質(zhì)完整性約束域約束、主鍵約束、唯一約束、外鍵約束SQL Server中的數(shù)據(jù)完整性約束視圖ER模型向關(guān)系數(shù)據(jù)庫模型的轉(zhuǎn)換關(guān)系代數(shù)五個基本操作（選擇、投影、集合并、幾何差，集合積）自然聯(lián)結(jié)關(guān)系代數(shù)式的構(gòu)成SPJ

51、操作，關(guān)系代數(shù)的優(yōu)化本章已學(xué)內(nèi)容與后續(xù)內(nèi)容一覽關(guān)系代數(shù)及關(guān)系運算關(guān)系代數(shù)：Relational Algebra關(guān)系運算：Relational Calculus關(guān)系代數(shù)和關(guān)系運算是數(shù)據(jù)庫的理論基礎(chǔ)書中稱它們?yōu)椴樵冋Z言，只關(guān)注查詢。區(qū)別：關(guān)系代數(shù)中的查詢著重“過程”，故由“操作符”集合組成，是“過程化”的查詢；而關(guān)系運算中的查詢側(cè)重“結(jié)果”，故主要描述結(jié)果應(yīng)滿足的“條件”，是“非過程化”的查詢。查詢的輸入輸出均是“關(guān)系”，即輸入為關(guān)系，結(jié)果輸出仍為關(guān)系。因此，可以組合查詢。關(guān)系代數(shù)基本的代數(shù)表達式：由基本的代數(shù)操作符（Operator）對操作數(shù)（Operand）進行一元或二元的操作。基本的操作符：

52、SELECTION（選擇) PROJECTION(投影) UNION(并) INTERSECTION (交) DIFFERENCE(差) CROSS-PRODUCT(積，笛卡爾積)操作數(shù)則是關(guān)系實例。關(guān)系查詢時，既可用列（屬性）名，也可用列（屬性）位置來表示列（屬性）。操作的過程類似于集合運算。返回的結(jié)果是一個關(guān)系實例。這個結(jié)果還可以作為關(guān)系代數(shù)的輸入。如果結(jié)果中有重復(fù)的元組，理論上去掉重復(fù)元組。基于基本操作符，可遞歸定義關(guān)系代數(shù)表達式。選擇和投影學(xué)生表 S118張大衛(wèi)6421陳勝35320李峰11219何大明8gradeagesnamesid課程表 Ccreditcnamecid2數(shù)據(jù)結(jié)構(gòu)1

53、033數(shù)據(jù)庫101選課表 Escorecidsid801036911018grade=3(S)（11，李峰，20，3）, (35，陳勝，21，4)C（R）為一元關(guān)系操作符。從關(guān)系實例中選擇出滿足條件的行。條件式中使用, ,=,。相應(yīng)的SQL查詢描述：SELECT * FROM S WHERE grade=3sname,grade(S)（101，數(shù)據(jù)庫）(R)為一元關(guān)系操作符。從關(guān)系實例中提取出所需的一列或多列。相應(yīng)的SQL查詢描述：SELECT sname，grade FROM Scid，cname(credit=3(C) )（何大明，2）,（李峰，3）,（陳勝，4) ,（張大衛(wèi)，1） 相應(yīng)的

54、SQL查詢描述：SELECT cid，cnameFROM CWHERE credit=3集合并和交學(xué)生表 S1118張大衛(wèi)6421陳勝35320李峰11219何大明8gradeagesnamesidS1S2（8，何大明，19，2）,（11，李峰，20，3）, (35，陳勝，21，4), （6，張大衛(wèi)，18，1),（10，孫小惠，20，3）,（81，李峰，17，1）, （22，董一溪，21，4）學(xué)生表 S2118張大衛(wèi)6421董一溪22117李峰81320孫小惠10gradeagesnamesidS1S2 RS為二元關(guān)系操作符。 結(jié)果包含R和S關(guān)系實例中的所有元組。 前提條件是R、S關(guān)系模式兼容

55、。即R，S中列的個數(shù)相同，對應(yīng)列的類型一致，對應(yīng)列名不要求一致。 SQL語句： Select * FROM S1 UNIONSelect * FROM S2 RS為二元關(guān)系操作符。 結(jié)果包含R和S關(guān)系實例中的相同的元組。 前提條件是R、S關(guān)系模式兼容。 SQL語句： Select * FROM S1 INTERSECTSelect * FROM S2（6，張大衛(wèi)，18，1）集合差學(xué)生表 S1118張大衛(wèi)6421陳勝35320李峰11219何大明8gradeagesnamesidS1-S2（8，何大明，19，2）,（11，李峰，20，3）, (35，陳勝，21，4)學(xué)生表 S2118張大衛(wèi)642

56、1董一溪22117李峰81320孫小惠10gradeagesnamesid R-S，從R中把與S相同的元組減去。 結(jié)果模式與R一致。 前提條件是R、S關(guān)系模式兼容。即R，S中列的個數(shù)相同，對應(yīng)列的類型一致，對應(yīng)列名不要求一致。 SQL語句： Select * FROM S1 EXCEPTSelect * FROM S2由于RS =R-(R-S), 所以一些數(shù)據(jù)庫書上省略介紹RS。集合積（笛卡爾乘積）學(xué)生表 S1118張大衛(wèi)6421陳勝35320李峰11219何大明8gradeagesnamesidS1E RS為二元關(guān)系操作符。 結(jié)果包含R和S中所有的列。如R中有M個屬性p個元組,S中N個屬性q

57、個元組,則RS的結(jié)果關(guān)系有M+N個屬性(列)，p q個元組。 如果有相同的列名，則在結(jié)果字段中不命名，只用位置表示。 相應(yīng)的SQL語句： Select S1.sid,sname,age,grade,E.sid,cid,scoreFROM S1,E注：S1和E表中都有sid，則需要注上表名，即采用“表名.列名”方式。選課表 Escorecidsid801036911018(sid)snameagegrade(sid)cidscore8何大明1928101918何大明19261038011李峰20381019111李峰20361038035陳勝21481019135陳勝2146103806張大衛(wèi)1

58、818101916張大衛(wèi)181610380SELECT-FROM-WHERE與關(guān)系代數(shù)SELECT S1.sid, sname, E.cid, scoreFROM S1,E WHERE S1.sid=E.sid學(xué)生表 S1118張大衛(wèi)6421陳勝35320李峰11219何大明8gradeagesnamesidS1E選課表 Escorecidsid801036911018(sid)snameagegrade(sid)cidscore8何大明1928101918何大明19261038011李峰20381019111李峰20361038035陳勝21481019135陳勝2146103806張大衛(wèi)1

59、818101916張大衛(wèi)1816103801.讀取FROM子句中表的數(shù)據(jù)，做笛卡爾乘積。2.選擇滿足WHERE子句中條件的紀錄。3.按SELECT子句中指定的屬性輸出結(jié)果表806張大衛(wèi)6918何大明8scorecidsnamesidsid,sname,cid,score ( S1.sid=E.sid (S1E)改名（D（1sid，5 sid2），S1E ）(sid)snameagegrade(sid)cidscore8何大明1928101918何大明19261038011李峰20381019111李峰20361038035陳勝21481019135陳勝2146103806張大衛(wèi)18181019

60、16張大衛(wèi)181610380sidsnameagegradesid2cidscore8何大明1928101918何大明19261038011李峰20381019111李峰20361038035陳勝21481019135陳勝2146103806張大衛(wèi)1818101916張大衛(wèi)181610380 為解決RS中產(chǎn)生的名字沖突，引入改名操作：（R（F），S） 對任意代數(shù)表達式S，改名操作返回一個新的關(guān)系實例R，其元組與S相同，模式與S相同。僅那些在F這個改名列表中列出的字段名改變了，語義不變。F的形式為： 舊名新名，或位置新名可以利用改名操作，將中間結(jié)果放在一個新關(guān)系中，此時，S中沒有屬性名沖突，則F