CSC 370 Database Systems Assignment 1


5/5 - (2 votes)

Page 1 of 3
CSC 370
Database Systems
Assignment 1

• 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
• 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
• 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
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?

PlaceholderCSC 370 Database Systems Assignment 1
Open chat
Need help?
Can we help?