Moving to second form

Guys, how would you create second form of th开发者_JS百科is table (primary key is: {isbn,copy}):

isbn AB-1234-X

authorID IC45

authorName I.Conn

title The final curtain

copy 2

classification Detectivefiction

userID xyz44

A relation is in 2NF iff

• it's in 1NF, and
• every non-prime attribute is dependent on the whole of every candidate key (not on just part of any candidate key)

The only candidate key is {isbn, copy}. So the question becomes three questions.

1. Is this relation in 1NF?
2. Are any of the non-prime attributes {authorID, authorName, title, classification, userID} dependent only on {isbn}?
3. Are any of the non-prime attributes {authorID, authorName, title, classification, userID} dependent only on {copy}?

What do you think?

Later . . .

Then I'm creating separate table with attributes: isbn, autorID, autorName, title, Classification and another table with attributes: isbn, copy, userID.

Yes. In "relational speak", you replaced the original relation R with these two projections.

• R₁ = {isbn, copy, userid}
• R₂ = {isbn, authorid, authorname, title, classification}

If you've done that correctly, you should be able to create R again by joining R₁ and R₂ on {isbn}.

Now both R₁ and R₂ are in 2NF. (I think that was the point of the homework question.) You might want to consider whether R₁ and R₂ are in

• 3NF
• BCNF
• 4NF
• 5NF

Still later . . .

Speaking informally, a relation is in 3NF iff

• it's in 2NF, and
• there are no transitive dependencies.

When I say "it's in 2NF", I mean the relation in question is in 2NF and it's not already in 3NF, BCNF, 4NF, or 5NF.

What normal form are R₁ and R₂ in? You'll want to explain your reasoning, otherwise your lecturer is liable to make you look foolish. And we don't want that.

• R₁ = {isbn, copy, userid}
• R₂ = {isbn, authorid, authorname, title, classification}

And still later . . .

R₁ is in 5NF. R₂ is in 2NF.

R₂ isn't in 3NF, because there's a transitive dependency between "isbn" and "authorname".

• isbn->authorid, and
• authorid->authorname

Remove this transitive dependency by replacing R₂ with these two projections (R₃ and R₄).

• R₁ = {isbn, copy, userid} (5NF)
• R₃ = {isbn, authorid, title, classification}
• R₄ = {authorid, authorname}

I don't think there's a functional dependency between title and classification.

Have your classification & author in a separate table, like so:

Book Table:

isbn AB-1234-X

authorID IC45

title The final curtain

copy 2

classificationID 1

userID xyz44

Author Table:

AuthorID

AuthorName

Classfication:

ClassificationID

ClassificiationName