Database Cheat Sheet

⚛️ ACID Properties

🧠 Memory Hack: "All Cars In Danger" → Atomicity, Consistency, Isolation, Durability

Atomicity (All or Nothing)

Definition: Transaction is treated as a single unit - either all operations succeed or all fail.

Example: Money Transfer

Account X: $500, Account Y: $200
Transfer $100 from X to Y:
T1: X = X - 100 (X becomes 400)
T2: Y = Y + 100 (Y becomes 300)

If T1 succeeds but T2 fails → Transaction is ABORTED
Both accounts return to original state (X=$500, Y=$200)

💡 Tip: Think of atomicity as an "undo" button - if anything goes wrong, the database goes back to its previous state.

Consistency (Integrity Constraints Maintained)

Definition: Database must remain in a valid state before and after transaction.

Example: Continuing Money Transfer

Before: X($500) + Y($200) = $700
After: X($400) + Y($300) = $700
✓ Total is maintained → Database is CONSISTENT

Isolation (Transactions Independent)

Definition: Concurrent transactions don't interfere with each other.

Example: Problem Without Isolation

X = $500, Y = $500
Transaction T: X = X * 100; Y = Y + X
Transaction T'': Sum = X + Y

If T'' reads X after T updates it but before T updates Y:
T'' reads: X = $50,000, Y = $500 → Sum = $50,500 ❌
Correct sum should be: X = $50,000, Y = $50,500 → Sum = $100,500 ✓

💡 Tip: Isolation prevents "dirty reads" - reading data that's in the middle of being changed.

Durability (Changes Persist)

Definition: Once a transaction commits, changes are permanent even if system crashes.

🧠 Memory Hack: Durability = "Durable like a diamond" - once committed, it's forever!

🎨 Database Design Process

🧠 Memory Hack: "Charlie Loves Onions" → Conceptual, Logical, Optimization

1. Conceptual Design (ER Diagrams)

Draw Entity-Relationship diagrams showing entities, attributes, and relationships.

Example: University Database

Entities: STUDENT, COURSE, DEPARTMENT
Relationships: Student ENROLLS-IN Course (M:N)
Attributes: Student(ID, Name, DOB, Major)
Course(CourseNo, Title, Credits)

2. Logical Design (Relational Model)

Convert ER diagrams to relational schemas (tables).

Example: Converting to Relations

STUDENT(StudentID, Name, DOB, Major)
COURSE(CourseNo, Title, Credits)
ENROLLS(StudentID, CourseNo, Grade) -- Junction table for M:N

3. Optimization (Normalization)

Remove redundancy and anomalies through normal forms (1NF, 2NF, 3NF, BCNF).

💡 Tip: Think of normalization as "organizing your closet" - everything has its proper place!

🔷 ER Diagram Components

Entities (Rectangle)

Real-world objects distinguishable from other objects.

Example: Types of Entities

Strong Entity: EMPLOYEE (has its own key: SSN)
Weak Entity: DEPENDENT (depends on EMPLOYEE, identified by combination of employee SSN + dependent name)

Attributes (Oval)

Type	Description	Example
Simple	Single atomic value	Age, SSN
Composite	Can be divided into parts	Address(Street, City, Zip)
Multivalued	Multiple values allowed	Phone_Numbers = {123, 456, 789}
Derived	Calculated from other attributes	Age (derived from BirthDate)

🧠 Memory Hack: "Some Cats Make Dinner" → Simple, Composite, Multivalued, Derived

Relationships (Diamond)

Cardinality Constraints:

Example: All Cardinality Types

1:1 (One-to-One):
EMPLOYEE manages DEPARTMENT
Each employee manages ≤ 1 department, each department has ≤ 1 manager

1:N (One-to-Many):
DEPARTMENT has EMPLOYEE
Each department has many employees, each employee works for 1 department

M:N (Many-to-Many):
EMPLOYEE works-on PROJECT
Each employee works on many projects, each project has many employees

💡 Tip: For M:N relationships, always create a junction table in the relational model!

Participation Constraints:

Total Participation (Double line): Every entity must participate. Example: Every EMPLOYEE must work for a DEPARTMENT.
Partial Participation (Single line): Not all entities need to participate. Example: Not all EMPLOYEEs manage a DEPARTMENT.

🧠 Memory Hack: "Total = Two lines, Partial = One line"

🔑 Keys in Databases

Types of Keys

Key Type	Definition	Example
Superkey	Any set of attributes that uniquely identifies a tuple	{SSN}, {SSN, Name}, {SSN, Age}
Candidate Key	Minimal superkey (no redundant attributes)	{SSN}, {EmployeeID}
Primary Key	Chosen candidate key (underlined)	SSN
Foreign Key	References primary key in another table	Dno in EMPLOYEE references Dnumber in DEPARTMENT
Partial Key	Part of key for weak entity (dashed underline)	Dependent_Name in DEPENDENT

🧠 Memory Hack: "Super Can Primary Foreign Partially" for the order of key types!

Example: Finding Keys

CAR(State, RegNo, SerialNo, Make, Model, Year)

Candidate Keys:
1. {State, RegNo} - License plate is unique per state
2. {SerialNo} - VIN is globally unique

Superkeys:
{SerialNo}, {State, RegNo}, {SerialNo, Make}, {State, RegNo, Model}, etc.

Primary Key: Choose {SerialNo} (smaller)

🌳 Enhanced ER (EER) Model

Specialization/Generalization

Example: Employee Specialization

Superclass: EMPLOYEE(SSN, Name, Salary)
Subclasses:
- SECRETARY(TypingSpeed)
- ENGINEER(EngType)
- TECHNICIAN(TGrade)

A Secretary IS-A Employee (inherits all attributes)

Constraints on Specialization

Constraint	Symbol	Meaning	Example
Disjoint	d	Entity can belong to at most ONE subclass	STUDENT is either Undergraduate OR Graduate (not both)
Overlapping	o	Entity can belong to MULTIPLE subclasses	EMPLOYEE can be both ENGINEER and MANAGER
Total	Double line	Every entity MUST belong to a subclass	Every VEHICLE is either CAR or TRUCK
Partial	Single line	Entity MAY NOT belong to any subclass	Not all EMPLOYEEs are MANAGERS

🧠 Memory Hack: "DOPT" → Disjoint/Overlapping, Partial/Total

Categories (Union Types)

Subclass with multiple superclasses from different entity types.

Example: Vehicle Owner

OWNER is a category of {PERSON, COMPANY, BANK}
An owner can be a person OR company OR bank
(Subset of the UNION, not intersection)

💡 Tip: Category = Union (U symbol), Shared Subclass = Intersection

🔄 ER to Relational Mapping

🧠 Memory Hack: "Really Smart Owls Often Make Mountains Nearly Spectacular" → 7 mapping steps

Step 1: Regular Entity Types

Create a table for each strong entity with all simple attributes.

Example:

ER: EMPLOYEE(SSN, Name, Address, Salary)
Relational: EMPLOYEE(SSN, Name, Address, Salary)

