Homework Assignment 2 Due On 11/16/2019 Write The Relational

Homework Assignment 2 Due On 11162019write The Relational Schema

Homework Assignment 2 - Due on 11/16/2019 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.) Remove all partial dependencies, write the relational schema, and draw the new dependency diagrams. Identify the normal forms for each table structure you created. 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. Draw the Crow’s Foot ERD.

Paper For Above instruction

This paper addresses the comprehensive process of normalizing a relational database schema based on given requirements, emphasizing identifying and removing partial and transitive dependencies, and understanding the implications for database normalization forms. The primary goal is to ensure the database structure adheres to best practices for integrity, reduction of redundancy, and efficiency, culminating in an accurate Entity-Relationship Diagram (ERD) using Crow’s Foot notation.

Initial Schema Description:

The table in question appears to be an order or invoice table, which involves multiple products per invoice and includes a composite primary key. Let's suppose an initial schema called 'InvoiceDetails' with attributes: Invoice_Number, Product_ID, Quantity, Price, Customer_ID, Customer_Name, Customer_Address, and perhaps other relevant details. The primary key is a composite of Invoice_Number and Product_ID, as each invoice can contain multiple products.

Step 1: Identifying Dependencies

- The composite primary key is (Invoice_Number, Product_ID).

- Partial dependencies occur if any non-prime attribute depends only on a part of the composite key. For instance, if Customer_ID, Customer_Name, and Customer_Address depend solely on Invoice_Number, then there's a partial dependency.

- Transitive dependencies occur if a non-prime attribute depends on another non-prime attribute, which in turn depends on the primary key. For example, if Customer_Name and Customer_Address depend on Customer_ID, which depends on Invoice_Number, then Customer_Name and Customer_Address transitively depend on the primary key via Customer_ID.

Step 2: Removing Partial Dependencies

To eliminate partial dependencies, we need to decompose the original schema into two tables:

1. Invoice table: Invoice_Number, Customer_ID, Date, etc.

2. InvoiceDetails table: Invoice_Number, Product_ID, Quantity, Price

In the first, attributes depend solely on Invoice_Number; in the second, attributes depend on both Invoice_Number and Product_ID.

Step 3: Drawing Dependency Diagrams

The dependency diagram for the initial schema shows dependencies from the composite key to non-prime attributes, with dependencies from Invoice_Number to customer details. After decoupling, the dependency diagram reflects two tables with their respective keys and dependencies.

Step 4: Normalization to 2NF

The first step ensures every non-prime attribute is fully dependent on the entire primary key, thus achieving Second Normal Form (2NF). The split into 'Invoice' and 'InvoiceDetails' tables eliminates partial dependencies.

Step 5: Removing Transitive Dependencies

Transitive dependencies involve non-prime attributes depending on other non-prime attributes. In this example, Customer_Name and Customer_Address depend on Customer_ID, which depends on Invoice_Number. So, to remove transitive dependencies, we also create a Customer table:

- Customer: Customer_ID, Customer_Name, Customer_Address, etc.

Now, the 'Invoice' table references Customer_ID via a foreign key, and customer details are stored separately, eliminating transitive dependencies.

Step 6: Final Normalized Schema

The normalized schema includes:

- Customer: Customer_ID (PK), Customer_Name, Customer_Address

- Invoice: Invoice_Number (PK), Customer_ID (FK), Date

- InvoiceDetails: Invoice_Number (PK, FK), Product_ID (PK, FK), Quantity, Price

- Product: Product_ID (PK), Product_Name, Unit_Price

Each table adheres to 3NF, with no partial or transitive dependencies.

Step 7: Drawing the Crow’s Foot ERD

The ERD illustrates the relationships:

- Customer (1) ------ (Many) Invoice

- Invoice (1) ------ (Many) InvoiceDetails

- Product (1) ------ (Many) InvoiceDetails

This ERD accurately captures the relationships between customers, invoices, products, and invoice details, demonstrating referential integrity and normalization.

Conclusion:

Through systematic identification and removal of partial and transitive dependencies, the database schema is normalized to at least 3NF, ensuring minimal redundancy and maximized data integrity. The Crow’s Foot ERD visually depicts the relationships among the entities, providing clarity for database implementation.

References

  • Communications of the ACM, 13(6), 377-387.
  • An Introduction to Database Systems (8th ed.). Pearson.
  • Modern Database Management. Pearson.
  • Entity-Relationship Modeling: Foundations of Database Design. Morgan Kaufmann.
  • Fundamentals of Database Systems (7th ed.). Pearson.
  • Database Systems Journal, 2(3), 45-50.
  • Information Systems Journal, 22(4), 319-337.
  • Journal of Data Management, 15(2), 124-138.
  • Computer Science Review, 29, 101-115.
  • Foundations of Database Systems. Cengage Learning.

Related Assignments