## Description

1 Introduction

The goals for this assignment are to

1. become familiar with the PostgreSQL system1

;

2. create a relational database and populate it with data;

3. examine the side-effects on the state of the database caused by inserts and

deletes in the presence or absence of primary and foreign key constraints;

4. formulate some queries in SQL and evaluate them in PostgreSQL; and

5. translate TRC queries to SQL and formulate queries and constraints in

TRC.2

To turn in your assignment, you will need to upload to Canvas a single file

with name assignment1.sql which contains the necessary SQL statements that

solve the problems in this assignment. The assignment1.sql file must be such

that the AI’s can run it in their PostgreSQL environment. In addition, you

will need to upload a separate assignment1.txt file that contains the results of

running your queries. We have posted the exact requirements and an example

∗This assignment covers lectures 1 through 4

1To solve this assignment, you will need to download and install PostgreSQL (version 12

or higher) on your computer.

2To solve problems related to TRC, follow the syntax and semantics described in the

TRC SQL.pdf document in the module Tuple Relational Calculus and SQL (lecture 4). That

document contains multiple examples of TRC queries and constraints and how they can be

translated to SQL.

1

for uploading your solution files. (See the module Instructions for turning

in assignments.) Finally, you will need to upload an assignment1.pdf file

that contains the solutions for problems related to TRC.3

For the problems in this assignment we will use the following database

schema:4

Person(pid, pname, city)

Company(cname, headquarter)

Skill(skill)

worksFor(pid, cname, salary)

companyLocation(cname, city)

personSkill(pid, skill)

hasManager(eid, mid)

In this database we maintain a set of persons (Person), a set of companies

(Company), and a set of (job) skills (Skill). The pname attribute in Person is

the name of the person. The city attribute in Person specifies the city in which

the person lives. The cname attribute in Company is the name of the company.

The headquarter attribute in Company is the name of the city wherein the

company has its headquarter. The skill attribute in Skill is the name of a

(job) skill.

A person can work for at most one company. This information is maintained

in the worksFor relation. (We permit that a person does not work for any

company.) The salary attribute in worksFor specifies the salary made by the

person.

The city attribute in companyLocation indicates a city in which the company is located. (Companies may be located in multiple cities.)

A person can have multiple job skills. This information is maintained in the

personSkill relation. A job skill can be the job skill of multiple persons. (A

person may not have any job skills, and a job skill may have no persons with

that skill.)

A pair (e, m) in hasManager indicates that person e has person m as one of

his or her managers. We permit that an employee has multiple managers and

that a manager may manage multiple employees. (It is possible that an employee

has no manager and that an employee is not a manager.) We further require

that an employee and his or her managers must work for the same company.

The domain for the attributes pid, salary, eid, and mid is integer. The

domain for all other attributes is text.

3

It is strongly recommended that you use Latex to write TRC formulas and queries. For a good way to learn about Latex, look at

https://www.overleaf.com/learn/latex/Free online introduction to LaTeX (part 1). You

can also inspect the Latex source code for this assignment as well as the document

TRC SQL.tex provided in Module 4.

4The primary key, which may consist of one or more attributes, of each of these relations

is underlined.

2

We assume the following foreign key constraints:

• pid is a foreign key in worksFor referencing the primary key pid in

Person;

• cname is a foreign key in worksFor referencing the primary key cname in

Company;

• cname is a foreign key in companyLocation referencing the primary key

cname in Company;

• pid is a foreign key in personSkill referencing the primary key pid in

Person;

• skill is a foreign key in personSkill referencing the primary key skill

in Skill;

• eid is a foreign key in hasManager referencing the primary key pid in

Person; and

• mid is a foreign key in hasManager referencing the primary key pid in

Person;

The file data.sql contains the data supplied for this assignment.

3

2 Database creation and impact of constraints

on insert and delete statements.

Create a database in PostgreSQL that stores the data provided in the data.sql

file. Make sure to specify primary and foreign keys.

1. Provide 4 conceptually different examples that illustrate how the presence

or absence of primary and foreign keys affect insert and deletes in these

relations. To solve this problem, you will need to experiment with the

relation schemas and instances for this assignment. For example, you

should consider altering primary keys and foreign key constraints and then

consider various sequences of insert and delete operations. You may need

to change some of the relation instances to observe the desired effects.

Certain inserts and deletes should succeed but other should generate error

conditions. (Consider the lecture notes about keys, foreign keys, and

inserts and deletes as a guide to solve this problem.)

4

3 Formulating queries in SQL

For this assignment, you are required to use tuple variables in your SQL statements. For example, in formulating the query “Find the pid and pname of each

person who lives in Bloomington” you should write the query

SELECT p.pid, p.pname

FROM Person p

WHERE p.city = ‘Bloomington’

rather than

SELECT pid, pname

FROM Person

WHERE city = ‘Bloomington’

Write SQL statements for the following queries. 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.

You can not use the SQL JOIN operations or SQL aggregate functions such

as COUNT, SUM, MAX, MIN, etc in your solutions.

2. Find the pid, pname of each person who (a) lives in Bloomington, (b)

works for a company where he or she earn a salary that is higher than

