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.

Outlines

00:00

📘 Introduction to Schema Testing on Database Tables

The speaker begins by introducing the concept of schema testing on database tables, emphasizing the importance of understanding database requirements such as table names, column details, data types, and sizes. A reference to a database design document is made, which is essential for conducting tests on database objects. The 'classic models' database is mentioned as the subject of testing, with a schema document outlining tables, their purposes, relationships, and column specifications being shared. The document serves as a guide for creating test cases to verify table presence, naming conventions, column count, names, data types, sizes, and constraints.

05:05

📋 Test Case Preparation for Database Tables

This paragraph delves into the specifics of preparing test cases for database tables. It discusses the need to verify table presence in the database schema, adherence to naming conventions, the correct number of columns, column names, data types, column sizes, nullability, and keys or constraints applied to columns. An example of an Excel sheet containing sample test cases is shared, with test case IDs and descriptions for checking various aspects of the database tables. The paragraph also explains how to execute SQL queries to check for table existence and naming conventions using the 'SHOW TABLES' command in MySQL.

10:09

🔍 Executing SQL Queries for Schema Validation

The speaker provides a detailed walkthrough of executing SQL queries to validate the schema of a database. This includes checking the number of columns in a table using the 'COUNT' function on the 'information_schema.columns' table, verifying column names and data types, and ensuring column sizes match the design document. The paragraph explains how to use the 'DESCRIBE' command to get metadata about a table and how to use the 'information_schema.columns' table to extract detailed information about columns, including whether nulls are allowed and the type of keys associated with each column.

15:12

🗂️ Describing the 'information_schema.columns' Table

This section describes the 'information_schema.columns' table, which contains metadata about all the tables in a MySQL database. The 'DESCRIBE' command is used to display all the fields and their information, such as data types, nullability, keys, and other details. The paragraph explains how this administrator table can be used to extract information from any table in the database, which is crucial for schema validation and testing.

20:12

🖋️ Validating Column Names and Data Types

The speaker demonstrates how to validate column names and data types in a table using SQL queries. By selecting specific columns from the 'information_schema.columns' table, the speaker shows how to retrieve and compare column names and data types against the expected values from the database design document. The importance of matching these elements to ensure the database schema is correctly implemented is highlighted.

25:19

🔑 Checking Column Sizes and Nullability

This paragraph focuses on verifying the size of columns and their nullability as per the database design document. The speaker explains how to execute queries to retrieve column names along with their data types and sizes, and how to compare these with the expected values. The concept of null fields as unknown values is introduced, and the speaker shows how to check which columns allow nulls using the 'information_schema.columns' table.

🗝️ Verifying Column Keys and Constraints

The final paragraph discusses the verification of column keys and constraints in a table. The speaker explains how to use the 'information_schema.columns' table to extract column names along with their associated keys, such as primary keys or foreign keys. The importance of understanding different types of integrity constraints in databases is mentioned, and the speaker demonstrates how to execute queries to ensure that the correct keys are associated with the columns as per the design document.

Keywords

💡Schema Testing

Schema testing is a type of database testing that focuses on verifying the structure and integrity of the database schema. It ensures that the database design adheres to the specified requirements. In the video, schema testing is the main theme, where the presenter discusses how to perform testing on the database schema, specifically on tables, to confirm that they meet the design document's specifications.

💡Database Element

A database element refers to a fundamental component within a database system, such as tables, columns, or keys. The script emphasizes the importance of understanding database elements like tables, which are where actual data is stored in rows and columns. The video's focus on table schema testing highlights the significance of these elements in maintaining database integrity.

💡Table

A table in a database is a structured collection of data held in rows and columns. It is a basic unit for organizing data. The script describes how to test various aspects of a table, including its existence, naming conventions, column count, and data types, which are all crucial for ensuring the table's compliance with the database schema requirements.

💡Column

A column in a database table represents a vertical entity that holds data of a specific data type. The script mentions the need to verify the number of columns, their names, data types, and sizes, which are essential for validating the table's schema. The column's attributes are tested to ensure they match the database design document.

