第三章數(shù)據(jù)庫靜態(tài)結(jié)構(gòu)設(shè)計(jì)和實(shí)現(xiàn)-數(shù)據(jù)庫設(shè)計(jì)匯報(bào)人：AiPPTDESIGN時(shí)間：202X.X目錄概念模型一般概念PowerDesigner完整示例PowerDesigner的概念數(shù)據(jù)模型CDM（ConceptualDataModel）PowerDesigner的物理數(shù)據(jù)模型PDM（PhysicalDataModel）SQLServerDDL語句0201數(shù)據(jù)庫設(shè)計(jì)是數(shù)據(jù)庫應(yīng)用程序開發(fā)的基礎(chǔ)，直接影響開發(fā)和運(yùn)行效率。良好的設(shè)計(jì)能減少冗余，避免異常，提升系統(tǒng)性能。數(shù)據(jù)庫設(shè)計(jì)的重要性01將用戶需求抽象為概念模型。02將概念模型轉(zhuǎn)化為邏輯結(jié)構(gòu)設(shè)計(jì)。03使用工具（如PowerDesigner）輔助設(shè)計(jì)。設(shè)計(jì)流程實(shí)體是客觀存在的事物，如學(xué)生、課程。實(shí)體具有屬性，如學(xué)生的學(xué)號、姓名。實(shí)體通過碼唯一標(biāo)識，如學(xué)號。實(shí)體關(guān)系是實(shí)體之間的對應(yīng)關(guān)系，分為1:1、1:n、m:n。關(guān)系實(shí)體與關(guān)系01用矩形表示實(shí)體，橢圓表示屬性，菱形表示關(guān)系。02關(guān)系類型標(biāo)注在連接線上，如1:1、1:n、m:n。03關(guān)系可以有屬性，如選課關(guān)系中的成績。實(shí)體關(guān)系圖（E-R圖）建立CDM新建概念模型，定義實(shí)體和屬性。設(shè)置實(shí)體的主碼和屬性的數(shù)據(jù)類型。建立實(shí)體之間的關(guān)系，設(shè)置關(guān)系類型和基數(shù)。概念數(shù)據(jù)模型（CDM）CDM是E-R圖的改進(jìn)，用于生成物理數(shù)據(jù)模型。CDM獨(dú)立于具體數(shù)據(jù)庫，關(guān)注需求分析。CDM包含實(shí)體、屬性、關(guān)系等元素。使用PowerDesigner使用PowerDesigner生成物理模型的警告

學(xué)生和課程之間的多對多關(guān)系生成物理模型時(shí)會(huì)有一個(gè)index

inclusion的警告原因是外碼和主碼生成pdm時(shí)會(huì)自動(dòng)建立index，生成的學(xué)生－課程關(guān)系中，學(xué)號和課程同是為主碼和外碼，所以重復(fù)建立了以下索引：學(xué)號，課程號，學(xué)號＋課程號。生成的DDL語句（部分）PDM包含了選擇的DBMS的DDL語句：createtableStudents(stdidchar(6)notnull,classidchar(6)notnull,nametitlenull,constraintPK_STUDENTSprimarykey(stdid))altertableStudentsaddconstraintFK_STUDENTS_STDBELONG_CLASSESforeignkey(classid)referencesclasses(classid)3.3 PowerDesigner的概念數(shù)據(jù)模型（ConceptualDataModel）

PD對概念數(shù)據(jù)模型定義主要的內(nèi)容：設(shè)計(jì)數(shù)據(jù)庫過程通常開始于概念級，在此級不需要考慮實(shí)際物理實(shí)現(xiàn)的細(xì)節(jié)。一個(gè)概念數(shù)據(jù)模型CDM代表了一個(gè)數(shù)據(jù)庫的整體邏輯結(jié)構(gòu)，它獨(dú)立于任何軟件或數(shù)據(jù)存儲(chǔ)結(jié)構(gòu)。CDM主要作用：（PDhelp）以圖形方式表示數(shù)據(jù)的組織結(jié)構(gòu)（E-R圖）檢驗(yàn)數(shù)據(jù)設(shè)計(jì)的有效性產(chǎn)生物理數(shù)據(jù)模型PhysicalDataModel(PDM)，其中包含了數(shù)據(jù)庫的物理實(shí)現(xiàn)可以產(chǎn)生一個(gè)使用UML標(biāo)準(zhǔn)表達(dá)的面向?qū)ο竽Ｐ蚈bject-OrientedModel(OOM)3.3.1 基本概念目標(biāo)：使用PowerDesigner建立概念數(shù)據(jù)模型新建：選菜單New，然后選ConceptualDataModel（概念模型），在Browse窗口的根WorkSpace下產(chǎn)生一個(gè)概念模型的結(jié)點(diǎn)，使用彈出菜單更名為“TeachingCDM”。在該結(jié)點(diǎn)下已自動(dòng)加入一個(gè)Diagram。在概念模型中加入實(shí)體等元素：右擊概念模型TeachingCDM，選擇New/Entity或其他菜單。設(shè)置元素的特性：右擊元素，選擇菜單Properties將出現(xiàn)特性設(shè)置的對話框，不同類型的元素由不同的頁框組成。元素的Name用于圖中顯示，所以一般取中文，而Code用于生成物理模型的對象名（如表名、列名等），一般取英文字母。實(shí)體（Entity）實(shí)體特性窗口中主要包含下列頁框：General：設(shè)置實(shí)體（Entity）的編碼（Code）、名稱（Name）和發(fā)生的行數(shù)（Number）Attributes（屬性）：設(shè)置實(shí)體的屬性Identifiers：設(shè)置實(shí)體的標(biāo)識（對應(yīng)物理模型中碼和唯一性約束）實(shí)體屬性（Attributes）和數(shù)據(jù)項(xiàng)（DataItems）實(shí)體屬性：在實(shí)體的特性窗口的Attributes頁框中設(shè)置實(shí)體的屬性屬性設(shè)置內(nèi)容：包括Code、Name、Comment、Datatype、Length、Domain和StandardChecks（Maximum、Minimum和Default）等數(shù)據(jù)項(xiàng)DataItem：在Entity中加入的所有屬性將被自動(dòng)加入在DataItems結(jié)點(diǎn)下作為數(shù)據(jù)項(xiàng)進(jìn)行集中管理。也可以直接在DataItems下增加數(shù)據(jù)項(xiàng)，實(shí)體屬性通過使用相同的Code引用該數(shù)據(jù)項(xiàng)定義。屬性和數(shù)據(jù)項(xiàng)關(guān)系：實(shí)體的屬性可以看作是DataItems下數(shù)據(jù)項(xiàng)的組合；DataItems是所有實(shí)體屬性的集合數(shù)據(jù)項(xiàng)機(jī)制的意義相同數(shù)據(jù)項(xiàng)目定義一次，節(jié)省了工作量保證不同實(shí)體相同屬性的定義一致性保證相同性質(zhì)的列名一致性后兩條通常先定義數(shù)據(jù)項(xiàng)，然后在實(shí)體中引用該數(shù)據(jù)項(xiàng)前兩條通過域也能實(shí)現(xiàn)相同含義不同定義的屬性code名不要同名（如進(jìn)價(jià)和售價(jià)等）屬性的Code值：缺省情況下為DataItems的唯一性標(biāo)識，不同實(shí)體相同Code屬性被認(rèn)為對應(yīng)的是一個(gè)數(shù)據(jù)項(xiàng)定義。是以后生成建表SQL語句的列名。屬性的M、P和D：M=Mandatory（強(qiáng)制）：選中表示屬性非空，P=PrimaryIdentifier：選中為主標(biāo)識。D=Displayed：選中在圖中顯示該屬性，否則不顯示。AttributeProperties－列約束設(shè)置：雙擊某一屬性，進(jìn)入屬性特性設(shè)置框，其中standardChecks頁框中包含了下列常用設(shè)置：最大、最小、缺省值、格式以及所有合法值生成的物理模型中建表SQL語句示例：

…… ageintnulldefault18 constraintCKC_AGE_ENTITY_1check(ageisnullor(agebetween16and20andagein(17,18,19,20,16))),

