# Many-to-Many Relationships and Entity Relationship Diagrams
# Many-to-Many Relationships (~10 min)
# Table Aliasing (~3 min)
# Creating Entity Relationship Diagrams
# One-to-Many ER Diagrams
# Many-to-Many ER Diagrams
# SQL Analytics M7 - Composite Primary Keys (~6 min)
# Creating Entity Relationship Diagrams
Link to the diagraming tool -> drawSQL ( https://drawsql.app)
# Practice Questions
Create an entity relationship diagram for the following questions using drawSQL. Please check with your instructor and get permission before using any other tool.
- (4pts) Create an entity relationship diagram to represent the tables created in the script below.
- (3pts) Create an entity relationship diagram to represent the tables below.
# Guests
| ID | FirstName | LastName | Phone |
|---|---|---|---|
| 1 | Nathaniel | Mandrake | (111) 222 3333 |
| 2 | David | Martinez | (333) 444 5555 |
| 3 | Valentine | Wiggin | (666) 777 8888 |
# Reservations
| ID | GuestID | RestaurantID | ReservationDateTime |
|---|---|---|---|
| 1 | 3 | 2 | 2024-01-02 19:00:00 |
| 2 | 1 | 1 | 2024-01-03 9:30:00 |
| 3 | 2 | 1 | 2024-01-03 10:00:00 |
| 4 | 3 | 2 | 2024-01-04 17:30:00 |
# Restaurants
| ID | Name |
|---|---|
| 1 | Bob’s Gourmet Macaroni |
| 2 | Cheese Louise Dinner |
- (5pts) Create an entity relationship diagram for a chain of lemonade stands. They need a database to help them track information about their stands, shipments and employees. Requirements:
- Each lemonade stand has a street address and a target revenue.
- Lemonade stands can receive many shipments, but a shipment can only go to one lemonade stand. The database should track which shipment is sent to which stand.
- Shipments must always have the date that they were sent. A shipment may also have the date that they were received but this value might be empty if a shipment has not reached the stand yet.
- Each shipment can contain many different supply items (such as lemons, water, napkins, etc.) and a supply item can belong to many shipments. The database should also track how many of each supply item was sent in a shipment.
- Each supply item has a name and a price.
- Lemonade stands can have many employees but an employee can only work at one stand at a time.
- Each employee has a first name, a last name and an age. The database should also track which day of the week (M, Tu, W, Th, F, Sat, or Sun) that they work. Employees are only scheduled to work once each week.
Representing this scenario should require 5 tables. If you find yourself with either more or less, it’s likely you need to rethink the design.
# How to Submit
For each question, take screenshots of your completed entity relationship diagrams from drawSQL.
Upload each of your images to the assignment submission on Canvas. You should be submitting a total of 3 screenshots for this assignment.