ACC306 Chaper 4 Video 3

Introduction to Relational Database Design

• Purpose: Understanding how to create and design a relational database.

• Graphical Representation: Data is typically represented in tables known as relations in the relational model.

Basic Concepts in Relational Databases

• Tables: The fundamental structure in a relational database consists of tables.

• Attributes and Tuples:

  • An attribute is a column in a table.

  • A tuple is a row in a table.

Key Identifiers

• Primary Key: Unique identifier for each tuple, usually the first column in a table.

  • Example: Item ID is typically the primary key.

  • Definition: A primary key must contain unique values and cannot contain NULL values (this ensures each row can be uniquely identified).

• Foreign Key: Refers to the primary key from another table.

  • Example: Vendor ID is a foreign key; it is not unique and can be NULL.

  • Definition: A foreign key signifies a relationship between two tables and is used to maintain referential integrity in the database.

Problems with Storing Data in a Single Table

• Redundant Data: Storing all information in one table leads to redundant data, causing inefficiencies and potential anomalies.

  • Example of redudant data: Sales invoice data repeated multiple times (e.g., customer names, invoice dates)

• Types of Anomalies:

  • Update Anomaly: Changes to data in one location may not propagate to all relevant instances, leading to inconsistencies.

    • Example: Changing an address for a customer may not reflect everywhere this customer appears in the database.

  • Delete Anomaly: Removing a record can unintentionally delete necessary information about an entity.

    • Example: Deleting a sale might remove all details about a customer if that was the only sale recorded.

  • Insert Anomaly: Constraints in inserting new data can arise because of a lack of related records.

    • Example: New items cannot be added to the database unless they are sold; new customers cannot be added unless they have made a purchase.

Best Practices for Designing Relational Databases

• Normalization Process: To avoid redundancy, database design should break data into smaller, well-defined tables.

  • Each table should focus on one specific aspect (e.g., customers, sales, inventory).

  • Each table should maintain unique primary keys and establish adequate foreign keys to link data.

• Single Value Attribute: Each attribute in a table should hold a single value.

• Table Characteristics:

  • Example Tables:

    • Customer Table:

      • Attributes: Customer ID (Primary Key), Customer Name, Address, City, State.

    • Sales Table:

      • Attributes: Sales Invoice ID (Primary Key), Date, Salesperson, Customer ID (Foreign Key).

    • Inventory Table:

      • Attributes: Item ID (Primary Key), Item Description.

Key Database Design Rules

1. Entity Integrity Rule: Every primary key must be unique and cannot be NULL.

2. Referential Integrity Rule: A foreign key can be NULL and must correspond to a valid primary key in a referred table.

3. Non-Key Attributes: Attributes that do not fall under primary key or foreign key categories but provide essential details (e.g., item price, description).

Illustrative Example of Designing Relational Tables

• Problem Statement: Need to reorganize data from a single table (consolidated table with repeated data) into smaller, more structured tables to avoid anomalies.

Steps to Redesign the Database

1. Identify Tables:

   • Create an Invoice Table:

     • Invoice ID (Primary Key), Customer ID (Foreign Key), Shipping Date, Order Date.

   • Create a Customer Table:

     • Customer ID (Primary Key), Customer Name.

   • Create an Item Table:

     • Item ID (Primary Key), Description.

2. Create a Cross-Reference Table (to solve the problem of differing quantities for the same item on various invoices):

   • Invoice/Item Table:

     • Invoice ID (Foreign Key), Item ID (Foreign Key), Quantity.

     • This table allows for linking quantities of items to specific invoices without redundancy.

Practice Exercise

• Problem 4.8: Apply the principles discussed to the table presented, creating relational tables that address the update, insert, and delete anomalies.

• Error to Note: Review for corrections (example: Purchase ID should be labeled correctly).

Conclusion

• The importance of carefully planning and structuring a relational database to mitigate redundancy, maintain data integrity, and facilitate operations effectively. Keeping normalization principles in mind will allow successful database design that supports scalability and ease of use.