Database Project: Prairie Sports Club - Design, SQL, Normalization

This project focuses on designing and implementing a database for the Prairie Sports Club. The solution includes an entity-relationship diagram, normalization to the third normal form (3NF), a data dictionary, and SQL queries for data entry and manipulation. The database design covers entities such as members, membership types, teams, leagues, fixtures, and opponents. SQL queries are provided for creating tables, inserting data, and performing various data retrieval and update operations, including selecting members, listing fixtures, counting wins, updating subscriptions, and deleting opponents. The project also includes a brief assessment discussing assumptions, initial design considerations, and how requirements have been met.

COVER PAGE

Contents
COVER PAGE................................................................................................................................................1
Task 1 Design...............................................................................................................................................3
a) Entity relationship diagram..............................................................................................................3
b) Normalization..................................................................................................................................3
Members relation................................................................................................................................4
Team relation......................................................................................................................................7
Fixture relation..................................................................................................................................10
c) Data dictionary..............................................................................................................................12
Task 2 Data entry and Data manipulation.................................................................................................13
1) Creation queries in sql...................................................................................................................13
2. Enter data in members and membership types.................................................................................19
3. Enter league data...............................................................................................................................21
4. Enter team data.................................................................................................................................21
5 Enter sports and opponent data.........................................................................................................22
6 Enter fixture data................................................................................................................................23
7 Write a query that selects the first name and surname of team members alphabetical order of
surname.................................................................................................................................................23
8 Write a query that selects all home fixtures by date in descending order..........................................24
9 Write a query that counts all the fixtures where the result is a win...................................................24
10 Write a query that returns the all fixture details where the result is ‘Lose’ ordered by date...........24
11 Write a query that selects all Member First Names, Surnames and Membership Type who pay by
Direct Debit grouped by Member Type.................................................................................................25
12. Update the subscription amount for Team to £120........................................................................25
13 Add a new membership type ‘Casual’...............................................................................................25
14 Update the membership type for Jake Smith from Team to Casual.................................................26
15 Delete the opponent team Colne Ravens.........................................................................................26
Task 3 Assessment.....................................................................................................................................26
Assumptions..........................................................................................................................................26
Initial design..........................................................................................................................................26
How requirements have been met........................................................................................................26
Bibliography...............................................................................................................................................27

Task 1 Design
a) Entity relationship diagram
Figure 1: Entity relationship diagram
b) Normalization
Normalization is the process of normalizing through a step of normal forms in order to enforce data
integrity while reducing data redundancy. Normalization for this database has been done through three
normal forms.
 1NF- At 1NF all repeating groups are eliminated.
 2NF- At 2NF the relation achieved in 1NF are normalized and all partial dependencies exiting in
the relation are eliminated.
 3NF- At 3NF all the relations achieved at 2NF are normalized by eliminating all transitive
dependencies.
To show the normalization process it’s important to show all the relations in their un-normalized normal
form (UNF) so as to describe normalization up to 3NF. The relations derived from the case study in their
normal un-normalized form are;

Members relation
UNF
Member (memberName, address, telNO, type, subscriptionAmount, method)

1NF
At 1NF all repeating groups are eliminated and any composite attributes are decomposed. Using the
relation in UNF above the name field and address are composite attributes and are decomposed to
form.
Member (firstname, lastname, street, city, county, postalcode, telNO, memberType,
subscriptionAmount, method)
Using the data in UNF the new relation in 1NF becomes
FirstName lastName street city count
y
postalCode telNO memberTyp
e
subscriptionA
mount
method
Jake Smith 12
Olivier
Lane
St Albans Herts AL1 5TH 07678887675 Team 100 DD
Penny O’Reilly The
Nest
Crows
lane
Harpenden Herts AL3 6TY 088767545321 Team 100 DD
Humerah Kahn Flat 6
station
Road
Letchworth Herts SG5 1AG 07678089098 Full 150 CC
Paul Connor 45
High
street
St Albans Herts Al3 4TH 07675434561 Team 100 Cash
Jean-Paul Croix The
Hushli
ngs
petty
lane
St Albans Herts Al2 7YH 07876546547 Full 150 CC
Seamus O’Malley 21
Roma
n
Road
St Albans herts AL3 4TY 07789098089 Social 75 DC
Katie Fergusso
n
13
Manor
Scresc
ent
St Albans Herts AL4 5YT 08678089998 Social 75 DD
2NF
At 2NF all partial dependencies are eliminated. To eliminate partial dependencies a primary key is
introduced to form the following relation.
Member (memberID ,firstname, lastname, street, city, county, postalcode, telNO, memberType,
subscriptionAmount, method)

