Week 8 exercises

Here is two weeks' worth of a library's schedule for their librarians.

This data refers to the weeks starting March 6^th, 2023. The schedule is for two weeks, and you cannot assume it repeats in the future.

A value of 1 in the shift column means the librarian will work in the morning. A value of 2 means afternoon. Actual start and end times of these shifts are variable, so don't sweat that.

In your own database, normalize this data by creating tables and representing this data. It is okay to add data, alter data formatting & add columns.

After normalizing, write two queries for your lab (and I'll also take a look at your tables and columns, so don't drop them!):

One that can tell me the names and shifts of the librarians working not the first Monday, but the Monday after, and
one that can tell me Beverly Cleary's assignments for that day.

Your tables should:

...duplicate data as little as possible.
...have no one-to-one relationships that you can't justify.
...have no many-to-many relationships that aren't mediated by a bridging table.
...have one table for each type of entity (person, place, thing, event).
...have one cell for 1 datum (piece of data).
...maintain referential integrity with keys.
...maintain data integrity with constraints and referential actions.
...maintain semantics with data types.

Your queries should:

...still return good data if the table data were to change (e.g. don't hard-code values if you don't have to).
...be as accurate as possible (e.g. avoid false positives wherever you can).

The data

librarian_fname	librarian_surname	shift	day	assignment
Jesse	Shera	1	Monday	main desk; catalogue
Anne Carroll	Moore	2	Monday	children's library
Beverly	Cleary	1	Monday	reference; catalogue
Marcel	Duchamp	1	Tuesday	main desk; reference
Audre	Lorde	2	Tuesday	main desk; catalogue
Carla	Hayden	1	Wednesday	main desk; catalogue
Ardelia	Lortz	2	Wednesday	reference
Mary	Ahern	1	Monday	main desk; reference
Dolly	Parton	1	Monday	children's library
Beverly	Cleary	1	Monday	reference; catalogue
Marcel	Duchamp	1	Tuesday	main desk; children's library
Carla	Hayden	2	Tuesday	main desk; catalogue
Audre	Lorde	1	Wednesday	main desk; catalogue

A couple reminders:

Two people can have the same name. People change their names without changing jobs. These are things that happens in life. In this data, that hasn't happened yet, but that doesn't mean it won't happen as more data is added.
The days in the above table are real days, meaning you can turn them into real dates, and work with them accordingly.
Go back and look at the example for 2nf. How can that be applied here?