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.
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
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:
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:
|Carmenfirstname.lastname@example.org||Peru||3536789||SA007||1||01/01/09||20/12/09 1pm||Lufthansa||LF5||LHR||FRA||3hr||Economy||600||Boeing 747||AVO,snack|
|I. Gadgetemail@example.com||London||111-888||UK5812||2||03/11/09||20/12/09 1pm||Lufthansa||LF5||LHR||FRA||3hr||Economy||600||Boeing 747||AVO,snack|
For solution connect with our online professionals.