……行約束：進(jìn)貨價(jià)格必須小于銷售價(jià)格在概念模型中新建一個(gè)BusinessRules，在Expresion/Server中輸入“進(jìn)貨價(jià)格>=銷售價(jià)格”（Client中只在文檔中反應(yīng)，而不會(huì)對物理模型產(chǎn)生影響）在實(shí)體的Properties中的Rules頁框點(diǎn)擊AddObjects，選擇上面建立的rule。在生成的物理模型的建表的SQL語句中出現(xiàn)：

…… constraintCKT_ENTITY_1check(age>length)

……選作實(shí)體主碼數(shù)據(jù)項(xiàng)的排它性若一個(gè)數(shù)據(jù)項(xiàng)作為一個(gè)實(shí)體的主碼，就不能再作為其他實(shí)體的屬性?；诘睦碛墒荘owerdesigner假設(shè)與某一實(shí)體主碼同名的屬性必是外碼，而外碼是在生成物理模型時(shí)根據(jù)實(shí)體間關(guān)系自動(dòng)生成，在概念模型中無需設(shè)置。引出的Code的取名（即表列名）問題：被選作主碼屬性的code不能被其他實(shí)體使用所有實(shí)體主碼屬性的Code取值必須不同名。如實(shí)體一般均有編號，并且通常為主碼，為此我們必須在編號前加上前綴，如StdId。任一實(shí)體主碼屬性的Code值不能再被其他實(shí)體的屬性使用Code

數(shù)據(jù)項(xiàng)定義，同時(shí)Code列名，所以生成的物理模型中相同的列名必須具有相同的定義。在商場管理信息系統(tǒng)中，很多單據(jù)都有數(shù)量屬性，若這些數(shù)量屬性具有相同的長度和精度要求，則Code可相同，反之，則Code不能同名，如必須取名為OrderQty、SaleQty等。突破限制的方法：如在上述Code不能同名情況下，要求生成的物理模型列名一定要同名，可在生成的物理模型時(shí)打開選項(xiàng)：ConvertNamesintoCodes，即把Name作為列名。但一般不建議這樣做。選擇菜單Tools/ModelOptions，對Model設(shè)置中的DataItem中關(guān)閉UniqueCode選項(xiàng)。（注意：進(jìn)入ModelOptions對話框內(nèi)容根據(jù)當(dāng)前Diagram是概念模型還是物理模型而不同）判定實(shí)體屬性合適的準(zhǔn)則：在概念模型中，不要試圖使用屬性來反映兩個(gè)實(shí)體的關(guān)系，這本身就包含了實(shí)現(xiàn)方法，而實(shí)現(xiàn)方法應(yīng)該由物理模型完成。輔導(dǎo)員不應(yīng)該作為班級的屬性，而應(yīng)該通過班級和輔導(dǎo)員的關(guān)系來反映。學(xué)號不應(yīng)該作為校園卡的屬性，而應(yīng)該通過學(xué)生和校園卡的關(guān)系來反映校園卡和學(xué)號的對應(yīng)關(guān)系。課程和學(xué)生的關(guān)系A(chǔ)ssociation即“選課”中不應(yīng)該包括教師屬性，而應(yīng)該通過課程、學(xué)生和教師三者之間的Association來反映學(xué)生所選課和教師關(guān)系。實(shí)體標(biāo)識（Identifier）定義：實(shí)體的屬性或?qū)傩越M合，在非空情況下其值唯一地標(biāo)識一個(gè)實(shí)體（可以為空）PrimaryIdentifier：一個(gè)實(shí)體可有多個(gè)Identifier，但只能指定一個(gè)為PrimaryIdentifier，一定非空。設(shè)置方法：在實(shí)體Propertise窗口的Identifier中設(shè)置，雙擊某個(gè)Identifier設(shè)置其對應(yīng)屬性。生成物理模型：PrimaryIdentifier對應(yīng)屬性即為主碼，其他Identifier對應(yīng)屬性被定義為Unique約束。PrimaryIdentifier設(shè)置：在實(shí)體的Properties窗口的Attributes中指定一個(gè)實(shí)體的某些屬性為PrimaryIdentifier后，該實(shí)體將自動(dòng)產(chǎn)生一個(gè)Identifier_1，其對應(yīng)屬性即為所有指定為PrimaryIdentifier的屬性。去除屬性的PrimaryIdentifier標(biāo)志，并不會(huì)自動(dòng)刪除Identifier_1，但對應(yīng)屬性被自動(dòng)刪除；反之，刪除Identifier_1或去除對應(yīng)屬性或去除PrimaryIdentifier標(biāo)志，則實(shí)體所有屬性自動(dòng)去除PrimaryIdentifier標(biāo)志。

域（Domain）定義域，即屬性的取值類型和范圍。所有實(shí)體屬性的Domain可取已定義的域，其Datatype將被該域取值類型和范圍所取代。例：定義一個(gè)名稱域title，則學(xué)生姓名和課程名稱的Domain取title。域定義保證了具有相同取值類型和范圍的屬性域的一致性，一旦域需要修改，不必再逐個(gè)對實(shí)體屬性進(jìn)行修改，而只要對定義的域作一次修改。 圖（Diagram）以圖形化的方式顯示概念模型，一個(gè)概念模型下至少有一個(gè)Diagram，自動(dòng)顯示概念模型下的實(shí)體等元素。打開Palette窗口：右擊工具欄，在彈出式菜單中打開Palette選項(xiàng)。在Palette窗口中包含了圖形化方式顯示的概念模型中的元素，可選中需要的元素加入Diagram中。 關(guān)系Powerdesigner提供了兩種方法建立實(shí)體之間關(guān)系。Relationships：為ER模型表示法Associations：為Merise表示法，Merise為信息系統(tǒng)設(shè)計(jì)和開發(fā)方法，類似UML。在一個(gè)概念數(shù)據(jù)模型中，可以只使用relationship或只使用Association，也可以兩者同時(shí)使用。關(guān)系（Relationship）建立實(shí)體之間的關(guān)系，在其Properties的Detail中設(shè)置關(guān)系的不同類型。在生成物理模型（Table）時(shí)，將根據(jù)兩個(gè)實(shí)體的不同的關(guān)系，作不同處理：one-one：雙方或單方產(chǎn)生外碼。one-many或many-one：many方產(chǎn)生外碼。many-many：產(chǎn)生新表，屬性由兩者的主碼屬性構(gòu)成。Relationship不能包含屬性。Relationship的Details：主導(dǎo)作用（Dominantrole）：在OnetoOne情況下可選：None：雙方產(chǎn)生外碼實(shí)體Ato實(shí)體B：僅在實(shí)體B產(chǎn)生外碼實(shí)體Ato實(shí)體B：基數(shù)（Cardinality）：n,m表示一個(gè)實(shí)體A可對應(yīng)n-m個(gè)實(shí)體B，可選0,1、1,1、0,n和1,n依賴關(guān)系（Dependent）：含義：A的每個(gè)實(shí)例被B的一個(gè)實(shí)例所標(biāo)識對物理模型影響：B的主碼將成為A的主碼一部分同時(shí)為B的外碼強(qiáng)制關(guān)系（Mandatary）：含義：A的每個(gè)實(shí)例需要一個(gè)B的實(shí)例對物理模型影響：A中的對B的外碼非空01“AtoB”的依賴（Dependent）關(guān)系表示要確定實(shí)體A的一個(gè)實(shí)例，必須首先確定實(shí)體B的一個(gè)實(shí)例，或者說B的標(biāo)識是構(gòu)成A的標(biāo)識的一個(gè)部分。02“AtoB”的依賴關(guān)系反映了一個(gè)實(shí)體A對實(shí)體B的依附關(guān)系，其基本特征是實(shí)體A必須通過實(shí)體B來唯一標(biāo)識。依賴關(guān)系0201強(qiáng)制關(guān)系表示一個(gè)實(shí)體的每個(gè)實(shí)例必須對應(yīng)另一個(gè)實(shí)體的實(shí)例。如學(xué)生必須屬于一個(gè)班級，班級號在學(xué)生表中為非空。強(qiáng)制關(guān)系基數(shù)和依賴、強(qiáng)制之間的約束關(guān)系：

