數(shù)據(jù)庫(kù)設(shè)計(jì)的基本步驟和方法課件

DatabaseDesignLiuXiufengInformationandtechnologyschoolDatabaseDesignLiuXiufengObjectivesforStudents1．GraspthebasicprocessesandmethodofDBdesign

(掌握數(shù)據(jù)庫(kù)設(shè)計(jì)的基本步驟和方法)2.Graspthemethodofrequirementsanalysis

(掌握需求分析的方法)3．Graspthebasicprocessesandmethodofconceptualdesign(掌握概念結(jié)構(gòu)設(shè)計(jì)的基本步驟和方法)

ObjectivesforStudents1．Grasp2LectureOutlineDBDesignOverview1BasicProcessesofDBDesign2RequirementsAnalysis3ConceptualStructureDesign

4ConceptualDesign

4Conclusion5LectureOutlineDBDesignOverv37.1DBDesignOverviewThedefinitionofDBdesign數(shù)據(jù)庫(kù)設(shè)計(jì)

對(duì)于一個(gè)給定的應(yīng)用環(huán)境，構(gòu)造（設(shè)計(jì)）優(yōu)化的數(shù)據(jù)庫(kù)邏輯模式和物理結(jié)構(gòu)，并據(jù)此建立數(shù)據(jù)庫(kù)及其應(yīng)用系統(tǒng)，使之能夠有效地存儲(chǔ)和管理數(shù)據(jù)，滿足各種用戶的應(yīng)用需求，包括信息管理要求和數(shù)據(jù)操作要求。7.1DBDesignOverviewThedefi4TwopointsofDBDesign1informationmanagementrequirements(信息管理要求)：

storeandmanageallkindsofdataobjectsindatabase2datamanipulationrequirements(數(shù)據(jù)操作要求)：

dosomeoperationsondata,eg.search,add,delete,alterTwopointsofDBDesign1inform5TheFeaturesofDBDesign1.Basicrulesofconstructingdatabse:

(數(shù)據(jù)庫(kù)建設(shè)的基本規(guī)律)三分技術(shù)，七分管理，十二分基礎(chǔ)數(shù)據(jù)Management(管理)1.ManageDBASProjects2.ManagebusinessprocessofenterpriseBasicData(基礎(chǔ)數(shù)據(jù))

1.collect、store2.Update2.Combinedatadesignandoperationdesign:(數(shù)據(jù)設(shè)計(jì)和處理設(shè)計(jì)相結(jié)合)TheFeaturesofDBDesign1.Bas6數(shù)據(jù)庫(kù)設(shè)計(jì)應(yīng)用系統(tǒng)設(shè)計(jì)概念模型設(shè)計(jì)建立數(shù)據(jù)庫(kù)數(shù)據(jù)分析邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)物理數(shù)據(jù)庫(kù)設(shè)計(jì)子模式設(shè)計(jì)功能說(shuō)明功能模型系統(tǒng)調(diào)試應(yīng)用開(kāi)發(fā)應(yīng)用設(shè)計(jì)事務(wù)設(shè)計(jì)功能分析現(xiàn)實(shí)世界結(jié)構(gòu)和行為分離的設(shè)計(jì)數(shù)應(yīng)概念模型設(shè)計(jì)建立數(shù)據(jù)庫(kù)數(shù)據(jù)分析邏輯數(shù)據(jù)庫(kù)設(shè)計(jì)物理數(shù)據(jù)庫(kù)7TheMethodofDBDesign1.Needsfortheknowledgebelow:

ComputerbasicknowledgePrincipleandMethodofSoftwareEngineerMethodandTechniqueofprogrammingdesignDatabasebasicknowledgeDatabaseDesigntechnologyKnowledgeinthespecificfield計(jì)算機(jī)的基礎(chǔ)知識(shí)軟件工程的原理和方法程序設(shè)計(jì)的方法和技巧數(shù)據(jù)庫(kù)的基本知識(shí)數(shù)據(jù)庫(kù)設(shè)計(jì)技術(shù)應(yīng)用領(lǐng)域的知識(shí)TheMethodofDBDesign1.Needs8TheMethodofDBDesign2.Differentkindsofmethodsincluding:

