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

3

Multiplicity of ER Relationships Using Chen’s

Notation Using Chen’s Notation

1 2 3 a b c d

One-to-one:

1 2 3 a b c d

Many-to-one:

1 2 3 a b c d

One-to-many:

1 2 3 a b c d

Many-to-many:

X Y

N M

X Y

1 N

X Y

N 1

X Y

1 1

X Y

Multiplicity of ER Relationships

4

Product name category price Company name Makes

How to read a relatjonship in both directjons:

• 1. A product is made by a one company
• 2. A company makes many product

N 1

5

address name ssn Person buys makes employs Company Product name category stockprice name price

What does this say?

N 1 N 1

No specifjed cardinality ofuen means N:M, or we do not want to decide, yet.

6

Multi-way Relationships

How do we model “A person buys a product in a store?” Purchase Product Person Store

7

Q: What do the 1s and the N mean?

Multiplicity in Multiway Relationships

Purchase

Product Person Store

N 1 1

8

Betuer: many to many to many relatjonship

Multiplicity in Multiway Relationships

Purchase

Product Person Store

N N N

Conversion of Multi-way Relationship to New Entity + Binary Relationships?

9

Purchase

Product Person Store

Multi-way Relationship Entity + Binary

Purchase Person Store Product StoreOf ProductOf BuyerOf date

N N N 1 1 1

Multiple purchases per (product, store, person) possible here! Multiple purchases per (product, store, person) possible here!

ID

10

• 3. Design Principles

Purchase Product Person What’s wrong with these examples? What’s wrong with these examples? President Person Country

N 1

11

Design Principles: What’s Wrong?

Purchase Product Store date personName personAddr

12

Design Principles: What’s Wrong? - Fixed

Product Person Store date Purchase

N M N 1 N 1

Examples: Entity vs. Attribute

13

Should address be an attribute?

Employee Addr 1 Addr 2

Address Street Addr ZIP Employee AddrOf

Or an entity?

N 1

Examples: Entity vs. Attribute

14

Should address be an attribute?

Employee Addr 1 Addr 2

• How do we handle

employees with more than two addresses?

• How do we handle

addresses where internal structure of the address (e.g. zip code, state) is useful?

Examples: Entity vs. Attribute

15

Address

Street Addr

ZIP Employee AddrOf

Use an entity

In general, when we want to record several values, we choose a separate entity. In general, when we want to record several values, we choose a separate entity.

N 1

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

name price category Gizmo1 99.99 Camera Gizmo2 19.99 Edible

17 Product price category name

Product

• An entjty set becomes a

relatjon (multjset of tuples / table)

– Each tuple is one entjty – Each tuple is composed of the entjty’s atuributes, and has the same primary key

From ER Diagrams to Relational Schema

name price category Gizmo1 99.99 Camera Gizmo2 19.99 Edible

18 Product price category name

Product

CREATE TABLE Product( name CHAR(50) PRIMARY KEY, price DOUBLE, category VARCHAR(30) ) CREATE TABLE Product( name CHAR(50) PRIMARY KEY, price DOUBLE, category VARCHAR(30) )

From ER Diagrams to Relational Schema (N:M)

name fjrstname lastname date Gizmo1 Bob Joe 01/01/15 Gizmo2 Joe Bob 01/03/15 Gizmo1 JoeBob Smith 01/05/15

19

Purchased

• A relatjon between entjty sets A1, …, AN also

becomes a multjset of tuples / a table – Each row/tuple is one relatjon, i.e. one unique combinatjon of entjtjes (a1,…,aN) – Each row/tuple is

• composed of the union of the entjty sets’

atuributes

• has the entjtjes’ primary keys as foreign keys
• has the union of the entjty sets’ keys as

primary key

Purchased Product name category price Person fjrstname date lastname

N M

From ER Diagrams to Relational Schema (N:M)

20

CREATE TABLE Purchased( name CHAR(50), fjrstname CHAR(50), lastname CHAR(50), date DATE, PRIMARY KEY (name, fjrstname, lastname), FOREIGN KEY (name) REFERENCES Product, FOREIGN KEY (fjrstname, lastname) REFERENCES Person ) CREATE TABLE Purchased( name CHAR(50), fjrstname CHAR(50), lastname CHAR(50), date DATE, PRIMARY KEY (name, fjrstname, lastname), FOREIGN KEY (name) REFERENCES Product, FOREIGN KEY (fjrstname, lastname) REFERENCES Person )

name fjrstname lastname date Gizmo1 Bob Joe 01/01/15 Gizmo2 Joe Bob 01/03/15 Gizmo1 JoeBob Smith 01/05/15

Purchased

Purchased Product name category price Person fjrstname date lastname

N M

From ER Diagrams to Relational Schema (1:N)

21

ID Number Street ZIPCode CustID 1 123 Main St 75000 1 2 6660 Willow Dr 86123 1 3 1 Nowhere Pl 99999-1234 2

Address

has Customer name ZIPCode ID Address ID Street

1 N

Number

• A 1:N relatjonship can be implemented without an

extra table.

• Add the primary key of the “1 side” to the table for

the “N side” entjty.

From ER Diagrams to Relational Schema (1:N)

22

CREATE TABLE Address( ID CHAR(50), Number CHAR(50), Street CHAR(50), ZIPCode CHAR(10), PRIMARY KEY (ID), FOREIGN KEY (CustID) REFERENCES Customer, ) CREATE TABLE Address( ID CHAR(50), Number CHAR(50), Street CHAR(50), ZIPCode CHAR(10), PRIMARY KEY (ID), FOREIGN KEY (CustID) REFERENCES Customer, )

ID Number Street ZIPCode CustID 1 123 Main St 75000 1 2 6660 Willow Dr 86123 1 3 1 Nowhere Pl 99999-1234 2

Address

has Customer name ZIPCode ID Address ID Street

1 N

Number

From ER Diagram to Relational Schema

23 Purchased Product name category price Person fjrstname date lastname Store name address

How do we represent this as a relatjonal schema?

Alternative Notations

Exercise: Add Multiplicity to your ER diagram

25

A player can

• nly belong to
• ne team, a

play can only be in one game, a pass/run..? Multiple players Tackle a single person in a play Players can achieve a Personal Record linked to a specifjc Game and Play

Also make sure to add (new concepts underlined):

Players have a weight which changes in on

• vs. ofg-season