Database Management Systems — Complete Detailed Note

A database is a shared, organised collection of data plus the software that lets many users store, find, change and protect it without corrupting it. The whole unit is about four promises a database makes: each fact is stored once and found by its meaning, not by its position; questions can be asked in a high-level language instead of by hand-searching files; many users can work at once without losing each other's work; and committed work survives a crash. Every topic in this unit — the ER model, the relational model, SQL, normalisation, transactions and recovery — is one mechanism that keeps one of those promises. Learn the promise first, and the mechanism becomes easy to remember. Hinglish me: database ka kaam hai data ko ek baar, sahi jagah, surakshit rakhna — aur sabko ek saath kaam karne dena bina gadbad ke.

Learning objectives

Explain why a DBMS is needed instead of a file system, and describe the three-schema architecture and data independence.
Design ER diagrams: entities, attributes, keys, relationships, cardinality, participation and weak entities, and convert an ER design to relations.
Use the relational model: domains, tuples, integrity constraints and relational algebra operations, including selection, projection, join and division.
Write SQL: DDL, DML, DCL and TCL statements, joins, subqueries, grouping, aggregation, views and basic index use.
Find functional dependencies, compute attribute closure, and normalise a relation to 1NF, 2NF, 3NF and BCNF, explaining each violation.
Explain transactions and ACID, schedules and serialisability, two-phase locking, timestamp ordering, deadlock and its prevention.
Explain recovery: logging, write-ahead logging, checkpoints and the roles of undo and redo.
Apply all of the above to exam-style numerical, SQL and design questions.

Why a database, not files. In a file system, each application owns its files, so the same fact is copied in many places, formats disagree, and a change in one copy leaves the others stale. A DBMS centralises control: data is stored once, described once in a schema, and shared through a controlled interface. That gives data independence (applications do not break when storage changes), concurrent access with control, recovery after failure, security through authorisation, and integrity through constraints. The cost is real too: a DBMS is complex, needs administration, and can be slower for a single simple file task. The exam tests this trade-off: a DBMS is chosen when data is shared, large, structured and must stay consistent, not for every small one-off list.

Three-schema architecture and data independence. The external level is what each user group sees: views tailored to their needs. The conceptual level is the whole database design: entities, relations and constraints, independent of storage. The internal level is how data is physically stored: files, indexes, record layouts. Logical data independence means the conceptual schema can change (for example, adding a column) without rewriting applications. Physical data independence means the internal storage can change (for example, adding an index) without changing the conceptual schema or applications. A database administrator (DBA) manages the schema, security, backup and performance; users and application programs use the external level only.

Data and the data model. Data are recorded facts; information is data placed in a context that answers a question. A data model is a set of concepts for describing data, its relationships and its constraints. The main models in this unit are the entity-relationship model (for design) and the relational model (for implementation). A schema is the design; an instance is the data stored at one moment. A database state must satisfy its constraints at all times; a state that breaks a constraint is not a valid state, even if the software accepted the rows.

