UMD CMSC 424 - Database Design (54 pages)

Previewing pages 1, 2, 3, 4, 25, 26, 27, 51, 52, 53, 54 of 54 page document View the full content.
View Full Document

Database Design



Previewing pages 1, 2, 3, 4, 25, 26, 27, 51, 52, 53, 54 of actual document.

View the full content.
View Full Document
View Full Document

Database Design

237 views


Pages:
54
School:
University of Maryland, College Park
Course:
Cmsc 424 - Database Design
Database Design Documents
Unformatted text preview:

CMSC424 Database Design Instructor Amol Deshpande amol cs umd edu CMSC424 Spring 2005 Data 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 Model CMSC424 Spring 2005 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 CMSC424 Spring 2005 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 CMSC424 Spring 2005 4 Entity 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 Employees CMSC424 Spring 2005 Entity 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 Job CMSC424 Spring 2005 ER Diagram Starting Example access date cust name number cust id customer has account cust street cust city balance Rectangles entity sets Diamonds relationship sets Ellipses attributes CMSC424 Spring 2005 7 Rest of the class Details of the ER Model How to represent various types of constraints semantic information etc Design issues A detailed example CMSC424 Spring 2005 Next Relationship Cardinalities We may know One customer can only open one account OR One 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 lost CMSC424 Spring 2005 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 CMSC424 Spring 2005 Mapping Cardinalities One to One One to Many Many to One customer has account customer has account customer has account customer has account Many to Many CMSC424 Spring 2005 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 CMSC424 Spring 2005 Next 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 2005 Types of Attributes access date cust name number cust id customer has account cust street cust city balance CMSC424 Spring 2005 Types of Attributes age multi valued double ellipse access date derived dashed ellipse cust name number cust id date of birth customer has account cust city phone no balance cust street CMSC424 Spring 2005 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 CMSC424 Spring 2005 Next Keys Key set of attributes identifying individual entities or relationships CMSC424 Spring 2005 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 CMSC424 Spring 2005 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 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 Diagram CMSC424 Spring 2005 Entity Keys date of birth cust id is a natural primary key Typically SSN forms a good primary key Try to use a candidate key that rarely changes cust name cust id age customer e g something involving address not a great idea cust street cust city phone no CMSC424 Spring 2005 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 CMSC424 Spring 2005 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 CMSC424 Spring 2005 account Relationship Set Keys Is cust id account number a candidate key Depends access date number cust id customer has CMSC424 Spring 2005 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 CMSC424 Spring 2005 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 CMSC424 Spring 2005 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 CMSC424 Spring 2005 Next Data Constraints Representing semantic data constraints We already saw constraints on relationship cardinalities CMSC424 Spring 2005 Participation Constraint Given an entity set E and a


View Full Document

Access the best Study Guides, Lecture Notes and Practice Exams

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 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?