Question 1

In database design, is it always desirable in every situation to normalize all relations up to Boyce–Codd Normal Form (BCNF), regardless of reporting needs, performance constraints, or legacy integration requirements?

Accepted Answer

Introduction / Context:Normalization reduces redundancy and update anomalies by organizing data according to formal normal forms. Boyce–Codd Normal Form (BCNF) is stricter than Third Normal Form (3NF). This question tests whether BCNF is always the universally desirable target, independent of business constraints such as performance, analytics, and legacy interoperability.Given Data / Assumptions:BCNF eliminates all anomalies that arise from non-trivial functional dependencies where determinants are not candidate keys.Operational workloads may include heavy reads, complex reporting, or strict latency Service Level Objectives.Systems may need to integrate with legacy schemas, packaged apps, or dimensional models.Concept / Approach:BCNF is ideal for transactional integrity but can increase the number of joins. For mixed or analytic workloads, designers sometimes choose 3NF or purposeful denormalization to reduce join cost, simplify queries, or match dimensional star/snowflake patterns. Sound engineering balances integrity with performance and usability.Step-by-Step Solution:Identify functional dependencies and decompose relations to meet BCNF.Evaluate query patterns and latency goals; estimate join selectivity and frequency.If BCNF causes excessive joins for critical queries, consider 3NF or selective denormalization with documented controls.Add safeguards such as computed columns, indexed views, or ETL validation to protect data quality when denormalizing.Continuously monitor workload and revisit decomposition as needs change.Verification / Alternative check:Prototype both designs (BCNF vs. near-BCNF/3NF with selective denormalization) and benchmark key queries. Confirm that integrity constraints (keys, foreign keys, check constraints) still enforce correctness.Why Other Options Are Wrong:“Universally preferred with no exceptions” ignores performance and analytics trade-offs. “Only with surrogate keys” or “only for read-only marts” are unrelated conditions; marts are often intentionally denormalized for OLAP.Common Pitfalls:Blindly denormalizing without constraints; or, conversely, forcing BCNF and harming critical query performance. Failing to document the rationale behind any deviation from BCNF.Final Answer:Does not apply — BCNF is not always the best choice in every situation

Question 2

Do denormalized tables, by definition, satisfy Boyce–Codd Normal Form (BCNF)? Consider the typical practice of intentionally merging attributes to reduce joins.

Accepted Answer

Introduction / Context:Denormalization is the deliberate introduction of redundancy to improve read performance or simplify queries. BCNF, in contrast, eliminates redundancy caused by certain functional dependencies. This question probes whether the act of denormalization is compatible with BCNF.Given Data / Assumptions:Denormalization often combines tables or repeats derived attributes.BCNF requires every determinant to be a candidate key.Design goals may include minimizing joins for heavy reporting.Concept / Approach:When attributes that depend on a non-key determinant are merged into one table, new dependencies are introduced where determinants are not keys. That contradicts BCNF. Therefore, denormalized tables generally do not meet BCNF, although they may still be correct and useful with proper controls (constraints, ETL, audits).Step-by-Step Solution:List functional dependencies for the table.Check if each determinant is a candidate key.If a non-key determinant exists (common in denormalization), BCNF is violated.Mitigate risks using computed columns, triggers, or ETL checks.Document reasons and add indexes to support performance goals.Verification / Alternative check:Perform dependency analysis or use query-based profiling to detect anomalies (duplicates, inconsistent derived values) that indicate BCNF violation.Why Other Options Are Wrong:Surrogate keys and nullability do not guarantee BCNF. Declaring that denormalization “guarantees BCNF” reverses the definitions.Common Pitfalls:Assuming a surrogate key alone fixes normalization; ignoring referential checks after denormalization; failing to document which fields are duplicated and how to update them.Final Answer:Does not apply — denormalized tables typically violate BCNF

Question 3

Does normalizing a schema generally require programmers to write more complex SQL queries (for example, more joins) compared with an equivalent denormalized design?

Accepted Answer

