CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu Today E R Modeling continued Example of an E R Model Relational Model Database Design Steps Entity relationship Model Typically used for conceptual database design Three Levels of Modeling info Conceptual DB design Conceptual Data Model Logical DB design Logical Data Model Relational Model Typically used for logical database design Physical DB design Physical Data Model 3 Motivation 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 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 Mapping Cardinalities Express the number of entities to which another entity can be associated via a relationship set Most useful in describing binary relationship sets Mapping 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 Mapping 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 More complicated Details in the book Next Types of Attributes Simple vs Composite Single valued vs Multi valued Single value per attribute 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 Types of Attributes access date cust name number cust id customer has account cust street cust city balance Types of Attributes age multi valued double ellipse derivedaccess date dashed ellipse cust name number cust id date of birth customer has account cust city phone no balance cust street 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 Entity Keys date of birth cust name cust id is a natural primary key Typically SSN forms a good primary key Try to use a candidate key that rarely changes cust id age e g something involving address not a great idea customer cust street cust city phone no Relationship Set Keys What attributes are needed to represent a relationship completely and uniquely Union of primary keys of the entities involved and relationship attributes access date number cust id customer has account cust id access date account number describes a relationship completely Relationship 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 superkey access date number cust id customer has account Relationship Set Keys Is cust id account number a candidate key Depends access date number cust id customer has account Relationship Set Keys Is cust id account number a candidate key Depends access date number cust id customer has account If one to one relationship either cust id or account number sufficient Since a given customer can only have one account she can only participate in one relationship Ditto account Relationship Set Keys Is cust id account number a candidate key Depends access date number cust id customer has account If one to many relationship as shown account number is a candidate key A given customer can have many accounts but at most one account holder per account allowed Relationship Set Keys General rule for binary relationships one to one primary key of either entity set one to many primary key of the entity set on the many side many to many union of primary keys of the associate entity sets n ary relationships More complicated rules What Why have we been doing Understanding this is important Rest are details That s what books manuals are for 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 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
View Full Document
Unlocking...