CMSC424: Database DesignTodayDatabase Design StepsMotivationER Diagram: Starting ExampleMapping CardinalitiesSlide 7Slide 8Next: Types of AttributesTypes of AttributesSlide 11Slide 12Next: KeysEntity KeysSlide 15Slide 16Relationship Set KeysSlide 18Slide 19Slide 20Slide 21Slide 22…Next: Recursive RelationshipsRecursive RelationshipsNext: Weak Entity SetsWeak Entity SetsSlide 28Slide 29Slide 30More…Example DesignPowerPoint PresentationSlide 34Slide 35Slide 36Slide 37Thoughts…Slide 39Design IssuesSummarySlide 42Relational Data ModelKey Abstraction: RelationWhy Called Relations?RelationsSlide 47DefinitionsSo…Extra slides…Next: Data ConstraintsParticipation ConstraintSlide 53Cardinality ConstraintsNext: SpecializationFinally: AggregationSlide 57Slide 58Next: Relationship CardinalitiesCMSC424: Database DesignInstructor: Amol Deshpande [email protected]E/R Modeling continued…Example of an E/R ModelRelational Model3 Database Design StepsThree Levels of ModelinginfoConceptual Data ModelLogical Data ModelPhysical Data Model Conceptual DB design Logical DB design Physical DB designEntity-relationship Model Typically used for conceptual database designRelational Model Typically used for logical database designMotivationYou’ve just been hired by Bank of America as their DBA for their online banking web site.You are asked to create a database that monitors:customersaccountsloansbranchestransactions, …Now what??!!!5ER Diagram: Starting ExampleRectangles: entity setsDiamonds: relationship setsEllipses: attributescustomerhascust-streetcust-idcust-namecust-cityaccountbalancenumberaccess-dateMapping CardinalitiesExpress the number of entities to which another entity can be associated via a relationship setMost useful in describing binary relationship setsMapping CardinalitiesOne-to-OneOne-to-ManyMany-to-OneMany-to-ManycustomerhasaccountcustomerhasaccountcustomerhasaccountcustomerhasaccountMapping CardinalitiesExpress the number of entities to which another entity can be associated via a relationship setMost useful in describing binary relationship setsN-ary relationships ?More complicatedDetails in the bookNext: Types of AttributesSimple vs CompositeSingle value per attribute ?Single-valued vs Multi-valuedE.g. Phone numbers are multi-valuedDerivedIf date-of-birth is present, age can be derivedCan help in avoiding redundancy, enforcing constraints etc…Types of Attributescustomerhascust-streetcust-idcust-namecust-cityaccountbalancenumberaccess-dateTypes of Attributescustomercust-streetcust-idcust-namecust-cityhasaccountbalancenumberaccess-datephone no.date-of-birthagemulti-valued (double ellipse)derived (dashed ellipse)Types of Attributescustomercust-streetcust-idcust-namecust-cityhasaccountbalancenumberaccess-datephone no.date-of-birthagemonthday yearComposite AttributeNext: KeysKey = set of attributes that uniquely identifies an entity or a relationshipcustomercust-streetcust-idcust-namecust-cityphone no.agedate-of-birthPossible Keys: {cust-id} {cust-name, cust-city, cust-street} {cust-id, age} cust-name ?? Probably not.Domain knowledge dependent !!Entity KeysEntity KeysSuperkeyany attribute set that can distinguish entitiesCandidate keya minimal superkeyCan’t remove any attribute and preserve key-ness{cust-id, age} not a candidate key {cust-name, cust-city, cust-street} is assuming cust-name is not uniquePrimary keyCandidate key chosen as the key by DBAUnderlined in the ER DiagramEntity Keys{cust-id} is a natural primary keyTypically, SSN forms a good primary keyTry to use a candidate key that rarely changese.g. something involving address not a great ideacustomercust-streetcust-idcust-namecust-cityphone no.agedate-of-birthRelationship Set KeysWhat attributes are needed to represent a relationship completely and uniquely ?Union of primary keys of the entities involved, and relationship attributes{cust-id, access-date, account number} describes a relationship completelycustomerhascust-idaccountnumberaccess-dateRelationship Set KeysIs {cust-id, access-date, account number} a candidate key ?No. Attribute access-date can be removed from this set without losing key-nessIn fact, union of primary keys of associated entities is always a superkeycustomerhascust-idaccountnumberaccess-dateRelationship Set KeysIs {cust-id, account-number} a candidate key ?Dependscustomerhascust-idaccountnumberaccess-dateRelationship Set KeysIs {cust-id, account-number} a candidate key ?Dependscustomerhascust-idaccountnumberaccess-dateIf one-to-one relationship, either {cust-id} or {account-number} sufficientSince a given customer can only have one account, she can only participate in one relationshipDitto accountRelationship Set KeysIs {cust-id, account-number} a candidate key ?Dependscustomerhascust-idaccountnumberaccess-dateIf one-to-many relationship (as shown), {account-number} is a candidate keyA given customer can have many accounts, but at most one account holder per account allowedRelationship Set KeysGeneral rule for binary relationshipsone-to-one: primary key of either entity setone-to-many: primary key of the entity set on the many sidemany-to-many: union of primary keys of the associate entity setsn-ary relationshipsMore complicated rules…What have we been doingWhy ?Understanding this is importantRest are details !!That’s what books/manuals are for.Next: Recursive RelationshipsSometimes a relationship associates an entity set to itselfRecursive Relationships Must be declared with rolesemployeeworks-foremp-streetemp-idemp-nameemp-citymanagerworkerNext: Weak Entity SetsAn entity set without enough attributes to have a primary keyE.g. Transaction EntityAttributes:transaction-number, transaction-date, transaction-amount, transaction-typetransaction-number: may not be unique across accountsWeak Entity SetsA weak entity set must be associated with an identifying or owner entity setAccount is the owner entity set for TransactionWeak Entity SetsaccountbalancenumberTransactionhastrans-typetrans-numbertrans-datetrans-amt Still need to be able to distinguish between different weak entities associated with the same strong entityWeak Entity
View Full Document
Unlocking...