Results 1 to 2 of 2

Thread: Solve this problem

  1. #1
    Join Date
    Oct 2009

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

  2. #2
    Join Date
    Oct 2009
    Is this your school assignment? If so, I would think you ought to at least try to figure it out then ask for help if you get stuck. That's how you learn not by getting the cloud to do your work.

    If not, then perhaps you ought to break this down to the specific problem you are stuck on. I don't mind helping folks out but I am not about to read through nearly 1,000 words to do so.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts