Unit 4 Database Design and Development Assignment Help - Level 4 BTEC HND in Computing

Introduction

The aim of the report is to present information about the database design, development and implementation of a given case study, thecase is about developing a database for ICON College Summer games. In the following sections of the report, there are information about the background of the case, system requirements and business rules, entity relationship diagram, normalisation process for mapping the conceptual ER diagram into a suitable logical diagram that can be implemented using the MySQL platform, following the normalisation, the database design phase is completed, so there is an assessment of the database from the perspective of the users' requirements and from the perspective of the system requirements. Following the design phase, the database development and implementation process will start, the development phase includes the activities related to creation of the tables, foreign keys, and populating the same. It will require coding in SQL. SQL is a DDL or Data Definition Language and DML or Data Manipulation Language for all relational databases. Following the implementation of the database using the MySQL platform, there is the testing phase and the explanation of the steps taken to test the database. It will check whether the database is capable for insertion, deletion, and manipulation of data or not, whether all users requirements and system requirements are met or not and so on. Finally there will be a reflective discussion on the recommendations about the database.

Background

ICON College arranges an amusement competition in late spring. The event comprises of various activities like table and card games like monopoly, as well as physical field games like basketball. Students are allowed to participate in the activities as per their interests.

The college management is aiming at developing a suitable relational database that will help to keep records of the registered players, events and activities, achievements and the awards given to the winners.

The assumptions for the database are,
• The database will contain records related to the amusement competition only. It will not track the records of other students' academic details.
• The ER diagram will be developed using the crows' foot notation.

System Requirements and Business Rules

The identified entities, business rules and relationships for the proposed database for the ICON College have been given below.

The business rules are,
• Each event can have multiple participants and each participant can participate in multiple events.
• Each event has more than one match. Each match belongs to a single event
• Each match has two or more participations. Each participation belongs to a single match
• A student can have more than one participation or no participation at all. Each participation is for a single student.
• Each match has one or more associated results. Each result is related to a single match.

The system requirements for the proposed database solution for the ICON college amusement competition are,
• The database must be capable of maintaining details of the events, participants, matches, results and the overall standing of the tournament
• Queries must be executed on the underlying database.
• Users must be able to insert data into the database
• Users must be able to manipulate the data in the database
• Users must be able to delete data from the database if needed
• Users must be able to update data on the database.

ER Diagram
An entity relationship or ER diagram models the real-world entities to be captured by the database and the relationships between entities. The ER diagram is,

Normalization
Normalisation is a process of restricting a database in such a way that the data redundancy issues are reduced and the overall data integrity is improved. There are various normal forms. Depending on various conditions of each normal form, a relation goes through and the outcome is a set of relations satisfying the rules of the last normal form.

Functional dependencies are important for normalisation. The functional dependencies in the current ER diagram are listed below,
FD 1: idEvent → {nameEvent, typeEvent, startDateEvent, finishDateEvent}
FD 2: idMatch→nameMatch, dateMatch
FD 3: idEvent, idMatch is trivial dependency
FD 4: idStudent → {nameStudent, genderStudent, yearStudent, addressStudent, phoneNumber, email}
FD 5: idMatch, idWinner→ score, award
FD 6: idParticipation→idMatch, idStudent
Currently, the database has the following relational schema.
EVENT (idEvent, nameEvent, typeEvent, startDateEvent, finishDateEvent)
MATCH (idMatch, nameMatch, dateMatch)
MATCHLIST (idEvent, idMatch)
STUDENT (idStudent, nameStudent, genderStudent, yearStudent, addressStudent, phoneNumber, email)
RESULT (idMatch, idWinner, score, award)
PARTICIPATION (idParticipation, idMatch, idStudent)

1st Normal Form
Based on the given information about the relational schema and the set of FDs, it is clear that each entity has primary keys that will help to identify each of record in each relation, all attributes of each relation are atomic. Hence, the set of relations in 1NF are,
EVENT (idEvent, nameEvent, typeEvent, startDateEvent, finishDateEvent)
MATCH (idMatch, nameMatch, dateMatch)
MATCHLIST (idEvent, idMatch)
STUDENT (idStudent, nameStudent, genderStudent, yearStudent, addressStudent, phoneNumber, email)
RESULT (idMatch, idWinner, score, award)
PARTICIPATION (idParticipation, idMatch, idStudent)

2nd Normal Form
It shows that there is no partial dependency on any of the relation, hence all are in 2NF also.
EVENT (idEvent, nameEvent, typeEvent, startDateEvent, finishDateEvent)
MATCH (idMatch, nameMatch, dateMatch)
MATCHLIST (idEvent, idMatch)
STUDENT (idStudent, nameStudent, genderStudent, yearStudent, addressStudent, phoneNumber, email)
RESULT (idMatch, idWinner, score, award)
PARTICIPATION (idParticipation, idMatch, idStudent)

