Question 1

Database redesign and reverse engineering: does the data model produced from reverse engineering exclude associative (intersection) tables, or should those many-to-many bridge entities be captured in the model as well?

Accepted Answer

Introduction / Context: This statement tests your understanding of reverse engineering in database design. Reverse engineering converts an existing schema (tables, keys, constraints) back into a conceptual or logical data model that explains entities and relationships, including many-to-many associations that are commonly implemented as associative (intersection) tables. Given Data / Assumptions: You start from an existing relational schema. Intersection tables are present to implement many-to-many relationships (e.g., student_course). The goal is to recover a platform-neutral model (entities, attributes, and relationships). Concept / Approach: In reverse engineering, designers analyze primary keys, foreign keys, and naming conventions to infer which tables represent core entities and which are associative entities that bridge two parents. These associative tables are not discarded; rather, they are recognized and mapped to the correct many-to-many relationship (optionally kept as an explicit associative entity if it carries attributes like enrollment_date, role, or status). Step-by-Step Solution: Identify tables with a composite primary key made of two foreign keys to separate parent tables.Classify such tables as associative (intersection) tables.In the data model, represent the many-to-many association and keep the associative entity if it has attributes beyond the keys.Confirm that the reverse-engineered model does include these artifacts; it does not omit them. Verification / Alternative check: Run a schema mining tool that detects FK-to-PK patterns; it will flag intersection tables as relationship carriers between two entities. Why Other Options Are Wrong: Claiming the model omits intersection tables is incorrect; they are central to representing many-to-many relationships. DBMS licensing (open source vs. commercial) is irrelevant. Common Pitfalls: Confusing “data” with “structure.” Reverse engineering focuses on schema structure (tables/keys), not necessarily on exporting all row data. Another pitfall is treating every small table as lookup rather than recognizing an associative role. Final Answer: Incorrect — a proper reverse-engineered data model includes the associative (intersection) tables/relationships.

Question 2

In SQL, does NOT EXISTS evaluate to true when the subquery returns any matching row, or only when the subquery returns no rows at all?

Accepted Answer

Introduction / Context: EXISTS and NOT EXISTS are common SQL predicates used to test the presence or absence of rows from a subquery. Understanding their truth conditions is essential for accurate filtering. Given Data / Assumptions: A subquery is executed to check for matching rows. EXISTS is true if the subquery returns at least one row. NOT EXISTS negates that logic. Concept / Approach: NOT EXISTS is logically equivalent to “no rows returned by the subquery.” If the subquery produces even a single row, EXISTS evaluates to true and NOT EXISTS evaluates to false. This holds for both correlated and uncorrelated subqueries; correlation only affects how the subquery is parameterized, not the predicate’s definition. Step-by-Step Solution: Define semantics: EXISTS → at least one row, NOT EXISTS → zero rows.Examine the statement’s claim that “any row makes NOT EXISTS true.”Recognize the claim contradicts the definition.Conclude the statement is inaccurate (NOT EXISTS is true only when no rows are returned). Verification / Alternative check: Test with a tiny table. If the subquery SELECT 1 FROM dual (or any table) returns a row, EXISTS is true and NOT EXISTS is false; if it returns none, NOT EXISTS is true. Why Other Options Are Wrong: Correlation and GROUP BY do not alter the boolean meaning of NOT EXISTS; they only influence the subquery’s result set. Common Pitfalls: Confusing NOT EXISTS with NOT IN; NULL handling can make NOT IN tricky, whereas NOT EXISTS is robust to NULLs. Final Answer: Incorrect — NOT EXISTS is true only when the subquery returns no rows.

Question 3

Database projects: is it generally difficult to design and build a database correctly on the very first attempt, without iteration or refactoring?

Accepted Answer

