Re: Which normal form is this violating?

From: Larry Coon <larry_at_assist.org>
Date: Fri, 26 Apr 2002 14:23:42 -0700
Message-ID: <3CC9C55E.1969@assist.org>

--CELKO-- wrote:  

> >> I'm having the usual disputes about database design issues.
>
> The information that my coworkers have is say, x and y. It has a one
> to one relationship. Therefore, it goes into one and the same table.
>
> CREATE TABLE Table_A
> (fieldx INTEGER NOT NULL PRIMARY KEY,
> fieldy INTEGER NOT NULL);
>
> However, they are arguing that it goes into another table.
>
> CREATE TABLE Table_A
> (fieldx INTEGER NOT NULL PRIMARY KEY);
>
> CREATE TABLE Table_B
> (fieldx INTEGER NOT NULL PRIMARY KEY,
> fieldy INTEGER NOT NULL); <<
>
> Did they put a FOREIGN KEY constraint anywhere?
>
> I would call it a Domain-Key violation because Table_A is redundant.
> SQL prevents you from having two tables with the same names, but
> relational theory says that since a table is a set, it can appear only
> once in a schema, no matter what the name. Table_B represents a set
> of facts about the relationship between (fieldx, fieldy); Table_A
> represents a redundant projection of Table_B.
>
> The redundancy leads to errors:
>
> INSERT INTO Table_A VALUES (42);
> INSERT INTO Table_B VALUES (42, 13);
> INSERT INTO Table_B VALUES (99, 23);

Here's where I lost you, unless you're assuming that Table_B.fieldx is NOT a foreign key. If it IS a foreign key (and I think it's reasonable to assume that's how they're thinking about it), then the above insert fails.

It seems to me like Table_A defines the domain of fieldx values, and Table_B actually uses those values. To give an example that's less abstract...

  CREATE TABLE colors
  (color VARCHAR(5) NOT NULL PRIMARY KEY);  

  CREATE TABLE cars
  (license_no VARCHAR(7) NOT NULL PRIMARY KEY,    color VARCHAR(5) NOT NULL,
   FOREIGN KEY color REFERENCES colors(color));

   INSERT INTO colors VALUES ('red');
   INSERT INTO colors VALUES ('white');
   INSERT INTO colors VALUES ('black');

   INSERT INTO cars VALUES ('3ABC123', 'red');

   INSERT INTO cars VALUES ('4DEF234', 'red');    INSERT INTO cars VALUES ('5GHI345', 'white');

So it's a case of using a second table and a foreign key to implement a domain constraint.

> Is 42 an element of the domain of fieldx? Yes and yes, he said
> redundantly.

Is "red" in the domain of car colors?

   select color from colors where color = 'red'

   color

---

   red

> Is 99 an element of the domain of fieldx? Yes and no, he
> said contradictingly.

Is "blue" in the domain of car colors?

   select color from colors where color = 'blue'

   color

---

No redundancy, and no contradiction.

Larry Coon
University of California
larry_at_assist.org
and lmcoon_at_home.com Received on Fri Apr 26 2002 - 16:23:42 CDT