Solve this problem

Paradise Villages is an up-market holiday consortium providing ethically sound activity
holidays with a touch of luxury; accommodation is in canvas ‘villages’. To minimize
environmental impact villages consist of no more than 15 tents, most being between 8 and
12 tents. Each village is locally managed to maximize the use of local knowledge and
products. There are now more than 250 Paradise Villages throughout the UK and
Australasia; numbers continue to rise.
You are to design a database for Paradise Villages. Initially each village will maintain its
own local database, but they will all be of the same design.
The database must allow for clients to:
- make holiday bookings, each booking may be for a number of tents, and can be
complex; clients organizing family holidays may want different numbers of tents on
different days as family groups come and go;
- reserve (and be billed for) activities, e.g. the hire of canoes; data would include the date
the canoe trip took place and the number of canoes used. Generally activities are
arranged once the holidaymaker is on-site.
Some sites also have additional facilities (such as a gourmet restaurant) in which case
clients should be able to make bookings before the holiday commences or during the
holiday.
Scenario
Mr Carogill wants to book a special holiday at the Paradise Villages site called ‘Gypsy
Island’ for his family to celebrate his 60th birthday in April 2010.
- His older daughter Julie and her husband Tom have twins who will still be quite young,
so they will want to stay at the site for the whole holiday.
- Mr Carogill, together with his younger daughter Flossie and her partner Jo, plans to
make an expedition part way through the holiday.
- At the end of the holiday Flossie and Jo have to leave a day earlier than the others.
- Mr Carogill’s brother and his brother’s wife cannot make the first week of the holiday,
but will join in later.
Thus Mr Carogill’s requirements are:
7th – 11th and 17th – 21st April 2010 tent for single occupancy (for Mr Carogill)
7th – 11th and 17th – 20th April 2010 tent for 2 (Flossie and Jo)
7th – 21st April 2010 tent for 4 (2 adults + 2 children)
15th – 21st April 2010 tent for 2 (brother and his wife)
Mr Carogill wants to pre-book his party into the gourmet restaurant on 19th April 2010. For
this he wants to reserve 2 high chairs (for the twins).
He wants any activities booked by his family during the holiday to be charged against his
account (thus, for example, Flossie and Jo should not be presented with a bill when they
leave).
Your database should allow Paradise Villages to meet Mr Carogill’s requirements assuming
they have the capacity (i.e. tents are available for those dates etc.).
Paradise Villages needs to record the following data:
About the client:
• Name.
• Address.
• Telephone,
• Credit card details.
About the site:
• For each tent its name and capacity.
• For the restaurant (or other facilities) if present, its name and capacity.
• For equipment, the item type (e.g. canoe or high chair) and how many of the item are
held.
About bookings:
• It is necessary to record that a booking is made.
• The cost of the booking at the time the booking is made.
About billing:
• The cost of items (e.g. a 2-person tent) throughout the year must be recorded.
Generally this will be divided into high, mid and low season rates.
• As time goes by these prices will be updated.
Unstructured information:
• There is a need for both clients and Paradise Villages to record ad hoc information,
e.g. a client might write a note “I am allergic to all forms of gluten, please can you
make sure there is something I can eat for breakfast.”
Note that when the database is designed it might be necessary to introduce other data
items such as identifying keys.
ASSESSMENT TASK: Conceptual and Logical Design (marked out of 40)
1.1 Conceptual model (20 marks)
Create an ER-diagram (simple or enhanced) for Paradise Villages. You should adopt the
diagrammatic conventions used in the text book (Ramakrishnan) but you may annotate your
diagram to convey more information if you wish.
What to submit for this section: your diagram, together with a maximum of one page of
A4 in which you can explain aspects of your diagram, explain the meaning of any attributes
you have introduced, and state any assumptions or decisions you have made.
1.2 Logical model (20 marks)
Convert your diagram into a set of relations (see Unit 5). Represent your relations in the
following style:
What to submit for this section: a set of relations and their constraints as described
above, together with a maximum of one page of A4 in which you explain any decisions you
made
Patient (pno, pname, title, DOB, children, gp)
Primary key (pno)
Constraint title in (‘mrs’, ‘ms’, miss’, ‘mr’)
Drug (dno, dname, DOI)
Primary key (dno)
Dose (pno, dno. dosedate, unit, qty)
Primary key (pno, dno, dosedate)
Foreign key (pno) references patient (pno) on delete cascade
Foreign key (dno) references drug (dno) on delete no action
You should include actions to be taken to maintain referential integrity (on
delete…, on update…), and other integrity constraints