No orphans, no duplicates, no broken links. Learn the constraints and business rules that keep a database trustworthy.
These terms were extracted from Database Design – 2nd Edition by Adrienne Watt, eCampusOntario (originally published by BCcampus) which is licensed under a Creative Commons Attribution 4.0 International License.
Download this book for free here
obtained from users when gathering requirements and are used to determine cardinality
Business rules are obtained from users when gathering requirements.
expresses the minimum and maximum number of entity occurrences associated with one occurrence of a related entity
Business rules are used to determine cardinality and connectivity.
the relationship between two tables, e.g., one to one or one to many
Business rules are used to determine cardinality and connectivity.
the rules that force DBMSs to check that data satisfies the semantics
Constraints are a very important feature in a relational model.
requires that every table have a primary key; neither the primary key, nor any part of it, can contain null values
To ensure entity integrity, it is required that every table have a primary key.
where the primary key contains the foreign key; indicated in an ERD by a solid line
An identifying relationship will have a solid line (where the PK contains the FK).
logical statements that state what data values are or are not allowed and which format is suitable for an attribute
Such information is provided in logical statements called integrity constraints.
one entity occurrence requires a corresponding entity occurrence.
In a mandatory relationship, one entity occurrence requires a corresponding entity occurrence.
does not contain the foreign key in the primary key; indicated in an ERD by a dotted line
A non-identifying relationship is indicated by a broken line and does not contain the FK in the PK.
the FK can be null or the parent table does not need to have a corresponding child table occurrence
In an optional relationship, the FK can be null or the parent table does not need to have a corresponding child table occurrence.
a record whose foreign key value is not found in the corresponding entity – the entity where the primary key is located
To ensure that there are no orphan records, we need to enforce referential integrity.
requires that a foreign key must have a matching primary key or it must be null
Referential integrity requires that a foreign key must have a matching primary key or it must be null.
a popular database system based on the relational model introduced by E. F. Codd of IBM’s San Jose Research Laboratory
RDBMS is a popular database system that is based on the relational model introduced by E. F. Codd of IBM’s San Jose Research Laboratory.
the type of relationship between two tables in an ERD (either identifying or non-identifying); this relationship is indicated by a line drawn between the two tables.
The line that connects two tables, in an ERD, indicates the relationship type between the tables: either identifying or non-identifying.