Introduction / Context: Real-world data modeling involves understanding complex business rules, evolving requirements, and performance constraints. The first version of a schema often needs iteration to address edge cases, integrity rules, and workload realities. Given Data / Assumptions: Requirements change as stakeholders see prototypes. Query patterns and volumes are not always fully known up front. Normalization, indexing, and partitioning choices may need tuning after observation. Concept / Approach: Adopt an iterative approach: begin with a normalized logical model, validate with sample data and representative queries, then adjust physical design (indexes, partitions, materialized views) and sometimes refine the logical model to accommodate new constraints or performance goals. Step-by-Step Solution: Elicit requirements; draft an initial ER or relational model.Prototype and load sample data; measure typical queries.Collect feedback; identify gaps or inefficiencies.Refactor entities/relationships and adjust physical structures until objectives are met. Verification / Alternative check: Post-deployment monitoring usually reveals additional indexing needs, denormalization opportunities, or constraints to add. This empirical cycle confirms that first-attempt perfection is rare. Why Other Options Are Wrong: Perfection on the first try is unrealistic; technology choices or programming languages do not eliminate the need for iteration. Common Pitfalls: Locking the schema too early; skipping representative workload tests; over-denormalizing prematurely; omitting constraints for the sake of speed. Final Answer: Correct — building a database correctly the first time is uncommon; iterative refinement is the norm.

Question 4

Schema evolution: is adding new nullable columns to an existing relational table typically straightforward for both design and deployment?

Accepted Answer

Introduction / Context: Production databases often evolve. The ease of adding a column depends on whether default values are required and whether the column allows NULL. Nullable additions are typically the least disruptive change. Given Data / Assumptions: The new column allows NULL and has no mandatory default. No immediate backfill is required. The DBMS supports online DDL or minimally intrusive ALTER TABLE operations. Concept / Approach: When a column is nullable, existing rows do not need to be rewritten with a default value; they can logically treat the new attribute as “unknown/not applicable.” As a result, ALTER TABLE ADD COLUMN operations can often be fast and online (varies by DBMS), especially for metadata-only changes. Step-by-Step Solution: Assess whether the column can be NULL and whether a default is necessary.Execute ALTER TABLE ... ADD COLUMN ... NULL.Optionally backfill data in batches to avoid large blocking writes.Deploy application changes to read/write the new column gradually. Verification / Alternative check: DBMS documentation commonly cites that adding nullable columns is lighter weight than adding NOT NULL columns with defaults (which may rewrite the table). Why Other Options Are Wrong: Requiring full reloads or downtime is not universally true; many systems support online or low-impact schema changes. Index presence or isolation level do not determine feasibility by themselves. Common Pitfalls: Adding NOT NULL with default on huge tables may rewrite pages; plan carefully. Also verify ORM mappings and ensure application code handles NULL. Final Answer: Correct — adding a nullable column is generally straightforward.

Question 5

Naming policies: some organizations require that applications never reference base table names directly (e.g., forcing use of views, synonyms, or APIs). Is that a legitimate stance?

Accepted Answer

Introduction / Context: Abstraction layers (views, synonyms, stored procedures, service APIs) decouple applications from physical table structures. Some organizations formalize this decoupling as a policy: applications must not reference base tables directly. Given Data / Assumptions: Desire for schema evolution without breaking apps. Centralized security and auditing via controlled interfaces. Multiple consumers with differing needs. Concept / Approach: The policy allows DBAs to alter physical schemas (split/merge tables, change indexes/partitions) while preserving stable virtual contracts to applications. Views can expose only approved columns and can implement row/column security. APIs or stored procedures can validate and enforce business rules. Step-by-Step Solution: Create views or synonyms that present a stable logical schema.Grant privileges on these abstractions, not on base tables.Refactor base tables as needed; keep view contracts intact.Use versioned endpoints for breaking changes. Verification / Alternative check: Enterprise patterns (data services, data virtualization) commonly rely on this approach to avoid tight coupling and to centralize security. Why Other Options Are Wrong: Relational theory does not forbid indirection; it encourages logical independence. Limiting this to specific system types or dialects misses the architectural motivation. Common Pitfalls: Overusing views can mask performance issues; ensure underlying indexes match exposed query patterns. Keep documentation and versioning clear. Final Answer: Correct — some organizations deliberately prevent direct use of base table names by applications.

Question 6

Socio-technical perspective: do information systems and organizations not merely influence each other but, in effect, co-create each other over time?

Accepted Answer