基數(shù)（n,m）和依賴、強(qiáng)制關(guān)系存在下列約束關(guān)系： 1）0，1：不強(qiáng)制、不依賴 2）0，n：不強(qiáng)制、無依賴 3）1，1：強(qiáng)制、依賴可選 4）1，n：強(qiáng)制、無依賴強(qiáng)制（Mandatory）關(guān)系實(shí)例：1-1例：學(xué)生to校園卡，假設(shè)學(xué)生可不辦校園卡，則學(xué)生和校園卡非強(qiáng)制關(guān)系，否則為強(qiáng)制關(guān)系。多-1例：學(xué)生to興趣班，假設(shè)一個(gè)學(xué)生最多參加一個(gè)興趣班，則為非強(qiáng)制關(guān)系；假設(shè)一個(gè)學(xué)生必須參加一個(gè)興趣班，則為強(qiáng)制關(guān)系依賴（Dependent）關(guān)系實(shí)例：學(xué)生和班級的多-1關(guān)系：如整個(gè)學(xué)校學(xué)生的學(xué)號唯一，則學(xué)生對班級為非依賴關(guān)系。產(chǎn)生物理模型結(jié)果是班號僅僅作為學(xué)生的外碼。如學(xué)生的學(xué)號僅在班級中唯一，則學(xué)生對班級為依賴關(guān)系。產(chǎn)生物理模型結(jié)果是班號將作為學(xué)生主碼一部分（和學(xué)號一起為主碼）同時(shí)班號為學(xué)生的外碼。把關(guān)系轉(zhuǎn)換為實(shí)體：1－1、1－多和多－多關(guān)系均可以轉(zhuǎn)換為實(shí)體，特別是對多－多關(guān)系，轉(zhuǎn)換為實(shí)體后可以包含屬性。把Relationship轉(zhuǎn)換為實(shí)體的方法是使用關(guān)系的彈出菜單中的ChangetoEntity。思考和練習(xí)：在TeachingCDM中，students和classes的Relationship加上依賴關(guān)系，然后生成物理模型，觀察區(qū)別。在實(shí)際應(yīng)用中，學(xué)號往往包含了學(xué)生的級別、專業(yè)、班號等信息，這實(shí)際不符合1NF，如何設(shè)計(jì)更規(guī)范？同時(shí)又能按需求規(guī)則輸出包含級別、專業(yè)、班號等信息的學(xué)號？提示：參考上述1-多關(guān)系中的依賴關(guān)系進(jìn)行設(shè)計(jì)。思考和練習(xí)：實(shí)體學(xué)生的班長的關(guān)系，由于班長本身是學(xué)生，所以是一個(gè)實(shí)體自己和自己的關(guān)系，建立這種關(guān)系后將在物理模型中產(chǎn)生什么情況？如此處理是否是唯一方法？是否可以通過學(xué)生和班級的關(guān)系來反映學(xué)生和班長的關(guān)系，比較兩種方法。Association和AssociationLink作用：Association通過AssocationLink與多個(gè)實(shí)體連接，并可包括自己的屬性。一般用于二個(gè)和二個(gè)以上實(shí)體的聯(lián)系。使用方法：可以直接用AssociationLink連接兩個(gè)實(shí)體，將產(chǎn)生一個(gè)Association，兩個(gè)Assocation和實(shí)體的連接AssociationLink。也可以先建立一個(gè)Association，然后用AssociationLink連接Assocation和某個(gè)實(shí)體。生成物理模型：將同Relationship一樣根據(jù)是1-1、1-多還是多-多關(guān)系生成外碼或生成一個(gè)Table。1:n關(guān)系在“多”的一方實(shí)體表中加入“1”的一方實(shí)體的主碼作為外碼。如學(xué)生表中加入班級號作為外碼。1:1關(guān)系在一方實(shí)體表中加入另一方實(shí)體的主碼作為外碼?？梢赃x擇在任一方實(shí)體表中實(shí)現(xiàn)。m:n關(guān)系創(chuàng)建一個(gè)獨(dú)立的關(guān)系表，包含兩個(gè)實(shí)體的主碼。主碼由兩個(gè)實(shí)體的主碼組合而成。如學(xué)生選課表包含學(xué)號和課程號。關(guān)系轉(zhuǎn)換規(guī)則關(guān)系類型的表示：通過Association連接的兩個(gè)實(shí)體的關(guān)系類型，是分別通過兩實(shí)體和Association的AssociationLink類型來確定的。下面是班級和學(xué)生的1－多關(guān)系，其中的1，1和1，n并非表示班級和學(xué)生的多－1關(guān)系（事實(shí)是1－多關(guān)系），而表示的是學(xué)生在學(xué)生和班級關(guān)系中發(fā)生1－1次，而班級在學(xué)生和班級的關(guān)系中將發(fā)生1－n次。1－多Association及生成的物理模型：Relationship和Association的使用建議：對1－1、1－多和不包含屬性的多－多關(guān)系，使用Relationship。實(shí)體之間的多－多關(guān)系且包含屬性的使用Association比較簡單。要處理復(fù)雜的問題，如建立關(guān)系之間的關(guān)系或關(guān)系與實(shí)體之間的關(guān)系，必須使用Relationship。思考和練習(xí)：學(xué)生和課程的關(guān)系即“選課”可使用Association，也可以使用一個(gè)實(shí)體，該實(shí)體具有成績屬性，然后分別和學(xué)生和課程建立強(qiáng)制的依賴多-1關(guān)系，分別用這兩種方法設(shè)計(jì)CDM，然后生成PDM。比較分析兩種方法的優(yōu)缺點(diǎn)。上述實(shí)體及其關(guān)系可以在兩個(gè)實(shí)體建立的Relationship后，右擊Relationship使用彈出菜單中的ChangetoEntity/Standard生成。實(shí)例分析學(xué)生班級中班長的表達(dá)首先班長不能作為單獨(dú)的實(shí)體，他是學(xué)生實(shí)體中的一員。不能用實(shí)體表示，是否能用屬性表示，似乎可以在班級中增加一個(gè)屬性班長學(xué)號解決這個(gè)問題，缺陷是這個(gè)屬性是外碼，單純地作為屬性丟失這個(gè)特征。外碼反映的是實(shí)體之間的關(guān)系，所以班長應(yīng)該用關(guān)系來表達(dá)，表達(dá)方式可以有以下幾種：班級和學(xué)生的1對1關(guān)系，生成的物理模型可能會(huì)有兩種，一種在學(xué)生中產(chǎn)生班級的外碼，一種是班級中產(chǎn)生學(xué)生的外碼，顯然后者更好，只需要在1-1關(guān)系中設(shè)置學(xué)生為主導(dǎo)學(xué)生和學(xué)生的多對1關(guān)系，學(xué)生中將產(chǎn)生班長學(xué)號外碼，顯然一個(gè)班的學(xué)生這個(gè)外碼值都會(huì)相同，此方法不可取。

繼承關(guān)系（Inheritance）：繼承關(guān)系的一端連接具有普遍性的Entity，稱為ParentEntity，繼承關(guān)系的另一端連接具有特殊性的一個(gè)或多個(gè)Entity稱為ChildEntity。例如，“學(xué)生”為“本科生”與“研究生”的Parent，后者為前者的Child。繼承關(guān)系生成物理模型的控制：如關(guān)系屬性中打開GenerateChildren選項(xiàng),可選Parent的所有屬性或僅主屬性復(fù)制到Children對應(yīng)表中。如關(guān)閉GenerateChildren選項(xiàng)，則僅生成Parent表，該表將包含子實(shí)體所有屬性并可設(shè)置SpecifyingAttribute。如生成父子表同時(shí)打開，父表主碼將作為子表主碼和外碼。SpecifyingAttribute:設(shè)置屬性，這些屬性將出現(xiàn)在生成的父表中，通常用作區(qū)分是哪個(gè)Children。0102繼承借鑒了面向?qū)ο蟮乃枷?，父?shí)體的屬性可以被子實(shí)體繼承。如“學(xué)生”作為父實(shí)體，“本科生”和“研究生”為子實(shí)體。繼承的概念繼承的物理模型通過對繼承關(guān)系的特性設(shè)置可以控制由此生成的物理數(shù)據(jù)模型。

