Data Modelling, Management and Governance for Sports Club Management System

This report focuses on designing a database solution for sports club record system using data modelling, management and governance techniques. It includes use case diagram, entity relationship model, normalized database, SQL statements and queries.

DATA MODELLING,
MANAGEMENT AND
GOVERNANCE

TABLE OF CONTENTS
INTRODUCTION...........................................................................................................................1
Use case diagram.............................................................................................................................1
Entity relationship model.................................................................................................................2
Normalized Database.......................................................................................................................4
SQL statement to create table in database.......................................................................................5
SQL queries.....................................................................................................................................6
Design decisions, issues and challenges faced while completing this report..................................8
CONCLUSION................................................................................................................................9
REFERENCES..............................................................................................................................11

INTRODUCTION
Data modelling can be defined as a kind of process which is used to create visual
representation of either whole information system or helps in developing communication
between data points and structure (Miao and et. al., 2021). It is one of the most important part of
database. In database it helps in providing relationship between various type of information that
are to be stored within a database. It is an important part of database because it can help database
users in getting a basic overview of ways in which data within a database is stored in an
appropriate and effective manner. This report will lay emphasis upon designing a database
solution for sports club record system. For designing a database solution, first of all use case
diagram was designed for understanding amin users of system and ways in which they will be
interacting with the system, then Entity Relationship Model will be designed for providing a
brief overview of database structure of Sports Club Management System, SQL statements used
to create tables within database will also be provided, and lastly 2 SQL queries will be explained
for showing ways in which Sports Club members can view their booking. For development of
this database PhpMyAdmin will be used.
PhpMyAdmin
It is a free and open- source tool of administration for both MySQL and MariaDB. It isa
portable web- based application written in Php and has grown popular for MySQL
administration tools. Some of the most important features of PhpMyAdmin are: web interface,
MySQL and MariaDB database management, import SQL/ JSON/ CSV file, export file in
different format, administer multiple server.
Use case diagram
Use case diagram isa kind of UML diagram which is drawn for summarizing details of
system user or actors, and ways in which they interact with the system also known as use cases
(Fauzan and et. al., 2019). One of the main use of this diagram is to help programmer or
developer to understand ways in which current system will be used, who will be using the system
and ways in which they will be interacting with the system (Sabharwal, Kaur and Sibal, 2017). It
is a high- level orientation diagram that provides an overview of ways in which system, use case
and users interact with each other.
In this use case diagram of Sports Club management system there are two main actors
who will be interacting with the system. Two main actors interacting with the system are: sports
1

club staff members and sports club members. Both the users interact with the system in different
manner. Sports club members can only register themselves to the system, login and book
activities in which they want to participate whereas Sports club staff members can interact with
the system in variety of manner. Such as Sports club staff members can interact with the system
for updating members information, activities schedule, checking attendance, updating staff
members information, view daily booking activities etc. On the basis of these identified use cases
and actors, use case diagram of Sports club management system have been drawn in appendix:
Please refer appendix.
Entity relationship model
Entity relationship model is a kind of graphical representation of database. It is a high-
level data model which is used to define main elements of data and relationship they have with
the system (Kheirabadi., and et. al., 2019). This model directly helps in defining relationship
between different entities and attributes. In other words, it can be said that it helps in providing
design view of database for understanding overall structure of database and type of data which
will be stored within this database (El‐ Assady and et. al., 2017). There are three main elements
in ER model that are: entity, attribute and relationship. Entity is represented in rectangular box,
attributes are represented in eclipse and relationship in Diamond or rhombus.
In the below drawn ER model there are four main entities named as: club_member,
staff_members, activity_booking, and sports_class. Each of these entities have their own
attributes that helps in identifying ways in which data will be stored within Sports club
management system. Each of these entities in below drawn ER model are interrelated to each
other with the help of relationship. For example: staff_membmers can update Sports_class,
Club_members can book Activity_booking etc.
Please refer Appendix
Normalized Database
Normalization is a process of structuring a database in accordance with a series of normal
forms for reducing data redundancy and bring improvement within data integrity. Out of all
types of normal forms 3rd normal form is most commonly used and implemented (Albarak and
Bahsoon, 2018). Third normal forms is also known as 3NF which is based upon a normalized
principle of reducing duplication of data, ensure data integrity and simplify data management.
2

