Using the types of entities found in the Henry Books database(books, authors, an
ID: 3613034 • Letter: U
Question
Using the types of entities found in the Henry Books database(books, authors, and publishers), create an example of a table thatis in first normal form but not in second normal form and anexample of a table that is in second normal form but not in thirdnormal form. In each case, justify your answer and show howto convert to the next higher form.
Using the types of entities found in the Henry Books database(books, authors, and publishers), create an example of a table thatis in first normal form but not in second normal form and anexample of a table that is in second normal form but not in thirdnormal form. In each case, justify your answer and show howto convert to the next higher form.
Explanation / Answer
Following is an example of a table that is in first normal formbut not in second normal form:
BOOKCODE
PUBLISHERCODE
PRICE
PAPERBACK
01
AA
7.99
Yes
02
BB
8.25
Yes
01
BB
24.34
No
03
AA
32.4
No
Here, same book code can be assigned to different publishers orsame publisher may be having various book codes. So only thecomposite key { BOOKCODE, PUBLISHERCODE } qualifies as a candidatekey for the table. But PAPERBACK attribute is dependent onpublisher code alone which is a part of candidate key. Thereforethe table is not in 2NF. 2NF states that a fact in a table mustdepend on entire composite Primary Key not just part of the PrimaryKey
2NF alternative to this design would represent the sameinformation in two tables:
BOOKCODE
PUBLISHERCODE
PRICE
01
AA
7.99
02
BB
8.25
01
BB
24.34
03
AA
32.4
PUBLISHERCODE
PAPERBACK
AA
Yes
BB
Yes
BB
No
AA
No
Now the tables are in 2NF.
Following is an example of a table that is in second normal formbut not in third normal form:
BOOKCODE
PUBLISHERCODE
PAPERBACK
01
AA
Yes
02
BB
Yes
01
BB
No
03
AA
No
The above table violates third normal form as it states that NoDependencies on Non-Key Attributes. PAPERBACK depends on thePUBLISHERCODE attribute.
It can be modified in the following way:
BOOKCODE
PUBLISHERCODE
01
AA
02
BB
01
BB
03
AA
PUBLISHERCODE
PAPERBACK
AA
Yes
BB
Yes
BB
No
AA
No
BOOKCODE
PUBLISHERCODE
PRICE
PAPERBACK
01
AA
7.99
Yes
02
BB
8.25
Yes
01
BB
24.34
No
03
AA
32.4
No
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.