Help with 3NF

[weasel7711]

I run a small database for a very small business. I am normalizing our database which keeps track of our orders for repairs.

I have one of the tables in 2NF but I am not sure how to normalize it further.
Here is an example of how it looks:

RMA_NUM | TYPE  | MODEL_NUM | SERIAL_NUM
0000001 | ALPHA | ALPH-1000 | ALP-012345
0000001 | BETA  | BETA-2000 | BET-000012
0000001 | DELTA | DELT-1400 | DEL-766652
0000234 | ALPHA | ALPH-1000 | ALP-134529
0000234 | DELTA | DELT-1400 | DEL-881625
0000255 | DELTA | DELT-3000 | DEL-182761
0000732 | ALPHA | ALPH-2000 | ALP-912761
0000732 | BETA  | BETA-2000 | BET-238237

We have RMA numbers for each order. Another table contains RMA number indicating customer, PO number, date shipped, repair needed, etc. RMA_NUM is the primary key in both tables. This table represents multiple types/models for repair per RMA. Thus it was moved to its own table when I normalized the original table. How would I convert this into 3NF?

Thanks for your help.

[tesuji]

Hi,

>>> RMA_NUM is the primary key in both tables.

This is not true because there are duplicates in RMA_NUM (1,1,1, etc).

So what is your primary key ?

(As for 3NF you need to check whether transitive dependencies exist. However, pk must be given priorly.)

-- tesu

[weasel7711]

Tesu,
You are correct that rma_num is not the primary key in the table I was showing above. It swas a mistake on my park. It is the primary key in the other table as there is only one instancde of any specific RMA number in that tasble. Originally there were more as there was one instance per product type. So if an rma contained 3 products there would be 3 entries in the db with multiple repeated columns. I moved them out into a second table, the one you see above.

My question is the same as the one you posed to me. What would my primary key be with the above table? A combination of the rma and the serial number?

Thanks,
Weasel

[tesuji]

Please state all your tables and give them clear names. Is there a one-to-many relationship between the order table and the above table (what name?) ? On the other side there seems to be also a product table. If so, there could be a many-to-one exists between product table and the above table (meaninful name for that supposed linking table then could be orderproduct).

Then you would have: ORDER ---< orderproduct >--- PRODUCT, where the primary key of linking table orderproduct could be pk(rma_num, product_num). (---< stands for one-to-many)

-- tesu

[weasel7711]

ORDER_Table

RMA_NUM | CUSTOMER  | DATE_IN | DATE_OUT | STATUS  | PO_NUM | ...
0106701 | Company A | 7/1/10  | 7/6/10   | Shipped | 1256   | ...
0106702 | Company C | ...     | ...      | ...     | ...    | ...
0106801 | Company B | ...     | ...      | ...     | ...    | ...
0106802 | Company A | ...     | ...      | ...     | ...    | ...
0106803 | Company D | ...     | ...      | ...     | ...    | ...

PRODUCT_Table

RMA_NUM | TYPE  | MODEL_NUM | SERIAL_NUM
0000001 | ALPHA | ALPH-1000 | ALP-012345
0000001 | BETA  | BETA-2000 | BET-000012
0000001 | DELTA | DELT-1400 | DEL-766652
0106802 | ALPHA | ALPH-1000 | ALP-134529
0106802 | DELTA | DELT-1400 | DEL-881625
0000255 | DELTA | DELT-3000 | DEL-182761
0000732 | ALPHA | ALPH-2000 | ALP-912761
0000732 | BETA  | BETA-2000 | BET-238237

There is a one to many relationship ( ORDER -|---<- PRODUCT ) from ORDER to PRODUCT. As in, an one ORDER can contain one or many PRODUCTS. A PRODUCT can return under warranty (or not) and appear later in the PRODUCT table under a different RMA_NUM. So multiple PRODUCTs can come in under one ORDER.

The creator of the DB had them as one table, so MODEL and SERIAL were originally part of the ORDER table. I ported them out when normalizing the table.
I'm new to DB management so this is a learning experience for me. Fortunately as of now the database is fairly small.

[tesuji]

Can SERIAL_NUM be assigend to different RMA_NUM in PRODUCT_Table ?

If answer is yes, you should decompose your existing tables into:

ORDER_Table(RMA_NUM, CUSTOMER, ...)

PRODUCT_Table_unique (SERIAL_NUM, TYPE, MODEL_NUM, ..)

PRODUCT_Table (RMA_NUM, SERIAL_NUM, Quantity?, ...)

Where underlined columns denote primary keys. Now 2NF and 3NF seems to be fulfilled.

If the answer is no, there is nothing to do for PRODUCT_Table is already in 3NF
reason: there is no transitive dependency. A transitive dependency would exist for example if for each ALPHA in non-key column TYPE the values in associate non-key column MODEL_NUM would be the same. This is not true, e.g. ALPHA --> ALPH-1000 and ALPHA --> ALPH-2000, therefore TYPE -|-> MODEL_NUM, thus no transitive dependency. Therefore 3NF complies.

-- tesu