Introduction / Context: Information systems both reflect and drive organizational processes, culture, and decision-making. This is a central tenet in information systems theory and practice. Given Data / Assumptions: Organizations adapt processes when new systems are deployed. Systems are tailored to organizational policies and constraints. Feedback loops cause mutual shaping over time. Concept / Approach: Socio-technical systems theory explains how technology and social structures interdepend. Implementing ERP, CRM, or data platforms changes roles, metrics, and incentives; in turn, organizational objectives and governance requirements shape subsequent system iterations. Step-by-Step Solution: Recognize bidirectional influence: requirements inform design.Deploy system; observe organizational changes (workflow, responsibilities).Gather feedback; adjust system and policies accordingly.Repeat as strategy and environment evolve. Verification / Alternative check: Case studies show major system rollouts altering reporting lines, approval flows, and decision rights, which then demand further system changes. Why Other Options Are Wrong: Claiming no organizational effect is unrealistic; limiting this phenomenon to startups ignores evidence across sectors. Common Pitfalls: Underestimating change management; focusing solely on features while ignoring training, incentives, and governance. Final Answer: Correct — organizations and information systems co-create and co-evolve.

Question 7

Reverse engineering definition: is reverse engineering the process of reading an existing database schema and generating a data model from that schema?

Accepted Answer

Introduction / Context: Reverse engineering is a standard activity when inheriting legacy systems or redesigning existing databases. It helps teams understand entities, keys, and relationships embodied in a live schema. Given Data / Assumptions: You have access to the DB schema (tables, columns, constraints). Tools or techniques analyze PK/FK structures to infer a model. The goal is a conceptual/logical representation independent of vendor specifics. Concept / Approach: Reverse engineering maps tables to entities, columns to attributes, PKs to identifiers, and FKs to relationships. Associative tables are recognized for many-to-many relationships, and additional rules (unique constraints, check constraints) are captured as model constraints. Step-by-Step Solution: Extract schema metadata.Identify entities and their identifiers from PKs/unique constraints.Derive relationships from foreign keys and cardinalities.Refine the model by naming standards and business semantics. Verification / Alternative check: ER tools can automatically generate diagrams from schema and then allow manual adjustments for business meaning. Why Other Options Are Wrong: Forward engineering is the opposite (model → schema). Database type and notation style do not change the definition. Common Pitfalls: Assuming schema names always match business semantics; overlooking constraints that imply optionality or exclusivity. Final Answer: Correct — reverse engineering reads the schema to produce a data model.

Question 8

Redesign hygiene: during a database redesign, is it typical to maintain at least two separate copies of the schema (for example, development and test) rather than working only against production?

Accepted Answer

Introduction / Context: Safe schema redesign requires separate environments to prototype changes, validate data migrations, and run regression tests without risking production integrity or availability. Given Data / Assumptions: Separate dev, test/QA, and production environments exist. CI/CD processes apply migrations progressively. Representative data is available in non-prod for realistic testing. Concept / Approach: Multiple schema copies support iterative design: developers experiment in dev; QA validates in test with realistic volumes; only then are changes promoted to prod. This approach reduces risk and surfaces performance or compatibility issues early. Step-by-Step Solution: Clone schema (and anonymized data) into dev and test.Apply migration scripts; run unit/integration/performance tests.Fix issues; repeat until stable.Promote to production via controlled release. Verification / Alternative check: Most SDLC best practices mandate ≥2 non-prod environments before production deployment. Why Other Options Are Wrong: Relying solely on production is unsafe. ORM usage or hosting model does not remove the need for separate environments. Common Pitfalls: Testing with unrealistic data sizes; skipping data migration rehearsals; inadequate rollback plans. Final Answer: Correct — keep at least dev and test schema copies during redesign.

Question 9

Careers in data quality: given that creating test databases and test data is critical, are there truly “few career opportunities” in this area?

Accepted Answer

Introduction / Context: Test databases, synthetic data generation, data masking, and environment management are vital to quality assurance, compliance, and performance testing. These capabilities underpin safe releases in data-intensive organizations. Given Data / Assumptions: Regulated industries require masked/anonymized test data. Large systems need realistic volumes for performance testing. CI/CD pipelines integrate database migration testing. Concept / Approach: Roles include Test Data Management (TDM) engineers, database QA engineers, data environment managers, SREs focusing on data, and compliance engineers. Tool ecosystems (masking, subsetting, virtualization) and cloud services increase demand for specialists in these domains. Step-by-Step Solution: Identify the organizational need for safe, realistic test data.Establish pipelines to provision, subset, mask, and refresh databases.Automate validation and integrate with CI/CD.Measure impact on release quality and speed, demonstrating value and supporting dedicated roles. Verification / Alternative check: Job boards and enterprise teams highlight ongoing hiring for QA data roles, especially where privacy regulations apply. Why Other Options Are Wrong: Calling opportunities “few” ignores widespread needs across industries; company size or language choice does not negate demand. Common Pitfalls: Using production data in test without masking; inadequate refresh cadence; ignoring performance parity between test and prod. Final Answer: Incorrect — this field offers many roles and steady demand.

