Relations (Database Design)

Question 1

Any set of one or more columns that uniquely identify a row in a
table.

Answer 1

Superkey

Question 2

A minimal superkey, meaning it uniquely identifies a row
without any unnecessary columns.

Answer 2

Candidate Key

Question 3

The chosen candidate key to uniquely identify rows in a table.

Answer 3

Primary Key

Question 4

Other candidate keys not chosen as the primary key

Answer 4

Alternate Key

Question 5

A key derived from real-world attributes (e.g., SSN, Email).

Answer 5

Natural Key

Question 6

An artificially generated key (e.g., UserID) with no business
meaning, often used for simplicity and efficiency.

Answer 6

Surrogate Key

Question 7

A single column serving as a primary key (e.g., StudentID).

Answer 7

Simple Key

Question 8

A primary key made of multiple columns (e.g., {OrderID,
ProductID}).

Answer 8

Composite Key

Question 9

Often used interchangeably with composite key, but
technically refers to combining columns that have meaning together.

Answer 9

Compound Key

Question 10

Defines the number of instances of one entity that can or must be associated with instances
of another entity

Answer 10

Cardinality / Multiplicity

Question 11

Each record in one table is associated with exactly one record in another
table.

Answer 11

One-to-One

Question 12

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.

Answer 12

One-to-Many

Question 13

Records in one table can relate to multiple records in another and vice versa

Typically implemented using a junction table

Answer 13

Many-to-Many

Question 14

Defines whether an entity’s participation in a relationship is mandatory or optional.

Answer 14

Modality / Participation

Question 15

Entities in one table may or may not have related records in another table

Answer 15

Optional

Question 16

Every record in one table must have a related record in another table

Answer 16

Mandatory

Question 17

The structure of a table, defined by its name, attributes, and data
types. It is static and does not change frequently.

Answer 17

Relation Schema

Question 18

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.

Answer 18

Relation Instance

Question 19

3 Types of Constraints

Answer 19

Implicit Constraint
Explicit Constraint
Semantic (Application Based) Constraint

Question 20

Inherent to the data model itself, such as atomic values in the
relational model

Answer 20

Implicit Constraint

Question 21

Defined in the schema, such as domain, primary key, foreign key,
and unique constraints.

Answer 21

Explicit Constraint

Question 22

Rules specific to the business logic,
enforced at the application level.

Answer 22

Semantic Constraint

Question 23

5 Types of Relational Integrity Constraint

Answer 23

Domain Constraint
Key Constraint
NULL Constraint
Entity Integrity
Referential Integrity

Question 24

Ensure that values stored in an attribute match its defined data
type and range.

Answer 24

Domain Constraints

Question 25

Ensure uniqueness for rows in a table.

Answer 25

Key Constraints

Question 26

Restrict whether an attribute can be left empty.

Answer 26

NULL Constraints

Question 27

Ensures that no primary key value is NULL, as every record must be uniquely identifiable.

Answer 27

Entity Integrity

Question 28

Ensures that a foreign key value in one table matches a primary key value in another table.

Answer 28

Referential Integrity