Tables stores in Sports Club management system database are normalized and all the normalized
tables have been shown below:
Staff_members table
Club members table
Sorts_cass table
Activity_booking table
3

All the above tables are in normalized form because all the above tables consist of lowest
data redundancy and are integrated to each other.
SQL statement to create table in database
There are four main tables in Sports Club management system database and each of them
have their own attributes (known as columns). Below given are create statements used for
creating all the four main tables of Sports Club management system database made in
PhpMyAdmin.
 Create table statement for table Club_member
CREATE TABLE `club_members` (
`member_reference_number` varchar (50) NOT NULL,
`M_first_name` varchar (50) NOT NULL,
`M_surname` varchar (50),
`M_address` varchar (50),
`telephone_number` bigint (10),
`M_email-address` varchar (50),
`dob` date,
`medical_condition` varchar (50),
PRIMARY KEY (member_reference_number)
);
 Create table statement for table staff-members
CREATE TABLE `staff_members` (
`Staff_number` varchar (50) NOT NULL,
`S_first_name` varchar (50) NOT NULL,
`S_surname` varchar (50),
`Role` varchar (50) NOT NULL,
`S_contact-number` bigint (50) NOT NULL
PRIMARY KEY (Staff_number)
);
 Create table statement for table sports_class
CREATE TABLE `sports_class` (
4

`Class_code` varchar (50) NOT NULL,
`title` varchar (50) NOT NULL,
`day` text NOT NULL,
`time_of_ delivery` time (6) NOT NULL
PRIMARY KEY (Class_code)
);
 Create table statement for table activity_booking
CREATE TABLE `activity_booking` (
`Booking_id` varchar (50) NOT NULL,
`member_reference_number` varchar (50) NOT NULL,
`Class_code` varchar (50) NOT NULL
PRIMARY KEY (member_reference_number)
);
ALTER TABLE `activity_booking`
ADD KEY `fk_foreign_key_name` (`member_reference_number`),
ADD KEY `fk_foreign_key_name1` (`Class_code`);
SQL queries
Query 1
How a sports club member can view their current bookings
(For individual member)
Using this query current booking of a particular sports cub member current bookings can
be identified.
SELECT activity_booking.Booking_id, activity_booking.member_reference_number, club_mem
bers.M_first_name, activity_booking.Class_code, sports_class.title
from activity_booking
JOIN sports_class ON activity_booking.Class_code = sports_class.Class_code JOIN club_memb
ers ON activity_booking.member_reference_number = club_members.member_reference_numb
er
5

WHERE activity_booking.member_reference_number = 'M103';
Output
(For all members)
Using this query current booking of all the sports club members can be viewed in an
appropriate manner.
SELECT activity_booking.Booking_id, activity_booking.member_reference_number, club_mem
bers.M_first_name, activity_booking.Class_code, sports_class.title FROM activity_booking
JOIN sports_class ON activity_booking.Class_code = sports_class.Class_code JOIN club_memb
ers ON activity_booking.member_reference_number = club_members.member_reference_numb
er;
Output
Query 2
Displays the current weekly activities for the sports club
This query will help in extracting information of activities held in this week for the
Sports club.
SELECT sports_class.Class_code, sports_class.title, sports_class.day, sports_class.time_of_deliv
ery
FROM sports_class;
Output
6

