Lecture Notes: Database Systems (Relational Model & SQL)
Overview
These lecture notes cover core concepts of relational databases, design principles, normalization, SQL basics,
transactions, indexing, and backup strategies. Intended for undergraduate students or beginners preparing for
database coursework.
Relational Model Fundamentals
• **Relation**: A table with rows (tuples) and columns (attributes).
• **Primary Key**: A unique identifier for tuples in a relation.
• **Foreign Key**: An attribute that creates a link between two relations.
Entity-Relationship (ER) Modeling
• **Entities** represent real-world objects (e.g., Student, Course).
• **Attributes** describe entity properties (e.g., StudentID, Name).
• **Relationships** show associations (one-to-one, one-to-many, many-to-many).
• **Design tip**: Convert many-to-many relationships into associative tables.
Normalization
• **1NF (First Normal Form)**: Atomic attribute values; no repeating groups.
• **2NF (Second Normal Form)**: 1NF + no partial dependencies on a composite key.
• **3NF (Third Normal Form)**: 2NF + no transitive dependencies.
• **BCNF**: Stronger form of 3NF for certain edge cases.
• **Why normalize?** Reduce redundancy, avoid update anomalies, and improve data integrity.
• **When to denormalize?** For read-heavy systems where performance beats strict normalization (use with
caution).
SQL Basics (Examples)
• **Create table**: `CREATE TABLE Student (StudentID INT PRIMARY KEY, Name VARCHAR(100), Age INT);`
• **Insert**: `INSERT INTO Student (StudentID, Name, Age) VALUES (1, 'Asha', 20);`
• **Select**: `SELECT Name FROM Student WHERE Age > 18;`
• **Join example**: `SELECT [Link], [Link] FROM Student s JOIN Enrollment e ON [Link]=[Link]
JOIN Course c ON [Link]=[Link];`
Transactions & ACID Properties
• **Atomicity**: All-or-nothing transactions.
• **Consistency**: Database moves from one valid state to another.
• **Isolation**: Concurrent transactions do not interfere.
• **Durability**: Once committed, changes persist.
Indexing & Query Optimization
• **Index types**: B-tree (general), Hash (equality), Composite indexes.
• **Use cases**: Speed up SELECT queries, but indexes add overhead to writes.
• **Explain plan**: Use `EXPLAIN` to inspect query execution and optimize accordingly.
Backup, Recovery & Maintenance
• **Full backup**: Complete copy of database.
• **Incremental backup**: Only changes since last backup.
• **Point-in-time recovery**: Use transaction logs to restore to a moment.
• **Maintenance**: Rebuild indexes, update statistics, monitor slow queries.
Practical Tips for Students
1. Model data with ER diagrams before writing SQL.
2. Write small queries and validate results incrementally.
3. Practice normalization on sample datasets.
4. Use sample databases (Sakila, Pagila, Chinook) for hands-on learning.
---
References (suggested reading): Database System Concepts by Silberschatz, Korth & Sudarshan; SQL
documentation for your RDBMS.
