Part2 : Database Testing | Environment Setup

Summary

This tutorial video script guides viewers through setting up an environment for database testing. It begins with installing MySQL Server and downloading the 'classic models' sample database, followed by importing the schema and data into the local server. The script also covers exploring the database schema, which includes tables like customers, orders, and products, and mentions future lessons on testing database objects like tables, views, and relationships.

Takeaways

• 📚 The video is a tutorial on setting up an environment for database testing, following an introduction to database testing in a previous video.
• 💾 The first step in setting up the environment is to install a database server; the video uses MySQL as an example, but other databases like Oracle or MSSQL Server are also options.
• 🔗 The video provides a link for downloading the MySQL server installer, which is essential for the setup process.
• 🖥️ Viewers are guided to watch another video for step-by-step instructions on installing MySQL on a Windows operating system, covering basic database operations and connections.
• 🗂️ After installing MySQL, the next step is to create a database schema, which is necessary for database testing.
• 🔍 The 'Classic Models' sample database schema is recommended for practicing database testing, which can be downloaded from the MySQL website.
• 📁 The 'Classic Models' schema comes with an SQL file that needs to be imported to create the database structure and populate it with data.
• 🛠️ MySQL Workbench is introduced as the client software for the MySQL database server, used for connecting to the local server and managing databases.
• 🔄 The process of creating a new database and importing the 'Classic Models' schema data into it using MySQL Workbench is demonstrated.
• 📋 The 'Classic Models' database consists of eight tables, each serving a different purpose and interconnected through primary and foreign keys.
• 🔬 The video concludes with an overview of the tables in the 'Classic Models' schema and a preview of upcoming videos that will cover testing different database objects.

Q & A

What is the main topic of the video?

The main topic of the video is setting up an environment for database testing, specifically using MySQL as the database server.

Why is environment setup necessary before database testing?

Environment setup is necessary to install the database server, create the required schemas, and populate them with data, which provides the foundation for performing database testing.

Which database server is used in the video for demonstration?

MySQL is used in the video for demonstrating the setup process for database testing.

What is the link provided in the video for downloading MySQL?

The video provides a link in the description for downloading the MySQL installer from the MySQL Community Downloads page.

What are the two different flavors of MySQL mentioned in the video?

The two different flavors mentioned are for the web community and the installer community, with the latter being for standalone software installation on a local machine.

How can viewers learn to install MySQL on Windows?

Viewers can learn to install MySQL on Windows by following a provided video link that explains the step-by-step process, including using SQL Workbench and command line clients.

What is the sample database schema used for database testing in the video?

The sample database schema used is called 'Classic Models,' which is a popular sample database provided by MySQL.

What does the 'Classic Models' database schema contain?

The 'Classic Models' database schema contains several tables such as customers, employees, offices, order details, orders, payments, product lines, and products, which are interconnected through primary and foreign keys.

How can one import the 'Classic Models' database schema into MySQL?

One can import the 'Classic Models' database schema into MySQL by downloading the .sql file, then using the Data Import feature in MySQL Workbench to import the data into the 'classic models' schema.

What command is used to create a new database in MySQL?

The command used to create a new database in MySQL is 'CREATE DATABASE database_name;', where 'database_name' is the name of the new database, such as 'classic models'.

What command is used to switch to a specific database in MySQL?

The command used to switch to a specific database in MySQL is 'USE database_name;', which connects to the specified database for further operations.

What command in MySQL shows all the tables in a database?

The command 'SHOW TABLES;' in MySQL is used to display all the tables available in the currently selected database.

What will be covered in the next video according to the script?

The next video will cover how to test different database objects such as tables, views, and the relationships between tables, as discussed in a previous video.

Outlines

00:00

🛠️ Setting Up the Database Testing Environment

The script begins with an introduction to the video's purpose, which is to demonstrate the setup of an environment for database testing. The speaker explains the prerequisites, starting with the installation of a database server, such as MySQL, Oracle, or MSSQL. The focus is on MySQL, and the audience is guided to download it from the official website. The speaker also provides a link to a video tutorial on installing MySQL on Windows. After the installation, the script discusses the necessity of having a database schema for testing, specifically the 'classic models' schema, and provides a link to download it. The process of importing the schema into the newly installed MySQL server is outlined, including using MySQL Workbench to connect to the local server, create a new database, and import the data from the downloaded SQL file.

