Design and Implementation Report: Sports Club Database System

This report provides a comprehensive overview of the design and implementation of a sports club database system. It includes a use case diagram illustrating system functions and actor interactions, an entity relationship model (ERM) detailing the relationships between entities such as members, staff, and booking activities, and corresponding SQL statements for database creation and querying. The report outlines the relational schema, primary and foreign key usage, and the overall structure of the database to support sports club management, member activity tracking, and staff management. The system aims to streamline operations, improve data accessibility, and enhance the management of sports-related activities within the club.

Report Design and Implementation
1

Contents
INTRODUCTION......................................................................................................................3
TASK.........................................................................................................................................4
Use case diagram....................................................................................................................4
Entity relationship model (ERM)...........................................................................................9
SQL statements....................................................................................................................19
Create database tables..........................................................................................................24
Two SQL queries.................................................................................................................25
CONCLUSION........................................................................................................................27
REFERENCES.........................................................................................................................29
2

INTRODUCTION
A database refers to the information or data that is useful for setup and easily access,
update or manage data in proper manner. Typically, a large collection of data can be stored in
the database and provide the facility for other to access relevant data in proper manner. For
considering the sport club system, it focuses on the designing database system and collect or
store all relevant information. In future, it is accessible for user’s to identify the sport club
activity on the weekly basis.
The report is based on the implementation of a new membership record system that
provide the better facility to store information related members, sport related activity
bookings and staff members. The designer will select a particular fields and attributes to
establish a strong relationship between one or more tables. The case scenario is related to
sport club database system and allows the different members to check availability of sports
classes per week.
The aim of report is to develop or design a club sport system and combined with
different activities. It provide the brief idea about the information which is related to the club
in particular region. Additionally, it offers the different services and ability to book or move
from club and participate into different activities by members. In this way, it is always
motivated the people to join sport club and showing all necessary equipment, machines. It is
helping to improve the innovative program and also proposed the system which can be
developed.
The scope of project is to build the best solution in regards of sports club system and
keep record, maintain all information about members, staff members and booking activities.
Once, it developed the sports club management systems in order to target the best solution
and consider all essential resources to build their management. Typically, it can happen in the
small enterprise, it would require to keep record all essential information and accessible at
any time. Generally, it should be considered the essential resources which are limited and
destined to improve the sports related service. It offers the better services and improving the
entire management system.
3

TASK
Use case diagram
It is based on the graphical representation to express the high level functions, scope of
sport club record system. When a use case diagram is helping to determine the better
interaction between systems and actors (Ataei, 2021). The use case of sport club system is
describe what the system behave and how each or every actors use it. In some cases, the
system of sport club record system operates internally.
4
Login account
Check sports activity
Update/edit time-table
Replace equipment
Book slot
Confirmed
Staff
Admin
Member
s
Sign up
Received Notification

Figure: 1 User Case Diagram
The importance of use case diagram is to understand the high level functions and scope of
Sports club management system. The significant role of use case diagram is to find the
interaction between actors and systems. It allows to understand how it will perform certain
actions in step by step manner. Before starting the project of Sport club management, it is an
essential aspect to create use case model for business perspective so that it can easily
represent the significant role of participants (Vaidya, 2021). In this way, it is also developed
more understanding of staff members of spot club, participant members and booking
activities. Once, it is gathering the requirements and then move towards creating a use case to
capture system needs. At the time of analysis and design, it is always used the use cases and
actors, identify how they will perform different tasks in proper manner.
 Use case- it is described the function of Sport club management system and perform
to achieve desirable goals and objective. A use case must be needed to the focused on
the desirable results and finding the value of users in the system.
 Actors- it can be represented the significant role of particular users and establish a
strong interaction with system. Sometimes, it depends on the modelling. A use can be
external objects and humans.
 Subsystem- it is kind of stereotyped approach that is always represented the concept
of behavioural units, independency in the system. Subsystems are becoming consider
an important to identify the large scale approach and modelling.
 Relationship- A relationship refers to the connection between different elements of
model. it is helping to add semantics in which define the overall structure, behaviour
of different elements.
In the context of UML, use case diagram plays an important role in the sport club system
and identify the involvement of different users, their specific interactions. Therefore, it is
necessary to understand the behaviour of users and create visualizes the interactions between
development system as well as actors (Chau and Chittayasothorn, 2021). Typically, the
external entities are known as actors. It may be represented the human users and showing an
external hardware. In the case of sport club system, it can be identified the different actors
such as sport club members, staff members of sport club and administrator.
5

Based on the figure-1, it has been proposed the use case diagram and build more
understanding towards use case cases. On the basis of analysis, it starts with the beginning
section such as sign up. It provide the opportunity for members where they can complete
registration process. Afterward, they were easily login from her or him to enter their personal
information. Furthermore, the system will search the information and completing the
verifying or validating process. If in case, it may arise a mistaken information where it
automatically display same home page. After successfully completing the process, member
can be checking the sport activity on the daily basis. In case, it has found the availability and
immediately book a slot, get a confirmation message.
Apart from that, it has been identified that staff member as user can edit, update and
replace equipment’s according to the requirements of particular sport activity. On the basis of
proposed diagram, it is displayed all correct information for users to make and utilise the
system. It is also re-directing the user to login their account and accessing all essential
information or data.
Use case description-
Use case-1
Use case name Member signup or registration
Actor Member
Admin
Pre-condition Member of sport club has followed the path
of registration and completing their signup
process so that they can further proceed.
Flow of events  Click on the signup option
 Member of sport club can enter
