🔄 ER to Relational Mapping & DDL

Transforming Conceptual Designs into Logical Database Schemas with SQL DDL

📚 Chapter 5
🎯 CS 340
👩‍🎓 Shoug Alomran
⏱️ Comprehensive Summary

📑 Table of Contents

🎯 Overview
1️⃣ Regular Entities
2️⃣ Weak Entities
3️⃣ Binary 1:1
4️⃣ Binary 1:N
5️⃣ Binary M:N
6️⃣ Multivalued Attributes
7️⃣ N-ary Relationships
8️⃣ Specialization/Generalization
9️⃣ Union Types
💻 DDL Basics
🎯

ER-to-Relational Mapping Overview

📘 What is Mapping?

ER-to-Relational Mapping is the systematic process of transforming a conceptual ER (or EER) diagram into a logical relational database schema that can be implemented in a DBMS.

🎯 The 9-Step Algorithm

Steps 1-7: Map ER Model components

  • Steps 1-2: Entity types (regular and weak)
  • Steps 3-5: Binary relationships (1:1, 1:N, M:N)
  • Step 6: Multivalued attributes
  • Step 7: N-ary relationships

Steps 8-9: Map EER Model constructs

  • Step 8: Specialization/Generalization
  • Step 9: Union types (Categories)
1️⃣

Step 1: Mapping Regular Entity Types

1

Regular (Strong) Entities

📋 Algorithm

For each regular entity type E in the ER schema:

  1. Create a relation R
  2. Include all simple attributes of E as attributes of R
  3. Choose one key attribute of E as the primary key for R
  4. If the key is composite, the simple attributes that form it together become the primary key
💡 Example

ER Entity: EMPLOYEE with attributes {SSN, Name, BDate, Address}

Relational Schema:

EMPLOYEE(SSN, Name, BDate, Address)

SSN is chosen as the primary key (underlined)

📌 Note on Composite Attributes

For composite attributes, include only the simple component attributes. For example, if Name is composite (Fname, Minit, Lname), include Fname, Minit, and Lname as separate attributes.

2️⃣

Step 2: Mapping Weak Entity Types

2

Weak Entities

📋 Algorithm

For each weak entity type W with owner entity type E:

  1. Create a relation R
  2. Include all simple attributes of W as attributes of R
  3. Include as foreign key the primary key of the owner entity relation
  4. The primary key of R is the combination of:
    • The owner's primary key(s)
    • The partial key of W (if any)
💡 Example: DEPENDENT

Weak Entity: DEPENDENT (partial key: Dependent_name)

Owner Entity: EMPLOYEE (primary key: SSN)

Relational Schema:

DEPENDENT(ESSN, Dependent_name, Sex, BDate, Relationship) FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN)

Primary key is {ESSN, Dependent_name}

⚠️ Critical Point

The owner's primary key becomes both a foreign key AND part of the primary key of the weak entity relation.

3️⃣

Step 3: Mapping Binary 1:1 Relationships

3

Binary 1:1 Relationships

📋 Three Approaches

For each binary 1:1 relationship type R between S and T:

🔹 Approach 1: Foreign Key (Most Common)

Choose one relation (preferably with total participation) and include the other's primary key as a foreign key.

Example: EMPLOYEE MANAGES DEPARTMENT (1:1)

DEPARTMENT(Dnumber, Dname, Mgr_ssn, Mgr_start_date) FOREIGN KEY (Mgr_ssn) REFERENCES EMPLOYEE(SSN)

Mgr_ssn is the foreign key in DEPARTMENT

🔹 Approach 2: Merged Relation

If both entities have total participation, merge them into a single relation with attributes from both.

🔹 Approach 3: Relationship Relation

Create a separate relation for the relationship with foreign keys to both entities. (Rarely used for 1:1)

💡 Choosing the Best Approach

Use Foreign Key approach when one side has total participation. Place the foreign key in the relation with total participation to avoid NULL values.

4️⃣

Step 4: Mapping Binary 1:N Relationships

4

Binary 1:N Relationships

📋 Algorithm

For each binary 1:N relationship type R:

  1. Identify relation S representing the entity type at the N-side
  2. Include as foreign key in S the primary key of relation T (the 1-side)
  3. Include any simple attributes of the relationship as attributes of S
💡 Example: WORKS_FOR

Relationship: EMPLOYEE (N) WORKS_FOR (1) DEPARTMENT

Relational Schema:

EMPLOYEE(SSN, Name, BDate, Address, DNO) FOREIGN KEY (DNO) REFERENCES DEPARTMENT(Dnumber) DEPARTMENT(Dnumber, Dname, Mgr_ssn)

DNO is foreign key in EMPLOYEE (the N-side)

🎯 Key Rule

The foreign key always goes in the relation on the N-side (many side) of the relationship.