Question 10

Subquery evaluation order: is a non-correlated (regular) subquery evaluated first (inner to outer), effectively processing from the bottom up?

Accepted Answer

Introduction / Context: Understanding evaluation order helps predict results and performance. In general, non-correlated subqueries are computed first and their result is then used by the outer query. Correlated subqueries may be re-evaluated per outer row. Given Data / Assumptions: “Regular” means non-correlated: the inner query does not reference columns from the outer. Optimizers may rewrite queries while preserving semantics. Logical evaluation order differs from physical execution plans, but the result is as if the inner were available first. Concept / Approach: For non-correlated subqueries, the DBMS can compute the inner result set once and reuse it, or transform the query (e.g., to a join). Either way, the semantics match “bottom-up” processing. Step-by-Step Solution: Identify a non-correlated subquery in SELECT, WHERE, or FROM.Recognize that its result does not depend on outer rows.DBMS evaluates or rewrites it so the outer query can reference the produced set/value.Therefore, the statement that regular subqueries are processed bottom-up is correct semantically. Verification / Alternative check: EXPLAIN plans often show subquery computation (or transformation to derived tables/joins) before outer operations. Why Other Options Are Wrong: Claiming subqueries are evaluated last contradicts typical semantics. GROUP BY presence or client software does not dictate logical order. Common Pitfalls: Assuming the same for correlated subqueries; those may run per outer row unless decorrelated by the optimizer. Final Answer: Correct — regular (non-correlated) subqueries are effectively processed bottom-up.

Question 11

In database design and normalization, a dependency graph (also called a functional-dependency diagram) uses nodes and directed arcs to illustrate attributes and their dependency relationships across a database schema. Is this description accurate?

Accepted Answer

Introduction / Context: Dependency graphs, often called functional-dependency (FD) diagrams, are visual tools used during logical database design and normalization. They show which attributes depend on which other attributes so designers can identify potential redundancy, update anomalies, and the need for decompositions into higher normal forms such as 3NF and BCNF. Given Data / Assumptions: A database schema contains relations (tables) and attributes (columns). Functional dependencies describe rules such as A -> B, meaning attribute A functionally determines attribute B. Graphs use nodes to represent attributes or attribute sets and directed arcs to indicate determination. Concept / Approach: By modeling dependencies as a directed graph, designers can quickly see determinants, candidate keys, and transitive chains. This supports decisions about decomposition to eliminate partial and transitive dependencies, thereby reducing redundancy and anomalies. Step-by-Step Solution: List discovered functional dependencies from requirements or profiling.Create nodes for attributes or attribute groups (e.g., {OrderID}, {CustomerID}, {CustomerCity}).Draw directed arcs from each determinant to the attributes it determines.Identify keys as determinants that reach all other attributes in the relation.Use the diagram to plan decompositions that remove non-key determinants. Verification / Alternative check: Cross-check the graph with normalization rules; if non-key determinants exist, anomalies are likely. Confirm by testing sample inserts/updates for inconsistency risks. Why Other Options Are Wrong: It is not limited to hierarchical databases; it is primarily used with relational schemas. “Cannot be determined” is incorrect because the definition of the diagram does not require sample data. Common Pitfalls: Confusing foreign-key relationships with functional dependencies, or omitting composite determinants (e.g., A,B -> C) from the diagram. Final Answer: Correct

Question 12

In standard SQL querying, the EXISTS predicate evaluates to true if the subquery returns at least one row that satisfies the given condition. Is that interpretation correct?

Accepted Answer