05:03

🔍 Exploring the Classic Models Database Schema

This paragraph delves into the exploration of the 'classic models' database schema after its successful import. The script describes how to use MySQL Workbench to create a new database, import data, and verify the presence of tables, views, stored procedures, and functions within the database. The focus is on the 'classic models' database, which contains several tables such as customers, orders, payments, and others, each serving a specific purpose and interconnected through primary and foreign keys. The speaker also mentions the availability of documentation for the schema and the importance of understanding each table's role in the database for effective testing.

10:04

🗓️ Preparing for Practical Database Testing

The final paragraph of the script wraps up the environment setup and previews the next steps. Having successfully installed MySQL and imported the 'classic models' database, the speaker outlines the next video's content, which will cover practical database testing of various objects such as tables, views, and the relationships between them. The script emphasizes the importance of this setup as a foundation for the upcoming testing procedures and assures the audience that the subsequent video will provide hands-on demonstrations of the testing techniques discussed previously.

Keywords

💡Database Testing

Database Testing is the process of verifying and validating the accuracy and completeness of a database management system by executing test cases. In the video, it is the main theme, as the script discusses setting up an environment for this purpose. The video aims to show viewers how to prepare for database testing, which includes understanding the difference between database testing and user interface testing.

💡User Interface Testing

User Interface Testing is the process of evaluating a software's graphical user interface to ensure it meets the requirements and provides a good user experience. The script contrasts this with database testing, highlighting that while user interface testing focuses on the frontend, database testing is concerned with the backend data integrity and functionality.

💡Database Server

A Database Server is a software system that manages and processes requests to a database, allowing multiple users and applications to access the data. In the script, the installation of a database server like MySQL is the first step in setting up the environment for database testing, emphasizing its importance as the foundation for any database operations.

💡Schemas

In database terminology, a Schema refers to the organization of data as a blueprint of how the database is structured, including tables, fields, and relationships. The script mentions the necessity of having required schemas for database testing, which is essential for understanding the structure and constraints of the database before testing.

💡MySQL

MySQL is an open-source relational database management system that is widely used for various applications. The script uses MySQL as an example of a database server to be installed and configured for the purpose of demonstrating database testing. It is chosen for its popularity and community support.

💡MySQL Workbench

MySQL Workbench is a unified visual tool for database architects, developers, and DBAs. It provides a graphical interface for MySQL database server configuration, development, and monitoring. In the video script, MySQL Workbench is used to connect to the local server, create databases, and import data for testing.

💡Sample Database

A Sample Database is a pre-populated database that developers and testers can use to practice and demonstrate database operations. The script instructs viewers to download a sample database called 'Classic Models' to use for testing, illustrating how to work with real-world data in a controlled environment.

💡SQL File

An SQL File is a text file containing SQL statements that can be executed against a database. In the context of the video, the script mentions an '.sql' file that contains the schema and data for the 'Classic Models' sample database, which needs to be imported into MySQL to set up the testing environment.

💡Data Import

Data Import refers to the process of loading data from an external source into a database. The script describes how to use MySQL Workbench to import data from an SQL file into the 'Classic Models' database, which is a crucial step in preparing the database for testing.

💡Tables

In database design, a Table is a collection of related data held in a structured format within a database. The script explains that the 'Classic Models' database contains several tables such as 'Customers', 'Orders', and 'Products', each serving a specific purpose and containing relevant data for the business context.

💡Primary Key and Foreign Key

A Primary Key is a unique identifier for a record in a database table, while a Foreign Key is a field that links to a primary key in another table, creating a relationship between the two. The script mentions that the 'Classic Models' database tables are interconnected using primary and foreign keys, which is important for relational integrity and testing.

Highlights

Introduction to database testing, its purpose, and its distinction from user interface testing.

Discussion on various types of database testing methods.

Demonstration of setting up an environment for database testing.

Installation of MySQL as a prerequisite for database testing.

Providing a link for downloading MySQL Community Server.

Instructions on selecting the appropriate MySQL installer for local machine setup.

Explanation of downloading and installing MySQL Workbench for database management.

Guidance on how to connect to a local MySQL server using Workbench.

