Homework Assignment 2 Due On 11/16/2019 Using The Invoice Ta
Homework Assignment 2 Due On 11162019using The Invoice Table Struc
Using the invoice table structure provided, do the following: Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you create. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you create. Draw the Crow’s Foot ERD.
Paper For Above instruction
The task begins with analyzing the provided invoice table structure, which contains attributes such as invoice number, product number, sale date, product label, vendor code, vendor name, quantity sold, and product price. This table is designed to record transactions involving multiple products per invoice, implying it uses a composite primary key (INV_NUM, PROD_NUM). The first step is to establish the relational schema that accurately represents these attributes and their relationships.
Constructing the initial relational schema involves creating a table, potentially named "InvoiceDetails," with attributes including INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, and PROD_PRICE, with INV_NUM and PROD_NUM serving as the composite primary key. Creating the dependency diagram requires identifying all functional dependencies in this initial schema. For example, VEND_CODE and VEND_NAME depend on PROD_NUM, and perhaps SALE_DATE and PROD_LABEL depend on INV_NUM and PROD_NUM; VEND_CODE might also determine VEND_NAME if unique, and PROD_PRICE could depend on PROD_NUM.
Identifying all dependencies involves determining which attributes depend on others entirely or partially. Partial dependencies occur if a non-key attribute depends only on part of the composite key, such as product details depending solely on PROD_NUM. Transitive dependencies exist if a non-key attribute depends on another non-key attribute; for instance, VEND_NAME depending on VEND_CODE, which itself depends on PROD_NUM. Recognizing and removing these dependencies require decomposing the schema into higher normal forms, such as 2NF and 3NF.
To remove partial dependencies, we decompose the original schema into separate tables—one for invoice details, one for products, and one for vendors—ensuring that non-key attributes are dependent solely on whole keys. For example, create a "Product" table with PROD_NUM, PROD_LABEL, and PROD_PRICE, and a "Vendor" table with VEND_CODE and VEND_NAME. The invoice details table then references these through foreign keys, respecting normalization principles. The modified dependency diagrams reflect these decompositions, with dependencies aligned to the new schemas.
Next, removing transitive dependencies involves further normalization. If VEND_NAME depends on VEND_CODE, which in turn depends on PROD_NUM, we eliminate this transitivity by creating a separate "Vendor" table. Similarly, if product price depends solely on PROD_NUM, we maintain this dependency within the "Product" table. The schema now adheres to 3NF, with all non-key attributes fully dependent on the primary key, and no transitive dependencies remaining.
Finally, to visualize the database schema, a Crow’s Foot Entity-Relationship Diagram (ERD) is constructed. It displays entities such as Invoice, Product, Vendor, and InvoiceDetails, along with relationships indicating how they connect. For example, the Invoice entity links to InvoiceDetails with a one-to-many relationship, and InvoiceDetails links to both Product and Vendor through foreign keys. This ERD provides a clear conceptual framework for the database structure, illustrating how data about invoices, products, and vendors interrelate within normalized tables, facilitating data integrity and efficient querying.
References
- Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.
- Database Systems: A Practical Approach to Design, Implementation, and Management (6th ed.). Pearson.