CHP8 NORMALISATION

Question 1

Normalisation

Answer 1

• Process for evaluating and correcting table structures to minimise data redundancies

Question 2

Stages of normalisation

Answer 2

1. First normal form (1NF) no repeating groups and PK is identified
2. Second normal form (2NF) - no partial dependencies
3. Third normal form (3NF) - no transitive dependencies = most business database goes as high as 3NF for normalisation process

Question 3

Purpose of normalisation

Answer 3

• All attributes in a table are dependent on the primary key
• To ensure data is uniquely identifiable by a primary key value

Question 4

Steps to reach 1NF

Answer 4

1. Eliminate repeating groups and nuls and appropriate null value.
2. Identify the primary key.
   - Using composite key which is the combination of two attributes (more than one primary key)
3. Identify all dependencies
   Transitive and partial dependencies

Question 5

Two types of dependencies:

Answer 5

1. Partial dependencies - A dependency based on only a part of a composite primary key is a partial dependency
   - EG PROJ_NAME depends on PROJ_NUM and EMP_NAME depends on EMP_NUM.
2. Transitive dependencies - transitive dependency is a dependency from one nonprime to another nonprime attribute(not connected to any key)

Question 6

Steps to reach 2NF

Answer 6

• Write each key component, then write the composite key on the last line.
• Assign corresponding depending attributes such as
  PROJ_NUM -> PROJ_NAME
  EMP_NUM->EMP_NAME,JOB_CLASS
  PROJ_NUM,EMP_NUM->ASGN_HRS
• 3 total tables

Question 7

Steps to reach 3NF

Answer 7

• Write its PK in the new interdeminant
• Eliminate transitive dependencies to create more tables