在特性窗口的“生成Generation”頁框中，“生成父表（Generateparent）”的設(shè)置和父實(shí)體的特性窗口中的“生成Generate”的設(shè)置作用是一樣的，關(guān)閉這個(gè)選項(xiàng)，父實(shí)體對應(yīng)的表將不在物理數(shù)據(jù)模型中生成。打開這個(gè)選項(xiàng)，則父實(shí)體對應(yīng)的表將在物理數(shù)據(jù)模型中生成，并且子實(shí)體對應(yīng)的表將以父實(shí)體的主碼屬性為主碼同時(shí)為外碼。互斥性繼承（MutuallyExclusiveChildren）

：同一事件(occurrence)不能出現(xiàn)在同一Parent的兩個(gè)child中，這種繼承稱為互斥性繼承，反之，則稱為非互斥性繼承。如父實(shí)體一個(gè)學(xué)生只能是本科生或研究生取其一，則本科生和研究生為學(xué)生的互斥性繼承，否則，如一個(gè)學(xué)生可同時(shí)為本科生和研究生，則為非互斥性繼承。此設(shè)置只影響文檔而不影響生成的PDM。繼承的實(shí)踐應(yīng)用：對若干實(shí)體的公共屬性，可把這些公共屬性抽取出來形成一個(gè)父實(shí)體，具有公共屬性的實(shí)體可從該父實(shí)體繼承這些公共屬性。如所有單據(jù)都有單據(jù)號，日期，制單人等，則這些屬性可形成一個(gè)“單據(jù)公共屬性”實(shí)體，所有單據(jù)實(shí)體繼承該實(shí)體屬性。概念模型下，出現(xiàn)在主鍵(如單據(jù)號)中的數(shù)據(jù)項(xiàng)不能重復(fù)使用，若在物理模型下，所有單據(jù)號要使用相同的列名，可以使用Inheritance。實(shí)用技巧：實(shí)際繼承的前提條件是Child具有較多的共同屬性。使用繼承可避免共同屬性重復(fù)維護(hù)并保持其一致性。僅生成Child：適用Child之間共同屬性相對少而差異大的情況。在Child之間非互斥情況下，會(huì)有冗余。僅生成Parent：適用Child之間差異小的情況，五斥情況下，某個(gè)Child的屬性對其他Child一定為空。生成Parent及部分Child：適用Child之間共性屬性相對多而差異也大情況，此情形Child僅需要繼承Parent的主屬性?；コ夂头腔コ馇闆r均適用，互斥情況下Parent與Child為1-1，在非互斥情況下為1對多。例：使用繼承完成下列概念模型設(shè)計(jì)業(yè)務(wù)單據(jù)：共同屬性：單據(jù)號、日期、備注、制單人、審核人等諸多共同屬性。特殊屬性：進(jìn)貨單：供應(yīng)商、結(jié)算方式出庫單：倉庫、出庫類型思考和練習(xí)：繼承最后生成的物理模型可有下列選擇，分析其利弊：生成Parent，不生成Parent僅繼承主碼屬性繼承所有屬性一張單據(jù)（以銷售單為例）的數(shù)據(jù)包括單據(jù)號，日期和單據(jù)明細(xì)中的品號，品名，數(shù)量和單價(jià)。錯(cuò)誤的設(shè)計(jì)：直接把些屬性放在一個(gè)銷售表中，思考主碼是什么，是否符合規(guī)范化？建立錯(cuò)誤的概念模型：為了避免不規(guī)范，概論書上把單據(jù)直接設(shè)計(jì)為兩個(gè)實(shí)體，單據(jù)頭和單據(jù)明細(xì)，而實(shí)際單據(jù)顯然是一個(gè)實(shí)體。建立正確的概念模型：一個(gè)實(shí)體，屬性為其中的1-1的數(shù)據(jù)，即單據(jù)號，日期，單據(jù)明細(xì)的數(shù)據(jù)是銷售單實(shí)體和商品實(shí)體建立的多對多關(guān)系，在這個(gè)多對多關(guān)系中包含屬性數(shù)量，品號，品名和單價(jià)均在商品表中通過商品號外碼引用。概念數(shù)據(jù)模型實(shí)例分析

例1：商品和單據(jù)實(shí)體的概念模型錯(cuò)誤的設(shè)計(jì)：單據(jù)實(shí)體的屬性一部分和單據(jù)為1-1，另一部分為1-多，所以把它分成兩個(gè)實(shí)體，分別為單據(jù)摘要和單據(jù)明細(xì)。生成的物理數(shù)據(jù)模型：必須在生成的物理數(shù)據(jù)模型中為單據(jù)明細(xì)設(shè)置主碼：正確的處理：單據(jù)明細(xì)實(shí)際上反映的是單據(jù)實(shí)體和商品實(shí)體的多對多關(guān)系：生成的物理數(shù)據(jù)模型：生成的物理數(shù)據(jù)模型不必作任何修改例2 同一實(shí)體的多個(gè)外鍵引用的處理 單據(jù)主表中有制單人、申請人和驗(yàn)收人等，這些屬性同是“員工表”的外鍵。產(chǎn)生問題是：在概念模型中，“員工”實(shí)體和“單據(jù)”實(shí)體將有多個(gè)1-Mrelationship，生成物理模型后，“單據(jù)”表中將產(chǎn)生多個(gè)“員工號”外鍵，其列名由PowerDesigner根據(jù)概念模型對應(yīng)屬性的code自動(dòng)合成，無法人工控制。錯(cuò)誤的處理：概念和物理數(shù)據(jù)模型生成物理數(shù)據(jù)模型中單據(jù)的處理人的列名無法控制，只能作修改。實(shí)際上單據(jù)中處理人的列是重復(fù)的，不符合1NF。正確的處理：增加單據(jù)責(zé)任實(shí)體。生成的物理模型：例3：多供應(yīng)商問題問題：一個(gè)超市中某種商品的供應(yīng)商一般一個(gè)時(shí)期固定為一個(gè)，所以供應(yīng)商和商品的關(guān)系為1-多，但可能會(huì)有個(gè)別商品同時(shí)有多個(gè)供應(yīng)商，即對個(gè)別商品供應(yīng)商和商品的關(guān)系為多-多。通常的解決方案：設(shè)計(jì)成多對多模型，包含3個(gè)關(guān)系：商品（*商品編號，名稱，單位，…）供應(yīng)商（*供應(yīng)商編號，名稱，地址，聯(lián)系電話…）商品和供應(yīng)商對應(yīng)表（*商品編號，*供應(yīng)商編號）設(shè)計(jì)缺陷：因?yàn)闃O個(gè)別的商品，查詢?nèi)魏魏蜕唐芳肮?yīng)商相關(guān)的信息，都必須連接三個(gè)表。連接的表越多，查詢效率自然越差。改進(jìn)方法：設(shè)計(jì)成1-多模型，適用大多數(shù)商品：商品（*商品編號，名稱，單位，供應(yīng)商編號…）供應(yīng)商（*供應(yīng)商編號，名稱，地址，聯(lián)系電話…）對個(gè)別商品，可能有多個(gè)供應(yīng)商，其供應(yīng)商編號可取一個(gè)特殊的編號表示它有多個(gè)供應(yīng)商，而其對應(yīng)的供應(yīng)商使用下列關(guān)系來表示：商品和供應(yīng)商對應(yīng)表（*商品編號，*供應(yīng)商編號）優(yōu)點(diǎn)：對大多數(shù)商品的商品信息和供應(yīng)商信息查詢，只需要連接兩個(gè)表。如何設(shè)計(jì)概念數(shù)據(jù)模型：可在商品實(shí)體和供應(yīng)商實(shí)體之間建立兩個(gè)關(guān)系，一個(gè)為多對一，一個(gè)為多對多。表示對多數(shù)商品實(shí)體和供應(yīng)商實(shí)體為多對一，對個(gè)別商品商品實(shí)體和供應(yīng)商實(shí)體為多對多。思考和練習(xí)：對兩種設(shè)計(jì)，分別寫出并比較查詢供應(yīng)商供貨表的select語句，列包括：供應(yīng)商編號，供應(yīng)商名稱，商品編號，商品名稱，按第一列排序。僅查單供應(yīng)商的商品僅查多（多于一個(gè)）供應(yīng)商的商品查所有商品針對二種設(shè)計(jì)方案，從對商品和供應(yīng)商的增、刪和改三個(gè)方面，分別給出處理邏輯，比較其復(fù)雜性，并設(shè)計(jì)一種商品和供應(yīng)商關(guān)系的維護(hù)界面。一）生成物理數(shù)據(jù)模型打開生成對話框：使用菜單Tools/GeneratePhysicalDataModel生成物理數(shù)據(jù)模型。出現(xiàn)對話框PDMGenerationOptions對生成進(jìn)行控制。第一次生成：選中“GenerateNewPhysicalDataModel”，并選擇DBMS（MicrosoftSQLServer2000），按確認(rèn)則生成PDM。重新生成：修改了CDM后，要重新生成PDM，選中“UpdateExistingPhysicalDataModel”，并關(guān)閉“PreserveModifications”選項(xiàng)，按確認(rèn)則重新生成PDM覆蓋原PDM。3.4PowerDesigner的物理數(shù)據(jù)模型PDM3.4PowerDesigner的物理數(shù)據(jù)模型PDM二）修改物理數(shù)據(jù)模型問題：有時(shí)需要對生成的PDM進(jìn)行修改，修改后若重新生成PDM，對PDM的修改將丟失。解決方法：打開“UpdateExistingPhysicalDataModel”中PreserveModifications選項(xiàng)，PD將根據(jù)CDM生成PDM，并與原來的（生成后可能修改過的）PDM比較，由用戶選擇是那些內(nèi)容要根據(jù)新的PDM進(jìn)行更新、刪除或增加。建議：盡可能建立合理完整的CDM，避免或盡可能少地對生成的PDM作修改。例：由CDM生成PDM后對CDM和PDM做下列修改：

