Normalization

Question 1

Define Normalization

Answer 1

→ is a database design technique that reduces data redundancy and eliminates undesirable characteristics like Insertion, Update and Deletion Anomalies.
→ Normalization rules divides larger tables into smaller tables and links them using relationships.
→ Normalization is typically carried out through a series of steps called normal forms:
○ 1NF (First Normal Form)
○ 2NF (Second Normal Form)
○ 3NF (Third Normal Form)
○ BCNF (Boyce-Codd Normal Form)
○ 4NF (Fourth Normal Form)
○ 5NF (Fifth Normal Form)
○ 6NF (Sixth Normal Form)

Question 1

1NF (First Normal Form)

Answer 1

→ Each table cell should contain a single value.
→ Each record needs to be unique.

Question 2

Primary Key

Answer 2

→ A primary is a single column value used to identify a database record uniquely.
→ It has following conditions:
○ A primary key cannot be NULL
○ A primary key value must be unique
○ The primary key values should rarely be changed
○ The primary key must be given a value when a new record is inserted.

Question 3

Composite Key

Answer 3

→ A composite key is a primary key composed of multiple columns used to identify a record uniquely

Question 4

Functional Dependency

Answer 4

In a functional dependency X→YX→Y, where X and Y are sets of attributes, the value of X uniquely determines the value of Y. In other words, if you know the value of X, you can predict the value of Y.

Question 5

2NF (Second Normal Form)

Answer 5

→ Be in 1NF
→ All non-key attributes are fully functionally dependent on the entire composite primary key.

Question 6

Foreign Key

Answer 6

→ Foreign Key references the primary key of another Table! It helps connect your Tables
→ A foreign key can have a different name from its primary key
→ It ensures rows in one table have corresponding rows in another
→ Unlike the Primary key, they do not have to be unique. Most often they aren’t
→ Foreign keys can be null even though primary keys can not

Question 7

Transitive Functional Dependency

Answer 7

A transitive functional dependency is when changing a non-key column, might cause any of the other non-key columns to change.

Question 8

3NF (Third Normal Form)

Answer 8

→ Be in 2NF
There are no transitive dependencies among the non-key attributes.

Question 9

Super Key

Answer 9

→ A super key is any set of attributes (one or more) that can uniquely identify a tuple in a table.
→ It may include more attributes than necessary to uniquely identify tuples.
→ Super keys are used to understand the possible ways of uniquely identifying data but may not be the most minimal or efficient.

Question 9

BCNF (Boyce-Codd Normal Form)

Answer 9

→ an extension of 3NF
→ A table is in BCNF if every functional dependency X → Y, X is the super key of the table.

Question 10

Candidate Key

Answer 10

→ A candidate key is a minimal super key, meaning it is a subset of a super key, and removing any attribute from it would result in a loss of uniqueness.
→ Candidate keys are potential choices for the primary key of a table.