Relations (Database Design) Flashcards
Any set of one or more columns that uniquely identify a row in a
table.
Superkey
A minimal superkey, meaning it uniquely identifies a row
without any unnecessary columns.
Candidate Key
The chosen candidate key to uniquely identify rows in a table.
Primary Key
Other candidate keys not chosen as the primary key
Alternate Key
A key derived from real-world attributes (e.g., SSN, Email).
Natural Key
An artificially generated key (e.g., UserID) with no business
meaning, often used for simplicity and efficiency.
Surrogate Key
A single column serving as a primary key (e.g., StudentID).
Simple Key
A primary key made of multiple columns (e.g., {OrderID,
ProductID}).
Composite Key
Often used interchangeably with composite key, but
technically refers to combining columns that have meaning together.
Compound Key
Defines the number of instances of one entity that can or must be associated with instances
of another entity
Cardinality / Multiplicity
Each record in one table is associated with exactly one record in another
table.
One-to-One
A single record in one table can be related to multiple records in
another table, but each record in the second table relates to only one record in the first.
One-to-Many
Records in one table can relate to multiple records in another and vice versa
Typically implemented using a junction table
Many-to-Many
Defines whether an entity’s participation in a relationship is mandatory or optional.
Modality / Participation
Entities in one table may or may not have related records in another table
Optional
Every record in one table must have a related record in another table
Mandatory
The structure of a table, defined by its name, attributes, and data
types. It is static and does not change frequently.
Relation Schema
A snapshot of the data in the table at a specific point in time. It
consists of all rows (tuples) currently stored in the table.
Relation Instance
3 Types of Constraints
Implicit Constraint
Explicit Constraint
Semantic (Application Based) Constraint
Inherent to the data model itself, such as atomic values in the
relational model
Implicit Constraint
Defined in the schema, such as domain, primary key, foreign key,
and unique constraints.
Explicit Constraint
Rules specific to the business logic,
enforced at the application level.
Semantic Constraint
5 Types of Relational Integrity Constraint
Domain Constraint
Key Constraint
NULL Constraint
Entity Integrity
Referential Integrity
Ensure that values stored in an attribute match its defined data
type and range.
Domain Constraints
Ensure uniqueness for rows in a table.
Key Constraints
Restrict whether an attribute can be left empty.
NULL Constraints
Ensures that no primary key value is NULL, as every record must be
uniquely identifiable.
Entity Integrity
Ensures that a foreign key value in one table matches a primary
key value in another table.
Referential Integrity
