Supertype: A general entity that has subtypes.
Subtype: A specific entity that inherits attributes from the supertype.
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.
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.
Used to determine which subtype an entity belongs to.
Example: A "Person_Type" column with values ‘Student’ or ‘Professor’ in a "Person" table.
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.
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.
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)
);
Eliminates Redundant Data – Saves storage space.
Ensures Data Integrity – Prevents inconsistent data due to update anomalies.
Complex Queries – Requires multiple joins for data retrieval.
Performance Overhead – The DBMS needs to process more joins, which can slow down queries.
Denormalization involves combining normalized tables into a single table for performance optimization in read-heavy databases.
Before (Normalized):
EMPLOYEE (EmployeeNumber, Name, Email, Dept_Name)
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
);
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;
A trigger is an automated database action executed when a specified event occurs.
BEFORE INSERT
AFTER INSERT
INSTEAD OF INSERT
BEFORE UPDATE
AFTER UPDATE
INSTEAD OF UPDATE
BEFORE DELETE
AFTER DELETE
INSTEAD OF DELETE
Enforce business rules.
Maintain audit logs.
Prevent invalid transactions.
A view is a virtual table derived from a query.
Simplifies complex queries.
Enhances security by restricting access to specific data.
CREATE VIEW EmployeePhoneView AS
SELECT LastName AS EmployeeLastName, FirstName AS EmployeeFirstName, OfficePhone AS EmployeePhone
FROM EMPLOYEE;
SELECT * FROM EmployeePhoneView;
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'
);
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.
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';
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.
Used to manipulate data in tables.
Main DML Statements:
SELECT – Retrieves data.
INSERT – Adds new rows.
UPDATE – Modifies existing rows.
DELETE – Removes rows.
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.
Defines the minimum number of entities required in a relationship.
Values:
0 (optional participation)
1 (mandatory participation)
✅ Eliminates redundant data.
✅ Reduces update anomalies.
❌ More complex queries.
❌ Can cause performance issues.
Definition: Joining data in normalized tables before storing it in a new, non-normalized table.
Example: Combining STUDENT and COURSE into ENROLLMENT.
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.
Required for many-to-many relationships.
Example: STUDENT and COURSE need an ENROLLMENT table.
CREATE TABLE ENROLLMENT (
StudentID INT,
CourseID INT,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID)
);