Assignment 1: Testing PostgreSQL The goal of this assignment is get familiarity with PostgreSQL and SQL. Consider the following relation schemas for a database that maintains sailors, boats, and reservations of boats by sailors. Sailor(SId: INTEGER, Sname: VARCHAR(20), Rating: INTEGER, Age: INTEGER) Boat(BId: INTEGER, Bname VARCHAR(15), Color VARCHAR(15)) Reserves(SId: INTEGER, BId: Integer, Day: VARCHAR(10)) You should assume that SId in Reserves is a foreign key that references the primary key SId in Sailor, and that BId in Reserves is a foreign key that references the primary key BId in Boat. Note the attached text files sailor.txt, boat.txt, and reserves.txt that contain the relation instances for the Sailor, Boat, and Reserves relations. 1. Create a database in PostgreSQL that stores these relations. 2. Write SQL statements for the following queries: (a) Find the bid’s and bnames of red boats. (b) Find the names of sailors who have reserved boat 103. (c) Find the names of boats reserved by sailors who have a rating below 8. (d) Find the names of sailors who have reserved a red or a green boat. (e) Find the names of sailors who have reserved a blue and a green boat. (f) Find the names of sailors who have reserved at least two boats. (g) Find the Sid’s of sailors who have not reserved a boat. Submit your solutions in a file called Assignment1.sql that contains a series of SQL statements for these problems. 1
