ERD

Dude! Where’s my Car? E R D E x er c ise

Jesse and Chester’s car is missing. Maybe it is parked somewhere? Having looked into how a parking

permit database is structured (see the walkthrough), we have to trace Jesse and Chester’s steps in the

last few days. They recall placing orders at an online store based in Diagon’s Alley for birthday presents.

These are the data from the online store. Note: We will “visit” Hogwarts next week.

Problem Draw an ER diagram for the three entities (ie tables): Customer, Employee, and Order. The data are

given in the corresponding table. For simplicity, and also because we want to get the concepts right, we

may assume these data are all there is in the tables.

The Customer table comprises customers who makes an order. The customer can set up an account with

the store, but may not have place any orders. Naturally, the same customer can also make multiple

orders using the same customer account. The order table is a record of all the sales transactions that

have occurred. The Employee table shows the employees who are assigned to handle the order.

Naturally, each employee can be assigned to multiple sales orders, but some may not have been

assigned to any particular order just yet. To avoid confusion, we’ll use SSN as the ID for each employee.

Note that this is not a good practice because SSNs are very sensitive and not suitable for private use.

CUSTOMER

Customer_No First_Name Last_Name

A001 Jesse Montgomery

A002 Chester Greenburg

A003 Wanda Garner

A004 Wilma Sokoloff

ORDER

Customer_No Customer_No SSN

X333 A002 000-000-0000

X555 A002 222-222-2222

X666 A003 222-222-2222

X777 A003 111-111-1111

X999 A003 111-111-1111

EMPLOYEE

SSN First_Name Last_Name

000-000-0000 Harry Potter

111-111-1111 Hermione Granger

222-222-2222 Ginny Weasley

333-333-3333 Ron Weasley

Solution