💡Data Type

Data type refers to a classification that tells what kind of data can be stored in a column. The script discusses checking the data type of each column to ensure it aligns with the database schema. For instance, a column may be of type 'int' for integers or 'varchar' for variable-length character strings.

💡Size

In the context of a database column, size refers to the maximum amount of data that the column can hold. The script mentions verifying the size of columns to ensure they can accommodate the expected volume of data. For example, a 'varchar(50)' column can store strings up to 50 characters long.

💡Nulls

Nulls represent the absence of a value in a database column. The script explains the importance of checking whether columns accept null values or not, as this affects the data integrity and completeness. It is part of the schema testing process to confirm that nullability constraints are correctly implemented.

💡Keys

Keys in a database are used to enforce constraints on the data, such as uniqueness and referential integrity. The script discusses checking the types of keys applied to columns, such as primary keys, foreign keys, and check constraints, to ensure they are correctly associated with the columns as per the schema design.

💡Test Cases

Test cases are defined procedures or scenarios used to determine whether a particular software feature performs as intended. The script outlines creating test cases for database tables to verify their presence, naming conventions, column details, and constraints. These test cases are essential for systematic schema validation.

💡Information Schema

The information schema is a meta-database in MySQL that contains tables describing the database's structure. The script refers to the 'information schema.columns' table, which stores metadata about all the columns in all the databases. It is used to extract information necessary for schema testing, such as column names, data types, and keys.

💡Administrator Tables

Administrator tables, also known as system tables, store metadata about the database itself. The script mentions using administrator tables like 'information schema.columns' to retrieve metadata about other tables in the database, which is crucial for schema testing and ensuring the database's structural integrity.

💡MySQL

MySQL is an open-source relational database management system. The script uses MySQL as an example to demonstrate how to perform schema testing. Specific MySQL commands like 'SHOW TABLES' and 'DESCRIBE' are used to retrieve information about the database schema for testing purposes.

Highlights

Introduction to schema testing on tables, emphasizing the importance of understanding database requirements such as table names, columns, data types, and sizes.

The necessity of referencing a database design document before conducting schema testing.

Demonstration of a database schema document for 'classic models' database, detailing tables, their purposes, and relationships.

Explanation of metadata in the 'information schema.columns' table in MySQL, which stores details about database columns.

Description of test cases for verifying table presence, naming conventions, column count, and names against the schema document.

Use of SQL queries to check the number of columns in a table and compare it with the schema document.

Verification process for column names and data types using the 'information schema.columns' table in MySQL.

Importance of checking column sizes to ensure they match the schema requirements.

Testing for null fields in tables to verify if they allow null values as per the schema design.

Verification of keys and constraints associated with table columns, such as primary keys and foreign keys.

Execution of test cases through direct database connection and running SQL queries to validate table structures.

Use of an Excel sheet to organize and document test cases, including ID, description, expected results, and actual results.

Emphasis on repeating test cases for all tables in a database to ensure comprehensive schema validation.

Explanation of 'describe' command in MySQL to display metadata of a table, including fields, types, nulls, and keys.

Guidance on updating test case status to 'passed' or 'failed' based on the execution results.

Highlighting the importance of structural testing, also known as white box testing, for direct inspection of database components.

Outlook on future videos covering testing of other database objects like stored procedures and functions.

Transcripts

hi everyone welcome you all in the today's video  we are going to see how we can perform schema  

testing on table so table is a basic database  element in which the actual data will be stored  

in multiple columns in the rows format so before  going to test the table of the schema of the table  

so first we need to know the requirement like  what is the table name and what are the different  

columns are available and what is the data type  of the columns and the size of the column so  

these details are most of the times available in  database design document so that is a reference  

document we need to have before conducting testing  on the database objects now let me share a small  

document here and which is basically describing  the database schema of classic models database  

so in the previous video i have shown you how to  set up classic models database in mysql server  

mysql so on the same database we are going to  continue the testing so this is a database schema  

document and basically which contains uh what  are the tables are available in the database  

