Week 6

Question 1

What is normalization?

Answer 1

Normalization is the process for evaluating and correcting table structures to minimize data redundancies and data anomalies.

Question 2

What are the characteristics of well normalized table structures?

Answer 2

The characteristics of well normalized table structure are:

• Each table represents a single subject
• No data item will be unnecessarily stored in more than one table
• All attributes in a table are dependent on the primary key

Question 3

What are the three normal forms?

Answer 3

The three normal forms are:
1. First normal form (1NF)
2. Second normal form (2NF)
3. Third normal form (3NF)

Question 4

Why is normalization needed?

Answer 4

Normalization is needed because it:

• Is an important part of designing a new database structure
• Analyses the relationship among the attributes within each entity
• Determines if the structure can be improved
• Improves the existing data structure and creates an appropriate database design(Each table is free from anomalies of insertion, update, and deletion)

Question 5

What are the steps for normalization?

Answer 5

1. Identify dependencies of each table
2. Progressively break the table into new set of tables

Question 6

What is functional dependency?

Answer 6

Functional dependency is where the value of attribute A determines one and only one value of attribute B

Question 7

What is partial dependency?

Answer 7

Partial dependency is where an attribute value depends on a portion of a composite primary key. For example:

If (A+B) –> (C, D, …)

Where (A+B) is the primary key

If B –> C (C is dependant on B)
Then this would be a partial dependency because attribute C depends on only a part of the composite primary key (Which is B)

Question 8

What is transitive dependency?

Answer 8

Transitive dependency is where a non-primary key attribute is dependent on another non-primary key attribute. For example:

If (A+B) –> (C, D, …)

Where (A+B) is the primary key

If C –> D (D is dependant on C)
Then this would be a transitive dependency because D is a non-primary key attribute and depends on C which is also a non-primary key attribute.

Question 9

How do you organise dependencies in a dependency diagram?

Answer 9

Desirable dependencies should go above the diagram, such as dependency based on primary key whereas less desirable dependencies such as partial and transitive dependencies should go below the diagram.

Question 10

When is a table considered to be in first normal form (1NF)?

Answer 10

A table is considered to be in first normal form (1NF) when:

• There are no repeating groups in the table
• All the key attributes are defined
• All attributes are dependent on the primary key
• All relational tables satisfy 1NF requirements
• May contain other (partial or transitive) dependencies thus subject to data redundancies and various anomalies

Question 11

When is a table considered to be in second normal form (2NF)?

Answer 11

A table is considered to be in second normal form (2NF) when:

• It is in 1NF and
• It contains no partial dependencies (no attribute is dependant on only a portion of a composite primary key)

Question 12

When is a table considered to be in third normal form (3NF)?

Answer 12

A table is considered to be in third normal form (3NF) when:

• It is in 2NF and
• It contains no transitive dependencies (no non-primary key is dependent on another non-primary key attribute)

Question 13

What are the steps for conversion to First Normal Form (NNF –> 1NF)

Answer 13

1. Eliminate the repeating groups
2. Identify the primary key
3. Identify all dependencies

Question 14

What is a surrogate key?

Answer 14

A surrogate is a system defined attribute that is used when the primary key is considered to be unsuitable. They are created and managed via the DBMS