FIT9138 - Relational Databases & Schema Normalization Flashcards

Question 1

Prime vs. Non-Prime Attribute

Accepted Answer

A **prime attribute** is any attribute that is a member of *at least one* candidate key. A **non-prime attribute** is not part of any candidate key.

Question 2

Trivial Functional Dependency

Accepted Answer

A functional dependency $X \rightarrow Y$ is trivial if $Y \subseteq X$ (i.e., the right-hand attributes are a subset of the left-hand attributes).

Question 3

Attribute Set Closure ($X^+$)

Accepted Answer

The set of all attributes that are functionally determined by attribute set $X$ under a set of FDs $F$. If $X^+ = R$, then $X$ is a superkey for relation $R$.

Question 4

Relational Schema

Accepted Answer

A formal definition of a table structure comprising a relation name, a set of attributes, and their associated domains. Denoted formally as $R(A_1, A_2, ..., A_n)$.

Question 5

Primary Key (PK)

Accepted Answer

A minimal superkey chosen to uniquely identify each tuple (row) in a relation. In SQL, it cannot contain `NULL` values and must be unique across all rows.

Question 6

Foreign Key (FK)

Accepted Answer

An attribute or set of attributes in a relation that refers to the primary key of another relation, maintaining referential integrity between tables.

Question 7

Entity Integrity Constraint

Accepted Answer

A fundamental rule in relational databases stating that no primary key value can be `NULL`, ensuring every tuple is uniquely identifiable.

Question 8

Referential Integrity Constraint

Accepted Answer

A rule stating that any foreign key value in a referencing table must either match an existing primary key value in the referenced table or be completely `NULL`.

Question 9

Functional Dependency (FD)

Accepted Answer

A constraint between two sets of attributes in a relation $R$. Denoted as $X \rightarrow Y$, meaning that if two tuples agree on attribute set $X$, they must also agree on attribute set $Y$.

Question 10

Superkey

Accepted Answer

A set of one or more attributes $K$ in a relation $R$ that uniquely identifies every tuple in that relation. Uniqueness holds, but it may contain unnecessary redundant attributes.

Question 11

Candidate Key

Accepted Answer

A minimal superkey—a set of attributes $K$ that uniquely identifies tuples in a relation, such that no proper subset of $K$ is itself a superkey.

Question 12

Armstrong's Axioms

Accepted Answer

Sound and complete inference rules for FDs:
1. **Reflexivity**: If $Y \subseteq X$, then $X \rightarrow Y$.
2. **Augmentation**: If $X \rightarrow Y$, then $XZ \rightarrow YZ$.
3. **Transitivity**: If $X \rightarrow Y$ and $Y \rightarrow Z$, then $X \rightarrow Z$.

Question 13

First Normal Form (1NF)

Accepted Answer

A relation is in 1NF if the domain of every attribute contains only atomic (indivisible) values, and there are no repeating groups or nested relations.

Question 14

Partial Dependency

Accepted Answer

A functional dependency $X \rightarrow Y$ where $X$ is a proper subset of a candidate key and $Y$ is a non-prime attribute. Eliminating partial dependencies moves a relation from 1NF to 2NF.

Question 15

Second Normal Form (2NF)

Accepted Answer

A relation is in 2NF if it is in 1NF and every non-prime attribute is fully functionally dependent on every candidate key (i.e., no partial dependencies exist).

Question 16

Transitive Dependency

Accepted Answer

A functional dependency $X \rightarrow Z$ where $X \rightarrow Y$ and $Y \rightarrow Z$ hold, and $Y$ is a non-prime attribute that does not determine $X$.

Question 17

Third Normal Form (3NF)

Accepted Answer

A relation is in 3NF if for every non-trivial FD $X \rightarrow Y$, either $X$ is a superkey OR $Y$ is a prime attribute. This eliminates transitive dependencies.

Question 18

Boyce-Codd Normal Form (BCNF)

Accepted Answer

A stricter form of 3NF where for every non-trivial FD $X \rightarrow Y$, $X$ MUST be a superkey. It eliminates all redundancy from functional dependencies, including overlapping candidate keys.

Question 19

Lossless-Join Decomposition

Accepted Answer

A property guaranteeing that decomposing relation $R$ into $R_1$ and $R_2$ produces no spurious tuples when rejoined: $R_1 \bowtie R_2 = R$. This holds if $(R_1 \cap R_2) \rightarrow R_1$ or $(R_1 \cap R_2) \rightarrow R_2$.

Question 20

Dependency Preservation

Accepted Answer

A decomposition property where all original functional dependencies in $F$ can be enforced within individual decomposed relations without performing costly joins across tables.

Question 21

SQL Integrity Constraints

Accepted Answer

Declarative rules enforced by the DBMS to ensure data accuracy:
- `PRIMARY KEY` (Unique + NOT NULL)
- `FOREIGN KEY` (Referential integrity)
- `UNIQUE` (Enforces candidate key property)
- `NOT NULL` (Prevents missing values)
- `CHECK` (Custom logical expressions, e.g., `CHECK (salary > 0)`)

FIT9138 - Relational Databases & Schema Normalization

Cards in this deck

Related Flashcard Sets

JLPT N4 - Grammar Patterns & Kanji

German B1 - Intermediate Grammar & Vocabulary

Kubernetes Fundamentals & Orchestration

CFA Level I - Ethical and Professional Standards