Assignment 1 Solution

Description

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).

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.

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 right-hand side.

2. What are all the keys of ?

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

Page 3 of 3