Unit 2: E-R Modeling

I. Orientation

Entity-Relationship (E-R) modeling is a conceptual database-design method introduced by Peter Chen in 1976. It represents the real-world objects, properties, and associations that a database must store before those requirements are implemented as relational tables. The central principle is to separate the meaning of data from its physical storage details.

• Conceptual focus: Model entities such as STUDENT, attributes such as Student_ID, and relationships such as “enrolls in.”
• Design sequence: Identify requirements, construct an E-R model, convert it into relational schemas, and enforce integrity through constraints and transactions.
• Abstraction levels: Conceptual design describes meaning; logical design describes relations; physical design describes files, indexes, and storage.
• Integrity assumption: Every stored value should satisfy domain, key, entity-integrity, and referential-integrity rules.
• Transaction assumption: Database operations may execute concurrently or fail, so transactions must preserve the ACID properties.

II. Conceptual E-R Model

The conceptual E-R model provides a structured description of data independent of a particular database management system.

A. Basics of ER Modelling

The basics of ER Modelling consist of identifying objects, their properties, and the meaningful associations between them.

• Entity: An entity is a distinguishable real-world object, such as a student with Student_ID = 1024.
• Entity set: An entity set is a collection of similar entities, such as all records in STUDENT.
• Attribute: An attribute describes an entity or relationship. For example, Date_of_Birth describes a student.
• Relationship: A relationship connects entity instances, such as STUDENT “enrolls in” COURSE.
• Degree: The degree is the number of participating entity sets. A relationship between STUDENT and COURSE is binary; one involving EMPLOYEE, PROJECT, and DEPARTMENT is ternary.
• Design principle: Model facts once and represent dependencies explicitly; storing a student's department name repeatedly in enrollment records creates redundancy.

B. Entities, Attributes, and Relationships

Entities, Attributes, and Relationships are the three primary building blocks of an E-R model.

• Strong entity: A strong entity has its own identifying key. EMPLOYEE(Employee_ID, Name) can exist independently.
• Weak entity: A weak entity depends on an owner entity and has a partial key. DEPENDENT(Name, Birth_Date) may require Employee_ID plus Name for identification.
• Simple attribute: Salary stores one indivisible value for a particular design.
• Composite attribute: Name may be divided into First_Name, Middle_Name, and Last_Name; components can be stored separately.
• Single-valued attribute: Each student has one Admission_Date.
• Multivalued attribute: A student may have several phone numbers. This is usually represented by a separate relation rather than a repeating column.
• Derived attribute: Age can be calculated from Date_of_Birth and the current date, so storing both may create inconsistency.
• Relationship attributes: Grade belongs to the ENROLLS relationship because it depends on both a student and a course.
• Cardinality: A one-to-many relationship allows one department to have many employees, while each employee belongs to one department.
• Participation: Total participation means every employee must belong to a department; partial participation permits an employee without an assigned department.

C. ER Diagrams and Notations

ER Diagrams and Notations provide a visual language for documenting entities, attributes, relationships, and structural restrictions.

• Rectangles: A rectangle represents an entity set, such as STUDENT.
• Ellipses: An ellipse represents an attribute, such as Name.
  • Underlined attribute: An underlined attribute is a key, such as Student_ID.
  • Double ellipse: A double ellipse represents a multivalued attribute, such as Phone_Number.
  • Dashed ellipse: A dashed ellipse represents a derived attribute, such as Age.
• Diamonds: A diamond represents a relationship set, such as ENROLLS.
• Double rectangle: A double rectangle identifies a weak entity, such as DEPENDENT.
• Double diamond: A double diamond identifies the identifying relationship between a weak entity and its owner.
• Participation lines: A double line indicates total participation; a single line indicates partial participation.
• Min-max notation: (0, N) means an entity may participate zero to many times; (1, 1) means exactly one participation.
• Crow's-foot notation: A crow's foot indicates “many,” while a single bar indicates “one”; an optional relationship is often shown using a circle.

III. Relational Representation

Relational representation transforms conceptual entities and relationships into tables whose rows are tuples and whose columns are attributes.

A. Conversion of ER Models to Relational Schemas

Conversion of ER Models to Relational Schemas applies systematic mapping rules so that E-R semantics are preserved in relations.

• Strong entity rule: Create one relation for each strong entity. For STUDENT(Student_ID, Name, DOB), underline or declare Student_ID as the primary key.
• Composite attribute rule: Store only the components. Name(First_Name, Last_Name) becomes two columns rather than one structured value.
• Multivalued attribute rule: Create a separate relation containing the owner key and the multivalued attribute:

STUDENT_PHONE(Student_ID, Phone_Number)
PRIMARY KEY (Student_ID, Phone_Number)

