Up To Week 5 Defenitions

Minimum cardinality

The minimum number of relationship instances an entity must participate in, usually 0 for optional participation or 1 for mandatory participation.

Maximum cardinality

The maximum number of relationship instances an entity may participate in; this determines whether a relationship is 1:1, 1:M, or M:N.

Cardinality

The allowed number of instances of one entity that can be related to instances of another entity.

Optional participation

A relationship in which an entity may participate zero times; shown with a minimum cardinality of 0.

Mandatory participation

A relationship in which an entity must participate at least once; shown with a minimum cardinality of 1.

One-to-one (1:1) relationship

A relationship where each row on either side can be related to at most one row on the other side.

One-to-many (1:M) relationship

A relationship where one parent row can relate to many child rows, while each child row relates to one parent.

Many-to-many (M:N) relationship

A relationship where rows on both sides can relate to many rows on the other side; it normally requires a bridge or associative table.

0..1

Zero or one; the relationship is optional and can have at most one related row.

Zero or many

A minimum cardinality of 0 and a maximum cardinality of many; commonly shown as a circle with a crow's foot.

Crow's foot

ERD notation indicating the many side of a relationship.

Primary key (PK)

A column or set of columns that uniquely identifies each row in a table and cannot be NULL.

Foreign key (FK)

A column or set of columns whose non-NULL values must match a referenced key in another table, enforcing referential integrity.

Referential integrity

The rule that a foreign-key value must reference an existing valid parent row, unless the foreign key is allowed to be NULL.

UNIQUE constraint

A constraint that prevents duplicate values or duplicate combinations of values in the constrained column or columns.

Composite key

A key made of more than one column that together uniquely identifies a row.

Partial key

An attribute of a weak entity that distinguishes rows only within the context of their owning entity; it combines with the owner's key to form the full key.

Surrogate key

An artificial identifier, such as an auto-increment ID, created to uniquely identify rows rather than using a business-defined value.

Business key

A real-world attribute or combination of attributes that the business considers unique, even when a surrogate primary key is used.

Strong entity

An entity that can be uniquely identified by its own primary key without borrowing its owner's key.

Weak entity

An entity that cannot be fully identified by its own attributes and therefore borrows the key of an owning entity as part or all of its primary key.

Identifying relationship

A relationship where the parent's primary key becomes part of the child's primary key; MySQL Workbench commonly draws it with a solid line.

Non-identifying relationship

A relationship where the foreign key is not part of the child's primary key; MySQL Workbench commonly draws it with a dashed line.

Associative entity

An entity used to represent a many-to-many relationship, usually containing foreign keys to both related entities.

Bridge table

A table placed between two entities to implement a many-to-many relationship, typically using the two foreign keys as a composite key.

Derived attribute

A value calculated from other stored data rather than stored directly, such as calculating age from birth_date.

Operational database

A database that supports day-to-day business activities and transactions, such as opening repair jobs, recording payments, or making sales.

Business rule

A confirmed requirement or constraint describing how the organization says its data and processes must behave.

Assumption

A design decision believed to be true when it has not been explicitly confirmed as a business requirement.

Conceptual design

The high-level database design stage that identifies entities, relationships, and major business rules without focusing on implementation details.

Logical design

The stage that converts the conceptual model into tables, attributes, keys, relationships, and constraints while remaining mostly independent of a specific DBMS.

Physical design

The implementation-focused stage that decides DBMS-specific details such as data types, indexes, storage choices, and column definitions.

INNER JOIN / JOIN

Returns only rows that have matching values on both sides of the join condition.

LEFT JOIN

Returns every row from the left table and matching rows from the right table; unmatched right-side columns become NULL.

CROSS JOIN

Returns every possible combination of rows from the two tables, producing a Cartesian product.

JOIN condition

The ON expression that specifies how rows from two tables correspond, usually by matching a primary key to its foreign key.

Composite-key JOIN

A join involving an entity with a composite key; all columns needed to identify the referenced row must be matched to avoid incorrect extra matches.

Row multiplication

The increase in result rows when one row matches multiple rows in a joined table; joining through multiple many-side relationships can multiply rows further.

Double counting from JOINs

An aggregation error where the same logical value is repeated by joins and then counted or summed more than once.

SUM(DISTINCT value)

Sums only distinct numeric values, but is not a reliable general fix for join double counting because legitimate equal values can be collapsed.

Subquery

A query nested inside another SQL statement whose result is used by the outer query.

Correlated subquery

A subquery that refers to a value from the outer query and is evaluated in relation to each outer row.

EXISTS

A condition that is true when a subquery returns at least one row.

NOT EXISTS

A condition that is true when a correlated subquery returns no matching rows; commonly used to find rows with no related records.

IN

Tests whether a value matches any value returned by a list or subquery.

NOT IN

Tests whether a value matches none of the values in a list or subquery; NULL in the subquery result can cause the comparison to evaluate as UNKNOWN and return no rows.

NULL

A marker meaning a value is missing, unknown, or not applicable; it is not the same as zero or an empty string.

COUNT(*) in a correlated subquery

Counts matching related rows for each outer row; if no matching rows exist, the result is 0 rather than NULL.

Update anomaly

A problem caused by duplicated data where the same fact must be updated in several rows and some copies may be missed.

Insertion anomaly

A problem where a fact cannot be stored without also storing unrelated data because multiple concepts are combined in one table.

Deletion anomaly

A problem where deleting one row unintentionally removes the only stored copy of another important fact.

Normalization

The process of organizing tables to reduce unnecessary duplication and prevent insertion, update, and deletion anomalies.

Nullable foreign key

A foreign key that may contain NULL, allowing the relationship to be optional, such as a repair job that has not yet been assigned a technician.

NOT NULL foreign key

A foreign key that must contain a value, making participation on the child side mandatory, though this alone does not make the child a weak entity.

One-to-many foreign-key placement

For a 1:M relationship, the foreign key normally goes in the table on the many side and references the one side.

Many-to-many foreign-key placement

For an M:N relationship, place the foreign keys in a separate bridge table rather than storing one foreign key directly in either entity.

Composite primary key in a bridge table

Using the foreign keys together as the primary key prevents the same relationship pair from being stored more than once.

Identifying owner key

The primary key of a parent or owner entity that is copied into a weak child's primary key to help identify the child.