7 - Designing Relational Databases from ER Diagrams — Module Topics

Introduction to ER Diagrams and Relational Schemas

• What Is an Entity-Relationship (ER) Diagram? — An ER diagram is a visual blueprint that models the data requirements of a system by depicting entities, their attributes, and the relationships between them.
• Core ER Diagram Notation and Symbols — ER diagrams use a standardized set of symbols to represent each modeling concept, making them readable across teams and tools.
• What Is a Relational Schema? — A relational schema is the formal description of a relational database's structure, specifying each table, its columns, data types, and constraints.
• The Role of ER Diagrams in Database Design — ER diagrams occupy the conceptual design phase, acting as an intermediary step between understanding business requirements and implementing physical tables.
• Key Terminology: Entities, Attributes, and Relationships — Precise terminology is essential for translating ER diagrams into relational schemas without ambiguity or loss of meaning.
• Weak Entities and Their Dependence on Strong Entities — A weak entity is one that cannot be uniquely identified by its own attributes alone and must rely on a related strong entity for its identification.

Mapping Strong Entities and Attributes to Tables

• What Is a Strong Entity? — A strong entity is an entity type that has a unique identifier and can exist independently in the database without relying on any other entity.
• Converting a Strong Entity to a Relational Table — Each strong entity in an ER diagram maps directly to a single relational table in the database schema.
• Mapping Simple Attributes to Columns — Each simple (single-valued, atomic) attribute of a strong entity becomes a column in the corresponding relational table.
• Handling Composite Attributes — Composite attributes, which are made up of multiple sub-attributes, require a decision about how they are represented in the relational table.
• Identifying and Assigning the Primary Key — The key attribute of a strong entity, identified in the ER diagram by an underlined attribute name, becomes the primary key of the corresponding table.
• Dealing with Multi-Valued Attributes — Multi-valued attributes, which can hold more than one value for a single entity instance, cannot be directly stored as a single column in a relational table.
• Derived Attributes and Their Treatment — Derived attributes are values that can be calculated from other stored data and are typically not stored as columns in the relational table.

Handling One-to-Many Relationships

• Understanding One-to-Many Relationships in ER Diagrams — A one-to-many (1:N) relationship means that one instance of an entity on the 'one' side can be associated with multiple instances of the entity on the 'many' side, but not vice versa.
• The Foreign Key Placement Rule — In a one-to-many relationship, the foreign key is always placed in the table representing the 'many' side of the relationship, referencing the primary key of the 'one' side.
• Translating the 'One' Side Entity to a Table — The entity on the 'one' side of the relationship is converted into a table whose primary key will serve as the reference point for the foreign key in the child table.
• Translating the 'Many' Side Entity to a Table — The entity on the 'many' side is converted into a table that includes both its own attributes and an additional foreign key column referencing the parent table.
• Determining Which Side Holds the Foreign Key: A Decision Process — Students must practice a systematic approach to determine the correct placement of the foreign key when reading an ER diagram.
• Handling Participation Constraints (Optional vs. Mandatory) — Participation constraints in an ER diagram indicate whether every entity instance must participate in the relationship, which affects how the foreign key column is defined.
• Common Mistakes and Best Practices — Several frequent errors arise when mapping one-to-many relationships, and following best practices ensures a clean, normalized schema.

Handling Many-to-Many Relationships

• Why Many-to-Many Relationships Cannot Be Directly Mapped — A many-to-many relationship between two entities cannot be represented by simply adding a foreign key to either participating table.
• Introducing the Junction Table — A junction table (also called an associative table, bridge table, or linking table) is created to resolve a many-to-many relationship by representing each pairing of related entities as its own row.
• Defining Foreign Keys in the Junction Table — The junction table includes at least two foreign key columns, each referencing the primary key of one of the participating entity tables.
• Forming the Composite Primary Key — The combination of the two foreign key columns in a junction table typically forms a composite primary key, uniquely identifying each relationship instance.
• Adding Relationship Attributes to the Junction Table — When the many-to-many relationship in the ER diagram carries its own attributes, those attributes become additional columns in the junction table.
• Mapping the ER Diagram to the Junction Table Schema — Converting a many-to-many relationship from an ER diagram follows a consistent set of steps to produce the correct relational schema.

Mapping Weak Entities and Their Relationships

• Understanding Weak Entities — A weak entity is one that cannot be uniquely identified by its own attributes alone and must rely on a related parent (owner) entity for its full identification.
• Partial Keys and Their Role — A partial key is the attribute or set of attributes that partially distinguishes weak entity instances among those belonging to the same owner entity instance.
• Creating the Weak Entity's Table — When mapping a weak entity to a relational table, you include all of its own attributes plus the primary key of its owner entity as a foreign key.
• Forming the Composite Primary Key — The primary key of a weak entity's table is a composite key formed by combining the partial key of the weak entity with the foreign key inherited from the owner entity.
• Mapping the Identifying Relationship — The identifying relationship between a weak entity and its owner does not always require a separate junction table; instead, it is captured directly through the foreign key embedded in the weak entity's table.
• Referential Integrity and Deletion Behavior — Weak entities have an existential dependency on their owner, meaning that if the owner entity is deleted, the weak entity instances associated with it should also be removed.

Validating and Refining the Relational Schema

• Cross-Checking Tables Against ER Entities and Relationships — The first step in validation is verifying that every entity and relationship in the original ER diagram has been correctly represented as a table or foreign key in the schema.
• Verifying Primary Key Integrity — Each table in the schema must have a clearly defined primary key that uniquely identifies every row, consistent with the identifiers specified in the ER diagram.
• Validating Foreign Key Constraints and Referential Integrity — Foreign keys enforce referential integrity by ensuring that values in a referencing column always correspond to an existing row in the referenced table.
• Checking for Consistency and Naming Conventions — A consistent, readable schema uses uniform naming conventions for tables, columns, primary keys, and foreign keys throughout, reducing ambiguity and maintenance errors.
• Applying Normalization Checks to the Schema — After mapping the ER diagram, the schema should be reviewed against normalization principles — at minimum up to Third Normal Form (3NF) — to eliminate redundancy and update anomalies.
• Confirming Completeness of Integrity Constraints — Beyond primary and foreign keys, the schema should encode all integrity constraints implied by the ER diagram, including domain constraints, uniqueness constraints, and participation rules.
• Iterating and Finalizing the Schema — Schema validation is an iterative process; findings from the review steps should feed back into corrections, followed by re-verification until the schema fully and accurately reflects the ER diagram.