Re: Need help with 3NF tables (hoepfully)
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