Re: Which normal form is this violating?

Larry Coon wrote:

>--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.
>

Wouldn't it make more sense to store the values of each color in the Color table along with a unique id, and then store the id in any other table that needs that value? What happens if you need to modify the name of a particular color?

 From that sense, it would appear to be redundant.

>
>
>
>Larry Coon
>University of California
>larry_at_assist.org
>and lmcoon_at_home.com
>
Received on Sat Apr 27 2002 - 17:23:10 CDT