Unit 2: Database Design and ER Model

Overview of the Design Process

Database design is the formal process of creating a structured schema for a database system that meets user requirements, ensures data integrity, avoids redundant storage, and supports efficient query processing.

Phases of Database Design

1. Requirements Analysis: Understanding what data needs to be stored, what applications build upon it, and what operational constraints exist by interviewing domain experts and users.
2. Conceptual Database Design: Creating a high-level conceptual schema using a data model like the Entity-Relationship (ER) model. This step focuses on enterprise entities, relationships, and constraints without worrying about software implementation details.
3. Logical Database Design: Translating the high-level conceptual schema into a relational database schema (tables, columns, primary keys, and foreign keys).
4. Schema Refinement (Normalization): Analyzing logical schemas to eliminate data redundancy and anomalies (insertion, update, and deletion anomalies).
5. Physical Database Design: Selecting physical storage structures, indexing strategies, file organizations, and partitioning to satisfy performance requirements.
6. Security and Application Design: Defining user roles, access privileges, views, and application interfaces.

Database Design Process Goal: Transform real-world enterprise requirements into an optimized, unambiguous, and consistent collection of database tables and operational constraints.

Comparison of Design Schemas

Exam Note: Questions frequently ask to outline the step-by-step database design life cycle. Always highlight that logical design transforms high-level ER concepts into physical relational tables.

Entity-Relationship Model

The Entity-Relationship (ER) model is a high-level conceptual data model introduced by Peter Chen in 1976. It models real-world enterprise data using entities, relationships, and attributes.

Entities and Entity Sets

Entity: A real-world object, person, place, or concept that is distinguishable from other objects based on its attributes. Example: A specific student named John with ID 101.

Entity Set: A collection or set of entities of the same type that share similar properties or attributes. Example: The set of all students in a university.

Attributes and Attribute Types

An attribute is a property or characteristic that describes an entity.

• Simple (Atomic) Attribute: An attribute that cannot be divided into smaller sub-components. Example: Age, Salary, Marks.
• Composite Attribute: An attribute that can be divided into smaller sub-parts with independent meaning. Example: Name (First_Name, Last_Name) or Address (Street, City, ZipCode).
• Single-valued Attribute: An attribute that holds a single value for a specific entity instance. Example: Social_Security_Number, Date_of_Birth.
• Multi-valued Attribute: An attribute that can store multiple values for a single entity instance. Example: Phone_Numbers, Degrees.
• Derived Attribute: An attribute whose value is calculated or derived from another attribute. Example: Age (derived from Date_of_Birth), Total_Amount (derived from Item_Price * Quantity).
• Null Attribute: An attribute value assigned when an entity does not have a value or when the value is unknown/missing.

Relationships and Relationship Sets

Relationship: An association among two or more entities. Example: Student "John" ENROLLS_IN Course "Database Systems".

Relationship Set: A mathematical set of relationships of the same type. Formally, if E1, E2, ..., En are entity sets, then a relationship set R is a subset of {(e1, e2, ..., en) | e1 ∈ E1, e2 ∈ E2, ..., en ∈ En}.

Degree of a Relationship Set: The number of participating entity sets in a relationship set.

• Unary (Degree 1): Relationship between entities of the same entity set (e.g., Employee SUPERVISES Employee).
• Binary (Degree 2): Relationship between two entity sets (e.g., Student ENROLLS Course).
• Ternary (Degree 3): Relationship involving three entity sets (e.g., Supplier SUPPLIES Part TO Project).

Constraints

Data constraints define restrictions on valid data stored in the database, preserving accuracy and data integrity.

Mapping Cardinalities (Cardinality Ratios)

Mapping cardinality specifies the number of entities to which another entity can be associated via a relationship set in binary relationships:

• One-to-One (1:1): An entity in A is associated with at most one entity in B, and an entity in B is associated with at most one entity in A. Example: Manager MANAGES Department.
• One-to-Many (1:N): An entity in A is associated with any number of entities in B, but an entity in B can be associated with at most one entity in A. Example: Department EMPLOYS Employee.
• Many-to-One (N:1): An entity in A is associated with at most one entity in B, but an entity in B can be associated with any number of entities in A. Example: Student BELONGS_TO Department.
• Many-to-Many (M:N): An entity in A is associated with any number of entities in B, and vice versa. Example: Student ENROLLS Course.

Participation Constraints

Participation constraints specify whether the existence of an entity depends on its being related to another entity via the relationship set.

• Total Participation (Existence Dependency): Every entity in the entity set must participate in at least one relationship in the relationship set. Example: Every Department MUST have a Manager.
• Partial Participation: Some entities in the entity set may not participate in any relationship in the relationship set. Example: Not every Employee MANAGES a Department.

Keys in ER Modeling

Super Key: A set of one or more attributes that uniquely identifies an entity in an entity set.

Candidate Key: A minimal super key; a super key from which no subset can be removed without losing the ability to uniquely identify entities.

Primary Key: A candidate key selected by the database designer to uniquely identify entities within an entity set.

Primary Key for Relationship Sets:

• For 1:1 binary relationship set: Primary key can be the primary key of either entity set.
• For 1:N or N:1 binary relationship set: Primary key is the primary key of the 'Many' side entity set.
• For M:N binary relationship set: Primary key is the combination (union) of primary keys of both participating entity sets.

