Databases

Example Schema: School Database

Students table:

Courses table:

Enrollments table:

This schema shows three tables and how they relate. Students can enroll in courses. The Enrollments table connects them.

Tables (Relations)

Each table stores one type of entity. Students table stores students. Courses table stores courses. Not: Students_and_Courses_and_Everything_Else table. This separation is crucial. We'll see why when we discuss normalization.

Primary Keys

Definition: A primary key is a field (or combination of fields) that uniquely identifies each record in a table.

Rules:

• Must be unique (no two records have same value)
• Cannot be NULL (must have a value)
• Should never change

Example: In the Students table, StudentID is the primary key. Every student has a unique StudentID. No two students share an ID. If StudentID = 12345, there's exactly one student with that ID.

Why not use names? Two students might have the same name. Names can change (marriage). IDs don't change.

Foreign Keys

Definition: A foreign key is a field in one table that refers to the primary key of another table, creating a relationship between the tables.

Example: The Enrollments table has StudentID as a foreign key. It references the primary key StudentID in the Students table. This says: "This enrollment belongs to this specific student."

Referential integrity: The database ensures foreign keys always point to valid records. You can't enroll a student with ID 99999 if no such student exists.

Relationships

One-to-One (1:1): One record in Table A relates to exactly one record in Table B. Example: Each employee has one passport. Each passport belongs to one employee.

One-to-Many (1:M): One record in Table A relates to many records in Table B. Example: One teacher teaches many courses. Each course has one teacher.

Many-to-Many (M:N): Many records in Table A relate to many records in Table B. Example: Students enroll in many courses. Courses have many students. Implementation: Requires a junction table (like Enrollments). Can't directly represent M:N in relational databases.

ERD Components

Entities: Represented as rectangles. These become tables.

Attributes: Represented as ovals connected to entities. These become fields.

Relationships: Represented as diamonds connecting entities.

Cardinality: Shows relationship type (1:1, 1:M, M:N).

Crow's foot notation (common):

• One: Single line — represents exactly one record
• Many: Crow's foot (three lines) — represents multiple records

Example ERD — School System

This shows:

• One teacher teaches many courses (1:M)
• One course has one teacher (M:1)
• Students enroll in many courses (M:N)
• Courses have many students (M:N)

First Normal Form (1NF)

Definition: A table is in 1NF if each field contains only atomic (indivisible) values and each record is unique.

Rules:

• Each cell contains a single value (no lists, no sets)
• Each record is unique (has primary key)
• No repeating groups (like Course1, Course2, Course3)

Bad (not 1NF):

"Courses" column contains multiple values. Violates 1NF.

Good (1NF):

Each cell has a single value. Each row is a unique combination of student and course. Problem: Still redundant. "Alice" appears twice. If she changes name, update multiple rows.

Second Normal Form (2NF)

Definition: A table is in 2NF if it's in 1NF and all non-key attributes are fully dependent on the entire primary key (no partial dependencies).

Only relevant for composite primary keys (keys made of multiple fields).

Example with composite key — Composite primary key: (StudentID, CourseID)

Bad (1NF but not 2NF):

Problems:

• StudentName depends only on StudentID (not the full key)
• CourseName depends only on CourseID (not the full key)
• Only Grade depends on both StudentID and CourseID

This is partial dependency—some attributes depend on part of the key, not the whole key.

Good (2NF) — Split into three tables:

Students:

Courses:

Enrollments:

Now all non-key attributes fully depend on the entire primary key. Problem: Still possible redundancy if non-key attributes depend on other non-key attributes.

Third Normal Form (3NF)

Definition: A table is in 3NF if it's in 2NF and no non-key attribute depends on another non-key attribute (no transitive dependencies).

Bad (2NF but not 3NF):

Primary key: StudentID. Problem: AdvisorName depends on AdvisorID (not on StudentID directly). This is transitive dependency: StudentID → AdvisorID → AdvisorName. If Dr. Smith changes name, must update multiple rows.

Good (3NF) — Split into two tables:

Students:

Advisors:

Now if Dr. Smith changes name, update one row in the Advisors table. Students table doesn't change.

SELECT: Retrieving Data

Basic syntax:

SELECT column1, column2
FROM table_name
WHERE condition;

Get all students:

SELECT * FROM Students;

Get specific columns:

SELECT FirstName, LastName FROM Students;

Filter with WHERE:

SELECT * FROM Students
WHERE LastName = 'Smith';

Multiple conditions:

SELECT * FROM Students
WHERE LastName = 'Smith' AND FirstName = 'John';

Pattern matching with LIKE:

SELECT * FROM Students
WHERE Email LIKE '%@gmail.com';

Sorting with ORDER BY:

SELECT * FROM Students
ORDER BY LastName ASC, FirstName ASC;

Limiting results:

SELECT * FROM Students
LIMIT 10;

INSERT: Adding Data

Syntax:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

Example:

INSERT INTO Students (StudentID, FirstName, LastName, Email)
VALUES (1, 'Alice', 'Johnson', 'alice@email.com');

Multiple rows:

INSERT INTO Students (StudentID, FirstName, LastName)
VALUES
  (2, 'Bob', 'Smith'),
  (3, 'Carol', 'Williams'),
  (4, 'David', 'Brown');

UPDATE: Modifying Data

Syntax:

UPDATE table_name
SET column1 = value1, column2 = value2
WHERE condition;

Example:

UPDATE Students
SET Email = 'new.email@gmail.com'
WHERE StudentID = 1;

DELETE: Removing Data

Syntax:

