Relational Database Management Systems and SQL
What this domain tests
You must explain the relational model, identify keys and constraints, justify normalisation, and write or interpret SQL statements using SELECT with filtering, grouping and joins.
Core rules
- A relation is a table; a tuple is a row, an attribute a column. A DBMS reduces redundancy and enforces consistency, sharing, security and integrity.
- Keys: super key, candidate key, primary key (unique and not NULL), alternate key, foreign key (referential integrity), composite key.
- Normal forms: 1NF needs atomic values; 2NF removes partial dependency on part of a composite key; 3NF removes transitive dependency on non-key attributes.
- SQL families: DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE), DQL (SELECT), DCL (GRANT, REVOKE), TCL (COMMIT, ROLLBACK).
- Clause order: SELECT ... FROM ... WHERE ... GROUP BY ... HAVING ... ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation.
- Aggregates: COUNT, SUM, AVG, MIN, MAX. Compare NULL with IS NULL, never with =.
Worked example
SELECT dept, AVG(sal) FROM emp WHERE sal > 10000 GROUP BY dept HAVING COUNT(*) >= 3 ORDER BY AVG(sal) DESC; Rows with salary above 10000 are kept first; they are grouped by department; groups with fewer than three such employees are dropped; each surviving group prints its department and average salary, highest average first.
Traps that cost marks
- Putting an aggregate condition in WHERE instead of HAVING.
- Confusing DELETE (removes rows, table remains) with DROP (removes the table).
- Writing marks = NULL instead of marks IS NULL.
- Assuming COUNT(column) counts NULLs; it does not, while COUNT(*) does.
- Selecting a non-grouped column alongside an aggregate.