Question 1

In standard SQL Data Definition Language (DDL), which command correctly adds a new column to an existing table (syntax shown generically without a data type)?

Accepted Answer

Introduction / Context: Schema evolution often requires adding new columns. Using the correct Data Definition Language (DDL) syntax avoids errors and ensures portability across relational databases, especially those that follow SQL standards closely. Given Data / Assumptions: You have an existing table and need to add a column. Some DBMSs require specifying the data type (for example, ADD COLUMN status TEXT). We are focusing on the canonical SQL form. Concept / Approach: The standard command to change a table definition is ALTER TABLE. To add a column, use ADD COLUMN followed by the column name (and typically a data type and optional constraints). Some engines permit ADD without the word COLUMN, but ADD COLUMN is widely supported and self-documenting. Step-by-Step Solution: Identify the target table.Issue: ALTER TABLE TableName ADD COLUMN ColumnName DataType [constraints];Backfill or update data as needed, and consider defaults or NOT NULL with care. Verification / Alternative check: Execute the statement in a development environment and describe the table to confirm the new column appears with the correct definition. Why Other Options Are Wrong: MODIFY TABLE: Not standard SQL; some vendors use ALTER TABLE exclusively. ADD ColumnName without COLUMN: May work on some DBMSs, but the canonical form includes COLUMN and increases portability. APPEND COLUMN: Not a standard SQL keyword for this operation. Common Pitfalls: Adding a NOT NULL column without a DEFAULT to a large table can fail or lock for long periods. Plan migrations with defaults and backfill strategies. Final Answer: ALTER TABLE TableName ADD COLUMN ColumnName

Question 2

Understanding the NOT EXISTS keyword in SQL The NOT EXISTS keyword in SQL will evaluate to TRUE under which condition?

Accepted Answer

Introduction / Context: The NOT EXISTS keyword in SQL is a logical operator often used in correlated subqueries. It helps identify rows in the outer query for which no corresponding match exists in the inner query. Understanding how NOT EXISTS is evaluated is crucial for accurate query writing and avoiding unexpected results. Given Data / Assumptions: We are using a correlated subquery that checks for conditions against an outer query row. The evaluation returns TRUE or FALSE depending on the presence or absence of rows in the subquery result. The database stops evaluating as soon as it determines the result (short-circuit evaluation). Concept / Approach: The EXISTS operator checks for the presence of at least one row that satisfies the subquery condition. NOT EXISTS inverts this logic. Therefore, NOT EXISTS returns TRUE only if the subquery does not return any rows that meet the given condition. This means all rows effectively fail to meet the condition, or no rows exist at all. Step-by-Step Solution: Step 1: Write a correlated subquery such as: SELECT * FROM Customers c WHERE NOT EXISTS (SELECT 1 FROM Orders o WHERE o.CustomerID = c.CustomerID).Step 2: For each customer, the subquery checks if at least one order exists.Step 3: If no orders exist for that customer, the subquery returns no rows, making NOT EXISTS evaluate to TRUE.Step 4: If at least one order is found, the EXISTS condition is TRUE, so NOT EXISTS becomes FALSE. Verification / Alternative check: A quick verification: compare NOT EXISTS with NOT IN. While both can filter missing matches, NOT EXISTS is usually safer when NULL values may appear in the subquery results. Why Other Options Are Wrong: A: Any row meeting the condition would make EXISTS true, and NOT EXISTS false. C: Both conditions cannot hold simultaneously since they are mutually exclusive. D: Neither condition is not logical in this context; SQL evaluates presence/absence directly. E: NULLs do not directly affect EXISTS; only the presence of rows matters. Common Pitfalls: Confusing NOT EXISTS with NOT IN; NULL behavior is different. Believing that NOT EXISTS checks all rows — it simply checks if any row exists; once found, it determines the truth value. Forgetting to correlate the subquery with the outer query, leading to unintended results. Final Answer: If all rows in the subquery fail the condition.

Question 3

How SQL evaluates regular (non-correlated) subqueries Conceptually, a regular subquery (not correlated) is evaluated and combined with the outer query by which processing approach?

Accepted Answer