Introduction / Context: EXISTS is a core SQL predicate used to test for the presence of rows from a subquery. Understanding how EXISTS short-circuits and how it differs from IN and JOIN patterns is crucial for writing efficient, semantically correct queries. Given Data / Assumptions: The subquery can be correlated (referencing outer query columns) or uncorrelated. EXISTS returns a Boolean: true if at least one row qualifies, false otherwise. The subquery typically selects a minimal set of columns (often SELECT 1). Concept / Approach: EXISTS is evaluated as a semi-join test. The database engine stops scanning the subquery as soon as a single qualifying row is found. This makes it ideal for presence checks, especially with correlated subqueries and appropriate indexes. Step-by-Step Solution: Write a subquery expressing the matching condition.Wrap it in EXISTS (SELECT 1 FROM ... WHERE correlation).Rely on short-circuit behavior to avoid unnecessary work.Use NOT EXISTS for anti-join semantics (finding non-matching rows).Compare behavior to IN and LEFT JOIN ... IS NULL for clarity and performance. Verification / Alternative check: Test with sample data: add or remove a single qualifying row and observe the Boolean flip of EXISTS. Why Other Options Are Wrong: EXISTS does not require exactly one row or GROUP BY; it only needs at least one qualifying row. Common Pitfalls: Selecting many columns inside the EXISTS subquery (unnecessary), or expecting EXISTS to return data rather than a Boolean. Final Answer: Correct

Question 13

In SQL, a correlated subquery is fundamentally the same as a regular (uncorrelated) subquery and behaves identically during evaluation. Is that statement accurate?

Accepted Answer

Introduction / Context: Subqueries come in two major types: uncorrelated (regular) and correlated. Understanding the distinction is essential for predicting performance and correctness, particularly when filtering, computing derived values, or performing existence checks. Given Data / Assumptions: An uncorrelated subquery can be evaluated once independently of the outer query. A correlated subquery references columns from the outer query and must be re-evaluated for each outer-row candidate. Optimizers sometimes transform correlated subqueries into joins or semi-joins. Concept / Approach: Because correlated subqueries are dependent on the current outer row, they behave like nested loops conceptually: for each outer row, run the inner query. This often increases cost unless indexed properly or transformed by the optimizer. Regular subqueries, in contrast, can be computed once and reused. Step-by-Step Solution: Identify whether the subquery references outer query columns.If yes, treat it as correlated; expect row-by-row evaluation unless optimized.Index the correlated predicate columns to reduce repeated work.Consider rewriting as JOIN/EXISTS if it simplifies logic or improves performance.Benchmark both forms; let the optimizer choose the best plan. Verification / Alternative check: Explain plans typically show nested-loop-like behavior for correlated subqueries compared to a one-time computation for uncorrelated ones. Why Other Options Are Wrong: It is not “the same” for EXISTS or IN specifically—correlation changes evaluation strategy irrespective of the predicate used. Common Pitfalls: Assuming identical performance; forgetting indexes on correlated columns; misunderstanding that correlated subqueries return different results if the outer row changes. Final Answer: Incorrect

Question 14

Because database redesign requires deep SQL knowledge and careful schema refactoring, many teams rely on automated tools and generators to assist the redesign process. Is this generally a fair characterization?

Accepted Answer

Introduction / Context: Database redesign encompasses reverse engineering, normalization, refactoring, migration planning, and impact analysis on applications and reports. Due to complexity and risk, teams often use specialized tooling to automate portions of the workflow. Given Data / Assumptions: Redesign affects DDL, constraints, indexes, and stored code. Downstream dependencies include ETL, APIs, ORMs, and BI models. Tools can automate discovery, dependency mapping, and script generation. Concept / Approach: Automation assists but does not replace engineering judgment. Tools build dependency graphs, suggest keys and FKs, compare schemas, and generate change scripts. They also help validate data quality, enforce naming standards, and produce migration plans with rollback steps. Step-by-Step Solution: Reverse engineer the current schema and dependencies.Identify normalization or partitioning opportunities.Use tooling to generate DDL diffs and migration scripts.Dry-run migrations in non-production; benchmark key queries.Deploy with monitoring and backout plans. Verification / Alternative check: Compare manual and tool-generated plans for coverage and accuracy; unit and integration tests confirm behavior. Why Other Options Are Wrong: The statement is not limited to NoSQL or empty databases; automation benefits most environments. Common Pitfalls: Overreliance on tools without understanding semantics; inadequate testing of generated scripts. Final Answer: Correct

Question 15

Once a database is designed “correctly” the first time, it will never need redesign or refactoring in the future. Is this claim valid given evolving business requirements and workloads?

Accepted Answer