memb
erID
FirstName lastName street city count
y
postalC
ode
telNO memberTyp
e
subscription
Amount
metho
d
1 Jake Smith 12
Olivier
Lane
St
Albans
Herts AL1 5TH 0767888767
5
Team 100 DD
2 Penny O’Reilly The
Nest
Crows
lane
Harpen
den
Herts AL3 6TY 0887675453
21
Team 100 DD
3 Humerah Kahn Flat 6
station
Road
Letchw
orth
Herts SG5
1AG
0767808909
8
Full 150 CC
4 Paul Connor 45
High
street
St
Albans
Herts Al3 4TH 0767543456
1
Team 100 Cash
5 Jean-Paul Croix The
Hushli
ngs
petty
lane
St
Albans
Herts Al2 7YH 0787654654
7
Full 150 CC
6 Seamus O’Malley 21
Roma
n
Road
St
Albans
herts AL3 4TY 0778909808
9
Social 75 DC
7 Katie Fergusso
n
13
Manor
Scresc
ent
St
Albans
Herts AL4 5YT 0867808999
8
Social 75 DD
 3NF
At 3NF all transitive dependencies are eliminated. Considering the relation achieved at 2NF above there
exists transitive dependency because the memberType determines the subscription amount and this can
be normalized to form another relation thus resulting to the following relations.
Member (memberID ,firstname, lastname, street, city, county, postalcode, telNO, memberType,
subscriptionAmount, method)
memb
erID
FirstName lastName street city county postalCode telNO memberTypeI
D
method
1 Jake Smith 12
Olivier
Lane
St
Albans
Herts AL1 5TH 07678887675 1 DD
2 Penny O’Reilly The
Nest
Crows
lane
Harpen
den
Herts AL3 6TY 088767545321 1 DD

3 Humerah Kahn Flat 6
station
Road
Letchw
orth
Herts SG5 1AG 07678089098 2 CC
4 Paul Connor 45
High
street
St
Albans
Herts Al3 4TH 07675434561 1 Cash
5 Jean-Paul Croix The
Hushli
ngs
petty
lane
St
Albans
Herts Al2 7YH 07876546547 2 CC
6 Seamus O’Malley 21
Roma
n
Road
St
Albans
herts AL3 4TY 07789098089 3 DC
7 Katie Fergusso
n
13
Manor
Scresc
ent
St
Albans
Herts AL4 5YT 08678089998 3 DD
MemberTypes (memberTypeID,memberType,subscriptionAmount)
MemberTypeID memberType subscriptionAmount
1 Team 100
2 Full 150
3 Social 75
Team relation
UNF
Team (TeamName, sport, League, FirstDay,lastDay,memberName,captain)

1NF
Normalization to 1NF involves removing all repeating groups. Using the relation in UNF there are no
repeating groups thus the relation is already in 1NF.
2NF
Normalization to 2NF involves eliminating all the partial dependencies. For the team relation partial
dependencies are eliminated by introducing a primary key teamID
TeamID Team
Name
Sport League First day of
season
Last day of
season
memberNam
e
Captain
1 Prairie
Dogs
Football FB1 09/09/2017 04/05/2018 Seamus
O’Malley
Y
2 Prairie
Shots
Golf Community
1
15/08/2017 06/06/2018 Jean-Paul
Croix
Y
1 Prairie
Dogs
Football FB1 09/09/2017 04/05/2018 Paul Connor N
1 Prairie
Dogs
Football FB1 09/09/2017 04/05/2018 Jake Smith N
2 Prairie
Shots
Golf Community
1
15/08/2017 06/06/2018 Katie
Ferguson
N
3NF
Normalization to 3NF involves eliminating all the transitive dependencies. From the relation achieved in
2NF above. Sport can be decomposed to form a sports relation.
Sport (sportID, name)
SportID name
1 Football
2 Golf
Season (seasonID, firstDay, lastDay)

