ICON College of Technology and Management

Programme: BTEC HND in Computing

Unit Number and Name: Unit 4 Database Design and Development

Level: Level 4

Assignment Title - Database Design and Development

ASSIGNMENT

Case Study: Developing a database for ICON Summer Games

ICON College holds an amusement competition in the late spring comprising of numerous activities ranging from table/card games such as monopoly to hard physical field games such as basketball. Students will be able to show interest and participate in any activity of their choice.

Records will be kept for each registered player, participating events/activities, their achievements in such events and awards given. Records will also be kept for each event, participating registered players, past records/goals to beat, participants' scores/activities to identify winners and present awards, and criteria/rules for participation.

Each match will be refereed to guarantee that reasonable play is observed and to pass judgment on the possible winners.

The summer game requires a design of a database with the above specifications indicating necessary keys and relationships. An Entity Relationship Diagram needs to be drawn for the entities along with their attributes such that the database is in the third normal form (Apply 1NF, 2NF and 3NF).

Summary: Design a relational database that is capable of maintaining Events, Participants/Players, Matches, Results and overall tournament standing.

LO1: Use an appropriate design tool to design a relational database system for a substantial problem

It is important to show the different components of the case study data that illustrates the logical structure of the tables that makes up the database. In this view, you are required to illustrate the data structures and relationship of the tables extracted by designing the Entity Relational Diagram. Your implementation should illustrate at least four (4) inter-related tables resolving "many to many" relationships if there are any. It is necessary to explain any assumptions made for the user and system requirements.
From the tables extracted, ensure to list all the attributes. The aim of normalization is to reduce duplications. You are to produce a well normalized database up the third Normal Form following your listing specifically identifying the primary and foreign keys. The effectiveness of database design is usually assessed through testing. Assess your database design in relation to the user and system requirements.

LO2: Develop a fully functional relational database system, based on an existing system design

After a successful database design, the next step is to develop the database using the structured query language. Using your design as a guide, develop your database by all the tables using Structured Query Language with necessary attributes and declare primary and foreign keys where necessary. Ensure your implementation is justified to meet user requirements. The tables created must be populated with records of at least five (5) entries for each table to enhance querying your database.

There are different tools or applications or platforms that helps in developing and querying the database e.g. Ms SQL Server. You are required to use a visual tool to demonstrate the extraction of meaningful data through the implementation of query languages. To justify the use of SQL, it is important to include screenshots of the SQL and the query outcome. To enhance your understanding, ensure to evaluate each of the queries in answering the transactions. These transactions are itemized as below:

a) List all the available events in the tournament.
b) List all the players' names, addresses, phone numbers and email addresses.
c) List all the events that participant John Smith is registered for in ascending order by date.
d) List participants' names who won more than 3 matches
e) List all the participant IDs, names, match-date, event ID and event name played in August 2017.
f) Update a participant's address from a city to London
g) Count all matches in October 2018.

Note that it is mandatory to provide the SQL statement and show the output from the query in the form of screenshots.

Due to the data-specific nature of databases, it is important that they are secured and maintained. To reflect your understanding of database security and maintenance, you are required to assess how these are ensured in your implementation of the fully functional database system in accordance with users and system's requirements. Suggest how to better write or structure the languages in future use.

LO3: Test the system against user and system requirements.

It is necessary to test database and in the process of successfully carrying out testing, a test plan suffice. In your report, outline how the system has been tested against users and system's requirements. This test plan preferably to be in a table format illustrating at least six (6) records tested. Ensure to have "Test Description", "Expected Outcome", "Actual Outcome" as headings. The "Actual Outcome" heading should include a visual representation such as screenshots of results and annotations.
From the test plan created, you are to explain the different database testing techniques and assess with evidence, one of the testing techniques implemented on your database development. You are required to implement and test the verification and validation process with above query transaction from the database illustrating the understanding of the various features of SQL (update, sorting, joining tables, conditions using the where clause, grouping, set functions, sub-queries etc.). In your report, include recommendations on how you can improve your database development.

LO4:
Produce technical and user documentation
Documentation helps in understanding the concept of database development. To reflect your understanding of technical and user documentation, you are required to produce a fully technical and user documentation for your designed database for the college. Your documentation should include diagrams showing movement of data through the system, and flowcharts describing how the system works.
Enhancing database development is paramount in completing the development cycle. You are required to assess any future improvements that may be required to ensure the continued effectiveness of the database system.

Learning Outcomes and Assessment Criteria

 

Pass

 

Merit

 

Distinction

 

LO1 Use an appropriate design tool to design a relational database system for a substantial problem

P1 Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with clear statements

of user and system requirements.

M1 Produce a comprehensive design for a fully functional system which includes interface and output designs, data validations and data

normalisation.

D1 Assess the effectiveness of the design in relation to user and system requirements.

 

LO2 Develop a fully functional relational database system, based on an existing system design

 

 

P2 Develop the database system with evidence of user interface, output and data validations, and querying across multiple tables.

 