Introduction / Context: Database schemas are living artifacts. Even a well-designed schema must adapt to new features, regulations, volumes, and performance constraints. This question challenges the idea that an initially perfect design eliminates future redesign. Given Data / Assumptions: Business rules evolve (pricing, tax, products, workflows). Nonfunctional needs change (scale, latency, availability). Technology shifts (new engines, indexing, partitioning). Concept / Approach: Good design anticipates change via normalization, abstraction, and modularity, but cannot foresee everything. Redesign may introduce new entities, refactor relationships, partition large tables, or denormalize selectively for analytics. Step-by-Step Solution: Monitor pain points (slow queries, anomalies).Collect requirements and profile data usage.Propose minimal, consistent schema changes.Test migrations with representative loads.Deploy iteratively with rollback strategies. Verification / Alternative check: Historical change logs in mature systems show periodic schema evolution driven by real-world needs. Why Other Options Are Wrong: Using BCNF or being read-only does not freeze requirements; archival systems may still need new compliance attributes or access patterns. Common Pitfalls: Deferring necessary refactors; making piecemeal changes that create inconsistencies. Final Answer: Incorrect

Question 16

Is database redesign especially difficult when a database has no data at all, compared to when it contains production data that must be preserved and migrated?

Accepted Answer

Introduction / Context: Redesign difficulty depends on both schema complexity and the presence of data that must be maintained. This question probes whether the absence of data makes the process harder than having a populated system. Given Data / Assumptions: Schema-only databases have no migration risk. Populated databases require data transformation, validation, and downtime planning. Reverse engineering business rules can benefit from data samples but is not strictly required. Concept / Approach: In practice, redesign is generally easier without data because you avoid complex migrations, re-keying, backfills, and data-quality issues. Although the lack of sample data can hinder discovery of edge cases, the high-cost activities (data movement and validation) are minimized. Step-by-Step Solution: If data exists, profile it; map transforms and build repeatable migration jobs.If no data exists, focus on schema correctness, constraints, and tests.Design validations to catch issues when data arrives (NOT NULL, CHECK, FK).Use seed datasets and synthetic data for early testing.Plan rollouts and backout strategies when real data is introduced. Verification / Alternative check: Compare effort: projects with terabytes of data typically invest far more time in migration planning than schema-only refactors. Why Other Options Are Wrong: Claiming it is “especially difficult” with no data overlooks the heavy lift of data migration and validation in populated systems. Common Pitfalls: Relying solely on synthetic data; forgetting that constraints and indexes must be tuned once real data patterns appear. Final Answer: Incorrect

Question 17

Does the model obtained by reverse engineering an existing database (from DDL and catalog introspection) directly yield a fully normalized, platform-agnostic logical data model?

Accepted Answer

Introduction / Context: Reverse engineering extracts a structural view of an existing database. However, physical artifacts (surrogate keys, indexes, denormalizations, naming quirks) mean the result is typically a physical/conceptual hybrid, not a pure logical model aligned to business semantics. Given Data / Assumptions: DDL reveals tables, columns, constraints, and sometimes comments. Physical design choices reflect performance or legacy constraints. Logical models abstract from platform specifics and emphasize semantics. Concept / Approach: The reverse-engineered output is a starting point. Analysts must remove purely physical concerns, consolidate synonyms, resolve overloaded columns, and reintroduce missing entities and relationships discovered via requirements analysis. Step-by-Step Solution: Extract schema objects and constraints.Map tables to entities and identify true business keys.Eliminate denormalizations; model many-to-many with association entities.Standardize names and data domains.Validate with stakeholders to align with business language. Verification / Alternative check: Walk through user stories with both models; mismatches highlight where the reverse-engineered schema diverges from business semantics. Why Other Options Are Wrong: Even BCNF sources may include platform-specific artifacts. ORM-generated schemas can still include compromises that are not logical-model constructs. Common Pitfalls: Accepting the physical schema as “the” logical truth; failing to capture business rules not enforced in DDL. Final Answer: Incorrect

Question 18

Processing model: Is a correlated subquery executed as a nested subquery that is evaluated once per candidate row of the outer query (subject to optimizer rewrites)?

Accepted Answer