and what's the main purpose of the table uh  including the description which is clearly given  

and this document is basically data model and  which is described in detail like what are the  

different tables are available and what are  the relationship between the tables and what  

are the different columns are available so all  this information we can get it from the small  

documentation this is basically data database  schema and this is basically database model  

and if i just come down here so this is the actual  requirement which we need uh to test the tables so  

here in this database basically we have different  tables like customers products product lines and  

orders and order details payments and employees  offices and so on so these are the different  

tables which are available in the database now  in this table again they have given some list of  

columns and each column is having some data type  along with the size like how much data this k this  

column can hold so that is basically representing  by the size and nulls are accepting or not so  

which is giving another column and the last column  is representing the keys like on which column  

what type of key is applied like primary  key or foreign key or check constraint or  

multi key and so on so these are the different  keys which will be there on this particular  

column so this is basically a reference document  and before conducting testing on the database and  

similarly if i just look at each and every table  is having different type of columns and data type  

along with the size and so on okay this is a small  reference document and which contains all the  

table information in detail so including  columns data types and everything so based  

on this document we need to prepare our test cases  okay so based on this document we need to prepare  

our test cases and currently this document is  having only the table information but which  

also includes a lot of other information related  to other objects so currently this document is  

having only the table information what are the  tables are available and what is their requirement  

so by referring this document uh we have to  create a test cases to test the tables okay  

so what are the exact scenarios we can test on the  table so let me share a small list of test cases  

and what are the things we need to  test on the table database table  

so first of all we need to check the table  presents on the database schema so whichever  

database we are referring like classic models  in which the database is present or not that  

we need to check first and then we need to check  the table name conventions so whenever you create  

some table name so we need to follow certain  naming conventions and whether it is following  

proper naming convention or not according to  the requirement document and then you need to  

check the number of columns in a table like how  many number of columns are there in the table  

as per the document we need to verify and check  number of uh check column names in a table so what  

are all columns are available so we need to verify  each and every column name which is properly given  

or not which is meaningful or not we need to  check and then we need to check the data type  

of columns in each table so in table let us say we  have a number of columns and each column is having  

some data type so what kind of data type they have  given to that particular column we have to check  

as per the design document and not only the  data type we need to also check the size  

okay so that size is also most important  so check the size of the columns in a table  

and we need to also verify check nulls fields in  the table so suppose there are certain columns  

which will allow the null value sometimes  such which will not allow the null value  

so we need to also check those columns whatever  available and finally we need to also check the  

keys that means what are the different constraints  are applied on those particular columns  

so check we need to check the column keys in a  particular table so these are the different uh  

tests which we need to do on the table level so  we need to prepare a proper format of document uh  

the particular test case document like  which is having all the different columns  

like test case id description expected actual test  data and so on so based on this information we  

need to first create a test cases so let me just  share a small document here so now if i just look  

at this this is a small document which is created  this excel sheet basically which contains the  

all the test cases sample test cases  now here i have just covered only table  

and columns related test cases so what we need to  verify the table and columns specific to tables  

and columns and a different type of object we  will see in the upcoming series so in this video  

especially let us mainly focus on the table and  fields fields are nothing but a columns now here  

you can see a test case ids and uh here you can  see check table presence in a database schema  

so here how we can check how we can how we will  know the database uh particular table is available  

in the database scheme or not so first we need to  connect to the database and then we can execute a  

query called show tables okay so in every database  these commands will be different slightly differ  

so we need to fetch those commands we need to  search for those commands particular databases and  

here i'm following the mysql database especially  okay so shows tables so this particular command  

will show you all the list of tables which are  available in the particular database schema  

and then what we need to verify that we need to  check the table name is properly given or not so  

table name should be displayed in the list that  means what the table is existed on the database  

and the same thing we are expecting here and  once you it is passed then we can update the  

status here and test data is not required i  just given some priority to the test cases  

and we can update the status passed  or failed at the time of execution  

now check the table name convention so in the  first test we are just verifying the table  

