sql database: table with 2 column (id name) and 2 primary key Third Normal Form Boyce-Codd Normal Form

Question

Imagine the following table. In my case, I am completely sure that name needs to be unique and not null [unique+not null = primary key]. Therefore name is a primary key. For some reasons (probably by habit), I have naturally created a primary key id column of type int.

Other assumptions: I absolutely need to keep name in my table and I am absolutely sure that name (of type varchar) will never exceed 20 characters.

Now my first question is [probably close question with yes or no expected]: do I respect BCNF Boyce-Codd Normal Form if I create such a table?

Second optional question [probably open question]: is it good practice to create column id in this case?

  CREATE TABLE Y (
    id int,
    name varchar(20),
    PRIMARY KEY(id, name)
    );

Answer 1

If each column is unique, you probably want either

CREATE TABLE Y (
  id int primary key,
  name varchar(20) not null unique,
);

or

CREATE TABLE Y (
  id int not null unique,
  name varchar(20) not null unique
);

The FDs here are id->name and name->id.

Informally, you satisfy BCNF when every arrow in your FDs is an arrow out of a candidate key. So this satisfies BCNF.

It's not a good thing to create a surrogate id column out of habit. Think first, and make yourself aware of the tradeoffs and side effects.

Answer 2

Although relations comprising only two attributes are always in BCNF, your question is a bit difficult to answer as the text does not correspond with the table design.

If I take your table design as base for answering the first question: You create a table Y comprising two attributes id and name, which are the only attributes of Yand which together form the only key, then it is always in BCNF.

When I take your textual description, where you state that name is unique (in terms of the relational model a key), then one has to think of the meaning of the surrogate attribute (or key?) id:

a. If id is a key, too, then FDs are id->name; name->id, where id is a key and name is a key, such that every FD has a key on his left hand side. BCNF is therefore fulfilled.

b. If idis not key on its own, but name->id holds, then it is still in BCNF (because the only FD has a key on his LHS). Though I would then not understand why you have an additional attribute id at all.

The main thing is, however, that your database scheme does simply not adhere to the rules stated in the text: The sole key with (id, name) is simply wrong expressed, because you should have id as primary key and name as unique constraint (i.e. an alternative key) or vice versa. Then it would be clearly in BCNF, and would correspond to option (a) mentioned above.

So it is not a question of BCNF or not, but a question of "scheme correct expressed or not".