DBMS & SQL
Complete Student Notes
Original educational guide
Page 1
Contents
1. Introduction to DBMS
2. Database Models
3. Tables, Rows, and Keys
4. SQL Fundamentals
5. Joins
6. Normalization
7. Transactions and ACID
8. Concurrency Control
9. Indexes and Performance
10. Security and Backup
11. Database Design
12. Practical SQL Workflow
Page 2
Introduction to DBMS
A database management system (DBMS) is software used to define, store, retrieve, update, and protect
structured data. It provides an organized interface between applications and stored information.
A DBMS reduces many problems associated with uncontrolled file-based storage, including duplication,
inconsistent updates, weak security, and difficult concurrent access.
Page 3
Database Models
The relational model represents information using tables consisting of rows and columns. Other models
include document, key-value, graph, and wide-column approaches.
The best model depends on access patterns, consistency requirements, relationships, scalability, and
application needs.
Page 4
Tables, Rows, and Keys
A table represents an entity or relationship. A row represents one record, while a column represents an
attribute.
A primary key uniquely identifies rows. A foreign key references a key in another table and helps represent
relationships between entities.
Page 5
SQL Fundamentals
SQL is used to define database structures, manipulate records, and query information. SELECT retrieves
data, INSERT adds rows, UPDATE changes rows, and DELETE removes rows.
Filtering with WHERE and sorting with ORDER BY are fundamental query techniques.
Page 6
Joins
Joins combine related rows from multiple tables. INNER JOIN returns matching combinations, while LEFT
JOIN retains rows from the left table even when no matching row exists.
Correct join conditions are essential. An incomplete condition can accidentally create a Cartesian product
and return far more rows than intended.
Page 7
Normalization
Normalization organizes relational data to reduce redundancy and update anomalies. First, second, and
third normal forms progressively impose structural constraints.
Normalization is a design technique rather than a rule that every database must maximize. Practical
systems may intentionally denormalize selected data for performance.
Page 8
Transactions and ACID
A transaction is a logical unit of work. ACID describes atomicity, consistency, isolation, and durability.
Transactions are particularly important when several related changes must succeed together or be rolled
back together.
Page 9
Concurrency Control
Multiple users may access the same database simultaneously. Concurrency control prevents conflicting
operations from producing incorrect results.
Locking, isolation levels, and multiversion techniques are common approaches. Higher isolation can
improve consistency but may reduce concurrency.
Page 10
Indexes and Performance
An index is an auxiliary structure that can speed up searches, joins, and ordering. However, indexes
consume storage and can increase the cost of writes.
Good indexing begins with understanding real query patterns. Indexes should be measured rather than
added indiscriminately.
Page 11
Security and Backup
Database security includes authentication, authorization, least-privilege access, encryption where
appropriate, auditing, and secure configuration.
Backups should be regular, tested, and protected from unauthorized access. A backup that has never
been restored in testing should not be assumed to be reliable.
Page 12
Database Design
Entity-relationship modeling helps identify entities, attributes, relationships, and constraints before
implementation.
A good design makes business rules explicit and prevents invalid states. Constraints should be enforced
as close to the data as practical.
Page 13
Practical SQL Workflow
A useful workflow is to understand requirements, model entities, define constraints, create tables, load
sample data, write representative queries, add indexes based on evidence, and test failure scenarios.
A small student project might contain users, products, orders, and payments. Designing the relationships
and writing analytical queries provides practice across most core DBMS concepts.
Page 14