COMP7240 Introduction to Database Concepts: Database Theory Assignment 2 Answer

COMP7240 - Introduction to Database Concepts Assignment 2: Database Theory

This assignment will be marked out of 20. It will count for 20% of the final grade. Below you will find 3 questions to reach this score. Marks are assigned for the process of finding a solution, not only for the result. Hence, it is better to include all essential ideas and steps that are necessary to derive a solution.

Instructions:

• This assignment should be done individually.
• You must submit two files on Wattle before the due date:
  • “u1234567-ass2.pdf” (replace u1234567 with your UID). This is the file that con- tains your answers to all the questions, including a JPEG figure for the EER diagram being exported from TerraER or other ER drawing tools.
  • “u1234567-ass2.xml” (replace u1234567 with your UID). This is the XML file that corresponds to the EER diagram drawn by TerraER. If you use a different drawing tool to draw your EER diagram, then you can submit the corresponding drawing file as well, for example, ”u1234567-ass2.vsdx” if using Microsoft Visio.
• Late submission  is  not  granted  under  any  circumstance.  You  will  be marked on  whatever  you   have   submitted  at  the  time  of  the  deadline.   Please    take  careful  note  of  deadlines  and  adhere  to  them.    Of  course,   if  you    find yourself in  a  situation  beyond  your  control  that  you believe  signif-  icantly affects an assessment, you should follow  the  ANU’s  special  con-  sideration process (https://www.anu.edu.au/students/program-administration/ assessments-exams/special-assessment-consideration).
• Plagiarism will attract academic penalties in accordance with the ANU guidelines. A student in this course is expected to be able to explain and defend any submitted assessment item. The course convener can conduct or initiate an additional interview about any submitted assessment item for any student. If there is a significant discrep- ancy between the two forms of assessment, it will be automatically treated as a case of suspected academic misconduct.

Question 17 Marks

A website offering holiday accommodations wishes to set up a catalog to manage information about its hotel offerings.

The website offers hotel rooms. Every hotel has a unique ID, name, contact details (e.g., phone number, address, and email), URL, number of room, and rating. Furthermore, each hotel provides certain facilities (e.g., Internet, elevator, and swimming pool), although facil- ities are not necessarily free. Each hotel has a collection of policies, such as policies about check-in time, check-out time, and cancellation. The website also provides distance informa- tion from a hotel to some nearby locations, such as the city centre, main train station and other major attractions.

Every room in a hotel has a unique number, is of a particular type (e.g., single, double and twin), and has some maximum occupancy. Every offer is for a particular room and includes a price-per-night, a start date, and an expiry date. The expiry date of an offer must be after its start date. Moreover, a room cannot have two overlapping offers, i.e., at any point of time, only at most one offer is available for a room. An offer may contain some conditions about the facilities and policies which can over-write the standard policies and facilities of the hotel. For example, a hotel usually offers cancellation up to 6pm on the day of arrival and a full refund can be received upon cancellation according to its standard policies, but the price of a particular room on offer might be so good that no refund is given upon cancellation.

Your tasks are as follows:

1.1 Design, using the notation of the lecture slides, an Enhanced Entity Relationship (EER) diagram for the above database application, which should include entities, relation- ships, attributes and constraints wherever appropriate.

(6 Marks) 1.2Validate your EER diagram by answering the question: “are there any information or

integrity constraints you are not able to represent in your EER diagram so far?”. If

so, give examples and explain why.

COMP7240 - Introduction to Database Concepts

Question 26.5 Marks

An information system handling customers and their flight booking data was requested by a travel agency. The IT section created a database schema containing the following relation schema Booking:

• Passenger Name
• Passenger Email
• Passenger Address
• Passenger Phone
• Passport No
• Booking No
• Booking Date
• Travel Date
• Airline
• Flight No
• Departure
• Destination
• Flight Duration
• Class
• Price
• Aircraft
• Services

Consider below the data requirements of the travel agency, which are represented by the functional dependencies fd1-fd6, and a sample relation over the relation schema Booking:

• fd1: {Passport No} → {Passenger Name, Passenger Email, Passenger Address,

Passenger Phone};

• fd2: {Flight No} → {Airline, Departure, Destination, Flight Duration};
• fd3: {Airline, Flight No} → {Aircraft};
• fd4: {Airline, Departure, Destination, Class} → {Service};
• fd5: {Booking No} → {Booking Date, Travel Date, Flight No};
• fd6: {Booking No, Passenger Name} → {Price, Class}.