Database Development and Design

This document provides an overview of database development and design for the Mt Albert football club. It includes information on business requirements, expected project outcomes, technical and administrative details, ER diagram, relation schemas, and database implementation and testing.

DATABASE DEVELOPMENT AND DESIGN
DATABASE DEVELOPMENT AND DESIGN
Name of the Student
Name of the University
[Class and ID]
Author Note
[Student name] [Student ID] Page 0 of 25

DATABASE DEVELOPMENT AND DESIGN
Table of Contents
Part A...............................................................................................................................................2
1. Business Overview...............................................................................................................2
2. Business requirements..........................................................................................................2
3. Expected project outcomes...................................................................................................3
4. Technical and administrative details....................................................................................3
Part B...............................................................................................................................................5
5. ER diagram...........................................................................................................................5
6. Relation schemas..................................................................................................................6
7. Normal forms the schema satisfies.......................................................................................6
Part C...............................................................................................................................................9
8. Database implementation......................................................................................................9
a. Creating the database........................................................................................................9
b. Creating the tables and their relationships......................................................................11
9. Database testing..................................................................................................................11
c. Populate your database with at least 20 test records.......................................................11
d. Test insertion of records..................................................................................................15
e. Test deletion of records...................................................................................................15
f. Test relationship between the tables...............................................................................15
Bibliography..............................................................................................................................16
[Student name] [Student ID] Page 1 of 25

DATABASE DEVELOPMENT AND DESIGN
Part A
1. Business Overview
The organization Mt Albert football club is a football association that focuses on offering
different opportunities towards the payers and employees. The club has huge number of
players and employees that focuses entirely on providing a better support towards the
activities that are conducted by the clubs. The players are being identified with the help of
their unique player ID that stores all necessary details including names, address and phone
number. The other important employees associated with the club includes physios specialized
in different fields. Each employees associated with the club are being identified with the use
of unique employee ID. The main aim of having physios is to ensure that proper health
conditions are maintained by each payer associated with the club. Each player can have more
physios that will be appointed by their respective coaches. The aim of this assessment is to
design an effective relational database schema that will help in representing the system
information. As a database designer it is important to ensure that all data are implemented
successfully within the database and the developed system is able to meet the needs of the
organization.
2. Business requirements
The client in this organization manly aims at managing the data related to each
employees and players efficiently. The designed database will store all the necessary details
regarding players including their unique ID, name, address, and phone numbers within the
system. This data will help the club to have a clear control over the football association.
Apart from this the developed database will also store the details regarding physios that are
being assessed towards the employees. This will ensure that the information system is able to
[Student name] [Student ID] Page 2 of 25

DATABASE DEVELOPMENT AND DESIGN
address all the necessary data on time. In addition to this the development database will also
store all the essential and unique details regarding each employees.
3. Expected project outcomes
It is expected that with the help of designing a database for Mt Albert football club it will
become easy to manage the data that are stored within the system. The main deliverables that
are associated with the project includes developing a database that will store all the details
associated with the players, employees, coaches and the physicians. This details are essential
and thus it becomes essential to prepare a proper database system that will store all the
information. The project will include database implementation based on the ER diagram and
relational schemas. The second part that will be included within the database includes testing
the developed database and storing the information regarding each activity that takes place
within the club. The ERD will also contain a list of tournaments that are going to be held on
within the club.
4. Technical and administrative details
a. DBMS software used: the software that is being used for creating the database is
MySQL Workbench. The main reason behind using this software is that it provides an
effective visual console towards the administrative MySql environment so that it
becomes easy to gain better visibility towards the database. The ER diagram will be
developed for the purpose of referencing with the MYSQL workbench. This will help at
the time of storing the data regarding the football club. In addition to this the MySql
workbench also offers effective way of managing the backup and recovery plans so that
each data can be maintained securely within the system.
[Student name] [Student ID] Page 3 of 25