3rd Normal Form
It is clear that there is no transitive dependency. Hence, all are in 3NF also.
EVENT (idEvent, nameEvent, typeEvent, startDateEvent, finishDateEvent)
MATCH (idMatch, nameMatch, dateMatch)
MATCHLIST (idEvent, idMatch)
STUDENT (idStudent, nameStudent, genderStudent, yearStudent, addressStudent, phoneNumber, email)
RESULT (idMatch, idWinner, score, award)
PARTICIPATION (idParticipation, idMatch, idStudent)

Assessment of the Database for Users and System Requirements
Data has become an important asset for any business. So security and maintenance is important to develop and manage a database. The relational design of the proposed database will help to implement the same completely into a fully functional physical database. The normalisation process has helped to produce a database design that has the minimal redundancy and the data integrity has been preserved. The foreign keys will help to implement and maintain the data integrity constraints. It will also help to ensure that the relationships and the business requirements have been properly met by the database.

Database Development
The database has been developed based on the following data dictionary. The same data types and other details have been implemented using the MySQL platform. MySQL is a leading open source platform to develop and run relational database management systems. It is used with the SQL language. The phases of the physical database development process are,
• mapping the relational schema into the physical design by creating the tables using CREATE TABLE command
• Then the tables are populated with sample data using the INSERT INTO command.
• Then the queries are written in SQL code and executed. The results are also collected for the further testing part.

The data dictionary is,

Implementation using MySQL

Creation of the tables,
create database icon3;
use icon3;

create table _Event(
idEvent INT NOT NULL,
nameEvent varchar(100) NOT NULL,
typeEvent varchar(50) NOT NULL,
startDateEvent Date NOT NULL,
finishDateEvent Date NOT NULL,
Primary Key(idEvent)
);

INSERT INTO _Event values (1, 'Card Game', 'Indoor', '2017-08-01', '2017-08-01');
INSERT INTO _Event values (2, 'Card Game 2', 'Indoor', '2018-10-01', '2018-10-01');
INSERT INTO _Event values (3, 'Card Game 3', 'Indoor', '2018-10-02', '2017-10-03');
INSERT INTO _Event values (4, 'Football', 'Outdoor', '2018-10-04', '2017-10-06');
INSERT INTO _Event values (5, 'Basketball', 'Outdoor', '2018-10-06', '2017-10-08');

Create Table _Match(
idMatch INT NOT NULL,
nameMatch varchar(100) NOT NULL,
dateMatch Date NOT NULL,
idEvent INT NOT NULL,
primary key (idMatch),
foreign key (idEvent) references _Event(idEvent)
);

INSERT INTO _Match values (1, 'Card Game 1 Match 1', '2017-08-01', 1);
INSERT INTO _Match values (2, 'Card Game 1 Match 2', '2017-08-01', 1);
INSERT INTO _Match values (3, 'Card Game 2 Match 1', '2018-10-01', 2);
INSERT INTO _Match values (4, 'Card Game 2 Match 2', '2018-10-01', 2);
INSERT INTO _Match values (5, 'Card Game 3 Match 1', '2018-10-02', 3);
INSERT INTO _Match values (6, 'Card Game 3 Match 2', '2018-10-02', 3);
INSERT INTO _Match values (7, 'Football', '2018-10-04', 4);
INSERT INTO _Match values (8, 'Basketball', '2018-10-06', 5);

Create Table _Student(
idStudent INT NOT NULL,
nameStudent Varchar(100) NOT NULL,
genderStudent varchar(10) NOT NULL,
yearStudent INT NOT NULL,
addressStudent varchar(250) NOT NULL,
phoneNumber char(10) NOT NULL,
email varchar(100) NOT NULL,
primary key (idStudent)
);

INSERT INTO _Student values (1, 'John Smith', 'Male', 2, 'address 1', '1234567890', '[email protected]');
INSERT INTO _Student values (2, 'Jordan', 'Male', 1, 'address 2', '1325963589', '[email protected]');
INSERT INTO _Student values (3, 'Kerry', 'Female', 4, 'address 3', '1236985202', '[email protected]');

Create Table _MatchList(
idEvent INT NOT NULL,
idMatch INT NOT NULL,
primary key (idEvent, idMatch),
foreign key (idEvent) references _Event(idEvent),
foreign key (idMatch) references _Match(idMatch)
);

Insert Into _MatchList values (1, 1);
Insert Into _MatchList values (1, 2);
Insert Into _MatchList values (2, 3);
Insert Into _MatchList values (2, 4);
Insert Into _MatchList values (3, 5);
Insert Into _MatchList values (3, 6);
Insert Into _MatchList values (4, 7);
Insert Into _MatchList values (5, 8);

Create Table _Result(
idMatch INT NOT NULL,
idWinner INT NOT NULL,
score INT NOT NULL,
award Varchar(100) NOT NULL,
primary key (idMatch, idWinner),
foreign key (idWinner) references _Student(idStudent),
foreign key (idMatch) references _Match(idMatch)
);