Introduction / Context: Correlated subqueries are a core SQL pattern for existence checks and scalar lookups. Their evaluation model influences performance and indexing strategy. Given Data / Assumptions: The inner subquery references columns from the outer query. Semantically, the inner subquery must consider each outer row. Optimizers may transform correlated subqueries into equivalent joins or semi-joins. Concept / Approach: Conceptually, a correlated subquery is nested: for each outer row, run the inner query using that row’s values. Engines avoid repeated work where possible via rewrites, caching, and indexes, but the logical model remains a per-row evaluation. Step-by-Step Solution: Detect correlation by references to outer columns.Ensure selective predicates and supporting indexes on inner tables.Consider EXISTS/NOT EXISTS for semi-join/anti-join semantics.Rewrite to JOINs where clearer or faster; verify with EXPLAIN plans.Measure performance with realistic data distributions. Verification / Alternative check: EXPLAIN plans display nested-loop behavior or transformed join operators; both are consistent with per-row semantics. Why Other Options Are Wrong: The behavior does not depend on DISTINCT or GROUP BY; correlation itself drives per-row evaluation. Common Pitfalls: Missing indexes on correlated predicates; assuming the optimizer will always rewrite to a join. Final Answer: Correct

Question 19

Standards question: Is the SQL-92 standard command to rename a table literally RENAME TABLE, or is table renaming vendor-specific (for example, ALTER TABLE ... RENAME TO)?

Accepted Answer

Introduction / Context: Renaming tables is a common maintenance task. However, SQL standards and vendors differ in syntax and capabilities, which can cause portability issues in migration scripts. Given Data / Assumptions: SQL-92 does not define a universal RENAME TABLE statement. Vendors use different syntaxes: many support ALTER TABLE old_name RENAME TO new_name; MySQL supports RENAME TABLE. Constraints, dependencies, and privileges can be affected by renames. Concept / Approach: Because the standard does not mandate RENAME TABLE, portability requires conditional logic or abstractions. Use vendor-specific DDL in deployment tooling and verify effects on foreign keys, triggers, views, and stored procedures. Step-by-Step Solution: Check the target DBMS documentation for supported rename syntax.Assess dependent objects (FKs, views, procs) and regenerate as needed.Apply the vendor-appropriate rename DDL in a transaction if supported.Run dependency checks to catch broken references.Update permissions and metadata if required. Verification / Alternative check: Attempt the standard-looking RENAME TABLE on multiple engines; observe that only some (e.g., MySQL) accept it, while others require different syntax. Why Other Options Are Wrong: The statement is not correct in SQL-92; FK presence is unrelated to whether the syntax exists. Common Pitfalls: Assuming portability; forgetting to refresh dependent views or stored code after renames. Final Answer: Incorrect

Question 20

Can a double application of NOT EXISTS (for example, using NOT EXISTS ... NOT EXISTS patterns) be used effectively to find rows that do not match or fail to satisfy a specified condition (classic anti-join or relational division patterns)?

Accepted Answer

Introduction / Context: NOT EXISTS is widely used for anti-joins: returning rows from one set that do not have matching rows in another. A double NOT EXISTS pattern is also common in relational division problems (e.g., find customers who bought all items in a set). Understanding these constructs is critical for writing expressive, performant SQL without resorting to procedural loops. Given Data / Assumptions: We have a parent set (e.g., customers) and a child set (e.g., orders or order lines). We need either non-matching rows or “for all” logic (division), such as “customers who bought every product in a list.” Appropriate indexes exist on join keys. Concept / Approach: Single NOT EXISTS implements anti-join: return parent rows for which no child row satisfies the condition. Double NOT EXISTS implements “not exists a required element that is not matched,” which is equivalent to “for all required elements, at least one match exists.” This pattern elegantly encodes universal quantification in SQL. Step-by-Step Solution: For anti-join: SELECT p.* FROM P p WHERE NOT EXISTS (SELECT 1 FROM C c WHERE c.key = p.key).For division: SELECT p.* FROM P p WHERE NOT EXISTS (SELECT 1 FROM R r WHERE NOT EXISTS (SELECT 1 FROM C c WHERE c.pkey = p.key AND c.attr = r.attr)).Ensure indexes on keys used in correlated predicates.Validate correctness with controlled test data.Compare to LEFT JOIN ... WHERE child.key IS NULL for anti-joins. Verification / Alternative check: Construct small sets where expected results are obvious; confirm that NOT EXISTS and double NOT EXISTS return exactly those rows. Why Other Options Are Wrong: NULLs in unrelated columns do not invalidate NOT EXISTS logic; modern SQL fully supports these constructs. Common Pitfalls: Forgetting to correlate correctly; missing indexes; misapplying NOT IN with NULLs, which behaves differently. Final Answer: Correct

Database Redesign Questions