Part3 : Database Testing | How To Test Schema of Database Table | Test Cases
Summary
TLDRThis 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
📘 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.
📋 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.
🔍 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.
🗂️ 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.
🖋️ 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.
🔑 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.
Mindmap
Keywords
💡Schema Testing
💡Database Element
💡Table
💡Column
💡Data Type
💡Size
💡Nulls
💡Keys
💡Test Cases
💡Information Schema
💡Administrator Tables
💡MySQL
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
تصفح المزيد من مقاطع الفيديو ذات الصلة
5.0 / 5 (0 votes)