P3 Implement a query language into the relational database system.

M2 Implement a fully functional database system which includes system security and database maintenance.

 

M3 Assess whether meaningful data has been extracted through the use of query tools to produce appropriate management information.

 

 

 

 

 

LO2 & 3

D2 Evaluate the effectiveness of the database solution in relation to user and system requirements, and suggest improvements.

 

LO3 Test the systems against user and system requirements

P4 Test the system against user and system requirements.

M4 Assess the effectiveness of the

testing, including an explanation of the choice of test data used.

 

LO4 Produce technical and user documentation

 

M5 Produce technical and user

D3 Assess any future improvements that may be required to ensure the continued effectiveness of the database system.

 

documentation for a fully functional

P5 Produce technical and user

system, including diagrams

documentation.

showing movement of data through

 

the system, and flowcharts

 

describing how the system works

RELATED COURSES & ASSIGNMENT SERVICE!!


COMMENTS(0)

LEAVE A COMMENT


Captcha

 

 

Are You Looking for 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.

Entity

Short Description of Entity

Attribute

Short Description of the attribute

Event

An event is a game.

 

 

 

 

idEvent

Unique code to represent each event. An event is a game played in the amusement competition.

 

 

nameEvent

Name of the event.

 

 

typeEvent

Type of an event. It can be either indoor game or outdoor game.

 

 

startDateEvent

Start date of an event

 

 

finishDateEvent

Finish date of an event.

MatchList

List of the matches related to an event

 

 

 

 

idEvent

Reference to the event

 

 

idMatch

Reference to the match

Student

A student must be a student of the ICON College and a participant to an event.

 

 

 

 

idStudent

Unique ID of each student.

 

 

nameStudent

Full name of a student

 

 

genderStudent

Gender of a student. It can be either male or female.

 

 

yearStudent

The academic year of the student. It can be any of the 1, 2,3,4,5 year depending on the chosen course of study of a student.

 

 

addressStudent

 

 

 

phoneNumber

 

 

 

email

 

Match

A match happens as a part of an event and between at least two participants.

 

 

 

 

idMatch

Unique code for each match.

 

 

nameMatch

Name of a match

 

 

dateMatch

Date of a match

Result

Results are associated with the matches and events. It must include details like score of the participants and the awards given to the winners.

 

 

 

 

idMatch

Reference to a match

 

 

idWinner

Reference to the winner of a match

 

 

score

Score of a match

 

 

award

Award given to the winner if there is any.

Participation

Details of the participation of students in matches and events.

 

 

 

 

idParticipation

Unique ID for each participation

 

 

idMatch

Reference to the match

 

 

idStudent

Reference to the student who has the participation

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,

Entity

Attribute

Datatype

Keys

EVENT

idEvent

INT

Primary key

 

nameEvent

Varchar

 

 

typeEvent

Varchar

 

 

startDateEvent

Date

 

 

finishDateEvent

Date

 

MATCH

idMatch

INT

Primary key

 

nameMatch

Varchar

 

 

dateMatch

Date

 

 

idEvent

INT

Foreign Key

STUDENT

idStudent

INT

Primary key

 

nameStudent

Varchar

 

 

genderStudent

Varchar

 

 

yearStudent

INT

 

 

addressStudent

Varchar

 

 

phoneNumber

Char(10)

 

 

email

Varchar

 

MATCHLIST

idEvent

INT

Primary key, Foreign key

 

idMatch

INT

Primary key, Foreign key

RESULT

idMatch

INT

Primary key, Foreign key

 

idWinner

INT

Primary key, Foreign key

 

score

INT

 

 

award

Varchar

 

PARTICIPATION

idParticipation

INT

Primary key

 

idMatch

INT

Foreign key

 

idStudent

INT

Foreign key

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

Test Description

Expected Outcome

Actual Outcome

Comment

Tables are present

It will show the names of the tables with the 'Show Tables' command

Shows the names of the tables properly

Passed

Tables have proper data

Check each table with SELECT * TableName

Command

Shows the tables with records properly

Passed

Insertion of data is possible

Check the outcome of the INSERT INTO commands

Records are successfully inserted into tables.

Passed

Table creation possible

Check the outcome of the CREATE TABLE command

Tables are successfully created

Passed

Query 1 is running properly

Check whether the query 1 is executing properly or not

Showing the list of the available events in the tournament properly

Passed

Query 2 is running properly

Check whether the query 2 is executing properly or not

Showing the details of the players as expected.

Passed

Query 3 is running properly

Check whether the query 3 is executing properly or not

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.

Passed

Query 4 is running properly

Check whether the query 4 is executing properly or not

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.

Passed

Query 5 is running properly

Check whether the query 5 is executing properly or not

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

August 2017.

Passed

Query 6 is running properly

Check whether the query 6 is executing properly or not

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

 

Passed

Query 7 is running properly

Check whether the query 7 is executing properly or not

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

 

Passed

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.