How to get rid of a into the key dependency?

Question

Suppose we have a functional dependency from an attribute outside the primary key to an attribute inside the primary key. How can we get rid of this dependency (which I intuitively think it's bad)?

Particularly, suppose we have the following functional dependencies:

CS -> T
T  -> C

where CS is the primary key. In my example, it happens luckily that TS could also initially be the primary key, so the situation is converted to:

TS -> C
T  -> C

which is actually a situation where we don't have into the key dependencies anymore but we have a partial dependency, which can be solved easily by splitting our table into two tables as follows

| T | C |

and

| T | S |

but what if TS wasn't also a primary key? How could we get rid of the initial into the key dependency/anomaly?

Answer 1

First, a note about the terminology: “primary key” is a term used for a relation managed by a Relational Database Management System, while in the normalization theory the terms normally used are “superkeys” and “candidate key”, or simply “key”.

Second, in your example you are asking:

How can we get rid of this dependency (which I intuitively think it's bad)?

The point is that the dependency is actually bad, in the sense that you have a relation with anomalies (in this case redundancy), but you cannot get rid of this anomaly without having another anomaly, that is losing a functional dependency.

In fact you could transform the schema in BCNF, with the following decomposed schemas:

R1 < (C T) , { T → C } >

R2 < (S T) , { } >

but, as you can see, the dependency CS → T is not preserved anymore, since no subschema contains all the three attributes. And this is worse than having a redundancy, because you could introduces inconsistencies in your database, that is cases in which that dependency is violated.

In fact, this is a classical example in which the schema is already in Third Normal Form (3NF), that, by definition, allow dependencies from a set of attributes that are not a key, to an attribute which is part of a key (called “prime” attribute).

So, this kind of anomaly is commonly accepted, and the relation is not decomposed.

Answer 2

Any "into the key" dependency in a given relation R will be removed when decomposing the relation into Boyce-Codd Normal Form (BCNF).

BCNF ensures that all dependencies are "from the full key".

Look here for how to decompose into BCNF form.

Edit

• From the full key : From the full primary key to outside the key.
• Into key dependency : From outside the key into the key.

And for completeness' sake, the other 2 type of dependencies - Partial dependencies are removed by decomposing to 2NF and transitive dependencies are removed by further decomposing it into 3NF. Hence by further decomposing into BCNF you essentially remove all the three types of dependencies ( partial,transitive,into the key )