COMP2400/6240 - Relational Databases Assignment 2: Enhanced Entity Relationship, Relational Algebra and SQL
COMP2400/6240 - Relational Databases
Question 1 3 Marks
Band-Aid is a booking agency that represents local Canberra bands by booking their shows nationally. The employees of Band-Aid are classified into two non-overlapping categories, agents and administrators. An agent represents one or more bands and can be contacted via their personal office phone or work email. Each administrator belongs to a department which offers administrative support to all agents of the agency. Note that the internal relationship between agents and administrators isn’t something
Band-Aid cares to track. Administrative departments include accounts, contracts, and promotions. Each Band-Aid employee can be uniquely identified by their employee number. Band-Aid also stores the name, phone, and email of the best point of contact for the band. Each band is represented by a particular agent and Band-Aid likes to record the date that representation began. If a band has just joined the agency (or they aren’t very popular!) then the agency may never have booked a show for the band.
Band-Aid maintains a list of venues. Each record includes the venue’s name, a distinct address, phone number, and the venue capacity. Band-Aid likes to keep a comprehensive list of venues, including new venues or venues where they’re yet to book a show.
The primary purpose of Band-Aid is to book shows. Each show is hosted by a particular venue. One or more of the bands Band-Aid represents would perform at the show. When Band-Aid books a show, they record the date and ticket price, and once the show is finalised, the number of tickets sold. Each show is assigned a unique show ID.
Your task is to design an Enhanced Entity Relationship (EER) diagram that captures the above require- ments. It should include entities, relationships, attributes, and constraints where appropriate. You may make more assumptions, if necessary, but any assumptions should be noted. If there are any requirements that cannot be captured in an EER-diagram, then they should also be identified.
Question 2
Consider the relation schema R = {A, B, C, D, E, F, G} and the following set Σ of FDs:
- C → BG
- AE → F
- CF → EA
- A → DF
5 Marks
- 2.1 What are the candidate keys of R? Justify your answer (i.e., include the main steps used for finding the candidate keys). (1 Mark)
- 2.2 Find a minimal cover of Σ and include the main steps used for finding a minimal cover. (2 Marks)
- 2.3 Demonstrate why R, given Σ, does not satisfy 3NF, and then identify a 3NF decomposition for R. You need to include the main steps used for identifying the 3NF decomposition. (2 Marks)
Question 3 3 Marks
Consider the relation schema Booking={Airline, Date, Destination, FlightNo, PassengerName, Origin, PassportNo, Seat, Terminal} and the following set Σ of FDs:
• Airline → Terminal
• FlightNo → Airline, Origin, Destination • PassportNo → PassengerName
• FlightNo, PassportNo, Date → Seat
• FlightNo, Date, Seat → PassportNo
Which, for brevity, we can alias the attributes and restate as Booking={A, D, E, F, N, O, P, S, T} and Σ: •A→T
• F → AOE •P→N
- FPD → S
- FDS → P
3.1 Is the above relation schema Booking in BCNF? If not, identify a BCNF decomposition for Booking and check if your BCNF decomposition is dependency-preserving. You need to include the main steps
used for identifying the BCNF decomposition. 3.2 Consider another set Σ1 of FDs on Bookings:
• Airline → Terminal
• FlightNo → Airline, Origin, Destination
• PassportNo → PassengerName
• FlightNo, PassportNo, Date → Terminal, Seat
• FlightNo, Date, Seat → PassportNo, PassengerName
Which can be similarly aliased as: •A→T
• F → AOE •P→N
• FPD→TS
• FDS→PN
Are Σ and Σ1 equivalent? Justify your answer. (1 Mark)
Question 4 4 Marks
The following table contains the relational algebra operators covered in our course. You should only use these operators to answer the following questions.
σφR
πA1 ,...,An R
ρR′ (A1,...,An)R ρR′ R
ρ(A1 ,...,An ) R R1∪R2 R1∩R2 R1−R2 R1×R2
R1 ▷◁φ R2 R1 ▷◁ R2 φ1 ? φ2 φ1 ? φ2
Selection by condition φ
Projection onto the set of attributes {A1 . . . , An} ′
RenamingtherelationnametoR andattributenamestoA1,...,An
Renaming the relation name to R
Renaming the attribute names to A1 , . . . , An
Union of two relations R1 and R2
Intersection of two relations R1 and R2
Difference of two relations R1 and R2
Cartesian product of two relations R1 and R2
Join of two relations R1 and R2 with the join condition φ Natural join of two relations R1 and R2
condition φ1 AND condition φ2
condition φ1 OR condition φ2
Consider a relational database schema S with the following relation schemas:
- Movie(title, production year, genre) with the primary key {title, production year}
- Person(id, first name, last name, year born) with the primary key {id}
- Director(id, title, production year) with the primary key {title, production year} and with the foreign keys [title, production year] ⊆ Movie[title, production year] and [id] ⊆ Person[id]
- Writer(id, title, production year, credits) with primary key {id, title, production year} and with the foreign keys [title, production year] ⊆ Movie[title, production year] and [id] ⊆ Person[id]
- 4.1 Answer the following questions using relational algebra queries only using the operators in the above table. (2 Marks)
[a] List the ids, first and last names of directors who have never written any movie(s). (1 Mark)
[b] A person has worked on a movie if this person is a director, a writer, or both a director and writer, of this movie. List the ids of persons who have worked on at least two distinct movies. (1 Mark)
- 4.2 Optimise the following relational algebra query. Your marks will depend on how well you optimise the query in your solution. Additionally, draw the query trees of the query before and after your optimisation. (2 Marks)
πDirector.idσ(Director.id=Person.id) ?(year born>production year−40)(Director × Person) −πidσ(year born<1970) ?(production year>2010)(Writer ▷◁ Person)
