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.