Introduction / Context: Subqueries are SELECT statements embedded inside another SQL statement. Understanding how a regular (non-correlated) subquery is evaluated helps you predict results, reason about NULL behavior, and rewrite queries for performance or clarity. Given Data / Assumptions: We are discussing regular subqueries (the inner query does not reference columns from the outer query). The outer query may use the subquery result in predicates such as IN, NOT IN, EXISTS, ANY, ALL, or as a scalar value. The DBMS optimizer may choose alternative physical plans, but the logical evaluation model still holds. Concept / Approach: Conceptually, a regular subquery is evaluated first to obtain a result set (or a single scalar). That result is then “plugged into” the outer query expression. This is often described as processing by nesting. Although optimizers can flatten or transform subqueries into joins, the logical semantics remain: evaluate the inner request, then apply it. Step-by-Step Solution: Write an outer SELECT that references a subquery, e.g., WHERE id IN (SELECT id FROM t2 WHERE flag = 1).Interpretation: compute the inner SELECT first to get the list of ids.Use that computed set in the outer query's predicate to filter rows.Recognize that optimizers may convert this to a semijoin for efficiency, but the logical nesting semantics still apply. Verification / Alternative check: Explaining plans frequently show subquery computation (or its equivalent join after transformation). The logical reading in documentation consistently presents regular subqueries as values supplied to the outer query. Why Other Options Are Wrong: A: “Top down” implies evaluating the outer query without the subquery result, which is not how semantics are defined. D: The nesting model is the correct logical explanation. E: Hash join is a possible physical method after transformation, not a requirement. Common Pitfalls: Confusing correlated and non-correlated subqueries; correlated ones run per outer row conceptually. Assuming physical execution must match the logical nesting order exactly. Final Answer: By nesting (the DBMS computes the subquery result and then applies it in the outer query).

Question 4

Finding rows that do NOT meet a specified condition In standard SQL, which construct is most appropriate to return rows that have no matching rows satisfying a condition in a related set?

Accepted Answer

Introduction / Context: It is common to ask for rows that do not have a related record, such as customers without orders or products without prices. SQL provides several ways to express anti-matching; choosing the right one affects both correctness and performance. Given Data / Assumptions: A main table (outer query) and a related table (inner subquery) exist. We want rows where no qualifying related rows exist. NULL semantics can affect IN/NOT IN behavior. Concept / Approach: NOT EXISTS expresses anti-matching cleanly: it is TRUE when the subquery returns no rows meeting the specified condition. Because EXISTS/NOT EXISTS respond only to row presence (not values), they avoid pitfalls where NULLs in subquery results can cause unexpected filtering (common with NOT IN). Step-by-Step Solution: Frame a correlated subquery: SELECT * FROM customers c WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id).The subquery searches for any related orders for the current customer.If none are found, NOT EXISTS evaluates to TRUE and the customer is returned.If at least one exists, NOT EXISTS is FALSE and the row is excluded. Verification / Alternative check: Replace NOT EXISTS with LEFT JOIN ... WHERE o.customer_id IS NULL as an equivalent anti-join; both convey the same intent when written correctly and with appropriate NULL handling. Why Other Options Are Wrong: A: EXISTS returns rows when a match does exist, not when it does not. B: Doubling NOT EXISTS is unnecessary and creates confusion. D/E: Incorrect or error-prone approaches, especially with NULLs in IN lists. Common Pitfalls: Using NOT IN with a subquery that returns NULLs, which can exclude all rows. Forgetting correlation, leading to global NOT EXISTS that returns unintended results. Final Answer: NOT EXISTS

Question 5

Dropping a column from a table Which SQL statement uses standard syntax to remove an existing column from a table definition?

Accepted Answer

