THE SCENARIO

You have been contracted to develop a database system for a company that operates holiday parks around the country. The database will store information about the parks, lodges, staff, customers, scheduled activities, and bookings made. The data requirements for the database system are described below. Each park has a unique ID, address, and postcode. The parks employ activity leaders who lead activities, and administrative staff with various job roles, e.g., manager, receptionist, HR, sales, etc. The data held for each member of staff will include a unique staff ID, their first name, surname, date of birth, their contact address and phone number, their salary, and any workplace adjustments they require. All activity leaders will have their speciality and Disclosure and Barring Service (DBS) check verification recorded. For administrative staff, their job role will be stored. Each park schedules a range of activities. Each activity is led by a single activity leader, administrative staff do not lead activities. An activity will have a unique ID, and the activity name, the date and time of the activity, the duration, and target audience (children, adults, all) will be recorded. Each park provides several lodges that can be booked for short breaks. Lodges are identified by a unique lodge ID. Other data stored for a lodge includes the area of the park the lodge is located (Central, Outer, or Exclusive), the lodge number, maximum number of occupants, and an indication of whether the lodge is wheelchair accessible, and if dogs are allowed. Customers must create an account before making a booking. Customers are identified by a unique customer number. The customers first name, surname, email address, and contact phone number will also be stored. After registering, customers can make a booking at one of the parks. Each booking is given a unique booking number. The booking date, the arrival and departure date, and any additional special requests will also be recorded. The customer will also need to select at least one lodge for their booking. If their party size is large, they will need to select multiple lodges. The number of guests and price will also need to be recorded for each selected lodge.

Question :

Design a conceptual data model for the scenario given above.

a) Construct an Entity-Relationship (ER) diagram using UML. The diagram should show all entities, the attributes of each entity, the relationships between the entities with appropriate multiplicity constraints, and the coverage properties for any hierarchical relationships you have identified.

b) State any assumptions necessary to resolve any ambiguities in the scenario. Pay special attention to cardinalities and participation constraints.