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 Database Design and Development Assignment Help - BTEC HND in Computing?


Database Design and Development (L4)
Case Study: Developing a database for ICON Summer Games

LO1: Use an appropriate design tool to design a relational database system for a substantial problem
Logical structure of the tables:

Entity Relational Diagram:

Assumptions and system requirements:
• Participant can participate Many Tournament
• In each Tournament, participate can participate an event
• Participant can Participate list of events in the tournament
• Participant should not participate inter events
• System maintain previous records of each events

List all the attributes:
The Tournament Database has following tables:
Tournament Table:
Tournament_ID (PK)
Tournament_Type
Tournament_Name
Tournament_Start_Date
Tournament_End_Date
Tournament_Reg_Start_Date
Tournament_Reg_End_Date
Phone_No
Email_ID
Address
State

Participant_Team Table:
Participant_ID (PK)
Tournament_ID (FK)
Team_Name
Team_Logo
Phone_No
Email_ID
Address
State
Tournament_Status

Tournament_Match Table:
Match_ID(PK)
Tournament_ID (FK)
Team_1
Team_2
Match_Date
Match_Time
Win_Team

Players Table:

Player_ID (PK)
Player_Name
Player_Gender
Player_DOB
Phone_No
Email_ID
Address
State
Game_Type
Game_Event Table:
Event_ID (PK)
Event_Name

Match_Score Table:
Match_Score_ID (PK)
Tournament_ID (FK)
Participant_ID (FK)
Match_ID (FK)
Player_ID (FK)
Event_Name (FK)
Runs
Wickets
Over
Goal
Total

Normalization:
Norm 1:
Normalization 1 should satisfy a single value in the table cell and maintain each record will be unique. As per Normalization 1 our tournament database maintains single cell value and each table has primary key to maintain unique records. For example, Match Score table depends on Tournament, Match, Participant and Players which will maintain unique values and avoid duplicates.
Norm 2:
Normalization 2 should satisfy normalization 1 and single column primary key which represent each record should maintain separately with primary key. For example, in our tournament database Event table maintain event_id and name only other details will not represent in the same table. So, details of the record should maintain with primary key.
Norm 3:
Normalization 3 should satisfy normalization 2 and no transitive functional dependencies which mean the non-key column value change then it will cause other non-key column value in the table. So, we should not have transitive functional dependencies. In tournament database of the tables are not have transitive functional dependencies.

Primary and Foreign keys:
The primary key and foreign key of each table given below
Tournament Table:
Tournament_ID (PK)

Participant_Team Table:
Participant_ID (PK)
Tournament_ID (FK)

Tournament_Match Table:
Match_ID(PK)
Tournament_ID (FK)

Players Table:

Player_ID (PK)
Game_Event Table:
Event_ID (PK)
Match_Score Table:
Match_Score_ID (PK)
Tournament_ID (FK)
Participant_ID (FK)
Match_ID (FK)
Player_ID (FK)
Event_Name (FK)

Database design:
Tournament Table:
Tournament_ID (PK) INT
Tournament_Type VARCHAR
Tournament_Name VARCHAR
Tournament_Start_Date VARCHAR
Tournament_End_Date DATE
Tournament_Reg_Start_Date DATE
Tournament_Reg_End_Date DATE
Phone_No INT
Email_ID VARCHAR2(20)
Address VARCHAR2(30)
State VARCHAR


Participant_Team Table:
Participant_ID (PK) INT
Tournament_ID (FK) INT
Team_Name VARCHAR(20)
Team_Logo VARCHAR(20)
Phone_No INT
Email_ID VARCHAR2(20)
Address VARCHAR2(30)
State VARCHAR(20)
Tournament_Status VARCHAR(20)

Tournament_Match Table:
Match_ID(PK) INT
Tournament_ID (FK) INT
Team_1 VARCHAR(20)
Team_2 VARCHAR(20)
Match_Date DATE
Match_Time TIME
Win_Team VARCHAR(20)

