A college bookstore currently maintains the following relational tables: The fie

Question

A college bookstore currently maintains the following relational tables: The fields in each table are shown in parenthesis. The primary key field(s) are underlined ORDER ORDER-NO, O-DATE, INSTRUCTOR-NO, INSTRUCTOR-NAME, INSTRUCTOR-DEPT) ORDERED-BOOK (ORDER-NO. ISBN-NO, BOOK-TITLE, BOOK-AUTHORS, COURSE-NO, COURSE- NAME) Apart from the dependencies suggested by the primary keys, assume only the following functional dependencies: INSTRUCTOR-NO] [INSTRUCTOR-NAME, INSTRUCTOR-DEPT] [ISBN-NO] [COURSE-NO] [COURSE-NAME > [BOOK-TITLE, BOOK-AUTHORS] Decompose the given tables to tables in 3 NF Answer:

Explanation / Answer

Original tables:

Order

(Order-NO, O-DATE, INSTRUCTOR-NO, INSTRUCTOR-NAME, INSTRUCTOR-DEPT)

ORDERED-BOOK

(ORDER-NO, ISBN-NO, BOOK-TITLE, BOOK-AUTHORS, COURSE-NO, COURSE-NAME)

1 NF:

For a table to be in 1NF it has to have atomic values.Since the tables does not have any values we cannot determine if it is in 1 NF or not. Hence assuming it is in 1NF.

2NF:

A table is said to be in 2NF if both the following conditions hold:

In the order table we see the relation, INSTRUCTOR-NO ->[INSTRCTOR-NAME, INSTRUCTOR-DEPT]

And

ORDER-NO->ORDER-DATE

But here Order-NO is the primary key. Hence this table is not in 2NF.

Decomposing it to 2NF.

Instructor

(INSTRUCTOR-NO, INSTRCTOR-NAME, INSTRUCTOR-DEPT)

Order

(ORDER-NO, ORDER-DATE, INSTRUCTOR-NO, ISBN-NO, COURSE-NO)

In the table ORDERED-BOOK, we see the relation

ISBN-NO -> BOOK-TITLE, BOOK-AUTHORS

COURSE-NO -> COURSE-NAME

Here we see ORDER-NO, ISBN-NO is the key of the table. Hence all the fields are not fully dependent on the key of the table hence it is not in 2 NF.

Decomposing the table to 2NF:

Books

(ISBN-NO, BOOK-TITLE, BOOK-AUTHORS)

Course

(COURSE-NO, COURSE-NAME)

3NF:

A table design is said to be in 3NF if both the following conditions hold:

Here the tables Instructor, Order, Course, and Books are already in 3NF, as they do not have any transitive dependencies in within the fields.

I have answered your question. Please do not forget to give a positive feedback to the answer. Thank you.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

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