Insert into _Result values (1, 1, 4, 'Yes');
Insert into _Result values (2, 1, 5, 'Yes');
Insert into _Result values (3, 1, 10, 'Yes');
Insert into _Result values (4, 2, 12, 'Yes');
Insert into _Result values (5, 3, 8, 'Yes');
Insert into _Result values (6, 3, 4, 'Yes');
Insert into _Result values (7, 3, 5, 'Yes');
Insert into _Result values (8, 1, 7, 'Yes');

Create Table _Participation(
idParticipation INT NOT NULL,
idMatch INT NOT NULL,
idStudent INT NOT NULL,
primary key (idParticipation),
foreign key (idStudent) references _Student(idStudent),
foreign key (idMatch) references _Match(idMatch)
);
Insert into _Participation values (1, 1, 1);
Insert into _Participation values (2, 1, 2);
Insert into _Participation values (3, 2, 1);
Insert into _Participation values (4, 2, 2);
Insert into _Participation values (5, 3, 1);
Insert into _Participation values (6, 3, 2);
Insert into _Participation values (7, 4, 2);
Insert into _Participation values (8, 4, 1);
Insert into _Participation values (9, 5, 3);
Insert into _Participation values (10, 5, 2);
Insert into _Participation values (11, 6, 3);
Insert into _Participation values (12, 6, 1);
Insert into _Participation values (13, 7, 3);
Insert into _Participation values (14, 7, 2);
Insert into _Participation values (15, 8, 1);
Insert into _Participation values (16, 8, 3);

Queries

Query 1
Select * from _event;

It lists the details of the available events in the tournament.

Query 2
Select * from _student;

It lists the details of the players including players' names, addresses, phone numbers and email addresses.

Query 3
Select _event.idEvent, nameEvent, startDateEvent, nameStudent
From ((_event INNER JOIN _match on _event.idEvent = _match.idEvent) INNER JOIN _participation ON _match.idMatch = _participation.idMatch) INNER JOIN _student ON _participation.idStudent = _student.idStudent
WHERE nameStudent = 'John Smith'
ORDER BY startDateEvent ASC;

It shows the name of the events in which the student John Smith has participated in the ascending order by the start date or date of the event.

Query 4
Select nameStudent, COUNT(idMatch) AS NumMatchesWon
From _student INNER JOIN _result ON _student.idStudent = _result.idWinner
GROUP BY idWinner
Having COUNT(idMatch)>3;

It shows the name of the student and the number of matches won by the student when the number of matches won by the student is more than 3.

Query 5
Select DISTINCT _participation.idStudent, nameStudent, dateMatch, _match.idEvent, nameEvent, nameMatch, startDateEvent
FROM ((_participation INNER JOIN _match ON _participation.idMatch = _match.idMatch) INNER JOIN _student ON _participation.idStudent = _student.idStudent) INNER JOIN _event ON _match.idEvent = _event.idEvent
WHERE startDateEventBetween'2017-08-01' AND '2017-08-31';

It shows the list of all the participant IDs, names, match-date, event ID and event name played in
August 2017.

Query 6
Update _student SET addressStudent = 'London' Where idStudent = 1;
Select * from _student;

It has changed the address of the student with student ID 1 from ‘address 1' to ‘London'.

Query 7
Select COUNT(idMatch) AS NumMatchesInOct2018
FROM _match
WHERE dateMatch BETWEEN '2018-10-01' AND '2018-10-31';

It shows the number of matches played in the month of October 2018.

System Testing
1. Command: Show Tables;
It shows all tables present in the database. The output is given below.

2. Command: Select * from _event;

3.Command: Select * from _match;

4. Command: Select * from _matchlist;

5. Command:Select * from _participation;

6. Command: Select * from _result;

7. Command: Select * from _student;

Test outcome

The testing process has been executed in parallel to the database development and the implementation process. The phases of the testing process have been described below.
• While designing the database, it was tested whether the business rules and data integrity constraints have been preserved or not, whether the normalisation process was executed properly or not, and whether the functional dependencies were preserved after the normalisation process or not.
• Then during the database development and implementation process, the codes were inspected to ensure typing errors. Once the database was developer, data was carefully inserted according to the business rules, user requirements, and the data integrity constraints.
• Finally, while executing the queries, it was tested whether there were all tables are present in the database or not, whether all tables are populated with the suitable records or not, whether the data is adequate for testing or not.
• Then there is the table that contains the details of the use cases. It helps to compare the expected outcomes and the actual outcomes of different use cases in details, and the comments are also added to show whether the outcomes match or not.

Movement of Data and the System

The following diagram shows how it can be used by the users to insert, update and manipulate data in the system.

To insert data, user can use the user friendly forms available on the MySQL interface or can use web based forms connected to the database. it will also help to update the data and queries can be written using SQL to run on the database.

Recommendations
It is recommended for the ICON College must use the database for their tournament management process. It will help them to organise data electronically and in future it will help to manage and manipulate the data faster.