DOC PREVIEW
UMD CMSC 424 - Database Design

This preview shows page 1-2-3-4-25-26-27-51-52-53-54 out of 54 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
View full document
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 54 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

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

UMD CMSC 424 - Database Design

Documents in this Course
Lecture 2

Lecture 2

36 pages

Databases

Databases

44 pages

Load more
Download Database Design
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Database Design and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Database Design 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?