1）PDM的學(xué)生表中增加“出生日期”

2）把CDM學(xué)生性別Sex由BooleanChar(1)選擇UpdateExistingPhysicalDataModel并打開PreserveModifications，按確認(rèn)后出現(xiàn)下列對話框，左邊是新生成的PDM，右邊是原生成并已增加“出生日期”的PDM，其中所有比較后的不一致項(xiàng)用戶可打勾表示用新PDM更新，更新方式是：不一致項(xiàng)上的“-”表示刪除，“+”表示增加，“=”表示更新本例中，我們希望保留PDM的“出生日期”，而更新Sex的定義，所以僅在右邊“性別”前打勾。點(diǎn)OK，產(chǎn)生新PDM將保留“出生日期”，性別類型改為Char(1)。更新時(shí)原PDM和新PDM比較控制界面：三）生成物理數(shù)據(jù)模型的其他控制Detail頁框：CheckMode：在生成PDM前檢查模型是否有錯(cuò)，有錯(cuò)則停止生成。SaveGenerationDependencies：跟蹤每一個(gè)生成對象的標(biāo)識。在以更新方式生成PDM時(shí)，即使同時(shí)修改了對象Name和Code，在新老PDM的比較時(shí)，PD仍能識別是同一對象。ConvertNamestoCodes：在生成時(shí)把對象Name作為Code，即以Name作為PDM的對象名（如表名和列名）Seletion頁框：列出所有實(shí)體，選擇是否生成。3.5數(shù)據(jù)庫的建立方法一：逐個(gè)手工創(chuàng)建數(shù)據(jù)表在PDM各table屬性（properties）中的Preview頁中包含了系統(tǒng)生成的創(chuàng)建該表的DDL語句，在SQLServer的查詢分析器中可選擇性的執(zhí)行這些DDL語句。在生成的SQL語句中，包含了對需要?jiǎng)?chuàng)建的對象是否已經(jīng)存在的判斷以及存在情況下進(jìn)行刪除的操作，使創(chuàng)建工作可重復(fù)進(jìn)行。生成的SQL片段：ifexists(select1fromsysobjectswhereid=object_id('Students')andtype='U')droptableStudentsgocreatetableStudents(snochar(6)notnull,classidchar(6)notnull,snametitlenull,sexbitnull,areanochar(3)null,Telephonenochar(8)null,constraintPK_STUDENTSprimarykey(sno),constraintAK_IDENTIFIER_2_STUDENTSunique(areano,Telephoneno))go方法二：自動(dòng)創(chuàng)建所有數(shù)據(jù)表準(zhǔn)備工作：建立ODBC數(shù)據(jù)源：PD通過ODBC執(zhí)行SQL命令，所以先要建立ODBC數(shù)據(jù)源（代表SQLServer中某個(gè)數(shù)據(jù)庫），可以使用控制面板/管理工具/數(shù)據(jù)源（ODBC）建立，也可在GenerateDatabase…過程中建立。建立數(shù)據(jù)庫：PDM生成的SQL語句并不包含建立數(shù)據(jù)庫的語句，所以先要使用企業(yè)管理器或查詢分析器建立數(shù)據(jù)庫，也可以在PD中用菜單Database/ExecuteSQL執(zhí)行建立數(shù)據(jù)庫的命令。自動(dòng)創(chuàng)建所有數(shù)據(jù)表選擇菜單Database/ChangeCurrentDBMS…，選擇MicrosoftSQLServer2000選擇菜單Database/GenerateDatabase…，在彈出的對話框中選擇目錄和文件名，所有建表的SQL語句將放入該文件中，并選中ODBCGeneration以建立數(shù)據(jù)表確認(rèn)后將彈出ConnecttoanODBCDataSource對話框，選擇數(shù)據(jù)源。確認(rèn)后將生成要執(zhí)行的SQL語句，按Execute執(zhí)行SQL語句010203單據(jù)分為單據(jù)摘要和單據(jù)明細(xì)。單據(jù)明細(xì)反映單據(jù)與商品的多對多關(guān)系。使用關(guān)（Association）建立多對多關(guān)系，添加相關(guān)屬性。單據(jù)設(shè)計(jì)單據(jù)設(shè)計(jì)員工與請假單為1:n關(guān)系，請假單為強(qiáng)制關(guān)系。員工與考勤卡為1:1關(guān)系，員工為主導(dǎo)。生成物理模型時(shí)，考勤卡依賴于員工，請假單關(guān)聯(lián)員工?？记谙到y(tǒng)考勤系統(tǒng)商品與供應(yīng)商關(guān)系通常為多對1，個(gè)別商品為多對多。01概念模型中建立兩種關(guān)系，多對1和多對多。02物理模型中增加多供應(yīng)商表，優(yōu)化查詢效率。03多供應(yīng)商問題多供應(yīng)商問題謝謝大家匯報(bào)人：AiPPT第四章查詢語句（SELECT）和視圖（VIEW）匯報(bào)人：AiPPTDESIGN時(shí)間：202X.XCONTENTSSELECT語句基礎(chǔ)01.目錄查詢語句小結(jié)05.多表查詢02.視圖（VIEW）03.典型查詢實(shí)例分析04.01SELECT語句SELECT語句SELECT語句用于從數(shù)據(jù)庫中檢索數(shù)據(jù)。示例：查詢進(jìn)貨單明細(xì)表的所有數(shù)據(jù)：SELECT*FROMBuyDetail。查詢語句基本格式查詢?nèi)繑?shù)據(jù)查詢整個(gè)表的所有內(nèi)容：SELECT*FROMBuyDetail。查詢指定列：SELECTBuyNo,GoodsNo,Unit,QuantityFROMBuyDetail。去除重復(fù)行：SELECTDISTINCTBuyNoFROMBuyDetail。分組統(tǒng)計(jì)查詢所有進(jìn)貨單的總行數(shù)：SELECTCOUNT(*)FROMBuyDetail。查詢每張進(jìn)貨單的合計(jì)金額：SELECTBuyNo,SUM(Price*Quantity)FROMBuyDetailGROUPBYBuyNo。查詢合計(jì)金額大于40000元的進(jìn)貨單：SELECTBuyNo,SUM(Price*Quantity)FROMBuyDetailGROUPBYBuyNoHAVINGSUM(Price*Quantity)>40000。包含計(jì)算列查詢進(jìn)貨單的進(jìn)貨單號、商品編碼、單位、單價(jià)、數(shù)量和金額（金額=數(shù)量×價(jià)格）：SELECT*,Price*QuantityASAmountFROMBuyDetail。使用別名簡化查詢結(jié)果：SELECT*,Price*QuantityASAmountFROMBuyDetail。排序查詢進(jìn)貨單明細(xì)表，要求進(jìn)貨單號按升序排列，同一進(jìn)貨單的行按商品價(jià)格降序排列：SELECT*FROMBuyDetailORDERBYBuyNo,PriceDESC。查詢合計(jì)金額大于40000元的進(jìn)貨單，按合計(jì)金額升序排列：SELECTBuyNo,SUM(Price*Quantity)FROMBuyDetailGROUPBYBuyNoHAVINGSUM(Price*Quantity)>40000ORDERBYSUM(Price*Quantity)。選擇運(yùn)算查詢單號為“B19002”的進(jìn)貨單中，商品編碼前兩位為“03”且價(jià)格大于200元的商品的編碼、價(jià)格和數(shù)量：SELECTGoodsNo,Price,QuantityFROMBuyDetailWHEREBuyNo='B19002'ANDGoodsNoLIKE'03%'ANDPrice>200。單表查詢02多表查詢交叉連接和內(nèi)連接交叉連接（笛卡兒積）：SELECTStdName,EleNameFROMStudent,Elective。內(nèi)連接：SELECTa.StdName,b.EleNameFROMStudentaJOINStudent_ElectivebONa.StdId=b.StdIdJOINElectivecONb.EleId=c.EleId。外連接左外連接：SELECTa.StdName,b.EleNameFROMStudentaLEFTJOINStudent_ElectivebONa.StdId=b.StdIdLEFTJOINElectivecONb.EleId=c.EleId。全連接（通過UNION實(shí)現(xiàn)）：SELECTa.CusId,a.CusName,b.GoodsName,b.QuantityFROMCustomeraLEFTJOINSalebONa.CusId=b.CusIdUNIONSELECTb.CusId,a.CusName,b.GoodsName,b.QuantityFROMCustomeraRIGHTJOINSalebONa.CusId=b.CusId。自連接查詢學(xué)生編號、學(xué)生姓名、鄰座同學(xué)姓名：SELECTa.StdId,a.StdName,b.StdNameFROMStudentaLEFTJOINStudentbONa.SidId=b.StdId。外連接條件和選擇條件避免在WHERE條件中使用外連接條件中對方表的列，以免失去外連接的意義。數(shù)據(jù)源中數(shù)據(jù)表的連接數(shù)據(jù)源中數(shù)據(jù)表的各種連接交叉連接：即兩個(gè)表的笛卡爾積運(yùn)算內(nèi)連接：為缺省連接，返回所有符合連接條件的行左（右）外連接：結(jié)果集為滿足連接條件的行+所有左（右）表不滿足連接條件的行，這些行對應(yīng)右表的列值均為空全連接：返回行中包括左右兩邊表不滿足連接條件的行，即返回行一定包含左右兩邊表的所有行。自連接：將表中的列與同一表中的其它列連接起來稱為自連接。例：交叉連接和內(nèi)連接交叉連接：獲得所有學(xué)生和課程的組合

