M33972 AE2- Database Solution and Report Brief

MODULE LEARNING OUTCOMES

On successful completion of this module, students should be able to:

LO1: Evaluate theoretical background to the use of database management techniques

LO2: Review the role of database application in strategically supporting enterprise

LO3: Implement and evaluate a database solution for a small enterprise

LO4: Review and discuss emerging database technologies

LO5: Critically review the Management of database technologies and performance tools

CASE STUDY

Development of a Database Solution for Elite Sports Academy Elite Sports Academy is a premier training facility for young athletes specializing in various sports, such as football, basketball, and swimming. The academy has been managing its operations and athlete data manually, leading to inefficiencies and data redundancies. To address these issues, the academy plans to implement a relational database management system (RDBMS) to streamline its processes, improve data accuracy, and better serve athletes, coaches, and administrative staff.

Organizational Structure

The academy is divided into several sports departments, each focused on a specific sport, such as football, basketball, or swimming. Each department consists of several teams, and each team is managed by a head coach, with assistant coaches assigned to provide additional support. Athletes are assigned to one team, but they may participate in more than one sport depending on their skills and interests.

Coaches develop training schedules and monitor the progress of the athletes through assessments, feedback, and performance data. The academy also keeps records of athletes’ personal information, such as their age, sport, and contact details for their guardians. Coaches work in close collaboration with medical staff to ensure athletes are fit for training and competitions.

Current Manual Record System

The current system includes the following manual records:

Athlete Information Card: Each athlete’s personal details, including name, date of birth, guardian information, and the sport they are training for, are recorded on an information card. This card also keeps track of an athlete's training schedule, the coach responsible, and assessments.

Team and Coach Information: Each team has a record detailing the athletes assigned to it, the head coach, assistant coaches, and the training times. When athletes are moved to different teams or leave the academy, their information is crossed off the team list.

Training and Performance Record: This card captures data on an athlete’s performance in assessments and feedback from coaches. It includes details such as the assessment type (e.g., strength, endurance), performance score, and feedback from the coach. Each assessment is dated, and athletes often undergo multiple assessments in different categories.

Key Requirements for the New System

Athlete Records: The system must store detailed personal information of each athlete, including name, date of birth, guardian contact details, and the sport(s) they are training for.

Team Management: Each team is identified by a unique team name and is associated with one or more sports. A team consists of a head coach, assistant coaches, and athletes. Coaches must be uniquely identified by a staff number to prevent confusion when names overlap.

Training Schedules and Assessments: Each athlete has a training schedule tailored by their coach. Training assessments should capture performance data and feedback, and be linked to the athlete, the assessment type, and the date of the assessment.

Guardian and Emergency Contact Information: Each athlete has associated guardian information that includes the name, address, and contact details of their guardian. This information needs to be accessible by the administrative team in case of emergencies.

Tasks

The new relational database system will store all the above data and remove data redundancies that exist in the current manual system. By linking athletes to their teams, coaches, training schedules, and performance assessments, the system will centralize all relevant data, making it accessible and manageable by various user groups at the academy.

1. Entity Relationship Modelling (ERD)

Draw an ERD for the Elite Sports Academy, identifying entities. Ensure to include relationships between these entities, such as the link between athletes and teams or assessments. Do not include attributes.

The ERD should fit on ONE page and MUST be produced electronically. Hand-drawn diagrams will receive NO marks. 
There are a number of free or trial-period ERD drawing software packages that you can use, Like Lucidchart which is free.
Hand in: ER diagram, list of assumptions. (Assumptions are made when the case study text does not give you enough information. These assumptions should only relate to the design of the system. For example, you may have decided to include or omit certain entities – Assumptions should be in text form – less than 200 words)

2. Data Dictionary/Database Creation

Create a data dictionary for the tables you have identified in your ERD. The tables should contain a logical range of attributes and clearly indicate the keys. For each table, the data dictionary should include the components as shown in template below:

• Create the DDL script for the creation of the database schema, including all tables, keys, and constraints.
• Highlight the constraints added (e.g., foreign key constraints, unique identifiers).
• Hand in:
• Data Dictionary (entities, attributes, data type, size, keys, constraints) as template.
  
• DDL scripts and explanation of any modifications made to the ERD. 

3. Testing Data

• Create and populate a working database for the given case study.
• Deploy your database on an online server of your choice (e.g. https://www.freesqldatabase.com)
• Take the screenshots showing all the tables created.
• Fill the tables with sample values. You can get help of Generative AI tools such as ChatGPT. Take screenshots of how you filled
• tables with sample values. Show the tables using Select * From <Table> queries to show your filled values.

4. Sample Queries

Write FIVE SQL statements that reflect the needs of the business. A good screenshot of both the query and the output will be required with explanation of what each query will achieve for the business. The marking will be for:

• The level of complexity of each query,
• The query being of use to the business,
• Use of additional features such as constraints etc.

Sample queries: 

• Create a query to provide a list of teams with their head coaches and a list of athletes assigned to each team.
• Generate a report for one head coach that includes their athletes' training assessments for a specific month

T1: ERD

Logical and clear layout

Meaningful entities

Meaningful relationships

Cardinality 

Proper notation

Completeness

Assumptions

T2:  Data Dictionary/scripts

• A comprehensive DD that relates exactly to the ERD, sensible choice of attributes and data types. Data types are correct for above stated RDBMS. All PKs and FKs in the correct table. Attributes relevant, explanations given where required, domains etc. present. Tables creation scripts are correct and perfectly aligned with DD.
  
  
• A good DD that has minor errors only in Data types, sizes, attributes etc. Most Tables creation scripts are correct and perfectly aligned with DD.
  
  
• Significant errors in the DD, does not relate to the ERD, contains errors in data types, attributes keys, no explanations given. Table creation scripts are erroneous. 

T3: Testing Data 

Deployment

• All the tables identified in previous steps are correctly deployed on database server. Screenshot of creation process of each table is provided (screenshot of Table creation query shown on database tool)
  
  
• Most tables identified in previous steps are correctly created on database server. Screenshot of creation process of most of the tables are provided
  
  
• Significant errors in deployment of tables and failure to provide the screenshots.
  
  
• All the tables are populated with sample data generated using Generative AI. Screenshot of sample data creation process (e.g. prompt used on ChatGPT) are provided.
  
  
• Most of the tables are populated with sample data generated using Generative

• Significant errors in population of tables and failure to provide the screenshots.

T4: SQL QUERIES

The queries will contain relevant SQL code and show that the student has grasped some of the more complex SQL syntax. Each query has 5 marks, which will be awarded on the basis of:

• The query being of good business use.
• Good differentiation from other queries.
• Use of additional features such as constraints etc.