Database Systems - Final Exam Study Notes
Ch1: Intro & DBMS Concepts
· Database: Organized collection of related data.
· DBMS (Database Management System): Software to create/manage databases (e.g.,
MySQL, PostgreSQL, Oracle).
· File System vs DBMS:
· File sys: Data redundancy, inconsistency, hard to query.
· DBMS: Controls redundancy, enforces integrity, provides security, concurrency
control, backup/recovery.
· 3-Layer Architecture:
1. External (View level): What user sees.
2. Conceptual (Logical): Overall DB structure.
3. Internal (Physical): How data stored on disk.
· Data Independence:
· Logical: Change conceptual schema without changing external apps.
· Physical: Change internal schema without changing conceptual.
· Key Roles:
· DBA (Database Admin): manages DBMS, security, backups.
· End Users: interact via apps.
· App Devs: write programs that use DB.
Ch2: Relational Model
· Relation = Table.
· Tuple = Row.
· Attribute = Column.
· Domain: Set of allowed values for attribute.
· Key Types:
· Superkey: Any set of attributes that uniquely IDs tuple.
· Candidate Key: Minimal superkey.
· Primary Key: Chosen candidate key (underline in schema).
· Foreign Key: Refers to PK in another table → enforces referential integrity.
· Schema vs Instance: Schema is structure, instance is actual data at a time.
· Integrity Constraints:
· Relational Algebra (PROJECT π, SELECT σ, JOIN ⋈, UNION ∪, etc.) — procedural
· Domain, Entity (PK not null), Referential (FK must match existing PK).
language.
Ch3: SQL (Structured Query Language)
· DDL (Data Def Lang):
```sql
CREATE TABLE Student (
sid INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
gpa DECIMAL(3,2)
);
ALTER TABLE ... ADD/DROP COLUMN;
DROP TABLE;
```
· DML (Data Manipulation):
```sql
INSERT INTO Student VALUES (1, 'Alex', 3.8);
UPDATE Student SET gpa = 3.9 WHERE sid = 1;
DELETE FROM Student WHERE name = 'Alex';
```
· Basic Queries (SELECT):
```sql
SELECT name, gpa FROM Student WHERE gpa > 3.5 ORDER BY name DESC;
SELECT DISTINCT major FROM Student;
```
· Aggregate Funcs: COUNT, SUM, AVG, MAX, MIN.
```sql
SELECT AVG(gpa), major FROM Student GROUP BY major HAVING AVG(gpa) > 3.0;
```
· Joins:
· Inner Join: Matched rows only.
```sql
SELECT [Link], [Link] FROM Student s JOIN Enroll e ON [Link] = [Link];
```
· Outer Join: LEFT, RIGHT, FULL (include unmatched rows).
· Nested Subqueries: IN, EXISTS, ANY, ALL.
· Views: Virtual table.
```sql
CREATE VIEW GoodStudents AS SELECT * FROM Student WHERE gpa > 3.5;
```
Ch4: ER Modeling
· Entity: Real-world object (rectangle).
· Weak Entity: Depends on another entity (double rectangle).
· Attribute Types: Simple vs composite, single vs multivalued (oval), derived
(dashed oval).
· Relationship: Association between entities (diamond).
· Cardinality: 1:1, 1:N, M:N.
· Participation: Total (double line) vs partial.
· Converting ER to Relational Schema:
· Entity → table.
· 1:N relationship → FK on N-side.
· M:N relationship → new junction table with FKs.
· Composite attr → separate columns.
· Multivalued attr → new table.
Ch5: Normalization
· Goal: Reduce redundancy, avoid anomalies (update, insertion, deletion).
· Functional Dependency (FD): X → Y means if X same, then Y must be same.
· Normal Forms:
1. 1NF: All attributes atomic (no repeating groups).
2. 2NF: In 1NF + no partial dependency (non-prime attr fully dependent on PK).
3. 3NF: In 2NF + no transitive dependency (non-prime attr depends only on PK, not
other non-prime).
4. BCNF: Stronger 3NF — for every FD X → Y, X must be superkey.
· Decomposition: Must be lossless and dependency preserving.
Ch6: Transactions & Concurrency
· Transaction: Sequence of ops (read/write) treated as single logical unit.
· ACID Properties:
· Atomicity: All or nothing.
· Consistency: DB rules preserved.
· Isolation: Concurrent transactions don't interfere.
· Durability: Once committed, changes permanent.
· Concurrency Problems:
· Dirty read, Non-repeatable read, Phantom read, Lost update.
· Schedules: Serial vs concurrent.
· Locks: Shared (read) vs Exclusive (write). 2-Phase Locking (2PL) guarantees
serializability (growing phase, shrinking phase) but can cause deadlock.
· Deadlock Handling: Prevention (wait-die, wound-wait) or Detection (wait-for
graph) & recovery.
Ch7: Indexing & Hashing
· Index: Data structure to speed up retrieval.
· Primary Index: On ordered file, search key is PK.
· Secondary Index: On non-key field.
· Dense vs Sparse Index:
· Dense: index entry for every search key.
· Sparse: index entry for some blocks → less space, slower.
· B+ Tree: Balanced, fanout high, leaves linked for range queries.
· Hashing: Hash function maps key to bucket.
· Static hashing: fixed buckets.
· Dynamic hashing (extendible): buckets split as needed.
Stuff to Memorize for Exam:
· SQL syntax for SELECT, JOIN, GROUP BY.
· Steps to normalize to 3NF.
· ACID & concurrency control definitions.
· Difference between 2PL and timestamp ordering.
· When to use B+ tree vs hashing.
---
Handwritten style additions on margin:
· "ERD → Relational mapping is BIG on test - know M:N junction table!"
· "ACID - remember Atomicity = rollback, Durability = committed = safe."
· "Indexing: B+ tree good for range, Hashing good for exact match."
· "Normalization: 1NF (atomic), 2NF (no partial), 3NF (no transitive)."
· "⚠️ Practice writing SQL joins!!"