relation

[Borkoff]

Hi! I need some help please!

I have to create a DB that has to be normalized to 3NF. I have a table 'Products' that contains info about all the products -
product_ID,
product_name,
quanitity (left in warehouse),
procurement_price,
sales_price.
And I have TWO more tables that contain info about transactions - 'Procured products' and 'Sold_products' tables.
In both these tables I have a field with number of procured and sold products.
My question is - is it possible or do i have to link the product_quantity in warehouse with the quantity of procured_products and the sold_products so my DB would meet 3NF?

[tesuji]

Hi Borkoff

In order to prove 3NF these pieces of Information are necessary and sufficient:

1. All involved tables (relations)

2. All columns (attributes) of those tables

3. Primary keys of all involved tables (necessary to prove 2NF and 3NF)

4. Semantics of the datasets (instances of relations) to determine functional dependencies

Usually 4th item is dropped tacitly for one often hasn't got enough instances when
starting design. Also this point isn't that important for homework, mostly.

I suggest that you repost enough data to fulfill first three items completely. That's an
absolute necessary condition for receiving any serious help. (Whether it is also
a sufficient condition depends on human beings goodness;))

-- tesu

[Borkoff]

My DB structure.
Table Products:
Product_ID (PK),
Product_Name,
Quantity.

Table Suppliers:
Supplier_ID (PK),
Supplier_name,
Phone,
Address.

Table Employees
Employee_ID (PK),
Emp_Name,
Emp_Surname,
Phone,
Address.

Table Procured_Products (I do not know how is this in English):
Transaction_ID (PK),
Product_ID (references Products(Product_ID),
Quantity,
Procurement_price,
Date,
Supplier_ID (references Suppliers(Supplier_ID).

Table Sold_products:
Transation_ID (PK)
Product_ID (references Products(Product_ID),
Quantity,
Date,
Sale_price,
Responsible_employee_ID (references Employees(Employee_ID)

This is as far I have got. I would really appreaciate any help because I have to make this DB for my university. Is this DB ok and in 3NF? I have read many articles on normalization but all was too academic and not with practical examples so I hardly understand normalization.

[tesuji]

Hi Borkoff

At first glance, your tables fulfill 3NF because
1) there aren't composed attributes (columns) nor repeating groups --> 1NF ok.
2) Also 2NF is fulfilled for 1NF is fulfilled and no column is functionally dependent on a subset of primary key columns. This is always true if the primary key only consists of one column.
3) And as far as I see there aren't any columns which transitively dependent on the primary key, obviously true for each of your table. So 3NF is also fulfilled, 2NF comprised.

As for the primary key of Procured_Products I think there exist a true natural key for this table is the result of the many-to-many relationship between products and suppliers. This primary key (Product_ID, Supplier_ID) is always necessary to define the many-to-many relationship (if it were omitted there would be some violation of normalization theory, I think so). Unfortunately, (Product_ID, Supplier_ID) isn't always unique, for example a product is to suppy repeatedly by same supplier. To solve this problem date or better timestamp should be included in PK.

-- tesu