COMP2400/6240 Relational Databases Semester 2, 2024 Assignment 2 (Database Theory)
COMP2400/6240 – Relational Databases Semester 2, 2024
Assignment 2 (Database Theory)
Instructions
-
This assignment must be done individually.
Do not share your solutions, or partial solutions, with anyone. Do no post any idea/partial solution/result related to the assignment on the course discussion forum, or anywhere else where it can be read by other students.
You may of course ask clarification questions, but make sure you phrase your questions so that they do not give away what you think the answer or solution is. If you are not sure if a question is ok to post to the forum then ask your tutor during a lab or come to a drop-in session instead.
You must not use generative tools (ChatGPT or similar) to create your answers to the assignment. Your answers should be your answers. Reference all resources that you have used in completing this assignment.
-
You must submit your solutions through Wattle before the assignment deadline. There will be an Assignment activity on the course Wattle page where you should upload your solution file. You can submit more than once, but we can only see, and will only mark, the last file that you submit.
Late submissions will not be accepted. Submissions made outside the Assignment activity on Wattle (for example, files sent by email) will not be accepted under any circumstance. You will be marked on what you have submitted at the time of the deadline.
-
This assignment will count for 17% of the final course mark. Marks are reserved for detailing the process of deriving your solution, not just for the result. You should justify your answers and, include all essential ideas and steps to derive your solutions.
-
You should try your best to type the solutions. The scanned images of handwritten texts and equations can be unreadable for marking. As for the EER diagram, you are recommended to export a JPEG file from TerraER and include it in the PDF file.
-
Plagiarism, collusion, and the use of disallowed tools will attract academic penalties in accordance with the ANU guidelines. Every student in this course is expected to be able to explain and defend any submitted assess- ment item. The course conveners can conduct or initiate an additional interview about any submitted assessment item for any student. If there is a significant discrepancy between the two forms of assessment, this will be seen as a case of potential academic misconduct.
-
If you find yourself in an unforeseeable situation beyond your control that you believe significantly affects your ability to complete the assessment on time, you can submit an ECA through the system
Question 1 6 Marks
FlyHigh is an airline company that specializes in providing on-demand charter flight services. The company operates a fleet of ten aircraft, catering to a diverse range of customers who require flexible and personalized flight options. To effectively manage its operations, FlyHigh maintains records for each customer, including their unique customer number, full name, contact phone number, and residential or business address.
To ensure the reliability and safety of its fleet, FlyHigh has established five dedicated service teams. Each service team is composed of three service engineers and one administrative staff member. The service engineers are responsible for performing all necessary mechanical services on the aircraft, while the administrative staff handles the coordination and docu- mentation of these services. FlyHigh’s service teams can perform mechanical service on any of the aircraft in the fleet. To maintain optimal performance and compliance with safety standards, each aircraft undergoes a thorough mechanical service every six months.
FlyHigh classifies its employees into four categories: pilots, service engineers, flight atten- dants, and administrative staff. For each employee, the company maintains records that include their employee number, first name, last name, and date of birth. In addition to this general information, specific data is recorded for pilots, including their license number and category ratings.
The company also tracks detailed information about its aircraft. This includes the aircraft’s registration number, model code, number of seats, mileage, engine strength, and manufac- turer. For each aircraft’s mechanical service, FlyHigh records the type of service performed, the date of the service, a description of the service, and any relevant comments.
When it comes to flight operations, an aircraft can be chartered by only one customer for a specific trip. Each flight requires a pilot and a co-pilot to operate the aircraft. All pilots must hold either a Commercial Pilot License (CPL) or an Air Transport Pilot License (ATPL). Furthermore, a pilot is only allowed to fly aircraft that fall within their specific category ratings, ensuring they are qualified and authorized to operate those types of aircraft. The company maintains records for each charter trip. The data held on each charter trip is the date of trip, start time, customer number, and aircraft registration number.
Your tasks are as follows:
-
1.1 Design an Enhanced Entity-Relationship (EER) diagram for the database application described above, using the notations from the lecture slides. Your diagram should include entities, relationships, attributes, and constraints where applicable. You should also document any assumptions you make while creating the EER diagram.
(4.5 Mark)
-
1.2 Identify any requirements in the database application described above that cannot be represented in an EER diagram.
(1.5 Mark)
2
Question 2 5 Marks
A job recruitment agency requested an information system to manage candidates, employers, and job applications. In response, the IT department developed a database schema that includes the following relation schema: Job Application.
Job Application
- Candidate Name
- Candidate DoB
- Candidate Email
- Candidate Education
- Job ID
- Position
- Type
- Salary
- Location
- Closing Date
- Employer ID
- Employer Name
- Employer Address
- Application Date
- StatusThe IT department identified the data requirements from the job recruitment agency using
the following functional dependencies:
(FD1) {Candidate Name, Candidate DoB} → {Candidate Email, Candidate Education};
(FD2) {Job ID} → {Position,Type,Closing Date, Employer ID};
(FD3) {P osition, T ype, Location, Employer ID, Employer Address} → {Salary};
(FD4) {Employer ID} → {Employer Name, Employer Addresss};
(FD5) {Employer Address} → {Location};
(FD6) {Candidate Name, Candidate DoB, Job ID} → {Application Date, Status}.
Your tasks are as follows:
-
2.1 Based on the functional dependencies (FD1-FD6) provided, determine whether the relation schema Job Application is in 3NF. If it is not, apply the 3NF de- composition algorithm from the lecture notes to identify a 3NF decomposition for Job Application. To achieve full marks, ensure you include all key st
eps used in the 3NF decomposition process. (2 Mark)
-
2.2 Based on the functional dependencies (FD1-FD6) provided, determine whether the relation schema Job Application is in BCNF. If it is not: (3 Mark)
(a) Identify how many different BCNF decompositions exist for Job Application by applying the BCNF decomposition algorithm from the lecture notes. You must provide sufficient information to justify your answer. Simply giving a number without justification will not receive any marks.
(b) Present all BCNF decompositions that are both lossless and dependency- preserving. To achieve full marks, ensure you include all key steps used in the BCNF decomposition process.
Note that: (1) a 3NF/BCNF decomposition here means the set of relation schemas and functional dependencies you obtain after applying the 3NF/BCNF decomposi- tion algorithms on Job Application (2) A simple answer “yes/no” without proper justification would not receive any mark.
3
Question 3 6 Marks
The following table contains the relational algebra operators covered in our course. You may 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 simple hotel booking system with a relational database schema as follows:
-
Staff={eno, ename, ird} with the primary key {eno},
-
Room={rno, location, facilities} with the primary key {rno},
-
Guest={gname, contact, phone} with the primary key {gname, contact},
-
Offer={rno, date, price} with the primary key {rno, date} and the foreign key: [rno] ⊆ Room[rno]
-
Booking={gname, contact, rno, date, eno} with the primary key {gname, contact, rno, date} and with the foreign keys:
[gname, contact] ⊆ Guest[gname, contact] [rno, date] ⊆ Offer[rno, date]
[eno] ⊆ Staff[eno]We have the following relations over the above relational database schema:
Staff
Guest
Room
|
location |
facilities |
|
Level 0 |
Spa, Tennis court |
|
Level 13 |
13 Bowling lanes |
|
Level 2 |
Pool, Pool table |
|
ename |
ird |
|
Daffy |
875-649-322 |
|
Walder |
858-531-989 |
eno 1 2
rno
11 13 17
Booking
|
date |
price |
|
29/04/2016 |
749.00 |
|
29/04/2016 |
1313.13 |
|
29/04/2016 |
235.00 |
|
13/04/2016 |
749.00 |
|
13/04/2016 |
1313.13 |
rno
11 13 17 11 13
Offer
eno
1 1 1
|
gname |
contact |
phone |
|
Pepe |
Hotsforskunksville |
0800 443344 |
|
Taz |
Bunkerhill |
0800 232323 |
|
Coyote |
Ringroad |
0800 838383 |
|
gname |
contact |
rno |
date |
|
Pepe |
Hotsforskunksville |
11 |
29/04/2016 |
|
Coyote |
Ringroad |
17 |
29/04/2016 |
|
Pepe |
Hotsforskunksville |
13 |
13/04/2016 |
4
Consider the following relational algebra query: πgname,rno,location(σeno̸=eno′∨rno̸=rno′∨date̸=date′ (Room ▷◁ Offer ▷◁
(Booking ▷◁ ρgname,contact,rno′ ,date′ ,eno′ (Booking)) ▷◁ Guest ▷◁ Staff)) Your tasks are to
-
3.1 Write a SQL query that can be translated into the given relational algebra query.
(1 Mark) -
3.2 Draw the query tree for the given relational algebra query.
(1 Mark)
-
3.3 Evaluate the relational algebra query over the given relations, and show each of your answers as a table that includes the attribute names and tuples. For example, such a table can be shown as follows:
gname
Pepe Coyote Pepe
contact
Hotsforskunksville Ringroad Hotsforskunksville
rno date
11 29/04/2016 17 29/04/2016 13 13/04/2016
(a) Show the result of Booking ▷◁ ρgname,contact,rno′,date′,eno′ (Booking).
(c) Show the result of πgname,rno,location(σeno̸=eno′∨rno̸=rno′∨date̸=date′(Room ▷◁ Offer ▷◁ (Booking ▷◁ ρgname,contact,rno′,date′,eno′(Booking)) ▷◁ Guest ▷◁ Staff)).
(2 Mark)
3.4 Optimize the given relational algebra query. Your marks will depend on how well you present the key ideas of query optimization in your answer.
(2 Mark)
