Log out Go to App
Go to App

Learning Materials

Features

Discover

Chapter 10: Problem 33

Consider the following relation for published books: BOOK (Book_title, Authorname, Book_type, Listprice, Author_affil, Publisher) Author_affil refers to the affliation of author. Suppose the following dependencies exist: Book_title \(\rightarrow\) Publisher, Book_type Book_type \(\rightarrow\) Listprice Authorname \(\rightarrow\) Author-affil a. What normal form is the relation in? Explain your answer. b. Apply normalization until you cannot decompose the relations further. State the reasons behind each decomposition.

Short Answer

Expert verified
The given relation is in Second Normal Form (2NF). It can be decomposed to two relations R1(Book_title, Publisher, Authorname, Author_affil) and R2(Book_type, Listprice) to reach Third Normal Form (3NF) and further to BCNF as there are no more dependencies that violate the BCNF condition.

Step by step solution

01

Analyzing the Dependencies

From the question, the functional dependencies are Book_title \(\rightarrow\) Publisher, Book_type, Book_type \(\rightarrow\) Listprice and Authorname \(\rightarrow\) Author_affil. To determine the normal form, check if these dependencies satisfy the conditions for 1NF, 2NF, 3NF and BCNF.
02

Determining the Normal Form

The relation is in 1NF because each attribute has atomic values. It's in 2NF because there are no partial dependencies (an attribute dependent on part of the primary key), assuming the primary key is Book_title. It's not in 3NF because there is a transitive dependency (Book_type \(\rightarrow\) Listprice and Book_title \(\rightarrow\) Book_type). Therefore, this relation is in 2NF.
03

Decomposing to 3NF

To remove the transitive dependency and achieve 3NF, decompose the relation into two: R1(Book_title, Publisher, Authorname, Author_affil) and R2(Book_type, Listprice). Now, in R1, Book_title \(\rightarrow\) Publisher, Authorname and in R2, Book_type \(\rightarrow\) Listprice, therefore these two relations are in 3NF. The reasons behind this decomposition is to remove the transitive dependency, maintain data integrity and reduce redundancy.
04

Decomposing to BCNF

The relations R1 and R2 are already in Boyce-Codd Normal Form (BCNF) because for each relation, for every one of its dependencies X \(\rightarrow\) Y, X is a super key. This means we cannot decompose the relations further

Unlock Step-by-Step Solutions & Ace Your Exams!

  • Full Textbook Solutions

    Get detailed explanations and key concepts

  • Unlimited Al creation

    Al flashcards, explanations, exams and more...

  • Ads-free access

    To over 500 millions flashcards

  • Money-back guarantee

    We refund you if you fail your exam.

Start your free trial

Over 30 million students worldwide already upgrade their learning with Vaia!

One App. One Place for Learning.

All the tools & learning materials you need for study success - in one app.

Get started for free

Most popular questions from this chapter

Discuss insertion, deletion, and modification anomalies. Why are they considered bad? Illustrate with examples.

Consider the relation \(R,\) which has attributes that hold schedules of courses and sections at a university; \(R=\\{\) CourseNo, SecNo, OfferingDept, Credit- Hours, CourseLevel, InstructorSSN, Semester, Year, Days_Hours, RoomNo, NoOfStudents \(\\} .\) Suppose that the following functional dependencies hold on \(R:\) \\{CourseNo \(\\} \rightarrow\\{\text { OfferingDept, CreditHours, CourseLevel }\\}\) \\{CourseNo, SecNo, Semester, Year) \(\rightarrow\) \\{Days_Hours, RoomNo, NoOfStudents, InstructorSSN \(\left\\{\mathrm{R}_{\text {oom }} \mathrm{No}, \text { Days_Hours, Semester, Year }\right\\} \rightarrow\\{\) Instructorssn, CourseNo, SecNot Try to determine which sets of attributes form keys of \(R .\) How would you normalize this relation?

What undesirable dependencies are avoided when a relation is in \(2 \mathrm{NF}\) ?

What is a minimal set of functional dependencies? Does every set of dependencies have a minimal equivalent set? Is it always unique?

Consider a relation \(R(A, B, C, D, E)\) with the following dependencies: \\[ A B \rightarrow C, C D \rightarrow E, D E \rightarrow B \\] Is \(A B\) a candidate key of this relation? If not, is \(A B D ?\) Explain your answer.
See all solutions

Recommended explanations on Computer Science Textbooks

Data Structures

Read Explanation

Algorithms in Computer Science

Read Explanation

Computer Network

Read Explanation

Databases

Read Explanation

Computer Systems

Read Explanation

Problem Solving Techniques

Read Explanation
View all explanations

What do you think about this solution?

We value your feedback to improve our textbook solutions.

Study anywhere. Anytime. Across all devices.

Sign-up for free