Data modelling & ER diagrams 19
- Entity-Relationship Diagram:
Diagram of the entities in a system and the relationships between them; the step before turning the design into tables. PP 03
- Entity:
A distinct type of thing the database stores data about (patient, car, order); usually becomes a table. Also in Read 01. PP 01
- Attribute:
A property of an entity; becomes a column in the table. Read 01
- Tuple:
A single row of a table. Read 01
- Relationship:
How two entities are associated (for example a customer places orders). Also in Read 01. PP 01
- Cardinality:
How many rows on one side relate to rows on the other: 1:1, 1:N or M:N. Also in Read 01. PP 04
- One-to-One Relationship:
Each row on either side has at most one partner on the other side. PS 01
- One-to-Many Relationship:
One row on the parent side relates to many rows on the child side; implemented with a foreign key in the child table. PP 02
- Many-to-Many Relationship:
Rows on both sides can relate to many rows on the other; resolved with a junction table. PP 02
- Junction Table:
Table holding two foreign keys that turns a many-to-many relationship into two one-to-many ones. Also in Read 01. PP 02
- Weak Entity:
An entity that cannot be identified without its owner entity; its key includes the owner's key. PS 01
- Associative Entity:
An entity that models a many-to-many relationship and carries its own attributes (for example Appointments). PS 01
- Conceptual Model:
ERD of entities and relationships before keys and tables are added. PS 01
- Logical Model:
The conceptual model turned into tables with primary and foreign keys; still independent of the DBMS. PS 01
- Logical ERD:
An ERD at the logical level: tables, columns, data types and PK/FK markers (the diagram form of the logical model). See also Logical Model. PS 02
- Normalisation:
Organising data into tables to reduce redundancy and update anomalies. Read 01
- First Normal Form:
Every cell holds one atomic value and there are no repeating groups. Read 01
- Second Normal Form:
1NF plus no partial dependency of a non-key column on part of a composite key. Read 01
- Third Normal Form:
2NF plus no transitive dependency between non-key columns. Read 01
Keys & constraints 16
- Primary Key:
Column or columns that uniquely identify each row; unique and never NULL. Also in Read 01. PP 03
- Foreign Key:
Column that references a key in another table, linking child rows to a parent and enforcing referential integrity. Also in Read 01. PP 01
- Composite Primary Key:
A primary key made of two or more columns, typical for junction tables. PP 04
- Surrogate Key:
An artificial id column (often AUTO_INCREMENT) used as the primary key instead of a natural one. PP 04
- Referential Integrity:
The rule that every foreign key value must exist in the parent table (or be NULL), so no orphan rows appear. PP 01
- Constraint:
A rule the database enforces on column values or table data. Also in Read 01. PP 01
- UNIQUE Constraint:
No two rows may share a value in the column (or column combination); NULLs are allowed. Also in Read 01. PP 01
- NOT NULL Constraint:
The column may not hold NULL; a value is required. Also in Read 01. PP 02
- CHECK Constraint:
Restricts column values to those satisfying a condition. Also in Read 01. PP 02
- DEFAULT Constraint:
Value stored automatically when an INSERT supplies none. Also in Read 01. PP 03
- AUTO_INCREMENT:
MySQL column attribute that generates the next numeric id automatically. Also in Read 01. PP 03
- Database Index:
Lookup structure on a column that speeds up reads and joins at some cost to writes. Also in Read 01. PP 01
- ON DELETE CASCADE:
Referential action: deleting a parent row also deletes its child rows. Read 01
- SET NULL:
Referential action: deleting the parent sets the child's foreign key to NULL. Read 01
- RESTRICT:
Referential action: blocks deleting or changing a parent row that still has children. Read 01
- Data Type:
The kind of value a column stores (INT, VARCHAR, DATE and so on), chosen at table creation. PS 02
DDL, DML & statements 9
- DDL:
SQL that defines or changes structure: CREATE, ALTER, DROP, TRUNCATE. Read 01
- DML:
SQL that reads or changes rows: SELECT, INSERT, UPDATE, DELETE. Read 01
- CREATE TABLE:
Creates a table with its columns, types and constraints. Also in Read 01. PS 02
- ALTER TABLE:
Changes an existing table, for example adding a column or a constraint. Also in Read 01. PP 04
- DROP TABLE:
Deletes a table together with its data. Read 01
- TRUNCATE TABLE:
Removes all rows but keeps the table structure. Read 01
- INSERT INTO:
Adds rows to a table; columns with a default or AUTO_INCREMENT can be left out of the column list. Also in Read 01. PP 03
- UPDATE:
Modifies existing rows. Read 01
- DELETE:
Removes rows. Read 01
Querying basics 19
- SELECT:
Chooses the columns (or expressions) in the output. Also in Read 01. PS 03
- FROM:
Names the source tables of a query. Read 01
- WHERE:
Filters individual rows before grouping; cannot use aggregates. Also in Read 01. PP 01
- Alias:
Temporary name for a column or table, given with the AS keyword. Also in Read 01. PS 03
- DISTINCT:
Removes duplicate rows from the result. Also in Read 01. PP 04
- ORDER BY:
- LIMIT:
Restricts how many rows are returned. Also in Read 01. PS 03
- OFFSET:
Skips the first N rows, usually with LIMIT for paging. Read 01
- Logical Order of Execution:
The order SQL evaluates clauses: FROM, JOIN, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT. Read 01
- AND:
True only if all conditions hold. Read 01
- OR:
True if any condition holds. Read 01
- NOT:
Negates a condition. Read 01
- IN:
Matches any value in a list or subquery result. Read 01
- BETWEEN:
Inclusive range test. Read 01
- LIKE:
Pattern match using % (any characters) and _ (one character). Read 01
- CONCAT Function:
Joins strings into one, for example first and last name. PS 04
- CASE:
If-else expression inside a query. Read 01
- CAST:
Converts a value to another data type. Read 01
- DATEDIFF:
Number of days between two dates (MySQL). Read 01
Aggregation & grouping 9
- Aggregate Function:
Summarises many rows into one value (COUNT, SUM, AVG, MIN, MAX). Also in Read 01. PP 01
- COUNT:
Counts rows or non-NULL values; COUNT(DISTINCT col) counts unique values. Also in Read 01. PS 04
- COUNT(*) vs COUNT(column):
COUNT(*) counts every row; COUNT(column) skips NULLs in that column. Read 01
- SUM:
- AVG:
- MIN:
Lowest value. Read 01
- MAX:
Highest value. Read 01
- GROUP BY:
Forms groups of rows so aggregates are computed per group. Also in Read 01. PP 01
- HAVING:
Filters groups after aggregation (WHERE filters rows before). Also in Read 01. PP 01
Joins & set operations 16
- JOIN:
Combines rows from two or more tables. Also in Read 01. PS 03
- ON:
The condition that matches rows in a join. Read 01
- INNER JOIN:
- LEFT JOIN:
All left rows plus matching right rows; unmatched rows get NULLs on the right. Also in Read 01. PP 01
- RIGHT JOIN:
All right rows plus matching left rows. Also in Read 01. PP 01
- FULL OUTER JOIN:
All rows from both sides, matched where possible; MySQL has no keyword and emulates it with UNION. PP 01
- CROSS JOIN:
Cartesian product: every left row paired with every right row. Also in Read 01. PP 02
- Self Join:
Joining a table to itself, using two aliases. PS 04
- Anti-Join:
LEFT JOIN plus IS NULL on the right key to find rows with no match. Read 01
- JOIN Fan-Out:
Row multiplication when joining one-to-many tables, which inflates counts and sums if not handled. PS 04
- Relational Model:
The relational way of storing data: entities as tables, attributes as columns, tuples as rows, linked by relationships with a cardinality. Also in Read 01. PP 03
- Set Operations:
UNION, UNION ALL, INTERSECT and EXCEPT: operators that combine two SELECT results with matching columns. Also in Read 01. PP 01
- UNION:
Combines two SELECT results and drops duplicates. Also in Read 01. PP 01
- UNION ALL:
Combines two SELECT results and keeps duplicates. Also in Read 01. PP 01
- INTERSECT:
Rows that appear in both queries (MySQL 8.0.31 and later). Read 01
- EXCEPT:
Rows in the first query but not the second (MySQL 8.0.31 and later). Read 01
Subqueries, NULL & advanced SQL 21
- Subquery:
- Scalar Subquery:
A subquery that returns exactly one value. Also in Read 01. PS 04
- Correlated Subquery:
A subquery that references the outer query and runs per outer row. Also in Read 01. PS 04
- Derived Table:
A subquery in the FROM clause treated as a temporary table. PS 04
- EXISTS:
True if the subquery returns any row. Read 01
- NULL:
Marker for a missing or unknown value; it is not equal to anything, not even another NULL. Also in Read 01. PP 04
- IS NULL:
Tests whether a value is NULL. Read 01
- IS NOT NULL:
Tests whether a value is present. Read 01
- COALESCE:
Returns the first non-NULL argument. Read 01
- IFNULL:
MySQL function that replaces NULL with a given value. Read 01
- NULLIF:
Returns NULL if two values are equal, otherwise the first. Read 01
- Common Table Expression:
Named temporary result set defined with WITH, usable in the main query. Read 01
- Window Function:
Calculation across related rows without collapsing them into one. Read 01
- OVER:
Clause that defines the window for a window function. Read 01
- PARTITION BY:
Splits a window into groups. Read 01
- ROW_NUMBER:
Unique sequential number per row in a window. Read 01
- RANK:
Rank with gaps after ties. Read 01
- DENSE_RANK:
Rank without gaps after ties. Read 01
- LAG:
Value from the previous row in the window. Read 01
- LEAD:
Value from the next row in the window. Read 01
- View:
Stored query that acts as a virtual table. Read 01
Transactions & tools 11
- Transaction:
A sequence of operations treated as one unit. Read 01
- COMMIT:
Saves a transaction's changes permanently. Read 01
- ROLLBACK:
Undoes changes since the transaction began. Read 01
- SAVEPOINT:
Checkpoint inside a transaction to roll back to. Read 01
- ACID:
Atomicity, Consistency, Isolation, Durability: the guarantees of a transaction. Read 01
- MySQL Workbench:
Client GUI used to write SQL and send it to a server. PP 05
- MySQL Server:
The process that executes SQL and stores the data. PP 05
- Client-Server Architecture:
A client sends requests and a server executes them and returns results. PP 05
- Identifier:
A database-specific name such as a table or column name. PP 05
- Keyword:
A word with special meaning in SQL syntax. PP 05
- Sakila Database:
MySQL's sample DVD-rental database (film, rental, customer, payment) used in the problem sets. PS 03