Importance of having a database schema for effective database testing.

Downloading the 'classic models' sample database schema for testing purposes.

Process of importing the 'classic models' SQL file into the MySQL server.

Using MySQL Workbench to create a new database and import sample data.

Exploration of the 'classic models' database schema and its tables.

Description of the different tables within the 'classic models' schema, such as customers, orders, and products.

Explanation of how to execute SQL queries to view data within the database tables.

Teaser for the next video, which will cover practical testing of database objects like tables and views.

Conclusion of the video with a summary of the steps taken to set up the database testing environment.

Transcripts

hi everyone welcome you all in  our previous video we have seen uh  

introduction about database testing and as  part of that we have discussed about what is  

database testing and what is the difference  between user interface and database testing  

and also we have discussed about different types  of database testing now in the today's video i'm  

going to show you how we can set up environment  to perform database testing so before performing  

database testing we need to set up the environment  so first we need to install database server  

and which we need to have the database along  with the required schemas and then we will  

explore those schemas and then we will start  the database testing so now we'll start with  

environment setup so first of all what we need  to do is we need to first download and install  

mysql so you can use any database server you  can use it oracle or mssql server or any other  

databases but i am using here is my sequel okay  so download and install my sequel so these are  

first prior first prerequisite so for that what  you need to do is we need to go to this link  

and i will provide this link in the description  you can get it from there so go to directly  

this link and they provided uh installer of mysql  server so you can just download and install so you  

can just look at here mysql community downloads in  this link you can get it here so my sql installer  

for windows you can just click on this link and  again it will go to the next page and here you can  

it will show you two different flavors and  one is for web community the other is for  

uh installer community so installer only for  community so you can just select the second  

option if you want to install mysql server on  your local machine as a standalone software  

and once you click on this download it will try  to download your my sequel server installer in  

the dot msi file so once you get that file you  can start installing the my sql server so for  

that i'm just going to share this video okay  so here i'm just giving the link how to install  

uh how to install a ms sql server or how to  install my sql on your windows operating system  

so you can just go to the video how to install my  sequel on windows so you can just go to the video  

and you will be able to understand how we can  install my sequel on windows cycle expense step  

by step and along with the installation i also uh  explained about to database lines like how to use  

sql workbench and how to use command line clients  how to connect to the database so the basic stuff  

i have already explained in this video so please  go to the video so before proceeding further  

okay this is as a prerequisite so once we  install mysql on windows so the second thing is  

we need to have a database schema so for database  testing we need to have one database schema  

so for that i'm using a sample database schema  i'm going to download which is basically classic  

uh models okay so the first part is we have  successfully downloaded and install mysql server  

and the second step is we need to have sample  database download ms sql sample database the  

database name is classic models so how to download  this classic model so you can just go to this  

website and here you can just search for download  classic models database okay and you can get it  

directly from there my sequel website itself  we can just look at here mysql sample database  

and this is more popular database so by default  you will get it through mysql and but you will  

get only the schema but you don't have any tables  or anything so we need to download the data and  

we need to populate in our database so if i just  go to this link and they will provide you how to  

download mysql sample database you can just click  on this link download mysql sample database and as  

soon as you click on this link which will download  it a small zip file and go to this zip file and  

once extracted you will see small sql file here  mysql sample database dot sql file you can see  

and this particular file we have to import and  from this particular file we have to load the  

data into our database so for that what we need to  do is we need to first go to mysql so whichever we  

have installed in the first step we need to first  go and open the workbench so as soon as we have  

installed mysql or database server by default you  will get the workbench mssql workbench this is a  

client software of mysql database server and now  open this and as soon as you open this client and  

you will able to connect to the local server so  currently my sql is installed on my local machine  

so you can just go to the database and connect  to the database and here you need to provide the  

connection details like i provided something  like localhostname and 3306 is a default port  

number of mysql and i'm giving root username and  if password is required and you can specify the  

password also and once you provide the connection  details of your database here you can just say ok  

and it will automatically connect it to the your  local database and if i just look at here there  

are some default databases you can see already  here and these databases by default comes along  

with the mysql server which is uh by default it is  provided okay so now what we need to do we need to  

create our own database and then we need to import  the data into the database for our testing so we  