Introduction / Context:Normalization decomposes data into multiple related tables to remove redundancy and anomalies. While this improves integrity, it often increases the number of joins in queries. The question asks about the practical impact of normalization on SQL complexity.Given Data / Assumptions:Normalized schemas (3NF/BCNF) separate entities and relationships into many tables.Most business queries need columns spanning several entities.Developers can mitigate complexity with views, stored procedures, or ORM mapping.Concept / Approach:More tables generally mean more joins. Joins add predicates, aliasing, and grouping, which can increase SQL length and cognitive load. However, integrity benefits (fewer anomalies, clearer semantics) often outweigh this cost, particularly in OLTP systems. For analytics, dimensional models balance simplicity and performance.Step-by-Step Solution:Normalize entities to remove redundancy.Identify reporting/transactional queries.Count and optimize joins with indexes and selective projections.Use views or materialized views to encapsulate complex joins.Monitor execution plans and tune where needed.Verification / Alternative check:Create equivalent reports against normalized and denormalized schemas; note the number of joins and SQL verbosity. Measure latency and maintenance costs.Why Other Options Are Wrong:Normalization does not “always reduce” SQL complexity; it often increases it. The effect is not limited to surrogate-key usage or strictly to OLTP.Common Pitfalls:Over-normalizing without considering frequent query paths; not indexing join keys; writing unaliased verbose SQL that obscures intent.Final Answer:Applies — normalized designs usually require more joins and moderately more complex SQL

Question 4

When building a new database from preexisting tables or flat files, is it safe to assume that referential integrity (foreign key consistency) has already been enforced on the received data?

Accepted Answer

Introduction / Context:In data migrations and greenfield builds that reuse legacy tables, referential integrity (RI) problems are common. This question targets proper due diligence when inheriting data versus trusting upstream processes blindly.Given Data / Assumptions:Source systems may lack declared foreign keys.Exports may have been filtered or transformed, introducing orphaned references.Data may span multiple systems with inconsistent codes.Concept / Approach:Never assume RI. Instead, profile and validate. Use foreign key discovery, distinct counts, anti-joins to find orphans, and domain checks. Only after cleaning and confirmation should you enforce RI constraints in the target schema.Step-by-Step Solution:Inventory candidate parent/child tables and key columns.Run anti-joins to detect orphans: child.key not found in parent.key.Quantify anomalies and determine remediation (fix values, add parents, or drop bad rows).Create and validate foreign keys; add indices to support them.Automate ongoing quality checks in ETL/ELT.Verification / Alternative check:Cross-tab counts and integrity reports before and after remediation demonstrate improved consistency. Sample-based manual inspection can confirm edge cases.Why Other Options Are Wrong:“Assume RI if production” is risky; production systems can still have errors. NULL presence or surrogate keys do not guarantee RI.Common Pitfalls:Trusting documentation without data profiling; missing code table mismatches; overlooking soft-deleted or inactive parent rows.Final Answer:Does not apply — never assume RI; profile and validate the data

Question 5

When reverse-engineering a schema from existing data, is the presence of a general-purpose “remarks” or “comments” column a common design problem that can hide multiple attributes and hinder normalization?

Accepted Answer

Introduction / Context:Legacy datasets often include “remarks,” “notes,” or “misc” columns that hold heterogeneous text. While useful for humans, they impede data quality and analytics because business attributes get buried in unstructured text. This question asks whether such columns are a common design problem.Given Data / Assumptions:Remarks fields contain mixed content (exceptions, reasons, codes, dates).Structured attributes are missing or inconsistently recorded.Normalization is desired to support reliable querying and constraints.Concept / Approach:General remarks columns reduce observability and break declarative constraints. The remedy is to identify recurring data elements within the text and promote them to explicit typed columns or child tables; keep the remarks field only for truly unstructured narrative.Step-by-Step Solution:Profile the remarks data; mine for recurring patterns (e.g., “Reason:”, “Code=”).Create new attributes with proper domains (status, reason_code).Backfill using parsing rules where feasible; otherwise, leave unmatched cases as text.Enforce constraints and foreign keys for the new attributes.Retain a remarks field for free text, but discourage embedding structured data in it.Verification / Alternative check:After extraction, queries become simpler and more accurate; data quality metrics (completeness, validity) improve.Why Other Options Are Wrong:Issues do not depend on text length or OLAP vs. OLTP; any unstructured catch-all field can hide structure.Common Pitfalls:Attempting to parse every legacy note perfectly; failing to provide users with new fields to capture structured values going forward.Final Answer:Applies — free-text remarks fields commonly mask real attributes

Question 6