DATABASE DEVELOPMENT AND DESIGN
b. Backup: for backup purpose within the database it is important to export the database to
a file within the local system that will be referred to as the data dump. The steps for
performing the backup within the database are listed below:
Step 1: choosing the data export that is listed under the server tab
Step 2: after this the user needs to select the database on which backup function will be
performed
Step 3: After this the user needs to select the tables for performing the export
Step 4: the user needs to choose a location where database backup needs to be saved.
After this the file needs to be export to dump project folder and the database needs to be
included with all the selected tables.
Step 5: after this the user needs to start export and should the password of the database
After all this steeps are executed successfully the user needs to restore the database from
the previously made backup.
Restoring the database
Step1: choosing the database that is to be imported
Step 2: clicking the data import under server tab section
[Student name] [Student ID] Page 4 of 25

DATABASE DEVELOPMENT AND DESIGN
Step 3: entering the database password that is set by the system
Step 4: importing the data from dump project folder
Step 5: after this the user needs to select Import from Self-Contained File and enter the
location in the File Path text field
Step 6: the data gets backed up based on the size and each data gets stored successfully
within the system
c. Recovery plans
With the help of a recovery plan it will become easy to manage the data stored within the
system. The steps that are essential for recovery plan are described below:
Step1: choosing the database that is to be imported
Step 2: clicking the data import under server tab section
Step 3: entering the database password that is set by the system
Step 4: importing the data from dump project folder
Step 5: after this the user needs to select Import from Self-Contained File and enter the
location in the File Path text field
Step 6: the data gets backed up based on the size and each data gets stored successfully
within the system
Part B
5. ER diagram
[Student name] [Student ID] Page 5 of 25

DATABASE DEVELOPMENT AND DESIGN
(Figure 1: ER diagram)
6. Relation schemas
Player (PlayerID (pk), PlayerName, PlayerAddress, Player_phno)
Employee details (EmployeeID (PK), Designation, Contact_emp, emailid, Emp_name)
Physios (PhysiosID (PK), Physio_name, Specialization, PlayerID (FK), Officelocation,
Contactno)
Coach (CoachID (pk), Coach_name, Player_ID (fk), EmployeeID (FK))
[Student name] [Student ID] Page 6 of 25

DATABASE DEVELOPMENT AND DESIGN
7. Normal forms the schema satisfies
Player (PlayerID (pk), PlayerName, PlayerAddress, Player_phno)
Player table is stated to be in 1NF as it includes single value and contains unique
attributes for the field PlayerID.
The table Player is stated to be in 2NF as it contains a column with primary key.
The table can be stated in 3NF as it lies in 2NF and does not contains any transitive
functional dependencies.
[Student name] [Student ID] Page 7 of 25

