Normal Form

Question 1

Normalisation of data

Answer 1

A process of analyzing the given relation schemas based on their Functional Dependencies and primary keys to achieve the desired properties of:

• minimizing redundancy
• minimizing the insertion, deletion and update anomalies

Question 2

Normal Form

Answer 2

A series of rules that sun to reduce data redundancy and inconsistencies by organising data in a structured manner.

The normalisation process involves breaking down database into smaller, more manageable parts called tables and applying set of rules to ensure that each table contains unique and relevant information.

Question 3

First Normal Form

Answer 3

1NF: A table is in 1NF if it has a primary key and no repeating groups of data.

Question 4

Second Normal Form

Answer 4

2NF: A table is in 2NF if it is in 1NF and all non-key attributes are fully functionally dependent on the primary key

Question 5

3NF
Third Normal Form

Answer 5

No transitive dependencies for non-prime attributes

Question 6

Fourth Normal Form

Answer 6

4NF: A table is in 4NF if it is in 3NF and it doesn’t have any nontrivial multi-valued dependencies (MVDs)

Question 7

Informal Design Guidelines for Relational Databases

Answer 7

Set of best practices and principles that are used to design effective and efficient relational databases:

• Semantics of the relational attributes must be clear
• sideline for redundant information in tuples and update anomalies
• Null values in tuples

Question 8

Guideline 1

Answer 8

• Each tuple in a relation should represent one entity or relationship instance
• Attributes of different entities should not be mixed in the same relation
• Only foreign keys should be used to refer to other entities
• Entity and Relationship attributes should be kept apart as much as possible

Question 9

Functional Dependencies

Answer 9

A functional Dependency indicates that the value of one or more attributes uniquely determines the value of another.

A -> B

The constraint must hold on every relation instance

If K is a key of R then K functionally determines all attributes in R

Question 10

Normalisation of data

Answer 10

A process of analysing the given relation schemas based on their Functional Dependencies and primary keys to minimise:
- redundancy
- insertion, deletion and update anomalies

Question 11

Normal Form

Answer 11

• we verify whether a relation schema is in a particular form using keys and FDs of a relation.
• the highest normal form condition that it meets, indicates the degree of normalisation

Question 12

Non-addictive join Property

Answer 12

Guarantees that the spurious tuples generation problem does not occur with respect to the relation schema after decomposition
It is extremely important and cannot be sacrificed

Question 13

Normalisation

Question 14

Prime attribute

Answer 14

An attribute that is a member of the primary key

Question 15

Full Functional Dependency

Question 16

Second Normal Form
2NF

Answer 16

Every non prime attributes should be fully functionally dependent on the whole key (candidate keys), not just part of it.

Question 17

Update, insertion and deletion anomalies

Question 18

Informal Design Guidelines for Relational Databases

Answer 18

Guideline 1: each tuple in a relation should represent one entity or relationship instance.

Guideline 2: design a schema that does not soccer from insertion, deletion or update anomalies

Guideline 3: relations should be designed such that their tuples will have as few NULL values as possible

Question 19

BCNF

Answer 19

For each functional dependencies the determinant must be a superkey