Introduction / Context: Changing a table's structure is a Data Definition Language (DDL) task. Dropping a column must follow the DBMS's accepted ALTER TABLE syntax. Using a portable, standards-aligned form reduces surprises across platforms. Given Data / Assumptions: We want to remove a column permanently from a table. We will handle dependencies such as indexes, constraints, or generated columns first if required by the DBMS. We aim for widely supported wording. Concept / Approach: The canonical form is ALTER TABLE ... DROP COLUMN ... . Many vendors also accept slight variations, but the explicit DROP COLUMN keyword pair is the most standard and clear. Always review constraints and foreign keys that reference the column before dropping. Step-by-Step Solution: Issue: ALTER TABLE TableName DROP COLUMN ColumnName;Confirm there are no dependent indexes or constraints; drop them first if necessary.Verify application code and views do not reference the removed column.Apply in a maintenance window if the table is large to avoid long locks. Verification / Alternative check: Most mainstream DBMS documentation (e.g., PostgreSQL, SQL Server, Oracle, MySQL) supports ALTER TABLE ... DROP COLUMN, sometimes with additional options (CASCADE, RESTRICT). Why Other Options Are Wrong: A/B/E: MODIFY/CHANGE TABLE are not standard SQL statements. D: Some vendors allow DROP without COLUMN, but DROP COLUMN is clearer and more portable. Common Pitfalls: Dropping a column that is part of a foreign key or primary key without first adjusting constraints. Forgetting to update ORM mappings and ETL scripts. Final Answer: ALTER TABLE TableName DROP COLUMN ColumnName

Question 6

SQL for database redesign analysis Which SQL techniques are commonly helpful when analyzing an existing schema during database redesign or reverse engineering?

Accepted Answer

Introduction / Context: Database redesign often begins with discovering how data is actually used, where integrity rules are enforced, and which rows have or lack related rows. SQL constructs like correlated subqueries and EXISTS/NOT EXISTS are invaluable for profiling relationships and finding anomalies. Given Data / Assumptions: An existing database with tables, keys, and varying data quality. We need to find orphans, duplicates, and optional relationships in practice. We may not have perfect documentation of constraints. Concept / Approach: Correlated subqueries allow row-by-row checks against related tables. EXISTS/NOT EXISTS provide efficient presence/absence tests. Together, they expose referential patterns, cardinalities, and exceptions that shape the new design, indexes, and constraints. Step-by-Step Solution: Use EXISTS to confirm relationships: SELECT 1 WHERE EXISTS (SELECT 1 FROM Child WHERE Child.parent_id = Parent.id).Use NOT EXISTS to find orphans: SELECT * FROM Parent p WHERE NOT EXISTS (SELECT 1 FROM Child c WHERE c.parent_id = p.id).Use correlated counts to check multiplicities: SELECT p.id, (SELECT COUNT(*) FROM Child c WHERE c.parent_id = p.id) AS child_cnt ...Aggregate findings to decide on constraints, indexes, and normalization/denormalization. Verification / Alternative check: Explain plans typically show semi-joins or anti-joins for EXISTS/NOT EXISTS, which scale well with appropriate indexes and confirm their suitability for redesign analysis. Why Other Options Are Wrong: A or B alone are too restrictive; both tools are useful. D: Incorrect—these are precisely the tools redesigners use. E: Window functions are powerful but serve different profiling needs. Common Pitfalls: Relying on NOT IN with NULL-producing subqueries, which can mislead results. Ignoring index support on foreign key columns during analysis. Final Answer: Both of the above are helpful

Question 7

Seeing structure dependencies clearly Which artifact best helps designers understand dependencies among database structures (such as which objects rely on others)?

Accepted Answer

Introduction / Context: During maintenance or redesign, we must know how objects depend on each other. Dropping or altering an object without understanding dependencies can break views, functions, triggers, or downstream jobs. Visual tools make this analysis faster and safer. Given Data / Assumptions: The schema contains tables, views, materialized views, procedures, and constraints. We need to map “uses/depends on” relationships. We will use the artifact to plan safe changes. Concept / Approach: A dependency graph explicitly draws nodes (objects) and edges (dependencies). Unlike a general ER data model, which focuses on logical entities and relationships, a dependency graph captures executable and compile-time links among database objects, guiding change sequencing and impact analysis. Step-by-Step Solution: Enumerate objects and extract dependency metadata from the catalog.Build a directed graph where an edge means “object A depends on object B.”Use the graph to find safe drop/alter orders via topological sorting.Validate refactoring plans and regression-test impacted objects. Verification / Alternative check: Most DBMSs expose dependency views (e.g., information_schema, pg_catalog). Visualizing them as a graph quickly reveals clusters and critical hubs. Why Other Options Are Wrong: B: A data model is valuable but focuses on logical design, not executable dependencies. C: “Graphical display” is vague; usefulness comes from explicit dependency edges. D/E: Provide insufficient detail for dependency analysis. Common Pitfalls: Confusing ER relationships (business semantics) with executable dependencies (build/run order). Ignoring cross-schema or cross-database dependencies. Final Answer: A dependency graph.