SeasonID firstDay lastDay
1 09/09/2017 04/05/2018
2 15/08/2017 15/08/2017
Team (teamID, name,sportID, seasonID, captain, leagueID)
TeamID name sportID seasonID captainID leagueID
1 Prairie Dogs 1 1 6 1
2 Prairie Shots 2 2 5 2
teamMembers (teamId,memberID)
teamID memberID
1 6
1 4
1 1
2 5
2 7
 League relation
UNF
League (name,contactName, TelNumber)
1NF
League (name,contactName, TelNumber)
2NF
Eliminate all partial dependencies by introducing a primary key
LeagueID Name Contact name Tel number
1 FB1 Kelly Holmes 077485996
2 Community 1 Ifzal khan 0774858521

3NF
Eliminate all transitive dependencies. The relation is already in 3NF.
LeagueID Name Contact name Tel number
1 FB1 Kelly Holmes 077485996
2 Community 1 Ifzal khan 0774858521
Fixture relation
UNF
Fixture (fixturID, team, opponentTeamDetails, Location, Result, Score, Date)
1NF
Normalization to 1NF involves removing all repeating groups and breaking the composite attributes.
Fixtu
reID
Team opponentName opponentLocation opponentContact location resul
t
score date
1 Prairie Lions Conbine Club 0765787656 Home win 2:1 09/09/201

Dogs 7
2 Prairie
Dogs
Harriers Thompson Centre 07678089098 Away Lose 5:1 14/09/201
7
3 Prairie
Dogs
Colne Raven St Leots sports
center
0765676876 Home Lose 2:1 21/09/201
7
4 Prairie
Dogs
Creven Edge Craven Cottage 0789876566 Home win 4:3 28/09/201
7
5 Prairie
Dogs
Furious Fitness first 098999987 Away Draw 4:4 25/10/201
7
6 Prairie
shots
Argents The Crown Club 09998789098 Away Win 2:1 11/11/201
7
2NF
The relation is already in 2NF since there are no partial dependencies.
3NF
Normalization to 3NF involves eliminating the transitive dependencies in the relation. The opponent
name determines the opponent location and the opponent contact this can be decomposed to form a
relation.
Fixture (FixtureID,team,opponentID,location, result, score , date)
Fixtu
reID
Team OpponentID location result score date
1 Prairie
Dogs
1 Home win 2:1 09/09/2017
2 Prairie
Dogs
2 Away Lose 5:1 14/09/2017
3 Prairie
Dogs
3 Home Lose 2:1 21/09/2017
4 Prairie
Dogs
4 Home win 4:3 28/09/2017
5 Prairie
Dogs
5 Away Draw 4:4 25/10/2017
6 Prairie
shots
6 Away Win 2:1 11/11/2017
Opponent (opponentID, opponentName, opponentLocation)
opponentID opponentName opponentLocation opponentContact
1 Lions Conbine Club 0765787656
2 Harriers Thompson Centre 07678089098
3 Colne Raven St Leots sports center 0765676876
4 Creven Edge Craven Cottage 0789876566
5 Furious Fitness first 098999987

6 Argents The Crown Club 09998789098
c) Data dictionary
Entity Attribute Data Type Constraint
member memberID Integer Primary key
firstName Varchar(50)
lastName Varchar(50)
Street Varchar(50)
City Varchar(50)
County Varchar(50)
postalCode Varchar(10)
telNO Varchar(25)
memberTypes memberTypeID integer Primary key
Type Varchar(25)
subscriptionAmount Integer
memberSubscriptions subID Integer Primary key
memberID Integer Foreign key references
member.memberID
memberTypeID integer Foreign key references
memberTypes.memberTypeID
Date Date
method CHAR(4)
teamMembers memberID integer Primary key
Foreifn key references
member.memberID
teamID Integer Primary key
Foreign key references
team.teamID
sport sportID Integer Primary key
name Varchar(50)
team teamID Integer Primary key
name Varchar(50)
sportID Integer Foreign key references
sport.sportID
seasonID Integer Foreign key references
season.seasonID
captainID Integer Foreign key references
member.memberID
leagueID integer Foreign key references

