Final Model

Internal Model

This is the conceptual model for the tables. It is suppose to have not partial or transitive dependencies in the diagram. The internal models shows all of the tables attributes.

3NF

3NF

PRODUCT (PROD_NUM, PROD_TYPE, PROD_NAME)

CUSTOMER (CUST_ID, CUST_NAME, CUST_ADDRESS, CUST_PHONE)

MANAGER (MANG_ID, MANG_NAME)

REQUEST (VEND_NUM, PROD_NUM, MANG_NAME)

REQUEST LINE (VEND_NUM, PROD_NUM, QUANT_ORDER)

VENDOR (VEND_NUM, VEND_CODE, VEND_NAME)

INVOICE (INV_NUM, CUST_NUM, DATE_SOLD, INV_SUB, INV_TAX, INV_TOTAL)

INV_LINE (INV_NUM, PROD_NUM, QUANT_SOLD)

SALES REP (REP_ID, REP_NAME)


This is the 3NF created from 2NF to remove all of the transitive dependencies. I took out the transitive dependencies out of the PRODUCT table. I created a separate table for the VENDOR to display the VEND_CODE and VEND_NAME. Then a primary key was created called VEND_NUM. A INVOICE LINE was created to show the attributes that would be contained within the INVOICE LINE for invoices. The REQUEST LINE is created for the same purpose of the INVOICE LINE to show the attributes that would be contained within the LINE.

2NF

2NF

PRODUCT (PROD_ID, PROD_NAME, PROD_TYPE, VEND_CODE, VEND_NAME
Transitive Dependencies – VEND_CODE, VEND_NAME

CUSTOMER (CUST_ID, CUST_NAME, CUST_ADDRESS, CUST_PHONE)

INVOICE (INV_NUM, INV_SUB, DATE_SOLD)


Here is the 2NF created from the 1NF. Here I took out all of the partial dependencies from 1NF and put them into separate tables. Since it is in 2NF then there should transitive dependencies still. The transitive dependency is in the PRODUCT table; VEND_CODE and VEND_NAME. The transitive dependencies will be taken out in 3NF.

1NF - Dependency Diagram

ADDED:



NOTE: The INV_NUM is a partial dependency to DATE_SOLD, INV_SUB, INV_TAX.


This is the dependency diagram for the 1NF. This is a layout for the attributes used in the table. The partial dependencies are listed above in the diagram. A partial functional dependency occurs when the value in a non-key attribute of a table is dependent on the value of some part of the table's primary key (but not all of it). Also, the transitive dependencies are also labeled in the diagram above. A transitive dependency is basically when a non-key attribute depends on another non-key attribute.

Beginning Conceptual Model

This is the conceptual model for the sales department and purchasing department. The conceptual model is the basic overall view for the business before everything has been normalized. This diagram is to provide a starting point to identify tables and their relationships.

End User (Purchasing Line)

This is the end user diagram for the purchasing department. The manager sends in a Request order to the vendor when a particular product is low or out of stock from the business. The vendor receives the request order and processes it. The products are then fulfilled and sent to the place of business.