Question 8

Reverse engineering outcome When you reverse engineer an existing relational database into a model, the resulting representation is typically a:

Accepted Answer

Introduction / Context: Reverse engineering extracts structure from an existing database to create documentation and a foundation for redesign. The target audience needs an understandable, technology-agnostic view of entities and relationships—not raw storage internals. Given Data / Assumptions: The source is a live schema with tables, keys, and constraints. We wish to capture entities, attributes, and relationships independent of file layouts or hardware. The model will inform normalization, naming standards, and application integration. Concept / Approach: A logical data model abstracts physical details while keeping implementable structures: tables (entities), columns (attributes), primary/foreign keys (relationships), and cardinalities. Conceptual models are higher level and often precede implementation; internal/physical models focus on storage, partitions, and indexes. Reverse engineering typically produces a logical model closest to what exists. Step-by-Step Solution: Read metadata (catalog) for tables, columns, and constraints.Map them into entities, attributes, and keyed relationships.Generalize naming and datatype domains without vendor-specific quirks.Document assumptions and anomalies for later cleanup. Verification / Alternative check: Modeling tools (e.g., ER/Studio, ERwin) export logical ER diagrams from existing schemas, confirming the typical reverse-engineering outcome. Why Other Options Are Wrong: A: Conceptual models omit many implementation details needed for redesign. B: Internal models are too low-level and vendor-specific for initial analysis. D/E: Do not reflect standard data modeling deliverables. Common Pitfalls: Copying vendor datatypes verbatim instead of mapping to logical domains. Failing to capture unique keys and alternate keys critical for integrity. Final Answer: Logical model (tables, keys, and relationships abstracted from physical specifics).

Question 9

Understanding EXISTS in SQL The EXISTS keyword in SQL evaluates to TRUE under which circumstance?

Accepted Answer

Introduction / Context: EXISTS and NOT EXISTS are fundamental to writing presence/absence logic in SQL, especially for correlated subqueries. They check for the existence of qualifying rows rather than specific values, which makes them robust in the presence of NULLs. Given Data / Assumptions: We are using a subquery that may return zero or more rows. EXISTS evaluates to a Boolean based solely on row presence. The subquery is often correlated to the outer row. Concept / Approach: EXISTS returns TRUE as soon as the subquery finds a single row satisfying the condition. The values returned by the subquery do not matter; even SELECT 1 works because only existence matters. If no qualifying row is found, EXISTS returns FALSE. Step-by-Step Solution: Write: WHERE EXISTS (SELECT 1 FROM Child c WHERE c.parent_id = p.id).If at least one Child row exists for the current Parent, the condition is TRUE.If none exist, the condition is FALSE.Short-circuit behavior means evaluation stops on the first match. Verification / Alternative check: Compare with IN; EXISTS vs IN can be equivalent, but EXISTS is safer with NULLs. Query plans commonly show semi-joins for EXISTS predicates. Why Other Options Are Wrong: B: Describes NOT EXISTS, not EXISTS. C/D: Mutually incompatible and not how EXISTS works. E: NULLs do not matter; only the presence of a row matters. Common Pitfalls: Expecting EXISTS to count rows; it does not—it is a Boolean test. Returning actual columns in the EXISTS subquery unnecessarily; SELECT 1 is sufficient. Final Answer: When any row in the subquery meets the condition only.

Question 10

Renaming a table with standard SQL According to the SQL-92 family of standards and common vendor practice, which command is used to change a table’s name (with the appropriate sub-clause)?

Accepted Answer

