C207 <Database Systems>

Week 15 - Revision Exercise

---------------------------------------------------------------------------------------------------------------
Section D: Normalisation

You have been asked to normalise a database for a swmming club. The data that is to be stored in this
database relates to the swimmers, swimmer skills and levels. You have been given the following form:

Swimmer Number : SK12001

Swimmer Name : Adam Smith
School Number : T2037
School Name : ACC High School
School Location : 12 Queenstreet

Skills Info:
Skill Skill
Skill Skill Number Name Skill Skill Skill Skill Skill
Number 1 Number 2 3 1 Name 2 Name 3 Level 1 Level 2 Level 3

Competitive
1 2 3 Basic Swimming Life-saving Gold Silver Bronze

Do the following:
1) Normalise the database to the first normal form (1NF).
2) Normalise the database to the second normal form (2NF)
3) Normlase the database to the third normal form (3NF)
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

Steps and solutions:

Step 1: Before doing normalization, first ask what is the purpose of the form or report? What is the
meaning of every piece of information that is presented on this form? In this case, it’s about the
skills of a swimmer, i.e swimmer’s information.

Step 2: With the above understanding, proceed to applying the 1NF rules:

2.1) Eliminate repeating groups –

a) How many repeating groupings are there?

Answer: ONE repeating group with three repeated columns.

2
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

b) Each repeating group of attributes then forms a new entity (Table). If you have x
number of repeating groups, then you will have x number of new tables. In this case,
since we have only one repeating group, in 1 NF, we then have the main table plus one
new table resulted from the repeating group. Details as follows:

Entity 1 (Table 1):

swimmer_info(swimmer_number, swimmer_name, school_number, school_name,

school_location)

Entity 2 (Table 2) – resulted from the one repeating group:

skill(skill_number, skill_name, skill_level)

2.2) Every table must have a unique identifier or primary key. So decide a key (highlighted
in blue) for every table, as follows:

Entity 1 (Table 1):

swimmer_info(swimmer_number, swimmer_name, school_number, school_name,

school_location)

Entity 2 (Table 2):

a) Since skill info is an additional piece of info related to the swimmer, naturally, we have
to assume swimmer_number to become the key for this table too:

swimmer_skill(swimmer_number, skill_number, skill_name, skill_level)

b) After “assuming” the key, ask ourselves if it is unique? If not, we have to make it
unique. In this case, if we only take swimmer_number as the key, then one swimmer can
only have one skill and this is not true. To allow one swimmer to have more than one skill,
we need to include the skill_number to become part of the key to ensure its uniqueness.
This forms a composite key for the swimmer_skill table. After the revision, here’s how the
structure of the table looks like:

swimmer_skill(swimmer_number, skill_number, skill_name, skill_level)

2.3) Eliminate calculated/derived column(s):

No such column sighted for elimination, so remain status quo.

3
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

2.4) After applying the 1NF rules, you should have two tables (attributes highlighted in
blue are the primary keys and red are the foreign keys):

Table 1 : swimmer_info(swimmer_number, swimmer_name, school_number,

school_name, school_location)

Table 2: swimmer_skill(swimmer_number, skill_number, skill_name, skill_level)

2.5) Violating the 1NF rules will mean that the table(s):
a) Have no primary keys
b) Have repeating groups
c) Have calculated/derived columns

4
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

Step 3: Apply 2NF rules

3.1) In 1NF ? – Yes

3.2) No partial dependencies:

a) Since partial dependencies only apply to tables with compound or composite primary
keys, then we only need to review swimmer_skill table to see if it can be normalized
further.

b) In swimmer_skill table, we understand that skill_name is dependent on skill_number and

so we say it is partially dependent on the primary key. As such, it should be dropped
(eliminated) from this table to form a new table with skill_number as the primary key.
What about skill_level? In this case, skill level is dependent on the swimmer, ie
swimmer_number rather than the skill_number, hence it should stay within the same
table. With this understanding, a new table is formed with some changes to the current
table, as follows:

Table 2: swimmer_skill(swimmer_number, skill_number, skill_level)

Table 3: skill_info(skill_number, skill_name)

3.3) After applying the 2NF rules, you should have three tables (attributes highlighted in
blue are the primary keys and red are the foreign keys):

Table 1 : swimmer_info(swimmer_number, swimmer_name, school_number,

school_name, school_location)

Table 2: swimmer_skill(swimmer_number, skill_number, skill_level)

Table 3: skill_info(skill_number, skill_name)

3.4) Violating the 2NF rules will mean that the table(s):
a) In 1NF form
b) Consist of partial dependencies.

5
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

Step 4: Apply 3NF rules

4.1) In 2NF ? – Yes

4.2) No transitive dependencies:

a) Review the three tables to see if transitive dependencies exist. In this case, we can
see that transitive dependency happens in table 1 because both school_name and
school_location are dependent on school_number and school_number is dependent
on swimmer_number. As such, school_name and school_location are transitive
dependent on swimmer_number. In this case, swimmer_number is the determinant
for school_number and school_number is the determinant for school_name and
school_location.

Note that a dependent attribute is an attribute whose value can be determined by

another attribute. A determinant is a column or set of columns that functionally
determines another column.

b) With the above identification, we have to drop or eliminate the two dependent
attributes (school_name, school_location) to form a new table and turn
school_number to become a foreign key in the existing table.

4.3) After applying the 3NF rules, you should have four tables (attributes highlighted in blue
are the primary keys and red are the foreign keys):

Table 1 : swimmer_info(swimmer_number, swimmer_name, school_number)

Table 2: school_info( school_number, school_name, school_location)

Table 3: swimmer_skill(swimmer_number, skill_number, skill_level)

Table 4: skill_info(skill_number, skill_name)

4.4) Violating the 3NF rules will mean that the table(s):
a) In 2NF form
b) Consist of transitive dependencies.

6
C207 <Database Systems>
Week 15 - Revision Exercise
----------------------------------------------------------------------------------------------------------------------------------

Step 5: Review the tables to ensure the following:

a) Every table must have a primary key

b) Their relationships are established with the appropriate use of primary keys and/or
foreign keys.
c) The attributes are grouped logically which make biz sense to you and others.