Database Fundamentals and Relational Concepts
This domain tests the theory behind relational storage: how tables, keys, and relationships are structured, why normalisation exists, and what integrity rules protect data quality. Questions often present a small schema and ask which key or relationship applies.
Tables, rows, and keys
- A table (relation) holds rows (records, tuples) and columns (fields, attributes). Each column has a declared data type and should hold one kind of value.
- A primary key uniquely identifies each row and cannot be null. A candidate key is any column set that could serve; the chosen one becomes primary and the rest become alternate keys.
- A composite key combines two or more columns; a surrogate key is a system-generated identifier with no business meaning, such as an auto-numbered ID.
- A foreign key is a column in one table holding a primary key value from another table, and it establishes the relationship.
Relationships and integrity
Cardinality describes how many rows relate: one-to-one, one-to-many (the common case, such as one customer to many orders), and many-to-many, which requires a junction table holding both foreign keys. Referential integrity prevents a child row from pointing at a parent that does not exist, and blocks deleting a parent that still has children unless cascading actions are defined. Entity integrity requires primary keys to be unique and non-null.
Normalisation
First normal form requires atomic values with no repeating groups. Second normal form removes partial dependencies, where a non-key column depends on only part of a composite key. Third normal form removes transitive dependencies, where a non-key column depends on another non-key column.
Worked example
An Orders table stores OrderID, CustomerID, CustomerName, and CustomerCity. CustomerName and CustomerCity depend on CustomerID, not on OrderID, so the table violates third normal form and invites update anomalies. The fix is a Customers table keyed by CustomerID, with the Orders table keeping CustomerID as a foreign key.
Traps that cost marks
- Assuming any unique column is automatically the primary key.
- Treating NULL, an empty string, and zero as the same value.
- Implementing a many-to-many relationship directly without a junction table.