Page 1 of 3

CSC 370

Database Systems

Assignment 1

Important

• A clear, handwritten submission is acceptable. Ensure your answers are clear

and legible.

• You must have a cover sheet at the front of your submission (with

course, assignment #, your name, your student number).

• All sheets must be stapled together. The teaching team will not be responsible

for missing pages or incomplete answers associated with unstapled

submissions.

• Please show all work! (Some questions remind you to do so.)

• The assignment is in three parts, with total marks of 100.

• Submit your work by placing it into the ECS second-floor dropbox for

CSC 370 before the due date/time. Late submissions will not be accepted.

Part A (50 marks)

Consider the following database schema for relations representing airline-flight

data:

• Flights(fno: integer, cityfrom: string, cityto: string,

distance: integer, departs: datetime, arrives: datetime)

• Airplane(aid: integer, manufacturer: string, code: integer,

range: integer)

• Certification(sid: integer, aid: integer, type: string,

ispilot: boolean)

• Staff(sid: integer, sname: string, salary: integer)

The Staff relation describes pilots, mechanics, and other kinds of staff as well;

every pilot is certified to fly some aircraft, and every mechanic is certified to

maintain some aircraft. (These certifications also imply that individual pilots and

mechanics are qualified for their positions). Only pilots and mechanics have

certifications.

Write the following ten queries using the relational algebra (RA) with operations as

described in lectures. Note that some of these queries cannot expressed in the RA,

and if this is the case for a query, informally explain why this is so (i.e., a formal

proof of inexpressibility is not necessary).

Page 2 of 3

1. Find the sids of pilots certified for some Bombardier aircraft (i.e., an airplane

manufactured by Bombardier).

2. Find the names of mechanics certified to maintain some Embraer aircraft.

3. Find the aids of all airplanes that can be used on non-stop flights from

Toronto (Canada) to Seoul (South Korea).

4. Identify the flights that can be piloted by every pilot whose salary is more

than $150,000.

5. Find the names of mechanics who can maintain airplanes with a range less

than 3000 miles but are not certified to work on any Airbus airplane.

6. Find the sids of staff who make the highest salary.

7. Find the sids of staff who make the second-highest salary.

8. Find the sids of staff who are certified for the largest number of aircraft

(either pilots or mechanics).

9. Find the sids of staff who are certified to fly exactly three aircraft.

10. Find the total amount paid to staff as salaries.

Part B (20 marks)

Assuming a relational-algebra expression exists for them, write the expression-tree

equivalents of your answer to:

1. Query 4 of Part A.

2. Query 5 of Part A.

Page 3 of 3

Part C (30 marks)

Consider a relation with the schema �(�,�, �, �) with functional dependencies

�� → �, �� → �, �� → �, and �� → �. Answer each part below, and show all work

(that is, show work towards computing closures).

1. What are all the non-trivial FDs that follow from the functional

dependencies? Ensure all of your FDs have a single attribute on the righthand side.

2. What are all the keys of �?

3. What are all the superkeys for � that are not keys?