Introduction / Context: Renaming a table is a DDL operation. While specific syntax varies slightly, standards-aligned and widely supported approaches use the ALTER TABLE command with a RENAME clause (for example, ALTER TABLE old_name RENAME TO new_name). Given Data / Assumptions: We intend to change the identifier of an existing table. We are targeting portable, standards-style SQL. Privileges, dependencies, and locks are handled appropriately. Concept / Approach: The generic approach is ALTER TABLE with a RENAME sub-clause. Different vendors support variants (e.g., Oracle: RENAME old TO new or ALTER TABLE ... RENAME TO; PostgreSQL: ALTER TABLE ... RENAME TO ...; MySQL: RENAME TABLE ... also exists). The safest standards-oriented answer is ALTER TABLE as the top-level verb for renaming. Step-by-Step Solution: Use: ALTER TABLE old_name RENAME TO new_name; (or the vendor-equivalent).Ensure dependent objects (views, triggers) adapt automatically or are updated.Consider a synonym/alias to preserve compatibility if needed.Perform the change in maintenance windows to minimize impact. Verification / Alternative check: Vendor documentation confirms ALTER TABLE as the canonical DDL verb for renaming with RENAME TO sub-clause in standards-compliant engines. Why Other Options Are Wrong: A: RENAME TABLE exists in some DBMSs, but it is not the generic standards-aligned answer. B/E: Not standard SQL verbs for renaming. D: Incorrect because ALTER TABLE supports renaming. Common Pitfalls: Overlooking dependencies such as foreign keys and code that references the old name. Renaming in production without checking replication and tooling impacts. Final Answer: ALTER TABLE

Question 11

Safely dropping a foreign-key column Before you drop a column that serves as a foreign key in a relational table, what must you do first?

Accepted Answer

Introduction / Context: Foreign keys enforce referential integrity between a child table and a parent table. DDL operations that remove or alter the foreign-key column must respect these constraints to keep the catalog in a consistent state. Given Data / Assumptions: A column participates in a FOREIGN KEY constraint referencing a parent table. We intend to drop that column from the child table. We have privileges to alter tables and constraints. Concept / Approach: Because the column is referenced by a constraint, the DBMS will block dropping it until the constraint is removed or altered. The correct sequence is to drop (or disable, depending on the DBMS) the foreign key constraint first, then drop the column. Dropping the primary key or the entire table is unnecessary and destructive relative to the stated goal. Step-by-Step Solution: Identify the constraint name via catalog views (e.g., information_schema or system catalogs).Execute ALTER TABLE Child DROP CONSTRAINT fk_child_parent; (vendor syntax may vary).After the constraint is removed, issue ALTER TABLE Child DROP COLUMN parent_id;Recreate constraints or adjust schema as needed. Verification / Alternative check: DBMS documentation universally prevents dropping a constrained column without first removing the dependent constraint, confirming the required order of operations. Why Other Options Are Wrong: A: Dropping the primary key is unrelated and may introduce other integrity problems. B: Dropping the table is excessive; we only need to change one column. D: Not all listed steps are required; only removing the FK constraint is. E: Cache settings are irrelevant to DDL constraint rules. Common Pitfalls: Forgetting indexes that may also exist on the FK column (drop or keep as needed). Overlooking application code that relies on the relationship. Final Answer: Drop the foreign key constraint.

Question 12

Before attempting any changes to a database's structure (schema or physical design), what complete set of preparatory actions should be taken to minimize risk and ensure recoverability and correctness?

Accepted Answer

Introduction / Context: Safe database change management requires planning, validation, and rollback readiness. Even a small structural change (for example, adding a constraint or altering a column type) can impact applications, data quality, and availability. This question asks for the full set of prudent, professional actions to perform before making any database structural modifications. Given Data / Assumptions: You plan to change schema objects such as tables, indexes, constraints, or triggers. The database contains production data that must be protected. You have access to a non-production environment for testing. Concept / Approach: Best practice combines three pillars: understanding, testing, and backup. First, thoroughly understand the current schema and data so you can predict side effects. Second, test proposed changes in a separate test or staging database to confirm behavior and performance. Third, take a full, restorable backup of the production database so you can recover quickly if something goes wrong. Step-by-Step Solution: 1) Document the current schema, dependencies, and data volumes; review application usage of affected objects.2) Rehearse the change in a test environment using production-like data; validate DDL, DML side effects, and performance.3) Create and verify a complete backup; confirm restore steps and timing.4) Schedule the change with stakeholders; implement with monitoring and a rollback plan. Verification / Alternative check: Perform a trial recovery from the backup in a sandbox. Validate that the restored database is consistent and applications can connect. This confirms that the backup truly protects you. Why Other Options Are Wrong: Option A: Understanding alone is insufficient without testing and backups. Option B: Testing without understanding or backup still risks production impact. Option C: A backup alone prevents data loss but does not ensure correctness or performance of the change. Common Pitfalls: Skipping test rehearsals, assuming a backup is valid without restore testing, and overlooking dependencies such as views, ETL jobs, or app code that rely on the old schema. Final Answer: All of the above should be done.