already downloaded this mysql sample database  so now we need to have this in our mysql server  

so for that we need to first open the sql editor  and here we can execute all the sql statements  

in mysql client or mysql workbench client so  how to create a database i'm just creating the  

new database how to create it create and sometimes  if you already have the database in this list you  

don't need to create again okay if the database  is not available then you need to create it so  

create a database this is a command which you have  to give create a database and then database name i  

say classic models so as soon as you executed this  command this will create a new database and if you  

refresh here you can see the new database which is  created and inside this there are no objects are  

available there are no database objects like we  don't have tables view store procedure functions  

right only the folders are there but we don't have  any objects here this is completely new database  

so now to perform the testing we need sample  tables right we need to create some sample view  

store procedure functions and so on so we will  see them later so first of all let us try to  

uh dump some sample data okay to start testing  so for that we whatever file we have already  

downloaded so we need to just import this  data into your database so how to import this  

data so we need to keep this sql file in some  folder and then go to mysql workbench from here  

you need to just go to the server menu so in  the server menu you can find something called  

data import so once you click on this data import  and you can easily import the data into the  

databases any of these databases and  we already downloaded one file it we  

need to select the second option here  because that is a self-contained file  

and import from self content file here we need  to browse our partner file so can click on this  

browse option and select the file whichever  we downloaded it is basically dot sql file  

then open and once you browse the file in the next  field we need to specify default to target schema  

so we already created classic models right so we  need to dump this data into the classic models  

now here select the classic models  from the dropdown so which you  

if you already created the database here which  will automatically pop up in this drop down  

and we selected the source and this  is a target database and as soon as  

i selected these two then you can say start  import button here you can see start import  

and once you click on the start input all the data  will be imported into your classic models database  

and still it is doing it yeah so once it  is done you can just close this window and  

then go to the database mac and here if i just  refresh this project or refresh your project  

refresh your database you can see the all the  tables are automatically populated now you can  

also see the tables like this so which database  you are going to connect now classic model so  

we can use one command called use classic models  and whenever you want to work with any databases  

here first we need to use the database this is a  command which will automatically connect to any of  

the database and from there we can execute other  queries so we can just execute this statement  

and now it is currently pointing to classic  models and then i want to see all the tables  

which are available in the classic models so we  can execute one more command called show tables so  

this particular command will show you what are all  tables which are available in the classic models  

database okay so now let us explore this uh schema  of this each and every table so for that they also  

given the documentation so this is actual schema  of classic model database which contains a number  

of tables like payments customers orders offices  employees product lines products order details  

so all together six seven eight tables are  there in the database and each table is  

internally connected with the primary key and  foreign key so we will also validate them later  

and if i just look at each and every table  is having their own purpose so we can just  

look at here customers that this is the  table basically stores the customers data  

and the products is a table which is basically  stores the all the product information and  

product lines will store different category of the  products and orders table will store the orders  

which are done by the customers or placed by the  customers and order details uh is another stable  

other table which will store the sales order line  items for each sale order so this will store more  

detailed information about the orders and payments  information payments table will store the payments  

made by the customers based on their account  and then employees table which is having all  

employees data which are working under the  particular organization or particular store  

and finally offices table which stores the sales  office data so these are the basic uh tables which  

are available on which are provided as part of  classic models database and which is also having  

some data and if you want to see some data from  the customers you can simply say select star from  

customers and execute this query and it will  show you what is the data which is available in  

this table okay similarly every table is already  populated some data which is already available  

so now our environment setup is done so we have  successfully installed mysql server database  

second step we have downloaded mysql sample  database and we have imported that into database  

and also we have explored the database schema  which is classic model so classic models contains  

are totally eight tables so what are those  tables we have customers table employees table  

offices order details orders payments product  lines and products so again each table is having  

different number of columns and each column is  having different type of data types and everything  

so we will see all these things as part of testing  so this is a basic thing which we need to set up  

before going for testing okay so in the next video  we will see how we can test different objects of  

database like how to test the table how to test  the view and how to test the relation between the  

tables okay so whatever types of testing we have  discussed in the previous video so we will do  

all kinds of testings practically on the database  objects okay that we will see in the coming video  

so that's all for this video guys thanks  for watching in the next video we will see