existence or table is available or not in the  database schema and the second test we are just  

checking the table name convention so whenever  you give some name to the table it should be more  

meaningful according to the design document we  need to check the name is given properly or not  

it should not be have only one single character  or single letter double letters and like we should  

not have any space special characters so these  things we need to verify when you're checking the  

table name convention so for that also we can  execute this command show tables as a query  

and this will give you all the tables list of  tables and we can check each and every table  

name is properly given or not as per the decent  argument the table name should be a single word  

and table name should not contains any spaces so  based on the requirement we can add a more number  

of expectations here and this is one test so table  existence we have verified and then table name  

name naming convention and the next one is  check number of columns in a table so as soon as  

the table is available we need to check how many  number of columns are there in the table as per  

the document so for that we need to execute one  query from my sql so in the my sequel there is a  

table admin table which is available that  is information underscore schema.column so  

columns is actual table name and which is  available in information underscore schema  

a database so this is having the metadata so what  is that metadata means the metadata is nothing  

but a data about the data that means suppose  i have a table in the table i have a column  

but in that that column information i want to know  like what is that name of the column what is the  

size of the column what is the data type of the  column so that information will be maintaining  

some other tables they are basically comes under  administrator tables so information schema dot  

columns is administrator table in mysql database  and similarly every database different databases  

having different type of administrator tables  and you can find all the metadata in those tables  

so if i execute this particular query select count  star as a number of columns this alias base scale  

i'm giving from information underscore schema  dot columns where table name equal to customer so  

whichever table you want to find or on whichever  table you want to find the number of columns you  

can specify the table name here and that will  give you number of columns which are present in  

this particular table so this is a query which  you have to execute on mysql and then this will  

return the value how many total number of columns  are available in the database so this is one test  

and now the next one is check column names  in the table so here what are the columns  

are available and what are the names are given  properly or not as per the documentation we need  

to go through so here in the documentation they  clearly provided what are the column names and  

what are the data type and size so everything is  clearly mentioned so accordingly we need to check  

so check column names in the table so for that  what we can do is we can also execute this command  

or sql query select the column name so as i  said information underscore schema.columns  

is administrator table so which contains all  the metadata information so from that we can  

extract this column data so column underscore  name from information underscore schema.columns  

where we need to specify the table name so this  particular query will return the number of columns  

all the columns names from the table what are all  columns are available from the table it will show  

you just the column names from the table and  then we need to check those column names are  

available as mentioned in the database document  again so here whatever column names they have  

exactly mentioned the names are exactly same or  not we need to verify and the next test case is  

check the data type of column so in the previous  test case we just verify the names of the columns  

now in this we need to verify the data type of  the columns and for that we need to access another  

column from the same table like information  schema dot columns is having one more column  

called column underscore name data underscore type  so column name along with the data type i am able  

to access so these two things i am just trying to  retrieve from the information schema dot columns  

table where table name equal to customers so this  query will give you exactly what are the columns  

are available in the table and what are the data  types of those particular columns so once you get  

the data from this query so then we need to check  that data along according to our documentation  

and that is one test and the next test case  check the size of the columns so there are three  

different cases the first case we just verify  the names of the columns in the fifth test case  

we just verify the data type of the column now  here we are checking the size of the column okay  

suppose when i say var card and which having some  size like 10 15 or 20 something so we need to also  

verify the size of each and every column in the  table or each and every field in a table so again  

we use the same admin table information underscore  schema dot columns where table name equal to  

customer and this will give you column name i'm  printing column name along with the column type  

okay so you need to remember few number of columns  here we are using a column name okay so this is a  

column name and here we are using column name  along with the data type both we are testing  

and here we are checking column name and column  type also column type in the sense what again  

what type of column it is again data type column  name and data type will be displayed that means  

the size of the column will be displayed and here  null's fields in the table so there is another  

requirement here so if i just look at this column  so nulls are allowed or not no means not allowed  

yes means allowed so we need to check this  information also again for that also we have a  

another column called e is nullable and this  column is available in information schema.column  

