SQL & Databases Data Modeling and Database Design Syllabus

What is the closure $X^{+}$ of a set of attributes, and what is it used for?

$X^{+}$ is the set of all attributes functionally determined by $X$ under a given set of FDs. It is used to test whether $X \to Y$ holds (check $Y \subseteq X^{+}$) and to determine whether $X$ is a superkey (when $X^{+}$ = all attributes).

Distinguish full functional dependency, partial dependency, and transitive dependency.

Full FD: $Y$ depends on the whole of a composite key $X$, not any proper subset. Partial dependency: $Y$ depends on only part of a composite candidate key. Transitive dependency: $X \to Z$ holds via a non-key attribute ($X \to Y \to Z$ where $Y$ is non-prime).

Define prime and non-prime (non-key) attributes.

A prime attribute is a member of at least one candidate key of the relation. A non-prime (non-key) attribute is one that belongs to no candidate key. These definitions underpin the 2NF, 3NF, and BCNF conditions.

State the requirement for First Normal Form (1NF).

A relation is in 1NF if every attribute contains only atomic (indivisible) single values and there are no repeating groups or multivalued/composite attributes; each cell holds one value from its domain and each row is unique.

State the requirement for Second Normal Form (2NF).

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 dependency of a non-prime attribute on part of a composite key).

State the requirement for Third Normal Form (3NF).

A relation is in 3NF if it is in 2NF and has no transitive dependency of a non-prime attribute on a candidate key. Equivalently, for every nontrivial FD $X \to A$, either $X$ is a superkey or $A$ is a prime attribute.

State the condition for Boyce-Codd Normal Form (BCNF).

A relation is in BCNF if for every nontrivial functional dependency $X \to Y$, the determinant $X$ is a superkey. BCNF is stricter than 3NF because it drops the 3NF allowance that $A$ may be a prime attribute.

How does BCNF differ from 3NF, and what is a key trade-off?

3NF permits an FD $X \to A$ where $A$ is prime even if $X$ is not a superkey; BCNF forbids this, requiring every determinant to be a superkey. Trade-off: a 3NF decomposition is always dependency-preserving and lossless, but a BCNF decomposition may not preserve all dependencies.

What is Fourth Normal Form (4NF), and what dependency does it address?

4NF addresses multivalued dependencies (MVDs). A relation is in 4NF if it is in BCNF and for every nontrivial MVD $X \twoheadrightarrow Y$, $X$ is a superkey. It eliminates redundancy from independent multivalued facts stored in one relation.

Define a multivalued dependency (MVD).

$X \twoheadrightarrow Y$ holds if, for a fixed value of $X$, the set of $Y$ values is independent of the values of the remaining attributes. MVDs represent independent one-to-many facts and generalize functional dependencies.

What is Fifth Normal Form (5NF / Project-Join Normal Form), and what dependency does it address?

5NF addresses join dependencies. A relation is in 5NF if it cannot be losslessly decomposed into smaller relations except by projections implied by its candidate keys; every join dependency is a consequence of the candidate keys, eliminating remaining redundancy.

What are the three main modification anomalies caused by redundancy?

Insertion anomaly (cannot add data without unrelated missing data), deletion anomaly (removing a row unintentionally loses other facts), and update anomaly (a redundant value must be changed in many places, risking inconsistency).

What are the two essential properties a good relational decomposition must satisfy?

Lossless-join (non-additive) property: reconstructing by natural join yields exactly the original relation with no spurious tuples. Dependency preservation: the FDs of the original can be enforced by checking constraints on the individual decomposed relations.

State the lossless-join test for a binary decomposition of $R$ into $R_1$ and $R_2$.

The decomposition is lossless if and only if the common attributes form a key of at least one relation: $(R_1 \cap R_2) \to R_1$ or $(R_1 \cap R_2) \to R_2$. Equivalently, the shared attributes are a superkey of $R_1$ or of $R_2$.

What is denormalization, and why is it done?

Denormalization deliberately introduces controlled redundancy into a normalized schema (e.g., duplicating columns, pre-joining tables, storing derived values) to improve read/query performance by reducing the number of joins and aggregations at query time.

When is denormalization typically appropriate?

When workloads are read-heavy, queries repeatedly join or aggregate the same tables, latency requirements are strict, data changes infrequently, and the extra cost/risk of maintaining redundant data (via triggers, jobs, or application logic) is acceptable.