Step 2: Weak Entity Types

Include owner's primary key as foreign key. Combine with partial key for primary key.

Example:

DEPENDENT weak entity depends on EMPLOYEE
DEPENDENT(ESSN, Dependent_Name, Sex, BirthDate)
FK: ESSN references EMPLOYEE(SSN)

Step 3: Binary 1:1 Relationships

Three approaches:

Example: EMPLOYEE manages DEPARTMENT

Approach 1 - Foreign Key (Best for total participation):
DEPARTMENT(Dnumber, Dname, Mgr_SSN, Start_Date)
FK: Mgr_SSN references EMPLOYEE(SSN)

Approach 2 - Merged Relation (For both total):
EMPLOYEE_DEPT(SSN, Name, Dnumber, Dname, Start_Date)

Approach 3 - Relationship Relation (For both partial):
EMPLOYEE(SSN, Name)
DEPARTMENT(Dnumber, Dname)
MANAGES(SSN, Dnumber, Start_Date)

💡 Tip: For 1:1, put foreign key in the table with total participation to avoid NULLs!

Step 4: Binary 1:N Relationships

Put foreign key on the N side.

Example: DEPARTMENT has EMPLOYEEs

DEPARTMENT(Dnumber, Dname)
EMPLOYEE(SSN, Name, Dno)
FK: Dno references DEPARTMENT(Dnumber)

⚠️ Warning: NEVER put the foreign key on the 1 side - it would create multivalued attributes!

Step 5: Binary M:N Relationships

Create a new junction/relationship table with both primary keys as foreign keys.

Example: EMPLOYEE works-on PROJECT

EMPLOYEE(SSN, Name)
PROJECT(PNumber, PName)
WORKS_ON(SSN, PNumber, Hours)
FK: SSN references EMPLOYEE(SSN)
FK: PNumber references PROJECT(PNumber)

Step 6: Multivalued Attributes

Create separate table with primary key of parent + multivalued attribute.

Example: DEPARTMENT Locations

DEPARTMENT(Dnumber, Dname)
DEPT_LOCATIONS(Dnumber, Location)
FK: Dnumber references DEPARTMENT(Dnumber)

Step 7: N-ary Relationships (n > 2)

Create table with all participating entity primary keys as foreign keys.

Example: SUPPLIER supplies PART to PROJECT

SUPPLY(SName, PartNo, ProjName, Quantity)
FK: SName references SUPPLIER
FK: PartNo references PART
FK: ProjName references PROJECT

Step 8: Specialization/Generalization

Four Options:

Example: EMPLOYEE with subclasses SECRETARY, ENGINEER

Option 8A - Multiple Relations (Superclass + Subclasses):
EMPLOYEE(SSN, Name, Salary)
SECRETARY(SSN, TypingSpeed)
ENGINEER(SSN, EngType)
Use: Works for any specialization

Option 8B - Subclass Relations Only:
SECRETARY(SSN, Name, Salary, TypingSpeed)
ENGINEER(SSN, Name, Salary, EngType)
Use: Only for TOTAL specialization

Option 8C - Single Relation with Type Attribute:
EMPLOYEE(SSN, Name, Salary, JobType, TypingSpeed, EngType)
Use: For DISJOINT specialization

Option 8D - Single Relation with Boolean Flags:
EMPLOYEE(SSN, Name, Salary, IsSecretary, IsEngineer, TypingSpeed, EngType)
Use: For OVERLAPPING specialization

💡 Tip: Choose mapping option based on constraints: Total→8B, Disjoint→8C, Overlapping→8D, Any→8A

Step 9: Categories (Union Types)

Create relation with surrogate key + attributes from each superclass.

Example: OWNER category

PERSON(PersonID, Name)
COMPANY(CompanyID, Name)
BANK(BankID, Name)
OWNER(OwnerID, PersonID, CompanyID, BankID, ...)
-- Only one of PersonID/CompanyID/BankID will be non-NULL

📊 Relational Model Concepts

Terminology Comparison

Informal	Formal	Example
Table	Relation	EMPLOYEE
Column	Attribute	SSN, Name
Row	Tuple	(123, "John", ...)
Table Definition	Schema	EMPLOYEE(SSN, Name, Salary)
Populated Table	Instance/State	All rows currently in table

Relation Characteristics

Tuples are unordered (no inherent row order)
Attributes are ordered in schema definition
All values are atomic (1NF: no composite/multivalued attributes)
Each attribute value must be from its domain
NULL values represent unknown/unavailable/not applicable data

💡 Tip: Think of relations as mathematical sets - order doesn't matter, no duplicates allowed!

Constraints in Relational Model

1. Domain Constraints

Values must be from the attribute's domain.

Example:

Age INT → must be integer
Email VARCHAR(50) → must be string
Grade ENUM('A','B','C','D','F') → must be one of these

2. Key Constraints

No two tuples can have the same key value.

3. Entity Integrity Constraint

Primary key cannot be NULL.

⚠️ Critical Rule: t[PK] ≠ NULL for any tuple t

4. Referential Integrity Constraint

Foreign key must either be NULL or match a primary key value in referenced table.

Example:

EMPLOYEE(SSN, Name, Dno)
DEPARTMENT(Dnumber, Dname)
FK: Dno references Dnumber

Valid: Dno = 5 (if department 5 exists)
Valid: Dno = NULL (employee not assigned)
Invalid: Dno = 99 (if department 99 doesn't exist)

Update Operations & Anomalies

INSERT Violations:

Domain: Wrong data type
Key: Duplicate primary key
Entity Integrity: NULL primary key
Referential Integrity: Foreign key references non-existent value

DELETE Options:

RESTRICT: Reject deletion if referenced
CASCADE: Delete all referencing tuples
SET NULL: Set foreign keys to NULL

Example: ON DELETE CASCADE

Delete DEPARTMENT 5 →
Automatically deletes all EMPLOYEEs with Dno = 5

UPDATE Violations:

Updating primary key = DELETE + INSERT
Updating foreign key may violate referential integrity
Updating ordinary attribute may violate domain constraint

💾 SQL - Structured Query Language

🧠 Memory Hack: "Sweet Fluffy Wombats Gulp Honey Oranges" → SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY (query order!)

DDL (Data Definition Language)

CREATE TABLE

CREATE TABLE Student (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50) NOT NULL,
    GPA DECIMAL(3,2),
    DeptID INT,
    FOREIGN KEY (DeptID) REFERENCES Department(DeptID)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);
                

💡 Tip: Always specify ON DELETE and ON UPDATE actions for foreign keys!

Common Data Types

Type	Description	Example
INT	Integer	Age INT
VARCHAR(n)	Variable string	Name VARCHAR(50)
CHAR(n)	Fixed string	SSN CHAR(9)
DECIMAL(p,s)	Exact decimal	Price DECIMAL(10,2)
DATE	Date (YYYY-MM-DD)	BirthDate DATE
BOOLEAN	True/False	IsActive BOOLEAN
ENUM	List of values	Status ENUM('Active','Inactive')

ALTER TABLE