DELETE FROM table_name
WHERE condition;

Example:

DELETE FROM Students
WHERE StudentID = 1;

INNER JOIN

Definition: Returns only rows where there's a match in both tables.

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example — Students and their enrollments:

SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses     ON Enrollments.CourseID = Courses.CourseID;

Result: Only students who are enrolled in courses.

LEFT JOIN (LEFT OUTER JOIN)

Definition: Returns all rows from the left table and matching rows from the right table. If no match, NULL for right table columns.

SELECT Students.FirstName, Courses.CourseName
FROM Students
LEFT JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
LEFT JOIN Courses     ON Enrollments.CourseID = Courses.CourseID;

Result: All students. For students with no enrollments, CourseName shows NULL.

RIGHT JOIN (RIGHT OUTER JOIN)

Definition: Returns all rows from the right table and matching rows from the left table. Less commonly used (can rewrite as LEFT JOIN by swapping tables).

FULL OUTER JOIN

Definition: Returns all rows from both tables. If no match, NULL for missing columns.

SELECT Students.FirstName, Courses.CourseName
FROM Students
FULL OUTER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
FULL OUTER JOIN Courses     ON Enrollments.CourseID = Courses.CourseID;

COUNT

Counts number of rows.

SELECT COUNT(*) FROM Students;
-- Returns total number of students

SELECT COUNT(*) FROM Students
WHERE LastName = 'Smith';
-- Returns number of students with last name Smith

SUM

Adds up values.

SELECT SUM(Credits) FROM Enrollments
WHERE StudentID = 1;
-- Total credits for student 1

AVG

Calculates average.

SELECT AVG(Grade) FROM Enrollments
WHERE CourseID = 101;
-- Average grade in course 101

MIN and MAX

Find minimum and maximum values.

SELECT MIN(DateOfBirth) FROM Students;
-- Oldest student (earliest birth date)

SELECT MAX(DateOfBirth) FROM Students;
-- Youngest student (latest birth date)

GROUP BY

Groups rows that have the same values.

-- Count students in each course:
SELECT CourseID, COUNT(*) AS NumStudents
FROM Enrollments
GROUP BY CourseID;

-- Average grade per course:
SELECT Courses.CourseName, AVG(Enrollments.Grade) AS AvgGrade
FROM Enrollments
INNER JOIN Courses ON Enrollments.CourseID = Courses.CourseID
GROUP BY Courses.CourseName;

HAVING

Filters groups (WHERE filters rows before grouping; HAVING filters groups after).

-- Courses with more than 10 students:
SELECT CourseID, COUNT(*) AS NumStudents
FROM Enrollments
GROUP BY CourseID
HAVING COUNT(*) > 10;

Creating a View

CREATE VIEW StudentCourses AS
SELECT Students.FirstName, Students.LastName, Courses.CourseName
FROM Students
INNER JOIN Enrollments ON Students.StudentID = Enrollments.StudentID
INNER JOIN Courses     ON Enrollments.CourseID = Courses.CourseID;

Using the View

SELECT * FROM StudentCourses
WHERE LastName = 'Smith';

Much simpler than writing the full join query every time!

ACID Properties

• Atomicity: All operations succeed or all fail. No partial completion.
• Consistency: Database remains in valid state before and after transaction.
• Isolation: Concurrent transactions don't interfere with each other.
• Durability: Once committed, changes are permanent (even if system crashes).

Example: Bank Transfer

Transfer $100 from Account A to Account B.

BEGIN TRANSACTION;

UPDATE Accounts
SET Balance = Balance - 100
WHERE AccountID = 'A';

UPDATE Accounts
SET Balance = Balance + 100
WHERE AccountID = 'B';

COMMIT;

If anything goes wrong (power failure, error in second UPDATE), the entire transaction rolls back. Account A doesn't lose $100 without Account B gaining it.

Commands

• BEGIN TRANSACTION — Start transaction
• COMMIT — Make changes permanent
• ROLLBACK — Undo all changes in transaction

Example with Error Handling

BEGIN TRANSACTION;

UPDATE Accounts SET Balance = Balance - 100 WHERE AccountID = 'A';

-- Check if sufficient funds
IF (SELECT Balance FROM Accounts WHERE AccountID = 'A') < 0 THEN
    ROLLBACK;
    -- Transaction cancelled
ELSE
    UPDATE Accounts SET Balance = Balance + 100 WHERE AccountID = 'B';
    COMMIT;
    -- Transaction successful
END IF;

Data Warehouse Architecture

• Data sources: Various operational databases, external sources
• ETL process (Extract, Transform, Load):
  • Extract: Pull data from sources
  • Transform: Clean, standardize, aggregate data
  • Load: Insert into data warehouse
• Data warehouse: Central repository
• Data marts: Subsets of warehouse for specific departments (sales, finance, marketing)
• BI tools: Business intelligence software for analysis, visualization, reporting

Star Schema

Definition: A star schema is a data warehouse design where a central fact table connects to multiple dimension tables, forming a star-like structure.

• Fact table: Contains measurable data (sales, revenue, quantities)
• Dimension tables: Contain descriptive attributes (products, customers, time)

Example: Retail Sales Warehouse

Fact table — Sales:

Dimension tables:

Query example — Total sales by category per month:

SELECT Product.Category, Date.Month, SUM(Sales.Revenue)
FROM Sales
JOIN Product ON Sales.ProductID = Product.ProductID
JOIN Date    ON Sales.DateID    = Date.DateID
GROUP BY Product.Category, Date.Month;