Modelling and Normalisation

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.

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

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

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


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

School: UIUC

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

flag Report DMCA

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