Keys in the relational model

A super key is any set of attributes that uniquely identifies each row. A candidate key is a minimal super key: remove any attribute and uniqueness is lost. The primary key is the candidate key chosen by the designer; it cannot contain NULL, which is the entity integrity rule. Candidate keys not chosen become alternate keys. A composite key uses more than one attribute.

A foreign key in one table refers to the primary or a candidate key of another table, or of the same table in a self-reference. Referential integrity requires every non-NULL foreign key value to match an existing referenced value. Unlike a primary key, a foreign key may be NULL and may repeat.

Functional dependency

X → Y means that a value of X determines exactly one value of Y. A prime attribute is part of some candidate key; every other attribute is non-prime. Normal forms are defined by which dependencies are allowed, so identify the candidate keys before testing any normal form.

Normal forms

• 1NF: every attribute holds atomic values; no repeating groups or multi-valued cells.
• 2NF: in 1NF, and no non-prime attribute depends on only part of a composite candidate key. A 1NF table whose candidate keys are all single attributes is automatically in 2NF.
• 3NF: for every non-trivial dependency X → A, either X is a super key or A is prime. In practice this removes transitive dependencies such as EmpID → DeptID → DeptName.
• BCNF: for every non-trivial dependency X → A, X must be a super key. Every BCNF table is in 3NF; the reverse is not always true.
• 4NF removes non-trivial multi-valued dependencies; 5NF deals with join dependencies.

Example: in OrderLine(OrderID, ProductID, Qty, ProductName) with key (OrderID, ProductID), ProductName depends on ProductID alone. That partial dependency breaks 2NF; moving ProductName to a Product table fixes it. Normalisation reduces insertion, update and deletion anomalies; denormalisation is sometimes chosen deliberately for read performance.

SQL command categories

• DDL defines structure: CREATE, ALTER, DROP, TRUNCATE, RENAME.
• DML changes data: INSERT, UPDATE, DELETE, MERGE. SELECT is classed as DML in older texts and as DQL in newer ones.
• DCL controls access: GRANT, REVOKE.
• TCL manages transactions: COMMIT, ROLLBACK, SAVEPOINT.

DELETE removes chosen rows through a WHERE clause and can be rolled back before commit. TRUNCATE removes all rows, keeps the table structure, accepts no WHERE clause and is faster; in Oracle it commits implicitly. DROP removes the table itself along with its data.

Joins, grouping and transactions

An inner join returns only matching rows. A left outer join keeps every row of the left table and fills unmatched right-side columns with NULL; a right outer join does the reverse; a full outer join keeps unmatched rows from both. A cross join returns the Cartesian product, so tables of m and n rows give m × n rows. A self join joins a table to itself, for example employee to manager.

WHERE filters rows before grouping and cannot use aggregate functions directly; HAVING filters groups after GROUP BY. Transactions follow ACID: atomicity, consistency, isolation and durability. Durability is the guarantee that committed changes survive a system failure.

Exam method

• Find all candidate keys first; then mark prime and non-prime attributes.
• Test 2NF only against composite keys; test 3NF and BCNF dependency by dependency.
• For SQL statements, ask whether the command changes structure, data, access or transaction state.

Key points to remember

Practice: 10 MCQs

Attempt all ten questions, then press Submit. The correct answer and a short explanation appear under every question, followed by your score analysis.