-- Add column
ALTER TABLE Student ADD Email VARCHAR(100);

-- Modify column
ALTER TABLE Student MODIFY COLUMN GPA DECIMAL(4,3);

-- Drop column
ALTER TABLE Student DROP COLUMN Email;

-- Add constraint
ALTER TABLE Student ADD CONSTRAINT unique_email UNIQUE(Email);
                

DROP TABLE

DROP TABLE Student; -- Permanent deletion!

⚠️ Warning: DROP TABLE permanently deletes the table and all data - use with extreme caution!

DML (Data Manipulation Language)

Basic Query Structure

SELECT [DISTINCT] <attributes>
FROM <tables>
[WHERE <condition>]
[GROUP BY <attributes>]
[HAVING <group condition>]
[ORDER BY <attributes> [ASC|DESC]]

SELECT - Projection

-- All columns
SELECT * FROM Student;

-- Specific columns
SELECT Name, GPA FROM Student;

-- Remove duplicates
SELECT DISTINCT Major FROM Student;

-- Calculated columns
SELECT Name, Salary * 1.1 AS NewSalary FROM Employee;

-- Rename columns
SELECT Name AS StudentName, GPA AS Grade FROM Student;
                

WHERE - Selection

-- Comparison operators
SELECT * FROM Student WHERE GPA >= 3.5;

-- Logical operators
SELECT * FROM Student WHERE GPA >= 3.5 AND Major = 'CS';

-- BETWEEN
SELECT * FROM Student WHERE Age BETWEEN 18 AND 25;

-- IN
SELECT * FROM Student WHERE Major IN ('CS', 'SE', 'IS');

-- LIKE pattern matching
SELECT * FROM Student WHERE Name LIKE 'A%';  -- Starts with A
SELECT * FROM Student WHERE Name LIKE '%a%';  -- Contains a
SELECT * FROM Student WHERE Name LIKE 'J__n';  -- J, any 2 chars, n

-- IS NULL
SELECT * FROM Employee WHERE SupervisorSSN IS NULL;

-- NOT
SELECT * FROM Student WHERE Major NOT IN ('CS', 'SE');
                

🧠 Memory Hack: "% = Zero or More, _ = Exactly One" for LIKE patterns

JOIN Operations

-- INNER JOIN (default)
SELECT S.Name, C.Title
FROM Student S JOIN Enrollment E ON S.StudentID = E.StudentID
               JOIN Course C ON E.CourseID = C.CourseID;

-- LEFT OUTER JOIN
SELECT S.Name, C.Title
FROM Student S LEFT JOIN Enrollment E ON S.StudentID = E.StudentID;
-- Returns all students, even those not enrolled

-- RIGHT OUTER JOIN
SELECT S.Name, C.Title
FROM Student S RIGHT JOIN Enrollment E ON S.StudentID = E.StudentID;
-- Returns all enrollments, even if student deleted

-- FULL OUTER JOIN
SELECT S.Name, C.Title
FROM Student S FULL OUTER JOIN Enrollment E ON S.StudentID = E.StudentID;
-- Returns all students AND all enrollments

-- CROSS JOIN (Cartesian Product)
SELECT * FROM Student CROSS JOIN Course;
-- Every student paired with every course

-- NATURAL JOIN
SELECT * FROM Student NATURAL JOIN Enrollment;
-- Automatic join on columns with same name
                

💡 Tip: LEFT JOIN = "Keep everything from the left table"
RIGHT JOIN = "Keep everything from the right table"
INNER JOIN = "Keep only matches"

Aggregation Functions

-- COUNT
SELECT COUNT(*) FROM Student;  -- Count all rows
SELECT COUNT(DISTINCT Major) FROM Student;  -- Count unique majors

-- SUM
SELECT SUM(Salary) FROM Employee WHERE Dno = 5;

-- AVG
SELECT AVG(GPA) FROM Student;

-- MAX/MIN
SELECT MAX(Salary), MIN(Salary) FROM Employee;

-- Multiple aggregates
SELECT Dno, COUNT(*) AS NumEmployees, AVG(Salary) AS AvgSal
FROM Employee
GROUP BY Dno;
                

GROUP BY & HAVING

-- Group by single attribute
SELECT Major, AVG(GPA) AS AvgGPA
FROM Student
GROUP BY Major;

-- Group by multiple attributes
SELECT Major, Year, COUNT(*) AS NumStudents
FROM Student
GROUP BY Major, Year;

-- Filter groups with HAVING
SELECT Major, AVG(GPA) AS AvgGPA
FROM Student
GROUP BY Major
HAVING AVG(GPA) > 3.5;

-- WHERE filters tuples, HAVING filters groups
SELECT Dno, COUNT(*) AS NumEmp, AVG(Salary) AS AvgSal
FROM Employee
WHERE Salary > 30000  -- Filter BEFORE grouping
GROUP BY Dno
HAVING COUNT(*) > 5;  -- Filter AFTER grouping
                

🧠 Memory Hack: "WHERE = rows/tuples, HAVING = groups" (W before H, filtering before grouping!)

ORDER BY

-- Ascending (default)
SELECT * FROM Student ORDER BY GPA;

-- Descending
SELECT * FROM Student ORDER BY GPA DESC;

-- Multiple columns
SELECT * FROM Student ORDER BY Major ASC, GPA DESC;
                

Nested Queries (Subqueries)

-- IN
SELECT Name FROM Student
WHERE StudentID IN (
    SELECT StudentID FROM Enrollment WHERE CourseID = 'CS340'
);

-- ANY/ALL
SELECT Name FROM Employee
WHERE Salary > ALL (
    SELECT Salary FROM Employee WHERE Dno = 5
);

-- EXISTS
SELECT Name FROM Employee E
WHERE EXISTS (
    SELECT * FROM Dependent D
    WHERE E.SSN = D.ESSN AND E.Name = D.Name
);

-- NOT EXISTS
SELECT Name FROM Sailor S
WHERE NOT EXISTS (
    SELECT * FROM Reserves R WHERE S.sid = R.sid
);
-- Sailors who haven't reserved any boat

-- Correlated subquery
SELECT Name FROM Employee E
WHERE Salary > (
    SELECT AVG(Salary) FROM Employee WHERE Dno = E.Dno
);
-- Employees earning above their department average
                

💡 Tip: Use EXISTS for checking existence, IN for small lists, ANY/ALL for comparisons

Set Operations

-- UNION (removes duplicates)
SELECT Name FROM Student_2023
UNION
SELECT Name FROM Student_2024;

-- UNION ALL (keeps duplicates)
SELECT Name FROM Student_2023
UNION ALL
SELECT Name FROM Student_2024;

-- INTERSECT
SELECT StudentID FROM Enrollment WHERE CourseID = 'CS340'
INTERSECT
SELECT StudentID FROM Enrollment WHERE CourseID = 'CS101';
-- Students taking both courses

-- EXCEPT/MINUS
SELECT StudentID FROM Student
EXCEPT
SELECT StudentID FROM Enrollment;
-- Students not enrolled in any course
                

