Database Normalization: Membership and Machine Booking Forms

Desklib provides past papers and solved assignments for students. This assignment covers database normalization.

Database Assignment Part1

Table of Contents
List of Tables...................................................................................................................................2
List of Figures..................................................................................................................................3
Introduction......................................................................................................................................4
Normalization..................................................................................................................................5
Normalization Definition.............................................................................................................5
Rules of Normalization................................................................................................................5
Membership Form...........................................................................................................................7
UNF.............................................................................................................................................7
1 NF.............................................................................................................................................8
2 NF.............................................................................................................................................9
3 NF...........................................................................................................................................10
Machine Booking Form.................................................................................................................13
UNF...........................................................................................................................................13
1NF............................................................................................................................................13
2NF............................................................................................................................................15
3NF............................................................................................................................................16
ERD...............................................................................................................................................19
Conclusion.....................................................................................................................................21
References......................................................................................................................................22
List of Tables
Table 1: Info Table..........................................................................................................................8
2

Table 2: Member Info Table............................................................................................................9
Table 3: Game & Staff Info Table...................................................................................................9
Table 4: Member Info Table..........................................................................................................10
Table 5: Staff Info Table................................................................................................................10
Table 6: Game Info Table..............................................................................................................10
Table 7: Booking Table.................................................................................................................14
Table 8: Machine Info Table.........................................................................................................14
Table 9: Software Info Table.........................................................................................................15
Table 10: Booking Table...............................................................................................................15
Table 11: Machine Info Table.......................................................................................................16
Table 12: Software Info Table.......................................................................................................16
Table 13: Member Details.............................................................................................................16
Table 14: Booking Info Table........................................................................................................16
Table 15: Connection between Member & Software....................................................................17
Table 16: Machine Info Table.......................................................................................................17
Table 17: Software Info Table.......................................................................................................17
Table 18: Member Details.............................................................................................................18
Table 19: Booking Info Table........................................................................................................18
Table 20: Connection table............................................................................................................18
List of Figures
Figure 1 ERD after 3NF................................................................................................................13
Figure 2 3NF for Machine Booking Form.....................................................................................19
Figure 3 ERD Diagram for Membership Form.............................................................................20
3

Figure 4 ERD for Machine Booking Form....................................................................................21
4

Introduction
This task is containing basically case study of game based project in which we have to need &
requirement of normalization. After understanding this, we have to understand rules which are
used for normalization. There are mainly five normalization rule first normal form, second
normal form, third normal form, BCNF (Boyce Codd Normal Form) & fourth normal form. In
these steps we also learn about basic idea of removing transitive & partial dependency. After that
we have to perform normalization step on given UNF that is membership form. In this we have
divided this whole table into different small tables which doesn’t have any type of redundancy of
data with completely free from transitive dependency & partial dependency.
5

Normalization
Normalization Definition
Normalization is way of managing database & removing repetition of data from database for
better & faster execution of queries. Normalization is widely used in database schema because it
provides flexibility & removing redundancy of data or data columns by separating them
according to their repeated group and new entities are generated with Foreign & Primary key. If
the table is not normalized properly then there are might be possibility that is insertion, updating
& deletion is happened randomly for removing these anomalies (SQL by Design: Why You
Need Database Normalization, 2019)
Rules of Normalization
There are mainly 5 rules of normalization and these are as follows simultaneously for completion
of normalization process for database:
 1NF (First Normal Form)
 2NF (Second Normal Form)
 3NF (Third Normal Form)
 Boyce Codd Normal Form (BCNF)
 4NF (Fourth Normal Form (Database Normalization | Normal Forms - GeeksforGeeks,
2018))
UNF
 UNF stands for Un-normalized form.
 This form is containing complete list of entities of attributes and identification of main
key or primary key which is unique.
 It is also used for identify key constraints & group which are repeated of attributes.
6

1 NF
 1NF is defined as the first normal form which is used for creating a new entity for
attributes for them only who are repeated group.
 The data or information which are going to be stored in data column are should be same
domain meaning is that same data type is used for all.
 Column name or attribute are uniquely identified.
 This above mentioned UNF (un-normalized form) are converted first into 1NF by
removing all repetitive group by breaking & dividing it into more than one tables which
are interconnected via foreign key.
2 NF
 2 NF is defined as second normal form which is only and only processed when there is
table is already in 1NF form.
 It should also make sure that there is no partial dependency in between tables.
 Partial dependency is always occurs when there composite key relation and more than
one table column are depending on primary key & foreign key also.
This above mentioned 1 NF (first normalized forms) are converted first into 2 NF by removing
all dependency by breaking & dividing it into more than one tables which are interconnected via
foreign key.
3 NF
 3 NF is standing for the third normal form which is only and only processed when table is