Question 13

During a structured database redesign process, how many parallel copies (environments) of the schema are typically maintained to separate development, testing, and production concerns?

Accepted Answer

Introduction / Context: Professional database change workflows separate concerns across environments. Isolating work-in-progress from validation and from live operations reduces risk, clarifies responsibilities, and enables controlled deployments. The question asks how many distinct schema copies are typically used in practice. Given Data / Assumptions: Teams follow a change management lifecycle. Production data must remain stable and available. Testing must happen before release to production. Concept / Approach: A common pattern is to maintain three environments: development (or sandbox) for rapid iteration, test (or QA/staging) for validation under realistic data and workload, and production for live users. Each environment has its own schema copy so that DDL/DML can be rehearsed and verified without jeopardizing production. Step-by-Step Solution: 1) Development (Dev): implement and refine DDL and migration scripts.2) Test/QA (Stage): validate correctness, performance, and compatibility with applications.3) Production (Prod): deploy approved changes with backups and monitoring in place. Verification / Alternative check: Audit trails and release notes should show each change advancing from Dev → Test → Prod with sign-offs, demonstrating three separate schema copies during the process. Why Other Options Are Wrong: One: mixes development with production, high risk. Two: lacks a dedicated test stage or a safe development area. Four: possible in larger orgs (e.g., perf, UAT), but “typical” is three. Common Pitfalls: Testing only on developers’ machines, skipping staging, or allowing schema drift between environments due to manual, unscripted changes. Final Answer: Three

Question 14

In practical database evolution, how common is the need to change relationship cardinalities (for example, from one-to-many to many-to-many) as requirements evolve?

Accepted Answer

Introduction / Context: Cardinality expresses how many rows in one table can relate to rows in another. Real-world businesses evolve, and occasionally the original assumptions about cardinality change (for example, a customer previously allowed a single active contract may now hold multiple). This question asks how common such changes are. Given Data / Assumptions: Initial models reflect business rules at a point in time. New products or policies may alter constraints and participation rules. Changes to cardinality can affect keys, constraints, and bridge tables. Concept / Approach: Cardinality changes are not everyday events, but they do arise. Adjustments may require adding junction tables, changing unique constraints, or modifying optionality. Because the blast radius can be large, these tasks are less frequent and are planned carefully with data migration and application changes. Step-by-Step Solution: 1) Confirm the new business rule with stakeholders.2) Adjust schema (for example, add a bridge table for many-to-many) and constraints.3) Migrate existing data to conform to the new model.4) Update application logic and reports accordingly. Verification / Alternative check: Use data profiling to locate rows that violate the new or old rules and to estimate migration complexity. Why Other Options Are Wrong: Common: overstates frequency; many systems keep stable cardinalities for long periods. Never or impossible: incorrect; schema evolution is feasible with planning. Common Pitfalls: Failing to add correct constraints after the change, leaving the data model ambiguous; not remodeling related processes and reports. Final Answer: a rare database design task, but does occur.

Question 15

Which statement is NOT true about a correlated subquery in SQL (that is, a subquery that references columns from its outer query)?

Accepted Answer