ER Diagrams

An ER Diagram is a graphical representation of the logical database structure.

Standard ER Symbols Summary

Example Conceptual Diagram Mapping

Consider a University Database System:

• Entity Sets: Student (StudentID, Name, Email), Course (CourseID, Title, Credits).
• Relationship Set: Enrolls (Grade, Date).
• Cardinality: M:N (A student can enroll in multiple courses; a course has multiple enrolled students).
• Participation: Course total participation in Enrolls (every course must have at least one enrolled student).

ER Design Issues

Designing a conceptual schema requires making structural choices. Designers often face non-trivial design dilemmas.

1. Entity Set vs. Attribute

Choosing whether to represent an object as an entity set or an attribute depends on the business domain requirements.

Example: Should Phone_Number be an attribute of Employee or an Entity Set? If an employee has multiple phone numbers, and location or carrier info must be recorded for each number, Phone_Number must be an Entity Set, not a plain attribute.

2. Entity Set vs. Relationship Set

It can be unclear whether an action or object is best represented as an entity set or a relationship set.

Guideline: Use a relationship set when describing an action or association taking place between existing entities. If the association itself needs to participate in other relationships, transform the relationship into an entity set.

3. Binary vs. Ternary (n-ary) Relationships

While ternary relationships represent relationships among three entities directly, they can often be replaced by multiple binary relationships or by introducing a new connecting entity set.

Common Mistake: Forcing a ternary relationship into binary relationships without maintaining proper semantic constraints, leading to loss of association integrity.

4. Placement of Relationship Attributes

Attributes belonging to a relationship set can be moved to participating entity sets in 1:1 or 1:N cardinalities, but MUST stay on the relationship set in M:N cardinalities.

Example: In a 1:N relationship between Department and Employee, 'Start_Date' of management can be placed in Employee or Relationship, but in M:N relationship Enrolls (Student, Course), 'Grade' MUST be on the relationship set.

Weak Entity Sets

An entity set may not have sufficient attributes to form a primary key on its own.

Weak Entity Set: An entity set that does not possess a primary key attribute and depends on the existence of an identifying (strong) entity set.

Strong Entity Set: An entity set that possesses a primary key that uniquely identifies each entity instance independently.

Key Concepts of Weak Entity Sets

• Identifying Entity Set (Parent/Owner): The strong entity set upon which the weak entity set depends.
• Identifying Relationship: The relationship linking the weak entity set to its identifying strong entity set (represented by a double diamond).
• Partial Key (Discriminator): The set of attributes that uniquely distinguishes weak entity instances that depend on the same strong entity instance (represented with a dashed underline).
• Participation: A weak entity set ALWAYS has total participation in its identifying relationship.

Primary Key Composition for Weak Entities

The primary key of a weak entity set is formed by combining the primary key of its identifying strong entity set and its own partial key (discriminator).

Primary Key (Weak Entity) = Primary Key (Strong Entity) + Partial Key (Weak Entity)

Real-World Example

Consider an Employee and Dependents database:

• Strong Entity: Employee (Primary Key: Employee_ID)
• Weak Entity: Dependent (Partial Key: Dependent_Name)
• Identifying Relationship: Has_Dependent
• Primary Key of Dependent: (Employee_ID, Dependent_Name)

Comparison: Strong Entity vs Weak Entity Set

Extended ER Features

As applications became more complex, extended/enhanced ER (EER) features were introduced to model advanced data structures.

1. Specialization

Specialization: A top-down design process where a higher-level entity set is broken down into sub-groupings of lower-level entity sets based on distinctive characteristics.

Example: An entity set Person can be specialized into Student and Employee entity sets based on role.

2. Generalization

Generalization: A bottom-up design process where multiple lower-level entity sets sharing common properties are synthesized into a single higher-level entity set.

Example: Combining Car, Truck, and Motorcycle into a higher-level entity set Vehicle.

3. Attribute Inheritance

A crucial property of specialization and generalization is attribute inheritance. Lower-level entity sets automatically inherit all attributes and relationship participation from their higher-level superclass entity set.

4. Constraints on Specialization / Generalization

Disjointness Constraints:

• Disjoint Constraint: Specifies that an entity instance can belong to at most one lower-level entity set. (e.g., A Student cannot be a Faculty member simultaneously if configured as disjoint).
• Overlapping Constraint: An entity instance can simultaneously belong to multiple lower-level entity sets. (e.g., A Person can be both an Employee and a Student).

Completeness Constraints:

• Total Completeness: Every higher-level entity instance MUST belong to at least one lower-level entity set.
• Partial Completeness: Some higher-level entity instances may not belong to any lower-level entity set.

5. Aggregation

Aggregation: An abstraction through which relationships are treated as higher-level entities, allowing relationship sets to be associated with other entity sets or relationship sets.

Why Aggregation is Needed: Standard ER diagrams do not allow relationships between relationships. Aggregation encapsulates a relationship set and its participating entity sets into a single composite abstract entity.

Example: An Employee works on a Project at a specific Job_Site. The relationship Works_On(Employee, Project) is aggregated into a single entity which then enters into a relationship with Evaluation or Manager.