Players Table:

Player_ID (PK) INT
Player_Name VARCHAR(20)
Player_Gender VARCHAR(6)
Player_DOB DATE
Phone_No INT
Email_ID VARCHAR2(20)
Address VARCHAR2(30)
State VARCHAR(20)
Game_Type VARCHAR(20)

Game_Event Table:
Event_ID (PK) INT
Event_NameVARCHAR(20)
Match_Score Table:
Match_Score_ID (PK) INT
Tournament_ID (FK) INT
Participant_ID (FK) INT
Match_ID (FK) INT
Player_ID (FK) INT
Event_Name (FK) VARCHAR(20)
Runs INT
Wickets INT
Over DECIMAL
Goal INT
Total INT
LO2: Develop a fully functional relational database system, based on an existing system design
Develop the database using the structured query language:
Create Tables:
Table 1: Tournament Table
Query:
create table tournament
(Tournament_ID INT primary key,
Tournament_Type VARCHAR(20),
Tournament_Name VARCHAR(20),
Tournament_Start_Date VARCHAR(20),
Tournament_End_Date DATE,
Tournament_Reg_Start_Date DATE,
Tournament_Reg_End_Date DATE ,
Phone_No INT,
Email_ID VARCHAR2(20),
Address VARCHAR2(30),
State VARCHAR(20))

Screen shot:

Table 2:Participant_Team
Query:
create table participant_team
(Participant_ID INT primary key,
Tournament_ID INT,
Team_Name VARCHAR(20),
Team_Logo VARCHAR(20),
Phone_No INT,
Email_ID VARCHAR2(20),
Address VARCHAR2(30),
State VARCHAR(20),
Tournament_Status VARCHAR(20),foreign Key (tournament_ID) REFERENCES tournament(tournament_id))

Screen shot:

Table 3:Tournament_Match Table
Query:
create table Tournament_match
(Match_ID INT primary key,
Tournament_ID INT,
Team_1 VARCHAR(20),
Team_2 VARCHAR(20),
Match_Date DATE,
Match_Time varchar2(10),
Win_Team VARCHAR(20),
foreign Key (tournament_ID) REFERENCES tournament(tournament_id))
Screen shot:

Table 4: Player Table
Query:
create table Players
(Player_ID INT primary key,
Player_Name VARCHAR(20),
Player_Gender VARCHAR(6),
Player_DOB DATE ,
Phone_No INT,
Email_ID VARCHAR2(20),
Address VARCHAR2(30),
State VARCHAR(20),
Game_Type VARCHAR(20))

Screen shot:

Table 5:
Query:
create table Game_Event(Event_ID int primary key,Event_Name varchar(20))

Screen shot:

Table 6:
Query:
create table Match_Score
(Match_Score_ID INT primary key,
Tournament_ID INT,
Participant_ID INT,
Match_ID INT,
Player_ID INT,
Event_ID INT,
Runs INT,
Wickets INT,
Over DECIMAL,
Goal INT,
Total INT,

foreign Key (tournament_ID) REFERENCES tournament(tournament_id),
foreign Key (participant_ID) REFERENCES participant_team(participant_id),
foreign Key (Match_ID) REFERENCES tournament_Match(match_id),
foreign Key (player_ID) REFERENCES players(player_id),
foreign Key (Event_ID) REFERENCES game_event(event_id))

Screen shot:

