Modelling Value: 10% Due date: 26-Aug-2016 Return date: 19-Sep-2016 Submission method options Alternative submission method Task 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. Task 1. Create an ERD that shows the entities, attributes, relationships, cardinality and optionality that describe the booking of a room by a guest. This ERD is to be labelled ERD 1. (50 marks) 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. Task 2. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of a dog(s) by a guest to their booking. Add these to ERD 1. This new ERD is to be labelled as ERD 2. (20 marks) 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. Task 3. Add the entities, attributes, relationships, cardinality and optionality that describe the addition of breakfast by a guest to their booking. Add these to ERD 2. This new ERD is to be labelled as ERD 3. (20 marks) 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. Task 4. Add the entities, attributes, relationships, cardinality and optionality that describe how many times a guest has booked a stay at Fermoy House. Add these to ERD 3. This ERD is to be labelled as ERD 4. (10 marks) Rationale This assessment item is designed to test your understanding of the following learning outcomes: Basic database design and modelling concepts, Identification and interpretation of business rules, Creation of an Entity Relationship Diagram from a set of business rules, Application of correct relationships, cardinalities, optionality using the Crows Foot modelling notation. Marking criteria Task HD DI CR Create an ERD that accurately represents all entities, their relationships with both cardinality and optionality correctly described and all Primary Keys and Foreign keys identified Create an ERD that accurately represents all entities, their relationships with most cardinality and optionality correctly described and all Primary Keys and Foreign keys identified Create an ERD that represents all entities, their relationships with most cardinality and optionality correctly described and all Primary Keys and most Foreign keys identified Correctly add additional entities, relationships, Primary and Foreign ERD 2: keys with all Dog cardinality and booking optionality correctly described to existing ERD to correctly reflect the additional business rule Correctly add additional entities, relationships, Primary and Foreign keys with all ERD 3: cardinality and Breakfast optionality correctly described to existing ERD to correctly reflect the additional business rule ERD 4: Correctly add Number additional entities, Correctly add additional entities and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule Correctly add additional entities and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule Correctly add additional entities Correctly add additional entities and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule Correctly add additional entities and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule Correctly add additional entities ERD 1: Booking PS FL Create an ERD that Create an ERD that represents some represents all entities, their entities, their relationships with relationships with little or no some cardinality and cardinality and optionality correctly optionality described described and all and few or no Primary Keys and Primary Keys and some Foreign keys Foreign keys identified identified Add additional entities and Add some additional relationships, entities and Primary and some relationships, with Foreign keys with few or no Primary some cardinality and and some Foreign optionality correctly keys, and with little described to existing or no cardinality and ERD to reflect the optionality described additional business to existing ERD rule Add additional entities and Add some additional relationships, entities and Primary and some relationships, with Foreign keys with few or no Primary some cardinality and and some Foreign optionality correctly keys, and with little described to existing or no cardinality and ERD to reflect the optionality described additional business to existing ERD rule Add additional Add some additional entities and entities and Task of stays HD relationships, Primary and Foreign keys with all cardinality and optionality correctly described to existing ERD to correctly reflect the additional business rule DI and relationships, Primary and Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule CR and relationships, Primary and most Foreign keys with most cardinality and optionality correctly described to existing ERD to reflect the additional business rule PS relationships, Primary and some Foreign keys with some cardinality and optionality correctly described to existing ERD to reflect the additional business rule FL relationships, with few or no Primary and some Foreign keys, and with little or no cardinality and optionality described to existing ERD Marking Guide Task ERD 1: Booking ERD 2: Dog Booking ERD 3; Breakfast ordering ERD 4: Number of stays Total Final mark Marks Score Comments 50 20 20 10 100 15 Presentation Assignments are to submitted as a single document with all ERD drawings embedded in the document. Assignments that are submitted with separate ERD drawings will not be accepted. ERD drawings are to be completed using either mySQL or the Draw.io tool