Part3 : Database Testing | How To Test Schema of Database Table | Test Cases

Summary

This video tutorial guides viewers through schema testing of database tables, a fundamental aspect of ensuring data integrity. It emphasizes the importance of understanding database design documents to identify table names, columns, data types, and constraints. The presenter shares a document outlining the 'classic models' database schema and demonstrates how to create test cases for verifying table presence, naming conventions, column counts, names, data types, sizes, nullability, and keys. Using MySQL as an example, the video shows how to execute SQL queries to validate these aspects, highlighting the process of structural testing for database schema validation.

Takeaways

• 📚 The video discusses the process of schema testing on database tables, emphasizing the importance of understanding table structure, column details, and data types.
• 🔍 Before testing, one must refer to a database design document for table names, column names, data types, and sizes to ensure the requirements are well understood.
• 📈 The video uses a 'classic models' database as an example to demonstrate how to set up and test a MySQL database schema.
• 📝 A database schema document is essential as it provides detailed information about tables, their relationships, and columns, which is crucial for creating test cases.
• 🔑 The script mentions various database objects like tables, columns, keys, and constraints, and how to verify their presence and properties through testing.
• 🛠️ The video outlines specific test cases for checking table presence, naming conventions, number of columns, column names, data types, column sizes, nullability, and keys.
• 📋 An Excel sheet is used to organize test cases, including test case IDs, descriptions, expected results, and status updates after execution.
• 💻 The execution of test cases involves connecting to the database and running SQL queries to verify the schema against the expected requirements.
• 🔬 The 'information_schema.columns' table in MySQL is highlighted as a key resource for extracting metadata about table columns for testing purposes.
• 📉 The video provides step-by-step instructions on how to execute SQL queries to test for table existence, column names, data types, column sizes, nullability, and keys.
• 🔄 The process of schema testing is iterative, requiring the same tests to be performed on all tables within a database to ensure accuracy and consistency.

Q & A

What is the purpose of schema testing in database testing?

Schema testing is performed to verify the structure and integrity of the database. It ensures that the tables, columns, data types, sizes, and constraints are defined as per the database design document, which is crucial for maintaining data consistency and accuracy.

What are the prerequisites before starting schema testing on a database table?

Before starting schema testing, one must know the table name, the different columns available, the data type of the columns, and the size of the columns. These details are usually available in the database design document, which serves as a reference for testing.

Can you explain the role of a database design document in schema testing?

A database design document provides detailed information about the database schema, including the tables, their relationships, columns, data types, and sizes. It is essential for schema testing as it outlines the expected structure and constraints of the database, which testers use to create and execute test cases.

What does the term 'information schema' refer to in the context of databases?

The 'information schema' is a collection of read-only views in a database that contain metadata about all the database's tables, columns, data types, and keys. It is used to retrieve information about the database structure and is often queried during schema testing to verify the actual database structure against the expected one.

How can you check if a specific table exists in a MySQL database?

In MySQL, you can check if a specific table exists by using the 'SHOW TABLES' command followed by the table name. This command lists all the tables in the database, and you can verify the presence of the required table in the output.

What is the significance of checking table name conventions during schema testing?

Checking table name conventions ensures that the table names adhere to the naming standards specified in the design document. This is important for maintaining consistency, readability, and avoiding errors due to improper naming, such as using spaces or special characters.

How do you verify the number of columns in a table as per the database design document?

You can verify the number of columns in a table by querying the 'information schema.columns' view in MySQL. The query 'SELECT COUNT(*) FROM information_schema.columns WHERE table_name = 'your_table_name'' will return the total number of columns in the specified table, which can then be compared with the expected number from the design document.

What is the importance of testing column names, data types, and sizes in a table?

Testing column names, data types, and sizes is crucial for ensuring that the table structure matches the design document. It verifies that each column is correctly named, has the appropriate data type to store the intended data, and has the correct size to accommodate the data within the specified limits.

Can you explain the process of checking nullability and keys associated with table columns during schema testing?

During schema testing, you check nullability by verifying which columns allow NULL values, indicating that they can store unknown or missing data. You also check the keys associated with columns, such as primary keys, foreign keys, or unique keys, to ensure they enforce the necessary constraints and relationships as defined in the design document.

How do you structure a test case document for schema testing of database tables?

A test case document for schema testing should include columns like Test Case ID, Description, Expected Result, Actual Result, Test Data, and Status. It should cover various test scenarios, such as checking table presence, table name conventions, column names, data types, column sizes, nullability, and keys associated with columns.

What is the difference between structural testing and other types of database testing?

Structural testing, also known as schema testing, focuses on the physical structure of the database, such as tables, columns, and constraints. It is different from other types of testing, such as functional testing, which verifies the functionality of the database against business requirements, or performance testing, which evaluates the database's performance under load.