Insert Tables:
Table 1: Tournament
Query:
insert into tournament values(1,'multilevel',' Tournament Goal','10-10-2017','10-30-2017','09-20-2017','09-30-2017',12345678,'[email protected]','no 1 first cross street','London')
insert into tournament values(2,'Single level',' Tournament success','10-10-2018','10-30-2018','09-20-2018','09-30-2018',87654321,'[email protected]','no 2 church road','paris')
insert into tournament values(3,'Double level',' Goalathon','06-10-2018','06-30-2018','06-20-2018','06-30-2018',09876554,'[email protected]','no 3 cross road','newyork')
insert into tournament values(4,'spiltlevel',' Basekeat goal','06-10-2017','06-30-2017','06-20-2017','06-30-2017',4567890,'[email protected]','no 4 ring road','London')
insert into tournament values(5,'multilevel','Goal try','06-10-2016','06-30-2016','06-20-2016','06-30-2016',7834568,'[email protected]','no 5 ring road','London')
insert into tournament values(5,'multilevel','Goal try','06-10-2016','06-30-2016','06-20-2016','06-30-2016',7834568,'[email protected]','no 5 ring road','London')

Screen Shot:

Output:
TOURNAMENT_ID TOURNAMENT_TYPE TOURNAMENT_NAME TOURNAMENT_START_DATE TOURNAMENT_END_DATE TOURNAMENT_REG_START_DATE TOURNAMENT_REG_END_DATE PHONE_NO EMAIL_ID ADDRESS STATE
1 multilevel Tournament Goal 10-10-2017 10/30/2017 09/20/2017 09/30/2017 12345678 [email protected] no 1 first cross street Londan
2 Single level Tournament success 10-10-2018 10/30/2018 09/20/2018 09/30/2018 87654321 [email protected] no 2 church road paris
3 Double level Goalathon 06-10-2018 06/30/2018 06/20/2018 06/30/2018 9876554 [email protected] no 3 cross road new york
4 spiltlevel Baseket goal 06-10-2017 06/30/2017 06/20/2017 06/30/2017 4567890 [email protected] no 4 ring road London
5 multilevel Goal try 06-10-2016 06/30/2016 06/20/2016 06/30/2016 7834568 [email protected] no 5 ring road London

Table 2:Participant_team
Query:
insert into participant_teamvalues(100,1,'success','cup','2345677','[email protected]','no34 bell road','london','No return')
insert into participant_teamvalues(200,2,'Weldon','star','78953377','[email protected]','no12 large area road','paris','No return')
insert into participant_teamvalues(300,3,'Goal score','goal ball','23953377','[email protected]','no1 church road','london','No return')
insert into participant_teamvalues(400,4,'Ball','Ball','45953377','[email protected]','no4 geek road','london','No return')
insert into participant_teamvalues(500,5,'flag Ball','flag','78953377','[email protected]','no5 gerrk road','paris','No return')
insert into participant_teamvalues(400,4,'Ball','Ball','45953377','[email protected]','no4 geek road','london','No return')
insert into participant_teamvalues(500,5,'flag Ball','flag','78953377','[email protected]','no5 gerrk road','paris','No return')
Screen Shot:

Output:
PARTICIPANT_ID TOURNAMENT_ID TEAM_NAME TEAM_LOGO PHONE_NO EMAIL_ID ADDRESS STATE TOURNAMENT_STATUS
100 1 success cup 2345677 [email protected] no34 bell road london No return
200 2 Weldon star 78953377 [email protected] no12 large area road paris No return
300 3 Goal score goal ball 23953377 [email protected] no1 church road london No return
400 4 Ball Ball 45953377 [email protected] no4 geek road london No return
500 5 flag Ball flag 78953377 [email protected] no5 gerrk road paris No return

Table 3:Tournament_Match
Query:
insert into Tournament_Match values(1,1,1,2,'10-10-2017','00:10:00','team1')
insert into Tournament_Match values(2,2,3,4,'10-10-2019','00:10:00','team2')
insert into Tournament_Match values(3,3,5,6,'10-10-2018','00:10:00','team6')
insert into Tournament_Match values(4,4,7,8,'10-10-2016','00:10:00','team7')
insert into Tournament_Match values(5,5,10,8,'06-10-2016','00:10:00','team8')
Screen Shot:

