1 Lecture 05: E/R Diagrams Wednesday, April 7, 2010 Dan Suciu -- 444 Spring 2010Announcements • Homework 1 is posted: due April 21st • You need to create tables, import data: – On SQL Server, in your own database, OR – On postgres (we will use it for Project 2) • Follow Web instructions for importing data • Read book about CREATE TABLE, INSERT, DELETE, UPDATE Dan Suciu -- 444 Spring 2010 23 Outline • E/R diagrams – Chapter 4.1-4.4 • From E/R diagrams to relations – Chapters 4.5 Dan Suciu -- 444 Spring 20104 Database Design • Why do we need it? – Agree on structure of the database before deciding on a particular implementation. • 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 Dan Suciu -- 444 Spring 20105 Entity / Relationship Diagrams Objects entities Classes entity sets Attributes: Relationships - first class citizens (not associated with classes) - not necessarily binary Product address buys Dan Suciu -- 444 Spring 20106 Person Company Product buys makes employs prod-ID category price address name ssn stockprice name7 Keys in E/R Diagrams • Every entity set must have a key • May be a multi-attribute key: Product prod-ID category price Dan Suciu -- 444 Spring 2010 Order prod-ID cust-ID date8 What 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 Product9 Multiplicity 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 Dan Suciu -- 444 Spring 201010 address name ssn Person buys makes employs Company Product prod-ID category stockprice name price What does this say ?11 Multi-way Relationships Purchase Product Person Store Dan Suciu -- 444 Spring 2010 date12 Converting Multi-way Relationships to Binary Purchase Person Store Product StoreOf ProductOf BuyerOf date Arrows are missing: which ones ?13 3. Design Principles Purchase Product Person What’s wrong? President Person Country Moral: be faithful! Dan Suciu -- 444 Spring 201014 Design Principles: What’s Wrong? Purchase Product Store date personName personAddr Moral: pick the right kind of entities.15 Design Principles: What’s Wrong? Purchase Product Person Store date Dates Moral: don’t complicate life more than it already is.16 From E/R Diagrams to Relational Schema • Entity set relation • Relationship relation Dan Suciu -- 444 Spring 2010Entity Set to Relation Product prod-ID category price Product(prod-ID, category, price) prod-ID category price Gizmo55 Camera 99.99 Pokemn19 Toy 29.99Create Table (SQL) Dan Suciu -- 444 Spring 2010 18 CREATE TABLE Product ( prod-ID CHAR(30) PRIMARY KEY, category VARCHAR(20), price double)Relationships to Relations 19 Orders prod-ID cust-ID date Shipment Shipping-Co address name Shipment(prod-ID,cust-ID, name, date) prod-ID cust-ID name date Gizmo55 Joe12 UPS 4/10/2010 Gizmo55 Joe12 FEDEX 4/9/2010 dateCreate Table (SQL) Dan Suciu -- 444 Spring 2010 20 CREATE TABLE Shipment( name CHAR(30) REFERENCES Shipping-Co, prod-ID CHAR(30), cust-ID VARCHAR(20), date DATETIME, PRIMARY KEY (name, prod-ID, cust-ID), FOREIGN KEY (prod-ID, cust-ID) REFERENCES Orders )21 Multi-way Relationships to Relations Purchase Product Person Store prod-ID price ssn name name address Dan Suciu -- 444 Spring 2010 How do we represent that in a relation ?22 Modeling Subclasses Products Software products Educational products23 Product name category price isa isa Educational Product Software Product Age Group platforms Subclasses Dan Suciu -- 444 Spring 201024 Understanding Subclasses • Think in terms of records: – Product – SoftwareProduct – EducationalProduct field1 field2 field1 field2 field1 field2 field3 field4 field525 Subclasses 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 Dan Suciu -- 444 Spring 201026 Modeling UnionTypes With Subclasses FurniturePiece Person Company Say: each piece of furniture is owned either by a person, or by a company Dan Suciu -- 444 Spring 201027 Modeling Union Types with Subclasses Say: each piece of furniture is owned either by a person, or by a company Solution 1. Acceptable (What’s wrong ?) FurniturePiece Person Company ownedByPerson ownedByPerson Dan Suciu -- 444 Spring 201028 Modeling Union Types with Subclasses Solution 2: More faithful isa FurniturePiece Person Company ownedBy Owner isa29 Constraints in E/R Diagrams Finding constraints is part of the modeling process. Commonly used constraints: Keys: social security number uniquely identifies a person. Single-value constraints: a person can have only one father. Referential integrity constraints: if you work for a company, it must exist in the database. Other constraints: peoples’ ages are between 0 and 150. Dan Suciu -- 444 Spring 201030 Keys in E/R Diagrams Product name category price Underline: Multi-attribute key v.s. Multiple keys Not possible in E/R31 Single Value Constraints makes makes v. s. Dan Suciu -- 444 Spring 201032 Referential Integrity Constraints Company Product makes Company Product makes Each product made by at most one company. Some products made by no company Each product made by exactly one company.33 Other Constraints Company Product makes <100 What does this mean ? Dan Suciu -- 444 Spring 201034 Weak Entity Sets Entity sets are weak when their key comes from other classes to which they are related. University Team affiliation number sport name Notice: we encountered this when converting multiway relationships to binary relationships35 Handling Weak Entity Sets University Team affiliation number sport name How do we represent this with relations ? Dan Suciu -- 444 Spring
View Full Document
Unlocking...