• One-to-one rule: Place the primary key of one entity as a foreign key in the other, preferably on the side with total participation. Add UNIQUE when the foreign key must identify at most one row.
• One-to-many rule: Place the key of the “one” side in the relation representing the “many” side. Department_ID becomes a foreign key in EMPLOYEE.
• Many-to-many rule: Create a new relation containing the keys of both entities. ENROLLS(Student_ID, Course_ID, Grade) normally uses (Student_ID, Course_ID) as its primary key.
• Weak entity rule: Include the owner's key in the weak entity relation. DEPENDENT(Employee_ID, Name, Birth_Date) may use (Employee_ID, Name) as its primary key.
• Relationship attributes: Store them in the relationship relation for many-to-many relationships, because Grade depends on the particular student-course pair.

IV. Transaction Processing

A transaction is a logical unit of database work, such as transferring 500 units from account A to account B. The DBMS must preserve correctness despite failures and concurrent execution.

A. Introduction and implementation of ACID properties

Introduction and implementation of ACID properties explains the four guarantees that define reliable transaction processing.

• Atomicity: A transaction executes completely or has no effect. In a transfer, both UPDATE statements must commit together; otherwise the DBMS performs ROLLBACK.
• Consistency: A committed transaction changes one valid database state into another. If an account balance cannot be negative, a balance constraint must remain satisfied after commit.
• Isolation: Concurrent transactions should not expose intermediate results. Isolation levels such as READ COMMITTED prevent one transaction from reading another transaction's uncommitted update.
• Durability: After COMMIT, changes survive a system crash. Write-ahead logging records change information before modified data pages are written to storage.
• Implementation mechanism: Locks, timestamps, multiversion concurrency control, logging, checkpoints, and recovery procedures collectively implement ACID behavior.
• Transaction structure: A typical transaction is explicitly bounded:

BEGIN TRANSACTION;
UPDATE ACCOUNT
SET Balance = Balance - 500
WHERE Account_ID = 1;

UPDATE ACCOUNT
SET Balance = Balance + 500
WHERE Account_ID = 2;
COMMIT;

Here, BEGIN TRANSACTION starts the unit, COMMIT makes both updates permanent, and an error should cause ROLLBACK.

• Failure handling: If a crash occurs before commit, recovery uses the log to undo incomplete work; if commit was recorded, it redoes necessary changes.
• Isolation trade-off: Stronger isolation reduces anomalies such as dirty reads and lost updates but may increase locking and waiting.

V. Relational Integrity

Relational integrity ensures that values and relationships stored in tables remain valid over time.

A. Constraints on relations

Constraints on relations are rules declared by the schema or enforced by application logic to reject invalid database states.

• Domain constraint: A column value must have an allowed type, format, and range. Credits INTEGER CHECK (Credits BETWEEN 1 AND 6) rejects Credits = 0.
• NOT NULL constraint: A mandatory attribute such as Student_ID cannot be absent.
• Key constraint: No two tuples may share the same declared primary-key value. Two students cannot both have Student_ID = 1024.
• Entity integrity: A primary key must be unique and never NULL, because every tuple requires an unambiguous identity.
• Referential integrity: A foreign-key value must match a referenced primary-key value or be NULL when optional. An enrollment cannot reference nonexistent Course_ID = 900.
• Referential actions: ON DELETE CASCADE removes dependent rows; ON DELETE RESTRICT rejects deletion when dependents exist; ON DELETE SET NULL preserves the dependent row when the relationship is optional.
• Assertion or trigger logic: Complex rules, such as “a lecturer cannot supervise more than five active projects,” may require a trigger or carefully designed application transaction.
• Constraint timing: Immediate constraints are checked during each statement; deferred constraints are checked at transaction commit, which is useful when several related rows are inserted together.

VI. Key Structures

Keys identify tuples and express how relations connect. A sound key design prevents duplicates and supports efficient referencing.

A. Types of Keys

Types of Keys differ according to whether they identify tuples, qualify as minimal identifiers, or connect relations.

• Superkey: Any attribute set that uniquely identifies a tuple. In STUDENT(Student_ID, Email, Name), {Student_ID, Name} is a superkey, although it contains unnecessary attributes.
• Candidate key: A minimal superkey. {Student_ID} and possibly {Email} are candidate keys if each is unique and neither can be reduced.
• Primary key: The candidate key selected as the main identifier. Student_ID is commonly chosen because it is stable and compact.
• Alternate key: A candidate key not selected as primary. If Email is unique but Student_ID is primary, Email is an alternate key and should usually receive a UNIQUE constraint.
• Composite key: A key containing multiple attributes. ENROLLS(Student_ID, Course_ID) identifies one student's registration in one course.
• Foreign key: An attribute or attribute set referencing another relation's candidate or primary key. EMPLOYEE(Department_ID) references DEPARTMENT(Department_ID).
• Natural key: A meaningful real-world value, such as a national identification number; it may be unsuitable if it changes or exposes sensitive information.
• Surrogate key: A generated identifier, such as an integer sequence or UUID, with no business meaning. It simplifies references but does not remove the need for business uniqueness constraints.
• Key selection criterion: Prefer a key that is unique, stable, minimal, non-null, and efficient for indexing. A composite key is appropriate when identity genuinely depends on a combination, as in a student-course enrollment.