-1

I am doing a database theory course as part of my CS degree, and we have to confirm a database we designed is in BCNF. I am trying to create the functional dependencies, but I don't know what to do when a relation is all prime. (All attributes are primary keys, like when you have a M:N relationship.)

For example, consider this relation:

Prod_Cat[name, category_ID, EPC]

Where name, category_id, and EPC are all primary keys.

How would I define the functional dependencies for this?

philipxy
  • 14,867
  • 6
  • 39
  • 83
Fishingfon
  • 1,034
  • 3
  • 19
  • 33

2 Answers2

1

I'm not sure about the meaning of the columns in your table, but if your primary key consists of all the columns, you'll only have trivial functional dependencies of the form A->B with B being a subset of A. (If you had any other functional dependencies of the form A->B where B is not a subset of A, then B wouldn't be in the key.) Also, the table must be in BCNF (but may not be in 4NF).

Ronald
  • 26
  • 2
0

Normally we think of normalization applying to entity tables, not intersection tables. That is because a bare intersection table contains only independent keys, not attributes. In general, keys are created during the normalization process, so all dependencies are already resolved (assuming proper design).

However, an intersection table defines a relationship and relationships can have additional attributes.

create table DeptMgrs(
    DeptID  int not null references Departments( ID ),
    MgrID   int not null references Employees( ID ),
    Assigned date not null,
    constraint PK_DeptMgrs primary key( DeptID, MgrID )
);

Normally, there are no functional dependencies between the FK fields of an intersection table, thus none between DeptID and MgrID -- they are imported key fields and together they form the natural key of the relationship. However, the Assigned field tells the date this employee became the manager of that department. It is an attribute not of a department or an employee but of the relationship between them. Of course, this example is trivially in 3nf and even BCNF, but with additional attributes, normalization of the intersection table just may be required.

And shouldn't that be loads of fun?

Since you appear to have only FK fields in your intersection table, there should be no dependencies between them.

TommCatt
  • 5,498
  • 1
  • 13
  • 20