ER model. An entity is a thing about which data is stored (Student, Course). An entity set is a collection of similar entities. An attribute is a property (name, date of birth). Attributes can be simple (one value, such as a roll number), composite (made of parts, such as a name split into first and last), derived (computed from others, such as age from date of birth), or multivalued (several values, such as phone numbers). A key is an attribute or set of attributes that identifies an entity. A superkey is any set that identifies uniquely; a candidate key is a minimal superkey; the primary key is the candidate key chosen for identification; an alternate key is a candidate key not chosen; a foreign key is an attribute that references the primary key of another (or the same) relation. A relationship connects entity sets (a student enrols in a course). Cardinality states how many entities participate: one-to-one, one-to-many, many-to-many. Participation can be total (every entity must participate) or partial (participation is optional). A weak entity cannot be identified by its own attributes alone and depends on an owner entity (for example, a dependent of an employee, identified by the employee plus the dependent's name). In an ER diagram, rectangles are entities, ellipses are attributes, diamonds are relationships, double rectangles and double diamonds mark weak entities and their identifying relationships, and underlined attributes mark keys.

From ER to relations. Each strong entity set becomes a relation with its simple attributes; composite attributes are split into their parts; multivalued attributes become a separate relation. Each relationship becomes either a foreign key (for one-to-one and one-to-many, placed on the many side) or a separate relation (for many-to-many, with the keys of both sides, which together form its key). A weak entity becomes a relation whose key includes the owner's key. Total participation is enforced by a NOT NULL foreign key; partial participation allows NULL. This mapping is mechanical, and exam questions usually test one step of it: where the foreign key goes, or what the key of the new relation is.

Relational model. A relation is a table with a heading (attribute names and domains) and a body (tuples, or rows). A domain is the set of allowed values for an attribute. A tuple is one row; its degree is the number of attributes, its cardinality is the number of tuples. The model has rules that matter for questions: tuples are unordered, attributes are unordered in principle, every value is atomic (this is 1NF, covered below), and no two tuples are identical. Integrity constraints: domain constraints restrict values to their domain; key constraints require uniqueness and minimality; entity integrity requires that no primary key value is NULL; referential integrity requires that every foreign key value either is NULL (where allowed) or matches an existing primary key value. A NULL is not zero and not an empty string: it means value unknown or not applicable, and comparisons with NULL give unknown, not true or false, which is why SQL uses three-valued logic.

Relational algebra. Relational algebra is the formal language behind SQL, and questions often ask what an expression returns. Selection (sigma, σ) filters rows by a condition: σ_age>20(Student) keeps only students older than 20. Projection (pi, π) picks columns and removes duplicates: π_name(Student) lists names only. Rename (rho, ρ) renames a relation or attribute. Set operations (union ∪, intersection ∩, difference −) need union-compatible relations, meaning the same number of attributes with compatible domains. Cartesian product (×) pairs every tuple of one relation with every tuple of the other. A join combines product with a condition: a theta join uses any comparison, an equijoin uses equality, and a natural join equates all commonly named attributes and drops the duplicate columns. Division (÷) answers for-all questions, such as students who have taken all courses in a given set. The classic exam trap is the difference between selection and projection: selection filters rows, projection picks columns. Another trap: natural join drops duplicate columns, while a plain product followed by selection does not.

SQL, part 1 — definition and manipulation. SQL statements fall into four groups. DDL (Data Definition Language) defines structure: CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX. DML (Data Manipulation Language) changes data: SELECT, INSERT, UPDATE, DELETE. DCL (Data Control Language) controls access: GRANT, REVOKE. TCL (Transaction Control Language) controls transactions: COMMIT, ROLLBACK, SAVEPOINT. A table definition states columns, types, PRIMARY KEY, FOREIGN KEY ... REFERENCES, UNIQUE, NOT NULL, CHECK and DEFAULT constraints. SELECT has the order: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. WHERE filters rows before grouping; HAVING filters groups after grouping — this distinction is tested often. Aggregate functions (COUNT, SUM, AVG, MIN, MAX) ignore NULLs except COUNT(*), which counts rows. DISTINCT removes duplicate result rows. ORDER BY sorts, with ASC as default.

SQL, part 2 — joins, subqueries and views. An INNER JOIN returns only matched pairs. A LEFT JOIN returns all left rows, with NULLs where no right row matches; RIGHT JOIN is the mirror; FULL JOIN returns all rows from both sides. A self-join joins a table to itself, using aliases to tell the two copies apart (for example, employees and their managers). A subquery is a query inside another: in WHERE it can use IN, EXISTS, ANY or ALL; a correlated subquery refers to the outer query and runs once per outer row, while a non-correlated subquery runs once. EXISTS is true when the subquery returns at least one row, and it is often the right tool for for-all and existence questions. A view is a stored query that looks like a table: it simplifies access and can hide columns, but updating through a view is restricted, especially for views built from joins or aggregations. An index speeds up lookups on a column at the cost of slower writes and extra storage; a primary key automatically gets an index in most systems, and an index on a rarely filtered column is usually a waste.

Functional dependencies and closure. A functional dependency X → Y means: whenever two tuples agree on X, they must agree on Y. X is the determinant, Y the dependent. Armstrong's axioms let new dependencies be derived: reflexivity (if Y is a subset of X, then X → Y), augmentation (if X → Y, then XZ → YZ), transitivity (if X → Y and Y → Z, then X → Z). From these follow union, decomposition and pseudotransitivity. The closure of an attribute set X, written X+, is all attributes determined by X; it is computed by starting with X and repeatedly adding the right-hand side of any dependency whose left-hand side is already in the set. A superkey is a set whose closure is all attributes; a candidate key is a minimal superkey. Closure is the reliable tool for key questions: do not guess keys, compute them. A canonical cover is a minimal equivalent set of dependencies, with no redundant dependency and no extraneous attribute; it is used when decomposing, and its details are less often examined than closure itself.

Normalisation, step by step. Normalisation removes redundancy that causes update, insertion and deletion anomalies. An update anomaly: changing a repeated fact in one place but not another leaves the data inconsistent. An insertion anomaly: a fact cannot be stored because another fact is missing (for example, a new department cannot be stored without a student). A deletion anomaly: deleting one fact accidentally deletes another (deleting the last student of a department loses the department). First normal form (1NF) requires atomic values: no repeating groups, no multivalued attributes in a single cell. Second normal form (2NF) requires 1NF plus no partial dependency: no non-key attribute may depend on only part of a composite key. Third normal form (3NF) requires 2NF plus no transitive dependency: no non-key attribute may depend on another non-key attribute. Boyce-Codd normal form (BCNF) is stricter than 3NF: every determinant must be a superkey. A relation can be in 3NF but not BCNF when a determinant is not a superkey — the classic case is a relation where two overlapping candidate keys exist. Fourth normal form (4NF) removes multivalued dependencies, and fifth normal form (5NF) removes join dependencies; both are mentioned far less often in this examination than 1NF through BCNF, but their definitions should be known in one sentence each. A good decomposition is lossless (the original relation is recovered by joining the parts) and dependency-preserving (every dependency can be checked inside one part without joining). Lossless decomposition into BCNF is always possible; dependency preservation into BCNF is not always possible, and that trade-off is a standard theory question.

Transactions and ACID. A transaction is a unit of work that must be treated as a whole. Atomicity: all of it happens or none of it does. Consistency: it moves the database from one valid state to another. Isolation: concurrent transactions behave as if they ran one after another, even though they overlap. Durability: once committed, its effects survive crashes. A transaction passes through states: active, partially committed, committed, failed, aborted. The problems that concurrency control must prevent are: lost update (two transactions read and write the same item, and one update is lost), dirty read (reading uncommitted data that is later rolled back), unrepeatable read (the same item read twice gives different values because another transaction changed it), and phantom read (a set of rows matching a condition changes because another transaction inserted or deleted rows). A schedule is the order of operations of concurrent transactions. A serial schedule runs transactions one after another. A serialisable schedule gives the same result as some serial schedule. Conflict serialisability is tested with a precedence graph: an edge Ti → Tj exists when Ti does an operation that conflicts with a later operation of Tj on the same item (read-write, write-read or write-write); the schedule is conflict-serialisable exactly when this graph has no cycle. View serialisability is wider than conflict serialisability, but it is harder to test and is rarely the required method in a short question.

Locking, timestamps and deadlock. Two-phase locking (2PL) requires a transaction to acquire all locks before releasing any: a growing phase (locks only acquired) followed by a shrinking phase (locks only released). 2PL guarantees conflict serialisability, but it does not by itself prevent deadlock or cascading rollback. Strict 2PL holds all exclusive locks until commit or abort, which also prevents cascading rollback, and it is the variant most systems use in practice. Shared locks allow reading; exclusive locks allow writing, and an exclusive lock conflicts with both. Timestamp ordering gives each transaction a timestamp and orders operations by it, rejecting or rolling back operations that arrive out of order. Deadlock needs all four Coffman conditions: mutual exclusion, hold and wait, no preemption, and circular wait. Break any one and deadlock cannot happen. Prevention schemes include wait-die (an older transaction waits, a younger one aborts and retries) and wound-wait (an older transaction wounds, i.e. aborts, a younger one; a younger one waits). Detection uses a wait-for graph: a cycle means deadlock, and a victim transaction is chosen and aborted to break it. Starvation is different from deadlock: a transaction waits indefinitely because others keep overtaking it, even though no cycle exists.

Recovery. Recovery restores the database after a crash using a log. The log records, for each transaction, its start, its writes (old value and new value) and its commit or abort. Write-ahead logging (WAL) is the central rule: a log record must be written to stable storage before the corresponding data change reaches the database. After a crash, recovery redoes committed transactions (redo: reapply their writes) and undoes uncommitted ones (undo: restore old values). A checkpoint periodically writes all modified buffers to disk and records the point in the log, so recovery only needs to work from the last checkpoint instead of from the beginning. Shadow paging is an alternative that keeps two page tables (current and shadow) and switches atomically on commit; it avoids a log but is rarely used in large systems because of its cost. Deferred update writes only at commit time, so recovery needs redo only; immediate update writes during the transaction, so recovery needs both undo and redo. The exam usually asks which of undo and redo is needed in each case, and the answer follows from when the writes reached disk.

Indexing and physical design, briefly. An index is a data structure that finds rows by a key without scanning the whole table. A primary index is built on the ordering key of a file; a clustering index groups rows with similar values together; a secondary index is built on any other attribute and needs an extra lookup. B-trees and their variant B+ trees are the standard index structure: they stay balanced as data grows, so lookups stay logarithmic, and in a B+ tree the leaf level is linked, which makes range scans efficient. A dense index has an entry for every search-key value; a sparse index has entries only for some. Choosing indexes is a trade-off: every index speeds some queries and slows every write to that table, so indexes belong on columns used in WHERE, JOIN and ORDER BY, not on every column.

Key definitions

DBMS: Software that stores, retrieves, protects and shares a structured collection of data under central control.
Schema / instance: A schema is the design of a database; an instance is the data stored at one moment.
Data independence: Logical: the conceptual schema can change without rewriting applications. Physical: storage can change without changing the conceptual schema.
Entity / weak entity: An entity is a thing about which data is stored. A weak entity cannot be identified by its own attributes alone and needs an owner entity.
Candidate key / primary key / foreign key: A candidate key is a minimal set that identifies tuples uniquely. The primary key is the chosen candidate key. A foreign key references a primary key in another (or the same) relation.
Entity integrity / referential integrity: Entity integrity: no primary key value is NULL. Referential integrity: every foreign key value is NULL (where allowed) or matches an existing primary key.
Selection / projection: Selection (σ) filters rows by a condition. Projection (π) picks columns and removes duplicates.
Functional dependency X → Y: Whenever two tuples agree on X, they must agree on Y.
Closure X+: All attributes determined by the set X, computed by repeatedly applying functional dependencies.
1NF / 2NF / 3NF / BCNF: 1NF: atomic values. 2NF: 1NF + no partial dependency on part of a key. 3NF: 2NF + no transitive dependency. BCNF: every determinant is a superkey.
ACID: Atomicity, Consistency, Isolation, Durability — the four guarantees of a transaction.
Conflict serialisability: A schedule whose precedence graph has no cycle, so it is equivalent to some serial schedule.
Two-phase locking (2PL): Acquire all locks before releasing any: a growing phase followed by a shrinking phase. Guarantees conflict serialisability.
Deadlock: Transactions waiting on each other in a cycle, so none can proceed. Needs all four Coffman conditions.
Write-ahead logging (WAL): The rule that a log record is written to stable storage before the corresponding data change reaches the database.

Important concepts

Store each fact once. Every normal form is a way of enforcing that idea against one kind of repetition.
Selection filters rows, projection picks columns. Confusing the two is the commonest relational algebra error.
Compute keys by closure, do not guess them. A candidate key is minimal: remove any attribute and it must stop being a superkey.
Partial dependency is about part of a key; transitive dependency is about a non-key attribute depending on another non-key attribute. Name the violation correctly, or the normal form answer is wrong.
Serialisability is about equivalence to a serial order, not about running transactions one at a time. A concurrent schedule can be serialisable.
2PL guarantees serialisability, not freedom from deadlock. Strict 2PL additionally avoids cascading rollback.
Recovery is undo for the uncommitted and redo for the committed — and WAL is what makes both possible after a crash.

Examples

Step-by-step — ER to relations. Entities: Student(student_id, name) and Course(course_id, title). Relationship: Enrols (many-to-many, with attribute grade). Mapping: Students(student_id, name), Courses(course_id, title), Enrolments(student_id, course_id, grade). The many-to-many relationship must become its own relation, and its key is the pair of foreign keys. If Enrols had been one-to-many (each student has one advisor), the foreign key would instead sit on the many side: Students(student_id, name, advisor_id).
Step-by-step — attribute closure. Dependencies: A → B, B → C, C → D. Closure of {A}: start {A}; A → B adds B; B → C adds C; C → D adds D. So A+ = {A, B, C, D}, which means A is a superkey of a relation on ABCD, and if no smaller subset determines all attributes, A is a candidate key. Closure answers key questions mechanically: keep adding until nothing new appears.
Step-by-step — normalisation (worked fully). Relation R(student_id, course_id, student_name, course_title, instructor_name, instructor_room, grade). Dependencies: student_id → student_name; course_id → course_title, instructor_name; instructor_name → instructor_room; {student_id, course_id} → grade. Key: {student_id, course_id}. Violations: student_name depends on part of the key (partial, so not 2NF); course_title and instructor_name also depend on part of the key; instructor_room depends on instructor_name, a non-key attribute (transitive, so not 3NF). Decomposition to 3NF: Students(student_id, student_name); Courses(course_id, course_title, instructor_name); Instructors(instructor_name, instructor_room); Enrolments(student_id, course_id, grade). Check: in Courses, the only determinant is course_id, a superkey, so it is also in BCNF; in Instructors, instructor_name is the key; in Enrolments, grade depends on the whole key. The join of the four parts recovers R, so the decomposition is lossless, and every dependency is checked inside one part, so it is dependency-preserving.
Step-by-step — precedence graph. Schedule: T1 reads A, T2 reads A, T1 writes A, T2 writes A. Conflicts on A: T1's read conflicts with T2's later write (edge T1 → T2), and T2's read conflicts with T1's later write (edge T2 → T1). The graph has a cycle, so the schedule is not conflict-serialisable. If instead T2 only read A and never wrote it, there would be no cycle from those operations alone. Always list conflicts item by item; a graph drawn from memory is where errors creep in.
Step-by-step — SQL, WHERE versus HAVING. Find departments with more than 10 employees: SELECT dept, COUNT() FROM Emp GROUP BY dept HAVING COUNT() > 10. Using WHERE COUNT(*) > 10 is wrong, because WHERE runs before grouping and cannot see aggregates. Conversely, filtering individual rows (salary > 50000) belongs in WHERE, not HAVING. The rule is: row filter → WHERE, group filter → HAVING.
Step-by-step — average access and page-style arithmetic is not needed here, but one DBMS arithmetic check is: a relation with a composite key {student_id, course_id} and a non-key attribute that depends only on student_id is a partial dependency, full stop, regardless of how many other attributes the relation has.
Worked SQL example — students who took every course in a set. With Enrolments(student_id, course_id) and Required(course_id), the division Enrolments ÷ Required gives student_ids present for every required course. In SQL the same idea is written with NOT EXISTS twice: students for whom there is no required course that they have not taken. Division questions are the exam's way of testing for-all thinking.

Applications

University systems: student enrolment, grades, fees and timetables, where one fact (a student's name, a course's instructor) must not be repeated inconsistently.
Banking: transfers as transactions, where atomicity and durability are not optional — money must not appear, disappear or be half-moved by a crash.
Airline and railway booking: concurrent seat booking, where locking and isolation stop two users buying the same seat.
E-commerce: catalogues, orders and inventory, where referential integrity stops an order pointing at a product that no longer exists.
Examination systems themselves: question banks, attempts and results, where a student's attempt must survive a crash and be graded from consistent data.

Formulas

Closure: X+ starts as X; repeatedly add Y for every dependency A → B with A already in the set, until no change.
Superkey test: X is a superkey exactly when X+ contains every attribute of the relation. Candidate key: a superkey with no proper subset that is also a superkey.
Normal form tests: 2NF fails on partial dependency (non-key attribute depends on part of a key). 3NF fails on transitive dependency (non-key attribute depends on another non-key attribute). BCNF fails when any determinant is not a superkey.
Conflict serialisability: build the precedence graph (edge Ti → Tj for a conflicting operation of Ti before Tj on the same item); serialisable exactly when the graph is acyclic.
Index cost rule (qualitative, not a formula): each index speeds lookups on its columns and slows every insert, update and delete on the table.

Table: ER symbols (quick reference)

Symbol: Rectangle; Meaning: Entity set
Symbol: Double rectangle; Meaning: Weak entity set
Symbol: Ellipse; Meaning: Attribute
Symbol: Underlined ellipse; Meaning: Key attribute
Symbol: Double ellipse; Meaning: Multivalued attribute
Symbol: Dashed ellipse; Meaning: Derived attribute
Symbol: Diamond; Meaning: Relationship set
Symbol: Double diamond; Meaning: Identifying relationship for a weak entity

Table: Normal forms: what each one forbids

Form: 1NF; Requires: Atomic values only; Violation that breaks it: Repeating group or multivalued value in one cell
Form: 2NF; Requires: 1NF + no partial dependency; Violation that breaks it: Non-key attribute depends on part of a composite key
Form: 3NF; Requires: 2NF + no transitive dependency; Violation that breaks it: Non-key attribute depends on another non-key attribute
Form: BCNF; Requires: Every determinant is a superkey; Violation that breaks it: A determinant that is not a superkey (possible with overlapping candidate keys)
Form: 4NF; Requires: BCNF + no multivalued dependency; Violation that breaks it: A non-trivial multivalued dependency that is not a superkey

Table: SQL joins: what each returns

Join: INNER JOIN; Returns: Only rows matched in both tables
Join: LEFT JOIN; Returns: All left rows; NULLs where no right match exists
Join: RIGHT JOIN; Returns: All right rows; NULLs where no left match exists
Join: FULL JOIN; Returns: All rows from both tables; NULLs where unmatched
Join: CROSS JOIN; Returns: Every combination of rows (Cartesian product)

Table: Concurrency anomalies

Anomaly: Lost update; What happens: Two transactions update the same item; one update overwrites and loses the other
Anomaly: Dirty read; What happens: A transaction reads data written by an uncommitted transaction that later aborts
Anomaly: Unrepeatable read; What happens: The same item read twice in one transaction gives different values
Anomaly: Phantom read; What happens: A set of rows matching a condition changes because another transaction inserted or deleted rows

Table: Recovery: undo and redo by write timing

Write timing: Deferred update (write only at commit); Undo needed?: No — uncommitted work never reached disk; Redo needed?: Yes — committed work may not have reached disk
Write timing: Immediate update (write during transaction); Undo needed?: Yes — uncommitted writes may be on disk; Redo needed?: Yes — committed writes may not all be on disk

Diagrams (described)

Diagram (described): Three-schema architecture as three stacked layers. Top layer: External (views for different user groups). Middle: Conceptual (whole database design). Bottom: Internal (physical storage, files, indexes). Arrows between layers are labelled with the mapping, and the caption reads: change one layer without rewriting the layers above it — that is data independence.

Diagram (described): ER example. Rectangles Student and Course joined by a diamond Enrols, with an ellipse grade on the diamond. Student has underlined student_id and ellipses name; Course has underlined course_id and ellipse title. The double line from Enrols to each side would mark total participation; a single line marks partial participation.

Flowchart (described): Normalisation decision flow. Start: is every value atomic? No → 1NF fix (split repeating groups). Is there a partial dependency on part of a key? Yes → split into 2NF parts. Is there a transitive dependency? Yes → split again. Is every determinant a superkey? No → split to BCNF. At each split, check the decomposition is lossless (the parts join back to the original) before continuing.

Diagram (described): Precedence graph for a non-serialisable schedule. Two nodes T1 and T2 with an arrow T1 → T2 (a conflict on item A) and an arrow T2 → T1 (a later conflict on the same item). The cycle is circled and labelled: cycle = not conflict-serialisable.

Diagram (described): Transaction states. Active → partially committed → committed, with a branch from active and partially committed to failed → aborted. The point of the diagram: a transaction is committed only after the partially committed state, and aborted work must be undone.

Diagram (described): Write-ahead logging timeline. Two horizontal lines: Log (top) and Database (bottom). An arrow from a log record down to the database change is labelled: log first, data second. A crash mark between them shows why recovery can still redo committed work and undo uncommitted work.

Exam points

Selection filters rows, projection picks columns. If a question asks for columns, it is projection; if it asks for rows meeting a condition, it is selection.
Partial dependency → 2NF violation. Transitive dependency → 3NF violation. Determinant not a superkey → BCNF violation. Name the violation, then the form.
WHERE filters rows before grouping; HAVING filters groups after grouping. Aggregates belong in HAVING, row conditions in WHERE.
COUNT(*) counts rows including NULLs; COUNT(column) counts non-NULL values in that column.
A precedence graph with a cycle means not conflict-serialisable. No cycle means serialisable. Draw conflicts item by item.
2PL guarantees serialisability; strict 2PL also avoids cascading rollback. Neither guarantees freedom from deadlock.
Deadlock needs all four Coffman conditions. Break one — for example, by ordering resource requests (no circular wait) — and deadlock cannot occur.
WAL: log before data. Deferred update needs redo only; immediate update needs undo and redo.
Natural join equates common attributes and drops duplicates; a theta join keeps both sides' columns unless projection removes them.

Common mistakes

Calling a transitive dependency a partial dependency (or the reverse). Partial is about part of a key; transitive is about a chain through another non-key attribute.
Putting aggregates in WHERE. Aggregates are computed after grouping, so group conditions belong in HAVING.
Assuming a relation in 3NF is automatically in BCNF. BCNF is stricter, and overlapping candidate keys are exactly where 3NF passes and BCNF fails.
Treating NULL as zero or as an empty string. NULL means unknown or not applicable, and comparisons with NULL yield unknown, which is why WHERE filters such rows out.
Assuming 2PL prevents deadlock. It prevents non-serialisable schedules, not cycles of waiting.
Forgetting that a many-to-many relationship needs its own relation in the ER-to-relational mapping, with the pair of keys as its key.
Counting network and broadcast addresses as usable hosts — that is a networks question, but the same off-by-two habit appears in DBMS counting questions, so check what is excluded before answering.

Frequently asked

Why normalise at all, if joins cost performance?

Normalisation prevents update, insertion and deletion anomalies and keeps each fact in one place. In practice, designs are sometimes denormalised deliberately for read speed, but that is a conscious trade-off made after normalising, not a reason to skip normalisation.

Is BCNF always the goal?

Not always. BCNF decomposition is always lossless, but it is not always dependency-preserving. When a dependency would be lost, a designer may stop at 3NF and accept the BCNF violation, documenting the trade-off.

What is the difference between a key and an index?

A key is a logical concept: a set of attributes that identifies tuples and enforces constraints. An index is a physical structure that speeds up finding rows. A primary key usually gets an index, but keys and indexes are not the same thing.

How do I find a candidate key quickly in the examination?

Compute closures. Start from attribute sets that appear on the left of dependencies, add attributes until the closure covers all attributes, then try removing attributes one at a time: if the smaller set still covers everything, the larger set was not minimal.

Does a view store data?

No, in the usual case a view is a stored query, not stored data. Querying the view runs the underlying query. That is why views can hide columns and simplify access without duplicating data.

Revision summary

A database keeps each fact once, answers high-level questions, lets many users work together, and survives crashes. Design with ER (entities, keys, cardinality, weak entities), implement with relations (integrity constraints, algebra: selection filters rows, projection picks columns), query with SQL (WHERE before grouping, HAVING after; joins matched, left, right, full). Normalise by dependency: partial breaks 2NF, transitive breaks 3NF, a non-superkey determinant breaks BCNF; compute keys by closure, never by guessing. Transactions promise ACID; serialisability is tested by an acyclic precedence graph; 2PL (strict, in practice) enforces it without preventing deadlock, which needs all four Coffman conditions. Recovery runs on write-ahead logging: redo the committed, undo the uncommitted.