Output:
MATCH_ID TOURNAMENT_ID TEAM_1 TEAM_2 MATCH_DATE MATCH_TIME WIN_TEAM
1 1 1 2 10/10/2017 00:10:00 team1
2 2 3 4 10/10/2019 00:10:00 team2
3 3 5 6 10/10/2018 00:10:00 team6
4 4 7 8 10/10/2016 00:10:00 team7
5 5 10 8 06/10/2016 00:10:00 team8

Table 4: Players
Query:
insert into players values(1000,'George','male','06-10-1983','2345678','[email protected]','no 10 bell street','london','multiplayer')
insert into players values(2000,'Clinton','male','10-10-1989','4545678','[email protected]','no 1 river road','paris','multiplayer')
insert into players values(3000,'ankit','male','10-20-1985','89245678','[email protected]','no 8 mainroad','london','multiplayer')
insert into players values(3000,'ankit','male','10-20-1985','89245678','[email protected]','no 8 mainroad','london','multiplayer')
insert into players values(5000,'panti dhruv','male','02-20-1979','892345678','[email protected]','no 1 fashion street','paris','multiplayer')
insert into players values(3000,'ankit','male','10-20-1985','89245678','[email protected]','no 8 mainroad','london','multiplayer')
insert into players values(3000,'ankit','male','10-20-1985','89245678','[email protected]','no 8 mainroad','london','multiplayer')
insert into players values(5000,'panti dhruv','male','02-20-1979','892345678','[email protected]','no 1 fashion street','paris','multiplayer')

Screen Shot:

Output:
PLAYER_ID PLAYER_NAME PLAYER_GENDER PLAYER_DOB PHONE_NO EMAIL_ID ADDRESS STATE GAME_TYPE
1000 George Male 06/10/1983 2345678 [email protected] no 10 bell street london multiplayer
2000 Clinton Male 10/10/1989 4545678 [email protected] no 1 river road paris multiplayer
3000 Ankit Male 10/20/1985 89245678 [email protected] no 8 mainroad london multiplayer
4000 ishaangolkarini Female 06/20/1985 12345678 [email protected] no3 second street london multiplayer
5000 pantidhruv Male 02/20/1979 892345678 [email protected] no 1 fashion street paris multiplayer

Table 5:Game_Event
Query:
insert into game_event values(1,'multiplayer')
insert into game_event values(2,'single')
insert into game_event values(3,'duble')
insert into game_eventvalues(3,'duble cross')

Screen Shot:

Output:
EVENT_ID EVENT_NAME
1 multiplayer
2 single
3 duble
4 split
5 online

Table 6:Match_Score
Query:
insert into match_scorevalues(1,1,100,1,1000,1,300,6,25,0,300)
insert into match_scorevalues(2,2,200,2,2000,2,0,0,0,5,5)
insert into match_scorevalues(3,3,300,2,3000,3,0,0,0,2,2)
insert into match_scorevalues(4,4,400,4,4000,4,0,0,0,4,4)
insert into match_scorevalues(5,5,500,5,5000,5,350,7,50,0,350)
Screen Shot:

Output:
MATCH_SCORE_ID TOURNAMENT_ID PARTICIPANT_ID MATCH_ID PLAYER_ID EVENT_ID RUNS WICKETS OVER GOAL TOTAL
1 1 100 1 1000 1 300 6 25 0 300
2 2 200 2 2000 2 0 0 0 5 5
3 3 300 2 3000 3 0 0 0 2 2
4 4 400 4 4000 4 0 0 0 4 4
5 5 500 5 5000 5 350 7 50 0 350

Queries:
a) List all the available events in the tournament.
Query:
select * from game_event

Output:
EVENT_ID EVENT_NAME
1 multiplayer
2 single
3 duble
4 split
5 online


Screen Shot:


b) List all the players' names, addresses, phone numbers and email addresses.
Query:
select player_name,address, phone_no,email_id from players

Output:
PLAYER_NAME ADDRESS PHONE_NO EMAIL_ID
George no 10 bell street 2345678 [email protected]
Clinton no 1 river road 4545678 [email protected]
ankit no 8 mainroad 89245678 [email protected]
ishaangolkarini no3 second street 12345678 [email protected]
pantidhruv no 1 fashion street 892345678 [email protected]


