Entity-Relationship Model
The entity-relationship (ER) model describes the data of an application before any table is designed.
DB · Short notes
Database Management Systems short notes
20 short notes in syllabus order, about 156 minutes to read end to end.
ER model
Mapping ER Diagrams to Relations
An ER diagram becomes a relational schema by a short list of mechanical rules: one table per strong entity set, one per weak entity set, one per many-to-many relationship, one per multivalued attribute, and foreign keys for everything else.
Relational model, keys and integrity constraints
Relational Model and Keys
The relational model stores all data as relations: tables whose rows are tuples of atomic values. It was designed so that queries could be expressed as operations on sets, independent of how the rows are laid out on disk.
Integrity Constraints
An integrity constraint is a condition that every legal database instance must satisfy. The DBMS checks it on every insert, delete and update, and rejects (or repairs) any change that would break it.
Relational algebra
Relational Algebra
Relational algebra is a procedural query language: a query is an expression built from operators that take relations and return a relation. Because the output of every operator is again a relation, operators compose freely, and an expression reads like a pipeline.
Tuple relational calculus
Tuple Relational Calculus
Tuple relational calculus (TRC) is a declarative query language: a query states the property that answer tuples must satisfy and says nothing about how to compute them.
SQL
SQL: Data Definition, Data Manipulation and Basic Queries
SQL is the standard language of relational databases. It has a data definition part (DDL) that creates and changes schemas and constraints, a data manipulation part (DML) that inserts, updates and deletes rows, and a query part built around the SELECT ... FROM ... WHERE block.
SQL Joins, Grouping, Aggregation and NULLs
Most real queries combine tables with joins, summarise rows with aggregate functions, and must cope with missing values. These three features interact: an outer join manufactures NULLs, aggregate functions skip NULLs, and the WHERE and HAVING filters drop anything whose condition is UNKNOWN.
SQL Subqueries, Set Operations and Views
A subquery is a SELECT inside another statement. It lets a query compare a value with a computed set ("salary greater than every salary in EE"), test whether related rows exist ("departments with no employee"), or express "for all" conditions without a division operator.
Functional dependencies
Functional Dependencies, Closure and Canonical Cover
A functional dependency (FD) X → Y says that the values of attributes X determine the values of attributes Y: two tuples that agree on X must agree on Y.
Normal forms and decomposition
Normal Forms: 1NF, 2NF, 3NF and BCNF
A relation that stores two independent facts in one row repeats one of them, and repetition causes anomalies.
Lossless-Join and Dependency-Preserving Decomposition
, R_n whose attributes together cover R. Splitting is only safe if two properties hold. The decomposition must be lossless: joining the pieces back must give exactly the original relation, with no extra (spurious) tuples.
File organization and indexing: B and B+ trees
File Organization: Records, Blocks, Heap, Sorted and Hashed Files
A relation is stored as a file of records, and the file is stored on disk in fixed-size blocks (pages). The disk transfers whole blocks, so the cost of a database operation is measured in block accesses, not in record comparisons.
Indexing: Primary, Clustering, Secondary and Multilevel Indexes
An index is an auxiliary file that speeds up search on a field, the indexing field or search key. Index entries are much smaller than data records, so a search through the index reads far fewer blocks than a search through the data file.
B-Trees and B+ Trees
A static multilevel index from Indexing must be rebuilt as the file grows. B-trees and B+ trees are multilevel indexes that stay balanced under inserts and deletes: every leaf is at the same depth, and every node except the root is at least half full.
Transactions, schedules and serializability
Transactions and the ACID Properties
A transaction is a unit of program execution that reads and possibly updates database items and must behave as one indivisible step: either all of its effects persist or none do, and other transactions see it as if it ran alone.
Schedules, Conflict Serializability and View Serializability
Isolation says that concurrent transactions must behave as if they ran one at a time. Serializability makes this precise: a schedule is acceptable if it is equivalent to some serial schedule. Two notions of equivalence are used.
Recoverability: Recoverable, Cascadeless and Strict Schedules
Serializability ignores failures: it assumes every transaction commits. A real system must also handle a transaction that aborts after other transactions have read what it wrote. Recoverability classifies schedules by how safely they survive such aborts, using the positions of commits and aborts.
Concurrency control and recovery
Lock-Based Concurrency Control and Two-Phase Locking
A concurrency-control protocol decides, operation by operation, whether a transaction may proceed, must wait, or must abort, so that every schedule it lets through is serializable.
Timestamp-Based Concurrency Control
Locking discovers a serialization order at run time, through the order of lock points. Timestamp ordering fixes the order in advance: every transaction gets a unique timestamp when it starts, and the system allows a conflicting operation only if it respects timestamp order.