The Fermoy House database

Part A. Create an ERD

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:

• 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
• Create an ERD that meets the Fermoy House requirements. The ERD should show only Primary Key and Foreign Key attributes.

Part B. Create an RDM

Tasks:

Using the Fermoy House ERD created in Part A:

• Identify all attributes required for each table shown in the ERD
• Create an RDM for each table in the ERD

Part C. Normalise the RDM to BCNF

Tasks:

Using the RDM created in Part B:

• Create a set of Dependency Diagrams for the Fermoy House database
• Normalise the Fermoy House tables to BCNF
• Create a new RDM for the normalised tables

Part D. Modify your design

Tasks:

• 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.
• Based on the case you proposed, modify your design of the Fermoy House database accordingly.