Relational Database Design and Normalization Essentials

Database Management Systems and SDLC

Chapter 1: Introduction to Databases

• Data: Raw facts like numbers, names, and pictures.
• TFPS (Traditional File Processing System): The biggest problem was when data changes in one file, it could cause inconsistencies.
• Cons of TFPS: Data redundancy, limited sharing, long development times, and high maintenance.
• DBMS (Database Management System): Software used to manage databases.
• SDLC (Systems Development Life Cycle): Plan, analysis, design, implementation, and maintenance.
• Prototype: Build a prototype, implement, and improve it over time.

Entity-Relationship (ER) Modeling Concepts

Chapter 2: Database Design and ER Diagrams

• Entity: A noun, such as a person, place, or thing.
• Attributes: Descriptions or properties of an entity.
• Types of Attributes: Simple, composite, multivalued, and derived.
• Relationship: Shows how entities are related to each other.
• Cardinality: Shows how many instances of one entity connect to another.
• Strong Entity: Can stand on its own.
• Weak Entity: Depends on a strong entity to exist.
• ER Diagram (Why It Matters): A map for how data is connected inside a database. It helps determine what tables to make, how data connects, how to avoid duplicate data, and is easy to understand.
• Supertype: Parent entity (e.g., Vehicle).
• Subtype: Child entity (e.g., Car, Truck).
• Generalization: Taking many small groups and making them into one big group.
• Specialization: Breaking a big group down into smaller ones.
• Aggregation: Treats a relationship as an entity.

The Relational Model (RM) and Keys

• Characteristics of RM:
  • Each table has a unique name.
  • Each attribute has a unique name.
  • The order of rows and columns does not matter.
  • There are no duplicate rows.
  • Each column value must belong to a domain.
  • Values are atomic (not groups).
• Primary Key (PK): Uniquely identifies each row in a table.
• Foreign Key (FK): References a Primary Key in another table to maintain a relationship.
• Candidate Key: A column that is unique and minimal; a potential Primary Key.
• Integrity Constraint: Ensures data accuracy and consistency.
• Domain Constraint: Defines acceptable values and data types, ensuring values fall within specific ranges or formats.
• Referential Integrity Rules:
  • Cascade: Changes or deletions propagate to dependent rows.
  • Set Null: Foreign Key values are set to null.
  • Restrict: The operation is denied if a Foreign Key reference exists.

Database Normalization and Anomalies

Normalization: The process of organizing database attributes into tables to reduce redundancy.

• Insertion Anomaly: Cannot add data without other unrelated information.
• Deletion Anomaly: Deleting data unintentionally removes other important facts.
• Update Anomaly: Changing a value requires multiple updates across the database.
• First Normal Form (1NF): No repeating groups or multivalued attributes.
• Second Normal Form (2NF): The table is in 1NF, tables are decomposed into smaller keys, and non-key attributes depend on the entire composite key.
• Third Normal Form (3NF): The table is in 2NF and has no transitive dependencies.

Mapping ERD to Relational Schema

• Regular Entity: Becomes its own table.
• Weak Entity: The table includes the parent's Primary Key as a Foreign Key.
• 1:N Relationship: The Foreign Key goes on the "many" side.
• 1:1 Relationship: The Foreign Key goes in the smaller table (resulting in fewer nulls).
• M:N Relationship: Creates a new intersection table.
• Associative Entity: Creates a new table with Foreign Keys.
• Unary Relationship: The Foreign Key is in the same table.
• Ternary Relationship: Creates new tables with all Primary Keys.
• Supertype/Subtype: Main table plus one table per subtype.