1.2 Entity Relationship Modeling & SQL Mapping

1.2 Entity Relationship Modeling & SQL Mapping

Before writing SQL statements to create tables and query data, a database professional must translate business requirements into a formal structural design. Entity Relationship (ER) Modeling is the industry-standard visual abstraction technique used to capture entities, their attributes, and the relationships connecting them.

For the Oracle 1Z0-071 examination, you must know how to interpret Entity Relationship Diagrams (ERDs), understand attribute and cardinality classifications, resolve complex relationships, and systematically map ERD components into Oracle SQL Data Definition Language (CREATE TABLE, PRIMARY KEY, FOREIGN KEY, NOT NULL, and UNIQUE).

---

The Data Modeling Lifecycle

Database design progresses through three distinct levels of abstraction:

+--------------------------------------------------------------------------+
| 1. CONCEPTUAL MODEL                                                      |
|    High-level business concepts, major entities, and broad relationships.|
|    Independent of software, hardware, and DBMS.                          |
+--------------------------------------------------------------------------+
                                     |
                                     v
+--------------------------------------------------------------------------+
| 2. LOGICAL MODEL                                                         |
|    Detailed entities, normalized attributes, primary/foreign keys,       |
|    relationship cardinality, and business rules. Relational, but DBMS-agnostic.|
+--------------------------------------------------------------------------+
                                     |
                                     v
+--------------------------------------------------------------------------+
| 3. PHYSICAL MODEL                                                        |
|    Target DBMS implementation (Oracle SQL DDL): exact datatypes          |
|    (VARCHAR2, NUMBER), tablespaces, storage clauses, and indexes.        |
+--------------------------------------------------------------------------+

---

Core ERD Components: Entities and Attributes

1. Entities: Strong vs. Weak

