Search code examples
database-designdatabase-normalization

Moving to second form

Guys, how would you create second form of this 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

Solution

  • 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.

    • R1 = {isbn, copy, userid}
    • R2 = {isbn, authorid, authorname, title, classification}

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

    Now both R1 and R2 are in 2NF. (I think that was the point of the homework question.) You might want to consider whether R1 and R2 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 R1 and R2 in? You'll want to explain your reasoning, otherwise your lecturer is liable to make you look foolish. And we don't want that.

    • R1 = {isbn, copy, userid}
    • R2 = {isbn, authorid, authorname, title, classification}

    And still later . . .

    R1 is in 5NF. R2 is in 2NF.

    R2 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 R2 with these two projections (R3 and R4).

    • R1 = {isbn, copy, userid} (5NF)
    • R3 = {isbn, authorid, title, classification}
    • R4 = {authorid, authorname}

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