Do modification anomalies (insertion, update, and deletion anomalies) typically cause enough practical issues that most operational tables should be normalized to at least BCNF unless there is a justified trade-off?

Accepted Answer

Introduction / Context:Modification anomalies occur when redundant storage causes inconsistent updates, prevents valid inserts, or deletes unintended information. BCNF eliminates many root causes. This question asks whether anomalies are generally severe enough to warrant BCNF in typical operational schemas.Given Data / Assumptions:Operational systems must ensure correctness and ease of maintenance.Data redundancy increases risk of inconsistency.Some workloads justify selective denormalization for performance.Concept / Approach:BCNF offers strong guarantees against dependency-driven redundancy. Most OLTP tables benefit from BCNF or 3NF with careful exceptions. Where denormalization is chosen, compensating controls are essential.Step-by-Step Solution:Analyze functional dependencies for each table.Decompose to BCNF or 3NF where anomalies exist.Measure query and write performance.If denormalizing, document fields duplicated and define authoritative sources.Enforce constraints, triggers, or ETL rules to maintain consistency.Verification / Alternative check:Audit for conflicting values and orphaned facts; fewer inconsistencies post-normalization indicate success.Why Other Options Are Wrong:Claiming anomalies are rare contradicts operational experience. Limiting BCNF to audit tables or composite keys misunderstands its scope.Common Pitfalls:Over-normalizing without regard to critical query paths; denormalizing without governance, leading to drift.Final Answer:Applies — in most cases anomalies justify BCNF or near-BCNF

Question 7

In SQL, does the aggregate COUNT() return the total number of rows in a table or result set, including rows that contain NULLs in any column?

Accepted Answer

Introduction / Context:SQL provides aggregate functions for summarization. COUNT() is widely used, yet developers sometimes confuse it with COUNT(column). This question distinguishes these two behaviors.Given Data / Assumptions:COUNT() counts rows in the current scope of the query.COUNT(column) counts non-NULL values in that column only.Filters (WHERE) and grouping (GROUP BY) affect the counted set.Concept / Approach:COUNT() is defined to include every row that survives WHERE and JOIN conditions, independent of NULLs. It is the correct choice when you need the cardinality of the result set. Use COUNT(column) when you specifically want to ignore NULLs in that column.Step-by-Step Solution:Write the query with necessary joins and WHERE predicates.Use COUNT() to count the resulting rows.If you need to exclude rows where a column is NULL, use COUNT(column).Combine with GROUP BY for per-group counts.Verify by testing with rows that contain NULLs.Verification / Alternative check:Create a table with NULLs in several columns; COUNT() equals total rows, while COUNT(col) is lower when NULLs exist.Why Other Options Are Wrong:COUNT() never ignores rows due to NULLs. Primary keys or the presence of WHERE do not alter the definition.Common Pitfalls:Accidentally using COUNT(col) and misreporting totals; forgetting that joins can duplicate rows before counting.Final Answer:Applies — COUNT(*) counts all rows regardless of NULLs

Question 8

When designing from existing data, is it a common problem to find multiple values for a single attribute stored in one cell (for example, comma-separated lists), which violates First Normal Form (1NF)?

Accepted Answer

Introduction / Context:First Normal Form requires that attribute values be atomic and that there be no repeating groups. Legacy files often pack multiple values into one cell (e.g., “red,blue,green”). This question asks whether that is a common design problem.Given Data / Assumptions:Existing datasets frequently include delimited strings representing sets.Queries must filter or join on individual values.We aim to normalize the design for integrity and queryability.Concept / Approach:Multi-valued cells impede indexing and correctness. Proper modeling splits values into child rows or uses an association table for many-to-many relationships. This aligns with 1NF and supports set-based operations efficiently.Step-by-Step Solution:Detect columns containing delimiters or repeated patterns.Split data into a child table with one value per row (entity-attribute or association table).Create keys and foreign keys to enforce integrity.Backfill and validate counts to ensure no data loss.Refactor downstream queries to use joins rather than string parsing.Verification / Alternative check:After normalization, predicates and joins on individual values become sargable and perform better with indexes.Why Other Options Are Wrong:1NF prohibits list-in-cell regardless of data type or system; NoSQL allowance does not change relational rules.Common Pitfalls:Leaving the old delimited column as authoritative; failing to handle whitespace/casing during backfill.Final Answer:Applies — multi-valued cells are a common anti-pattern and violate 1NF

