Part 2 of CS347

Exclusive vs. Inclusive Subtypes

• Supertype: A general entity that has subtypes.

• Subtype: A specific entity that inherits attributes from the supertype.

Exclusive Subtypes (Disjoint Subtypes)

• An entity must belong to only one subtype.

• Represented in E-R diagrams with disjoint constraints.

• Example:

  • A "Vehicle" can be either a "Car" or a "Motorcycle," but not both.

Inclusive Subtypes (Overlapping Subtypes)

• An entity can belong to multiple subtypes.

• Represented with overlapping constraints.

• Example:

  • An "Employee" can be both a "Manager" and a "Trainer" at the same time.

Discriminator Attribute

• Used to determine which subtype an entity belongs to.

• Example: A "Person_Type" column with values ‘Student’ or ‘Professor’ in a "Person" table.

Intersection (Bridge or Junction) Tables

• Used to resolve many-to-many (M:N) relationships.

• Breaks an M:N relationship into two 1:M relationships.

• Contains foreign keys referencing both tables in the relationship.

Example:

Consider a university system with STUDENT and COURSE tables.
A student can enroll in multiple courses, and a course can have multiple students.
To handle this M:N relationship, we use an ENROLLMENT table as a junction table.

Schema Example

CREATE TABLE ENROLLMENT (

    StudentID INT,

    CourseID INT,

    EnrollmentDate DATE,

    PRIMARY KEY (StudentID, CourseID),

    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),

    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)

);

Advantages & Disadvantages of Normalized Tables

Advantages

1. Eliminates Redundant Data – Saves storage space.

2. Ensures Data Integrity – Prevents inconsistent data due to update anomalies.

Disadvantages

1. Complex Queries – Requires multiple joins for data retrieval.

2. Performance Overhead – The DBMS needs to process more joins, which can slow down queries.

Denormalization and Schema Design

Denormalization involves combining normalized tables into a single table for performance optimization in read-heavy databases.

Example Schema:

Before (Normalized):

1. EMPLOYEE (EmployeeNumber, Name, Email, Dept_Name)

2. DEPARTMENT (Dept_Name, BudgetCode)

After (Denormalized EMPLOYEE_DEPARTMENT Table):

CREATE TABLE EMPLOYEE_DEPARTMENT (

    EmployeeNumber INT PRIMARY KEY,

    Name VARCHAR(255),

    Email VARCHAR(255),

    Dept_Name VARCHAR(255),

    BudgetCode INT

);

SQL to Populate the Denormalized Table

INSERT INTO EMPLOYEE_DEPARTMENT (EmployeeNumber, Name, Email, Dept_Name, BudgetCode)

SELECT e.EmployeeNumber, e.Name, e.Email, d.Dept_Name, d.BudgetCode

FROM EMPLOYEE e

JOIN DEPARTMENT d ON e.Dept_Name = d.Dept_Name;

SQL Triggers

Definition

• A trigger is an automated database action executed when a specified event occurs.

Types of SQL Triggers

1. BEFORE INSERT

2. AFTER INSERT

3. INSTEAD OF INSERT

4. BEFORE UPDATE

5. AFTER UPDATE

6. INSTEAD OF UPDATE

7. BEFORE DELETE

8. AFTER DELETE

9. INSTEAD OF DELETE

Use Cases

• Enforce business rules.

• Maintain audit logs.

• Prevent invalid transactions.

SQL Views

Definition

• A view is a virtual table derived from a query.

Advantages of Views

• Simplifies complex queries.

• Enhances security by restricting access to specific data.

Example: Creating a View

CREATE VIEW EmployeePhoneView AS

SELECT LastName AS EmployeeLastName, FirstName AS EmployeeFirstName, OfficePhone AS EmployeePhone

FROM EMPLOYEE;

Querying the View

SELECT * FROM EmployeePhoneView;

Understanding SQL Subqueries

Example Query

SELECT f.film_id, f.title, f.rating

FROM FILM f

WHERE f.film_id IN (

    SELECT fc.film_id

    FROM FILM_CATEGORY fc

    JOIN CATEGORY c ON fc.category_id = c.category_id

    WHERE c.name = 'Romance'

);

Explanation

• The subquery retrieves all film_ids where the category is 'Romance'.

• The outer query retrieves the film_id, title, and rating for these films.

• Alternative Approach: Use a JOIN instead of a subquery.

JOIN Alternative

SELECT f.film_id, f.title, f.rating

FROM FILM f

JOIN FILM_CATEGORY fc ON f.film_id = fc.film_id

JOIN CATEGORY c ON fc.category_id = c.category_id

WHERE c.name = 'Romance';

SQL DDL vs. DML

DDL (Data Definition Language)

• Used to define and modify database structures.

• Main DDL Statements:

  • CREATE TABLE – Creates a new table.

  • ALTER TABLE – Modifies a table.

  • DROP TABLE – Deletes a table.

  • TRUNCATE TABLE – Removes all rows from a table.

DML (Data Manipulation Language)

• Used to manipulate data in tables.

• Main DML Statements:

  • SELECT – Retrieves data.

  • INSERT – Adds new rows.

  • UPDATE – Modifies existing rows.

  • DELETE – Removes rows.

Cardinality in ER Models

Maximum Cardinality

• Defines the maximum number of entities in a relationship.

• Types:

  • One-to-One [1:1] – One entity maps to only one other entity.

  • One-to-Many [1:N] – One entity maps to multiple entities.

  • Many-to-Many [N:M] – Multiple entities map to multiple entities.

Minimum Cardinality

• Defines the minimum number of entities required in a relationship.

• Values:

  • 0 (optional participation)

  • 1 (mandatory participation)

Normalization vs. Denormalization

Advantages of Normalization

✅ Eliminates redundant data.
✅ Reduces update anomalies.

Disadvantages of Normalization

❌ More complex queries.
❌ Can cause performance issues.

Denormalization Process

• Definition: Joining data in normalized tables before storing it in a new, non-normalized table.

• Example: Combining STUDENT and COURSE into ENROLLMENT.

SQL Query Optimization

Using Joins Instead of Subqueries

Instead of:

SELECT * FROM FILM WHERE film_id IN (

    SELECT film_id FROM FILM_CATEGORY WHERE category_id = 3

);

Use:

SELECT f.* FROM FILM f

JOIN FILM_CATEGORY fc ON f.film_id = fc.film_id

WHERE fc.category_id = 3;

💡 Why? Joins are faster than subqueries because they allow better indexing and optimization.

Bridge (Junction) Tables

• Required for many-to-many relationships.

• Example: STUDENT and COURSE need an ENROLLMENT table.

SQL Example

CREATE TABLE ENROLLMENT (

    StudentID INT,

    CourseID INT,

    PRIMARY KEY (StudentID, CourseID),

    FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),

    FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)

);