Write The Relational Schema And Draw Its Dependency Diagram

A Write The Relational Schema Draw Its Dependency Diagram And Ident

A 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 an invoice number references 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 table is attached in word document.

Paper For Above instruction

Introduction

Relational database design is fundamental to ensuring data integrity, efficiency, and ease of access. Core to this process are the concepts of normalization, dependency diagrams, and Entity-Relationship Diagrams (ERDs). This paper examines the development of a relational schema from an initial table, identifying dependencies, and progressing through normalization stages to achieve higher normal forms. It also includes drawing a dependency diagram and a Crow’s Foot ERD based on an attached table.

Initial Relational Schema and Dependency Analysis

The initial step involves understanding a hypothetical table used to record invoices and products. Assume the table includes attributes like invoice number, product ID, product description, supplier, quantity, and price. The primary key comprises a composite of invoice number and product ID, since each invoice can contain multiple products, but a specific product appears once per invoice.

Based on this, the initial relational schema might be:

InvoiceProducts (InvoiceNumber, ProductID, ProductDescription, Supplier, Quantity, Price)

In this schema, the primary key (InvoiceNumber, ProductID) uniquely determines other attributes: ProductDescription, Supplier, Quantity, and Price.

The initial dependency diagram shows that the entire primary key functionally determines all other attributes, but there are dependencies worth noting. Since ProductDescription, Supplier, and Price depend only on ProductID (not on InvoiceNumber), partial dependencies are present: 

ProductID → ProductDescription, Supplier, Price

This indicates that the initial table suffers from partial dependencies because non-key attributes (ProductDescription, Supplier, Price) depend only on part of the composite key (ProductID).

Transitive dependencies might appear if, for example, ProductDescription determines Supplier or Price, which could be the case in some schemas.

Normalizing to Remove Partial Dependencies

To eliminate partial dependencies, the schema must be decomposed into tables where each non-key attribute is fully functionally dependent on the entire primary key.

The first step yields two tables:

1. InvoiceProducts (InvoiceNumber, ProductID, Quantity)


2. Products (ProductID, ProductDescription, Supplier, Price)

Here, InvoiceProducts references Products via ProductID. Each table's primary key is:

- InvoiceProducts: (InvoiceNumber, ProductID)

- Products: ProductID

In this stage, the schema adheres to Second Normal Form (2NF), as partial dependencies are removed.

The dependency diagram now shows that:

- (InvoiceNumber, ProductID) determines Quantity

- ProductID determines ProductDescription, Supplier, Price

Removing Transitive Dependencies

Next, the goal is to eliminate transitive dependencies—where a non-key attribute depends on another non-key attribute. For example, if Price depends on Supplier, which depends on ProductID, that indicates a transitive dependency.

If Price depends solely on ProductID, then no transitive dependency exists since Price depends directly on ProductID. But assuming some dependence such as Supplier determining Price (which is unlikely in real-world scenarios), the schema would need further normalization.

Suppose the following dependencies:

ProductID → ProductDescription, Supplier, Price


Supplier → SupplierContact

then, Supplier determines SupplierContact, indicating a transitive dependency. To resolve this, we separate Supplier details:

3. Suppliers (SupplierID, SupplierName, SupplierContact)

And update Products:

Products (ProductID, ProductDescription, SupplierID, Price)

Now, the schemas are:

- InvoiceProducts (InvoiceNumber, ProductID, Quantity)

- Products (ProductID, ProductDescription, SupplierID)

- Suppliers (SupplierID, SupplierName, SupplierContact)

These structures are in Third Normal Form (3NF) because transitive dependencies are removed.

Drawing Dependency Diagrams

Using dependency diagrams, the relationships are visualized as:

- (InvoiceNumber, ProductID) → Quantity

- ProductID → ProductDescription, SupplierID

- SupplierID → SupplierName, SupplierContact

Each arrow indicates a functional dependency.

ER Diagram: Crow’s Foot Notation

The final step involves drawing a Crow’s Foot ERD. Based on the above tables, the entities are:

- Invoice (with InvoiceNumber)

- Product (with ProductID)

- Supplier (with SupplierID)

Relationships:

- An Invoice contains many Products (many-to-many), linked via InvoiceProducts.

- Each Product is supplied by one Supplier, but a Supplier provides many Products.

The ERD depicts:

- Invoice linked to Product via InvoiceProducts with a many-to-many relationship.

- Product linked to Supplier with a many-to-one relationship.

This ERD facilitates understanding the database’s structure and relationships.

Conclusion

Constructing a normalized database schema involves identifying and removing partial and transitive dependencies to achieve higher normal forms like 3NF. Dependency diagrams clarify how attributes depend on each other, and ER diagrams graphically represent the entity relationships. Proper normalization improves data integrity, reduces redundancy, and streamlines queries, essential for reliable and efficient database systems.

References

  • Database Systems (6th ed.). Pearson Education.
  • Fundamentals of Database Systems (7th ed.). Pearson.
  • An Introduction to Database Systems (8th ed.). Pearson.
  • Database System Concepts (7th ed.). McGraw-Hill Education.
  • Database Management Systems (3rd ed.). McGraw-Hill.
  • Principles of Database and Knowledge-Base Systems. Computer Science Press.
  • Database Design and Relational Theory: Normal Forms and All That. O'Reilly Media.
  • Database Processing: Fundamentals, Design, and Implementation (13th ed.). Pearson.
  • Relational Database Design and Implementation (4th ed.). Morgan Kaufmann.
  • Database Modeling & Design (5th ed.). Morgan Kaufmann.

Related Assignments