Data Modelling, Management and Governance

Assignment - Report (design) and implementation

Learning outcome 1: Exhibit a balanced and systematic understanding and knowledge of data modelling and management theory and data governance practice (including querying languages).

Learning outcome 2: Apply knowledge of data modelling and management concepts to design, develop and critically evaluate an information system within a chosen field of interest

This assessment is an individual assignment.

You are required to write a report on how you design and implement a database solution for a sports club record system. Remember this is NOT a research project. Your report cannot be research report.

The sports club scenario

You have been employed as a database designer to design and implement a Sportclub record system that stores information about sports activity and taught class bookings.

The imagination sport club exhibits the following facts:

1. Each sports club member has a unique membership number, the system needs to store member's first name, surname, address, telephone number, email address, date of birth and any medical conditions.

2. Each sports club staffalso has a unique staff number, first name, surname, role and a contact number.

3. Each sports class (staff teach) must have a unique class code, title, day and time of delivery.

4. Non-class Activity (only use facilities) and class bookings must follow the following rule:

a. Each membercan book up to five sports classes per week.
b. Each member can only book once of any one sport activity (like badminton, Basketball, ...) and the maximum "two-hour" time slot.
c. No activities that overlap can be booked.
d. Sports class only runs from 9am to 5pm but activity can run from 6am to 10pm.
e.
5. The system should allow:

a. An instructor (staff member) to search for the activities running on a particular day.
b. Sports club staff members and staff can check and update their personal details.
c. Sports club staff can record attendance for a class they are teaching.
d. Each club member can view their daily or weekly activity bookings.

Your report must provide (minimum):

1. A relational database system named "Sports Club Management System".

2. A"Use-case diagram" that identifies the key actors and use-cases in the system and represents the requirements specified in the scenario.

3. An "Activity diagram" for a club member to book an "badminton session" and a "Yoga" class.

4. An"Entity Relationship diagram" that you designed can satisfy the requirements outlined by the "use-case diagram" and the "activity diagram".

5. A list of the SQL statements that covers:
a. Used to create tables that implement the ERD identified in 3.
b. shows how a sports club member can view their currentbookings and reflect the booking rules.
c. displays the current week activities booked club members.
d. Display the current week class offered by the club including staff and number of class size.
e. Show most active members monthly (most bookings and most attendance)

Your report should explain your design process and decisions at each phase. You should also identify any issues or challenges that you faced in each step.