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’.