league.leagueID
Season seasonID Integer Primary key
firstDay Date
lastDay Date
league leagueID Integer Primary key
Name Varchar(50)
contactName Varchar(50)
telNO Varchar(25)
fixture fixtureID Integer Primary key
teamID Integer Foreign key references
team.teamID
opponentID Integer Foreign key references
opponent.opponentID
Location Varchar(50)
Result Varchar(10)
Score Varchar(5)
Date Date
opponent opponentID Integer Primary key
Name Varchar(50)
Location Varchar(50)
Task 2 Data entry and Data manipulation
1) Creation queries in sql
 Fixture table
CREATE TABLE IF NOT EXISTS `fixture` (
`fixtureID` int(11) NOT NULL,
`teamID` int(11) NOT NULL,
`opponentID` int(11) NOT NULL,
`location` varchar(10) NOT NULL,
`result` varchar(10) NOT NULL,
`score` varchar(5) NOT NULL,
`date` date NOT NULL
);
ALTER TABLE `fixture`
ADD PRIMARY KEY (`fixtureID`), ADD KEY `teamID`
(`teamID`,`opponentID`), ADD KEY `opponentID` (`opponentID`);
ALTER TABLE `fixture`
MODIFY `fixtureID` int(11) NOT NULL AUTO_INCREMENT;

 League table
CREATE TABLE IF NOT EXISTS `leaague` (
`leagueID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`contactName` varchar(50) NOT NULL,
`telNO` varchar(25) NOT NULL
);
ALTER TABLE `leaague`
ADD PRIMARY KEY (`leagueID`);
ALTER TABLE `leaague`
MODIFY `leagueID` int(11) NOT NULL AUTO_INCREMENT;
 Member table
CREATE TABLE IF NOT EXISTS `member` (
`memberID` int(11) NOT NULL,
`firstname` varchar(50) NOT NULL,
`lastname` varchar(50) NOT NULL,
`street` varchar(50) NOT NULL,
`city` varchar(50) NOT NULL,
`county` varchar(50) NOT NULL,
`postalcode` varchar(25) NOT NULL,
`telNO` varchar(25) NOT NULL
);
ALTER TABLE `member`
ADD PRIMARY KEY (`memberID`);
ALTER TABLE `member`
MODIFY `memberID` int(11) NOT NULL AUTO_INCREMENT;

 MemberSubscriptions table
CREATE TABLE IF NOT EXISTS `membersubscriptions` (
`subID` int(11) NOT NULL,
`memberID` int(11) NOT NULL,
`memberTypeID` int(11) NOT NULL,
`date` date NOT NULL,
`method` char(5) NOT NULL
);
ALTER TABLE `membersubscriptions`
ADD PRIMARY KEY (`subID`), ADD KEY `memberID`
(`memberID`,`memberTypeID`), ADD KEY `memberTypeID`
(`memberTypeID`);
ALTER TABLE `membersubscriptions`
MODIFY `subID` int(11) NOT NULL AUTO_INCREMENT;
 MemberTypes table
CREATE TABLE IF NOT EXISTS `membertypes` (
`memberTypeID` int(11) NOT NULL,
`type` varchar(50) NOT NULL,
`subcriptionAmount` int(11) NOT NULL
);
ALTER TABLE `membertypes`
ADD PRIMARY KEY (`memberTypeID`);

ALTER TABLE `membertypes`
MODIFY `memberTypeID` int(11) NOT NULL AUTO_INCREMENT;
 Opponent table
CREATE TABLE IF NOT EXISTS `opponent` (
`oponentID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`location` varchar(50) NOT NULL,
`contact` VARCHAR(25) NOT NULL
);
ALTER TABLE `opponent`
ADD PRIMARY KEY (`oponentID`);
ALTER TABLE `opponent`
MODIFY `oponentID` int(11) NOT NULL AUTO_INCREMENT;
 Season table
