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

DDL, DML & statements 9

Querying basics 19

Aggregation & grouping 9

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:

    Only rows that match in both tables. Also in Read 01. PP 02

  • 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

Transactions & tools 11