Design decisions, issues and challenges faced while completing this report
Design decisions
For development of Sports Club management system database following decisions were
taken are as follows: first of all, in this, four main tables were created as required and were
created in such a manner that they were interrelated to each other. In this an activity booking
table was created separately in which membership_reference_number of club members table and
class_code of sports class table was taken as a foreign key so that booking done by members and
for which sports or activity can be easily viewed or updated without affecting member table and
sports class table data. For development of this database PhpMyAdmin was used as it is one of
the most easily available databases that can be used for creating database. Not only this, it is
extremely easy to be used and can be understood by everyone. Due to this PhpMyAdmin was
used. PhpMyAdmin was suitable, cost effective and saved lots of time. Not only this, it further
helped in gaining much better understanding of SQL statements.
Issues and challenges
Issues and challenges faced while completing this report are as follows: one of the issues
is data security. Database created on PhpMyAdmin faces data security challenge the most. There
is no doubt that in this, different users can be created but it can e easily accessed by anyone due
to which its security reduces (Albarak and Bahsoon, 2018). Another issue associated with
PhpMyAdmin is that for manually inserting data or values in this database it is not suitable
because changes of error are high in this. Another issue with PhpMyAdmin is that it cannot
stored SQL queries fired or views created. Every time an SQL query is fired it cannot be saved
and if any kind of changes are required to be made in it or results are required to be viewed then
in both the case user has to again insert the SQL query and run for gaining access to its results.
7

While developing database in PhpMyAdmin one of the main challenges faced by me was
in terms of creating foreign key. As in this foreign key cannot be created directly. In order to
create foreign key it is important to use alter table for creating foreign key in any table in
PhpMyAdmin. It was one of the main challenges faced by me during this assessment.
Understanding SQL query error was another challenge faced during this assessment because if
any kind of error is generated during firing a query then in such case it becomes difficult to
understand what is the main error and in which part of query error has generated. It was another
challenge that was faced by me during this assessment. Another change was implementing SQL
query’s for extracting required results. It was a challenge because in this, queries cannot be
saved. Every time any kind of change in query was required to be made for identifying or
gaining appropriate answers, query was required to be inserted and run again and again as it
cannot be saved in this PhpMyAdmin database.
CONCLUSION
From the above report it has been concluded that data modelling is one of the most
important part of database as it helps in providing a brief overview of overall structure of
database. There are various kinds of data modes that can be used for getting an overview of ways
in which data is stored within a database. In the above report use case diagram has been drawn
for explaining number of users who uses the system, not only this, use case diagram had also
helped in explaining ways in which each of them will be interacting with the system. It can be
summarized that use case diagram helps in gaining understanding of the system. In the above
report ER model was also drawn of Sports Club management system database. ER diagram helps
in understanding number of entities in database, their attributes and relationship between those
entities. It directly helps in understanding and gaining an overview of database. From the above
report it has also been summarized that for creating a database it is important to have knowledge
of main entities and attributes of database so that tables and columns in it can be created. In the
above report create statements were used to create tables in PhpMyAdmin and two different
types of SQL queries were used for extracting required data from database.it has also been
concluded that there are variety of SQL statements that can be used for extraction of or accessing
data from database in an appropriate manner.
8

REFERENCES
Books and Journals
Albarak, M. and Bahsoon, R., 2018, May. Prioritizing technical debt in database normalization
using portfolio theory and data quality metrics. In Proceedings of the 2018 International
Conference on Technical Debt (pp. 31-40).
El‐ Assady, M., and et. al., 2017, June. NEREx: Named‐Entity Relationship Exploration in
Multi‐Party Conversations. In Computer Graphics Forum (Vol. 36, No. 3, pp. 213-225).
Fauzan, R., and et. al., 2019, July. Use case diagram similarity measurement: A new approach.
In 2019 12th International Conference on Information & Communication Technology
and System (ICTS) (pp. 3-7). IEEE.
Kheirabadi, M.A., and et. al., 2019. Making an entity-relationship model of the knowledge
management process with strategic thinking. Revista Latinoamericana de
Hipertension, 14(1), pp.55-62.
Miao, A., and et. al., 2021. Locality‐preserving data modelling and its application in fault
classification. The Canadian Journal of Chemical Engineering.
Sabharwal, S., Kaur, P. and Sibal, R., 2017. Empirical and Theoretical Validation of a Use Case
Diagram Complexity Metric. International Journal of Information Technology and
Computer Science, 9(11), pp.35-47.
9

APPENDIX
Use Case Diagram
ER model:
10

11