📌 Alternative Approach

You could create a separate relationship relation, but this is rarely done for 1:N relationships as it creates unnecessary complexity and has little benefit.

5️⃣

Step 5: Mapping Binary M:N Relationships

5

Binary M:N Relationships

📋 Algorithm

For each binary M:N relationship type R:

  1. Create a new relation S (relationship relation)
  2. Include as foreign keys the primary keys of both participating entity relations
  3. Their combination forms the primary key of S
  4. Include any simple attributes of the M:N relationship as attributes of S
💡 Example: WORKS_ON

Relationship: EMPLOYEE (M) WORKS_ON (N) PROJECT

Relational Schema:

WORKS_ON(ESSN, PNO, Hours) FOREIGN KEY (ESSN) REFERENCES EMPLOYEE(SSN) FOREIGN KEY (PNO) REFERENCES PROJECT(Pnumber)

Primary key is {ESSN, PNO}
Hours is an attribute of the relationship

⚠️ Critical Point

M:N relationships always require a separate relationship relation. You cannot use foreign keys in either participating entity relation because that would violate the relational model (multivalued attributes).

6️⃣

Step 6: Mapping Multivalued Attributes

6

Multivalued Attributes

📋 Algorithm

For each multivalued attribute A:

  1. Create a new relation R
  2. Include attribute A in R
  3. Include as foreign key K the primary key of the relation representing the entity/relationship that has A
  4. The primary key of R is the combination {A, K}
  5. If A is composite, include its simple components
💡 Example: DEPT_LOCATIONS

Multivalued Attribute: LOCATIONS of DEPARTMENT

Relational Schema:

DEPARTMENT(Dnumber, Dname, Mgr_ssn) DEPT_LOCATIONS(Dnumber, Dlocation) FOREIGN KEY (Dnumber) REFERENCES DEPARTMENT(Dnumber)

Primary key is {Dnumber, Dlocation}

🎯 Why a Separate Relation?

The relational model requires atomic values (First Normal Form). Multivalued attributes violate this, so they must be stored in a separate relation.

7️⃣

Step 7: Mapping N-ary Relationships

7

N-ary Relationships (n > 2)

📋 Algorithm

For each n-ary relationship type R (where n > 2):

  1. Create a new relation S
  2. Include as foreign keys the primary keys of ALL participating entity relations
  3. The primary key of S is usually the combination of all foreign keys
  4. Include any simple attributes of R as attributes of S
💡 Example: SUPPLY (Ternary Relationship)

Relationship: SUPPLIER supplies PART to PROJECT

Relational Schema:

SUPPLY(Sname, Part_no, Proj_name, Quantity) FOREIGN KEY (Sname) REFERENCES SUPPLIER(Sname) FOREIGN KEY (Part_no) REFERENCES PART(Part_no) FOREIGN KEY (Proj_name) REFERENCES PROJECT(Proj_name)

Primary key is {Sname, Part_no, Proj_name}

📌 Cardinality Considerations

If the relationship has cardinality constraints (e.g., M:N:1), the primary key may be just the combination of the foreign keys on the M and N sides, not all foreign keys.

8️⃣

Step 8: Mapping Specialization/Generalization

8

Specialization/Generalization Mapping

📋 Four Mapping Options

For specialization/generalization hierarchies, choose from:

🔹 Option 1: Multiple Relations (Superclass + Subclasses)

Create a relation for the superclass and separate relations for each subclass.

  • Superclass relation contains all common attributes + PK
  • Each subclass relation contains PK (as FK) + specific attributes
  • Best for disjoint specializations
🔹 Option 2: Multiple Relations (Subclasses Only)

Create relations only for subclasses, each containing all inherited + specific attributes.

  • No separate superclass relation
  • Each subclass includes all superclass attributes
  • Best for total, disjoint specializations
🔹 Option 3: Single Relation with Type Attribute

Create a single relation with all attributes from superclass and all subclasses.

  • Add a type attribute to distinguish entity types
  • Subclass-specific attributes may be NULL for non-applicable entities
  • Best for overlapping specializations
🔹 Option 4: Single Relation with Multiple Type Attributes

Single relation with multiple Boolean type attributes (one per subclass).

  • Each Boolean indicates membership in a subclass
  • Best for overlapping specializations where entities can belong to multiple subclasses
💡 Choosing the Right Option
  • Disjoint + queries need all entities: Option 1
  • Total + Disjoint: Option 2
  • Overlapping: Option 3 or 4
9️⃣

Step 9: Mapping Union Types (Categories)

9

Union Types (Categories)

📋 Algorithm

For each category (union type):

  1. Create a relation for the category
  2. Include a surrogate key as primary key
  3. Include this surrogate key as foreign key in each superclass relation
  4. The surrogate key links category instances to their corresponding superclass instances
