Navigation
Examines the testing phase of the SDLC specifically for database performance and accuracy.
What if a single missing comma in a database update could cause a global airline to ground all flights? In the world of high-stakes data, 'it works on my machine' isn't enough—we need rigorous testing to prevent digital catastrophe.
In the SDLC, database testing ensures that data remains accurate and accessible. Unit Testing focuses on the smallest parts of the database, such as individual Stored Procedures, Triggers, or Constraints. For example, a unit test might verify that a 'Price' column cannot accept negative values. Integration Testing, however, checks the communication between the database and the application. It ensures that when a user clicks 'Buy', the application correctly updates the inventory table and the transaction log simultaneously. We often use Mock Data during these phases to simulate real-world scenarios without risking actual user information.
1. Define a constraint: `ALTER TABLE Users ADD CONSTRAINT chk_Age CHECK (Age >= 18);` 2. Write a test case to attempt inserting a record with `Age = 17`. 3. The test passes if the database returns an error and rejects the record. 4. Write a second test case with `Age = 25`. The test passes if the record is successfully saved.
Quick Check
What is the primary difference between unit testing and integration testing in a database?
Answer
Unit testing checks individual components like constraints or triggers, while integration testing checks the flow of data between the database and the application.
Software code is versioned using tools like Git, but databases are stateful, making them harder to manage. Database Migration is the process of managing incremental, reversible changes to the database schema. Instead of manual changes, developers use Migration Scripts (often called 'changesets'). These scripts are stored in Version Control, allowing teams to track who changed what and when. If a new update breaks the system, developers can perform a Rollback to return the database to its previous stable state. This ensures that every developer on a team is working with the exact same database structure.
1. Create a migration file named `v2_add_email_to_users.sql`. 2. Add the 'Up' logic: `ALTER TABLE Users ADD Email VARCHAR(255);`. 3. Add the 'Down' logic (for rollbacks): `ALTER TABLE Users DROP COLUMN Email;`. 4. Apply the migration to the development environment and verify the schema version table updates to .
Quick Check
Why is 'Down' logic included in a migration script?
Answer
To allow for a 'rollback,' which reverts the database to its previous state if the update causes issues.
A database might work perfectly with 100 rows but crawl to a halt with 100 million. Performance Tuning is the process of optimizing the database to reduce Latency (). The most common tool is Indexing. An index is like a book's index; it allows the database to find data without scanning every row. However, indexes come with a cost: they speed up `SELECT` queries but slow down `INSERT` and `UPDATE` operations because the index itself must be updated. We measure efficiency using the Query Execution Plan, which shows the 'cost' of a query in terms of CPU and I/O usage.
Imagine a table with records. 1. Without an index, a search for a specific ID requires a Full Table Scan, which is complexity. In the worst case, it checks rows. 2. With a B-Tree index, the search complexity drops to . 3. Calculation: . 4. The database now only needs to perform approximately 20 'checks' instead of 1,000,000, drastically reducing the query time.
Which testing phase is most likely to catch an error where a Java application sends the wrong data type to a SQL column?
Adding an index to every single column in a table is a best practice for performance tuning.
If a search without an index takes time, and a search with an index takes time, what happens as (the number of rows) increases?
Review Tomorrow
In 24 hours, try to explain the trade-off between query speed and data insertion speed when using indexes.
Practice Activity
Research the 'EXPLAIN' command in SQL and try to run it on a simple query to see how the database plans to find your data.