The Problem 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.

Questions :

Produce a logical data model for a relational database.

a)In a relational schema, indicate the primary keys and any foreign keys introduced to model the relations and constraints. Represent the schema in textual form.

b)For each foreign key, specify whether a null value would be allowed. Justify your choice.

c)State which attributes may be null and explain why.

d)State any other required constraints for your logical data model.