so we need to capture these two column name along  with the is null so this will say no or yes and  

once you return the data no aureus then we need to  compare with our expected data and then we need to  

make or pass or fail from the same  table we need to access all the data  

and the next one check column keys in a  table so each and every column is sometimes  

having some primary equal key or foreign key or  check constraint or whatever it is so we need  

to verify those keys are associated with those  columns or not and that keys information also  

we can extract from the same table information  underscore schema.columns so this is a table name  

and and we need to extract uh two columns  again column name and column key these are  

the two things we are going to extract from the  same table and whatever the data we are getting  

from this query we we are again comparing with  our expected data as per the design so these are  

the few test cases which we can write on the table  and fields fields or mathematic columns in a table  

and this is basically schema validation structural  testing internally we are testing the table level  

columns and fields and everything so that is  a basically uh schema testing we are basically  

focusing mainly on the table schema testing so how  we can execute these queries so now let us see the  

execution part so this is the design part once you  understand this document and we need to understand  

all the tables what are all tables are available  and what are the relationships and uh then we need  

to analyze this data model what are the columns  data type size and available so finally we need to  

based on this document we need to create a small  excel sheet like this and test cases document and  

now we need to execute these test cases directly  by connecting the database so now let me connect  

to the database so here i already connected to my  mysql database and i already have a classic models  

database which is already set up in previous video  now if you expand this there are multiple tables  

are there so here whatever test cases we have  written here these test cases we need to repeat  

for all the tables and currently here i am taking  only one table that is a customers table and you  

need to repeat the same test cases and you need  to repeat the same test cases for multiple tables  

whatever tables are available in your database  for all the tables you need to perform the testing  

right but here you need to just change the table  name here i have used customers table for every  

test so instead of customers we need to specify  the other tables of the other names of the tables  

now let us see the execution part so let us see  each and every query how it is going to execute  

now i told you one important uh table name right  especially in my sequel that is information  

underscore schema.columns and if i just look at  this table and what are the content is available  

in the table go to my sql and go to the sql  query open this equal query and here just type  

describe and information schema.columns now let us  execute this query and see what it is uh giving so  

now we can just see this is the administrator  table and which contains all metadata for  

regarding all the tables you can just look at this  this is also a table which is available in the  

information schema this is basically administrator  database inside this uh when you execute this when  

i describe this particular table and what is the  describe command will do the describe command  

will display all the information metadata of  the table like all the fields information what  

are the type and what is the null keys and other  information will be displayed in this so now by  

using this table by taking help of this table we  will extract the information from any table it can  

be any table we can extract the information like  table catalog table schema table name column name  

ordinal position like keys nullable data type so  there are so many like say column key column types  

collation name so these are different information  will be stored in this administrator table  

so by taking help of this table we can get  any data or any information of the table  

which is available in our database now by taking  help of this particular table let's run the  

particular code so first of all we need to connect  to the classic borders classic models database so  

for that i'm using use command use classic models  so in my sequel what this command will do this  

command basically focus only on the myc classic  model because there are other databases also there  

so this command will say like on which database  we are going to execute your query so now it is  

connected to the classic models database now it is  using classic model database and once you connect  

it then we need to execute this query so first  of all let's see our table is there or not show  

tables is my command let me just execute show  tables [Music] so this particular command will  

give you what are all tables are available in the  classic models database schema now let me execute  

now you can see the customer is available as a  first table and totally a tables are there so  

customer employees offices order details orders  payments products product lines and products so  

these are the tables which are available in the  database so customers table is already available  

by database so with this uh this particular test  is got passed you can see okay as per expectation

so actual result is what so table is  present we can say as per uh expectation

okay so now this is got passed let me update as a  past now the next one check table name convention  

so what we are expecting here table name should  be a single word table so table name should not  

contains any spaces and uh this is applicable  for all the tables if i just look at all the  

table names there are single word tables  are there there is no spaces or there is  

no special characters nothing so everything  is fine so i can also make this test pass

right so now the next one check the number  of columns in a table how we can verify  

