Assignment
Case Study
You are assigned a task to design a database for a platform which helps owners of the houses to manage/run their businesses. Each owner wants to provide overnight accommodation and breakfast to couples only, 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 I: List business rules
List all the business rules for the database. // for help see ch. 2 (slides) of Book A An example of business rules is: An invoice is generated by only one customer
• Task II: Design ERD
Create 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 labeled as ERD 1.
In order to create point of differentiation between local bed and breakfast houses, some houses may allow guests to have up to 2 of their dogs stay in the attached kennels. The dog names are normally recorded so that each house can provide guests with a personalized dog collar for each dog.
• Task III: Design (extended) ERD
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 and new ERD is to be labeled as ERD 2.
Breakfast is provided for all guests as part of their accommodation, and guests can choose from either local/traditional or special breakfast. This information is normally recorded at the time of accommodation booking.
• Task IV: Design (extended) ERD
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 & new ERD is to be labeled as ERD 3.
Bookings are normally taken for up to 12 months in advance and guests who have stayed more than 5 times at the same house may receive a special gift on arrival.
• Task V: Design (extended) ERD
Add the entities, attributes, relationships, cardinality and optionality that describe how many times a guest have booked a stay. Add these to ERD 3. This ERD is to be labeled as ERD 4.
• Task VI: Design (extended) ERD
Consider a case that is not described above, but could happen in the platform described above. Explain the case and why it might occur.
Based on the case you proposed, modify your design of the platform accordingly.