Introduction / Context: A correlated subquery is evaluated once per candidate row of the outer query because it references outer columns inside the subquery. This behavior differs significantly from a non-correlated subquery, which the optimizer can often evaluate once and reuse. The question asks you to identify the statement that is not true of correlated subqueries. Given Data / Assumptions: A correlated subquery contains predicates like t1.col > (SELECT AVG(x) FROM t2 WHERE t2.key = t1.key). EXISTS and NOT EXISTS commonly use correlated forms. We compare their behavior to regular (non-correlated) subqueries. Concept / Approach: Correlated subqueries run per outer row, creating a nested processing pattern. They are therefore not “very similar” to regular subqueries in evaluation strategy and performance characteristics. They can also be applied creatively to verify dependencies or constraints by checking that no outer row violates a rule when paired with related rows in the subquery. Step-by-Step Solution: 1) Recognize EXISTS/NOT EXISTS forms usually reference outer columns → correlated.2) Understand evaluation: subquery depends on each outer row → nested execution.3) Compare to regular subqueries: non-correlated ones can be computed once → materialized or inlined.4) Conclude that “very similar to a regular subquery” is not true. Verification / Alternative check: Inspect execution plans: correlated subqueries often appear as nested loops with a parameterized inner probe, unlike a single, reusable subplan of non-correlated subqueries. Why Other Options Are Wrong: EXISTS/NOT EXISTS: frequently correlated, true. Nested processing: true by definition. Verifying functional dependencies: feasible by checking rule violations per row, true in principle. Common Pitfalls: Ignoring performance implications of per-row subquery evaluation; forgetting that many correlated subqueries can be rewritten as joins or window functions for better performance. Final Answer: They are very similar to a regular subquery.

Question 16

What is the term for the process of reading an existing database schema and producing a clear, high-level data model from it for documentation or redesign?

Accepted Answer

Introduction / Context: When teams inherit a database without good documentation, they often need to derive a conceptual or logical model from the implemented schema. This helps stakeholders understand entities, relationships, and constraints before making improvements. The industry term for this is reverse engineering. Given Data / Assumptions: The physical schema already exists with tables, keys, and constraints. Documentation is incomplete or outdated. Goal is to produce an understandable model for analysis and redesign. Concept / Approach: Reverse engineering extracts metadata (tables, columns, PKs, FKs), analyzes naming patterns and dependencies, and maps them into a logical model (entities, attributes, relationships). This model describes what the system represents rather than how it is physically implemented. Step-by-Step Solution: 1) Harvest metadata from the catalog or information schema.2) Identify primary and foreign keys to reconstruct relationships.3) Group related tables into subject areas and produce diagrams.4) Validate with domain experts and iterate. Verification / Alternative check: Cross-check the inferred model by sampling data and confirming cardinalities and optionalities match business rules. Why Other Options Are Wrong: Data modeling and database design are forward-looking activities; here we start from an existing schema and derive the model, which is reverse engineering. Common Pitfalls: Mistaking naming conventions for real business rules; failing to validate inferred relationships with actual users and data. Final Answer: reverse engineering.

Question 17

Because correctness matters, how do many experienced professionals feel about relying on fully automated tools for database redesign without careful oversight?

Accepted Answer

Introduction / Context: Schema changes can break applications, violate data quality, and degrade performance. While automation accelerates repetitive tasks, it cannot replace human judgment about business rules, edge cases, and migration nuances. This question asks about the prevailing professional attitude toward fully automated redesign. Given Data / Assumptions: Automated tools can generate DDL and migration scripts. Real databases have legacy quirks and undocumented constraints. Data migration requires validation and reconciliation steps. Concept / Approach: Many practitioners adopt a cautious stance. They use automation for scaffolding and repeatable operations but verify, test, and often customize the output. Hence, “skeptical” best describes their view of depending solely on automation without human oversight and rigorous testing. Step-by-Step Solution: 1) Use tools to propose changes and generate scripts.2) Review scripts for correctness, safety, and performance.3) Test in non-production with production-like data and edge cases.4) Deploy with monitoring and rollback plans. Verification / Alternative check: Post-deployment audits and benchmarks often reveal issues that automation missed (for example, missing indexes for new access paths or unintended constraint interactions). Why Other Options Are Wrong: Optimistic/Ambivalent: do not reflect the prudent caution necessary for high-impact schema changes. None of the above: incorrect because “skeptical” is appropriate. Common Pitfalls: Trusting default tool assumptions, skipping data validation after automated migrations, and failing to capture domain-specific rules that tools cannot infer. Final Answer: skeptical

Question 18

Considering type conversions during schema evolution, which of the following column modifications may fail because existing data cannot always be safely converted?

Accepted Answer

