Unit 09 · Practice
Practice
Your diagrams are saved in this browser, and each Check goes into your submission.
A hospital schedules its nurses into shifts. Each shift has several nurses working it, and each nurse works many shifts over a month. How should the hospital's database store which nurses work which shifts?
A library gives each member a card with a number on it. When a member loses their card, the library gives them a new one with a new number, and keeps the record of the old card. How should the library's database connect members and cards?
In the college database, enrollment is the joining table between student and class. Here are three of its rows.
| student_id | class_id | enrollment_time | gpa |
|---|---|---|---|
| 4 | 1 | 2025-12-10 19:44:21 | 3.2 |
| 4 | 18 | 2026-03-26 10:01:23 | 3.8 |
| 61 | 1 | 2025-12-11 10:13:19 | 1.5 |
What does the first row say?
Start from Books, join BooksAuthors on BookID, then join Authors on AuthorID. Put each alias right after its table's name.
An actor can be in many movies, and a movie has many actors. A movie database has Actors, Movies, and a joining table MovieActors between them. Where does the name of the character an actor played in a movie go?
A bookstore made its tables with the script below. Draw the diagram of the tables it makes, with every column, its type, the primary keys, NOT NULL where the script has it, and the lines.
CREATE TABLE Publisher (
ID INT,
Name VARCHAR(255) NOT NULL,
DateFounded DATE NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE Book (
ID INT,
Title VARCHAR(255) NOT NULL,
PublishDate DATE NOT NULL,
PublisherID INT,
PRIMARY KEY (ID),
FOREIGN KEY (PublisherID) REFERENCES Publisher(ID)
);
CREATE TABLE Author (
ID INT,
FirstName VARCHAR(255) NOT NULL,
LastName VARCHAR(255) NOT NULL,
PRIMARY KEY (ID)
);
CREATE TABLE BookAuthor (
BookID INT,
AuthorID INT,
PRIMARY KEY (BookID, AuthorID),
FOREIGN KEY (BookID) REFERENCES Book(ID),
FOREIGN KEY (AuthorID) REFERENCES Author(ID)
);
A restaurant booking service keeps its guests, its restaurants, and the reservations guests make at them in these three tables. Draw the diagram for them, with a type for every column, a primary key in each table, and the lines between them.
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 09: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 |
This question's database has Restaurants and Cuisines.
Restaurants
| ID | Name |
|---|---|
| 1 | Bob's Gourmet Macaroni |
| 2 | Cheese Louise Dinner |
Cuisines
| ID | Name |
|---|---|
| 1 | Italian |
| 2 | American |
| 3 | Mexican |
Write the CREATE TABLE for RestaurantCuisines, the joining table between them, with the columns RestaurantID and CuisineID. The two columns together are its primary key, and the database has to refuse a restaurant or a cuisine it doesn't have.
Then put in these rows: Bob's Gourmet Macaroni serves Italian and American, and Cheese Louise Dinner serves American.
Both columns go in one PRIMARY KEY, with a comma between them. Each column is also a FOREIGN KEY, pointing at the ID of the table it comes from.
This question has its own database. It keeps everything you run, on top of the tables it starts with, until you reset it.
Draw a database design for a chain of lemonade stands. The chain's requirements:
- Each lemonade stand has a street address and a target revenue.
- A lemonade stand can receive many shipments, but a shipment goes to only one stand. The database tracks which shipment was sent to which stand.
- Every shipment has the date it was sent. A shipment can also have the date it was received, but that's empty until the shipment reaches the stand.
- A shipment can contain many different supply items, like lemons, water, and napkins, and a supply item can be in many shipments. The database also tracks how many of each supply item was sent in a shipment.
- Each supply item has a name and a price.
- A lemonade stand can have many employees, but an employee works at only one stand at a time.
- Each employee has a first name, a last name, and an age. The database also tracks the day of the week the employee works (M, Tu, W, Th, F, Sat, or Sun). Employees are scheduled to work once each week.
The design should take five tables. If you end up with more or fewer, rethink the design.
Try your design
In the box below, write INSERT statements that put at least two rows in every table: two stands, an employee at each stand, two supply items, and two shipments, with at least two supply items in one of the shipments. Run the script, and fix the design or the script until every row goes in.
Run makes the tables your diagram describes, then runs your script on them. The script is saved with your diagram.
Show the SQL
When you're done, download your submission file and upload it to this unit's practice assignment in Canvas. It carries a summary of your progress and the record of every attempt you made on this page. You can submit as many times as you like; the last one counts.
Attempts are saved in this browser. To keep going on another computer, download your submission here and resume from it there.