Unit 09 · Practice

Practice

Your diagrams are saved in this browser, and each Check goes into your submission.

Part 1 · Many-to-many relationships
1

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?

2

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?

3

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?

4
In the books and authors database, write a query that shows the title of every book Orion Stardust wrote, with the author's name. Give each table an alias, and use the aliases in the rest of the query.
⌘/Ctrl + Enter

Start from Books, join BooksAuthors on BookID, then join Authors on AuthorID. Put each alias right after its table's name.

5

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?

Part 2 · Diagrams and a joining table
6

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)
);
7

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
8

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.

⌘/Ctrl + Enter

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.

Part 3 · Lemonade stands
9

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.

⌘/Ctrl + Enter
Show the SQL
Submit

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.