Question 9

In legacy or imported datasets, are missing values (for example, blanks or NULLs) a common design/data-quality problem that must be addressed during database design and loading?

Accepted Answer

Introduction / Context:Data imported from spreadsheets or legacy systems often contains missing values represented as NULLs or blanks. This affects constraint enforcement, analytics, and application behavior. The question asks if this is a common problem that must be planned for.Given Data / Assumptions:Different sources use different conventions (NULL, empty string, zero).Some attributes are mandatory for business rules.Loading and validation are part of the design process.Concept / Approach:Designers must define canonical representations and constraints. Required attributes should be NOT NULL; optional fields should be documented. ETL processes may impute, default, or reject rows with missing critical data.Step-by-Step Solution:Profile each column’s completeness.Declare NOT NULL and CHECK constraints where applicable.Transform blanks to NULLs consistently during load.Apply reference data lookups to replace missing codes where possible.Report residual gaps and create remediation workflows.Verification / Alternative check:Data quality dashboards showing completeness before/after ETL confirm improvements. Sample queries expose remaining blanks or NULLs.Why Other Options Are Wrong:Production data often includes missing values. The problem is not limited to text columns and is independent of foreign keys.Common Pitfalls:Treating empty strings as valid when the business requires a value; inconsistent NULL/blank handling across systems.Final Answer:Applies — missing or blank values are common and must be handled

Question 10

Is there an SQL construct called COLUMNS() to return the number and type of columns in a table, or should schema introspection be done via INFORMATION_SCHEMA or database-specific catalog views?

Accepted Answer

Introduction / Context:Developers often need to inspect table metadata (column names, data types). SQL itself does not define a generic COLUMNS() function. This question clarifies the standard approach to schema introspection.Given Data / Assumptions:We are working with relational databases (e.g., MySQL, PostgreSQL, SQL Server, Oracle).We want a portable way to discover column metadata.We must distinguish standard vs. vendor-specific methods.Concept / Approach:ANSI SQL defines INFORMATION_SCHEMA views (e.g., INFORMATION_SCHEMA.COLUMNS) that expose metadata. Vendors also provide catalog views (e.g., sys.columns in SQL Server, ALL_TAB_COLUMNS in Oracle). There is no COLUMNS() aggregate or function in standard SQL to list or count columns directly.Step-by-Step Solution:Query INFORMATION_SCHEMA.COLUMNS filtered by table_schema and table_name.COUNT() over that set yields the number of columns.Select data_type and ordinal_position for types and ordering.Use vendor catalog views if additional details are needed (collation, identity, computed).Wrap in a view or stored procedure for reuse.Verification / Alternative check:Run queries against multiple DBMSs; INFORMATION_SCHEMA returns consistent metadata where implemented. No engine will accept COLUMNS() as a valid construct.Why Other Options Are Wrong:Claims about ANSI or Oracle support for COLUMNS() are incorrect. The functionality belongs to metadata views, not an aggregate function.Common Pitfalls:Relying on nonportable DESCRIBE commands; forgetting to filter by schema; conflating column count with column statistics.Final Answer:Does not apply — there is no COLUMNS(); use INFORMATION_SCHEMA or catalog views

Question 11

Database normalization and anomalies: evaluate the claim below.  "We can eliminate modification anomalies with proper normalization that results in tables in Boyce–Codd Normal Form (BCNF)."

Accepted Answer

