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