Q.3. Consider following relation for published books, three functional dependenc

Question

Q.3. Consider following relation for published books, three functional dependencies are also shown BOOK Book titleAutborname BoktvpeListriceAuthoratülPublisher Following is the information for your reference; .BookTitle and Authorname together is the primary key . Author affil referes to the affiliation of the author. Assume that following three functional dependencies hold FDI: Authorname Author affil FD2 Book wwe Listprice FD3: Book title-> Book type, Publisher Questions to answer; (a) What normal form is the above BOOK relation in and why not in higher normal form? Explain your answer. (b) Apply normalization to achieve the highest possible normal form. Explain each decomposition that you do to achieve a particular normal form

Explanation / Answer

Dear Student ,

As per the requirement submitted above , kindly find the below solution.Firstly let's discuss what is normalization.

Normalization : Normalization is process of decomposing large and complex table into simple and smaller form.Normalization helps to remove redundancy means it removes duplicate data from the table and related data stores in the related table.

Question a :

Above Book relation table is in the first normal form.Because all the column in the table is automic in nature also author_affil is depend upon authorname and single author can have more than one book hence here duplication of data exits and needs to normalize further.

Question b :

First Normal Form (1NF) : First Normal Form (1NF) says all the columns in the table should be automic in the nature.In 1NF multivalued columns are not allowed , this means each column should have one value.

Above book relation given in the table is in the first normal form.

Second Normal Form (2NF): Second Normal Form (2NF) says table should be in first normal form and all the columns in the table should depends upon primary key column.In second normal from partial dependancies are not allowed.

Book relation needs to normalize into second normal form (2NF). In book relation Authorname and Author_Affil is related to author hence needs to create a seprate table for storing author information.Likewise for book details needs to store it into seprate tables.Below are the tables.

1.Table Name : Author

Schema : Authorname - Primary key , Author_Affil

2.Table Name : Book

Schema : Book_title-Primary Key , Book_type, Publisher

Here only one column is remaining that is ListPrice which is depends upon Book_type hence which is used to form a relation in Third Normal Form (3NF).

Third Normal Form (3NF) :In Third Normal Form (3NF), It should full fill all the requirements of second normal form and all the columns in the table should be depend upon primary key column. Transitive dependency are not allowed in the 3NF. Transitive dependency means non key column depends upon non key column in the table.

Here table given in the second normal form (2NF) are used to form a relation between tables, this means primary key of one table used as foreign key in another table also a new column which is acts as a BookAuthorID added into the table. Below is the table details.

Table Name : BookAuthor

Schema : BookAuthorID-Primary Key , Book_title - foreign key ,Book_type-foreign key, Listprice

Using above BookAuthor relation , it is possible to get details of book based on title and list of books written by a author.

NOTE : PLEASE FEEL FREE TO PROVIDE FEEDBACK ABOUT THE SOLUTION.

Related Questions

Navigate

• Browse (All)
• Browse Q
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.