Introduction / Context: Altering a column's data type requires converting all existing values to the new type. Some conversions are safe and lossless; others can fail or corrupt data if values do not conform. The question asks which change is risky and may not succeed. Given Data / Assumptions: Legacy tables may store dates as free-form text (char) with inconsistent formats. Numeric values can usually be represented as strings. The DBMS enforces data type rules during ALTER TABLE operations. Concept / Approach: Converting from char to date requires every existing value to match a valid date format. Any malformed or ambiguous text (for example, '13/32/2022' or mixed formats) can cause the conversion or ALTER to fail. By contrast, converting numeric to char is generally safe because any numeric can be serialized to text without loss of meaning. Step-by-Step Solution: 1) Audit the column for invalid or ambiguous date strings.2) Clean and standardize formats using staging columns or UPDATE with safe parsing.3) After data quality is ensured, perform the ALTER to a native date type. Verification / Alternative check: Attempt CAST/CONVERT in a SELECT to find rows that fail; only proceed when all rows convert correctly. This reduces risk during the actual ALTER. Why Other Options Are Wrong: Numeric to char: string representation is straightforward and rarely fails. Both succeed or neither succeed: overgeneralizations; success depends on data and conversion direction. Common Pitfalls: Ignoring time zone and locale differences in date strings; assuming the engine will auto-infer formats uniformly; failing to capture invalid rows before the ALTER. Final Answer: Changing a column data type from char to date

Question 19

Which SQL statement correctly alters table STUDENT to add a named CHECK constraint GradeCheck requiring that Grade values be greater than 0 (strictly positive)?

Accepted Answer

Introduction / Context: CHECK constraints enforce row-level data rules directly in the database. Adding a named constraint improves maintainability and allows targeted enable/disable and error messages. The task is to identify the correct SQL syntax for adding such a constraint. Given Data / Assumptions: Table name: STUDENT. Constraint name: GradeCheck. Rule: Grade must be greater than 0. Concept / Approach: Most SQL dialects support the pattern: ALTER TABLE CHECK (). The predicate uses normal boolean logic; here it is Grade > 0. The verb “ALTER CONSTRAINT” is typically used to modify properties of an existing constraint, not to define the check expression from scratch. Step-by-Step Solution: 1) Choose ALTER TABLE to modify structure and constraints of an existing table.2) Use ADD CONSTRAINT to create a new, named constraint.3) Specify CHECK (Grade > 0) as the rule. Verification / Alternative check: Run a small test: insert a row with Grade = 0 or Grade = -1 and confirm the database rejects it with a constraint violation; insert Grade = 1 and confirm success. Why Other Options Are Wrong: Option A: ALTER CONSTRAINT requires an existing constraint; syntax omits CHECK keyword. Option B: Missing the CHECK keyword; incomplete definition. Option D: Incorrect because Option C is valid. Common Pitfalls: Forgetting to name the constraint, which complicates later maintenance; writing Grade >= 0 by mistake if the business rule requires strictly positive values. Final Answer: ALTER TABLE STUDENT ADD CONSTRAINT GradeCheck CHECK (Grade > 0); ADD CONSTRAINT

Question 20

Under which condition is database redesign typically “not terribly difficult,” considering migration complexity and risk?

Accepted Answer

Introduction / Context: What makes a redesign difficult is rarely the DDL itself; it is moving and validating existing data while keeping applications working. Therefore the presence and volume of data drive risk, downtime, and effort. This question asks when redesign is comparatively easy. Given Data / Assumptions: We consider schema changes that would normally require data migration. Production systems usually have significant data to move and verify. No data means no migration risk or downtime for data copy. Concept / Approach: If a database contains no data, redesign tasks become more straightforward: update the schema, adjust constraints, and deploy. There is no need to transform existing rows, reconcile anomalies, or maintain dual-write strategies during cutover. Testing is still essential, but the hardest part—safe data migration—is absent. Step-by-Step Solution: 1) Confirm there is no data to migrate or preserve.2) Apply the new schema directly and run application tests.3) Start collecting data under the improved design. Verification / Alternative check: Contrast timelines: with data, you need extraction, transformation, validation, and rollback plans; without data, you focus on DDL correctness and app compatibility. Why Other Options Are Wrong: Structured, well-designed, or small databases can still be hard to redesign if data migration is non-trivial; even small datasets may involve complex dependencies. Common Pitfalls: Assuming “no data” also means no external dependencies; applications and integrations still need testing against the new schema. Final Answer: database has no data.

Database Redesign Questions