1

Have a bit of confusion regarding a normalization from 1NF to BCNF. The teacher gave us this excercise to illustrate a normalisation all the way up to BCNF. I think I have found a problem with the given solution and would like to clear this up.

The way I have solved it up to 3NF is as follows: 1NF

My understanding is that there are TWO partial dependencies here

The Primary key is made up of (SID, InstructorID)

  1. The Subject is dependent only on the the InstructorID and not on the entire primary key
  2. The Name is dependent only on the SID only and not on the entire primary key

2NF

Finally, 3NF where the transitive dependency has now been removed and refactored into it's own table. No BCNF violations here!!! 3NF

QUESTION:

Is my solution correct?

have I correctly performed the 2NF step?

Improve this question
10
  • You say "The teachers solution differed in that he left the Grade in the table at the 2NF step and therefore had to remove it at the BCNF normalization level." Did you mean "The teachers solution differed in that he left the Grade in the table at the 3NF step and therefore had to remove it at the BCNF normalization level."
    – ypercubeᵀᴹ
    Mar 4, 2020 at 13:26
  • Thanks for the comment! that is quite confusing isn't it! I have changed it to better reflect my meaning (I hope) - That he did not identify the Subject -> Grade dependency as a partial dependency
    – AFM-Horizon
    Mar 4, 2020 at 13:33
  • Yes, but you say that he left it at the 2NF step. I guess you mean the 2NF->3NF step.
    – ypercubeᵀᴹ
    Mar 4, 2020 at 13:38
  • But Subject->Grade dependency? I don't see that either in yours. Do you mean Gradepoint->Grade dependency?
    – ypercubeᵀᴹ
    Mar 4, 2020 at 13:39
  • I might remove the part about the teachers answer because it is very confusing looking back at it. I mean at the 1NF -> 2NF step but I see now that its pretty hard to understand!! Thanks!
    – AFM-Horizon
    Mar 4, 2020 at 13:43

2 Answers 2

Reset to default
1
  • Subject exists only as an attribute of instructor.
  • A grade is associated with an instructor-student pair, rather than a 'class'.

This means that:

  • A student would not be able to take more than one class with an instructor.
  • Information regarding the subject itself (for example its full name) would not be storable, which seems like a likely request. I'm suggesting to preemptively create a 'Subject' table even though it currently only has one column.
  • If an instructor changed their subject, that would implicitly change the subject for all past grades given by that instructor.
Improve this answer
1

So The question has been answered in great detail in chat by ypercubetm https://dba.stackexchange.com/users/993/ypercube%e1%b5%80%e1%b4%b9

The short anwer is yes the solution above is one possible correct solution

Many thanks to ypercubetm for taking the time breaking it down for me!

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.