Database-Design-design-theory

CMPSC431WDatabaseManagementSystemsLogicalDatabaseDesignSchemaRefinementandNormalFormsDatabaseDesignProcess1.RequirementAnalysis2.ConceptualDatabaseDesign3.LogicalDatabaseDesign4.SchemaRefinement5.PhysicalDatabaseDesign6.ApplicationandSecurityDesignThisiswhereE/RModelfitsinSupportedbyDBDesignTheoryLogicalDBDesign:ERRelational•EntitySetstoTablesProduct(name,category,price)ProductnamecategorypricenamecategorypriceiPhone12Electronics$700iPadProElectronics$300OfficeSoftware$120CREATETABLEProduct(nameVARCHAR(40),categoryVARCHAR(40),priceDECIMAL(2,10),PRIMARYKEY(name))LogicalDBDesign:ERRelationalOrders(prod-ID,cust-ID,date)Shipment(prod-ID,cust-ID,name,date)Shipping-Co(name,address)Ordersprod-IDcust-IDdateShipping-CoaddressnameShipmentdateprod-IDcust-IDnamedateIPH12PdongxUPS01/12/2021IPH12PdongxFEDEX02/12/2021Many-to-ManyRelationshiptoTablesShipment(prod-ID,cust-ID,name,date)Ordersprod-IDcust-IDdateShipping-CoaddressnameShipmentdateprod-IDcust-IDnamedateIPH12PdongxUPS01/12/2021IPH12PdongxFEDEX02/12/2021CREATETABLEShipment(prod-IDCHAR(6),cust-IDVARCHAR(60),nameVARCHAR(10),dateDATETIME,PRIMARYKEY(prod-ID,cust-ID,name),FOREIGNKEY(prod-ID,cust-ID)REFERENCESOrder(prod-ID,cust-ID),FOREIGNKEY(name)REFERENCEShipping-Co(name))KeyConstraints:CombiningRelations•FormanytoonerelationshipsmakesCompanyProductnamecategorynamepriceP.nameC.nameiPhone12AppleiPadProAppleOfficeMicrosoftnamecategorypriceiPhone12Electronics$700iPadProElectronics$300OfficeSoftware$120namecountryAppleUSMicrosoftUSProductMakesCompanycountryKeyConstraints:CombiningRelations•Noseparaterelationsformany-onerelationshippnamecnamecategorypriceiPhone12AppleElectronics$700iPadProAppleElectronics$300OfficeMicrosoftSoftware$120namecategorypriceiPhone12Electronics$700iPadProElectronics$300OfficeSoftware$120namecountryAppleUSMicrosoftUSProductMakeCompanynamecountryAppleUSMicrosoftUSP.nameC.nameiPhone12AppleiPadProAppleOfficeMicrosoftKeyConstraints:CombiningRelationspnamecnamecategorypriceiPhone12AppleElectronics$700iPadProAppleElectronics$300OfficeMicrosoftSoftware$120namecategorypriceiPhone12Electronics$700iPadProElectronics$300OfficeSoftware$120ProductMakeP.nameC.nameiPhone12AppleiPadProAppleOfficeMicrosoftCREATETABLEProduct(pnameVARCHAR(50),cnameVARCHAR(60),categoryVARCHAR(60),priceDECIMAL(2,10),PRIMARYKEY(pname,cname),FOREIGNKEY(cname)REFERENCESCompany(name))namecountryAppleUSMicrosoftUSCompanyCapturingParticipationConstraints•Wecancaptureparticipationconstraintsinvolvingoneentityset(exactlyoncesemantics)inabinaryrelationship,butlittleelse.CREATETABLEProduct(pnameVARCHAR(50),cnameVARCHAR(60)NOTNULL,categoryVARCHAR(60),priceDECIMAL(2,10),PRIMARYKEY(pname,cname),FOREIGNKEY(cname)REFERENCESCompany(name))TranslatingISAHierarchies•Generalapproach:•3relations:Employees,Hourly_EmpsandContract_Emps.•Employees(ssn,name,lot)•Hourly_Emps(ssnFK,hourlywages,hours_worked)•Contract_Emps(ssnFK,contractid)•Queriesinvolvingallemployeeseasy,thoseinvolvingjustHourly_Empsrequireajointogetsomeattributes.•Alternative:JustHourly_EmpsandContract_Emps.•Hourly_Emps(ssn,name,lot,hourlywages,hours_worked)•Contract_Emps(ssn,name,lot,contractid)•Eachemployeemustbeinoneofthesetwosubclasses.TranslatingAggregation•Treatarelationshiptableasanentity!•Keyconstraints-alsocombinerelations.Monitors(pidFK_P,didFK_D,ssn,until)Sponsor_Monitors(pidFK_P,didFK_D,ssnFK_E,since,until)OneStepFurther:SchemaRefinement•Refineschemawithdatabasedesigntheory.•Designtheoryisabouthowtorepresentyourdatatoavoidanomalies.StudentCourseRoomMike431WAQ3149Mary431WAQ3149Sam431WAQ3149......StudentCourseMike431WMary431WSam431W....CourseRoom431WAQ3149541T9204Design1Design2FourTypesofAnomalies-1•What’swrong?StudentCourseRoomMike431WAQ3149Mary431WAQ3149Sam431WAQ3149......Ifeverycourseisinonlyoneroom,containsredundantinformation!FourTypesofAnomalies-2•What’swrong?StudentCourseRoomMike431WAQ3149Mary431WT9204Sam431WAQ3149......Ifweupdatetheroomnumberforonetuple,wegetinconsistentdata=anupdateanomalyFourTypesofAnomalies-3•What’swrong?StudentCourseRoom......Ifeveryonedropstheclass,welosewhatroomtheclassisin!=adeleteanomalyFourTypesofAnomalies-4•What’swrong?StudentCourseRoomMike431WAQ3149Mary431WAQ3149Sam431WAQ3149......Similarly,wecan’treservearoomwithoutstudents=aninsertanomaly…541T9204EliminatingAnomalies•Isitbetter?StudentCourseMike431WMary431WSam431W....CourseRoom431WAQ3149541T9204Whythisdesignmaybebetter?Howtofindthisdecomposition?•Redundancy?•Updateanomaly?•Deleteanomaly?•Insertanomaly?NormalForms•1stNormalForm(1NF)=Alltablesareflat•2ndNormalForm=nolongerused•Boyce-CoddNormalForm(BCNF)=nobadFDs•3rd,4th,and5thNormalForms=seetextbooks1stNormalForm(1NF)StudentCoursesMary{CS145,CS229}Joe{CS145,CS106}……Violates1NF.1NFConstraint:Typesmustbeatomic!StudentCoursesMaryCS145MaryCS229JoeCS145JoeCS106In1stNFNormalForms•1stNormalForm(1NF)=Alltablesareflat•2ndNormalForm=nolongerused•Boyce-CoddNormalForm(BCNF)=nobadFDs•3rd,4th,and5thNormalForms=seetextbooksWhat’sthis?FunctionalDependencyA-Bmeansthat“whenevertwotuplesagreeonAthentheyagreeonB.”De