Modelling and Normalisation

Anonymous
timer Asked: Aug 25th, 2013
account_balance_wallet $5

Question Description

Part A. Create an ERD (50 marks):
Read the following problem and complete the tasks listed beneath it.

The Fermoy House database 
The owners of Fermoy House, a Bed and Breakfast guest house in the Blue Mountains of NSW have approached you to build them a database to help them run their business.

Fermoy House provides overnight accommodation and breakfast to couples and has four guest bedrooms and a detached private cottage in a private part of the garden. Each bedroom, and the cottage, will accommodate a maximum of 2 guests only.

In order to create a point of differentiation from other local bed and breakfast houses, Fermoy House will allow guests to have up to 2 of their dogs stay in the attached kennels. The dog names are normally recorded so that Fermoy House can provide guests with a personalised dog collar for each dog.

Breakfast is provided for all guests as part of their accommodation, and guests can choose from either a Continental or a cooked Irish breakfast. This information is normally recorded at the time of booking the accommodation.

Bookings are normally taken for up to 12 months in advance and guests who have stayed more than 5 times at Fermoy House receive a gift of wine and chocolates on arrival.

Tasks:
1. List all the business rules for the database for Fermoy House. An example of business rules is: An invoice is generated by only one customer; (10 marks)
2. Create an ERD that meets the Fermoy House requirements. The ERD should show only Primary Key and Foreign Key attributes. (40 marks)


Part B. Create an RDM (15 marks):

Tasks:
Using the Fermoy House ERD created in Part A:
1. Identify all attributes required for each table shown in the ERD;
2. Create an RDM for each table in the ERD;

Part C. Normalise the RDM to BCNF (25 marks):

Tasks:
Using the RDM created in Part B:
1. Create a set of Dependency Diagrams for the Fermoy House database;
2. Normalise the Fermoy House tables to BCNF;
3. Create a new RDM for the normalised tables.

Part D. Modify your design (10 marks):

Tasks:

1. Consider a case that is not described above, but could happen in the business of the Fermoy House. Please explain the case and why it might occur.

2. Based on the case you proposed, modify your design of the Fermoy House database accordingly.


Tutor Answer

Knowlez
School: UIUC

Hi! Contact me before awarding so that we discuss in detail regarding the set objectives

flag Report DMCA
Review

Anonymous
Thanks, good work

Similar Questions
Hot Questions
Related Tags
Study Guides

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors