Introduction to Database Systems CSE 444 Lecture 5: E/R Diagrams Magda Balazinska - CSE 444, Fall 2010 1Outline • E/R diagrams – Sec. 4.1- 4.4 [Old edition: Chapter 2] • From E/R diagrams to relations – Sec. 4.5 and 4.6 [Old edition: Sec. 3.2 and 3.3] Magda Balazinska - CSE 444, Fall 2010 2Database Design • Why do we need it? – Need a way to model real world entities in terms of relations – Not easy to go from real-world entities to a database schema • Consider issues such as: – What entities to model – How entities are related – What constraints exist in the domain – How to achieve good designs • Several formalisms exists – We discuss E/R diagrams Magda Balazinska - CSE 444, Fall 2010 3Magda Balazinska - CSE 444, Fall 2010 Database Design Process Data Modeling Refinement SQL Tables E/R diagrams Relations Files Physical Schema Conceptual Schema Next lecture Today 4Magda Balazinska - CSE 444, Fall 2010 Conceptual Schema Design Doctor patien_of Patient name zip name dno Conceptual Model: Relational Model: plus FD’s (FD = functional dependency) Normalization: Eliminates anomalies 5Entity / Relationship Diagrams Objects entities Classes entity sets Attributes are like in ODL (ODL = Object Definition Language) Relationships: like in ODL except - first class citizens (not associated with classes) - not necessarily binary Product address buys Magda Balazinska - CSE 444, Fall 2010 This is an entity set 6Person Company Product buys makes employs name category price address name ssn stockprice name 7Keys in E/R Diagrams • Every entity set must have a key Product name category price Magda Balazinska - CSE 444, Fall 2010 8What is a Relation ? • A mathematical definition: – if A, B are sets, then a relation R is a subset of A × B • A={1,2,3}, B={a,b,c,d}, A × B = {(1,a),(1,b), . . ., (3,d)} R = {(1,a), (1,c), (3,b)} • makes is a subset of Product × Company: 1 2 3 a b c d A= B= makes Company Product Magda Balazinska - CSE 444, Fall 2010 9Multiplicity of E/R Relations • one-one: • many-one • many-many 1 2 3 a b c d 1 2 3 a b c d 1 2 3 a b c d Magda Balazinska - CSE 444, Fall 2010 10address name ssn Person buys makes employs Company Product name category stockprice name price What does this say ? 11Multi-way Relationships How do we model a purchase relationship between buyers, products and stores? Purchase Product Person Store Can still model as a mathematical set (how ?) 12Q: What does the arrow mean ? Arrows in Multiway Relationships A: A given person buys a given product from at most one store Purchase Product Person Store Magda Balazinska - CSE 444, Fall 2010 13Q: What does the arrow mean ? Arrows in Multiway Relationships A: A given person buys a given product from at most one store AND every store sells to every person at most one product Purchase Product Person Store Magda Balazinska - CSE 444, Fall 2010 14Q: How do we say that every person shops at at most one store ? Arrows in Multiway Relationships A: Cannot. This is the best approximation. (Why only approximation ?) Purchase Product Person Store Magda Balazinska - CSE 444, Fall 2010 15Converting Multi-way Relationships to Binary Purchase Person Store Product StoreOf ProductOf BuyerOf date Magda Balazinska - CSE 444, Fall 2010 163. Design Principles Purchase Product Person What’s wrong? President Person Country Moral: be faithful to the specifications of the app! Magda Balazinska - CSE 444, Fall 2010 17Design Principles: What’s Wrong? Purchase Product Store date personName personAddr Moral: pick the right kind of entities. Magda Balazinska - CSE 444, Fall 2010 18Design Principles: What’s Wrong? Purchase Product Person Store date Dates Moral: don’t complicate life more than it already is. 19From E/R Diagrams to Relational Schema • Entity set relation • Relationship relation Magda Balazinska - CSE 444, Fall 2010 20Entity Set to Relation Product name category price Product(name, category, price) name category price gizmo gadgets $19.99 Magda Balazinska - CSE 444, Fall 2010 21Relationships to Relations makes Company Product name category Stock price name Makes(product-name, product-category, company-name, year) Product-name Product-Category Company-name Starting-year gizmo gadgets gizmoWorks 1963 Start Year price (watch out for attribute name conflicts) 22Relationships to Relations makes Company Product name category Stock price name No need for Makes. Modify Product: name category price StartYear companyName gizmo gadgets 19.99 1963 gizmoWorks Start Year price 23Multi-way Relationships to Relations Purchase Product Person Store name price ssn name name address Purchase(prodName,stName,ssn) Magda Balazinska - CSE 444, Fall 2010 24Modeling Subclasses Some objects in a class may be special • define a new class • better: define a subclass Products Software products Educational products So --- we define subclasses in E/R Magda Balazinska - CSE 444, Fall 2010 25Product name category price isa isa Educational Product Software Product Age Group platforms Subclasses Magda Balazinska - CSE 444, Fall 2010 26Understanding Subclasses • Think in terms of records: – Product – SoftwareProduct – EducationalProduct field1 field2 field1 field2 field1 field2 field3 field4 field5 Magda Balazinska - CSE 444, Fall 2010 27Subclasses to Relations Product name category price isa isa Educational Product Software Product Age Group platforms Name Price Category Gizmo 99 gadget Camera 49 photo Toy 39 gadget Name platforms Gizmo unix Name Age Group Gizmo todler Toy retired Product Sw.Product Ed.Product Magda Balazinska - CSE 444, Fall 2010 Other ways to convert are possible See book sec 4.6 [Old ed: 3.3] 28• OO: classes are disjoint (same for Java, C++) p1 p2 p3 sp1 sp2 ep1 ep2 ep3 Difference between OO and E/R inheritance Product SoftwareProduct EducationalProduct Magda Balazinska - CSE 444, Fall 2010 29• E/R: entity sets overlap Difference between OO and E/R inheritance SoftwareProduct EducationalProduct p1 p2 p3 sp1 sp2 ep1 ep2 ep3 Product Magda Balazinska - CSE 444, Fall 2010 30No need for multiple
View Full Document
Unlocking...