DATABASE DEVELOPMENT AND DESIGN
Employee details (EmployeeID (PK), Designation, Contact_emp, emailid,
Emp_name)
Employee details is the table that is considered to be in 1NF as it contains single value
and holds unique data for the field EmployeeID.
The table Employee details is stated to be in 2NF as it contains a column with primary
key.
The table can be stated in 3NF as it is already present within the 2NF and does not
contains any transitive functional dependencies.
Physios (PhysiosID (PK), Physio_name, Specialization, PlayerID (FK),
Officelocation, Contactno)
Physios is stated to be in 1NF as it includes single value and contains unique attributes
for the field PhysiosID.
The Physios is stated to be in 2NF as it contains a column with primary key and two
columns with foreign key respectively.
The table can be stated in 3NF as it lies in 2NF and does not contains any transitive
functional dependencies.
Coach (CoachID (pk), Coach_name, Player_ID (fk), EmployeeID (FK)
[Student name] [Student ID] Page 8 of 25

DATABASE DEVELOPMENT AND DESIGN
Coach is stated to be in 1NF as it includes single value and contains unique attributes for
the field CoachID.
The table Coach is stated to be in 2NF as it contains a column with primary key along
with three foreign key.
The table can be stated in 3NF as it lies in 2NF and does not contains any transitive
functional dependencies.
Part C
8. Database implementation
a. Creating the database
[Student name] [Student ID] Page 9 of 25

DATABASE DEVELOPMENT AND DESIGN
[Student name] [Student ID] Page 10 of 25

DATABASE DEVELOPMENT AND DESIGN
[Student name] [Student ID] Page 11 of 25

DATABASE DEVELOPMENT AND DESIGN
[Student name] [Student ID] Page 12 of 25

DATABASE DEVELOPMENT AND DESIGN
[Student name] [Student ID] Page 13 of 25

DATABASE DEVELOPMENT AND DESIGN
b. Creating the tables and their relationships
[Student name] [Student ID] Page 14 of 25

DATABASE DEVELOPMENT AND DESIGN
9. Database testing
c. Populate your database with at least 20 test records
INSERT INTO `player` (`PlayerID`, `PlayerName`, `PlayerAddress`,
`Player_phno`) VALUES
(11, 'Eden Hazard', '10/A, California ', 983663903), (12, 'Gareth Bell ',
'Mellbourne ', 278449040), (13, 'Luka Jovic ', 'Chicago', 27389404), (14,
'Cristiano ', '23/B, Chicago', 234689), (15, 'Neymar Louis ', 'Brazil', 234567), (18,
'Juan Foyth ', '12/A, brazil', 1235666), (19, 'Ryana Marie', 'Chicago', 24784959),
(21, 'Patrick Johnes', 'Brazil', 567900), (23, 'Andrew Russell', 'Brazil', 678990),
(24, 'Rivi Suzzan ', 'Paris ', 27383993), (25, 'Ritviz Suzane', 'Mellbourne',
23485905), (28, 'Diego Russell ', '20/3, Chicago', 3456878), (30, 'Xander Dutch ',
'12/B, Chicago', 23839005), (31, 'Ruis Smith ', 'Chicago', 3457216), (35,
'Adelmo', 'Brazil ', 36472992), (40, 'Giotto', 'Brazil', 234689), (43, 'Abbott', 'Paris
', 234696), (45, 'Robert pattinson ', 'chicago', 3446767);
[Student name] [Student ID] Page 15 of 25

DATABASE DEVELOPMENT AND DESIGN
INSERT INTO `coach` (`CoachID`, `Coach_name`, `PlayerID`, `EmployeeID`)
VALUES ('02', 'Marco Smith', '18', '108'), ('01', 'Riza Frass', '30', '102');
INSERT INTO `coach` (`CoachID`, `Coach_name`, `PlayerID`, `EmployeeID`)
VALUES ('03', ' Smith Jolie', '12', '106'), ('06', 'Sussane ', '45', '108');
INSERT INTO `coach` (`CoachID`, `Coach_name`, `PlayerID`, `EmployeeID`)
VALUES ('07', 'Crish Marie', '23', '101'), ('08', 'Runa Mathew', '21', '105');
INSERT INTO `coach` (`CoachID`, `Coach_name`, `PlayerID`, `EmployeeID`)
VALUES ('10', 'Ross Grace ', '45', '104'), ('05', 'Charles Smith', '31', '101');
[Student name] [Student ID] Page 16 of 25

DATABASE DEVELOPMENT AND DESIGN
INSERT INTO `coach` (`CoachID`, `Coach_name`, `PlayerID`, `EmployeeID`)
VALUES ('02', ' Smith Marie', '24', '106'), ('04', 'Charles Johnes', '43', '109');
INSERT INTO `physios` (`PhysioID`, `Physio_name`, `Specialization`,
`PlayerID`, `Officelocation`, `Contactno`) VALUES ('3', 'Smith Steve ',
'Acupuncture', '28', 'California ', '238394040'), ('6', 'Cryss Sussain', 'Chiropractic',
'19', 'Mellbourne', '23467212');
INSERT INTO `physios` (`PhysioID`, `Physio_name`, `Specialization`,
`PlayerID`, `Officelocation`, `Contactno`) VALUES ('5', 'Sizuka Leo',
'Acupuncture', '13', 'California ', '2357912'), ('10', 'Louis Henry ', 'Chiropractic',
'24', 'Mellbourne', '37467590');
[Student name] [Student ID] Page 17 of 25

DATABASE DEVELOPMENT AND DESIGN
INSERT INTO `physios` (`PhysioID`, `Physio_name`, `Specialization`,
`PlayerID`, `Officelocation`, `Contactno`) VALUES ('11', 'Ritzz James',
'Orthopedic', '13', 'California ', '52113409'), ('7', 'Anthrew Suzane', 'Chiropractic',
'19', 'Mellbourne', '23374849');
INSERT INTO `employee details` (`EmployeeID`, `Designation`, `Contact_emp`,
`emailid`, `Emp_name`) VALUES
(101, 'Manager', 2093040050, '[e-mail]', 'Albert Rose'),
(102, 'HR ', 903974667, '[e-mail]', 'Siri Suit'),
(103, 'Operationa', 162784905, '[e-mail]', 'Lucifer'),
(104, 'Project le', 35489606, '[e-mail]', 'Abbas Robert'),
[Student name] [Student ID] Page 18 of 25

DATABASE DEVELOPMENT AND DESIGN
(105, 'Manager', 26374884, '[e-mail]', 'Lisa smith '),
(106, 'IT supervi', 2567890, '[e-mail]', 'Alis Rose'),
(107, 'Employee', 12345657, '[e-mail]', 'Ritcha rogdg'),
(108, 'IT manager', 25367384, '[e-mail]', 'Stavros Hayd'),
(109, 'manager', 2345689, 'cristopher20@gmail.c', 'Cristopher ');
d. Test insertion of records
INSERT INTO `employee details` (`EmployeeID`, `Designation`, `Contact_emp`,
`emailid`, `Emp_name`) VALUES ('104 ', 'Project leader', '35489606',
'[e-mail]', 'Abbas Robert');
[Student name] [Student ID] Page 19 of 25

DATABASE DEVELOPMENT AND DESIGN
e. Test deletion of records
DELETE FROM physios WHERE PhysioID='2';
f. Test relationship between the tables
INSERT INTO `physios`(`PhysioID`, `Physio_name`, `Specialization`,
`PlayerID`, `Officelocation`, `Contactno`) VALUES (3,'Riza
Menon','Orthopedic','20','Melbourne',83940292)
[Student name] [Student ID] Page 20 of 25

DATABASE DEVELOPMENT AND DESIGN
Export file for the database
[Student name] [Student ID] Page 21 of 25

DATABASE DEVELOPMENT AND DESIGN
[Student name] [Student ID] Page 22 of 25

DATABASE DEVELOPMENT AND DESIGN
Bibliography
Coronel, C., & Morris, S. (2016). Database systems: design, implementation, & management.
Cengage Learning.
Patro, S., & Sahu, K. K. (2015). Normalization: A preprocessing stage. arXiv preprint
arXiv:1503.06462.
Kumar, L., Rajawat, S., & Joshi, K. (2015). Comparative analysis of nosql (mongodb) with
mysql database. International Journal of Modern Trends in Engineering and Research, 2(5),
120-127.
Al-Masree, H. K. (2015). Extracting Entity Relationship Diagram (ERD) from relational
database schema. International Journal of Database Theory and Application, 8(3), 15-26.
Stanescu, L., Brezovan, M., & Burdescu, D. D. (2016, September). Automatic mapping of
MySQL databases to NoSQL MongoDB. In 2016 Federated Conference on Computer
Science and Information Systems (FedCSIS) (pp. 837-840). IEEE.
Van Aken, D., Pavlo, A., Gordon, G. J., & Zhang, B. (2017, May). Automatic database
management system tuning through large-scale machine learning. In Proceedings of the
2017 ACM International Conference on Management of Data(pp. 1009-1024). ACM.
Arulraj, J., & Pavlo, A. (2017, May). How to build a non-volatile memory database management
system. In Proceedings of the 2017 ACM International Conference on Management of
Data(pp. 1753-1758). ACM.
[Student name] [Student ID] Page 23 of 25

DATABASE DEVELOPMENT AND DESIGN
Kamara, S., & Moataz, T. (2018, December). SQL on structurally-encrypted databases.
In International Conference on the Theory and Application of Cryptology and
Information Security (pp. 149-180). Springer, Cham.
[Student name] [Student ID] Page 24 of 25