Design and Normalization of a Database for a Sports Club

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