SELECT,b.subid,b.gradeFROMstudentsacrossJOINgradeb該語句等價(jià)于：SELECT,b.subid,b.gradeFROMstudentsa,gradeb內(nèi)連接：學(xué)生所選課程的成績表

SELECTa.id,,b.subid,b.gradeFROMstudentsaJOINgradebONa.id=b.id例：外連接左外連接：學(xué)生所選課程成績表，要包括沒選任何課的學(xué)生名單，他們對應(yīng)的課程號和成績顯示Null。

SELECTa.id,,b.subid,b.gradeFROMstudentsaLEFTJOINgradebONa.id=b.id思考和練習(xí)1）沒選任何課的學(xué)生課程號顯示“未選”，成績顯示空，如何修改?2）ONa.id=b.id后加anda.id=‘000002’，結(jié)果發(fā)生什么變化?（與增加WHEREa.id=‘000002’

比較）（顯示‘000002’學(xué)生的選課情況以及其他學(xué)生名單）例：全連接假設(shè)有客戶表和銷售表，有下列數(shù)據(jù)：

VIP客戶表 銷售表編號 姓名 日期客戶編號商品名稱 數(shù)量00001 張三 03-0199999 A 1

00002 李四 03-0100001 B 100

其中客戶“99999”表示普通客戶，如要查詢所有客戶的消費(fèi)記錄，列為“客戶編號，名稱，商品名稱，數(shù)量”則必須使用全連接：

SELECTa.id,,b.goodsnm,b.quantityFROMcustomersaFULLJOINsalesbONa.id=b.id例：自連接Students中增加鄰坐同學(xué)編號id_side，要求查詢學(xué)生編號，學(xué)生姓名，鄰坐同學(xué)編號，鄰坐同學(xué)姓名，其語句為：SELECTa.id,,a.neighbour_id,FROMstudentsa,studentsbWHEREa.id_side=b.id子查詢的使用邏輯運(yùn)算符查詢成績至少比學(xué)號為“19001”學(xué)生選修的某一門課成績要高的學(xué)生學(xué)號和課程號：ALL：所有比較都為TRUE。ANY：至少一個(gè)比較為TRUE。EXISTS：子查詢返回至少一行。IN：操作數(shù)與子查詢結(jié)果中至少一行相同。子查詢及其邏輯運(yùn)算符實(shí)現(xiàn)關(guān)系和集合運(yùn)算

一）關(guān)系運(yùn)算投影：選列-SELECT選擇：選行-WHERE廣義笛卡爾積：兩個(gè)或多個(gè)關(guān)系元組的全部組合-CROSSJOIN連接：對兩個(gè)或多個(gè)關(guān)系進(jìn)行廣義笛卡爾積的結(jié)果上進(jìn)行擴(kuò)展的（增加了外連接）選擇操作，連接條件即為選擇條件-JOIN/LEFTJOIN/RIGHTJOIN或WHERE。二）集合運(yùn)算 運(yùn)算及使用SELECT的實(shí)現(xiàn)方法：并-UNION（所有DBMS）交-子查詢或INTERSECT（Oracle）差-子查詢或MINUS（Oracle）除-子查詢1）并-UNION：例：把學(xué)生編號、姓名和教師編號、姓名合并查詢

SELECTsno,snameFROMStudents UNION SELECTtno,tnameFROMTeachers改進(jìn)上述查詢： 為了區(qū)分學(xué)生還是老師，并要求把學(xué)生和老師分列在一起，常用下列語句： SELECTsno,sname，’學(xué)生’FROMstudents UNION SELECTtno,tname,’老師’FROMteachers Orderby3不必在教師表和學(xué)生表中增加區(qū)分學(xué)生還是老師的列。Orderby3表示按查詢結(jié)果的第三列排序。若要求學(xué)生排在前面，則最后一行改為: orderby3desc2）交-子查詢或INTERSECT（Oracle）例：查詢被學(xué)號09001和09002的學(xué)生同時(shí)都選的課程編號

使用INTERSECT（Oracle）： SELECTcnoFROMelectivecourseWHEREsno=‘09001’ INTERSECT SELECTcnoFROMelectivecourseWHEREsno=‘09002’

使用子查詢： SELECTcnoFROMelectivecourseWHEREsno=‘09001’AND cnoIN(SELECTcnoFROMelectivecourseWHEREsno=’09002’)3）差-子查詢或MINUS（Oracle）例：學(xué)號為95001學(xué)生選擇但沒有被學(xué)號為95002的學(xué)生選擇的課程編號

使用MINUS（Oracle）： SELECTcnoFROMelectivecourseWHEREsno=‘09001’ MINUS SELECTcnoFROMelectivecourseWHEREsno=‘09002’