Introduction / Context: This question tests your understanding of database normalization, specifically Boyce–Codd Normal Form (BCNF), and its role in preventing modification anomalies. Modification anomalies include update, insert, and delete anomalies that arise when redundant data are stored due to improper schema design. The claim is that moving tables to BCNF can eliminate these anomalies.Given Data / Assumptions:Anomalies discussed are those caused by functional dependencies (FDs) such as A -> B creating redundancy when A and B are stored together improperly.BCNF requires that for every nontrivial functional dependency X -> Y, the determinant X is a superkey.We are evaluating logical design (schema form), not physical performance tuning or indexing.Concept / Approach: Moving to BCNF decomposes relations so that every determinant is a key, which removes redundancy implied by FDs. With redundancy reduced, the classic modification anomalies largely disappear: updating one fact no longer requires changing it in multiple rows; inserting facts does not depend on unrelated attributes; deleting a row does not inadvertently remove independent information. BCNF focuses on anomalies arising from FDs; higher forms (4NF, 5NF) address multivalued and join dependencies, respectively.Step-by-Step Solution:Identify anomalies in a relation caused by non-key determinants (for example, professor -> department stored in a course assignment table).Apply decomposition so every determinant becomes a superkey (professor and department in one table; course assignments in another).Verify lossless join and dependency preservation where possible.Observe that update/insert/delete anomalies due to FDs are removed.Verification / Alternative check: Compare workloads before and after normalization. After BCNF decomposition, updating a professor’s department involves a single row in a single relation rather than many scattered rows, demonstrating the elimination of FD-driven anomalies.Why Other Options Are Wrong:“Invalid” or “depends on indexing”: indexing affects speed, not logical redundancy.“Applies only to warehouses” or “single-attribute tables”: BCNF is a general relational concept and does not require trivial schemas.Common Pitfalls: Assuming BCNF cures all possible issues; anomalies from multivalued dependencies may require 4NF, and complex join dependencies may need 5NF. Also, some designs denormalize intentionally for performance, but that reintroduces potential anomalies.Final Answer: Valid (BCNF removes anomalies caused by functional dependencies)

Question 12

Design from existing tables: evaluate the assumption.  "When building a database from existing tables, we may safely assume that there are no multivalued dependencies (MVDs) in the provided data."

Accepted Answer

Introduction / Context: Many projects begin with data inherited from spreadsheets or legacy systems. The question probes whether it is safe to assume that such data contain no multivalued dependencies (MVDs), which can lead to redundancy and anomalies if left unaddressed. Recognizing and handling MVDs is critical for a robust design.Given Data / Assumptions:You receive existing tables or files that must be turned into a well-designed database.MVDs occur when two or more independent multi-valued attributes coexist for the same key (for example, a person with multiple skills and multiple hobbies stored in one table).Normalization to 4NF is used to address MVDs by separating independent multi-valued facts.Concept / Approach: You should profile and analyze incoming data, discovering functional and multivalued dependencies rather than assuming their absence. Assumptions lead to designs that keep hidden redundancies. Detecting MVDs involves checking whether attributes vary independently with respect to a chosen key, which implies a need for decomposition into separate relations.Step-by-Step Solution:Inspect each table for repeating groups or multiple lists embedded in a single row (skills, phone_numbers, categories).Test independence: if skill choices are independent of hobby choices for the same person, there is an MVD person -> skill and person -> hobby.Decompose to 4NF by creating separate child tables and linking by the key.Validate that the decomposition is lossless and that anomalies are removed.Verification / Alternative check: Run data profiling queries to detect many-to-many patterns within a single table (for example, COUNT DISTINCT combinations vs. independent distinct counts). Unexpectedly high redundancy signals MVDs.Why Other Options Are Wrong:Claims that MVDs are rare or vanish with a primary key ignore the nature of independent multi-valued attributes.Vendor dependence is irrelevant; MVDs are logical, not product-specific.Common Pitfalls: Treating CSV columns with delimited lists as normal attributes; believing that foreign keys alone cure redundancy without proper decomposition.Final Answer: Invalid (you must test for multivalued dependencies; never assume)

Question 13

BCNF criterion — clarify the condition.  "We have normalized a table into BCNF if all candidate keys are determinants."

Accepted Answer

Introduction / Context: Boyce–Codd Normal Form (BCNF) strengthens Third Normal Form by eliminating anomalies from all functional dependencies (FDs). The statement given reverses the correct BCNF condition. Understanding the exact criterion is essential to avoid leaving non-key determinants that cause redundancy.Given Data / Assumptions:Candidate keys are, by definition, determinants for all attributes in a relation.BCNF condition: for every nontrivial FD X -> Y, X must be a superkey.We are not discussing multivalued or join dependencies (handled by 4NF/5NF).Concept / Approach: Saying “all candidate keys are determinants” adds no information because keys always determine all attributes. The real question is whether any non-key determinant exists. If any FD has a left side that is not a superkey, the relation violates BCNF. Therefore, the statement is incorrect because it fails to rule out non-key determinants.Step-by-Step Solution:List all FDs for the relation.Identify determinants (left-hand sides).Check whether each determinant is a superkey.If any determinant is not a superkey, the relation is not in BCNF.Verification / Alternative check: Consider a relation with keys {A,B} and FDs B -> C. Even though {A,B} determines everything, B is a determinant but not a superkey, so the relation violates BCNF. This demonstrates why the statement is false.Why Other Options Are Wrong:“Valid” misstates BCNF.Index presence or warehouse context is irrelevant to the logical condition.Common Pitfalls: Confusing “keys determine attributes” with the BCNF requirement; assuming 3NF equivalence; forgetting to test all FDs, not just those involving keys.Final Answer: Invalid (BCNF requires every determinant to be a superkey, not merely that keys are determinants)

