Database Management Systems and SQL
Database Management Systems and SQL tests the relational model, keys, normalization, ER concepts and query writing. Expect output-based SQL and key identification.
Core rules
- A primary key is unique and not null; candidate keys are possible primary keys; a foreign key references another table and enforces referential integrity.
- Normalization: 1NF requires atomic values; 2NF removes partial dependency on a composite key; 3NF removes transitive dependency; BCNF requires every determinant to be a candidate key.
- SQL logical order: FROM/JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY. WHERE filters rows before grouping; HAVING filters groups.
- INNER JOIN returns matching rows; LEFT JOIN keeps all left-table rows; NULL is not equal to anything, so use IS NULL.
- DDL includes CREATE, ALTER and DROP; DML includes INSERT, UPDATE and DELETE; DCL includes GRANT and REVOKE; TCL includes COMMIT and ROLLBACK.
Worked example
SELECT dept, COUNT() FROM emp WHERE salary > 30000 GROUP BY dept HAVING COUNT() >= 2; First rows with salary at most 30000 are removed; surviving rows are grouped by dept; groups with at least two rows are kept; then dept and count are returned. Reasoning: WHERE cannot use the COUNT aggregate, but HAVING can filter on it.
Traps
COUNT(*) counts rows including NULLs; COUNT(column) ignores NULLs. DELETE removes rows; DROP removes the table structure. A foreign key may be NULL unless declared NOT NULL.