Database Management Systems
Model and keys
A relational database stores data in tables (relations) made of rows (tuples) and columns (attributes). A superkey uniquely identifies a row; a candidate key is a minimal superkey; the primary key is the chosen candidate and must be unique and not null; alternate keys are the candidates not chosen; a composite key spans two or more attributes; a foreign key references a primary key in another table and enforces referential integrity.
Normalisation
First normal form requires atomic values and no repeating groups. Second normal form adds the removal of partial dependencies, where an attribute depends on only part of a composite key. Third normal form adds the removal of transitive dependencies, where a non-key attribute depends on another non-key attribute.
SQL and administration
DDL covers CREATE, ALTER and DROP; DML covers SELECT, INSERT, UPDATE and DELETE; DCL covers GRANT and REVOKE; TCL covers COMMIT and ROLLBACK. Joins may be inner, left, right or full outer. WHERE filters rows before grouping while HAVING filters groups. ACID means atomicity, consistency, isolation and durability. The DBA handles security, tuning, backup and recovery, the data dictionary, views and stored procedures.
Trap: being in 2NF does not guarantee 3NF. Also, DELETE can be rolled back and fires triggers, TRUNCATE is faster and resets the table, and DROP removes the structure itself.
Worked example: Student(StudentID, CourseID, StudentName, CourseName) is in 1NF but not 2NF, since StudentName depends only on StudentID and CourseName only on CourseID. Split it into Student, Course and Enrolment tables. Rule of thumb: every non-key attribute must depend on the key, the whole key and nothing but the key.