Question 14

Reverse engineering from existing tables: do normalization principles still apply?

Accepted Answer

Introduction / Context: Projects often migrate from legacy tables or files to a new relational database. The temptation is to replicate the existing structure verbatim. This question asks whether normalization principles still apply in such a scenario. The correct mindset is to treat the legacy data as raw material, not as a finished logical model.Given Data / Assumptions:You receive existing tables (possibly denormalized or inconsistent).Your goal is to create a robust relational schema for ongoing use.Quality issues (duplicates, inconsistent codes) may exist and must be addressed.Concept / Approach: Normalization is a logical design discipline independent of data source. Whether starting from scratch or from legacy artifacts, you still must identify entities, candidate keys, functional dependencies, multivalued dependencies, and relationships. Proper normalization reduces redundancy and eliminates anomalies, enabling consistent updates and clear semantics.Step-by-Step Solution:Profile the incoming data to find keys, null patterns, and uniqueness.Document FDs and MVDs; remove repeating groups.Decompose relations to 3NF/BCNF (and 4NF if MVDs exist) with lossless joins.Add constraints (PKs, FKs, CHECKs) to enforce the model.Verification / Alternative check: Compare anomaly rates and data quality before and after normalization. Post-normalization, updates become localized and integrity violations are prevented by constraints, validating the approach.Why Other Options Are Wrong:Limiting normalization to “brand-new designs” ignores decades of practice.Storage engine and indexing do not replace logical design.Common Pitfalls: Blindly importing spreadsheets as tables; assuming column names equal well-defined attributes; skipping constraints because legacy data are messy.Final Answer: Valid (you must still analyze keys and dependencies and normalize)

Question 15

Multivalued dependencies (MVDs): are they harmless?  "Multivalued dependencies create harmless anomalies that can be noted but need not be eliminated."

Accepted Answer

Introduction / Context: Multivalued dependencies (MVDs) arise when two or more sets of attributes vary independently with respect to a key. If left in the same relation, MVDs introduce redundant combinations and classic anomalies. This question challenges the notion that MVDs are harmless and can be ignored.Given Data / Assumptions:MVD example: STUDENT has independent sets {SKILL} and {HOBBY}; storing both lists together inflates rows.Fourth Normal Form (4NF) is intended to remove redundancy from MVDs by decomposition.We are focused on logical integrity, not performance shortcuts.Concept / Approach: When MVDs are present, each independent set combines with the other, producing a Cartesian-like explosion of rows. This inflates storage and creates update anomalies: changing one list requires touching many rows; deleting an entry may remove unrelated facts. 4NF decomposes the table into separate relations keyed by the same determinant, eliminating the artificial combinations and restoring integrity.Step-by-Step Solution:Detect independence: verify attribute sets vary independently for a given key.If independence holds, recognize an MVD and the risk of redundancy.Decompose into separate key–attribute relations to reach 4NF.Confirm lossless join and constraint enforcement.Verification / Alternative check: Compare row counts pre- and post-decomposition. The inflated product of independent lists disappears in 4NF, while information content remains identical via joins.Why Other Options Are Wrong:Calling MVDs harmless ignores real update/insert/delete anomalies.FKs and indexes do not eliminate redundancy caused by MVDs.Dimensional models may tolerate controlled denormalization, but that is a deliberate trade-off, not evidence that MVDs are harmless in OLTP schemas.Common Pitfalls: Storing comma-separated lists; believing that validation at the application tier makes MVD redundancy acceptable; overlooking effects on analytics and reporting accuracy.Final Answer: Invalid (MVDs cause redundancy and anomalies; 4NF addresses them)

Question 16

Does proper normalization eliminate duplicated data?

Accepted Answer