already in 2 NF (Second Normal Form).
 It is working on concept of removing of transitive dependency because it should not have
any transitive dependency.
 Transitive dependency is occurring by holding the distinction of transitivity. It occurs
when there are three or more than three entities are there in single relation. In this they all
are depending on each other.
7

This above mentioned 2 NF (second normalized forms) are converted first into 3 NF by
removing all dependency by breaking & dividing it into more than one tables which are
interconnected via foreign key.
Membership Form
UNF
Table 1: Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Unique) Double Primary Key
2. Member first name char NOT NULL
3. Member surname char NOT NULL
4. Address line 1 Varchar NOT NULL
5. Address line 2 Varchar NA
6. Address City Varchar NOT NULL
7. Address Postal Code Varchar NOT NULL
8. Telephone Text NOT NULL
9. Email Varchar NOT NULL
10. Subscription Type Char NA
11. Renewal Date Date NA
12. Game Ref (Repetitive & Unique) Varchar Primary Key
13. Game (Repetitive) Char NOT NULL
14. Date (Repetitive) Date NOT NULL
15. Score (Repetitive) Integer NOT NULL
16. Staff ID (Repetitive) Varchar NOT NULL
17. Staff F-Name (Repetitive) Text NOT NULL
18. Staff Surname (Repetitive) Text NA
8

1 NF
Table 2: Member Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Unique) Double Primary Key
2. Member first name Text NOT NULL
3. Member surname Text NOT NULL
4. Address line 1 Varchar NOT NULL
5. Address line 2 Varchar NA
6. Address City Varchar NOT NULL
7. Address Postal Code Varchar NOT NULL
8. Telephone Text NOT NULL
9. Email Varchar NOT NULL
10. Subscription Type Char NA
11. Renewal Date Date NA
Table 3: Game & Staff Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Connected on Member
Info Table via Foreign Key)
Double Foreign Key
2. Game Ref (Unique) Varchar Primary Key
3. Game Char NOT NULL
4. Date Date NOT NULL
5. Score Integer NOT NULL
6. Staff ID Varchar NOT NULL
7. Staff F-Name Text NOT NULL
8. Staff Surname Text NA
9

2 NF
Table 4: Member Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Unique) Double Primary Key
2. Member first name Text NOT NULL
3. Member surname Text NOT NULL
4. Address line 1 Varchar NOT NULL
5. Address line 2 Varchar NA
6. Address City Varchar NOT NULL
7. Address Postal Code Varchar NOT NULL
8. Telephone Text NOT NULL
9. Email Varchar NOT NULL
10. Subscription Type Char NA
11. Renewal Date Date NA
Table 5: Staff Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Connected on Member
Info Table via Foreign Key)
Double Composite Primary Key &
Foreign Key
2. Game Ref (references
Game_Info(Game Ref) )
Varchar Composite Primary Key &
Foreign Key
3. Staff ID (Connected on Staff Info
Table via Foreign Key)
Varchar Foreign Key
4. Date Date NOT NULL
5. Score Integer NOT NULL
Table 6: Game Info Table
S.N. Entity Name Data Type Constraints
1. Game Ref (Unique) Varchar Primary Key
10

2. Game Char NOT NULL
Table7: Staff Info Table
S.N. Entity Name Data Type Constraints
1 Staff ID (Unique) Varchar Primary Key
2. Staff F-Name Text NOT NULL
3. Staff Surname Text NA
3 NF
Table 8: Member Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Unique) Double Primary Key
2. Member first name Text NOT NULL
3. Member surname Text NOT NULL
4. Address line 1 Varchar NOT NULL
5. Address line 2 Varchar NA
6. Address City Varchar NOT NULL
7. Address Postal Code Varchar NOT NULL
8. Telephone Text NOT NULL
9. Email Varchar NOT NULL
10. Subscription Type Char NA
11. Renewal Date Date NA
Table 9: Game Score Info Table
S.N. Entity Name Data Type Constraints
1. Member Ref (Connected on Member
Info Table via Foreign Key)
Double Composite Primary Key &
Foreign Key
11

2. Game Ref (references
Game_Info(Game Ref) )
Varchar Composite Primary Key &
Foreign Key
3. Staff ID (Connected on Staff Info
Table via Foreign Key)
Varchar Foreign Key
5. Date Date NOT NULL
6. Score Integer NOT NULL
Table 10: Game Info Table
S.N. Entity Name Data Type Constraints
1. Game Ref (Unique) Varchar Primary Key
2. Game Char NOT NULL
Table 11 Staff Info Table
S.N. Entity Name Data Type Constraints
1 Staff ID (Unique) Varchar Primary Key
2. Staff F-Name Text NOT NULL
3. Staff Surname Text NA
12