Relational Database Design/1

Key Concepts:

• Relational Database Design
• Good Design Practices
• Redundancy
• Anomalies (Insertion, Deletion, Update)
• Functional Dependency
• Decomposition (Lossy vs. Lossless)
• Normalization
• Atomic Domains
• First Normal Form (1NF)

1. Good Relational Design:

• Main Topics and Key Points: The module focuses on the principles of good relational database design, emphasizing the importance of normalization to reduce redundancy and anomalies. It introduces the first normal form (1NF) and atomic domains.
• Specific Details, Facts, Figures, and Technical Terms:
  • Good Design Practices: Reflect real-world structure, represent all expected data over time, avoid redundant storage, efficient access, support data integrity, clean, consistent, and easy to understand.
  • Contradictory Objectives: Efficiency of access (redundant data) vs. data integrity (redundancy makes it difficult to manage).
• Important Examples, Case Studies, or Real-World Applications Discussed:
  • Instructor and Department Relation: Demonstrates redundancy when combining instructor and department information into a single table.
  • Section and Class Relation: Shows a case where combining tables does not necessarily lead to redundancy.
  • Employee Table Decomposition: Illustrates lossy decomposition and the importance of preserving information.
  • Customer and Telephone Numbers: Shows how to handle multi-valued attributes and achieve 1NF.

2. Redundancy and Anomalies:

• Main Topics and Key Points: Redundancy leads to anomalies (inconsistencies) when changing data. Three types of anomalies are discussed: insertion, deletion, and update anomalies.
• Specific Details, Facts, Figures, and Technical Terms:
  • Redundancy: Multiple copies of the same data in the database.
  • Anomalies:
    • Insertion Anomaly: Inability to add a record without adding unrelated data. Example: Adding an instructor to a department without a building or budget.
    • Deletion Anomaly: Deletion of a record results in losing unrelated information. Example: Deleting the last instructor of a department and losing the department's building and budget information.
    • Update Anomaly: Inconsistency due to failure to update all redundant copies of data. Example: Updating the budget of a department in only some instructor records.
• Key Arguments or Perspectives Presented, with Their Supporting Evidence: Redundancy is caused by dependencies between attributes. Minimizing redundancy requires partitioning relations into smaller tables.

3. Functional Dependency and Decomposition:

• Main Topics and Key Points: Functional dependency is introduced as the cause of redundancy. Decomposition is the solution to remove or minimize redundancy. Good decomposition must preserve information and honor dependencies.
• Specific Details, Facts, Figures, and Technical Terms:
  • Functional Dependency: Attribute A functionally determines attribute B if, for every value of A, there is only one value of B.
  • Decomposition: Partitioning a relation into smaller relations.
  • Lossless Join Decomposition: A decomposition where the original relation can be reconstructed by joining the decomposed relations.
  • Lossy Decomposition: A decomposition where joining the decomposed relations results in additional, incorrect records.
• Step-by-Step Processes, Methodologies, or Frameworks Explained:
  • Normalization leads to good decomposition, which minimizes dependency, which minimizes redundancy, which minimizes anomaly.
• Important Examples, Case Studies, or Real-World Applications Discussed:
  • Employee Table Decomposition (Lossy): Decomposing the employee table into Employee1(ID, Name) and Employee2(Name, Street, City, Salary) results in a lossy decomposition because the join on Name introduces spurious tuples.
  • Table A, B, C Decomposition (Lossless): Decomposing a table R(A, B, C) into R1(A, B) and R2(B, C) can be lossless if the join of R1 and R2 on B results in the original table R.
• Conditions for Lossless Join Decomposition:
  • The union of the attributes of the decomposed relations must cover all attributes of the original relation.
  • The decomposed relations must have some intersection (common attributes) to enable joining.
  • The natural join of the projections of the original relation onto the decomposed relations must result in the original relation.

4. Atomic Domains and First Normal Form (1NF):

• Main Topics and Key Points: The module introduces atomic domains and the first normal form (1NF). A relation is in 1NF if all attributes have atomic domains and are single-valued.
• Specific Details, Facts, Figures, and Technical Terms:
  • Atomic Domain: A domain where every element is considered indivisible.
  • Composite Domain: A domain that can be further divided into sub-parts. Example: Name (first name, middle name, last name).
  • First Normal Form (1NF): A relation is in 1NF if all attributes have atomic domains and are single-valued.
• Important Examples, Case Studies, or Real-World Applications Discussed:
  • Customer and Telephone Numbers (Not in 1NF): A table with customer information and a "telephone numbers" field is not in 1NF because telephone numbers are composite and multi-valued.
  • Attempt to Fix with Two Telephone Number Fields (Still Not Good): Creating two separate telephone number fields is not a good solution because it doesn't handle customers with more than two numbers and introduces ambiguity in querying.
  • Separate Entries for Separate Telephone Numbers (Redundancy): Using separate entries for each telephone number of a customer introduces redundancy and changes the key.
  • Customer and Customer Telephone Number Tables (1NF): Separating the customer information and telephone number information into two tables, Customer(CustomerID, Name, ...) and CustomerTelephoneNumber(CustomerID, TelephoneNumber), resolves the 1NF violation and avoids redundancy.
• Key Arguments or Perspectives Presented, with Their Supporting Evidence: Multi-valued attributes and composite attributes violate 1NF. Decomposing relations with one-to-many relationships between attributes is a solution to achieve 1NF.

5. Synthesis/Conclusion:

The module provides a foundational understanding of relational database design principles. It emphasizes the importance of normalization to reduce redundancy and anomalies. The module introduces functional dependency, decomposition, atomic domains, and the first normal form (1NF). The examples and case studies illustrate the practical implications of these concepts and provide guidance on how to design good relational schemas. The key takeaway is that normalization, starting with achieving 1NF, is crucial for creating clean, consistent, and maintainable databases.