The Entity-Relationship Model ER Model - Part 2: Conversion to SQL - PowerPoint PPT Presentation

The Entity-Relationship Model ER Model - Part 2: Conversion to SQL By Michael Hahsler Based on slides for CS145 Introductjon to Databases (Stanford)

What you will learn about in this section 1. Relationships: multiplicity, multi-way 2. Design considerations 3. Conversion to SQL 2

Multiplicity of ER Relationships Using Chen’s Using Chen’s Notation Notation X Y 1 a 1 1 One-to-one: X Y 2 b 3 c d 1 a N 1 Many-to-one: 2 b X Y 3 c d 1 a 1 N One-to-many: 2 b X Y 3 c d 1 a N M Many-to-many: X 2 b Y 3 c d 3

Multiplicity of ER Relationships name name category price 1 N Product Makes Company How to read a relatjonship in both directjons: 1. A product is made by a one company 2. A company makes many product 4

name category name price 1 N makes Company Product 1 stockprice employs buys N What Person does this No specifjed say? cardinality ofuen means N:M, or we name ssn do not want to address 5 decide, yet.

Multi-way Relationships How do we model “A person buys a product in a store?” Product Purchase Store Person 6

Multiplicity in Multiway Relationships Q : What do the 1s and the N mean? Product N 1 Purchase Store 1 Person 7

Multiplicity in Multiway Relationships Betuer: many to many to many relatjonship Product N N Purchase Store N Person 8

Conversion of Multi-way Relationship to New Entity + Binary Relationships? Entity + Binary Multi-way Relationship 1 ProductOf Product Product N date ID 1 Store Purchase N Purchase StoreOf Store 1 N BuyerOf Person Person Multiple Multiple purchases per purchases per (product, store, (product, store, person) possible person) possible here! here! 9

3. Design Principles What’s wrong with these examples? What’s wrong with these examples? 1 N Product Purchase Person President Country Person 10

Design Principles: What’s Wrong? date Product Purchase Store personAddr personName 11

Design Principles: What’s Wrong? - Fixed date Product N M N 1 Store Purchase N 1 Person 12

Examples: Entity vs. Attribute Should address Or an entity? be an attribute? Street Addr ZIP Addr 1 Addr 2 Address N AddrOf Employee 1 Employee 13

Examples: Entity vs. Attribute Should address • How do we handle be an employees with more attribute? than two addresses ? Addr 1 Addr 2 • How do we handle addresses where Employee internal structure of the address (e.g. zip code, state) is useful? 14

Examples: Entity vs. Attribute Use an entity Street Addr ZIP Address N AddrOf 1 Employee In general, when we want to record several In general, when we want to record several values, we choose a separate entity. values, we choose a separate entity. 15

From ER Diagrams to Relational Schema Key concept: Both Entjty sets and Relatjonships become relatjons (tables in RDBMS) 16

From ER Diagrams to Relational Schema price category • An entjty set becomes a name Product relatjon (multjset of tuples / table) – Each tuple is one entjty Product name price category Gizmo1 99.99 Camera – Each tuple is composed of the Gizmo2 19.99 Edible entjty’s atuributes, and has the same primary key 17

From ER Diagrams to Relational Schema price category name Product CREATE TABLE Product( CREATE TABLE Product( name CHAR(50) PRIMARY KEY, name CHAR(50) PRIMARY KEY, price DOUBLE, price DOUBLE, category VARCHAR(30) category VARCHAR(30) ) ) Product name price category Gizmo1 99.99 Camera Gizmo2 19.99 Edible 18

From ER Diagrams to Relational Schema (N:M) date fjrstname lastname • A relatjon between entjty sets A 1 , …, A N also name category becomes a multjset of tuples / a table price M N Product Purchased Person – Each row/tuple is one relatjon, i.e. one unique combinatjon of entjtjes (a 1 ,…,a N ) – Each row/tuple is Purchased • composed of the union of the entjty sets’ atuributes name fjrstname lastname date • has the entjtjes’ primary keys as foreign keys Gizmo1 Bob Joe 01/01/15 • has the union of the entjty sets’ keys as Gizmo2 Joe Bob 01/03/15 primary key Gizmo1 JoeBob Smith 01/05/15 19

From ER Diagrams to Relational Schema (N:M) date fjrstname lastname name category CREATE TABLE Purchased( CREATE TABLE Purchased( name CHAR(50), name CHAR(50), price M N fjrstname CHAR(50), fjrstname CHAR(50), Product Purchased Person lastname CHAR(50), lastname CHAR(50), date DATE, date DATE, PRIMARY KEY (name, fjrstname, lastname), PRIMARY KEY (name, fjrstname, lastname), FOREIGN KEY (name) FOREIGN KEY (name) REFERENCES Product, REFERENCES Product, FOREIGN KEY (fjrstname, lastname) FOREIGN KEY (fjrstname, lastname) Purchased REFERENCES Person REFERENCES Person name fjrstname lastname date ) ) Gizmo1 Bob Joe 01/01/15 Gizmo2 Joe Bob 01/03/15 Gizmo1 JoeBob Smith 01/05/15 20

From ER Diagrams to Relational Schema (1:N) ID Street • A 1:N relatjonship can be implemented without an name extra table. ID N • Add the primary key of the “1 side” to the table for 1 Customer has Address the “N side” entjty. Number ZIPCode Address ID Number Street ZIPCode CustID 1 123 Main St 75000 1 2 6660 Willow Dr 86123 1 3 1 Nowhere Pl 99999-1234 2 21

From ER Diagrams to Relational Schema (1:N) ID Street name CREATE TABLE Address( CREATE TABLE Address( ID CHAR(50), ID CHAR(50), ID N 1 Number CHAR(50), Number CHAR(50), Customer has Address Street CHAR(50), Street CHAR(50), ZIPCode CHAR(10), ZIPCode CHAR(10), Number PRIMARY KEY (ID), PRIMARY KEY (ID), ZIPCode FOREIGN KEY (CustID) FOREIGN KEY (CustID) REFERENCES Customer, REFERENCES Customer, ) ) Address ID Number Street ZIPCode CustID 1 123 Main St 75000 1 2 6660 Willow Dr 86123 1 3 1 Nowhere Pl 99999-1234 2 22

From ER Diagram to Relational Schema How do we represent this as a relatjonal schema? date fjrstname lastname name category price Product Purchased Person name address Store 23

Alternative Notations

Exercise: Add Multiplicity to your ER diagram Also make sure to add (new concepts underlined): A player can Multiple Players can Players have a only belong to players achieve a weight which one team, a Tackle a Personal changes in on play can only single Record linked vs. ofg-season be in one person in a to a specifjc game, a play Game and Play pass/run..? 25