💡 Example: OWNER Category

Category: OWNER (union of PERSON, BANK, COMPANY)

Relational Schema:

OWNER(Owner_id, Owner_type, ...) PERSON(SSN, Name, Owner_id) BANK(Bank_code, Name, Owner_id) COMPANY(Reg_no, Name, Owner_id)

Owner_id is the surrogate key linking to specific superclass

🎯 Why Surrogate Keys?

Categories have superclasses with different key attributes. A surrogate key provides a uniform way to reference category members regardless of which superclass they belong to.

💻

DDL (Data Definition Language) Basics

📘 What is DDL?

Data Definition Language (DDL) is used to define, modify, and delete database structures. Key DDL commands: CREATE, ALTER, DROP.

CREATE TABLE

📝 Basic Syntax
CREATE TABLE table_name ( column_1 datatype, column_2 datatype, column_3 datatype, ... );
💡 Example: Creating CUSTOMER Table
CREATE TABLE CUSTOMER ( Customer_id NUMBER(4), CustomerName VARCHAR(30), CustomerAddress VARCHAR(50) );

Primary Keys

🔑 Defining Primary Keys
CREATE TABLE CUSTOMER ( Customer_id NUMBER(4) NOT NULL, CustomerName VARCHAR(30) NOT NULL, CustomerAddress VARCHAR(50), PRIMARY KEY (Customer_id) );

Composite Keys

🔗 Composite Primary Keys
CREATE TABLE Persons ( ID INT NOT NULL, LastName VARCHAR(255) NOT NULL, FirstName VARCHAR(255), Age INT, CONSTRAINT PK_Person PRIMARY KEY (ID, LastName) );

Foreign Keys

🔗 Defining Foreign Keys
CREATE TABLE Orders ( OrderID INT NOT NULL, OrderNumber INT NOT NULL, PersonID INT, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); -- Or with named constraint: CREATE TABLE Orders ( OrderID INT NOT NULL, OrderNumber INT NOT NULL, PersonID INT, PRIMARY KEY (OrderID), CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

DDL Constraints

⚙️ Common Constraints

Attribute Constraints:

  • NOT NULL: Column must have a value
  • UNIQUE: Column value must be unique in table
  • CHECK: Column value must satisfy a condition
  • DEFAULT: Provides default value if none specified

Foreign Key Conditions:

  • ON DELETE CASCADE: Delete referencing rows when referenced row is deleted
  • ON UPDATE CASCADE: Update foreign key when primary key is updated
  • ON DELETE SET NULL: Set foreign key to NULL when referenced row is deleted
💡 Complete Example: DEPENDENT Table
CREATE TABLE Employee ( ID INT NOT NULL, EmployeeName VARCHAR(255) NOT NULL, PRIMARY KEY (ID) ); CREATE TABLE DEPENDENT ( Name VARCHAR(255) NOT NULL, EmployeeID INT NOT NULL, DateOfBirth DATE, Gender VARCHAR(1) DEFAULT 'F', CONSTRAINT PK_Dependent PRIMARY KEY (EmployeeID, Name), FOREIGN KEY (EmployeeID) REFERENCES Employee(ID) );

ALTER and DROP

🔧 Modifying and Deleting Tables
-- ALTER: Add or modify columns ALTER TABLE Employee ADD Email VARCHAR(100); ALTER TABLE Employee MODIFY Salary NUMBER(10,2); -- DROP: Delete table DROP TABLE DEPENDENT; -- TRUNCATE: Remove all rows but keep structure TRUNCATE TABLE Orders; -- RENAME: Change table name RENAME TABLE Old_Name TO New_Name;
⚠️ Best Practices
  • Always define primary keys explicitly
  • Use meaningful constraint names for easier maintenance
  • Define NOT NULL for essential attributes
  • Specify ON DELETE/UPDATE actions for foreign keys
  • Use appropriate data types and sizes
✨

Chapter Summary

🎯 Key Takeaways
  • Steps 1-2: Entity mapping creates basic relations (regular and weak entities)
  • Steps 3-5: Relationship mapping depends on cardinality (1:1, 1:N, M:N)
  • Step 6: Multivalued attributes require separate relations
  • Step 7: N-ary relationships need relationship relations
  • Steps 8-9: EER constructs have multiple mapping options
  • DDL: SQL commands translate logical schemas into physical databases
🎓 Next Steps

With ER-to-Relational mapping complete, you can now:

  • Transform any ER/EER diagram into relational schemas
  • Use DDL to implement schemas in actual databases
  • Write SQL queries to manipulate and retrieve data
  • Understand normalization for optimal schema design
Shoug Alomran
CS 340: Introduction to Database Systems
Chapter 5 - ER-to-Relational Mapping & DDL
📚 Comprehensive Study Guide