Database Development, SQL and Back-end Testing

Heading:

Almost all corporations use database to store and manipulate their information. database development desires a significant attention to keep the data atomicity, consistency, isolability, durability, integrity, etc; conjointly desires additional work to secure data, stop data loss, improve performance, etc. therefore testing databases is crucial for several corporations

What I am going to learn throughout this course?

• • Concept of Data, Database and Information

  • Database development life cycle by defining Entities, Attributes, Constraints, ER diagram, Normalization and final Schema.

  • You will develop an E-commerce database from beginning to end.

  • Database testing through database development life cycle.

  • Different SQL to define database, manipulate data in database and control database.

  • To test SQL correctness, complexity and performance.

  • Testing with Oracle and MySQL.

  • How to do backend testing using different testing tools.

For more information please check the curriculum...

Audience and Requirements:

• • QA, BA, Developer, Project Lead, Manager

  • Anyone who is interested to know about database, database development, SQL, and database testing in detail.

  • No prior knowledge needed but strong desire to learn.

Training Highlights:

• • Mentorship will be available through training and after training

  • You will practice on interview questions starting from day one

  • You will be working on three big projects and experience yourself

Curriculum

• 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 ...

• • 1. 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 re-initialization

         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 front-end to back-end database integration test