Screen Shot:

c) List all the events that participant John Smith is registered for in ascending order by date.
Query:
select players.player_name,game_event.event_name from players,game_event where players.player_name='John Smith ' order by game_event.event_name

Output:
PLAYER_NAME EVENT_NAME
John Smith duble
John Smith multiplayer
John Smith online
John Smith single
John Smith split

Screen Shot:

d) List participants' names who won more than 3 matches
Query:
select win_team from tournament_match group by win_team having count(win_team)>3

Output:
WIN_TEAM
success

Screen Shot:

e) List all the participant IDs, names, match-date, event ID and event name played in August 2017.
Query:

select participant_team.participant_ID, participant_team.team_name, tournament_match.match_date,game_event.event_ID, game_event.event_name from participant_team,tournament_match,game_eventWHERE EXTRACT(YEAR FROM TO_DATE(tournament_match.match_date, 'DD-Mm-RR'))=2017 and extract(month from tournament_match.match_date) =10

Output:
PARTICIPANT_ID TEAM_NAME MATCH_DATE EVENT_ID EVENT_NAME
100 success 10/10/2017 1 multiplayer
200 Weldon 10/10/2017 1 multiplayer
300 Goal score 10/10/2017 1 multiplayer
400 Ball 10/10/2017 1 multiplayer
500 flag Ball 10/10/2017 1 multiplayer
100 success 10/10/2017 2 single
200 Weldon 10/10/2017 2 single
300 Goal score 10/10/2017 2 single
400 Ball 10/10/2017 2 single
500 flag Ball 10/10/2017 2 single
More than 10 rows available. Increase rows selector to view more rows.

Screen Shot:

f) Update a participant's address from a city to London
Query:
update participant_team set state='london' where state='paris' and participant_id=500

Output:
PARTICIPANT_ID TOURNAMENT_ID TEAM_NAME TEAM_LOGO PHONE_NO EMAIL_ID ADDRESS STATE TOURNAMENT_STATUS
100 1 success cup 2345677 [email protected] no34 bell road london No return
200 2 Weldon star 78953377 [email protected] no12 large area road paris No return
300 3 Goal score goal ball 23953377 [email protected] no1 church road london No return
400 4 Ball Ball 45953377 [email protected] no4 geek road london No return
500 5 flag Ball flag 78953377 [email protected] no5 gerrk road london No return

Screen Shot:

g) Count all matches in October 2018.
Query:
select count(match_date) from tournament_match where EXTRACT(YEAR FROM TO_DATE(tournament_match.match_date, 'DD-Mm-RR'))=2018 and extract(month from tournament_match.match_date) =10

Output:
COUNT(MATCH_DATE)
1

Screen Shot:

Database Secure and maintain:
The database can secure and main by following way
• Control access to the database
We need to control to access the database by providing privilege to access database so that we can secure our database.
• Identify sensitive and critical data
First , when we secure database we need to analysis and identify the sensitive and critical data of the database and apply security on them. For that we need to analysis the architecture of the database.
• Encrypt information
We can secure data by encrypt sensitive data and store it into database. Whenever we require we can decrypt the encrypted data so that we can secure sensitive data.
• Anonymize non-productive databases
We can create and maintain same database so that when any modification occurs then original data will protect from modification.

• Monitor database activity
An activity of the database should monitor periodically or always so that we can easily identify unauthorized user to access the database.

Fully Functional Database System:
The Fully Functional Database System (FFD) is used to indicate the functional dependency which should be minimum set of attributes. We can say in out tournament database has game_event table which event_name functionally depends on event_name and event_id not functionally dependent on any subset of event_name.

