Re: Need help with 3NF tables (hoepfully)

From: Stephan Bohren <stephan.bohren_at_systor.com>
Date: Fri, 22 Jan 1999 14:46:09 +0100
Message-ID: <36A88121.2D66E1A2_at_systor.com>


Ryan
You do the normalizing correctly. In a table in 3. NF each attribute each non-key attribute depends directly on the primary key. In your example this would only be the case, when your YEAR and MAKE-information has more than only one attribute:
In the real world you wouldn't create a table of its own just with filled with sequential numbers each representing a year. It makes no sense to define an artificial identifier (another sequential number) for these year numbers. If there are some restrictions in the range of years than may be used, you should put these rules rather in a column check constraint than implementing a table with the valid values. But theoretically it's a correct solution.
Also the CARMAKE-information should only be put into a separate table when it has more than only the key attribute. The value domain (the permitted values for the MAKE-attribute) can be defined in a column check constraint.

The error message shows that you didn't implement not only the tables but also the referential integrity (RI) constraints which describe the relationship between the tables and the rules that have to be applied when data is deleted (or, depending on the DBMS you use, also when updated). The error message makes me assume that you defined these constraint the wrong way round.

The table cars has two foreign keys pointing to the primary key of each respective parent table.The RI-constraints defined this way prevent you from inserting a record in a dependent table with a value in the foreign key which can not be found as a value in the primary key column of any record in the parent table. A foreign key value MUST always point to a record in the parent tables primary key column. Unless the RI constraint allows a conditional relationship. Then a NULL-value is allowed in a foreign key. When you start to insert values in such a child/parent schema you must begin loading the schema with the parent table. Because the car_year table in your example is a parent I assume that you have defined the RI constraint in the wrong direction.
Hope this helps.
Stephan

Ryan wrote:

> I want to make sure I understand something. Lets say I have a table
> called car parts:
>
> cars (CARID primary key, CARYEAR, CARMAKE,CARPRICE)
>
> if I want to make this a 3NF form I assume I would do something like:
>
> car_year(YEARID primary key, YEAR)
> car_make(MAKEID primary key, MAKE)
> cars(CARID primary key, YEARID foreign key, MAKEID foreign key,price)
>
> I am even close to hitting 3NF? I am trying to understand the seperation
> of data.
>
> NOW, how would I load data into multiple tables like this? for example,
> I created a schema similar to this and tried to insert values into
> car_year for example and kept getting the 'violation - no parent key
> found;.
>
> Any help would be greatly appreciated.
> Ryan
>
> - Remove [NO SPAM] from email address to reply -
Received on Fri Jan 22 1999 - 14:46:09 CET

Original text of this message