CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Today Next class homework etc E R Model Relational Model Data Modeling Goals Conceptual representation of the data Reality meets bits and bytes Must make sense and be usable by other people End result should capture all the domain knowledge Entity Relationship Model Two key concepts Entities An object that exists and is distinguishable from other objects Examples Bob Smith BofA CMSC424 Form entity sets with other entities of the same type 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 ER Diagram Starting Example access date cust name number cust id customer has account cust street cust city Rectangles entity sets Diamonds relationship sets Ellipses attributes balance 5 Relationship Cardinalities One to One customer has account customer has account customer has account customer has account One to Many Many to One Many to Many Types of Attributes access date age cust name number cust id date of birth customer has account phone no balance cust street month day cust city year Composite Attribute Next Keys Key set of attributes that uniquely identifies an entity or a relationship Entity Keys Possible Keys cust id date of birth cust name cust name cust city cust street cust id age cust id age cust name Probably not customer Domain knowledge dependent cust street cust city phone no Entity 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 candidate key 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 Diagram Next Recursive Relationships Sometimes a relationship associates an entity set to itself Recursive Relationships emp name emp id manager works for employee worker emp street emp city Must be declared with roles Next Weak Entity Sets An entity set without enough attributes to have a primary key E g Transaction Entity Attributes transaction number transaction date transactionamount transaction type transaction number may not be unique across accounts Weak Entity Sets A weak entity set must be associated with an identifying or owner entity set Account is the owner entity set for Transaction Weak Entity Sets Still need to be able to distinguish between different weak entities associated with the same strong entity number trans date trans number account has Transaction trans type balance trans amt Weak Entity Sets Discriminator A set of attributes that can be used for that number trans date trans number account has Transaction trans type balance trans amt Weak Entity Sets Primary key Primary key of the associated strong entity discriminator attribute set For Transaction account number transaction number More Read Chapter 6 for Semantic data constraints Specialization Generalization Aggregation Generalization opposite of specialization Lower and higher level entities Attribute inheritance Homework 1 Example Design We will model a university database Main entities Professor Projects Departments Graduate students etc SSN proj number name sponsor professor project area start rank budget dept no SSN name name dept grad office age homepage degree proj number SSN PI name professor area sponsor project start rank budget Co PI Appt Chair Supervises Time homepage Major name grad age advisor office dept SSN advisee dept no name RA Mentor degree proj number SSN PI name professor area sponsor project start rank budget Co PI Appt Chair Supervises Time homepage Major name grad age advisor office dept SSN advisee dept no name RA Mentor degree proj number SSN PI name professor area sponsor project start rank budget Co PI Appt Chair Supervises Time dept Major homepage And so on name grad advisee office SSN age advisor dept no name RA Mentor degree Thoughts Nothing about actual data No talk about the query languages How is it stored How do we access the data Semantic vs Syntactic Data Models Remember E R Model is used for conceptual modeling Many conceptual models have the same properties They are much more about representing the knowledge than about database storage querying Thoughts Basic design principles Faithful Satisfies the application requirements Models the requisite domain knowledge If not modeled lost afterwards Avoid redundancy Must make sense Potential for inconsistencies Go for simplicity Typically an iterative process that goes back and forth Design Issues Entity sets vs attributes Depends on the semantics of the application Consider telephone Entity sets vs Relationsihp sets N ary vs binary relationships Consider loan Possible to avoid n ary relationships but there are some cases where it is advantageous to use them It is not an exact science Recap Entity relationship Model Intuitive diagram based representation of domain knowledge data properties etc Two key concepts Entities Relationships We also looked at Relationship cardinalities Keys Weak entity sets Relational Data Model Introduced by Ted Codd late 60 s early 70 s Before Network Data Model Cobol as DDL DML Very contentious Database Wars Charlie Bachman vs Mike Stonebraker Relational data model contributes 1 2 3 4 Separation of logical physical data models data independence Declarative query languages Formal semantics Query optimization key to commercial success 1st prototypes Ingres CA Postgres Illustra Informix IBM System R Oracle DB2 Key Abstraction Relation Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Terms Tables aka Relations Why called Relations Why Called Relations Mathematical relations Given sets R 1 2 3 S 3 4 R S 1 3 1 4 2 3 2 4 3 3 3 4 A relation on R S is any subset of R S e g 1 4 3 4 Database relations Given attribute domains Branches Downtown Brighton Accounts A 101 A 201 A 217 Balances R Account Branches Accounts Balances Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Relations bname Account Downtown Brighton Brighton acct no balance A 101 A 201 A 217 500 900 500 Considered equivalent to Downtown A 101 500 Brighton A 201 900 Brighton A 217 500 Relational database semantics defined in terms of mathematical relations Relations Account bname acct no balance Downtown Brighton Brighton A 101 A 201 A 217 500 900 500 Considered equivalent to Terms Downtown A 101 500 Brighton
View Full Document
Unlocking...