CREATE TABLE IF NOT EXISTS `season` (
`seasonID` int(11) NOT NULL,
`firstDay` date NOT NULL,
`lastday` date NOT NULL
);
ALTER TABLE `season`
ADD PRIMARY KEY (`seasonID`);
ALTER TABLE `season`
MODIFY `seasonID` int(11) NOT NULL AUTO_INCREMENT;

 Sport table
CREATE TABLE IF NOT EXISTS `sport` (
`sportID` int(11) NOT NULL,
`name` varchar(50) NOT NULL
);
ALTER TABLE `sport`
ADD PRIMARY KEY (`sportID`);
ALTER TABLE `sport`
MODIFY `sportID` int(11) NOT NULL AUTO_INCREMENT;
 Team table
CREATE TABLE IF NOT EXISTS `team` (
`teamID` int(11) NOT NULL,
`name` varchar(50) NOT NULL,
`sportID` int(11) NOT NULL,
`seasonID` int(11) NOT NULL,
`cpatainID` int(11) NOT NULL,
`leagueID` int(11) NOT NULL
);
ALTER TABLE `team`
ADD PRIMARY KEY (`teamID`), ADD KEY `sportID`
(`sportID`,`seasonID`,`cpatainID`,`leagueID`), ADD KEY `leagueID`
(`leagueID`), ADD KEY `seasonID` (`seasonID`);
ALTER TABLE `team`
MODIFY `teamID` int(11) NOT NULL AUTO_INCREMENT;
 TeamMembers table
CREATE TABLE IF NOT EXISTS `teammembers` (
`memberID` int(11) NOT NULL,

`teamID` int(11) NOT NULL
);
ALTER TABLE `teammembers`
ADD KEY `memberID` (`memberID`,`teamID`), ADD KEY `teamID`
(`teamID`);
After creating the tables create foreign key constraints.
ALTER TABLE `fixture`
ADD CONSTRAINT `fixture_ibfk_1` FOREIGN KEY (`teamID`) REFERENCES
`team` (`teamID`),
ADD CONSTRAINT `fixture_ibfk_2` FOREIGN KEY (`opponentID`)
REFERENCES `opponent` (`oponentID`);
ALTER TABLE `membersubscriptions`
ADD CONSTRAINT `membersubscriptions_ibfk_1` FOREIGN KEY
(`memberID`) REFERENCES `member` (`memberID`),
ADD CONSTRAINT `membersubscriptions_ibfk_2` FOREIGN KEY
(`memberTypeID`) REFERENCES `membertypes` (`memberTypeID`);
ALTER TABLE `team`
ADD CONSTRAINT `team_ibfk_1` FOREIGN KEY (`leagueID`) REFERENCES
`leaague` (`leagueID`),
ADD CONSTRAINT `team_ibfk_2` FOREIGN KEY (`sportID`) REFERENCES
`sport` (`sportID`),
ADD CONSTRAINT `team_ibfk_3` FOREIGN KEY (`seasonID`) REFERENCES
`season` (`seasonID`);
ALTER TABLE `teammembers`
ADD CONSTRAINT `teammembers_ibfk_1` FOREIGN KEY (`memberID`)
REFERENCES `member` (`memberID`),
ADD CONSTRAINT `teammembers_ibfk_2` FOREIGN KEY (`teamID`)
REFERENCES `team` (`teamID`);

2. Enter data in members and membership types
 Member table