Introduction / Context: A central goal of normalization is to eliminate unnecessary duplication that leads to anomalies. This does not mean the database contains no repeated values at all (for example, many rows may share the same city), but rather that redundant storage of the same fact across multiple rows or tables is minimized by proper design.Given Data / Assumptions:Redundancy driven by functional or multivalued dependencies is the target of normalization.Normal forms (1NF through BCNF/4NF/5NF) progressively remove types of redundancy.Some environments intentionally denormalize for performance, accepting controlled redundancy.Concept / Approach: By ensuring that every determinant is a key (BCNF) and separating independent multi-valued attributes (4NF), a design stops storing the same fact in multiple places. As a result, updates are localized, and consistency improves. While some repetition of domain values remains natural (for example, many customers may have state = CA), that repetition is not problematic because it does not represent duplicated facts.Step-by-Step Solution:Identify repeated facts (for example, department name repeated in every employee row).Create a separate relation for the fact (DEPARTMENT), reference it by key from EMPLOYEE.Enforce constraints (PK/FK/UNIQUE) to keep a single source of truth.Verify that updates to the fact occur once, eliminating duplication-based anomalies.Verification / Alternative check: Compare update paths pre/post normalization: changing a department’s name affects 1 row in DEPARTMENT rather than hundreds in EMPLOYEE, proving duplication removal.Why Other Options Are Wrong:Claiming normalization increases duplication confuses logical design with physical storage overhead (for example, additional keys).Surrogate keys, storage engines, or read-only status do not change the logical principle.Common Pitfalls: Equating repeating domain values with duplication of facts; assuming denormalization is always better for performance; neglecting constraints that enforce the intended design.Final Answer: Valid (proper normalization removes unnecessary duplication caused by dependencies)

Question 17

First step when given legacy tables: what should you do before creating the new database?

Accepted Answer

Introduction / Context: When handed legacy tables or files and asked to build a new database, it can be tempting to jump straight into creating a new database instance. However, sound methodology begins with analysis and modeling, ensuring the resulting schema accurately represents entities, relationships, and constraints before any physical implementation.Given Data / Assumptions:You have existing data, possibly messy or denormalized.Business rules and dependencies might not be documented.Performance and security requirements are still being gathered.Concept / Approach: The first step is discovery and modeling: profile the data, identify candidate keys and dependencies, detect anomalies, and draft the conceptual and logical models. Only after the logical design is validated should you create the physical database, define tables and constraints, and load cleansed data. This reduces rework and prevents propagating legacy flaws.Step-by-Step Solution:Profile data to find unique keys, nulls, and code inconsistencies.Draft an entity–relationship model and derive a normalized logical schema.Validate the model with stakeholders and sample data.Then create the database and implement the schema physically.Verification / Alternative check: Teams that begin with modeling typically perform fewer disruptive refactors later. Comparing project timelines shows that early design prevents costly rebuilds post-implementation.Why Other Options Are Wrong:“Immediately create the database” risks locking in a poor structure.Whether the legacy source is relational or whether backups exist is orthogonal to the proper first step.Common Pitfalls: Migrating column-for-column without understanding semantics; skipping constraints; overlooking hidden dependencies.Final Answer: Invalid (first create a conceptual/logical design and profile the data)

Question 18

Design from existing data: are inconsistent values a common problem?

Accepted Answer

Introduction / Context: When building a database from existing datasets, one of the first obstacles is data quality. Inconsistent values—such as multiple spellings for the same concept, irregular date formats, mixed units, or ad hoc code systems—can derail a clean relational design and must be identified and corrected during profiling.Given Data / Assumptions:Legacy sources may include spreadsheets, CSVs, or lightly constrained tables.Validation rules may have been enforced inconsistently, if at all.Multiple systems may have contributed data, each with different conventions.Concept / Approach: Recognize and standardize synonymous or conflicting values (for example, “NY”, “N.Y.”, “New York”). Create reference tables and enforce foreign keys. Apply CHECK constraints for formats and ranges. Normalize units and use consistent data types. These steps reduce heterogeneity so the model can enforce integrity reliably.Step-by-Step Solution:Profile each column for distinct values, patterns, and outliers.Map variants to canonical codes (for example, use ISO country/state codes).Introduce reference data tables and constraints to enforce consistency going forward.Backfill and correct legacy rows to meet the new standards.Verification / Alternative check: After cleansing and constraints, attempts to insert inconsistent data should fail, proving the system now enforces uniformity. Reports should show reduced distinct value counts for standardized domains.Why Other Options Are Wrong:Dismissing inconsistencies as rare is contradicted by practice.Presence of primary keys or file format does not prevent inconsistent domain values.Common Pitfalls: Overlooking subtle variations (case, whitespace, punctuation); failing to convert units; neglecting historical data that do not meet current rules.Final Answer: Valid (inconsistent codes and formats are common in legacy data)

