Normalization & Design — SQL Questions with Answers

Answer: A — Organising tables to reduce redundancy and prevent update anomalies

Normalization splits data into related tables so each fact is stored once. This avoids duplication and the inconsistencies that arise when the same fact lives in many places.

NormalizationDesign

Answer: A — The same information stored in multiple places, risking inconsistency when one copy changes

If a customer's address is copied into every order row, changing it means updating many rows - and missing one leaves conflicting data. Normalization stores it once.

RedundancyAnomalies

Answer: A — Atomic (indivisible) values - no repeating groups or multiple values in one cell

1NF forbids storing '555-1234, 555-5678' in one phone column, or columns like phone1, phone2, phone3. Each cell holds one value; repeating data moves to its own table.

1NFAtomicity

Answer: A — the key, the whole key, and nothing but the key

This memorable phrase captures 1NF (atomic, tied to the key), 2NF (the WHOLE key), and 3NF (NOTHING BUT the key). It is a handy design checklist.

3NFDesign rule

Answer: A — Deliberately adding redundancy back to improve read performance, accepting the update cost

Sometimes storing a pre-computed total or a duplicated label speeds up heavy read workloads (reporting, analytics). It trades write complexity and consistency risk for read speed.

DenormalizationPerformance

Answer: A — A visual model of entities (tables), their attributes, and the relationships between them

ER diagrams are the blueprint for a schema: boxes for entities, lines for relationships (one-to-many, many-to-many), and crow's-foot notation for cardinality. Design before you build.

ER diagramModelling

Answer: A — Put a foreign key on the MANY side referencing the ONE side

One customer has many orders, so orders.customer_id references customers.id. The foreign key lives on the many side - the most common relationship in databases.

One-to-manyForeign keys

Answer: A — A shared/foreign key with a UNIQUE constraint, or by merging into one table

One-to-one splits optional or sensitive attributes (e.g. a user and their extended profile) into a second table sharing the key, enforced UNIQUE. Often the data could just live in one table.

One-to-oneUNIQUE

Answer: A — An artificial identifier (like an auto-increment id) with no business meaning, used as the primary key

Surrogate keys are stable (they never change) and simple, which is why most designs use them. Natural keys carry meaning but can change, complicating references.

Surrogate keyPrimary key

Answer: A — How many rows on one side relate to rows on the other (one-to-one, one-to-many, many-to-many)

Cardinality describes the relationship's shape and drives how you implement it (foreign key placement, junction tables). ER diagrams annotate it with crow's-foot symbols.

CardinalityER diagram

Answer: A — The address is stored once, so a single UPDATE fixes it everywhere - no missed copies

In a denormalized design the address might be copied into every order. Normalized, it lives in one customer row, so one update keeps all data consistent.

Update anomalySingle source of truth

Answer: A — A denormalized analytics design: a central fact table linked to dimension tables

Data warehouses use star schemas: a fact table (sales) surrounded by dimensions (date, product, store). Deliberately denormalized for fast aggregation, unlike transactional 3NF designs.

Star schemaData warehouse

Answer: A — Usually computed on read (SUM of items) to avoid it going stale - or stored/cached deliberately for performance

Storing a total risks it drifting out of sync with the items. Computing it on demand keeps it correct; caching it (with careful maintenance) is a performance-driven denormalization.

Derived dataDenormalization

Answer: A — It guarantees relationships stay valid - foreign keys always point to existing rows

Enforced by foreign keys, it prevents orphan records (an order for a deleted customer). It is a cornerstone of trustworthy relational data.

Referential integrityForeign keys

Answer: A — It enforces a fixed set of valid values and lets you rename or add statuses in one place

Free text invites typos ('shpped', 'Shipped', 'SHIPPED') that fragment reports. A lookup table with a foreign key guarantees consistency and central control.

Lookup tableData quality

Answer: A — Each fact is stored in exactly ONE place, so it is always consistent

Normalization implements this principle. When a fact lives in one place, it cannot contradict itself - the core reason normalized designs are trustworthy.

Single source of truthNormalization

Answer: A — Problems from redundancy: updating misses copies, you cannot insert data without unrelated data, and deleting loses needed facts

For example, if course info lives only in enrolment rows, deleting the last enrolment erases the course entirely (delete anomaly). Normalization prevents these.

AnomaliesNormalization

Answer: A — Removing partial dependencies - non-key columns must depend on the WHOLE composite key, not part of it

2NF applies when the primary key is composite. If a column depends on only part of the key (e.g. product_name depends on product_id alone in an order_items table), it belongs in a separate table.

2NFPartial dependency

Answer: A — Removing transitive dependencies - non-key columns must not depend on OTHER non-key columns

If a table has employee_id (key), dept_id, and dept_name, then dept_name depends on dept_id (a non-key), not directly on the key. 3NF moves department data to a departments table.

3NFTransitive dependency

Answer: A — For read-heavy analytics/reporting where join cost is high and data changes rarely

Data warehouses often denormalize into star schemas for fast aggregation. For transactional systems that change constantly, normalized designs remain safer. It is a deliberate trade-off, not a default.

DenormalizationData warehouse

Answer: A — With a junction (bridge) table holding foreign keys to both sides

Students and courses: an enrollments table links them, with student_id and course_id. It resolves the many-to-many into two one-to-many relationships and can carry extra data like a grade.

Many-to-manyJunction table

Answer: A — A small table of allowed values (e.g. statuses, categories) referenced by foreign key

Instead of repeating 'pending'/'shipped' text everywhere, store them once in a statuses table and reference by id. This enforces valid values and makes renaming a status a one-row change.

Lookup tableNormalization

Answer: A — They violate 1NF and cap the number of phones; a separate phones table scales cleanly

Repeating-group columns force a fixed maximum and make queries awkward ('find anyone with this number' must check three columns). A related phones table (one row per number) is the normalized solution.

1NFRepeating groups

Answer: A — When one column's value determines another's - e.g. zip_code determines city

Functional dependencies are the theory behind normal forms. If A determines B but A is not a key, B usually belongs in a table keyed by A - the essence of 3NF.

Functional dependency3NF

Answer: A — A stronger 3NF: every determinant must be a candidate key

BCNF handles edge cases 3NF misses, involving overlapping candidate keys. Most practical designs that reach 3NF are also in BCNF; it matters mainly in complex schemas.

BCNFNormal forms

Answer: A — Less redundancy and safer writes, but more joins - which can slow complex read queries

Normalization optimises for correctness and write integrity. Read-heavy analytical workloads sometimes denormalize to avoid many joins - a conscious balance between the two goals.

Trade-offsJoins

Answer: A — Aim for 3NF by default, then denormalize selectively where measured performance requires it

3NF is the practical sweet spot for most transactional systems: enough to prevent anomalies without excessive joins. Denormalize only with evidence that it helps a specific workload.

Design strategy3NF

Answer: A — Another name for a junction table - it represents the relationship itself and can hold its own attributes

The enrollments table between students and courses is an associative entity: it exists because of the relationship and can store attributes about it, like enrolment date and grade.

Associative entityJunction table

Answer: A — Attributes that apply to only some rows may belong in a separate related table (subtype)

If many columns are NULL for most rows (e.g. car-specific fields in a generic products table), those attributes likely belong to a subtype table linked by key - a cleaner, more normalized model.

NULLSubtypesDesign

Answer: A — Relationships and keys are hard to change once data exists; a clear model prevents costly restructuring later

Schema changes on populated production tables are risky and slow. Designing entities, keys and relationships upfront catches mistakes while they are cheap to fix.

ER diagramPlanning