the number of columns in a table so we can  just execute this command let me show you  

so what is this command will say select account  star as so this is basically alias because if even  

if you don't specify this the value will come here  but if you want to give some proper column name so  

i can just specify this is alias so count star as  a number of columns from and whatever the metadata  

we want to get from the for the particular table  we will able to access from information schema  

so select the count star as a number of columns  from information scheme underscore schema.columns  

and where we need to specify the table name  for which table you want to get information  

so we need to get the table name customers so  when you execute this particular executable of  

this particular statement this will give exactly  how many number of columns are available inside  

this particular table okay so select count star as  number of columns so this is number of columns he  

says just a name column alias we can say and even  if you're not specifying no problem it will just  

print the value like this let me execute  

so it will give you just like count star so just  if you want to give some proper alias you can say  

as number of columns and then you can execute  this query so once you executed this query so this  

will give you total number of columns which are  available in the table and then we need to compare  

with our expected so expected is also 13 actual is  also 13 so then i can make my test method passed  

this is one test and next one is check column  names in the table so each column name is  

properly given or not as per the design document  we need to check so for that we need to extract  

only column names so here instead of count star  we need to just specify the column next let's  

say select the column name this particular column  name is already available in this particular table  

information schema.columnstable this is again  admin statement administration table all metadata  

will be maintained in this so here we need to  specify the table name so select a column name  

and what this command will do this command  will extract column names only column names  

from this particular table now let's execute this  command and it will show you only column names  

from this table you can see customer number  customer name contact last name contact first  

name phone address line one address line twos  only columns you are verifying just the column  

names here we are not verifying size or date type  or nothing we are just verifying only the column  

names here so we can just look at here these are  the different columns it is displayed then we need  

to compare these column names manually with our  uh document so for the customer document what are  

the column names they have given so these are  exactly column names which are matching with  

the column names which are already  available in the table so that we need to  

compare and match and if everything is equal  then we can make this as a past so like this we  

can execute this query and we can get only column  name from the table and then if you want to test  

the same thing for another table just change  the table name here and that will give you the  

number of columns in another table okay so this  is a query which we need to execute to display  

only column names and then we can compare with  the as per the documentation and then we can make  

it is passed or failed so now the same columns  are available so i'm just making this as a past  

and now let's go to the next one check the data  type of the columns in a table so previous test we  

have just verified only column names are available  or not the names are correctly given or not but in  

this time we have to check the data type of the  column so what kind of data type they have given  

in each and every column so for that we need to  execute the same query but we need to extract  

other column let me show you this so select  column name so i'm extracting column name along  

with the data type so data type is also one of the  column which is available in this particular table  

so column name along with the data type  i want to get from this table where  

table name equal to customers so now when  i execute this query so this will give you  

the column names along with the data type  so previously we extracted only column names  

but this time we are extracting column names  along with the data type and again here we are not  

extracting the size we are just extracting only  the data so according to this we need to again  

check this data which is correct or not according  to our documentation so the same data type  

values are getting or not for a particular column  wise we need to verify so this particular column  

is having inter type so again we need to check  this column is having intertype then customer  

name contact last name first name these are all  wirecard types so these are all wirecard types  

right so like this we need to compare each and  every column along with the data type and once  

everything is fine so credit limit is a decimal so  here also credit decimal credit limit is a decimal  

so like this we need to compare the data type  of every column so if everything is matches then  

we can make this test method test case also  passed this is data type validation of the columns  

and next one check the size of the columns so  we verified the names of the columns verified  

the data type of the columns now we need to  verify the size of the column so each and every  

column is having a sum size let us say if you want  to store the name of a person i can give 15 or 20  

or 30 with worker type so that particular column  will allow you to store only the names which are  

uh the digits of the character so which within a  limit let's say when i say 15 i can store only 15  

characters maximum right the name should having  only 15 characters so that is decided by the  

size of the column so that also we need to  verify so we need to execute the same query again  

and here from the same administrator table this  time we have to extract the column type not the  

data type we have to extract the column type so  column name and column type so column type will  

