BCNF with one surrogate key and two unique keys

Question

I am trying to understand what BCNF is, and I have this attitude:

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

Where

• id is the surrogate’s primary key with non-zero and automatic growth ,
• ssn is a unique key with a non-zero property and
• Email is also a unique key with a null value .

Is there something that BCNF violates, 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 am wrong.

There are three attributes that determine the rest, so it is confusing that ssn and email exist on both the left and right sides of the equations. It seems that this relationship 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) 

Answer 1

Yes, your table is in BCNF. This is because you do not have overlapping candidate keys, i.e. There are no attributes that appear in two different keys.

If you have another attribute, X, which is part of the keys - for example, (SSN, X) and (email, X) - then your table will not work BCNF, because X must be the same for this pair of SSN and email . These key definitions will allow you to use different X values ​​for given SSNs and emails.

For a good explanation of BCNF, read this answer .

Answer 2

The correct answer to your question is to determine which functional dependencies should be applied. Write down what you think of addictions. If the left side of each nontrivial relationship is a superkey, then the relation satisfies BCNF.

Answer 3

Emailaddress or SSN restrictions that are unique or NULLable are too strong. You can apply them, but you should not impose them . (I think: foreign students.) As a rule, it is a bad idea to impose any restrictions on key domains that do not fall under your jurisdiction, even if they are close to unique ones (for example, in the case of SSN).

As an example: typo. Suppose someone wants to enroll as a student, but it looks like her SSN is already being used by another person, probably caused by a typo. Should you refuse a new student or delete an old one? (or should you allow the SSN field to be unique or NULLable?)

update / Final note: "datatbase-design" and "data normalization" are marked in this tag. I reacted to the choice of design (which is controversial). Others have tried to respond to aspects of BCNF (which are trivial given the keys of the UNIQUE candidate and the NOT NULL assumption)

Normalizing a bad data model will give you a normalized, but still incorrect model.