Database Management Systems and SQL
This domain tests the relational model, keys, normalization, and writing and reading SQL statements.
Keys and integrity
A super key uniquely identifies a tuple; a candidate key is a minimal super key; the primary key is the chosen candidate key and cannot be NULL. A foreign key references another table's primary key and enforces referential integrity. Entity integrity forbids a NULL primary key.
Normalization
- 1NF: atomic values, no repeating groups.
- 2NF: 1NF plus no partial dependency on part of a composite key.
- 3NF: 2NF plus no transitive dependency (a non-key attribute depending on another non-key attribute).
SQL categories
DDL: CREATE, ALTER, DROP. DML: SELECT, INSERT, UPDATE, DELETE. TCL: COMMIT, ROLLBACK. DCL: GRANT, REVOKE.
Worked example
SELECT dept, COUNT(*) FROM emp WHERE sal > 20000 GROUP BY dept HAVING COUNT(*) >= 2;
Evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. WHERE filters rows before grouping; HAVING filters groups after aggregation. The result has one row per department with at least two employees earning above 20000, showing the department and the count.
Traps
Using WHERE instead of HAVING for an aggregate condition; writing = NULL instead of IS NULL; believing COUNT(col) counts NULLs (it does not, while COUNT(*) does); confusing DELETE (removes rows) with DROP (removes the table); assuming a foreign key must be unique.