OZCST ITC556 Database System Paper Editing Service

OZCST ITC556 Database System Paper Editing Service

Question 1: Functional Dependency diagram

Functional Dependency diagram: The functional dependency is shown the relationship between the attributes of the same database table. One attribute of the table is known as a determinant and another attribute of the table is known as determined. If attribute A is determinant and another attribute B is determined then this is called functional dependency. This functional dependency graphically represents as A ® B and we can also say as B is functionally depended by A. In functional dependency diagram each attribute of the table is represented by the rectangle with an arrow to indicate the other attribute dependency. Rules of functional dependency diagram are given as:

1.Each attribute can be represented only once.

2.All the database table attributes of interest can perform.

3.Partial key dependencies do not appear.

4.Transitive dependencies also not appear.

Here, I have designed the functional dependency of given table. This functional dependency of the shows that all the attribute of a database table are depended on the purchase code and the inventory table attributes also depended on each other. The attribute of the vendor table also depended on each other, the dependencies of these attributes are shown by the green line in this given diagram.  

The graphical representation of functional dependencies:

1.Product code ® product name, purchase date, product price, Qty_sold, Inv_No, Inv_date, vendor_ID, and Vendor_Name.

2.The inventory table is functionally dependent as Inv_No ® Inv_Date

3.The functional dependency of vendor table: Vendor_ID ® Vendor_Name

Question 2: 3NF Relations

3 Normalization Form:

Product table: In product table the prod_code is the primary key and Vendor_ID and Inv_No are foreign keys. All the attribute (Prod_Name, Qty_sold, Prod_Price, Vendor_ID and Inv_No) are dependent on the prod_code attribute.

Inventory table: The Inv_no is the primary key in Inventory database table. Inv_date and Purchase_date are dependent on this primary key.

Vendor table: Vendor_Name attribute of vendor table is dependent on vendor_ID that is a primary key.

Question 3: Relational Schema for 3NF relation and referential integrity constraints

Referential integrity constraints: In first product table the prod_code is the primary key where all the data is saved according to prod_code value in the database. The inv_No and Vendor_ID are shows the integrity in the table. The value of vendor_ID and Inv_No cannot be a change in the product table because the vendor_Id connected with vendor table and Inv_No is connected with the inventory table. If the user wants to change the data then he should remove these attribute (Foreign key) from the table.

Question 4: ERD diagram

This ERD diagram represents three database table with their attributes. The database table is product table, Inventory table and Vendor table. The attributes of product table are prod_code, prod_name, Qty_sold, Prod_price, Inv_No and Vendor name where the prod_code is primary key and vendor_ID and Inv_No is the foreign key. The Inventory table attributes are Inv_no (primary key), Purchase_date and Inv_date. The attributes of Vendor table are vendor_ID (primary key) and Vendoe_Name.