CMSC424: Database DesignData ModelingMotivationDatabase Design StepsEntity-Relationship ModelSlide 6ER Diagram: Starting ExampleRest of the classNext: Relationship CardinalitiesMapping CardinalitiesSlide 11Slide 12Next: Types of AttributesTypes of AttributesSlide 15Slide 16Next: KeysEntity KeysSlide 19Slide 20Relationship Set KeysSlide 22Slide 23Slide 24Slide 25Slide 26Next: Data ConstraintsParticipation ConstraintSlide 29Cardinality ConstraintsNext: Recursive RelationshipsRecursive RelationshipsNext: Weak Entity SetsWeak Entity SetsSlide 35Slide 36Slide 37Next: SpecializationSpecialization: ExampleFinally: AggregationSlide 41Slide 42More…Slide 44Slide 45Slide 46Example DesignSlide 48Slide 49Slide 50Slide 51Slide 52SummarySlide 54CMSC424, Spring 2005CMSC424: Database DesignInstructor: Amol Deshpande [email protected], Spring 2005Data Modeling•Goals:•Conceptual representation of the data•“Reality” meets “bits and bytes”•Must make sense, and be usable by other people•Today:•Entity-relationship Model•Relational ModelCMSC424, Spring 2005Motivation•You’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:•customers•accounts•loans•branches•transactions, …•Now what??!!!CMSC424, Spring 20054 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 designCMSC424, Spring 2005Entity-Relationship Model•Two key concepts•Entities:•An object that exists and is distinguishable from other objects•Examples: Bob Smith, BofA, CMSC424•Have attributes (people have names and addresses)•Form entity sets with other entities of the same type that share the same properties•Set of all people, set of all classes•Entity sets may overlap•Customers and EmployeesCMSC424, Spring 2005Entity-Relationship Model•Two key concepts•Relationships:•Relate 2 or more entities •E.g. Bob Smith has account at College Park Branch•Form relationship sets with other relationships of the same type that share the same properties•Customers have accounts at Branches•Can have attributes:•has account at may have an attribute start-date•Can involve more than 2 entities•Employee works at Branch at JobCMSC424, Spring 20057ER Diagram: Starting Example•Rectangles: entity sets•Diamonds: relationship sets•Ellipses: attributescustomerhascust-streetcust-idcust-namecust-cityaccountbalancenumberaccess-dateCMSC424, Spring 2005Rest of the class•Details of the ER Model•How to represent various types of constraints/semantic information etc.•Design issues •A detailed exampleCMSC424, Spring 2005Next: Relationship Cardinalities•We may know:One customer can only open one accountOROne customer can open multiple accounts•Representing this is important•Why ?•Better manipulation of data•Can enforce such a constraint•Remember: If not represented in conceptual model, the domain knowledge may be lostCMSC424, Spring 2005Mapping Cardinalities•Express the number of entities to which another entity can be associated via a relationship set•Most useful in describing binary relationship setsCMSC424, Spring 2005Mapping Cardinalities•One-to-One•One-to-Many•Many-to-One•Many-to-ManycustomerhasaccountcustomerhasaccountcustomerhasaccountcustomerhasaccountCMSC424, Spring 2005Mapping Cardinalities•Express the number of entities to which another entity can be associated via a relationship set•Most useful in describing binary relationship sets•N-ary relationships ?CMSC424, Spring 2005Next: Types of Attributes•Simple vs Composite•Single value per attribute ?•Single-valued vs Multi-valued•E.g. Phone numbers are multi-valued•Derived•If date-of-birth is present, age can be derived•Can help in avoiding redundancy, enforcing constraints etc…CMSC424, Spring 2005Types of Attributescustomerhascust-streetcust-idcust-namecust-cityaccountbalancenumberaccess-dateCMSC424, Spring 2005Types of Attributescustomercust-streetcust-idcust-namecust-cityhasaccountbalancenumberaccess-datephone no.date-of-birthage•multi-valued (double ellipse)•derived (dashed ellipse)CMSC424, Spring 2005Types of Attributescustomercust-streetcust-idcust-namecust-cityhasaccountbalancenumberaccess-datephone no.date-of-birthagemonthday yearComposite AttributeCMSC424, Spring 2005Next: Keys•Key = set of attributes identifying individual entities or relationshipsCMSC424, Spring 2005customercust-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 KeysCMSC424, Spring 2005Entity Keys•Superkey•any attribute set that can distinguish entities•Candidate key•a minimal superkey•Can’t remove any attribute and preserve key-ness•{cust-id, age} not a superkey •{cust-name, cust-city, cust-street} is •assuming cust-name is not unique•Primary key•Candidate key chosen as the key by DBA•Underlined in the ER DiagramCMSC424, Spring 2005Entity Keys•{cust-id} is a natural primary key•Typically, SSN forms a good primary key•Try to use a candidate key that rarely changes•e.g. something involving address not a great ideacustomercust-streetcust-idcust-namecust-cityphone no.agedate-of-birthCMSC424, Spring 2005Relationship Set Keys•What 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-dateCMSC424, Spring 2005Relationship Set Keys•Is {cust-id, access-date, account number} a candidate key ?•No. Attribute access-date can be removed from this set without losing key-ness•In fact, union of primary keys of associated entities is always a superkeycustomerhascust-idaccountnumberaccess-dateCMSC424, Spring 2005Relationship Set Keys•Is {cust-id, account-number} a candidate key ?•Dependscustomerhascust-idaccountnumberaccess-dateCMSC424, Spring 2005Relationship Set Keys•Is {cust-id, account-number} a candidate key ?•Dependscustomerhascust-idaccountnumberaccess-date•If
View Full Document