Scheme based on functional dependencies

Question

I've been trying to figure this out in my head but i'm really stuck and would appreciate some help. Given the scheme

R(a,b,c,d)
fd1: a->c
fd2: b->d

Would it be accurate to say that the key here would be {a,b} ? If that's true, then would it also be accurate to say that attributes c,d due to their partial dependence on either a or b need to be in their own tables? Resulting in a scheme of the following manner?

R(a,b)
r1(a,c)
r2(b,d)

Or would it be right simply to have

R(a,c)
r1(b,d)

Surely a,b have to be present in some sort of form, as they're the key right? I'm not 100% sure. Help understanding this would be great

score 2 · Accepted Answer · answered Dec 07 '12 at 02:56

{a,b} is the only key for R. R is in 1NF.

In order to reach at least 2NF, remove partial key dependencies by projection.

r₁{ a c}
r₂{ b d}

This is the minimum cover. Both r₁ and r₂ are in 6NF. The key to R doesn't need to exist, because R doesn't exist. But R can be recovered from the Cartesian product of r₁ and r₂.

Using SQL . . .

create table t1 (
  a integer not null,
  b integer not null,
  c integer not null,
  d integer not null,
  primary key (a, b)
);

insert into t1 values
(1, 1, 10, 100),
(1, 2, 10, 200),
(2, 1, 20, 100),
(2, 2, 20, 200);

Note that this preserves the FDs a->c and b->d.

create table t2 (
  a integer primary key,
  c integer not null
);

insert into t2
select distinct a, c from t1;

create table t3 (
  b integer primary key,
  d integer not null
);

insert into t3 
select distinct b, d from t1;

drop table t1;

Now we can look at the data in tables t2 and t3.

select * from t2;
a   c
--
1   10
2   20

select * from t3;
b   d
--
1   100
2   200

And we can recover t1 through the Cartesian product of t2 and t3.

select t2.a, t3.b, t2.c, t3.d
from t2, t3;
a  b   c  d
--
1  1  10  100
1  2  10  200
2  1  20  100
2  2  20  200

Scheme based on functional dependencies

1 Answers1