databasedatabase-design3nfbcnf

Is this database 3NF or BCNF?


I have this table that the rules are: A student is examined in a discipline and obtains a position on the class list. It is known that two students cannot obtain the same position in the same subject. Is this a 3NF database or BCNF, i cant have a 100% sure answer to this

enter image description here


Solution

  • -- Student STUDENT examined in course COURSE
    -- obtained position POSITION on class list.
    --
    test {STUDENT, COURSE, POSITION}
     KEY {STUDENT, COURSE}
     KEY {COURSE, POSITION}
    

    Nontrivial FDs:

    {STUDENT, COURSE}   --> {POSITION}
    {COURSE, POSITION}  --> {STUDENT}
    

    Use this as a quick check for NF:

    ---------------------------------------
    For each nontrivial |
        FD X --> Y      |
    at least one holds  | 2NF   3NF   BCNF
    ---------------------------------------
    X is a superkey        ✔     ✔     ✔
    
    Y is a subkey          ✔     ✔
    
    X is not a subkey      ✔
    ---------------------------------------
    
    FD X --> Y is trivial iff Y ⊆ X
    

    For both nontrivial FDs X is a superkey; BCNF.