NewOrleans(新奧爾良法)MethodbasedonE-RModel(基于E-R模型的數(shù)據(jù)庫(kù)設(shè)計(jì)方法)3NFMethod(第三范式設(shè)計(jì)方法）ObjectDefinitionLanguageMethod(面象對(duì)象的設(shè)計(jì)方法)TheMethodofDBDesign2.Diffe9BasicProcessesofDBDesignBasicProcessesofDBDesign10PreparationforDBDesign

數(shù)據(jù)庫(kù)設(shè)計(jì)的準(zhǔn)備工作DecidePeopleParticipatinginDBDesign(選定參加設(shè)計(jì)的人)1.系統(tǒng)分析人員、數(shù)據(jù)庫(kù)設(shè)計(jì)人員(Analyser

andDesigner)自始至終參與數(shù)據(jù)庫(kù)設(shè)計(jì)2.用戶和數(shù)據(jù)庫(kù)管理員(usersandDBA)主要參加需求分析和數(shù)據(jù)庫(kù)的運(yùn)行維護(hù)3.應(yīng)用開(kāi)發(fā)人員（Applicationprogrammers）在系統(tǒng)實(shí)施階段參與進(jìn)來(lái)，負(fù)責(zé)編制程序和準(zhǔn)備軟硬件環(huán)境PreparationforDBDesign

數(shù)據(jù)庫(kù)設(shè)11DBASLifeCycleDBASLifeCycle12DatabaseInitialstudy(requirementanalysis)Analysethecompanysituation,defineproblemsandconstrains,defineobjectives,definescopeandboundariesDatabaseDesignCreatetheconceptualdesignCreatethelogicaldesignCreatethephysicaldesignImplementationandloadingInstallDBMS,createthedatabase(s),loadinitialdataDBASLifeCycleDatabaseInitialstudy(requir13DBASLifeCycle(cont.)TestingandevaluationTestthedatabaseFine-tune(adjust)thedatabaseEvaluatethedatabaseanditsapplicationprogramsOperationProducetherequiredinformationflowMaintenanceandevolutionIntroducechangesMakeenhancementsDBASLifeCycle(cont.)Testing147.1.3數(shù)據(jù)庫(kù)設(shè)計(jì)的基本步驟數(shù)據(jù)庫(kù)設(shè)計(jì)分6個(gè)階段需求分析概念結(jié)構(gòu)設(shè)計(jì)邏輯結(jié)構(gòu)設(shè)計(jì)物理結(jié)構(gòu)設(shè)計(jì)數(shù)據(jù)庫(kù)實(shí)施數(shù)據(jù)庫(kù)運(yùn)行和維護(hù)

需求分析和概念設(shè)計(jì)獨(dú)立于任何數(shù)據(jù)庫(kù)管理系統(tǒng)邏輯設(shè)計(jì)和物理設(shè)計(jì)與選用的DBMS密切相關(guān)7.1.3數(shù)據(jù)庫(kù)設(shè)計(jì)的基本步驟數(shù)據(jù)庫(kù)設(shè)計(jì)分6個(gè)階段15BasicprocessesofDBDesign

PhysicalDesign

LogicDesign

Mini-WorldView

RequirementsCollectionandAnalysis

ConceptualDesign

BasicprocessesofDBDesignP16BasicprocessesofDBDesignConceptualDesignHighleveldescription(oftendoneERmodel)EntityRelationshipmodellingandnormalisationDBMSsoftwareselectionLogicaldesignTranslateERintoDBMSdatamodelEg.translatemodelintodefinitionsfortables,views...SchemaRefinementconsistency,normalizationPhysicaldesignStoragestructures-optimizeperformanceDistributeddatabasedesignBasicprocessesofDBDesignCo17數(shù)據(jù)設(shè)計(jì)和處理設(shè)計(jì)同時(shí)進(jìn)行圖表數(shù)據(jù)設(shè)計(jì)和處理設(shè)計(jì)同時(shí)進(jìn)行圖表18

TwoKeypointsinDBDesign

2.CarryOutTheTwoJobsSimultaneouslyatAllStages,ConsultEachOther,SupplementEachOtherCombineDataDesignandManipulationDesignCloselyTwoKeypointsinDBDesign219SchemasinDBDesign數(shù)據(jù)庫(kù)設(shè)計(jì)不同階段形成的數(shù)據(jù)庫(kù)各級(jí)模式SchemasinDBDesign數(shù)據(jù)庫(kù)的各級(jí)模式SchemasinDBDesign數(shù)據(jù)庫(kù)設(shè)計(jì)不同階段形207.2RequirementsofAnalysis

(需求分析)TasksofRA(需求分析的任務(wù)）1.Definingrequirements

(詳細(xì)調(diào)查現(xiàn)實(shí)世界要處理的對(duì)象,如組織、部門、企業(yè),充分了解原系統(tǒng))2.Analyzingrequirements(明確用戶的各種需求)InputsOutputsProcessesDataStorage3.Decidethefunctionandboundariesofnewsystem(確定新系統(tǒng)的功能)

同時(shí)充分考慮今后可能的擴(kuò)充和改變7.2RequirementsofAnalysis

21InstanceofAInputsIndexpageSearchstringClickingonlinksOutputsSearchresultsWebpageProcessesSearchdatabaseforsearchstringRetrievewebpageDataStorageInstanceofAInputs22KeypointsofRA

需求分析的重點(diǎn)TheCriticPointsofInvestigation:Data,Operation

調(diào)查的重點(diǎn)是“數(shù)據(jù)”和“處理”GetUsers’NeedsonDB（獲得用戶對(duì)數(shù)據(jù)庫(kù)要求）InformationManagementRequirements(信息要求)OperationManagementRequirements(處理要求)SecurityandIntegrityRequirements(安全性與完整性要求)KeypointsofRA

需求分析的重點(diǎn)TheCr23DifficultiesofRAMakefinalusers’needsclear(確定用戶最終需求)Reasons:Userslackforcomputerknowledge(用戶缺少計(jì)算機(jī)知識(shí))Designerlackforusers’professionalknowledge(設(shè)計(jì)人員缺少用戶的專業(yè)知識(shí))Solutions(解決方法):Designershouldkeeptheconstantanddeepcommunicationwithusers(設(shè)計(jì)人員必須不斷深入地與用戶進(jìn)行交流)DifficultiesofRAMakefinalu24OftenUsedMethods(1)跟班作業(yè)(2)開(kāi)調(diào)查會(huì)(3)請(qǐng)專人介紹(4)詢問(wèn)(5)設(shè)計(jì)調(diào)查表請(qǐng)用戶填寫(6)查閱記錄OftenUsedMethods(1)跟班作業(yè)25StructuredAnalysis

(結(jié)構(gòu)化分析方法)從最上層的系統(tǒng)組織機(jī)構(gòu)入手自頂向下、逐層分解分析系統(tǒng)StructuredAnalysis

(結(jié)構(gòu)化分析方法)26StructuredAnalysis

(結(jié)構(gòu)化分析方法)1．AnySystemcanbeAbstractedtobeadiagrambelow:數(shù)據(jù)流數(shù)據(jù)流數(shù)據(jù)存儲(chǔ)信息要求數(shù)據(jù)來(lái)源處理數(shù)據(jù)輸出處理要求StructuredAnalysis

(結(jié)構(gòu)化分析方法)127StructuredAnalysis

(結(jié)構(gòu)化分析方法)2．DecomposeFunctionsandData(分解處理功能和數(shù)據(jù))

(1)DecomposeFunctions將處理功能的具體內(nèi)容分解為若干子功能

(2)DecomposeData處理功能逐步分解同時(shí)，逐級(jí)分解所用數(shù)據(jù)，形成若干層次的數(shù)據(jù)流圖

(3)expressionmethods(表達(dá)方法)處理邏輯：用判定表或判定樹(shù)來(lái)描述數(shù)據(jù)：用數(shù)據(jù)字典來(lái)描述3．GetConfirmationofusers將分析結(jié)果再次提交給用戶，征得用戶的認(rèn)可StructuredAnalysis

(結(jié)構(gòu)化分析方法)228ProcedureofRA

(需求分析過(guò)程)ProcedureofRA

(需求分析過(guò)程)297.2.3DataDictionary

(數(shù)據(jù)字典)1.PurposeofDataDictionary進(jìn)行詳細(xì)的數(shù)據(jù)收集和數(shù)據(jù)分析所獲得的主要結(jié)果2.ContentofDictionaryDataItem(數(shù)據(jù)項(xiàng))DataStructure(數(shù)據(jù)結(jié)構(gòu))DataFlow(數(shù)據(jù)流)DataStorage(數(shù)據(jù)存儲(chǔ))ProcedureofOperation)處理過(guò)程)7.2.3DataDictionary

(數(shù)據(jù)字典)130⒈DataItem(數(shù)據(jù)項(xiàng))

Atomicitem(數(shù)據(jù)項(xiàng)是不可再分的數(shù)據(jù)單位)

DescriptionofDataitem

數(shù)據(jù)項(xiàng)描述＝｛數(shù)據(jù)項(xiàng)名，數(shù)據(jù)項(xiàng)含義說(shuō)明，別名， 數(shù)據(jù)類型，長(zhǎng)度，取值范圍，取值含義， 與其他數(shù)據(jù)項(xiàng)的邏輯關(guān)系，數(shù)據(jù)項(xiàng)之間的 聯(lián)系｝⒈DataItem(數(shù)據(jù)項(xiàng))Atomicitem(數(shù)31⒉Datastructure

(數(shù)據(jù)結(jié)構(gòu))Specifythedataitems’srelationship

(數(shù)據(jù)結(jié)構(gòu)反映了數(shù)據(jù)之間的組合關(guān)系。)Consistofseveraldataitemsordatastructure,ortwothingstogether（一個(gè)數(shù)據(jù)結(jié)構(gòu)可以由若干個(gè)數(shù)據(jù)項(xiàng)組成，也可以由若干個(gè)數(shù)據(jù)結(jié)構(gòu)組成，或由若干個(gè)數(shù)據(jù)項(xiàng)和數(shù)據(jù)結(jié)構(gòu)混合組成。）

DescriptionofDataStructure 數(shù)據(jù)結(jié)構(gòu)描述＝｛數(shù)據(jù)結(jié)構(gòu)名，含義說(shuō)明，組成:｛數(shù)據(jù)項(xiàng)或數(shù)據(jù)結(jié)構(gòu)｝｝⒉Datastructure

(數(shù)據(jù)結(jié)構(gòu))Specify32⒊DataFlow(數(shù)據(jù)流)

movingrouteofdatastructureinsystem

(數(shù)據(jù)流是數(shù)據(jù)結(jié)構(gòu)在系統(tǒng)內(nèi)傳輸?shù)穆窂健?/p>

Description:

數(shù)據(jù)流描述＝｛數(shù)據(jù)流名，說(shuō)明，數(shù)據(jù)流來(lái)源，數(shù)據(jù)流去向，組成:｛數(shù)據(jù)結(jié)構(gòu)｝，平均流量，高峰期流量｝⒊DataFlow(數(shù)據(jù)流)movingroute33⒋DataStorage(數(shù)據(jù)存儲(chǔ))Placefordatatostayortobestored,alsoinputandoutputdataflow

(數(shù)據(jù)存儲(chǔ)是數(shù)據(jù)結(jié)構(gòu)停留或保存的地方，也是數(shù)據(jù)流的來(lái)源和去向之一。)Description:數(shù)據(jù)存儲(chǔ)描述＝｛數(shù)據(jù)存儲(chǔ)名，說(shuō)明，編號(hào)， 輸入的數(shù)據(jù)流，輸出的數(shù)據(jù)流， 組成:｛數(shù)據(jù)結(jié)構(gòu)｝，數(shù)據(jù)量，存取頻度， 存取方式｝⒋DataStorage(數(shù)據(jù)存儲(chǔ))Placefor34⒌Procedureofoperation

(處理過(guò)程)DecisionDiagramorDecisionTreeusedtodealwithlogics(具體處理邏輯一般用判定表或判定樹(shù)來(lái)描述)Description:處理過(guò)程描述＝｛處理過(guò)程名，說(shuō)明，輸入:｛數(shù)據(jù)流｝，輸出:｛數(shù)據(jù)流｝，處理:｛簡(jiǎn)要說(shuō)明｝｝⒌Procedureofoperation

(處理過(guò)程35RequirementsAnalysis

(需求分析)分析用戶活動(dòng)產(chǎn)生，產(chǎn)生業(yè)務(wù)流程圖確定系統(tǒng)范圍，產(chǎn)生系統(tǒng)范圍圖分析用戶活動(dòng)涉及的數(shù)據(jù)，產(chǎn)生數(shù)據(jù)流圖分析系統(tǒng)數(shù)據(jù)，產(chǎn)生數(shù)據(jù)字典需求分析文檔RequirementsAnalysis

(需求分析)分析36SummaryforrequirementsAnalysis設(shè)計(jì)人員應(yīng)充分考慮到可能的擴(kuò)充和改變，使設(shè)計(jì)易于更改，系統(tǒng)易于擴(kuò)充必須強(qiáng)調(diào)用戶的參與SummaryforrequirementsAnaly377.3ConceptualDesignUsingtheERModel(用ER圖進(jìn)行概念結(jié)構(gòu)設(shè)計(jì))Designchoices:Shouldaconceptbemodeledasanentityoranattribute?Shouldaconceptbemodeledasanentityorarelationship?Identifyingrelationships:Binaryorternary?NoteconstraintsoftheERModel:Alotofdatasemanticscan(andshould)becaptured.ButsomeconstraintscannotbecapturedinERdiagrams.We’llrefinethingsinourlogical(relational)design7.3ConceptualDesignUsingth38AbstractMethods1.分類(Classification,ismemberof)EntitySet(Student,Teacher,Course)2.聚集(Aggregation,ispartof)Attribute(Student:Sno,Sname,Ssex,Sage)3.概括(Generalization,issubsetof)SuperclassSubclassStudentundergraduategraduateAbstractMethods1.分類(Classifi39ExampleofERModelEntitySetRelationshipSetAttribute老師學(xué)生教mn學(xué)號(hào)姓名專業(yè)班級(jí)職稱性別姓名職號(hào)ExampleofERModelEntitySetR40CategorizationofAttibutes

(屬性的分類)基本屬性和復(fù)合屬性單值屬性和多值屬性多值屬性的處理將原來(lái)的多值屬性用幾個(gè)新的單值屬性來(lái)表示將原來(lái)的多值屬性用一個(gè)新的實(shí)體類型表示

導(dǎo)出屬性圖5.4地址屬性的層次結(jié)構(gòu)郵政編碼?。ㄊ校┟刂穮^(qū)名街道家庭地址門牌號(hào)碼多值屬性的表示零件編碼零件零件名供應(yīng)商規(guī)格進(jìn)貨價(jià)格銷售價(jià)格價(jià)格圖5.8導(dǎo)出屬性的表示工號(hào)職工姓名基本工資獎(jiǎng)金房租實(shí)發(fā)工資CategorizationofAttibutes

(屬41OperationonERModel

(ER模型上的操作)教師號(hào)姓名出生日期職務(wù)工資獎(jiǎng)金教師（a）教師號(hào)姓名出生日期教師不變信息職務(wù)工資獎(jiǎng)金教師號(hào)教師變動(dòng)信息（b）圖

實(shí)體類型的垂直分裂OperationonERModel

(ER模型上的操42DesignoflocalERModel

設(shè)計(jì)局部ER模式

需求分析結(jié)果確定局部結(jié)構(gòu)范圍實(shí)體定義聯(lián)系定義屬性分配還有局部結(jié)構(gòu)待分析有無(wú)進(jìn)入全局ER模式設(shè)計(jì)圖

局部ER模式設(shè)計(jì)確定屬性的原則：屬性應(yīng)該是不可再分解的語(yǔ)義單位；實(shí)體與屬性之間的關(guān)系只能是1:N的。屬性分配的原則：當(dāng)多個(gè)實(shí)體類型用到同一屬性時(shí)，一般把屬性分配給那些使用頻率最高的實(shí)體類型，或分配給實(shí)體值少的實(shí)體類型。有些屬性不宜歸屬于任一實(shí)體類型，只說(shuō)明實(shí)體之間聯(lián)系的特性DesignoflocalERModel

設(shè)計(jì)局部E43AnExampleofIntergrationofE-RModelLocalERModelofstudentsLocalERModelofCoursesIntergrationofERModelAnExampleofIntergrationof44WholeERModel(全局ER模式)無(wú)全局ER模式設(shè)計(jì)局部ER模式確定公共實(shí)體類型合并兩個(gè)局部ER模式檢查并消除沖突還有未合并的局部模式有還有沖突嗎有1.Attributeconflict2.Structureconflict3.Nameconflict:WholeERModel(全局ER模式)無(wú)全局ER模式45OptimizationofWholeERModel

全局ER模式的優(yōu)化Uniteentitysets(實(shí)體類型的合并)Eliminateredundantattributes(冗余屬性的消除)Eliminateredundantrelationship(冗余聯(lián)系的消除)OptimizationofWholeERModel46A）UniteEntities

(合并實(shí)體)一般1:1聯(lián)系的兩個(gè)實(shí)體可以合并為一個(gè)實(shí)體如果兩個(gè)實(shí)體在應(yīng)用中經(jīng)常需要同時(shí)處理，也可考慮合并例如病人和病歷，如果實(shí)際中通常是查看病人時(shí)必然要查看病歷，可考慮將病歷合并到病人實(shí)體中減少了聯(lián)接查詢開(kāi)銷，提供效率FlashA）UniteEntities

(合并實(shí)體)一般1:1聯(lián)系47B）eliminateredundantAttributes（消除冗余屬性）分ER圖中一般不存在冗余屬性，但集成后可能產(chǎn)生冗余屬性例如，教育統(tǒng)計(jì)數(shù)據(jù)庫(kù)中，一個(gè)分ER圖中含有高校畢業(yè)生數(shù)、在校學(xué)生數(shù)，另一個(gè)分ER圖中含有招生數(shù)、各年級(jí)在校學(xué)生數(shù)每個(gè)分ER圖中沒(méi)有冗余屬性，但集成后“在校學(xué)生數(shù)”冗余，應(yīng)消除FlashB）eliminateredundantAttribut48LogicDBDesignConvertingERtoRelationalFairlyanalogousstructureButmanysimpleconceptsinERaresubtletospecifyinrelationsLogicDBDesignConvertingER49ERtoRelationEntitysetstotables.

CREATETABLEEmployees(ssnCHAR(11),nameCHAR(20),lotINTEGER,

PRIMARYKEY(ssn))ssnnamelot123-22-3666Attishoo48231-31-5368Smiley22131-24-3650Smethurst35EmployeesssnnamelotERtoRelationEntitysetstot50RelationshipSetstoTablesIntranslatingamany-to-manyrelationshipsettoarelation,attributesoftherelationmustinclude:1)Keysforeachparticipatingentityset(asforeignkeys).Thissetofattributesformsasuperkeyfortherelation.2)Alldescriptiveattributes.CREATETABLEWorks_In(ssnCHAR(1),didINTEGER,sinceDATE,

PRIMARYKEY(ssn,did),FOREIGNKEY(ssn)REFERENCESEmployees,FOREIGNKEY(did)REFERENCESDepartments)ssndidsince123-22-3666511/1/91123-22-3666563/3/93231-31-5368512/2/92RelationshipSetstoTablesIn51Review:KeyConstraintsEachdepthasatmostonemanager,accordingtothekeyconstraint

onManages.Translationtorelationalmodel?Many-to-Many1-to-11-toManyMany-to-1dnamebudgetdidsincelotnamessnManagesEmployeesDepartmentsReview:KeyConstraintsEachde52TranslatingERwithKeyConstraintsSinceeachdepartmenthasauniquemanager,wecouldinsteadcombineManagesandDepartments.CREATETABLEManages(

ssnCHAR(11),didINTEGER,sinceDATE,

PRIMARYKEY(did),

FOREIGNKEY(ssn)REFERENCESEmployees,

CREATETABLEDept_Mgr(

didINTEGER,

dnameCHAR(20),budgetREAL,

ssnCHAR(11),

sinceDATE,

PRIMARYKEY(did),FOREIGNKEY(ssn)REFERENCESEmployees)Vs.dnamebudgetdidsincelotnamessnManagesEmployeesDepartmentsTranslatingERwithKeyConstr53Review:ParticipationConstraintsDoeseverydepartmenthaveamanager?Ifso,thisisaparticipationconstraint:theparticipationofDepartmentsinManagesissaidtobetotal(vs.partial).EverydidvalueinDepartmentstablemustappearinarowoftheManagestable(withanon-nullssnvalue!)lotnamednamebudgetdidsincenamednamebudgetdidsinceManagessinceDepartmentsEmployeesssnWorks_InReview:ParticipationConstrai54P