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

DatabaseDesignLiuXiufengInformationandtechnologyschoolDatabaseDesignLiuXiufengObjectivesforStudents1．GraspthebasicprocessesandmethodofDBdesign

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

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

ObjectivesforStudents1．Grasp2LectureOutlineDBDesignOverview1BasicProcessesofDBDesign2RequirementsAnalysis3ConceptualStructureDesign

4ConceptualDesign

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

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

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

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

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

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

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

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

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

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

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

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

PhysicalDesign

LogicDesign

Mini-WorldView

RequirementsCollectionandAnalysis

ConceptualDesign

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

TwoKeypointsinDBDesign

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

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

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

同時充分考慮今后可能的擴充和改變7.2RequirementsofAnalysis

21InstanceofAInputsIndexpageSearchstringClickingonlinksOutputsSearchresultsWebpageProcessesSearchdatabaseforsearchstringRetrievewebpageDataStorageInstanceofAInputs22KeypointsofRA

需求分析的重點TheCriticPointsofInvestigation:Data,Operation

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

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

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

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

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

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

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

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

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

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

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

(需求分析過程)ProcedureofRA

(需求分析過程)297.2.3DataDictionary

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

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

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

DescriptionofDataitem

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

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

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

DescriptionofDataStructure 數(shù)據(jù)結(jié)構(gòu)描述＝｛數(shù)據(jù)結(jié)構(gòu)名，含義說明，組成:｛數(shù)據(jù)項或數(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ù)流名，說明，數(shù)據(jù)流來源，數(shù)據(jù)流去向，組成:｛數(shù)據(jù)結(jié)構(gòu)｝，平均流量，高峰期流量｝⒊DataFlow(數(shù)據(jù)流)movingroute33⒋DataStorage(數(shù)據(jù)存儲)Placefordatatostayortobestored,alsoinputandoutputdataflow

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

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

(處理過程35RequirementsAnalysis

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

(需求分析)分析36SummaryforrequirementsAnalysis設(shè)計人員應(yīng)充分考慮到可能的擴充和改變，使設(shè)計易于更改，系統(tǒng)易于擴充必須強調(diào)用戶的參與SummaryforrequirementsAnaly377.3ConceptualDesignUsingtheERModel(用ER圖進行概念結(jié)構(gòu)設(shè)計)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é)號姓名專業(yè)班級職稱性別姓名職號ExampleofERModelEntitySetR40CategorizationofAttibutes

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

導(dǎo)出屬性圖5.4地址屬性的層次結(jié)構(gòu)郵政編碼省（市）名地址區(qū)名街道家庭地址門牌號碼多值屬性的表示零件編碼零件零件名供應(yīng)商規(guī)格進貨價格銷售價格價格圖5.8導(dǎo)出屬性的表示工號職工姓名基本工資獎金房租實發(fā)工資CategorizationofAttibutes

(屬41OperationonERModel

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

實體類型的垂直分裂OperationonERModel

(ER模型上的操42DesignoflocalERModel

設(shè)計局部ER模式

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

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

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

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

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

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