INSERT INTO `priarie`.`member` (`memberID`, `firstname`,
`lastname`, `street`, `city`, `county`, `postalcode`, `telNO`)
VALUES (NULL, 'Jake', 'Smith', '23 olivier lane', 'St Albans',
'Herts', 'Al1 5TH', '07678887675'), (NULL, 'Penny', 'O''Reilly',
'The Nes Crows lane', 'Haren den', 'Herts', 'Al3 6TY',
'088767545321'), (NULL, 'Humerah', 'Kahn', 'Flat6 station road',
'letchworth', 'Herts', 'SG5 1AG', '07678089098'), (NULL, 'Paul',
'Connor', '45 High street', 'St Albans', 'Herts', 'Al3 4TH',
'07675434561'), (NULL, 'Jean-paul', 'Croix', 'The Hushlings petty
lane', 'St Albans', 'Herts', 'Al2 7YH', '07876546547'), (NULL,
'Seamus', 'O’Malley', '21 Roman Road', 'St Albans', 'Herts', 'Al2
7YH', '07876546547'), (NULL, 'Katie', 'Fergusson', '13 Manor
Screscent', 'St Albans', 'Herts', 'AL4 5YT', '08678089998');

 MemberTypes table
INSERT INTO `priarie`.`membertypes` (`memberTypeID`, `type`,
`subcriptionAmount`) VALUES (NULL, 'Team', '100'), (NULL, 'Full',
'150'), (NULL, 'Social', '75');
 MemberSubscriptions
INSERT INTO `priarie`.`membersubscriptions` (`subID`, `memberID`,
`memberTypeID`, `date`, `method`) VALUES (NULL, '1', '1', '2018-
03-31', 'DD'), (NULL, '2', '1', '2018-03-31', 'DD'), (NULL, '3',
'2', '2018-03-31', 'CC'), (NULL, '4', '1', '2018-03-31', 'Cash'),
(NULL, '5', '2', '2018-03-31', 'CC'), (NULL, '6', '3', '2018-03-
31', 'DC'), (NULL, '7', '3', '2018-03-31', 'DD');

3. Enter league data
 League table
INSERT INTO `priarie`.`leaague` (`leagueID`, `name`,
`contactName`, `telNO`) VALUES (NULL, 'FB1', 'Kelly Holmes',
'0774859965'), (NULL, 'Community 1', 'Ifzal Khan', '0774858521
');
4. Enter team data
Start with
 Season table
INSERT INTO `priarie`.`season` (`seasonID`, `firstDay`,
`lastday`) VALUES (NULL, '2017-09-09', '2018-05-04'), (NULL,
'2017-08-15', '2018-08-15');
 Team table
INSERT INTO `priarie`.`team` (`teamID`, `name`, `sportID`,
`seasonID`, `cpatainID`, `leagueID`) VALUES (NULL, 'Prairie
Dogs', '1', '1', '6', '1'), (NULL, 'Prairie Shots', '2', '2',
'5', '2');
 TeamMembers table

INSERT INTO `priarie`.`teammembers` (`memberID`, `teamID`) VALUES
('6', '1'), ('4', '1'), ('1', '1'), ('5', '2'), ('7', '2');
5 Enter sports and opponent data
 Sport table
INSERT INTO `priarie`.`sport` (`sportID`, `name`) VALUES (NULL,
'Football'), (NULL, 'Golf');
 Opponent table
INSERT INTO `priarie`.`opponent` (`oponentID`, `name`,
`location`, `contact`) VALUES (NULL, 'Lions', 'Conbine Club',
'0765787656'), (NULL, 'Harriers', 'Thompson Centre',
'07678089098'), (NULL, 'Colne Raven', 'St Leots sports center',
'0765676876'), (NULL, 'Creven Edge', 'Craven Cottage',
'0789876566'), (NULL, 'Furious ', 'Fitness first', '098999987'),
(NULL, 'Argents', 'The Crown Club', '09998789098');

6 Enter fixture data
 Fixture table
INSERT INTO `priarie`.`fixture` (`fixtureID`, `teamID`,
`opponentID`, `location`, `result`, `score`, `date`) VALUES
(NULL, '1', '1', 'Home', 'Win', '2:1', '2017-09-09'), (NULL, '1',
'2', 'Away', 'Lose', '5:1', '2017-09-14'), (NULL, '1', '3',
'Home', 'lose', '2:1', '2017-09-21'), (NULL, '1', '4', 'Home',
'Win', '4:3', '2017-09-28'), (NULL, '1', '5', 'Away', 'Draw',
'4:4', '2017-10-25'), (NULL, '2', '6', 'Away', 'Win', '2:1',
'2017-11-11');
7 Write a query that selects the first name and surname of team
members alphabetical order of surname
Query
select firstname, lastname from member order by lastname;

8 Write a query that selects all home fixtures by date in descending
order
Query
select * from fixture where location='Home' ORDER BY date DESC;
9 Write a query that counts all the fixtures where the result is a win
Query
select count(fixtureID) from fixture where result='Win';
10 Write a query that returns the all fixture details where the result is
‘Lose’ ordered by date
Query
select * from fixture where result='Lose' ORDER By date;
11 Write a query that selects all Member First Names, Surnames and
Membership Type who pay by Direct Debit grouped by Member Type
Query
select firstname, lastname from member
inner join membersubscriptions on
member.memberId=membersubscriptions.memberID
inner join membertypes on
membersubscriptions.membertypeID=membertypes.membertypeID
where membersubscriptions.method='DD' group by membertypes.type;

12. Update the subscription amount for Team to £120
Query
update membertypes set subcriptionamount=120 where type='Team';
13 Add a new membership type ‘Casual’
Query
INSERT INTO `priarie`.`membertypes` (`memberTypeID`, `type`,
`subcriptionAmount`) VALUES (NULL, 'Casual', '50');
14 Update the membership type for Jake Smith from Team to Casual
Query
update membersubscriptions set memberTypeID=4 where memberID=1;
15 Delete the opponent team Colne Ravens
Query
Delete from opponent where name='Colne Ravens';

Task 3 Assessment
Assumptions
While modelling the database for the Prairie organization the following assumptions were made on top
of the case study to make it more help achieve a better database model.
 After registration of a member, the member is supposed to make a subscription for the type of
membership that the member wants thus I have introduced membersubsciption table which
records subscriptions made by members. The justification for introducing a member
subscription table is for subsequent subscriptions. For example after one year is over and the
initial subscription made by the member is done, then the member has to pay for another
subscription for the new year and this record is recorded as a subscription record.
Initial design
The final design of the database of the database was achieved from a series of steps upto when the final
design which was implemented was realized. These steps are;
 The first step was to evaluate the case study to come up with the entities. After identifying all
the entities and their attributes, the next step was normalization to make sure the final relations
that were going to be achieved were going to enforce data integrity by ensuring there is data
consistency.
 Normalization was done in three levels; 1NF, 2NF then 3NF. At 3NF the relations were ready to
be implemented as tables.
 The tables achieved in 3NF were then transferred to a data dictionary to define the data types
and the constraints existing in the relations. The data dictionary was to help during the
implementation of the database.
 Implementation of the database was done using SQL and the relations that I attained in 3NF
were turned to tables and the data inserted in the tables.
How requirements have been met
Considering the final design which has been achieved as a result of implementation of the database all
requirements have been met because the final database enforces data integrity by ensuring consistency
in the data that is saved in the database. The initial design objective was to make a completely relational
database where very table is related to another table and according to the final design as shown in
figure of the entity relationship diagram database is fully relational. Data can also be inserted and
fetched from the database as specified in the requirements document thus the final design has achieved
all the requirements.
Bibliography
Guru99. (2018).
What is Normalization? 1NF, 2NF, 3NF & BCNF with Examples. [online] Available at:
https://www.guru99.com/database-normalization.html [Accessed 26 Apr. 2018].
Hingorani (2017).
reinforcing database concepts by using entity relationships diagrams. [online]
ebscohost. Available at: http://web.b.ebscohost.com/abstract?
direct=true&profile=ehost&scope=site&authtype=crawler&jrnl=15297314&AN=125260222&h=Igf

%2fKVM34Y7EBoiarV74WdUA5bvhkR7PHoBbxJ8BSC2flYMZH6v5TKxRKHtiUN1zhYsbxV3vAxfQh5nUsmcs
wA%3d%3d&crl=c&resultNs=AdminWebAuth&resultLocal=ErrCrlNotAuth&crlhashurl=login.aspx
%3fdirect%3dtrue%26profile%3dehost%26scope%3dsite%26authtype%3dcrawler%26jrnl
%3d15297314%26AN%3d125260222 [Accessed 27 Apr. 2018].
Kaula, R. (2007).
Normalizing with Entity Relationship Diagramming. [online] The Data Administration
Newsletter. Available at: http://tdan.com/normalizing-with-entity-relationship-diagramming/4583
[Accessed 27 Apr. 2018].
Millamila, M. (2014).
Entity Relationship Diagrams and Normalization. [online] prezi. Available at:
https://prezi.com/tunrndt0nin4/entity-relationship-diagrams-and-normalization/ [Accessed 27 Apr.
2018].