Question 19

Read-only database design: do guidelines differ because updates never occur?

Accepted Answer

Introduction / Context: Read-only databases—such as reporting marts or historical archives—have different operational characteristics than online transaction processing (OLTP) systems. Since updates do not occur, designers can adopt guidelines that emphasize fast reads, summarization, and precomputation without worrying about update anomalies.Given Data / Assumptions:Workload is dominated by complex queries and aggregations.No user-initiated inserts/updates/deletes occur during normal operation.Data may be loaded in batches and then queried extensively.Concept / Approach: In read-only contexts, denormalization (for example, star schemas), materialized views, pre-aggregated tables, and additional indexes are common to accelerate analytics. Constraints may still be defined but are oriented toward ensuring load-time quality rather than enforcing transactional integrity during writes. Partitioning and columnar storage may be preferred for scan-heavy workloads.Step-by-Step Solution:Identify query patterns and required aggregations.Choose schemas favoring read performance (star/snowflake) and create useful indexes.Consider materialized views and summaries to avoid recomputation.Design batch ETL/ELT processes to refresh data while preserving query availability.Verification / Alternative check: Benchmark representative analytical queries on normalized vs. denormalized schemas. Read-only designs typically show markedly better performance with denormalization and appropriate indexing.Why Other Options Are Wrong:Claiming identical priorities ignores the lack of updates and different performance goals.Index status, hardware speed, or storage format alone do not define the design philosophy.Common Pitfalls: Over-denormalizing without documenting lineage; allowing uncontrolled duplication that complicates refreshes; forgetting to enforce integrity during data loading.Final Answer: Valid (read-only designs prioritize query performance and may denormalize)

Question 20

Identify the constraint type: "SALE.CNumber must exist in CUSTOMER.CNumber."

Accepted Answer

Introduction / Context: Understanding constraint types is essential for relational integrity. The statement "SALE.CNumber must exist in CUSTOMER.CNumber" specifies a rule connecting values across two tables. Determining the correct constraint type helps ensure correctness and efficient enforcement by the DBMS.Given Data / Assumptions:Table CUSTOMER has a column CNumber, typically declared as a primary key or at least UNIQUE/NOT NULL.Table SALE has a column CNumber that references CUSTOMER.CNumber.We are discussing declarative constraints supported by the DBMS.Concept / Approach: A referential integrity constraint (foreign key) ensures that each non-null value in a referencing column (SALE.CNumber) matches an existing value in the referenced column (CUSTOMER.CNumber). This preserves parent–child relationships and prevents orphan records. The DBMS enforces the rule on INSERT/UPDATE and, with defined actions, on DELETE/UPDATE in the parent.Step-by-Step Solution:Declare CUSTOMER.CNumber as a primary or unique key.Declare a foreign key on SALE(CNumber) referencing CUSTOMER(CNumber).Optionally specify ON DELETE/UPDATE actions (RESTRICT, CASCADE, SET NULL/DEFAULT).Verify that inserts into SALE require a matching CUSTOMER row.Verification / Alternative check: Attempt to insert SALE(CNumber) = 999 when no CUSTOMER row has CNumber = 999; the DBMS should reject the statement due to the foreign key violation.Why Other Options Are Wrong:Check constraints validate conditions within one row; they cannot consult another table’s values.Unique constraints apply to one table only and do not enforce cross-table existence.Triggers can enforce such rules, but this rule is expressible and best handled as a built-in foreign key constraint.Common Pitfalls: Forgetting to index the referencing column for performance; neglecting ON DELETE/UPDATE actions that match business rules; using triggers instead of declarative constraints unnecessarily.Final Answer: Valid (this is a referential integrity constraint/foreign key)

Database Design Using Normalization Questions

Questions

Practice smarter