INSERT

-- Insert single row
INSERT INTO Student VALUES (123, 'John', 3.5, 5);

-- Insert with specified columns
INSERT INTO Student (StudentID, Name) VALUES (124, 'Mary');

-- Insert multiple rows
INSERT INTO Student VALUES 
    (125, 'Ali', 3.8, 5),
    (126, 'Sara', 3.9, 3);

-- Insert from query
INSERT INTO HighGPA (StudentID, Name, GPA)
SELECT StudentID, Name, GPA FROM Student WHERE GPA >= 3.5;
                

UPDATE

-- Update single column
UPDATE Employee SET Salary = 50000 WHERE SSN = '123456789';

-- Update multiple columns
UPDATE Employee 
SET Salary = Salary * 1.1, Dno = 5 
WHERE Dno = 3;

-- Update with calculation
UPDATE Employee SET Salary = Salary * 1.1;

-- Update from subquery
UPDATE Employee SET Salary = (
    SELECT AVG(Salary) FROM Employee
) WHERE SSN = '123456789';
                

DELETE

-- Delete specific rows
DELETE FROM Student WHERE GPA < 2.0;

-- Delete all rows (keeps table structure)
DELETE FROM Student;

-- Better for deleting all: TRUNCATE (faster)
TRUNCATE TABLE Student;
                

⚠️ Warning: Always use WHERE clause with UPDATE/DELETE or you'll affect ALL rows!

Views

-- Create view
CREATE VIEW HighGPA AS
SELECT StudentID, Name, GPA 
FROM Student 
WHERE GPA >= 3.5;

-- Use view
SELECT * FROM HighGPA;

-- Drop view
DROP VIEW HighGPA;

-- Materialized view (stores data physically)
CREATE MATERIALIZED VIEW DeptSalary AS
SELECT Dno, AVG(Salary) AS AvgSalary
FROM Employee
GROUP BY Dno;

-- Refresh materialized view
REFRESH MATERIALIZED VIEW DeptSalary;
                

💡 Tip: Use views for security (hide sensitive columns), simplify complex queries, and provide different user perspectives

🔗 Functional Dependencies (FDs)

