Chapter 6, Problem 6RQ

Program Plan Intro

Normalization:

The process used to minimize data redundancy and dependency in a relational database is known as normalization. The database table is divided into two or more tables and defines the relationship between those tables.

First normal form (1NF):

• If a table does not contain any replicate fields or groups of fields then that model is called as first normal form.
• In this form, entities do not contain any single instance of the repeating variable.
  • It means that the entities contain only one instance of the attributes, multi-valued attributes are neglected.

Second normal form (2NF):

• The value of all non-primary key attributes should be dependent on the primary key attribute.
  • If any attribute is depending on the partial primary key then it should determine the other attributes for an instance of the entity.
• The partial dependencies should be removed from the data model.

Third normal form (3NF):

• The value of any non-primary key attributes will not depend on any other non-primary key attributes.
  • If any non-primary key attributes depend on any other non-primary key attribute then it should be moved or deleted.
  • It is termed as transitive dependency.

Partial dependency:

A partial dependency exists at that time of an attributes depends only a part of primary key. This dependency is related with 1^st normal form.

Transitive dependency:

A transitive dependency exists at that time of an attributes depends on another attribute which is not part of primary key.

Functional dependency:

An association between two attributes or two set of attributes in a same relational database table, which is having some constraints is known as functional dependency.

• In a table one attribute is functionally dependent on another attribute to take one value.

Explanation of Solution

b.

Dependencies diagrams for each database table:

Table1:

Create the database table with name of Table1 is given below:

Table1 (C1, C2)

• C2 are partial dependent on C1.

Normal form:

• The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram for table1 is shown below:

Explanation of Solution

c.

Dependencies diagrams for each database table:

Table1:

Create the database table with name of Table1 is given below:

Table1 (C1, C2)

• Here, C1 indicate the primary key.
• C2 are partial dependent on C1.

Normal form:

• The relation is in third normal form (3NF), since there is no transitive dependency and no repeated attributes.

The representation of dependency diagram for table1 is shown below:

Table2:

Create the database table with name of Table2 is given below:

Table2 (C1, C3, C4)

• Here, C1 and C3 indicate the primary key and C1 indicates the foreign key that belongs to Table1 and C4 indicates the foreign key that belongs to Table3...