give you the data type along with the size now  let's execute this command and you can see here  

so these are the different column names along  with the data type and the size both have given  

so when i say customer number int is a data type  11 is a size and customer name varchar is a data  

type 50 is a size so this data exactly should  match with our documentation so exactly match  

with our documentation okay and this thing  we need to compare so here we are basically  

comparing the we are checking the size of the  column okay these three test cases we are checking  

three different things in the first one we are  just checking the names of the column in this  

query we are checking the column data type and  in this we are verifying the size of the column  

as per the document is is correct or not so  the size of the column is perfectly fine almost  

everything is 50 and first one is 11 last one  is eleven ten comma two is a decimal and if i  

just look at the documentation also ten comma  two decimal int is eleven and rest of them all  

are fifty and postal code is a fifteen is  there so you can just look at here postal  

code is a fifteen perfectly fine so in that  case we can just make this test case passed

all right so now the column level validations are  done so there is there are two more validations we  

need to do on the table that is check null fields  in a table null fields how we can capture the null  

field so if i just look at the document again  so here there are number of columns are there  

so each column is say no no means no means  what that particular name will not allow you  

nulls yes means what it will allow you nulls  or repetition of values are not allowed  

so now you can see check null fields in a table  basically null is nothing but unknown value  

okay if you don't know the value you can just make  it as a null that is unknown value and later you  

can update the value okay so if you want to know  what are the columns or null allowed or not so we  

can execute this command and all these data we  can extract from the administrator table which  

is information schema dot columns so column  name along with the e is underscore nullable  

from information schema dot columns  where table name equal to customers  

so when you execute this now you can get  the same thing so this particular column  

no is nullable no customer name is nullable  no contact last name is nullable no  

okay so like this we can just verify uh these  nullable fields for every column as per the  

documentation like this and similarly finally we  need to verify the keys what are the different  

keys are associated with those columns and if  i just look at here for every table there is  

a primary key there is a multiple key here and  uh see here primary key and multiple keys are  

there and some tables are having two primary keys  some tables are having two multiple keys like this  

right so now we need to verify those  keys are properly associated with the  

columns or not so for that we have one more  command here column key we need to extract  

so execute this command so this one check the  null fields first right so let me execute this  

so this is the one we already executed this  so if i just execute this this will give you  

nullable columns so this is same  thing so i can just also make it past  

and then check the column keys in a table  so in the table we can also verify that  

each and every column is associated with  the constraint properly or not so for  

that we can also execute this command or this  particular query from information schema only so  

column name along with the column key so  what is the column key representing with this  

representing a particular key is allocated for  that particular column or not so understand this  

we need to know what are the different constraints  are available in the database all these things so  

integrity constraints concept you have to know  uh if you understand this one okay this is a  

dependent topic so column key from information  schema dot columns where table name equal to  

customers so when i execute this query so this  will give you exact what are the columns are  

available each column is associated with the what  is the column key here prime is a primary key  

means multiple key okay sometimes you may see  check constraints sometimes you see you need  

not null constraint like this so these are the  different keys you can see in this table so this  

is the query which we have to execute to know what  are all keys are available or respective columns  

okay so currently we have only in this table  i have only first one is a primary key custom  

customer number and sales repo sale  representative employee number is a multiple key  

okay multiple means it will allow the  multiple values and uh now this is a past  

so this is how we need to just verify the table  structure basically we are mainly focusing on the  

structure so which is becomes under structural  testing structural testing and which is again  

called as a white box testing we are checking the  directly table so table columns table column data  

type and table columns uh size of the columns the  data type of the columns and what are the keys are  

associated with those columns and whether these  keys are those those columns are allowing nulls or  

not so those things we are verifying basically on  the table uh table component okay so these are the  

few test cases which we can write and execute on  the table level so the same test we can continue  

with the rest of the tables which are available  in the database okay and uh in the next video  

i'll show you how we can test other type of  objects like stored processor functions and so on  

and uh that's it so that's all for this video guys  thanks for watching we'll see in the next video