• Entity Type: A category of person, place, object, or event about which an organization stores data (e.g., EMPLOYEE, DEPARTMENT, CUSTOMER, INVOICE).
• Entity Instance: A specific, single occurrence of an entity type (e.g., Employee #101, 'Lex De Haan').
• Strong (Independent / Regular) Entity: An entity that exists independently of other entities and possesses its own intrinsic primary key (e.g., CUSTOMER with CUSTOMER_ID).
• Weak (Dependent) Entity: An entity whose existence depends on a parent entity and whose primary identifier incorporates the parent entity's primary key (e.g., DEPENDENT or ORDER_ITEM). In an ERD, the connection to a weak entity is known as an identifying relationship.

2. Attribute Classifications

Attributes represent properties or characteristics belonging to an entity. In ER modeling, attributes are categorized as follows:

Exam Watchout: Storing multi-valued attributes as comma-delimited strings (e.g., '555-1212, 555-9876') is a severe relational design violation. It violates First Normal Form, makes indexing impossible, and severely complicates SQL query filtering.

---

Relationships, Cardinality, and Modality

A relationship represents a business association between two or more entities.

1. Degree of Relationships

• Unary (Recursive / Self-Referencing): An entity is related to itself (e.g., An EMPLOYEE manages other EMPLOYEES).
• Binary: A relationship between two distinct entities (e.g., DEPARTMENT contains EMPLOYEES). This is the most common type.
• Ternary: A simultaneous relationship involving three distinct entities (e.g., PHYSICIAN prescribes DRUG to PATIENT).

2. Cardinality and Modality

Relationships are characterized by two boundary constraints:

           [Modality / Minimum]  ------+      +------ [Modality / Minimum]
                                      |      |
                                      v      v
            EMPLOYEE  |----------------(0)---<|  DEPARTMENT
                      ^                       ^
                      |                       |
[Cardinality / Maximum: 1]  -------------------+      +--- [Cardinality / Maximum: Many]

• Cardinality (Maximum Constraint): The maximum number of entity instances in Table B that can be associated with a single instance in Table A:
  • One-to-One (1:1): Each row in A relates to at most one row in B, and vice versa.
  • One-to-Many (1:N): Each row in A can relate to multiple rows in B, but a row in B relates to at most one row in A.
  • Many-to-Many (M:N): Each row in A can relate to multiple rows in B, and each row in B can relate to multiple rows in A.
• Modality / Optionality (Minimum Constraint): The minimum number of entity instances required in the relationship:
  • Optional (Modality = 0): An entity instance can exist without participating in the relationship (represented by an open circle O or dashed line).
  • Mandatory (Modality = 1): An entity instance must participate in the relationship (represented by a perpendicular stroke | or solid line).

---

ERD Notational Standards

In database literature and certification exams, three major notation formats are encountered:

1. Chen Notation

• Entities: Enclosed in rectangles.
• Attributes: Enclosed in ovals connected to their entity (primary keys are underlined).
• Relationships: Enclosed in diamonds with cardinality labels (1, N, M) on connecting lines.

2. Crow's Foot Notation (Information Engineering - IE)

• Entities: Displayed as boxes with attribute lists inside.
• Relationships: Connecting lines with standard endpoint symbols:

3. Barker's Notation (Oracle CASE Method)

• Developed by Richard Barker for Oracle Corporation; widely used in Oracle Designer and official Oracle curricula:
  • Mandatory Attributes: Prefixed with an asterisk (*).
  • Optional Attributes: Prefixed with a lowercase o.
  • Primary Key Attributes (UID): Prefixed with an octothorpe (#).
  • Relationship Lines: Solid line indicates mandatory side; dashed line indicates optional side. Crow's foot at the line endpoint indicates the "Many" side.

---

Resolving Many-to-Many (M:N) Relationships

A fundamental rule of relational database design is that Many-to-Many relationships cannot be implemented directly in a relational schema.

Consider STUDENTS and COURSES: A student enrolls in many courses, and a course contains many students. If you try to place COURSE_ID in STUDENTS, you cannot store multiple values without violating First Normal Form. If you place STUDENT_ID in COURSES, you face the same limitation.

CONCEPTUAL DESIGN (Direct M:N - Not implementable directly in SQL):
+----------+                        +----------+
| STUDENTS | }O------------------O{ | COURSES  |
+----------+                        +----------+

RESOLVED RELATIONAL DESIGN (Using an Associative Junction Table):
+----------+        +----------------------+        +----------+
| STUDENTS | 1    N |     ENROLLMENTS      | N    1 | COURSES  |
|----------|--------|----------------------|--------|----------|
| #STUD_ID |        | #* STUD_ID (FK)      |        | #* CRSE_ID|
| * NAME   |        | #* CRSE_ID (FK)      |        | * TITLE  |
+----------+        |  * ENROLL_DATE       |        +----------+
                    |  o FINAL_GRADE       |
                    +----------------------+

Resolution Strategy:

1. Create an Associative Entity (also called a Junction Table, Bridge Table, or Intersection Table).
2. Break the M:N relationship into two 1:N relationships:
   • STUDENTS (1) to ENROLLMENTS (N)
   • COURSES (1) to ENROLLMENTS (N)
3. Place the primary keys of both parent tables into the junction table as foreign keys.
4. The primary key of the junction table is typically a composite primary key formed by (STUD_ID, CRSE_ID), or a dedicated surrogate key with a UNIQUE(STUD_ID, CRSE_ID) constraint.

---

Systematic Mapping Rules from ERD to Oracle SQL DDL

Transforming an ERD into valid Oracle SQL DDL follows deterministic mapping rules:

                               ERD-TO-SQL MAPPING

     ERD Component                                 Oracle SQL Construct
+----------------------+                     +--------------------------------+
| Entity Type          | ==================> | CREATE TABLE                   |
| Attribute            | ==================> | Table Column + Datatype        |
| Mandatory Attribute  | ==================> | NOT NULL Constraint            |
| Primary UID          | ==================> | PRIMARY KEY Constraint         |
| Alternate UID        | ==================> | UNIQUE Constraint              |
| 1:N Relationship     | ==================> | FOREIGN KEY on 'Many' Table    |
| 1:1 Relationship     | ==================> | FOREIGN KEY with UNIQUE        |
| M:N Relationship     | ==================> | Junction Table with 2 FKs      |
+----------------------+                     +--------------------------------+

Comprehensive DDL Mapping Example:

-- 1. Parent Table: DEPARTMENTS (1-side)
CREATE TABLE departments (
    department_id   NUMBER(4)           CONSTRAINT dept_pk PRIMARY KEY,
    department_name VARCHAR2(30)        CONSTRAINT dept_name_nn NOT NULL
);

-- 2. Child Table: EMPLOYEES (N-side of 1:N with DEPARTMENTS)
CREATE TABLE employees (
    employee_id     NUMBER(6)           CONSTRAINT emp_pk PRIMARY KEY,
    first_name      VARCHAR2(20),
    last_name       VARCHAR2(25)        CONSTRAINT emp_last_name_nn NOT NULL,
    email           VARCHAR2(100)       CONSTRAINT emp_email_nn NOT NULL 
                                        CONSTRAINT emp_email_uk UNIQUE,
    hire_date       DATE                DEFAULT SYSDATE CONSTRAINT emp_hire_date_nn NOT NULL,
    department_id   NUMBER(4),
    -- 1:N Foreign Key mapped to Many side:
    CONSTRAINT emp_dept_fk FOREIGN KEY (department_id)
        REFERENCES departments (department_id)
);

-- 3. 1:1 Relationship: EMPLOYEE_BADGES (Each employee has at most 1 badge)
CREATE TABLE employee_badges (
    badge_id        NUMBER(8)           CONSTRAINT badge_pk PRIMARY KEY,
    badge_number    VARCHAR2(20)        CONSTRAINT badge_num_nn NOT NULL UNIQUE,
    issue_date      DATE                DEFAULT SYSDATE NOT NULL,
    employee_id     NUMBER(6)           CONSTRAINT badge_emp_nn NOT NULL,
    -- 1:1 Foreign Key requires UNIQUE constraint on the FK column:
    CONSTRAINT badge_emp_fk FOREIGN KEY (employee_id)
        REFERENCES employees (employee_id),
    CONSTRAINT badge_emp_uk UNIQUE (employee_id)
);

-- 4. Associative Junction Table: PROJECT_ASSIGNMENTS (Resolving M:N between EMPLOYEES and PROJECTS)
CREATE TABLE project_assignments (
    employee_id     NUMBER(6),
    project_id      NUMBER(6),
    assigned_date   DATE                DEFAULT SYSDATE NOT NULL,
    role_name       VARCHAR2(30),
    -- Composite Primary Key:
    CONSTRAINT proj_assign_pk PRIMARY KEY (employee_id, project_id),
    -- Foreign Key to EMPLOYEES:
    CONSTRAINT proj_assign_emp_fk FOREIGN KEY (employee_id)
        REFERENCES employees (employee_id) ON DELETE CASCADE,
    -- Foreign Key to PROJECTS:
    CONSTRAINT proj_assign_proj_fk FOREIGN KEY (project_id)
        REFERENCES projects (project_id) ON DELETE CASCADE
);

---

Common Exam Traps in ERD Mapping

1. Placing the Foreign Key on the Wrong Side: In a 1:N relationship (e.g., Department to Employees), the foreign key column must always go on the Many side (EMPLOYEES). Placing EMPLOYEE_ID in DEPARTMENTS would restrict a department to having only one employee.
2. Forgetting Uniqueness on 1:1 Relationships: In a 1:1 relationship, if you define a foreign key without a UNIQUE constraint, the database engine will treat it as a standard 1:N relationship.
3. Omitting NOT NULL on Mandatory Foreign Keys: If a business rule states that every employee must belong to a department (mandatory modality), the DEPARTMENT_ID foreign key column in EMPLOYEES must be explicitly declared as NOT NULL.