Databases and data modelling
Databases and data modelling tests the relational model, entity–relationship design, normalisation to third normal form, SQL and the role of the DBMS. You must be able to justify a design, not just draw it.
Relational concepts
An entity becomes a table, an attribute a column and a tuple a row. A primary key uniquely identifies a row and cannot be null; a candidate key is any attribute that could serve as primary key; a composite key uses two or more attributes. A foreign key references a primary key in another table and enforces referential integrity. Relationships are one-to-one, one-to-many or many-to-many; a many-to-many relationship must be resolved with a junction table containing both foreign keys.
Normalisation
First normal form requires atomic values and no repeating groups. Second normal form requires 1NF and no partial dependency on part of a composite key. Third normal form requires 2NF and no transitive dependency of a non-key attribute on another non-key attribute.
Worked example
Enrolment(StudentID, StudentName, CourseID, CourseName) assumes each student takes one course. StudentName depends on StudentID, and CourseName depends on CourseID, which in turn depends on StudentID: transitive dependencies. To reach 3NF, split into Student(StudentID, StudentName, CourseID) and Course(CourseID, CourseName). StudentID is the primary key of Student, and CourseID is a foreign key there.
SQL and the DBMS
SELECT retrieves columns, WHERE filters rows, GROUP BY forms groups, HAVING filters groups, ORDER BY sorts, and JOIN matches rows across tables on a related column. INSERT, UPDATE and DELETE modify data; CREATE and ALTER are DDL. A DBMS provides concurrency control, backup and recovery, access control, a data dictionary and views. Transactions should be atomic, consistent, isolated and durable.
Traps
Using WHERE where HAVING is needed; writing = NULL instead of IS NULL; assuming a foreign key must be unique; leaving a many-to-many relationship unresolved; saying 2NF removes transitive dependencies.