🧠 Memory Hack: "If X determines Y, write X → Y" (arrow points to what's determined)

Definition

X → Y means: If two tuples have the same X value, they must have the same Y value.

Example: Employee Table

SSN → Name (SSN determines Name)
If t1[SSN] = t2[SSN], then t1[Name] = t2[Name]

SSN → {Name, Address, Salary} (SSN determines multiple attributes)
{SSN, PNumber} → Hours (combination determines Hours)

Types of Functional Dependencies

1. Full Dependency

Y depends on ALL of X (cannot remove any attribute from X).

Example:

{SSN, PNumber} → Hours is FULL
Because: SSN alone doesn't determine Hours
And: PNumber alone doesn't determine Hours

2. Partial Dependency

Y depends on part of X (can remove some attributes from X).

Example:

{SSN, PNumber} → Name is PARTIAL
Because: SSN → Name (don't need PNumber)

💡 Tip: Partial dependencies violate 2NF and cause redundancy!

3. Transitive Dependency

X → Y and Y → Z, so X → Z indirectly.

Example:

SSN → DNumber (Employee determines Department)
DNumber → DName (Department determines DeptName)
Therefore: SSN → DName (transitive dependency)

💡 Tip: Transitive dependencies violate 3NF and cause redundancy!

Armstrong's Axioms

Rule	Description	Example
Reflexivity	If Y ⊆ X, then X → Y	AB → B (trivial)
Augmentation	If X → Y, then XZ → YZ	If A → B, then AC → BC
Transitivity	If X → Y and Y → Z, then X → Z	If A → B and B → C, then A → C
Union	If X → Y and X → Z, then X → YZ	If A → B and A → C, then A → BC
Decomposition	If X → YZ, then X → Y and X → Z	If A → BC, then A → B and A → C

Finding Closure (X+)

Closure X+ = all attributes functionally determined by X.

Example: Finding Closure

Given:
FD1: A → BC
FD2: CD → E
FD3: E → F

Find: (A)+
Start: (A)+ = {A}
FD1: A → BC, so (A)+ = {A, B, C}
FD2: CD → E, but we don't have D ✗
No more changes
Result: (A)+ = {A, B, C}

Find: (AD)+
Start: (AD)+ = {A, D}
FD1: A → BC, so (AD)+ = {A, B, C, D}
FD2: CD → E, we have C and D ✓, so (AD)+ = {A, B, C, D, E}
FD3: E → F, we have E ✓, so (AD)+ = {A, B, C, D, E, F}
Result: (AD)+ = {A, B, C, D, E, F} → AD is a superkey!

💡 Tip: If X+ contains all attributes, X is a superkey!

⚡ Normalization & Normal Forms

🧠 Memory Hack: "The key [1NF], the whole key [2NF], and nothing but the key [3NF], so help me Codd [BCNF]"

Why Normalize?

Minimize Redundancy: Don't store same data multiple times
Prevent Update Anomalies: Avoid insertion, deletion, modification problems
Improve Data Integrity: Maintain consistency
Optimize Storage: Use less space

Update Anomalies

Example: Bad Design

EMP_PROJ(EmpID, EmpName, ProjID, ProjName, Hours)

Sample Data:
(E1, John, P1, ProjectX, 10)
(E1, John, P2, ProjectY, 20)
(E2, Mary, P1, ProjectX, 15)

1. Update Anomaly:
Change P1 name to "ProjectZ" → must update 2 rows!

2. Insert Anomaly:
Can't add new project without assigning employee
Can't add employee without assigning project

3. Delete Anomaly:
Delete E2 (only employee on P1) → lose project P1 info!

First Normal Form (1NF)

Rule: All attributes must be atomic (no composite, no multivalued, no nested relations).

Example: Violates 1NF

Bad:
EMPLOYEE(SSN, Name, PhoneNumbers)
Where PhoneNumbers = {123, 456, 789}

Good (Fix 1):
EMPLOYEE(SSN, Name)
PHONE(SSN, PhoneNumber)

Good (Fix 2 - with redundancy):
EMPLOYEE(SSN, Name, Phone1, Phone2, Phone3)

💡 Tip: Fix multivalued attributes by creating separate table or adding multiple columns

Second Normal Form (2NF)

Rule: Must be in 1NF AND no partial dependencies (non-prime attributes must fully depend on primary key).

Example: Violates 2NF

Bad:
WORKS_ON(SSN, PNumber, Hours, EName, PName)

FDs:
{SSN, PNumber} → Hours (full dependency ✓)
SSN → EName (partial dependency ✗)
PNumber → PName (partial dependency ✗)

Good (Decompose):
WORKS_ON(SSN, PNumber, Hours)
EMPLOYEE(SSN, EName)
PROJECT(PNumber, PName)

🧠 Memory Hack: 2NF = "No Partial Possibilities" (eliminate partial dependencies)

Third Normal Form (3NF)

Rule: Must be in 2NF AND no transitive dependencies (non-prime attributes depend only on primary key, not on other non-prime attributes).

Example: Violates 3NF

Bad:
EMPLOYEE(SSN, Name, DNumber, DName, DMgrSSN)

FDs:
SSN → DNumber (good)
SSN → DName (transitive ✗)
Because: SSN → DNumber and DNumber → DName

Good (Decompose):
EMPLOYEE(SSN, Name, DNumber)
DEPARTMENT(DNumber, DName, DMgrSSN)

Boyce-Codd Normal Form (BCNF)

Rule: For every FD X → A, X must be a superkey.

Stronger than 3NF: Eliminates anomalies where prime attributes depend on non-keys.

Example: In 3NF but Violates BCNF

Bad:
TEACH(Student, Course, Instructor)

FDs:
{Student, Course} → Instructor
Instructor → Course (violates BCNF! Instructor not superkey)

Good (Decompose):
TEACHES(Instructor, Course)
ENROLLED(Student, Instructor)

⚠️ Note: BCNF decomposition may lose some functional dependencies (not always dependency-preserving)

Normal Forms Summary

NF	Rule	Eliminates
1NF	Atomic values only	Repeating groups, multivalued attributes
2NF	1NF + No partial dependencies	Redundancy from partial dependencies
3NF	2NF + No transitive dependencies	Redundancy from transitive dependencies
BCNF	Every determinant is a superkey	All anomalies based on FDs

💡 Practical Tip: Most databases normalize to 3NF. BCNF is ideal but sometimes impractical. Denormalization is OK for performance when justified!

Normalization Algorithm

Step-by-Step Process

1. Check 1NF: Remove multivalued/composite attributes
2. Check 2NF: Remove partial dependencies
   For each partial dependency X → A, create new relation R(X, A)
3. Check 3NF: Remove transitive dependencies
   For each transitive dependency X → Y → Z, create R(Y, Z)
4. Check BCNF: For each FD X → A where X not superkey
   Decompose into R1(X, A) and R2(R - A)
5. Verify: Check lossless join and dependency preservation

🍃 NoSQL & MongoDB

SQL vs NoSQL

Aspect	SQL (RDBMS)	NoSQL (MongoDB)
Data Model	Tables with rows/columns	Documents (JSON-like)
Schema	Rigid, predefined	Flexible, dynamic
Relationships	Foreign keys, JOINs	Embedded documents, references
Scalability	Vertical (add more power)	Horizontal (add more servers)
ACID	Full support	Relaxed (eventual consistency)
Best For	Structured, transactional data	Unstructured, big data, rapid development

🧠 Memory Hack: "NoSQL = Not Only SQL" (it's an addition, not replacement!)

MongoDB Terminology

SQL	MongoDB
Database	Database
Table	Collection
Row/Tuple	Document
Column	Field
Primary Key	_id (auto-generated)
JOIN	Embedded documents/$lookup

JSON & BSON

JSON Example

{
  "_id": "123456789",
  "name": "John Smith",
  "age": 30,
  "email": "john@example.com",
  "address": {
    "street": "123 Main St",
    "city": "Houston",
    "state": "TX"
  },
  "skills": ["Java", "Python", "SQL"],
  "salary": 75000,
  "active": true
}
                    

💡 Tip: MongoDB uses BSON (Binary JSON) internally for better performance, but you work with JSON!

MongoDB Data Types

Type	Description	Example
String	UTF-8 text	"John"
Integer	32 or 64 bit	42
Double	Floating point	3.14
Boolean	true/false	true
Array	List of values	["a", "b", "c"]
Object	Embedded document	{"x": 1, "y": 2}
Date	Timestamp	ISODate("2024-01-01")
Null	Null value	null
ObjectId	Unique identifier	ObjectId("507f1f77...")

MongoDB CRUD Operations

Create (Insert)

// Insert one document
db.students.insertOne({
  name: "Ali Ahmed",
  age: 20,
  major: "CS",
  gpa: 3.8
});

// Insert many documents
db.students.insertMany([
  { name: "Sara Ali", age: 21, major: "SE", gpa: 3.9 },
  { name: "Omar Khaled", age: 19, major: "IS", gpa: 3.5 }
]);
                

Read (Query)

// Find all
db.students.find();

// Find with condition
db.students.find({ major: "CS" });

// Find with multiple conditions (AND)
db.students.find({ major: "CS", gpa: { $gte: 3.5 } });

// Find with OR
db.students.find({
  $or: [
    { major: "CS" },
    { major: "SE" }
  ]
});

// Find one
db.students.findOne({ name: "Ali Ahmed" });

// Projection (select specific fields)
db.students.find(
  { major: "CS" },
  { name: 1, gpa: 1, _id: 0 }  // 1 = include, 0 = exclude
);

// Sort
db.students.find().sort({ gpa: -1 });  // -1 = descending

// Limit
db.students.find().limit(5);

// Skip (pagination)
db.students.find().skip(10).limit(5);  // Page 3
                

Update

// Update one
db.students.updateOne(
  { name: "Ali Ahmed" },
  { $set: { gpa: 3.9 } }
);

// Update many
db.students.updateMany(
  { major: "CS" },
  { $set: { department: "Computer Science" } }
);

// Increment value
db.students.updateOne(
  { name: "Ali Ahmed" },
  { $inc: { age: 1 } }  // age = age + 1
);

// Push to array
db.students.updateOne(
  { name: "Ali Ahmed" },
  { $push: { courses: "CS340" } }
);

// Replace entire document
db.students.replaceOne(
  { name: "Ali Ahmed" },
  { name: "Ali Ahmed", age: 21, major: "CS", gpa: 4.0 }
);
                

Delete

// Delete one
db.students.deleteOne({ name: "Ali Ahmed" });

// Delete many
db.students.deleteMany({ gpa: { $lt: 2.0 } });

// Delete all documents
db.students.deleteMany({});
                

MongoDB Query Operators

Operator	Description	Example
$eq	Equal to	{ age: { $eq: 20 } }
$ne	Not equal	{ age: { $ne: 20 } }
$gt	Greater than	{ age: { $gt: 20 } }
$gte	Greater than or equal	{ age: { $gte: 20 } }
$lt	Less than	{ age: { $lt: 20 } }
$lte	Less than or equal	{ age: { $lte: 20 } }
$in	In array	{ major: { $in: ["CS","SE"] } }
$nin	Not in array	{ major: { $nin: ["CS"] } }
$and	Logical AND	{ $and: [{...}, {...}] }
$or	Logical OR	{ $or: [{...}, {...}] }
$not	Logical NOT	{ age: { $not: { $gt: 20 } } }
$exists	Field exists	{ email: { $exists: true } }

🧠 Memory Hack: MongoDB operators start with $ (dollar sign)

Aggregation Pipeline

// Example: Average GPA by major
db.students.aggregate([
  { $group: {
      _id: "$major",
      avgGPA: { $avg: "$gpa" },
      count: { $sum: 1 }
    }
  },
  { $sort: { avgGPA: -1 } }
]);

// Example: Filter then group
db.students.aggregate([
  { $match: { gpa: { $gte: 3.0 } } },  // WHERE gpa >= 3.0
  { $group: {
      _id: "$major",
      students: { $push: "$name" }
    }
  }
]);

// Example: Project (select fields)
db.students.aggregate([
  { $project: {
      name: 1,
      gpa: 1,
      highAchiever: { $gte: ["$gpa", 3.5] }
    }
  }
]);
                

💡 Tip: Aggregation pipeline is like SQL GROUP BY + HAVING + ORDER BY combined!

Embedding vs Referencing

Embedding (Denormalized)

Best for: 1:Few relationships, data read together

{
  "_id": "E123",
  "name": "John Smith",
  "department": {
    "deptId": "D5",
    "deptName": "Research",
    "location": "Houston"
  }
}
                    

Pros: One query to get all data, better performance
Cons: Data duplication, harder to update

Referencing (Normalized)

Best for: 1:Many, Many:Many, frequently updated data

// Employee document
{
  "_id": "E123",
  "name": "John Smith",
  "deptId": "D5"
}

// Department document
{
  "_id": "D5",
  "deptName": "Research",
  "location": "Houston"
}

// Query with $lookup (JOIN)
db.employees.aggregate([
  {
    $lookup: {
      from: "departments",
      localField: "deptId",
      foreignField: "_id",
      as: "department"
    }
  }
]);
                    

Pros: No duplication, easier updates
Cons: Requires multiple queries or $lookup

🔒 Database Security

Key Components

7 Pillars of Database Security

1. Access Control: Who can access what
2. Encryption: Data protection at rest and in transit
3. Audit Trails: Logging all activities
4. Data Masking: Hide sensitive data
5. Backup & Recovery: Prevent data loss
6. Patch Management: Keep systems updated
7. Firewalls: Network-level protection

SQL Security Commands (DCL)

-- GRANT privileges
GRANT SELECT, INSERT ON Student TO user_john;

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';

-- REVOKE privileges
REVOKE INSERT ON Student FROM user_john;

-- Create user
CREATE USER 'john'@'localhost' IDENTIFIED BY 'password123';

-- Show grants
SHOW GRANTS FOR 'john'@'localhost';
                

Database Users

User Type	Role	Responsibilities
DBA	Administrator	Authorize access, monitor performance, backup/recovery
Database Designer	Architect	Define schema, constraints, relationships
End User	User	Query, update data through applications
Application Developer	Programmer	Build applications using database

💡 Tip: Follow principle of least privilege - give users only the access they need!

📖 System Catalog (Data Dictionary)

Definition: Metadata about the database stored in the database itself.

What's Stored in System Catalog

Relation/table names
Attribute names and data types
Constraints (primary keys, foreign keys, NOT NULL, etc.)
Views definitions
Indexes and storage structures
User privileges and security info
Statistics for query optimization (tuple counts, value distributions)

Querying System Catalog in MySQL

-- Show all tables
SHOW TABLES;

-- Describe table structure
DESCRIBE Student;

-- Show table creation SQL
SHOW CREATE TABLE Student;

-- Query information schema
SELECT * FROM INFORMATION_SCHEMA.TABLES 
WHERE TABLE_SCHEMA = 'mydb';

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Student';
                    

💡 Tip: System catalog is how the DBMS knows about your database structure - it's the database about the database!

🏗️ Database Architecture

Three-Schema Architecture

🧠 Memory Hack: "ELI" → External, Logical, Internal (top to bottom)

Level	View	Description	Example
External	User View	Different views for different users	Students see grades, Faculty see all data
Conceptual/Logical	Community View	Overall logical structure (ER, tables)	STUDENT(ID, Name, GPA)
Internal/Physical	Storage View	How data is physically stored	B-trees, file organization, indexes

Data Independence

Two Types

1. Physical Data Independence:
Change internal schema without changing conceptual schema
Example: Change from B-tree to hash index → applications unaffected

2. Logical Data Independence:
Change conceptual schema without changing external schemas
Example: Add new attribute to table → views don't break
(Harder to achieve than physical!)

Client-Server Architectures

2-Tier Architecture

Client: User interface + application logic
Server: Database server (DBMS)
Example: Desktop application connecting directly to database

3-Tier Architecture

Presentation Tier: User interface (web browser, mobile app)
Application Tier: Business logic (web server, API)
Data Tier: Database server
Advantages: Better security, scalability, maintainability

💡 Tip: Modern web apps use 3-tier: React (presentation) + Node.js (application) + MySQL (data)

⚡ Quick Reference Tables

SQL vs MongoDB Commands

Operation	SQL	MongoDB
Create DB	CREATE DATABASE db	use db
Create Table/Collection	CREATE TABLE student(...)	db.createCollection("student")
Insert	INSERT INTO student VALUES(...)	db.student.insertOne({...})
Select All	SELECT * FROM student	db.student.find()
Where Clause	SELECT * FROM student WHERE age > 20	db.student.find({age: {$gt: 20}})
Update	UPDATE student SET age=21 WHERE id=1	db.student.updateOne({id:1}, {$set:{age:21}})
Delete	DELETE FROM student WHERE id=1	db.student.deleteOne({id:1})
Join	SELECT * FROM a JOIN b ON a.id=b.id	db.a.aggregate([{$lookup:{...}}])
Group By	SELECT major, COUNT(*) FROM student GROUP BY major	db.student.aggregate([{$group:{_id:"$major", count:{$sum:1}}}])

Normal Forms Cheat Sheet

Normal Form	Check For	How to Fix
1NF	Multivalued or composite attributes	Create separate table or flatten
2NF	Partial dependencies (non-prime → part of key)	Move to separate table with partial key
3NF	Transitive dependencies (non-prime → non-prime)	Move to separate table with determinant
BCNF	Any FD where determinant isn't superkey	Decompose into R1(X,A) and R2(R-A)

ER Diagram Symbols

Symbol	Meaning	Example
Rectangle	Strong Entity	EMPLOYEE
Double Rectangle	Weak Entity	DEPENDENT
Oval	Attribute	Name, Age
Double Oval	Multivalued Attribute	{Phone_Numbers}
Dashed Oval	Derived Attribute	Age (from BirthDate)
Underline	Key Attribute	SSN
Dashed Underline	Partial Key	Dependent_Name
Diamond	Relationship	WORKS_FOR
Double Diamond	Identifying Relationship	DEPENDENTS_OF
Single Line	Partial Participation	Not all employees manage
Double Line	Total Participation	All employees work for a dept
1, N, M	Cardinality Ratio	1:N, M:N
(min, max)	Participation Constraint	(0,N), (1,1)

🎯 Exam Tips & Tricks

Common Mistakes to Avoid

1. Forgetting Foreign Key Constraints
Always specify ON DELETE and ON UPDATE actions!

2. Confusing WHERE and HAVING
WHERE filters tuples BEFORE grouping
HAVING filters groups AFTER grouping

3. Wrong Placement of Foreign Keys
1:N → Foreign key goes on the N side
M:N → Create junction table with both keys

4. Not Checking All Normal Forms
Check in order: 1NF → 2NF → 3NF → BCNF
Don't skip steps!

5. Forgetting DISTINCT in Aggregates
COUNT(*) vs COUNT(DISTINCT column) are different!

Problem-Solving Strategies

For ER Diagrams:
1. Identify all nouns (potential entities)
2. Identify all verbs (potential relationships)
3. Determine attributes for each entity
4. Find keys (what uniquely identifies each entity?)
5. Determine cardinality (1:1, 1:N, M:N)
6. Check for weak entities (depend on others?)

For Normalization:
1. List all FDs from the problem
2. Find all candidate keys using closure
3. Check 1NF (atomic values?)
4. Check 2NF (partial dependencies?)
5. Check 3NF (transitive dependencies?)
6. Check BCNF (all determinants superkeys?)
7. Decompose if needed

For SQL Queries:
1. Understand what output is needed
2. Identify which tables are needed
3. Determine join conditions
4. Add WHERE filters
5. Add GROUP BY if aggregating
6. Add HAVING if filtering groups
7. Add ORDER BY if sorting needed
8. Test with sample data mentally

Time-Saving Tips

🧠 For Multiple Choice:
1. Eliminate obviously wrong answers first
2. Look for keywords (ALL, NONE, ALWAYS, NEVER often wrong)
3. Check for trick wording carefully
4. Use process of elimination

🧠 For Design Problems:
1. Draw ER diagram first (visualize!)
2. Map to relational step-by-step
3. Write down all constraints
4. Check your work by tracing sample data

Last-Minute Review Checklist

✓ Know all 4 ACID properties and examples
✓ Can draw and map ER diagrams (all 9 steps)
✓ Understand all key types (super, candidate, primary, foreign)
✓ Know cardinality ratios (1:1, 1:N, M:N) and where FK goes
✓ Can identify weak entities and their notation
✓ Understand EER concepts (specialization, generalization, categories)
✓ Know all 4 constraints on specialization (d/o, total/partial)
✓ Can write basic SQL queries (SELECT, FROM, WHERE, JOIN)
✓ Know difference between INNER/LEFT/RIGHT/FULL OUTER JOIN
✓ Understand aggregation functions (COUNT, SUM, AVG, MAX, MIN)
✓ Can use GROUP BY and HAVING correctly
✓ Know nested queries (IN, EXISTS, ANY, ALL)
✓ Understand functional dependencies (full, partial, transitive)
✓ Can find closure of attributes
✓ Know all normal forms (1NF, 2NF, 3NF, BCNF) and how to fix violations
✓ Can decompose relations properly
✓ Understand update anomalies (insert, delete, modify)
✓ Know MongoDB basic CRUD operations
✓ Understand embedding vs referencing in NoSQL
✓ Know database security concepts and DCL commands

🚀 Advanced SQL Topics

Triggers

Automatically execute code when certain events occur.

-- Create trigger to update total salary when employee added
CREATE TRIGGER update_dept_salary
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
    UPDATE Department 
    SET TotalSalary = TotalSalary + NEW.Salary
    WHERE Dnumber = NEW.Dno;
END;

-- Trigger to prevent deletion of employees with dependents
CREATE TRIGGER prevent_delete_with_dependents
BEFORE DELETE ON Employee
FOR EACH ROW
BEGIN
    IF (SELECT COUNT(*) FROM Dependent WHERE ESSN = OLD.SSN) > 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Cannot delete employee with dependents';
    END IF;
END;
                

💡 Tip: Triggers are useful for maintaining derived data, enforcing complex constraints, and auditing!

Assertions

Global constraints that must always be true.

-- Assertion: Every department must have at least one employee
CREATE ASSERTION dept_has_employee
CHECK (NOT EXISTS (
    SELECT * FROM Department D
    WHERE NOT EXISTS (
        SELECT * FROM Employee E
        WHERE E.Dno = D.Dnumber
    )
));

-- Assertion: Manager salary must be highest in department
CREATE ASSERTION manager_highest_salary
CHECK (NOT EXISTS (
    SELECT * FROM Employee E, Department D
    WHERE E.Dno = D.Dnumber 
    AND D.MgrSSN IS NOT NULL
    AND E.Salary > (
        SELECT Salary FROM Employee WHERE SSN = D.MgrSSN
    )
));
                

Transactions (TCL)

-- Start transaction
START TRANSACTION;

-- Make changes
UPDATE Account SET Balance = Balance - 100 WHERE AccountID = 1;
UPDATE Account SET Balance = Balance + 100 WHERE AccountID = 2;

-- Commit (make permanent)
COMMIT;

-- Or rollback (undo all changes)
ROLLBACK;

-- Savepoint for partial rollback
START TRANSACTION;
UPDATE Account SET Balance = Balance - 50 WHERE AccountID = 1;
SAVEPOINT sp1;
UPDATE Account SET Balance = Balance + 50 WHERE AccountID = 2;
-- Oops, mistake! Rollback to savepoint
ROLLBACK TO sp1;
-- Now commit the first update only
COMMIT;
                

🧠 Memory Hack: "COMMIT = Confirm It's MY Intent To save"

Indexes

Speed up query performance by creating fast lookup structures.

-- Create index on single column
CREATE INDEX idx_student_name ON Student(Name);

-- Create composite index
CREATE INDEX idx_employee_dept_salary ON Employee(Dno, Salary);

-- Create unique index
CREATE UNIQUE INDEX idx_student_email ON Student(Email);

-- Drop index
DROP INDEX idx_student_name;
                

💡 Tip: Index columns used in WHERE, JOIN, and ORDER BY clauses. But too many indexes slow down INSERT/UPDATE/DELETE!

Window Functions (Advanced Analytics)

-- Rank students by GPA
SELECT Name, GPA,
       RANK() OVER (ORDER BY GPA DESC) AS rank
FROM Student;

-- Running total of salaries
SELECT Name, Salary,
       SUM(Salary) OVER (ORDER BY SSN) AS running_total
FROM Employee;

-- Partition by department
SELECT Name, Dno, Salary,
       AVG(Salary) OVER (PARTITION BY Dno) AS dept_avg_salary
FROM Employee;

-- Row number within partition
SELECT Name, Major, GPA,
       ROW_NUMBER() OVER (PARTITION BY Major ORDER BY GPA DESC) AS rank_in_major
FROM Student;
                

Common Table Expressions (CTEs)

-- WITH clause for readable complex queries
WITH HighGPAStudents AS (
    SELECT StudentID, Name, GPA
    FROM Student
    WHERE GPA >= 3.5
)
SELECT S.*, E.CourseID
FROM HighGPAStudents S
JOIN Enrollment E ON S.StudentID = E.StudentID;

-- Recursive CTE (employee hierarchy)
WITH RECURSIVE EmployeeHierarchy AS (
    -- Base case: top-level managers
    SELECT SSN, Name, SuperSSN, 1 AS Level
    FROM Employee
    WHERE SuperSSN IS NULL
    
    UNION ALL
    
    -- Recursive case: employees under managers
    SELECT E.SSN, E.Name, E.SuperSSN, EH.Level + 1
    FROM Employee E
    JOIN EmployeeHierarchy EH ON E.SuperSSN = EH.SSN
)
SELECT * FROM EmployeeHierarchy ORDER BY Level, Name;
                

💡 Tip: CTEs make complex queries more readable than nested subqueries!

🔮 Object-Oriented Databases (OODBMS)

Key Concepts

Concept	Description	Example
Objects	Data stored as programming objects	Employee object with methods and attributes
Classes	Template for objects	Employee class defines structure
Inheritance	Classes inherit from parent classes	Manager inherits from Employee
Encapsulation	Data and methods bundled together	Employee.calculateSalary() method
Persistence	Objects survive program termination	Objects stored in database

RDBMS vs OODBMS

Aspect	RDBMS	OODBMS
Data Structure	Tables (rows & columns)	Objects with attributes & methods
Relationships	Foreign keys, JOINs	Direct object references
Query Language	SQL	OQL (Object Query Language)
Inheritance	Not supported	Native support
Complex Data Types	Difficult	Easy (arrays, nested objects)
Best For	Business transactions	CAD, multimedia, complex apps

OODBMS Example Structure

In OODBMS:
Customer {
  customerID: String
  name: String
  orders: List<Order> // Direct reference!
}

Order {
  orderID: String
  customer: Customer // Direct reference!
  amount: Float
}

In RDBMS:
Customer(CustomerID, Name)
Order(OrderID, CustomerID, Amount) -- Foreign key

💡 Tip: PostgreSQL started as an object-relational database, supporting both paradigms!

⚡ Performance & Optimization Tips

Query Optimization

1. Use indexes on frequently queried columns
Especially for WHERE, JOIN, and ORDER BY clauses

2. Avoid SELECT *
Only select columns you need to reduce data transfer

3. Use LIMIT for large result sets
Pagination improves performance

4. Use EXPLAIN to analyze queries
See query execution plan and identify bottlenecks

-- Analyze query performance
EXPLAIN SELECT * FROM Employee WHERE Salary > 50000;

-- Detailed analysis
EXPLAIN ANALYZE SELECT E.Name, D.Dname
FROM Employee E JOIN Department D ON E.Dno = D.Dnumber;
                

5. Use proper JOIN types
INNER JOIN is faster than OUTER JOIN when possible

6. Filter early with WHERE
Reduce data before grouping/joining

7. Denormalize for read-heavy workloads
Sometimes breaking normal forms improves performance

8. Use batch operations
INSERT many rows at once instead of multiple single inserts

When to Denormalize

Read operations vastly outnumber writes
Complex joins causing performance issues
Data warehouse / reporting scenarios
Real-time dashboards needing fast queries

⚠️ Remember: Denormalization trades consistency for performance. Only do it when benefits outweigh costs!

📝 Final Study Strategies

The 24-Hour Before Exam Plan

Morning (9 AM - 12 PM):
• Review ER diagrams and mapping (1 hour)
• Practice SQL queries - write 10 different queries (1.5 hours)
• Review normalization - work through 2 examples (30 min)

Afternoon (2 PM - 5 PM):
• Review all memory hacks from this cheat sheet (30 min)
• Practice finding FDs and closures (1 hour)
• Review ACID properties with examples (30 min)
• Quick MongoDB CRUD review (1 hour)

Evening (7 PM - 9 PM):
• Review all formulas and definitions (1 hour)
• Practice one complete database design problem (1 hour)
• Light review of weak areas only

Night Before:
• Read through this cheat sheet once
• Get 8 hours of sleep!
• DON'T cram new material

Active Recall Questions

Test yourself without looking at answers:
• What are the 4 ACID properties? Give examples.
• Draw an ER diagram for a university database
• What's the difference between 2NF and 3NF?
• Where does the foreign key go in 1:N relationship?
• Write SQL query to find employees earning above department average
• What's the closure of {A,B} given FDs: A→C, B→D, CD→E?
• How do you fix a partial dependency?
• What's the difference between INNER JOIN and LEFT JOIN?

Common Exam Question Types

Type 1: ER Diagram Design

Given requirements, draw ER diagram with:
✓ All entities (strong and weak)
✓ All relationships with cardinality
✓ All attributes (including keys)
✓ Participation constraints

Type 2: Mapping ER to Relational

Convert ER diagram to relations:
✓ Apply all 9 mapping steps
✓ Identify primary and foreign keys
✓ Specify ON DELETE/UPDATE actions

Type 3: Normalization

Given relation with FDs:
✓ Find all candidate keys
✓ Check each normal form
✓ Decompose if violations found
✓ Verify lossless join

Type 4: SQL Query Writing

Write queries to:
✓ Select with conditions
✓ Join multiple tables
✓ Use aggregation and GROUP BY
✓ Write nested subqueries

Type 5: Conceptual Questions

Explain concepts:
✓ ACID properties
✓ Referential integrity
✓ Data independence
✓ Update anomalies

📐 Master Formula & Definition Sheet

Key Definitions (Memorize These!)

Database: Organized collection of related data

DBMS: Software to manage databases (create, query, update, control)

Relation: Table with tuples (rows) and attributes (columns)

Superkey: Set of attributes that uniquely identifies tuples

Candidate Key: Minimal superkey

Primary Key: Chosen candidate key (cannot be NULL)

Foreign Key: Attribute referencing primary key in another relation

Functional Dependency X → Y: If t1[X] = t2[X], then t1[Y] = t2[Y]

Closure X+: Set of all attributes functionally determined by X

1NF: All attributes atomic (no multivalued/composite)

2NF: 1NF + No partial dependencies

3NF: 2NF + No transitive dependencies

BCNF: Every determinant is a superkey

Essential Formulas

Cardinality: Number of tuples in a relation

Degree/Arity: Number of attributes in a relation

Cartesian Product: |R × S| = |R| × |S| tuples

Lossless Join Test:
(R1 ∩ R2) → (R1 - R2) OR (R1 ∩ R2) → (R2 - R1) must be in F+

🎓 Final Exam Day Reminders

✨ Before You Start:
1. Read ALL questions first
2. Do easy questions first (build confidence!)
3. Budget time: divide total time by points
4. Leave hard questions for last

✨ During The Exam:
1. Draw diagrams for complex problems
2. Show your work (partial credit!)
3. Check for obvious mistakes (NULL in primary key?)
4. Use examples to verify your answer
5. If stuck, move on and come back

✨ Common Errors to Check:
✓ Did you underline all keys?
✓ Did you specify foreign key constraints?
✓ Did you check ALL normal forms?
✓ Did you use correct JOIN syntax?
✓ Did you handle NULL values properly?
✓ Did you use GROUP BY with aggregates?
✓ Did you put foreign key on correct side?

💪 You've Got This!
Remember: You've learned an entire semester of material. Trust your preparation, stay calm, and think through each problem logically. Database design is about understanding concepts and applying them systematically. You know this material!