necessary personal information
 Admin can be validate the correct
data and then stored in the database
system.
Post-condition  In future, member can be added the
new information in their account.
Exception The system does not allow the user to
6

proceed and develop a situation of system
failure.
Table: 1
Use case-2
Use case name Member login account
Actor Member
Pre-condition Member of sport club has followed the path
to login their account by using username,
password and also accessing the information
or data.
Flow of events  Member can enter username and
password.
 Access the relevant information and
choosing different options regarding
sport activity.
Post-condition  In future, member can be added the
new information in their account.
Exception The system does not allow the user to
proceed and develop a situation of system
failure.
Table: 2
Use case-3
Use case name Staff member update information or data
Actor Staff member
Pre-condition In the sport club, staff members of sport
club have focused on the booking activity
7

information. If a new system will get alert
message from members side.
Flow of events  Staff members of Sport club have
accessed their account by using
username and password.
 Update the information related
equipment, timing, sport activities in
the system.
Post-condition  In future, Staff member can be
completed modification in the
system and provide right detailed to
the users.
Exception The system does not allow the user to
proceed and develop a situation of system
failure.
Table: 3
Use case-4
Use case name Member book sports activity slot
Actor Member
Pre-condition Member of sport club has followed the path
to login their account by using username,
password and selecting the option for
particular sport activity.
Flow of events  Member can enter username and
password.
 Choosing the category of different
sport activities.
 Book a slot
 Received confirmation
Post-condition  In future, member can be added the
new information in their account.
8

Exception The system does not allow the user to
proceed and develop a situation of system
failure.
Table: 4
Entity relationship model (ERM)
Figure: 2 Entity relationship model
An entity relationship model is based on the approach to represent the relationship
between places, objects, people and events within IT system. It is one of the important aspect
to establish an interrelated connection with interest of specific domain (Coole, 2021). ERD
model is composed of different entities and classify things of interests. In some cases, it is
specifying the strong relationship that can exist between different entities.
In the context of sport club database system, it is commonly used entity relationship
model to represent things an enterprise needs to remember. Consequently, it can be defined
9

the abstract data model which can easily defined the data or information. Afterwards, it is
implemented in the database which is typically created a relational database.
Primary key- It is based on the column that contain values which is uniquely identify
each or every row in the specific table. A database must have primary key so that it helps to
perform different operations such as update, restore, insert, delete data (Ramez and
Shamkant, 2022). The purpose of primary key is to identify the uniqueness of row where it is
helping to prevent from duplicates. For example- member reference number, class code and
staff members.
Foreign key- it refers to the column in the table whose values must be matched in
another table. It is also supporting to reinforce the referential integrity and consider as an
essential aspect to build a relationship between two database tables in proper manner.
Entities Attributes
Member table  Member reference number (PK)
 First name
 Surname
 Address
 Phone no
 Email
 Date of birth
 Medical condition
 Class code (FK)
Booking activity table  Class code (PK)
 Title
 Day
 Time of delivery
Sports club staff table  Staff number (PK)
 First name
 Surname
 Role
 Contact number
 Member Reference number (FK)
 Class code (FK)
10

Table: 5
In figure-2, it has proposed the entity relationship diagram or model in order to
represent the different entities and their specific attributes. ER is one of the best computing
approach in regards of data or information, which helps to find the better solutions. In table-5,
it is also representing the primary and foreign key that uses at the time of database design.
Relational schema-
Member table
Reference
number
Firstname surname address telephone Email
address
Date
of
birth
Medical
condition
Class
code
SportClubStaff table
Staff
member
Firstnam
e
surname role Contact
number
Member
reference
number
Class
code
Booking activity table
Time of delivery Title Day Class code
A relational schema is defined the set of relational tables which are associated with different
items and also related to the one another. All kind of tables, indexes, view and roles are
created in order to fulfil all essential needs of particular business (Zhang, 2021). The
relational schema plays an important role because it helps to create or develop a set of
applications such as Sports club management system and belongs to the one schema. That’s
11

why, it is important to create appropriate relational schema to identify the interactions
between one or more tables.
Relationship between database tables
Relation-1
Figure: 3
In figure-3, it has been represented the relational schema between two tables such as member
table and booking activity table. These are two tables which can be developed a strong
relationship between each other by using foreign key. In BookingActivity_table, Class code
is a primary key which consists of unique values. In Member_table, class code can be
behaved as foreign key in which establish one to one relationship. For example- One member
is booking a one class activity at single time. This is the way to build a relations between
members and booking activity tables.
12