Homework Assignment 2 Due On 11/16/2019 Using The Inv 412181
Homework Assignment 2 Due On 11162019using The Invoice Table Struc
Homework Assignment 2 - Due on 11/16/2019 Using the INVOICE table structure shown below, do the following: Attribute Name Sample Value Sample Value Sample Value Sample Value Sample Value INV_NUM PROD_NUM AA-E3422QW QD-300932X RU-995748G AA-E3422QW GH-778345P SALE_DATE 15-Jan--Jan--Jan--Jan--Jan-2016 PROD_LABEL Rotary sander 0.25-in. drill bit Band saw Rotary sander Power drill VEND_CODE VEND_NAME NeverFail, Inc. NeverFail, Inc. BeGood, Inc. NeverFail, Inc. ToughGo, Inc. QUANT_SOLD PROD_PRICE $49.95 $3.45 $39.99 $49.95 $87
Write the relational schema, draw its dependency diagram and identify all dependencies, including all partial and transitive dependencies. You can assume that the table does not contain repeating groups and that any invoice number may reference more than one product. (Hint: This table uses a composite primary key.) 2. Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created.
3. Remove all transitive dependencies, write the relational schema, and draw the new dependency diagrams. Also identify the normal forms for each table structure you created.
4. Draw the Crow’s Foot ERD.
Paper For Above instruction
Introduction
The given assignment centers on the normalization process of a database table, specifically the Invoice table structure, to understand and eliminate data redundancy and ensure data integrity. This exercise involves creating relational schemas, dependency diagrams, identifying dependencies, and progressively normalizing the table to achieve higher normal forms. The process culminates in drawing an Entity-Relationship Diagram (ERD) utilizing Crow’s Foot notation for comprehensive visual representation.
Part 1: Initial Relational Schema and Dependency Analysis
The Invoice table, as described, contains attributes such as invoice number (INV_NUM), product number (PROD_NUM), sale date, product label, vendor code, vendor name, quantity sold, and product price. The sample data suggests that the primary key is a composite of INV_NUM and PROD_NUM because an invoice can contain multiple products, and products can appear on multiple invoices.
The initial relational schema, therefore, can be expressed as:
```sql
Invoice (INV_NUM, PROD_NUM, SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, QUANT_SOLD, PROD_PRICE)
```
The dependencies include:
- The primary key is (INV_NUM, PROD_NUM).
- Attributes like SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, and PROD_PRICE depend on PROD_NUM.
- VEND_CODE and VEND_NAME are functionally dependent on VEND_CODE.
- The invoice number (INV_NUM) may be related to multiple product entries, indicating possible multi-valued dependencies.
A dependency diagram would illustrate that attributes such as SALE_DATE, PROD_LABEL, VEND_CODE, VEND_NAME, and PROD_PRICE depend on key components, potentially showing partial dependencies since attributes like SALE_DATE depend solely on INV_NUM (if sale date applies to whole invoice), while others depend on PROD_NUM.
Part 2: Removing Partial Dependencies
Partial dependency occurs when non-key attributes are functionally dependent on part of a composite key. To resolve this:
- Separate the table into multiple tables.
- Create an Invoice table:
```sql
Invoice (INV_NUM, SALE_DATE)
```
- Product table:
```sql
Product (PROD_NUM, PROD_LABEL, PROD_PRICE, VEND_CODE)
```
- Vendor table:
```sql
Vendor (VEND_CODE, VEND_NAME)
```
- InvoiceItems table, which relates invoices and products:
```sql
InvoiceItems (INV_NUM, PROD_NUM, QUANT_SOLD)
```
This approach removes partial dependencies and moves the schema to 2NF. Diagrams would show dependencies of product attributes on PROD_NUM and vendor attributes on VEND_CODE, with foreign keys linking tables.
Part 3: Removing Transitive Dependencies
Transitive dependency exists when a non-key attribute depends on another non-key attribute. For example, PROD_PRICE depends on PROD_NUM, and VEND_NAME depends on VEND_CODE, which itself depends on VEND_CODE.
Further normalization involves:
- Ensuring that all non-key attributes depend only on key attributes.
- The schema after normalization:
```sql
Product (PROD_NUM, PROD_LABEL, PROD_PRICE, VEND_CODE)
Vendor (VEND_CODE, VEND_NAME)
Invoice (INV_NUM, SALE_DATE)
InvoiceItems (INV_NUM, PROD_NUM, QUANT_SOLD)
```
In this schema, the transitive dependencies are eliminated, resulting in 3NF.
Part 4: Drawing the Crow’s Foot ERD
The final ERD depicts:
- One-to-many relationship from Invoice to InvoiceItems.
- Many-to-one relationship from InvoiceItems to Product.
- Many-to-one relationship from Product to Vendor.
Crow’s Foot notation effectively illustrates these multiplicities facilitating clarity regarding entity relationships.
Conclusion
Through systematic normalization—from identifying dependencies to removing partial and transitive dependencies—the Invoice database schema becomes more efficient, reduces redundancy, and maintains data integrity. The modeling process using dependency diagrams and drawing the Crow’s Foot ERD provides a comprehensive visual understanding of how entities relate within the database schema.