6

I am trying to understand what BCNF is and I have a relation like this:

Student(id, ssn, email, name, surname)

where

  • id is a primary surrogate key with not null and auto increment properties,
  • ssn is a unique key with not null property, and
  • email is also a unique key with not null propery.

Are there anything that violates BCNF and if so, how can I overcome this situation with a better design?

EDIT

I am trying to write my functional dependencies but please correct me if I'm wrong.

There are three attributes which determine the others, so it's confusing that ssn and email both exist at left hand side and right hand side of the equations. It seems that this relation is not in bcnf but there must be something wrong :)

id -> (ssn, email, name, surname)
ssn -> (id, email, name, surname)
email -> (id, ssn, name, surname)
sedran
  • 3,498
  • 3
  • 23
  • 39
  • When some FDs hold, the FDs implied by them via Armstrong's axioms also hold. Including, when X->Y, every superset of X determines every subset of Y. Including the trivial FDs, which are easy to enumerate from a heading but you don't give them either. [Re FDs & BCNF](https://stackoverflow.com/a/53386492/3404097) PS Finding FDs, deriving FDs, definitions of BCNF, etc etc are faqs. – philipxy May 03 '23 at 21:46

3 Answers3

3

The way to answer your question properly is to identify what functional dependencies are supposed to apply. Write down what you think the dependencies are. If the left-hand side of every non-trivial dependency is a superkey then the relation satisfies BCNF.

nvogel
  • 24,981
  • 1
  • 44
  • 82
2

Yes, your table is in BCNF. This is because you don't have any overlapping candidate keys - i.e. there's no attributes which appear in two different keys.

If you had some other attribute, X, which formed part of the keys - e.g. (SSN, X) and (email, X) - then your table would fail BCNF because X should be the same for a given SSN and email pair. These key definitions would allow different values of X for given SSN and emails.

For a good explanation of BCNF, read this answer.

Community
  • 1
  • 1
Chris Saxon
  • 9,105
  • 1
  • 26
  • 42
  • 1
    If sedran's set of dependencies is complete then yes. But as a general rule the absence of overlapping keys doesn't automatically mean a relation is in BCNF. If there happened to be non-key dependencies or partial key dependencies then it would not satisfy BCNF. – nvogel May 12 '13 at 09:01
  • @sqlvogel - I can't think how you can fail to be in BCNF if there's no overlapping keys. Could you show me an example please? – Chris Saxon May 12 '13 at 14:13
  • E.g. the dependency A->B where A and B are nonprime would be a violation of BCNF (and 3NF) whatever the candidate keys are. – nvogel May 15 '13 at 19:57
  • 1
    @sqlvogel - I see what you mean now. I was thinking in terms of having already met 3NF. Is it correct to say that if a design meets 3NF, there must be overlapping keys to prevent it satisfying BCNF? – Chris Saxon May 16 '13 at 12:17
-4

The constraints of emailaddress or SSN being unique or nor NULLable are too strong. You can enforce them, but you should not impose them. (think: foreign students.) Generally it is a bad idea to impose any constraints on key-domains that are not under your jurisdiction, even if they are close to unique (such as in the SSN-case).

As an example: typo's. Suppose some person wants to enlist as a student, but it appears that her SSN is already in use by another person, probably caused by a typo. Should you refuse the new student, or delete the old one? (or should you allow the SSN field to be NON-unique or NULLable ?)

update/Final note: This topic is tagged "datatbase-design" and "data-normalisation". I reacted on the design choices (which are debatable). Others have attempted to react on the BCNF aspects (which are trivial, given the the UNIQUE candidate keys and the NOT NULL assumption)

Normalising a bad data model will give you a normalised but still wrong model.

wildplasser
  • 43,142
  • 8
  • 66
  • 109
  • 2
    The wisdom or otherwise of choosing some particular keys has nothing much to do with the normalization question though because the keys are already given. It's surely *not* a bad idea to require that students have a unique email address and unique Tax Id of some kind. Plenty of real world systems *do* require unique email addresses as identifiers because they are easily remembered, verified and relatively stable. And if a duplicate SSN does come along then it's perfectly sensible not to allow it and to require someone to check which of those duplicates might be mis-keyed or invalid. – nvogel May 12 '13 at 09:23
  • The OP is a toy-example, and it is IMHO a *bad* toy example, because it makes implicit assumptions about "foreign" domains. For real toy-examples, the teacher should use real toy problems (such as sudoku, scrabble, or crossword puzzles) – wildplasser May 12 '13 at 09:38
  • 3
    Hmmm, go tell Amazon or Microsoft that the fact that they require unique email addresses for their systems makes them a "toy"! I think you are the one making the assumptions about a problem that is setting a perfectly realistic example in normalization theory. – nvogel May 12 '13 at 09:48
  • Amazon and Microsoft are commercial businesses, which can impose restrictions on their customers (or refuse them). A government, school or university cannot impose anything, they should model and describe reality. As a bizarre example: polygamy; if a polygamist is naturalised to some western nationality, how should his marriages be registered? Or should he be devoced N-1 times in order to be entered into *the system* ? – wildplasser May 12 '13 at 10:35
  • Certainly they should model and describe reality. And if the reality is that every student is either allocated a unique email address or may use one of their own then that is the reality the database should model. It is very, very common for all sorts of organisations to require users to have unique email addresses for access to internal systems and services. Several government and public institutions I have relationships with require it. I'm very surprised if you aren't aware of that. – nvogel May 12 '13 at 14:40
  • I am very well aware of it. But the fact that people often make this (IMHO wrong, obviously) design decision does not imply that the chosen model is correct. (as an example: a lot of Dutch administration falsely assumed that every *person* has an `a-nummer`, or at least a BSN (effectively a SSN), causing people without these numbers (foreigners plus people from the Dutch Antilles) not being entered into *the system*, with as a result several shadow-administrations to co-exist. All caused by a fundamental design flaw ...) – wildplasser May 12 '13 at 14:59
  • 2
    Design flaw or not, it has nothing to do with normalization, which was the question asked. Had the question been about normalization of a database about grapes and wine regions, would you say something about French wine either or not being better than Italian ? Of course it is a "design flaw" if a system enforces uniqueness of something that isn't, or enforces presence of something that isn't. Not the question asked. – Erwin Smout May 12 '13 at 15:35