Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Which normal form is this violating?
--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
> 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