Assignment 6: Creating a Schema #

For this assignment, you will design and create a database schema on your own. You will combine your knowledge from earlier in the course on database normalization and transactional tables, with new knowledge on creating tables and constraints.

You will create a database schema to store data for a library management system. It needs to track books, library members, and the borrowing process.

Your database will hold data for the following entities:

Books: Represents the individual items available for borrowing.

• Title - The title of the book.
• Author - The author(s) of the book, (A Book can have more than 1 author).
• ISBN - International Standard Book Number (13 digits).
• PublicationYear - Year the book was published.
• Publisher - The Publisher of the book.
• Genre - The Genre of the Book.
• TotalCopies - Total number of copies the library owns.
• AvailableCopies - Number of copies currently available for borrowing.
• ReplacementCost - Price to replace each copy of the book if not returned.

Members: Represents the individuals registered with the library who can borrow books.

• FirstName - Member’s first name.
• LastName - Member’s last name.
• Address - Member’s address.
• PhoneNumber - Member’s phone number (one number per member).
• Email - Member’s email address (one email per member).
• RegistrationDate - Date the member registered.

Every member needs to have a unique identifier. Remember: addresses, phone numbers, emails, and even names can change.

Loans: Represents the act of a member borrowing a specific book. This is a transactional table.

• … entries linking the borrowed book to the member borrowing the book
• LoanDate - Date the book was borrowed.
• DueDate - Expected return date.
• ReturnDate - Actual date the book was returned (NULL if not yet returned).

§1 Submission Instructions: #

The primary submission for this assignment will be a SQL file containing all commands to re-create your database schema (contains all CREATE TABLE and ALTER TABLE commands).

You may either create a .sql file manually, or use the pg_dump command as a starting point, then manually clean up the file to leave behind only the CREATE TABLE and ALTER TABLE statements necessary to re-create your database.

You will need to normalize the database and create intermediate tables as necessary to relate all data. You will have at least 6 or more tables.

You will need to create foreign key constraints as appropriate to ensure that data is consistent across tables, a minimum of 4 foreign key constraints across the database. Foreign key constraints need to have ON DELETE and/or ON UPDATE constraints.

Inserting data into the tables is not required, but all tables and relationships need to be valid.

You will create an Entity Relationship Diagram to depict your schema. This diagram should include:

• All attribute names for each table, noting Primary Keys and Foreign Keys
• Relationships between tables and cardinality (e.g., one-to-one, one-to-many, many-to-many).

Short write up:

1. What normal form is your database using? Justify your answer.
2. Provide a short explanation of the purpose of each table.
3. Explain the reasoning for the cardinality of each table relationship in your ERD.
4. Explain how your schema prevents data redundancy. Provide specific examples.

§2 Deliverables: #

1. .sql file containing all SQL statements to re-create your database tables.
2. ERD diagram of your database
3. Write-up

§3 Launching the Assignment #

This is essentially the same as every other assignment, a summarized overview:

Unzip the assignment files and cd to the directory. Then launch the postgres instance:

docker-compose up

In another terminal window, exec into the postgres container (use bash)

docker exec -it postgres-a6 bash

The postgres database will be empty. You will need to create all tables for this assignment.

Run psql to run an interactive SQL session. If you want to execute a .sql file, run psql -f /workdir/FILENAME.sql.

If you want to reset the postgres instance, open the terminal window that is running docker-compose up and type CTRL + C, then run docker-compose up again. The postgres instance is storing all data in memory, so it is reset when it is stopped.

§4 Testing your .sql file #

You should verify that your .sql file is valid and creates all required tables/relationships before submission.

You can “execute” a .sql file with the psql cli:

1. Create or copy your sql file to the workdir folder within your project directory, e.g. workdir/a6.sql.
2. Launch and exec into the postgres container, start bash instead of psql: docker exec -it postgres-a6 bash
3. Execute your sql file, the file will be mapped into the container at /workdir/file-name.sql: psql -f /workdir/a6.sql
4. Verify your .sql file worked: psql then list tables \dt and run SQL SELECT * FROM ...
5. Restart your postgres instance to wipe it and start fresh.