[Solved] CSCI4380 Lab 4

In this lab, you will create SQL files to create tables in a schema, add data to those tables, and run a few simple queries against that data.

You may work in teams of up to three students, but make sure each of you understands the process.

## Summary

Consider a very basic schema for a public library, which contains the following relations:

`Book(barcode, isbn, title, author)` barcode is the key barcode is a number between 0 and 9,999,999,999 isbn should be considered a string between 10 and 21 characters in length title and author are both strings up to 127 characters in length `Patron(card_number, branch, name, phone_number, email)` `(card_number, branch)` is the key card_number is a number between 0 and 1,000,000 branch is a string up to 63 characters in length name is a string up to 127 characters in length phone number is a 10-digit number email is a string up to 255 characters in length `Checkout(barcode, card_number, branch, checkout_time, due_date)` Has no defined key barcode refers to the Book barcode card_number and branch refer to the Patron checkout_time is the exact checkout time (regardless of where the server is located) due_date is the day the book is due

## Setup

Run the following as a superuser (usually `postgres`) on your local postgres installation from Lab 1:

`postgresqlDROP DATABASE IF EXISTS lab_db;CREATE DATABASE lab_db;

DROP USER IF EXISTS lab_owner;CREATE USER lab_owner WITH PASSWORD insecure-password;

GRANT ALL PRIVILEGES ON DATABASE lab_db TO lab_owner;ALTER USER lab_owner SET search_path = testing;

`

Then you should be able to connect to your instance as the `lab_owner` user. Some variant of:

`psql -U lab_owner lab_db`

Once youve connected as the `lab_owner` user, run `CREATE SCHEMA testing;`, or you can run the provided `cleanup.sql` script.

You can use the cleanup script to reset the database to a clean state.

And as in Lab 1, you can run an arbitrary SQL file from the command line (from your host OS, not from the psql shell):

`psql -U lab_owner lab_db < file.sql`

You can clean up your work at any time by dropping the database and re-creating it by re-running the statements above. (Feel free to put them into a cleanup file you can run as a superuser, if you want.)

## Assignment

You will create four SQL files: `schema.sql` `data.sql` `1.sql` `2.sql`

### `schema.sql`

This file needs to contain three `CREATE TABLE` statements, one for each of the relations specified above. Make sure you choose appropriate data types and define the keys properly (where specified).

For the `Checkout` relation, dont worry about creating referential integrity constraints (we havent covered that in class yet).

### `data.sql`

Create statements to insert data representing the following:

Library patron Bill Smith, card number 256, Troy branch, phone number (518) 555-1234, [email protected] Library patron Annie Rivera, card number 562, Troy branch, phone number (518) 555-7384, [email protected] Book The Lord of the Rings by J. R. R. Tolkien ISBN `978-0618640157`, barcode 5837239683 Book Database Systems: The Complete Book by Hector Garcia-Molina ISBN `0131873253`, barcode 234 Book Enders Game by Orson Scott Card ISBN `978-1608872770` barcode 488329 Annie Rivera checks out Lord of the Rings at 2:35pm EST on February 15, 2020, due March 14, 2020 Bill Smith checked out Lord of the Rings at 10:01am EST on December 1, 2019, due December 29, 2019 Bill Smith checked out Database Systems at 9:37am EST on April 2, 2004, due April 30, 2004

### Query files

Write two sql queries:

`1.sql`: Provide email, title, due_date for all checkouts, ordered by due_date, newest first `2.sql`: Provide title, isbn, name for all books, where name is anyone whos checked out the book. Make sure you include books that havent been checked out, ordered by title

Each query should be in its own file, as defined above.