postgre lab
Budget: $10 – $30 USD
lab should be written sequentially in an .SQL text file named lab1.sql
In this lab, we will create tables for an equipment checkout system, typical to what a university help desk would offer. The user comes to the help desk to check out equipment. The status and inventory tables would be pre-populated by the help desk staff. When the user checks out a piece of equipment, a record is entered into the transactions table.
create a new database named lab1 to use when completing the following steps. When you submit your code to GitHub, this step is not necessary because the Drone continuous integration server does it automatically.
2. Create a new table named users
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• first_name
• last_name
• email (not null)
• password (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
2. Create a new table named status
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• Description (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
3. Create a new table named inventory
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• status_id (foreign key constraint by referencing the status table)
• description (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
4. Create a new table named transactions
It should have the following columns:
• SERIAL PRIMARY KEY id
• user_id (foreign key constraint by referencing the user table)
• inventory_id (foreign key constraint by referencing the inventory table)
• checkout_time (of type timestamp) | (not null)
• scheduled_checkin_time (of type timestamp)
• actual_checkin_time (of type timestamp)
• created_at (of type timestamp)
• updated_at (of type timestamp)
Note: timestamps are a string like 2012-02-15 17:30
5. Insert 5 users into the users table. The fields are self-explanatory.
6. Insert 5 records into the status table. The description field should be: Available, Checked out, Overdue, Unavailable, Under Repair. The other fields are self-explanatory.
7. Insert 5 records into the inventory table. The description field should be: Laptop1, Laptop2, Webcam1, TV1, Microphone1. The other fields are self-explanatory.
8. Insert 3 records into the transactions table. Then update the status of these three inventory items in the inventory table to Checked out. The fields are self-explanatory, keeping in mind the foreign key constraints. Two of the transactions should be for the user in the users table with id = 1. Two of the transactions should have a scheduled_checkin_time after July 31, 2020.
9. Alter the users table to add a column for signed_agreement (Boolean column that defaults to false).
10. Write a query that returns a list of all the equipment and its scheduled_checkin_time that is checked out ordered by scheduled_checkin_time in descending order.
11. Write a query that returns a list of all equipment due after July 31, 2020.
12. Write a query that returns a count of the number of items with a status of Checked out by user_id 1.
NOTE: You will need to look in the documentation for PostgreSQL 9.4 (https://www.postgresql.org/docs/9.4/static/plpgsql.html) to complete the tasks in the labs that are not covered in the examples.
In this lab, we will create tables for an equipment checkout system, typical to what a university help desk would offer. The user comes to the help desk to check out equipment. The status and inventory tables would be pre-populated by the help desk staff. When the user checks out a piece of equipment, a record is entered into the transactions table.
create a new database named lab1 to use when completing the following steps. When you submit your code to GitHub, this step is not necessary because the Drone continuous integration server does it automatically.
2. Create a new table named users
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• first_name
• last_name
• email (not null)
• password (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
2. Create a new table named status
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• Description (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
3. Create a new table named inventory
It should have the following columns:
• SERIAL PRIMARY KEY integer id
• status_id (foreign key constraint by referencing the status table)
• description (not null)
• created_at (of type timestamp)
• updated_at (of type timestamp)
4. Create a new table named transactions
It should have the following columns:
• SERIAL PRIMARY KEY id
• user_id (foreign key constraint by referencing the user table)
• inventory_id (foreign key constraint by referencing the inventory table)
• checkout_time (of type timestamp) | (not null)
• scheduled_checkin_time (of type timestamp)
• actual_checkin_time (of type timestamp)
• created_at (of type timestamp)
• updated_at (of type timestamp)
Note: timestamps are a string like 2012-02-15 17:30
5. Insert 5 users into the users table. The fields are self-explanatory.
6. Insert 5 records into the status table. The description field should be: Available, Checked out, Overdue, Unavailable, Under Repair. The other fields are self-explanatory.
7. Insert 5 records into the inventory table. The description field should be: Laptop1, Laptop2, Webcam1, TV1, Microphone1. The other fields are self-explanatory.
8. Insert 3 records into the transactions table. Then update the status of these three inventory items in the inventory table to Checked out. The fields are self-explanatory, keeping in mind the foreign key constraints. Two of the transactions should be for the user in the users table with id = 1. Two of the transactions should have a scheduled_checkin_time after July 31, 2020.
9. Alter the users table to add a column for signed_agreement (Boolean column that defaults to false).
10. Write a query that returns a list of all the equipment and its scheduled_checkin_time that is checked out ordered by scheduled_checkin_time in descending order.
11. Write a query that returns a list of all equipment due after July 31, 2020.
12. Write a query that returns a count of the number of items with a status of Checked out by user_id 1.
NOTE: You will need to look in the documentation for PostgreSQL 9.4 (https://www.postgresql.org/docs/9.4/static/plpgsql.html) to complete the tasks in the labs that are not covered in the examples.