使用子查詢： SELECTcnoFROMelectivecourseWHEREsno=‘09001’AND cnoNOTIN(SELECTcnoFROMelectivecourseWHEREsno=’09002’)4）除運(yùn)算和含義和實(shí)現(xiàn)含義：關(guān)系R和S的屬性分別分成兩組X和Y、Y和Z，R÷S是R在X上的投影中其對應(yīng)Y(x的像集)包含關(guān)系S在Y上的投影的所有元組。理解：一類對象X（學(xué)生）可以擁有1－N（選修1－N門課程）個(gè)特性Y，關(guān)系R記錄了每一個(gè)對象X（學(xué)生）所擁有的特性Y（選課表ElectiveCourse），關(guān)系S包含了所有1－N個(gè)特性（課程表Course），則R÷S為具有所有1-N個(gè)特性Y的對象X（選了所有課程的學(xué)生）實(shí)現(xiàn)：R(X,Y)÷S(Y,Z)其相反的條件是至少存在一個(gè)特性沒有被選擇，即存在一個(gè)特性，查詢結(jié)果中的對象不具有該特性。所以要解決的基礎(chǔ)查詢是：查詢沒有被選擇的特性:

SELECT*FROMSWHEREYNOTIN(SELECTYFROMRWHEREY=S.Y)R(X,Y)÷S(Y,Z)對應(yīng)的SELECT語句：

SELECTXFROMRWHERENOTexists(SELECT*FROMSWHEREYNOTIN(SELECTYFROMRWHEREY=S.Y))用EXISTS實(shí)現(xiàn)關(guān)系的除運(yùn)算實(shí)例三個(gè)表：Students(*sno,sname)Courses(*cno,cname)Electivecourse(*sno,*cno)查詢選修了全部課程的學(xué)生姓名。假設(shè)courses中一共只有兩門課，課程號分別為PUB001和PUB002，用已學(xué)知識寫出查詢同時(shí)選修了這兩門課的學(xué)生編號。解答1：selectsnofromscwherecno=‘PUB001’andcno=‘PUB002’-where后表達(dá)式永遠(yuǎn)為false解答2：selectsnofromscwherecno=‘PUB001’orcno=‘PUB002’-結(jié)果是只要選了其中一門都滿足條件解答3：selectsnofromscwherecno=‘PUB001’andsnoin(selectsnofromscwherecno=‘PUB002’)-正確但限制是課程數(shù)確定并且不能變化解答4：selectsnofromsc

groupbysnohavingcount(*)=2-同樣的問題，課程數(shù)變化必須修改程序解答5：selectsnofromscgroupbysnohavingcount(*)=(selectcount(*)fromcourse)-正確解答5說明條件為包含關(guān)系的，如果條件可轉(zhuǎn)化為確定的數(shù)量關(guān)系，就不一定要除運(yùn)算來實(shí)現(xiàn)。如下查詢條件中包含關(guān)系不能轉(zhuǎn)化為確定的數(shù)量關(guān)系：查詢選修了某學(xué)號同學(xué)選修的所有課程的學(xué)生學(xué)號。查詢選修的所有PUBXXX課程的學(xué)生，仍可以轉(zhuǎn)成數(shù)量關(guān)系，如何做？(查出每個(gè)學(xué)生選修PUBXXX的課程數(shù)，和PUBXXX的課程數(shù)相等)例：查詢選修了全部課程的學(xué)生號碼和姓名。查詢某學(xué)生students.sno沒有選擇的課程

SELECT*FROMcoursesWHEREcnoNOTIN(SELECTcnoFROMelectivecourseWHEREsno=students.sno)查詢語句為：

SELECTsno,snameFROMstudents WHERENOTEXISTS(SELECT*FROMcoursesWHEREcnoNOTIN(SELECTcnoFROMscWHEREsno=student.sno))例：查詢至少選修了學(xué)生95002選修的全部課程的學(xué)生號碼。。學(xué)生95001選修的所有課程：

SELECTcnoFROMelectivecourseWHEREsno=‘95001’上列課程中沒有被某學(xué)生選修的課程

SELECT*FROMcoursesWHEREcnoIN

(SELECTcnoFROMelectivecourseWHEREsno=‘95001’)ANDcnoNOTIN(SELECTcnoFROMelectivecourseWHEREsno=students.sno)最終的查詢語句：

selectsno,snamefromstudentswhereNOTEXIST (SELECT*FROMcoursesWHEREcnoIN

(SELECTcnoFROMelectivecourseWHEREsno=‘95001’)ANDcnoNOTIN(SELECTcnoFROMelectivecourseWHEREsno=students.sno))查詢和95001學(xué)生選修了一樣的課程的學(xué)生1）被95001學(xué)生選了但沒有被student.sno選的課程select*fromcoursewherecnoin(selectcnofromscwheresno='95001')andcnonotin(selectcnofromscwheresno=student.sno)2）被student.sno選了但沒有被01學(xué)生選的課程select*fromcoursewherecnonotin(selectcnofromscwheresno='95001')andcnoin(selectcnofromscwheresno=student.sno)3）所以最后的查詢語句是：selectsnofromstudentwherenotexists(select*fromcoursewherecnoin(selectcnofromscwheresno='95001')andcnonotin(selectcnofromscwheresno=student.sno))andnotexists(select*fromcoursewherecnonotin(selectcnofromscwheresno='95001')andcnoin(selectcnofromscwheresno=student.sno))andsno<>'95001'03視圖刪除視圖刪除視圖：DROPVIEWAllStudentElectiveGrade。視圖的特點(diǎn)1.視圖可以看作是一個(gè)保存在數(shù)據(jù)庫中的SELECT語句。2.視圖不占用物理空間，查詢視圖時(shí)執(zhí)行對應(yīng)的SELECT語句。3.視圖可以提供數(shù)據(jù)安全控制，實(shí)現(xiàn)邏輯獨(dú)立性，提高查詢效率。建立視圖視圖的定義和使用CREATE[ORREPLACE][ALGORITHM={UNDEFINED|MERGE|TEMPTABLE}][DEFINER=user][SQLSECURITY{DEFINER|INVOKER}]VIEWview_name[(column_list)]ASselect_statement[WITH[CASCADED|LOCAL]CHECKOPTION]04典型查詢實(shí)例分析包含不同選擇條件的聚合列的查詢【例4-20】根據(jù)Student和Student_Elective表數(shù)據(jù)，查詢輸出表4-4所示表格：表4-4包含不同選擇條件的聚合列的查詢包含不同選擇條件的聚合列的查詢(1)使用視圖(2)不使用視圖一行數(shù)據(jù)歸屬多個(gè)分組的查詢【例4-22】以2019年2月份為例，由于2000年2月的數(shù)據(jù)要被同時(shí)分組到2019年2月和3的兩個(gè)分組中（若存在3月以后銷售量據(jù)，也要被分配到當(dāng)年以后的月份中），但銷售表每一行又不能被重復(fù)分配到不同組中，解決這個(gè)矛盾的方法是把這些數(shù)據(jù)按以后的月份數(shù)進(jìn)行重復(fù)擴(kuò)展，并且每個(gè)擴(kuò)展必須加上“年”和“月”的標(biāo)志，即要求使用查詢語句得到表4-14所示數(shù)據(jù)：使用視圖創(chuàng)建視圖：CREATEVIEWMonSaleQty(SaleYear,SaleMon,GoodsNo,qty)ASSELECTYEAR(a.SaleDate),MONTH(a.SaleDate),b.GoodsNo,SUM(b.SaleQty)FROMSaleSummarya,SaleDetailbWHEREa.SaleNo=b.SaleNoGROUPBYYEAR(a.SaleDate),MONTH(a.SaleDate),b.GoodsNo。查詢視圖：SELECTa.SaleYear,a.SaleMon,a.GoodsNo,a.qty,b.qtyFROMMonSaleQtya,YearSaleQtybWHEREa.SaleYear=b.SaleYearANDa.SaleMon=b.SaleMonANDa.GoodsNo=b.GoodsNo。不使用視圖使用表達(dá)式：SELECTa.SaleYear,a.SaleMon,c.GoodsNo,SUM(IsEqualYearMon(a.SaleYear,a.SaleMon,b.SaleDate)*c.SaleQty)ASmonqty,SUM(c.SaleQty)ASyearqtyFROMV_SaleExistYearMona,SaleSummaryb,SaleDetailcWHEREYEAR(b.SaleDate)=a.SaleYearANDMONTH(b.SaleDate)<=a.SaleMonANDb.SaleNo=c.SaleNoGROUPBYa.SaleYear,a.SaleMon,c.GoodsNo。一行數(shù)據(jù)歸屬多個(gè)分組的查詢函數(shù)在SELECT中運(yùn)用一）查詢結(jié)果中某些統(tǒng)計(jì)列對應(yīng)不同的統(tǒng)計(jì)條件的查詢。例1：銷售表的月累計(jì)和年累計(jì)銷售額的統(tǒng)計(jì)。

