BCNF with One Surrogate Key and Two Unique Keys

Question

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)

Answer 1

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.

Answer 2

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.