9 9 08 Instructor Amol Deshpande amol cs umd edu Data Models Conceptual representa8on of the data Data Retrieval How to ask ques8ons of the database How to answer those ques8ons Data Storage How where to store data how to access it Data Integrity Manage crashes concurrency Manage seman8c inconsistencies 1 9 9 08 Overview of modeling En8ty rela8onship Model E R model Rela8onal Model Conver8ng from E R to Rela8onal Extra slides Goals Conceptual representa8on of the data Reality meets bits and bytes Must make sense and be usable by other people We will study En8ty rela8onship Model Rela8onal Model Note the di erence May study XML based models or object oriented models Why so many models 2 9 9 08 Why so many models Tradeo between Descrip8ve capabili8es How many concepts it has What can t it capture Is it reasonably easy for humans to use and reason about Performance Can it be implemented reasonably e ciently 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 transac8ons Now what Start with modeling the informa8on that needs to be stored 3 9 9 08 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 7 Overview of modeling En8ty rela8onship Model E R model Rela8onal Model Conver8ng from E R to Rela8onal Extra slides 4 9 9 08 Two key concepts En es An object that exists and is dis nguishable from other objects Examples Bob Smith BofA CMSC424 Have a0ributes people have names and addresses Form en ty sets with other en88es of the same type that share the same proper8es Set of all people set of all classes En8ty sets may overlap Customers and Employees Two key concepts Rela onships Relate 2 or more en88es E g Bob Smith has account at College Park Branch Form rela onship sets with other rela8onships of the same type that share the same proper8es Customers have accounts at Branches Can have abributes has account at may have an abribute start date Can involve more than 2 en88es Employee works at Branch at Job 5 9 9 08 access date cust name number cust id customer has account cust street cust city balance Rectangles en8ty sets Diamonds rela8onship sets Ellipses abributes 11 Details of the ER Model How to represent various types of constraints seman8c informa8on etc Design issues A detailed example 6 9 9 08 We may know One customer can only open one account OR One customer can open mul8ple accounts Represen8ng this is important Why Beber manipula8on of data If former can store the account info in the customer table Can enforce such a constraint Applica8on logic will have to do it NOT GOOD Remember If not represented in conceptual model the domain knowledge may be lost Express the number of en88es to which another en8ty can be associated via a rela8onship set Most useful in describing binary rela8onship sets 7 9 9 08 One to One One to Many Many to One customer has account customer has account customer has account customer has account Many to Many Express the number of en88es to which another en8ty can be associated via a rela8onship set Most useful in describing binary rela8onship sets N ary rela8onships More complicated Details in the book 8 9 9 08 Simple vs Composite Single value per abribute Single valued vs Mul8 valued E g Phone numbers are mul8 valued Derived If date of birth is present age can be derived Can help in avoiding redundancy enforcing constraints etc access date cust name number cust id customer has account cust street cust city balance 9 9 9 08 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 Key set of abributes that uniquely iden8 es an en8ty or a rela8onship 10 9 9 08 Possible Keys date of birth cust name cust id cust name cust city cust street cust id age cust id cust name Probably not age customer Domain knowledge dependent cust street cust city phone no Superkey any abribute set that can dis8nguish en88es Candidate key a minimal superkey Can t remove any abribute 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 11 9 9 08 cust id is a natural primary key Typically date of birth cust name Try SSN forms a good primary key to use a candidate key that rarely changes e g something involving address not a great idea cust id age customer cust street cust city phone no What abributes are needed to represent a rela8onship completely and uniquely Union of primary keys of the en88es involved and rela8onship abributes access date number cust id customer has account cust id access date account number describes a rela8onship completely 12 9 9 08 Is cust id access date account number a candidate key No Abribute access date can be removed from this set without losing key ness In fact union of primary keys of associated en88es is always a superkey access date number cust id customer has account Is cust id account number a candidate key Depends access date number cust id customer has account 13 9 9 08 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 Is cust id account number a candidate key Depends access date number cust id customer has account If one to many relationship as shown accountnumber is a candidate key A given customer can have many accounts but at most one account holder per account allowed 14 9 9 08 General rule for binary rela8onships one to one primary key of either en8ty set one to many primary key of the en8ty set on the many side many to many union of primary keys of the associate en8ty sets n ary rela8onships More complicated rules What have we been doing Why Understanding this is important Rest are details That s what books manuals are for 15 9 9 08 Some8mes a rela8onship associates an en8ty set to itself emp name emp id manager works for employee worker emp street emp city Must be declared with roles 16 9 9 08 An en8ty set without enough abributes to
View Full Document
Unlocking...