數(shù)據(jù)表：SaleSummary（*SaleNo，SaleDate）：單據(jù)摘要（銷售單編號，銷售日期）SaleDetail（*SaleNo，*GoodsNo，SaleQty，SalePrice）：單據(jù)明細(xì)（銷售單編號，品號，銷售數(shù)量，銷售價(jià)格）

要求查詢：月份，品號，月累計(jì)銷售數(shù)，年累計(jì)銷售數(shù)。年累計(jì)銷售數(shù)為從年初到某月底的銷售數(shù)。用以下語句可查詢兩個(gè)表中包含數(shù)據(jù)：

SELECTa.SaleDate,b.GoodsNo,b.SaleQty,b.SalePriceFROMSaleSummarya,SaleDetailbWHEREa.SaleNo=b.SaleNo要求輸出月度銷售匯總：1）僅輸出月累計(jì)銷售數(shù)為簡化起見，可把上面對銷售數(shù)據(jù)的查詢語句做成一個(gè)視圖sales。語句一：適用sales僅存放同一年的銷售數(shù)據(jù)，否則其中的SUM(quantity)將是各年某月的累計(jì)銷售數(shù)。

SELECTMONTH(saledate),goodsno,SUM(quantity)FROMsalesGROUPBYMONTH(saledate),goodsno語句二：適用sales存放各年的銷售數(shù)據(jù)

SELECTYEAR(saledate),MONTH(saledate),goodsno,SUM(quantity) FROMsalesGROUPBYYEAR(saledate),MONTH(saledate),goodsno語句三：把年月合并成一列 SELECTSTR(YEAR(saledate),4)+'-'+STR(MONTH(saledate),2),goodsno,SUM(quantity)FROMsales GROUPBYSTR(YEAR(saledate),4)+'-'+STR(MONTH(saledate),2),goodsno年月品號月累計(jì)銷售數(shù)2019-01G0001 37.002019-02G0001 50.002019-03G0001 25.00日期品號數(shù)量單價(jià)2019-01-01G0001 10.0089.002019-01-03G0001 15.0090.002019-01-20G0001 12.0089.002019-02-02G0001 20.0092.002019-02-14G0001 30.0094.002019-03-03G0001 15.0090.002019-03-18G0001 10.0089.00上表是銷售數(shù)據(jù)，右表為查詢結(jié)果分析：分組統(tǒng)計(jì)必須滿足下面兩條：必須能構(gòu)造一個(gè)表達(dá)式，使每一組的元組具有相同值。其蘊(yùn)涵了條件是每一個(gè)元組只能屬于一個(gè)組，即每一組的上述表達(dá)式的值要求不同。本例顯然不滿足上述條件，比如2020年3月的銷售數(shù)據(jù)，同時(shí)要被分組統(tǒng)計(jì)到3月及3月以后各月份的年累計(jì)銷售數(shù)據(jù)中。解決方法：由于2020年3月的數(shù)據(jù)要被同時(shí)分組到2020年3月到12月的各組中，但元組又不能被重復(fù)分配到不同組中，解決的方法是把這些數(shù)據(jù)重復(fù)擴(kuò)展10次，擴(kuò)展的方法是使用非等值連接。2）僅輸出年累計(jì)銷售數(shù)解決步驟：建立一個(gè)表happenmon(hyear,hmon)，存放所有發(fā)生過銷售和年-月，可使用下列語句產(chǎn)生數(shù)據(jù)： INSERTINTOhappenmonSELECTDISTINCT YEAR(saledate),MONTH(saledate)FROMsales擴(kuò)展Sales表：n月的數(shù)據(jù)被重復(fù)擴(kuò)展12-n+1次

SELECTa.hyear,a.hmon,b.saledate,b.goodsno,b.quantity FROMhappenmona,salesbWHEREYEAR(b.saledate)=a.hyearANDMONTH(b.saledate)<=a.hmon年月銷售日期品號數(shù)量201912019-1-1G000110201922019-1-1G000110201932019-1-1G000110201912019-1-3G000115201922019-1-3G000115201932019-1-3G000115201912019-1-20G000112201922019-1-20G000112201932019-1-20G000112201922019-2-2G000120201932019-2-2G000120201922019-2-14G000130201932019-2-14G000130201932019-3-3G000115201932019-3-18G000110日期品號數(shù)量單價(jià)2019-01-01G0001 10.0089.002019-01-03G0001 15.0090.002019-01-20G0001 12.0089.002019-02-02G0001 20.0092.002019-02-14G0001 30.0094.002019-03-03G0001 15.0090.002019-03-18G0001 10.0089.00上面為銷售表原始數(shù)據(jù)，右側(cè)為擴(kuò)展查詢結(jié)果。對該結(jié)果按“年，月和品號”分組匯總數(shù)量就是止本月累計(jì)數(shù)量。最終查詢語句及結(jié)果：SELECTa.hyear,a.hmon,b.goodsno,SUM(b.quantity)FROMhappenmon

a,salesbWHEREYEAR(b.saledate)=a.hyear

ANDMONTH(b.saledate)<=a.hmonGROUPBYa.hyear,a.hmon,b.goodsno年份月份品號年累計(jì)銷售數(shù)20191G00013720192G00018720193G00011123）同時(shí)輸出月累計(jì)銷售數(shù)和年累計(jì)銷售數(shù)

方案1：聯(lián)合兩個(gè)查詢并定義為一個(gè)視圖，然后對視圖分組統(tǒng)計(jì)定義視圖：（紅色為原查詢上增加內(nèi)容）

CREATEVIEWvsale(year,mon,goodsno,monqty,yearqty)AS SELECTYEAR(saledate),MONTH(saledate),goodsno,SUM(quantity),0 FROMsalesgroupbyYEAR(saledate),MONTH(saledate),goodsno UNION SELECTa.hyear,a.hmon,b.goodsno,0,sum(b.quantity) FROMhappenmona,salesbWHEREYEAR(b.saledate)=a.hyearANDMONTH(b.saledate)<=a.hmonGROUPBYa.hyear,a.hmon,b.goodsno輸出結(jié)果：

SELECTyear,mon,goodsno,SUM(monqty),SUM(yearqty) FROMvsaleGROUPBYyear,mon,goodsno年月品號月累計(jì)銷售數(shù)年累計(jì)銷售數(shù)2019-01G0001 37.0002019-02G0001 50.0002019-03G0001 25.0002019-01G0001037.002019-02G0001087.002019-03G00010112.00視圖數(shù)據(jù)：年月品號月累計(jì)銷售數(shù)年累計(jì)銷售數(shù)2019-01G0001 37.0037.002019-02G0001 50.0087.002019-03G0001 25.00112.00查詢結(jié)果即最終結(jié)果：方案2：把兩個(gè)查詢定義為兩個(gè)視圖然后進(jìn)行視圖的連接分別把1）的語句2和2）的查詢做成視圖年銷售數(shù)：vmonsale(year,mon,goodsno,qty)月銷售數(shù)：vyearsale(year,mon,goodsno,qty)按年、月和品號進(jìn)行連接兩個(gè)視圖

SELECTa.year,a.mon,a.goodsno,b.qty,a.qty FROMvyearsaleLEFTJOINvmons