30000, and (c) has at least one manager.

3. Find the pairs (c1, c2) of names of companies whose headquarters are located in the same city.

4. Find the pid and pname of each person who lives in a city that is different

than each city in which his or her managers live. (Persons who have no

manager should not be included in the answer.)

5. Find each skill that is the skill of at most 2 persons.

6. Find the pid, pname, and salary of each employee who has at least two

managers such that these managers have a common job skill but provided

that it is not the ‘Networks’ skill.

7. Find the cname of each company that not only employs persons who live

in MountainView. (In other words, there exists at least one employee of

such a company who does not live in MountainView.)

8. For each company, list its name along with the highest salary made by

employees who work for it.

9. Find the pid and pname of each employee who has a salary that is higher

than the salary of each of his or her managers. (Employees who have no

manager should not be included.)

5

4 Translating TRC queries to SQL

Consider the following queries formulated in TRC. Translate each of these

queries to an equivalent SQL query.5

You should note that this translating, modulo the handling of universal

quantifiers, is almost a syntactic rewrite of the way in which the queries are

formulated in TRC. This underscores the close correspondence between TRC

and SQL.

The SQL queries should be included in the assignment1.sql file and their

outputs should be reported in the assignment.txt file.

10.

{p.pid, p.pname, w.cname, w.salary | P erson(p) ∧ worksF or(w) ∧ p.pid = w.pid

p.city = ‘Bloomington’ ∧ 40000 ≤ w.salary ∧ w.cname 6= ‘Apple’}.

11.

{p.pid, p.pname | P erson(p)∧

∃c∃w(Company(c) ∧ worksF or(w) ∧ c.cname = w.cname ∧ p.pid = w.pid ∧ c.headquarter = ‘LosGatos’∧

∃hm∃m(hasManager(hm) ∧ P erson(m) ∧ hm.eid = p.pid ∧ hm.mid = m.pid ∧ m.city 6= ‘LosGatos))}.

In abbreviated form,

{p.pid, p.pname | P erson(p)∧

∃c ∈ Company ∃w ∈ worksF or(c.cname = w.cname ∧ p.pid = w.pid ∧ c.headquarter = ‘LosGatos’∧

∃hm ∈ hasManager ∃m ∈ P erson(hm.eid = p.pid ∧ hm.mid = m.pid ∧ m.city 6= ‘LosGatos))}.

12.

{s.skill | Skill(s) ∧ ¬(∃p∃ps P erson(p) ∧ personSkill(ps) ∧ p.pid = ps.pid∧

ps.skill = s.skill ∧ p.city = ‘Bloomington’)}.

In abbreviated form,

{s.skill | Skill(s) ∧ ¬(∃p ∈ P erson ∃ps ∈ personSkill(p.pid = ps.pid∧

ps.skill = s.skill ∧ p.city = ‘Bloomington’)}.

13.

{m.pid, m.pname | P erson(m)∧

∀hm((hasManager(hm) ∧ hm.mid = m.pid) → ∃e(P erson(e) ∧ hm.eid = e.pid ∧ e.city = m.city))}

In abbreviated form,

{m.pid, m.pname | P erson(m)∧

∀hm ∈ hasManager(hm.mid = m.pid → ∃e ∈ P erson(hm.eid = e.pid ∧ e.city = m.city))}

5You can not use SQL JOIN operations or aggregate functions.

6

5 Formulating queries in the Tuple Relational

Calculus

Formulate each of the queries in the even-numbered problems (i.e., problems 2,

4, 6, and 8) in Section 3 as TRC queries.

The solutions of these problems should be included in the assignment1.pdf

file.

14. (Problem 2) Find the pid, pname of each person who (a) lives in Bloomington, (b) works for a company where he or she earn a salary that is

higher than 30000, and (c) has at least one manager.

15. (Problem 4) Find the pid and pname of each person who lives in a city

that is different than each city in which his or her managers live. (Persons

who have no manager should not be included in the answer.)

16. (Problem 6) Find the pid, pname, and salary of each employee who has

at least two managers such that these managers have a common job skill

but provided that it is not the ‘Networks’ skill.

17. (Problem 8) For each company, list its name along with the highest salary

made by employees who work for it.

7

6 Formulating constraints in the Tuple Relational

Calculus

Formulate the following constraints in TRC and as boolean SQL queries.

The TRC solutions of these problems should be included in the assignment1.pdf

file and the SQL solutions should be included in the assignment1.sql file.

Here is an example of what is expected for your answers.

Example 1 Consider the constraint “ Each skill is the skill of a person.” In

TRC, this constraint can be formulated as follows:

∀s Skill(s) → ∃ps (personSkill(ps) ∧ ps.skill = s.skill)

or, alternatively

¬∃s(Skill(s) ∧ ¬∃ps(personSkill(ps) ∧ ps.skill = s.skill)).

This constraint can be specified using the following boolean SQL query.

select not exists (select 1

from Skill s

where not exists (select 1

from personSkill ps

where ps.skill = s.skill));

18. Each person works for a company and has at least two job skills.

19. Some person has a salary that is strictly higher than the salary of each of

his or her managers.

20. Each employee and his or her managers work for the same company.

8