Why database testing ensures data integrity in Testing Fundamentals - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

What is the main purpose of database testing in ensuring data integrity?

Choose the best explanation for why database testing is important for data integrity.

A It checks that data is stored correctly and remains accurate after operations.

B It ensures that the database server hardware is functioning properly.

C It verifies the user interface design of database management tools.

D It only tests the speed of database queries without checking data correctness.

💡 Hint

Think about what data integrity means in terms of data correctness and accuracy.

Database testing focuses on verifying that data is stored, retrieved, and updated correctly, which keeps data accurate and consistent.

🧠 Conceptual

intermediate

Which database testing activity helps prevent data corruption?

Select the database testing activity that directly helps maintain data integrity by preventing corruption.

A Checking the color scheme of the database admin interface.

B Measuring database query execution time.

C Testing data constraints like primary keys and foreign keys.

D Verifying network connectivity to the database server.

💡 Hint

Data constraints control how data relates and what values are allowed.

Testing constraints ensures that invalid or duplicate data cannot enter the database, preventing corruption.

❓ Predict Output

advanced

What is the output of this SQL query after inserting duplicate primary key?

Given a table with a primary key, what happens when you try to insert a duplicate key?

Testing Fundamentals

CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(50));
INSERT INTO users VALUES (1, 'Alice');
INSERT INTO users VALUES (1, 'Bob');

A Error: duplicate key value violates unique constraint

B Both rows inserted successfully

C Only the first row inserted, second ignored silently

D Table structure is dropped automatically

💡 Hint

Primary keys must be unique for each row.

Inserting a duplicate primary key violates the uniqueness rule, causing an error and preventing data corruption.

❓ assertion

advanced

Which assertion best verifies data integrity after a database update?

Choose the assertion that confirms data was updated correctly without corruption.

Testing Fundamentals

UPDATE accounts SET balance = balance - 100 WHERE account_id = 123;

A Assert that the balance column is of type VARCHAR

B Assert that the number of rows in accounts table is zero

C Assert that the database server is running

D Assert that SELECT balance FROM accounts WHERE account_id = 123 returns original balance minus 100

💡 Hint

Check if the data changed as expected after the update.

Verifying the updated balance matches the expected value confirms data integrity after the operation.

🔧 Debug

expert

Why does this test fail to ensure data integrity?

Identify the reason this database test does not properly verify data integrity.

Testing Fundamentals

def test_insert_user(db):
    db.execute("INSERT INTO users (id, name) VALUES (1, 'Alice')")
    result = db.query("SELECT * FROM users WHERE id = 1")
    assert result == [(1, 'Alice')]

A The INSERT statement syntax is incorrect.

B The assertion compares a tuple with a string, causing a mismatch.

C The SELECT query is missing a WHERE clause.

D The test does not connect to the database.

💡 Hint

Check the data types and structure returned by the query versus the assertion.

The query returns a tuple with id and name, but the assertion expects a list with only the name string, causing failure.

Practice

1. Why is database testing important for ensuring data integrity?

easy

A. It verifies that data is accurate and consistent in the database.

B. It improves the speed of the database server.

C. It changes the database schema automatically.

D. It deletes duplicate data without user approval.

Solution

1. Step 1: Understand the purpose of database testing

   Database testing checks if data stored is correct and consistent.

2. Step 2: Identify the role of data integrity

   Data integrity means data is accurate and reliable over its lifecycle.

3. Final Answer:

   It verifies that data is accurate and consistent in the database. -> Option A

4. Quick Check:

   Data integrity = Accurate and consistent data [OK]

Hint: Data integrity means data is correct and consistent [OK]

Common Mistakes:

• Confusing data integrity with performance
• Thinking testing changes data automatically
• Assuming testing deletes data

2. Which SQL query is best to check if a column has any NULL values to ensure data integrity?

easy

A. SELECT * FROM table WHERE column IS NULL;

B. SELECT * FROM table WHERE column = 0;

C. SELECT * FROM table WHERE column LIKE '%NULL%';

D. SELECT * FROM table WHERE column IS NOT NULL;

Solution

1. Step 1: Identify how to find NULL values in SQL

   SQL uses 'IS NULL' to check for NULL values, not '=' or LIKE.

2. Step 2: Match the correct query

   SELECT * FROM table WHERE column IS NULL; uses 'WHERE column IS NULL' which correctly finds NULLs.

3. Final Answer:

   SELECT * FROM table WHERE column IS NULL; -> Option A

4. Quick Check:

   Check NULL with IS NULL [OK]

Hint: Use IS NULL to find missing data in SQL [OK]

Common Mistakes:

• Using '=' to check NULL values
• Using LIKE to find NULLs
• Confusing IS NULL with IS NOT NULL

3. Consider this SQL query to check data integrity:

SELECT COUNT(*) FROM users WHERE email = '' OR email IS NULL;

What does this query return?

medium

A. The number of users with valid email addresses.

B. The number of users missing an email address.

C. The total number of users in the database.

D. The number of users with duplicate emails.

Solution

1. Step 1: Analyze the WHERE clause conditions

   The query counts rows where email is empty string or NULL, meaning missing emails.

2. Step 2: Understand the COUNT(*) function

   COUNT(*) returns the number of rows matching the condition.

3. Final Answer:

   The number of users missing an email address. -> Option B

4. Quick Check:

   Empty or NULL emails counted = missing emails [OK]

Hint: Empty or NULL means missing data [OK]

Common Mistakes:

• Thinking it counts valid emails
• Confusing COUNT(*) with COUNT(column)
• Assuming it finds duplicates

4. You wrote this SQL to check for duplicate user IDs:

SELECT user_id, COUNT(*) FROM users GROUP BY user_id;

Why might this query fail to find duplicates?

medium

A. COUNT(*) cannot be used with GROUP BY.

B. It uses GROUP BY incorrectly on user_id.

C. It should use WHERE instead of GROUP BY.

D. It does not filter groups having more than one record.

Solution

1. Step 1: Understand GROUP BY with COUNT(*)

   The query groups rows by user_id and counts each group size.

2. Step 2: Identify missing filter for duplicates

   Without filtering COUNT(*) > 1, it shows all groups, not just duplicates.

3. Final Answer:

   It does not filter groups having more than one record. -> Option D

4. Quick Check:

   Filter COUNT(*) > 1 to find duplicates [OK]

Hint: Add HAVING COUNT(*) > 1 to find duplicates [OK]

Common Mistakes:

• Assuming GROUP BY alone finds duplicates
• Thinking COUNT(*) is invalid with GROUP BY
• Using WHERE instead of HAVING for group filters

5. A database tester wants to ensure no invalid dates are stored in the orders table's order_date column. Which approach best ensures data integrity?

hard

A. Ignore date checks because the application handles it.

B. Delete all records with dates older than one year without checking.

C. Run a query to find dates outside the allowed range and report errors.

D. Update all dates to the current date automatically.

Solution

1. Step 1: Identify how to check invalid dates

   Querying for dates outside allowed range finds invalid data without changing it.

2. Step 2: Understand importance of reporting errors

   Reporting errors helps fix data issues without data loss or wrong updates.

3. Final Answer:

   Run a query to find dates outside the allowed range and report errors. -> Option C

4. Quick Check:

   Find and report invalid data to keep integrity [OK]

Hint: Find errors first, don't delete or change data blindly [OK]

Common Mistakes:

• Deleting data without validation
• Trusting application without testing
• Automatically changing data without review