LO3: Test the system against user and system requirements
Test Plan:
S.No Test Description Expected Outcome Actual Outcome
1. Tring to insert players details into players table with already exiting players id 6000 Insert record into players table
insert into players values(6000,'Jony','male','09/10/1982',2324324,'[email protected]','First cross road','Paris','Single')
because it is violate unique constrint on players_id. If we give instead of player_id as 7000 then record can insert into table as following output

2. Check length of character on emailed column The expected output of this test case should insert players details into players table but length of the email id field as created varchar2 with size 20 . if we give correct length of email id the following output we can expect
. insert into players values(7000,'Jony','male','09/10/1982',2324324,'[email protected]','First cross road','Paris','Single')

3. Insert record into mach_score table but it violate integrity constraints Here we do not have tournament id 10 but trying to insert record into match_score table which is depends on tournament table so voliate integrity constraints the following screen shot shows expected output to insert record into mathc_score table

Here we do not have tournament id 10 but trying to insert record into match_score table which is depends on tournament table so voliate integrity constraints the following screen shot shows actual output to insert record into mathc_score table

Different database testing techniques:
The different types of testing techniques are listed below
1. Structural Testing
2. Functional Testing
3. Non-functional Testing

Figure 3: Types of Database Testing
1. Structural Testing
In this technique we can validate all elements which are in the data repository that are primarily storage of data that data can modify or manipulate directly by the users. The validation of the database server also important to consider in the structural testing. The database schema should be validated in this structural testing

2. Functional Testing
The functional testing is very important to validate specification of the requirements which ensure the transaction and operations performed by end user. It consists of following requirements specification and condition.
• Check Mandatory field while accept null vales
• Check Sufficient size for length of each field
• Check Similar fields have same name in the tables of the database
• Check computed fields present in the database.

3. Non-Functional Testing
The Nonfunctional testing can classify into various categories which is required for business requirements. It can be stress testing, load testing, security testing.
Verification and Validation Process:
Update:
We can update the table using update command. The following query is used to update the participant_team table of state attribute to London
Query:
update participant_team set state='london' where state='paris' and participant_id=500

Output:
PARTICIPANT_ID TOURNAMENT_ID TEAM_NAME TEAM_LOGO PHONE_NO EMAIL_ID ADDRESS STATE TOURNAMENT_STATUS
100 1 success cup 2345677 [email protected] no34 bell road london No return
200 2 Weldon star 78953377 [email protected] no12 large area road paris No return
300 3 Goal score goal ball 23953377 [email protected] no1 church road london No return
400 4 Ball Ball 45953377 [email protected] no4 geek road london No return
500 5 flag Ball flag 78953377 [email protected] no5 gerrk road london No return

Sorting:
Using sort we can sort participant_name in asec or desc order. The following query is used to sort the participant_name.
select players.player_name,game_event.event_name fromplayers,game_event where players.player_name='John Smith ' order by game_event.event_name

Output:
PLAYER_NAME EVENT_NAME
John Smith duble
John Smith multiplayer
John Smith online
John Smith single
John Smith split

LO4: Produce technical and user documentation
Technical and User Documentation:

Diagrams:
Database Diagram:

Future Improvements:
The tournament database not maintain tournament admin, user login for tournament, tournament notice, block of the event and history of each event, tournament employee details, their payment details, participant of each event. If we maintain all stated above tables it will be complete database for tournament. The tournament table do have security which means anyone can access database and manipulate. In future we can apply any of the database security technique such as control the access of database, encrypt sensitive data, protect sensitive data, make duplicate copies and periodically monitor. We need to control to access the database by providing privilege to access database so that we can secure our database. First , when we secure database we need to analysis and identify the sensitive and critical data of the database and apply security on them. For that we need to analysis the architecture of the database.Encrypt information can secure data by encrypt sensitive data and store it into database. Whenever we require we can decrypt the encrypted data so that we can secure sensitive data.Anonymize non-productive databases can create and maintain same database so that when any modification occurs then original data will protect from modification.Monitor database activityAn activity of the database should monitor periodically or always so that we can easily identify unauthorized user to access the databasethe database then it will provide more security to the database.