Question 1
An adventure holiday firm organizes “adventure holidays” and wants to set up
a database to aid its business. The company owns a group of cabins in the
mountains at its headquarters, alongside a river. It also owns several rafts for
river-rafting. It organizes Clients to go on Rafting Trips, which can last just a
few hours, or several days, down different rivers, ‘shooting’ rapids of various
levels of difficulty, and camping on the bank of the river at night
They take bookings from Holiday-Groups, made up of one or more Clients.
(Clients prefer to book this way because Holiday-Groups get a discount on the
price of a holiday.) Holiday-Groups arrive and leave together, but the
individuals who make up a Holiday-Group do not necessarily all take part in
identical activities while on the holiday. Each Holiday-Group gets a unique
code.
A Client is identified by his Holiday Group Code plus a unique number. Each
Holiday-Group chooses one of their members as Group Coordinator. We also
record each Client’s first name and family name.
Holiday-Groups arrive and depart from the company’s headquarters together,
but may go on different river rafting expeditions while they are there. For
example, in a group which is staying for two weeks, one client may choose to
go on the ten-day rafting adventure, while another in the group may choose to
go on several shorter adventures.
Clients stay in Cabins. There may be one, two, or more Clients to a Cabin. A
Cabin has a unique Cabin-Name, and a daily rate, and can have one, two, or
three bedrooms. We want to record this information. No Cabin ever has
members of more than one Holiday-Group in it at the same time, but of course
a Holiday-Group may occupy more than one cabin. Cabins may be
unoccupied.
A Rafting-Trip has a start-date, and duration which can be from 1 to 14 days.
A Rafting-Trip will use only one raft. (The company has rafts with different
passenger capacities.)
A Rafting-Trip consists of a group of Clients, who may be from several
different Holiday-Groups. A Rafting-Trip starts on a given date. All Rafting
Trips start from the same location, but can end at different End-Locations
along the river. (The company arranges for rafters and their raft to be picked
up at the End-Location and returned to company headquarters at the end of a
Rafting Trip.)
A Rafting-Trip is always led by a company Employee, called a Raft-Master,
who is identified by his Employee-Number and Start-Date. Employees are
identified by Employee-Numbers; End-Locations are identified by GPSCoordinates
translated into an eight-digit number. For each End-Location we
record a brief description, and an average journey-time to the End-Location from the starting location. Each Employee has his Employee-Number
recorded, and his First and Family names.
A. Draw an Entity/Relationship Diagram that expresses the relationships
among the entity types described above. Assume that we model Holiday-Groups,
Clients, Employees, Rafting-trips, Cabins and Locations as entity
types. You need not indicate the attributes of each entity type.
B. Design a fully normalized relational schema that can capture the data
relationships expressed in your Entity-Relationship diagram.
Your schema should be able to capture the following example data. Populate
your proposed relations with this data.
A Client whose name is Michael Saville, is part of a Holiday-Group whose
HolidayGroupCode is E45; his unique number is 27, and he is staying in the
Cabin whose name is ‘Pioneer’. Staying in the same cabin, and a member of
the same group, is Ibrahim Jaffar, whose unique number is. 28. Group E45
arrived on the 1st of September 2014 and left on the 15th. Another Client is
Sundar Singh, whose unique number is 27, and whose HolidayGroupCode is
E53. The ‘Pioneer’ cabin has three bedrooms and a daily rate of £150.
Another cabin, ‘The Little Lodge’, has 2 bedrooms and a daily rate of £125.
Sundar Singh is staying here.
All three of them are going on a Rafting-Trip led by the employee whose
EmployeeNumber is E345, and whose name is Jumaane Ban!, starting on the
5th of September, and ending at a destination whose GPS Co-ordinates are
23455733. This End-Location takes an average of 3 days to reach, and has
the note ‘Locked gate – bring key’.
