1

Given the table:

  • Student_subject(studentid, subjectid, username, grade)

Primary key consisting of a combination of:

  • studentid, subjectid

Candidate key consisting of a combination of:

  • subjectcode, username

Now it can be saide the following functional dependencies exist:

  • studentid, subjectid -> grade (BCNF)
  • username, subjectid -> grade (BCNF)
  • studentid -> username (3NF)
  • username -> studentid (3NF)

BCNF - Boyce–Codd normal form (3.5NF)

So the NF of the table will equal the lowest NF found among all FDs, right? There seems to be some different explanations around for how to find the NF levels.

As I've seen from one explanation on how to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key, but I can't see that is the case (since the primary key is defined as the combination of studentid, subjectid) with

  • studentid -> username (3NF)
  • username -> studentid (3NF)

And if it doesn't fulfill 2NF, it can't satisfy a higher level 3NF. isn't it so that you start to see if 1NF is satisfied, and if it is you check if 2NF is as well and so on...

Also superkeys are anything that identifies a row uniquely, incl. candidate keys, primary key and all possible combinations of these with the rest of the attributes?

  • This question suggests several misunderstandings of normal forms. You start from FDs and derive candidate keys, not the other way around. Also, FDs don't have normal forms, they just affect the normal form of the relation. In any case, you're nowhere near BCNF. You have FDs with antecedents that are subsets of the candidate keys, so you're in 1NF. – beldaz Nov 3 '15 at 22:03
  • I agree with you that it does not even fulfill 2NF, however it is claimed in the solution that 3NF is satisfied, and that is what has gotten me confused. – BoroBorooooooooooooooooooooooo Nov 4 '15 at 9:45
  • The main normal forms are cumulative: you can't have 3NF without 2NF and, in turn, 1NF. It wouldn't be surprising if this was specified incorrectly in a question, as it's confusing even for tutors. – beldaz Nov 4 '15 at 10:11
  • Yes agreed on the cumulative part. However the tutor insists on this beeing 3NF.. For fulfilling 2NF the argumentation is that in order to satisfy it only one of the following conditions need be fulfilled, is that true?: - x is a superkey in the relation - A is a keyattribute - x is NOT a subset of any keys in the relation Where x is studentid and A is username in this example. – BoroBorooooooooooooooooooooooo Nov 4 '15 at 14:14
  • Yes, my mistake, I missed the fact that the relationship between student Id and user name is bidirectional. It is indeed in 3NF, not 1NF. – beldaz Nov 4 '15 at 21:27
1

to satisfy 2NF, it is so that all data/columns need to depend on the whole/entire primary key

That's incorrect. A relation R is in second normal if every non-prime attribute of R is fully dependent on each candidate key of R. In the case of studentid -> username and username -> studentid both username and studentid are prime attributes (attributes that are included in a candidate key). So those dependencies are not a violation of 2NF and Student_subject does satisfy 2NF with respect to those dependencies. As already pointed out, Normal Forms apply to relations, not to FDs.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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