SQL/ Database Backend Testing

1. Introduction to database
   1. Data vs information
   2. Database
   3. DBMS and RDBMS
2. RDBMS basics
   1. Entity and Attribute
   2. Data types
   3. ER diagram
   4. Primary key and foreign key
   5. Table level and Column level Constraints
   6. Normalization
   7. Database Schema
   8. Tables vs views
   9. Indexing
   10. Metadata
3. SQL: Structural Query Language
   1. Data Definition Language
   2. Data Manipulation Language
   3. Data Control Language
4. Business interest on RDBMS
   1. Batch data Loading and storing
   2. Batch data Extraction
   3. Application OnLine Transaction Processing read and write from RDBS
   4. Test data generator feed to RDBMS
5. Need of RDBMS testing
   1. Data is a vital asset of anything
   2. Data integrity, confidentiality and privacy
   3. Performance of data process
   4. Scalability
6. What should we tend to test in RDBMS?
   1. Black box test on the on top of RDBMS business interests
      1. Object to Relation Mapping
         1. On metadata and structure
         2. Actual data
      2. Incoming data values
         1. From programs/scripts
         2. From objects
         3. From forms ...
      3. Outgoing data values
         1. From tables
         2. From queries
         3. From views...
   2. White box test on RDBMS
      1. Stored procedures/functions
      2. Triggers
      3. Views
      4. Constraints
      5. Existing data quality
      6. Referential integrity/ data consistency/data integrity
      7. Default values for columns
7. When should we tend to test in Agile environment?
   1. Test First Development
      1. Add a test
      2. Run the tests
         1. If pass back to  step (i)
         2. If fails build slightly change and back to step (ii)  
   2. Test Driven Development
      1. Is TFD + Refactoring
      2. More on test automate driven
      3. Work on refactoring before and new feature
8. How should we test?
   1. Database sandboxes
      1. Unit test on Development sandboxes
      2. Project level test on Project integration sandbox
      3. System/acceptance test on Demo/Pre-production sandbox
      4. Operations and support on Production
   2. Writing database tests
      1. Same to the other application, write test cases
      2. Have test data
         1. Form external source data: xml, Json, or tables
         2. Test data creation scripts
         3. Consist of Test Cases
      3. Setup test environment
      4. Run test
      5. Check results
   3. Setting up database tests
      1. Create schema and load initial test data
      2. Data reinitialization
         1. Erasing the data and re load the initial data
         2. Update the present data to urge the initial data
   4. Database testing tools
      1. Category of database testing tools
         1. Interface test tools
         2. Internal database test tools
      2. Data privacy tools
         1. IBM Optim Data privacy tool
      3. Load testing tools
         1. Loadrunner
         2. Jmeter
         3. Empirix
         4. Mercury interactive
         5. RadView
         6. Web performance
      4. Test data generator
         1. Data factory
         2. Datatect
         3. DMT data Generator
         4. Turbo data
      5. Test data management
         1. IBM Optim Test Data Management Tool
      6. Unit testing tools
         1. AnyDb Test
         2. DBFit
         3. DBUnit
         4. NDbUnit
         5. SQLUnit
         6. TSQL Unit
         7. Visual studio team edition for database professionals
         8. XTUnit
9. Who should test?
   1. Unit testing
      1. Both Database Developers
      2. Database Administrators tie along
   2. Integration and other testings
      1. Testers and others
   3. Data management cluster or IT management cluster
      1. Should promote importance of database testing
      2. Should give training and testing tools
10. Current database regression testing challenges
    1. Lacked testing skills
    2. Lacked unit testing for actual databases
    3. Lacked database testing tools
11. Data inspection testing
    1. When?
       1. During updating data
       2. During importing data
       3. During migrating data
    2. How: compare and contrast
       1. Count column
       2. Use aggregation function(min, max, avg, ..) for compare and distinction
       3. Use minus query
       4. Take sample record check
       5. Take schema comparison
    3. Tools
       1. QuerySurge
       2. DB inspect
12. Tip for Schema testing
    1. Use schema table of your RDBMS
    2. Use SQL + aggregate functions
    3. Check database name
    4. SQL to Count tables
    5. PL/SQL to Check table names
    6. SQL to Count columns of each table
    7. PL/SQL to check column names, data types and constraints(keys, null values, …)
    8. Check triggers, views
13. Tips for performance test
    1. Check indexes
    2. Check views
    3. Check Queries efficiency
    4. Check functions and triggers
    5. Check clusters and load balances
14. ACID property validation
    1. Atomicity
    2. Consistency
    3. Isolation
    4. Durability
15. Best practices
    1. Use an in-memory database for regression testing, like HSQLDB (hyperSQL database)
    2. Start fresh each major test plan : drop database, create new, and load test data
    3. Take TDD approach
    4. Get hint from experienced database testers

Assessment:

Scenario 1: Create a schema for  Ecommerce database. Follow Database development life cycle

Scenario 2: Write 4 scenarios that you could test database migration. Write queries to.

Scenario 3: Write 6 scenarios that you would test during database development phase

Scenario 4: Write a query that runs 1) nested query 2) uses view 3) apply left outer join

Scenario 5: Write 4 scenarios that you would test frontend to backend database integration test