ER-to-Relational Mapping & Constraints: Rules, Examples, and Real-World DB Design

TAKEAWAYS:

• ER-to-relational mapping converts entities into tables and relationships into foreign keys, with special rules for 1:N, N:M, and weak entities.
• Domain constraints restrict data types/values (e.g., Salary > 0), while referential integrity ensures foreign keys match primary keys.
• A DELETE violating referential integrity triggers cascading actions (restrict, set null, set default, or cascade).
• Weak entities need a discriminator (e.g., OrderID in OrderItem) and a partial key.
• N:M relationships require a junction table (e.g., Enrollment between Student and Course).
• Real-world examples: eSewa’s transaction logs (constraints), Daraz’s order-item tracking (N:M mapping), and NTC’s network equipment (foreign keys).

---

1. ER-to-Relational Mapping: Core Rules

ER diagrams model real-world entities and relationships, but databases use tables (relations). Mapping follows strict rules:

1.1 Mapping Entities to Relations

• Each entity → One relation (table).
  • Attributes become columns; entity name becomes the relation name.
  • Primary key (PK) of the entity → Primary key of the relation.
• Example:

  erDiagram
      STUDENT ||--o{ ENROLLMENT : takes
      COURSE ||--o{ ENROLLMENT : offers

  → Maps to:

  CREATE TABLE Student (SID INT PRIMARY KEY, SName VARCHAR(50));
  CREATE TABLE Course (CID INT PRIMARY KEY, CName VARCHAR(50));
  CREATE TABLE Enrollment (SID INT, CID INT, Grade VARCHAR(2), PRIMARY KEY (SID, CID),
                           FOREIGN KEY (SID) REFERENCES Student(SID),
                           FOREIGN KEY (CID) REFERENCES Course(CID));
  

1.2 Mapping Relationships

Visual: Weak Entity Mapping

→ ORDERITEM(OrderID, ItemID, Quantity) where OrderID is a foreign key to ORDER(OrderID).

---

2. Database Constraints: Enforcing Rules

Constraints ensure data integrity. The three main types:

2.1 Domain Constraints

• Define valid values for attributes (e.g., Salary > 0, Email LIKE '%@gmail.com').
• Example:

  CREATE TABLE Employee (
      Salary DECIMAL(10,2) CHECK (Salary > 0),
      Email VARCHAR(100) CHECK (Email LIKE '%@gmail.com')
  );
  

  Violation:

  INSERT INTO Employee (Salary, Email) VALUES (-5000, 'test@example.com'); -- Fails CHECK.
  

2.2 Key Constraints

Visual: Key Constraints in Action

• SID is PK; if City were a PK, it would violate uniqueness (e.g., two students in Kathmandu).

2.3 Referential Integrity Constraints

• Ensures foreign keys reference valid primary keys.
• Actions on violation (when deleting/updating a PK):
  1. RESTRICT: Reject the operation (default).
  2. SET NULL: Set FK to NULL.
  3. SET DEFAULT: Set FK to a default value.
  4. CASCADE: Delete/update FK rows automatically.

Example: CASCADE DELETE

CREATE TABLE Department (
    Dnumber INT PRIMARY KEY,
    Dname VARCHAR(50)
);
CREATE TABLE Employee (
    Ssn INT PRIMARY KEY,
    Dno INT,
    FOREIGN KEY (Dno) REFERENCES Department(Dnumber) ON DELETE CASCADE
);

• Deleting Department(Dnumber=10) automatically deletes all employees in that department.

Violation Example:

DELETE FROM Department WHERE Dnumber = 10; -- CASCADE deletes matching Employee rows.

---

3. Special Cases in Mapping

3.1 Weak Entities

• Definition: Exist only because of a strong entity (e.g., OrderItem depends on Order).
• Mapping:
  • Add discriminator (FK to strong entity) + partial key (unique identifier for weak entity).
  • Example: OrderItem(OrderID, ItemID, Quantity) where:
    • OrderID is FK to Order(OrderID).
    • (OrderID, ItemID) is PK (composite).
    • ItemID is the partial key.

Real-World Tie: Daraz Order Tracking

• Problem: Daraz needs to track which items are in an order.
• Solution: OrderItem(OrderID, ProductID, Quantity, Price) where:
  • OrderID links to Order(OrderID, CustomerID, Date).
  • ProductID links to Product(ProductID, Name, Stock).
  • Constraint: Quantity <= Stock (domain constraint).

3.2 Subtypes and Specialization

• Definition: An entity with subcategories (e.g., Employee → Professor or Lecturer).
• Mapping:
  1. Single-table inheritance: All attributes in one table (rare).
  2. Separate tables: Subtypes get their own tables with FK to the parent.
     • Example:

       CREATE TABLE Employee (Ssn INT PRIMARY KEY, Name VARCHAR(50), Type VARCHAR(10));
       CREATE TABLE Professor (Ssn INT PRIMARY KEY, Department VARCHAR(50),
                              FOREIGN KEY (Ssn) REFERENCES Employee(Ssn));
       

       • Type in Employee distinguishes Professor/Lecturer.

Real-World Tie: eSewa Transaction Logs

• Problem: eSewa needs to track types of transactions (e.g., Payment, Refund).
• Solution:
  • Transaction(TransactionID, Amount, Type) where Type is Payment/Refund.
  • Constraint: Amount > 0 for Payment; Amount < 0 for Refund (domain constraint).

---

4. Constraint Violations and Handling

Worked Example: NTC Network Equipment

• Scenario: NTC tracks routers and their ports.
• ER Diagram:
• SQL with Constraints:

  CREATE TABLE Router (RouterID INT PRIMARY KEY, Model VARCHAR(50));
  CREATE TABLE Port (
      PortID INT PRIMARY KEY,
      RouterID INT,
      Type VARCHAR(20),
      FOREIGN KEY (RouterID) REFERENCES Router(RouterID) ON DELETE CASCADE
  );
  

• Violation Handling:
  • If Router(RouterID=101) is deleted, all its Port rows are cascaded deleted.
  • If Port(RouterID=999) is inserted, it fails (referential integrity).

---

5. Exam Tip: How This Unit is Tested

1. Mapping Questions (30%):

   • Given an ER diagram, draw the relational schema (tables, PKs, FKs).
   • Common pitfalls:
     • Forgetting to add foreign keys for 1:N/N:M.
     • Not including discriminators for weak entities.
   • Example Question:

     Given: STUDENT ||--o{ ENROLLMENT : takes ||--o{ COURSE
     Write the SQL for Enrollment.
     

     Answer:

     CREATE TABLE Enrollment (
         SID INT,
         CID INT,
         Grade VARCHAR(2),
         PRIMARY KEY (SID, CID),
         FOREIGN KEY (SID) REFERENCES Student(SID),
         FOREIGN KEY (CID) REFERENCES Course(CID)
     );
     

2. Constraint Questions (25%):

   • Define domain, key, and referential integrity constraints.
   • Explain what happens when a DELETE violates referential integrity (e.g., "Cascade deletes dependent rows").
   • Example Question:

     What is a referential integrity constraint? Explain with an example where deleting a department would violate it.
     

     Answer:

     Referential integrity ensures FKs reference valid PKs. Example: Deleting Department(Dnumber=5) would violate FKs in Employee(Dno=5). To fix, use ON DELETE CASCADE or SET NULL.

3. Real-World Application (20%):

   • Link concepts to Nepali apps (e.g., Daraz, eSewa, Pathao).
   • Example:

     Pathao’s driver-ride assignments use an N:M relationship (Driver ↔ Ride) mapped to a junction table DriverRide(DriverID, RideID, Status).

4. SQL Implementation (25%):

   • Write CREATE TABLE statements with constraints.
   • Example Question:

     Create a table for `BankAccount` with:
     - PK: `AccountNumber`
     - FK: `CustomerID` referencing `Customer(CustomerID)`
     - Domain: `Balance >= 0`
     

     Answer:

     CREATE TABLE Customer (CustomerID INT PRIMARY KEY, Name VARCHAR(100));
     CREATE TABLE BankAccount (
         AccountNumber INT PRIMARY KEY,
         CustomerID INT,
         Balance DECIMAL(10,2) CHECK (Balance >= 0),
         FOREIGN KEY (CustomerID) REFERENCES Customer(CustomerID)
     );
     

---