## Description

The goal of this assignment is to become familiar with the PostgreSQL system, to create a relational database, and to write and evaluate some simple SQL

statements and queries in that system.

You need to upload a single file with name assignment1.sql file to Canvas

which contains the necessary SQL statements that solve the problems in this

assignment. The assignment1.sql file must be so that the AI’s can run it in

their PostgreSQL shell. (We will post a sample .sql file that gives the template

for your ssignment1.sql file.)

Before you can solve this assignment, you will need download PostgreSQL

(version 10) and install it on your computer.

Consider the following relation schemas for a database that maintains sailors,

boats, and reservations of boats by sailors.

Sailor(sid integer, sname text, rating integer)

Boat(bid integer, bname text, color text)

Reserves(sid integer, bid integer, day text)

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 files sailor.sql, boat.sql, and reserves.sql that contain the

relation instances for the Sailor, Boat, and Reserves relations that are supplied

with this assignment.

1 Database creation and impact of constraints

on INSERT and DELETE statements.

1. Create a database with name assignment1 in PostgreSQL that stores

these relations. Make sure to specify primary and foreign keys. Then

write SQL queries that return each of the relation instances Sailor, Boat,

and Reserves.

2. Provide 6 examples that illustrate how the presence or absence of primary

and foreign keys affects insert and deletes in these relations. To solve

this problem, you will need to experiment with the Sailor, Boat, and

Reserves relation schemas and instances. For example, you should consider altering primary keys and foreign key constraints and then consider

1

various sequences of insert and delete operations. You may also need to

change some of the relation instances. Certain inserts and deletes should

succeed but other should create error conditions. (Consider the lecture

notes about keys, foreign keys, and inserts and deletes as a guide to solve

this problem.)

2 Formulating queries in SQL

Write SQL statements for the following queries. For this assignment, make

sure to always use tuple variables in your SQL statements. For example, in

formulating the query “Find the name of each boat” you should write the query

SELECT b.bname

FROM Boat b

instead of

SELECT bname

FROM Boat

Make sure that each of your queries returns a set but not a bag. In other

words, make appropriate use of the DISTINCT clause where necessary.

1. Find the sid and rating of each sailor.

2. Find the sid, name, and rating of each sailor whose rating is in the range

[2, 11] but not in the range [8, 10].

3. Find the bid, name, and color of each non-red boat that was reserved by

some sailor whose rating is more than 7.

4. Find the bid and name of each boat that was reserved by a sailor on a

weekend day but that was not reserved by a sailor on a Tuesday.

5. Find the sid of each sailor who reserved both a red boat and a green boat.

6. Find the sid and name of each each sailor who reserved at least two

different boats. (You should write this query without using the COUNT

aggregate function.)

7. Find the pairs of sids (s1, s2) of different sailors who both reserved a same

boat.

8. Find the sid of each sailor who did not reserve any boats on a Monday

or on a Tuesday.

9. Find the pairs (s, b) such that the sailor with sid s reserved the boat with

bid b, provided that the sailor s has a rating greater than 6 and the color

of boat b is not red.

2

10. Find the bid of each boat that where reserved by just one sailor. (You

should write this query without using the COUNT aggregate function.)

11. Find the sid of each sailor who reserved fewer than 3 boats. (You should

write this query without using the COUNT aggregate function.)

3 Formulating queries in Predicate Logic

For each query in Section ??, specify an expression in the Predicate Logic (also

called Relational Calculus). This will illustrate how closely this logic is associated with SQL.

To learn more about this logic, you can consult the book Database Management Systems by Ramakrishnan and Gehrke which you can find online.

You should upload your answers for these problems in a separate pdf file

named Assignment1.pdf.

Assume that associated with each of the relation Sailor, Boat, and Reserves,

there is a predicate as follows:

Relation Predicate

Sailor Sailor(x, y, z)

Boat Boat(x, y, z)

Sailor Sailor(x, y, z)

As an example consider the query “Find the bid and name of each boat

that was reserved by fewer than 2 sailors.” This query can be formulated in the

Predicate Logic with domain variables as follows1

:

{(b, n)| ∃c Boat(b, n, c)∧¬(∃s1∃d1∃s2∃d2(Reserves(s1, b, d1)∧Reserves(s2, b, d2)∧s1 6= s2)}.

And alternative formulation using a Predicate Logic with tuple variables is

as follows and is more inline with the SQL formulation:2

:

{(b.bid, b.bname)| Boat(b)∧

¬(∃r1∃r2(Reserves(r1) ∧ Reserves(r2) ∧ r1.bid = b.bid ∧ r2.bid = b.bid ∧ r1.sid 6= r2.sid)}.

1This logic is also called the Domain Relational Calculus.

2This logic is also called the Tuple Relational Calculus.

3