Mapping ER Model to Relational Model in DBMS

Detailed explanation with Oracle SQL examples for entities, attributes, relationships, and EER concepts.

1. Mapping Entities

a) Strong Entity

Create a table with all attributes and set primary key.

CREATE TABLE Student (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    dob DATE,
    course VARCHAR2(50)
);

b) Weak Entity

Include foreign key referencing owner entity and partial key as primary key.

CREATE TABLE Dependent (
    dep_name VARCHAR2(50),
    emp_id NUMBER,
    PRIMARY KEY(dep_name, emp_id),
    FOREIGN KEY(emp_id) REFERENCES Employee(emp_id)
);

2. Mapping Attributes

Simple Attribute: Column in table (e.g., name, dob)
Composite Attribute: Break into individual columns (FullName → FirstName, LastName)
Multi-valued Attribute: Separate table with FK

CREATE TABLE StudentPhone (
    student_id NUMBER,
    phone_number VARCHAR2(15),
    PRIMARY KEY(student_id, phone_number),
    FOREIGN KEY(student_id) REFERENCES Student(student_id)
);

3. Mapping Relationships

a) One-to-One (1:1)

CREATE TABLE CompanyCar (
    car_id NUMBER PRIMARY KEY,
    car_model VARCHAR2(50),
    emp_id NUMBER UNIQUE,
    FOREIGN KEY(emp_id) REFERENCES Employee(emp_id)
);

b) One-to-Many (1:N)

ALTER TABLE Student ADD teacher_id NUMBER;
ALTER TABLE Student ADD FOREIGN KEY (teacher_id) REFERENCES Teacher(teacher_id);

c) Many-to-Many (M:N)

CREATE TABLE Enrollment (
    student_id NUMBER,
    course_id NUMBER,
    PRIMARY KEY(student_id, course_id),
    FOREIGN KEY(student_id) REFERENCES Student(student_id),
    FOREIGN KEY(course_id) REFERENCES Course(course_id)
);

4. Mapping Specialization / Generalization

CREATE TABLE Employee (
    emp_id NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    salary NUMBER
);

CREATE TABLE Teacher (
    emp_id NUMBER PRIMARY KEY,
    subject VARCHAR2(50),
    department VARCHAR2(50),
    FOREIGN KEY(emp_id) REFERENCES Employee(emp_id)
);

CREATE TABLE Staff (
    emp_id NUMBER PRIMARY KEY,
    role VARCHAR2(50),
    work_shift VARCHAR2(50),
    FOREIGN KEY(emp_id) REFERENCES Employee(emp_id)
);

5. Summary Table

ER Concept	Relational Mapping
Strong Entity	Table with all attributes and primary key
Weak Entity	Table with partial key + foreign key of owner
Simple Attribute	Column in table
Composite Attribute	Break into individual columns
Multi-valued Attribute	Separate table with foreign key
1:1 Relationship	Primary key of one side as foreign key in other table
1:N Relationship	Primary key of “one” side as foreign key in “many” side
M:N Relationship	Separate table with foreign keys from both entities
Specialization/Generalization	Superclass table + subclass tables with FK to superclass

Trust Coding : Programming