Summarize the read vs. write trade-off between normalization and denormalization.

Normalization optimizes writes and integrity (each fact stored once, minimal update cost, no anomalies) but can slow reads that need many joins. Denormalization optimizes reads (fewer joins) at the expense of slower/more complex writes and the risk of inconsistency.

What is a materialized aggregate (materialized view), and how does it differ from a regular view?

A materialized view physically stores the precomputed result (e.g., aggregated totals) on disk, giving fast reads. A regular (virtual) view stores only the query definition and recomputes results on each access. The materialized copy must be refreshed to stay current.

What are the main refresh strategies for materialized aggregates, and their trade-off?

Complete refresh (recompute all), incremental/fast refresh (apply only changes via logs), on-commit, and on-demand/scheduled refresh. Trade-off: fresher data means more maintenance overhead on writes; staleness reduces write cost but risks serving out-of-date aggregates.

What is the difference between a logical schema and a physical schema?

The logical schema is the DBMS-independent design of relations, attributes, keys, and constraints (what data and relationships exist). The physical schema specifies how data is actually stored: file organization, data types, indexes, partitioning, and storage parameters.

Give the core rules for mapping ER constructs to a relational (physical) schema.

Each strong entity becomes a table with its attributes and primary key. Weak entities become tables including the owner's PK as part of a composite key. $1{:}N$ relationships add the PK of the 1-side as a foreign key on the N-side; $M{:}N$ relationships and multivalued attributes become separate tables.

What guidelines govern choosing appropriate data types and storage sizes for columns?

Pick the smallest type that safely holds the full value range and precision; use exact numeric (DECIMAL) for money instead of floating point; match string length to real needs; use native DATE/TIMESTAMP for temporal data; and prefer domain-accurate types to enforce integrity and reduce storage and I/O.

Why should exact numeric types be preferred over floating-point for monetary values?

Floating-point types (FLOAT/REAL) store values as binary approximations, so amounts like $0.10$ cannot be represented exactly and rounding errors accumulate. Fixed-precision DECIMAL/NUMERIC stores the exact decimal value, guaranteeing accurate sums and comparisons for currency.

What is table partitioning, and what problem does it solve?

Partitioning splits one logical table into multiple smaller physical segments (partitions) based on a partition key, while keeping a single logical view. It improves manageability, query performance via partition pruning, and enables parallelism and cheaper archiving/deletion of whole partitions.

Compare horizontal partitioning with vertical partitioning.

Horizontal partitioning divides a table by rows (each partition holds a subset of rows, same columns), e.g., by date. Vertical partitioning divides by columns (each partition holds a subset of columns for all rows), separating frequently accessed columns from rarely used ones.

Name the common horizontal partitioning strategies and their basis.

Range partitioning (rows assigned by value ranges, e.g., dates), list partitioning (by an explicit set of key values, e.g., region), hash partitioning (by a hash function on the key for even distribution), and composite partitioning (a combination such as range-hash).

State common naming-convention best practices for database schemas.

Use consistent, descriptive names; pick one case style (e.g., snake_case); avoid reserved words and spaces; be consistent with singular vs. plural table names; use meaningful foreign-key names and standard prefixes/suffixes (e.g., _id, pk_, fk_); and avoid abbreviations that obscure meaning.

How is a many-to-many ($M{:}N$) relationship represented in a relational schema?

With a separate junction (associative/bridge/link) table whose primary key is the combination of the foreign keys referencing the two related tables. Each row records one association pairing, plus any attributes describing the relationship itself.

What is a junction table's primary key, and what else can it hold?

Its primary key is typically the composite of the two foreign keys (or a surrogate key with a unique constraint on that pair). Besides the two FKs, it can store relationship attributes such as a quantity, price, role, or timestamp that belong to the association, not to either entity.

Describe the adjacency-list versus path-enumeration models for representing hierarchies.

Adjacency list: each row stores a parent_id foreign key pointing to its immediate parent (simple, but deep traversal needs recursion). Path enumeration: each row stores its full ancestor path as a string (e.g., '/1/4/9/'), making ancestor/descendant queries easy but complicating updates and integrity.

Describe the nested-set and closure-table models for storing hierarchies.

Nested set: each node stores left/right numeric bounds so subtrees are contiguous ranges (fast reads, expensive inserts). Closure table: a separate table stores one row for every ancestor-descendant pair with depth, enabling efficient hierarchy queries and easy updates at the cost of extra storage.