Chapter 6, Problem 7RQ

a.

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.

b.

Explanation of Solution

Dependencies diagrams for each database table:

Table1:

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

Table1 (ISBN, BookTitle, Edition)

• Here, “ISBN” indicates the primary key.
• “BookTitle” and “Edition” are partial dependent on ISBN.

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 (BookTitle, Publisher)

•  “BookTitle” are transitive dependent on “Publisher”.

Normal form:

• The relation is in second normal form (2NF), because there is transitive dependency in this database table.

The representation of dependency diagram for table2 is shown below:

Table3:

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

Table3 (Author_Num, LastName)

• Here, “Author_Num” indicate the primary key...