Database Management System Documentation

This assignment provides a comprehensive guide to using a database management system. The document explains the login process, which requires correct username and password. Once logged in, users can insert data into various tables by selecting the table and entering valid values for each field. Updating data involves selecting the table and changing the values of specific fields before clicking the update button. Deletion of data requires confirming the deletion with a yes or no option. The document is designed to help both specialized and non-specialized users understand how to use the system effectively.

DATABASE
ASSIGNMENT

DATABASE 2018
Table of Contents
Task 1 [ P1]............................................................................................................................................3
Introduction:...........................................................................................................................................3
Prerequisite Specifications:....................................................................................................................4
Flaws of the old database system:..........................................................................................................4
Design of a relational database system:.................................................................................................5
Entity Relationship Diagram:.................................................................................................................5
ER Diagram Symbols:............................................................................................................................6
ER Diagram For Patient Record System:...............................................................................................6
Relationship Diagram.............................................................................................................................7
Data Dictionary:.....................................................................................................................................8
Conclusion:..........................................................................................................................................10
Task4/P2...............................................................................................................................................10
Introduction:.........................................................................................................................................10
Data Validation....................................................................................................................................11
Constraints............................................................................................................................................11
Primary Key.........................................................................................................................................11
Foreign key:.........................................................................................................................................11
Unique Key..........................................................................................................................................11
Not Null Key........................................................................................................................................12
Implementing Necessary Constraints...................................................................................................12
Primary key..........................................................................................................................................12
Unique Key..........................................................................................................................................13
Not null Key.........................................................................................................................................13
Evidence of user interface and data validation....................................................................................14
Snapshots of login page and error message.........................................................................................14
Snapshots of data entry forms..............................................................................................................14

DATABASE 2018
Validate data querying multiple tables using joins..............................................................................15
Conclusion............................................................................................................................................17
Task6/P3...............................................................................................................................................17
Introduction..........................................................................................................................................17
SQL......................................................................................................................................................18
Basic Query Languages:......................................................................................................................18
Insert Statement....................................................................................................................................18
Examples..............................................................................................................................................18
Update Statement.................................................................................................................................21
Delete Statement..................................................................................................................................24
Product table.........................................................................................................................................24
Select Statement...................................................................................................................................27
Conclusion:..........................................................................................................................................28
Task8/P4...............................................................................................................................................29
Introduction..........................................................................................................................................29
Testing of Stock Management System.................................................................................................29
1. Insert.............................................................................................................................................29
2. Update...........................................................................................................................................30
Test Log...............................................................................................................................................33
Conclusion............................................................................................................................................34
Task11/P5.............................................................................................................................................34
Introduction..........................................................................................................................................35
Technical Documentation....................................................................................................................35
Introduction..........................................................................................................................................35
Overview..............................................................................................................................................35
Constraints/Risks..................................................................................................................................36
User Documentation.............................................................................................................................36

DATABASE 2018
User Manual.........................................................................................................................................36
Introduction..........................................................................................................................................36
Login Form...........................................................................................................................................36
For Inserting data.................................................................................................................................37
For updating data..................................................................................................................................39
For deleting data...................................................................................................................................40
Conclusion............................................................................................................................................41
Bibliography.........................................................................................................................................42

DATABASE 2018
Task 1 [ P1]
Design a relational database system using an appropriate design tools and techniques,
containing at least four tables, with clear statements of user and system requirements.
Introduction:
Stands for "Relational Database Management System." A RDBMS is a DBMS plot especially for
social databases. A relational database refers to a database that stores data in a composed
arrangement, using lines and sections. This makes it easy to discover and get to specific qualities
inside the database. It is "relational" because values inside each table are connected with each other.
Tables might be connected with different tables. The relational structure makes it conceivable to run
inquiries over different tables on the double.
The management system of a stationary shop means to make a database in order to keep records of
all data about product, Purchase, sales and so forth. As a junior database administrator I was enlisted
to design a relational database system of the stationary shop.
In this task I have designed a relational database system of the stationary shop incorporating clear
prerequisites of both, user and system. Be that as it may, before designing an alluring and interactive
database I required data about the stationary and its working system. Without procuring data about
the stationary and its management system the important database design was very unthinkable. In
this way, to design a compelling and productive database that can satisfy every one of the necessities
of stationary management I utilized beneath specified apparatuses and strategies:
 Interviews:
Like surveys, interviews are the crucial strategy in gathering data about the current system.
Different questions are advanced among the members of the system straightforwardly to
gather data about the system with respect to the issues and changes required in it. We met
with every member of the stationary exclusively and addressed them about the present
system, challenges they are confronting and changes required in the present system to expel
each one of those troubles.
 Observations:
Observation is likewise an accommodating methodology for accumulation of information
and data about any system. After observing the structure of a system, an essential idea about
it can be obtained. We observed the work place, stationary records, sales records, purchase

DATABASE 2018
records and working days of members deliberately which help to design the database of the
stationary management system. We even observed the old database management system of
the database and made notes about confinements of the current system and redesigns
required in it. Observation helped us to think about the correct state of the old system and
changes that are required in the new system.
Prerequisite Specifications:
The diverse management system has numerous necessities and requirements that are should have
been satisfied. After breaking down all the gathered data utilizing the above strategies and
methodology we investigated the different necessities that are required in the stationary management
system. The old database systems had different limitations and confinements which were should
have been overcome. The users and new connection database system included after prerequisites:
i. To keep enrollment points of interest of item, buy and deals.
ii. To keep rundown of all the customer with their address.
iii. To show rundown of the considerable number of customers.
iv. To track records of the new clients.
v. To ascertain add up to number of customer that were suffered by a specific illness.
Flaws of the old database system:
After analyzing the needs and requirements of the users and system with detailed examination of the
old system function procedures and methods I have pointed out the limitations of the old database
system:
i. Use of old database system with limitations and inaccessibility of new highlights.
ii. Difficulty in overseeing documents and records because of poor illustrations.
iii. Unable to track records of customer with their genuine address.
iv. Difficulty in generating reports of the patients as the information were not
standardized.
v. Problem in seeking patients and customer by utilizing their ID as the outside keys
were not utilized.
Design of a relational database system:

DATABASE 2018
A design is a work procedure in which greeting or conceptualization is kept into setup, configuration,
display, pattern, plan or part in light of particular customer design. To meet specificmatters a design
is made. The design that will be utilized as a part of full setting must be alluring, satisfy the
necessities of the users and good to the earth. With a mean to design an interactive and appealing
social database system satisfying every one of the users and system prerequisites following design of
stationary uniquely quiet record system has influenced utilizing Entity Relation To diagram (ER
Diagram).
Entity Relationship Diagram:
Entity Relationship Diagram, also called Entity Relationship Model is a visual representation of
different data using conventions that illustrates how entities relate to each other within a system
(Anon., n.d.). It is a graphical portrayal utilized as a part of processing regularly for organization of
data. ER diagram are oftentimes utilized amid advancement organize procedure to distinguish
different components and their relationships with each other.
 Relationships between Entities:
A relationship demonstrates how different entities are relationship with each other. There are
four sorts of relationship between elements:
 One-to –one:
The relationship can be one-to-one when just a single occasion of an element is related with
other example of another substance. In understanding record framework, a patient has one
specific address.
 One-to-Many:
The relationship can be one-to-many when one instance of a substance is related with one or
many instances of another element. In persistent record system, a customer,sales and stock
have one or many contact numbers.
 Many-to-One:
The relationship can be many-to-one when many instances of an entity is related with one
case of another element. In stuck record system, many customers can be related with one
single system.
 Many-to-Many:
The relationship can be many-to-many when one occurrence of a substance An is related
with one, zero or numerous cases of element B and another case of element B is related with
one, zero or numerous cases of element A. In understanding record framework, numerous
purchase may visits numerous sales

DATABASE 2018
ER Diagram Symbols:
In ER Diagram various symbols are used to represent the information of the system. Different
symbols different elements:
 Rectangular Boxes represents entities.
 Circle represents attributes.
 Diamond represents relationships.
ER Diagram For Patient Record System:
Fig: ER diagram of Stock Management System
Relationship Diagram

DATABASE 2018
Fig: Relationship Diagram of Stock Management System
The interrelationship outline or a system diagram which shows connection amongst circumstances
and end results is known as relationship diagram. The above diagram shows the relationship chart
which is used when a mind complex issue is being bankrupt down for causes and complex
arrangement is being completed. The relationship diagram shows the association and the impact for
the difference in that district like SMS helps in getting two outcomes i.e. increase customer service
and addition advantage. In like manner, when SMS is constructed the prerequisite of the new
programming extended for the change with the users preparing and the new machine is required.
New machine in like manner gives two outcomes i.e. executive preparing and moreover augment
power or power arrange which augment in working cost. This diagram shows the association of the
system with the prerequisites and the improvements. Relationship diagram makes users to think
about the prerequisites and the requirement for the change for their stationary shop.
Data Dictionary:
Data Dictionary is an arrangement of database tables used to store data. It contains data about
database questions, for example, tables, segments, index, data type and views. Essentially, data
dictionary is utilized as a part of SQL server for executing queries and is consequently refreshed at
whatever point the articles in database are changed, expelled or changed. The data dictionary for the
Patient Record System is depicted below:
Product Table

DATABASE 2018
Customer Table
Supplier Table
Purchase Table
Sales Table

DATABASE 2018
Conclusion:
thus, the design of relational database system of the patient record system was made utilizing
different above devices and strategies. ER diagram caused in greater stretch out to design the system
recognizing different entities and traits. The ER Diagram instrument and information lexicon
demonstrated very valuable in effective design of interactive and appealing database system
satisfying every one of the users and system prerequisites.

DATABASE 2018
Task4/P2
Develop the database system with evidence of user interface, output and data validations, and
querying across multiple tables.
Introduction:
Database Management System is a software which enables clients to make, recover, update, delete
and manage information in a database. Different DBMS programming like: My SQL and so on are
utilized for performing such activities on database.
In this task, I have indicated how I have actualized different information approvals and queries in My
SQL to design database and I am will explain the limitations like primary key, foreign key, unique
key, not null key. Furthermore, I will likewise explain about the user interface, output, data
validation and joins like inner join, left join, right join, outer join etc.
Data Validation
Data validation is a procedure that guarantees the conveyance of perfect and clear data to the
programs, applications and services utilizing it. It checks for the integrity and validity of data that is
being inputted to various programming and its parts. Data validation guarantees that the data
conforms to the prerequisites and quality benchmarks.Data validation is otherwise called input
validation.
Constraints
Primary Key
A primary key is an uncommon relational database table section (or mix of segments) assigned to
uniquely identify every table record. A primary key’s main features are:
 It must contain a unique value for each row of data.
 It can't contain invalid qualities.
A primary key is either a current table section or a segment that is particularly produced by the
database as indicated by a characterized grouping.
Foreign key:
Foreign key acts as cross reference between tables and uniquely identifies a row/record in another
table.

DATABASE 2018
Unique Key
Unique limitation is connected duplication isn't conceivable. Unique imperatives implement a
segment or an arrangement of column to have unique values.
Not Null Key
Not Null constraint watches that a column has a few values and isn't null. At the point when no
esteem is embedded into table it takes Null value as a matter of course however when Not Null
constraint is upheld the tables cannot have Null values.
Implementing Necessary Constraints
Primary key
Product Table
create table Product(
ProductIDuniqueidentifier PRIMARY KEY);
Customer Table
create table Customer(
CustomerIDuniqueidentifier primary key);
Supplier Table
create table Supplier(
SupplierIDuniqueidentifier primary key);
Purchase Table
create table Purchase(
PurchaseIDuniqueidentifier primary key);
Sales Table
create table Sales(
SalesIDuniqueidentifier primary key);

DATABASE 2018
Foreign Key
Unique Key
Product Table
CREATE TABLE Product (
ProductIDuniqueidentifier UNIQUE);
Customer Table
CREATE TABLE Customer (
CustomerIDuniqueidentifier UNIQUE);
Supplier Table
CREATE TABLE Supplier (
SupplierIDuniqueidentifier UNIQUE);
Purchase Table
CREATE TABLE Purchase (
PurchaseIDuniqueidentifier UNIQUE);
Sales Table
CREATE TABLE Sales (
SalesIDuniqueidentifier UNIQUE);
Not null Key

DATABASE 2018
Product Table
CREATE TABLE Product (
ProductIDuniqueidentifier NOT NULL);
Customer Table
CREATE TABLE Customer (
CustomerIDuniqueidentifier NOT NULL);
Supplier Table
CREATE TABLE Supplier (
SupplierIDuniqueidentifier NOT NULL);
Purchase Table
CREATE TABLE Purchase (
PurchaseIDuniqueidentifier NOT NULL);
Sales Table
CREATE TABLE Sales (
SalesIDuniqueidentifier NOT NULL);
Evidence of user interface and data validation
Snapshots of login page and error message

DATABASE 2018
If wrong user name or password is entered, it does not let you to login and give the error message
“Invalid username or password”.
Snapshots of data entry forms
 Drop down validation
Drop down validation allows you to choose only the contents that is recorded in it like that of “Size”
as mentioned above.
 Field validation with messages
Validate data querying multiple tables using joins
 Inner Join
Inner Join is a simple join which is used to return records that have matching values in both
tables.
Query
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
 Left Join
The SQL LEFT JOIN restores all rows from the left table, regardless of whether there are no
matches in the correct table. This implies if the ON proviso matches 0 (zero) records in the
correct table; the join will in any case restore a row in the outcome, however with NULL in
every column from the correct table.

DATABASE 2018
This implies a left join restores every one of the values from the left table, in addition to
coordinated values from the correct table or NULL if there should be an incidence of no
managing join found.
Query
select ProductName,s.SalesDate,(C.CustomerName +' '+ C.MiddleName +' '+ C.LastName)as
CustomerName from Product As p
Left Outer Join Sales As s
on p.ProductID = s.ProductID
Left Outer Join Customer as c
on s.CustomerID = c.CustomerID
Result
 Right Join
Restore all records from the correct table, and the coordinated records from the left table.
Query
select ProductName,s.SalesDate, (C.CustomerName +' '+ C.MiddleName +' '+ C.LastName)as
CustomerName from Product As p
Right Outer Join Sales As s
on p.ProductID = s.ProductID
Right Outer Join Customer as c
on s.CustomerID = c.CustomerID
Result

DATABASE 2018
 Cross Join
This type of join returns all rows from the left-hand table and right-hand table with nulls in
place where the join condition is not met. This sort of join restores all lines from the left-hand
table determined in the ON condition and just those rows from the other table where the
joined fields are equivalent (join condition is met).
Query
select *from Product
cross join Sales
cross join Customer
Result
Conclusion
Different queries are connected on the distinctive tables and modules of Patient Record System
Database and their correct outcomes are gotten effectively. Different imperatives are upheld to set
standards while inserting the qualities on the distinctive tables. Each one of those approach and
system made tables and records and perform distinctive activities and accomplish wanted yields.

DATABASE 2018

DATABASE 2018
Task6/P3
Implement a query language into the relational database system.
Introduction
Query language is used to make queries in a database. It is used mainly for creating, accessing and
modifying in and out from database management system(Techopedia, 2016).users to type the
organized command which is like English dialect. They give a medium to recovering records from
database by performing different activities previously creating outcomes. Different query languages
are utilized as a part of RDMS for recovering records or parts of records.
In this errand, I have actualized four query languages and portrayed the outcomes produced in the
wake of utilizing those inquiries.
SQL
SQL (Structured Query Language) is a standardized programming language used for managing
social databases and performing diverse exercises on the data in them. At first made in the 1970s,
SQL is reliably used by database administrators, and furthermore by architects forming data
combination contents and data specialists wanting to set up and run investigative request. The
employments of SQL consolidate changing database table and file structures; adding, updating and
deleting rows of data; ; and recouping subsets of data from inside a database for trade getting ready
and examination applications. Queries and other SQL assignments show up as commands made as
articulations - for the most part used SQL statements includes select, insert, update, delete, create,
alter and truncate.
Basic Query Languages:
Some basic query languages like insert, update, select and delete are used in SQL to perform specific
functions. The implementation and results of those queries are shown below:
Insert Statement
Insert statement is a statement that are used to insert data in a table of a database.
Syntax:
Insert into <tablename>
Values (expression1, expression2, …..),
(expression1, expression2, …..),

DATABASE 2018
………
Examples
Table
Table
name
Query in SQL Front End
Product
table
insertinto Product
values(newid(),'Cop
y','Normal','Classm
ate','PCS','10','10
0'),
(newid(),'Eraser','
Normal','Nataraj','
PKT','20','10')
privatevoidbtnInsert_Click(object sender,
EventArgs e)
{
if(txtProductName.Text=="")
{
MessageBox.Show("ProductName is required");
return;
}
conn.Open();
String query = "Insert into
Product(ProductID,ProductName,Brand,Size,UOM,MINSt
ockLevel,SalesPrice)
values(newid(),'"+txtProductName.Text+"','"+txtBra
nd.Text+"','"+txtSize.Text+"','"+txtUOM.Text+"','"
+txtMinStock.Text+"','"+txtSalesPrice.Text+"')";
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Product Inserted
Successfully !!!");
txtProductName.Text = "";
txtSize.Text = "";
txtBrand.Text = "";
txtMinStock.Text = "";
txtUOM.Text = "";
txtSalesPrice.Text = "";
conn.Close();
dataLoad();
}
Customer
Table
insertinto Customer
values(newid(),'Sak
sham','','Subba','B
haktapur','98434756
45'),
(newid(),'Ashish','
Jung','Kunwar','Put
alisadak','98604761
48')
privatevoid button1_Click(object sender, EventArgs
e)
{
if (txtFirstName.Text == "")
{
MessageBox.Show("FirstName is required");
return;
}
conn.Open();
String query = "Insert into
Customers(CustomerID,CustomerName,MiddleName,LastN
ame,CustomerAddress,CustomerContact)
values(newid(),'" + txtCustomerName.Text + "','" +
txtMiddleName.Text + "','" + txtLastName.Text +
"','" + txtCustomerAddress.Text + "','" +
txtCustomerContact.Text + "')";
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);

DATABASE 2018
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Customers Inserted
Successfully !!!");
txtFirstName.Text = "";
txtMiddleName.Text = "";
txtLastName.Text = "";
txtCustomerAddress.Text = "";
txtCustomerContact.Text = "";
conn.Close();
dataLoad();
}
Supplier
Table Insertinto Supplier
values(newid(),'Bha
ndari
Stores','Bhaktapur'
,'01435623')
privatevoidbtnInsert_Click(object sender,
EventArgs e)
{
if (txtSupplierName.Text == "")
{
MessageBox.Show("SupplierName is required");
return;
}
conn.Open();
String query = "Insert into
Supplier(SupplierID,SupplierName,SupplierAddress,S
upplierContact) values(newid(),'" +
txtSupplierName.Text + "','" +
txtSupplierAddress.Text + "','" +
txtSupplierContact.Text + "')";
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Supplier Inserted
Successfully !!!");
txtSupplierName.Text = "";
txtSupplierAddress.Text = "";
txtSupplierContact.Text = "";
conn.Close();
dataLoad();
}
Purchase
Table
Insertinto Purchase
values
(newid(),newid(),'2
018-04-
23','50','200',newi
d())
privatevoidbtnInsert_Click(object sender,
EventArgs e)
{
conn.Open();
String query = "Insert into
Purchase(PurchaseID,ProductID,PurchasedOn,Purchase
Rate,PurchaseQty,SupplierID) values(newid(),'" +
txtProductID.Text + "','" + txtPurchasedOn.Text +
"','" + txtPurchasedRate.Text + "','" +
txtPurchaseQTY.Text + "','" + txtSupplierID.Text +
"')";
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("PurchaseDetail Inserted
Successfully !!!");
txtProductID.Text = "";
txtPurchasedOn.Text = "";
txtPurchaseRate.Text = "";

DATABASE 2018
txtPurchaseQty.Text = "";
txtSupplierID.Text = "";
conn.Close();
dataLoad();
}
Sales Table insertinto Sales
values
(newid(),newid(),'2
018-04-
13','34',newid())
privatevoidbtnInsert_Click(object sender,
EventArgs e)
{
conn.Open();
String query = "Insert into
Sales(SalesID,ProductID,SalesDate,SalesQty,Custome
rID) values(newid(),'" + txtProductID.Text + "','"
+ txtSalesDate.Text + "','" + txtSalesQty.Text +
"','" + txtCustomerID.Text + "')";
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("SalesDetail Inserted Successfully
!!!");
txtProductID.Text = "";
txtSalesDate.Text = "";
txtSalesQty.Text = "";
txtCustomerID.Text = "";
conn.Close();
dataLoad();
}
Update Statement
Update statement is a statement that are used to update data in a table of database.
Syntax:
Update <tablename>
Set< column1> = value1, column2 = value2, ..
Where condition
Examples
Product Table
Table
name
Query in SQL Front End
update Product
set Size ='Normal'
where ProductName
='Stapler'
privatevoidbtnUpdate_Click(object sender,
EventArgs e)
{
conn.Open();

DATABASE 2018
String query = "Update Product set ProductName =
'" + txtProductName.Text + "', Brand ='" +
txtBrand.Text + "', Size ='" + txtSize.Text + "',
UOM ='" + txtUOM.Text + "', MINStockLevel ='" +
txtMinStock.Text + "', SalesPrice ='" +
txtSalesPrice.Text + "' where ProductID =
'"+txtProductId.Text+"'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Product Updated
Successfully !!!");
txtProductName.Text = "";
txtSize.Text = "";
txtBrand.Text = "";
txtMinStock.Text = "";
txtUOM.Text = "";
txtSalesPrice.Text = "";
conn.Close();
dataLoad();
}
Customer
Table
update Customer
setLastName='Yakkha
'
whereCustomerName='
Hari'
privatevoid button2_Click(object sender, EventArgs
e)
{
conn.Open();
String query = "Update Customers set CustomerName
= '" + txtCustomerName.Text + "', MiddleName ='" +
txtMiddleName.Text + "', LastName ='" +
txtLastName.Text + "', CustomerAddress ='" +
txtCustomerAddress.Text + "', CustomerContact ='"
+ txtCustomerContact.Text + "' where CustomerID =
'" + txtCustomerID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Customers Updated
Successfully !!!");
txtCustomerName.Text = "";
txtMiddleName.Text = "";
txtLastName.Text = "";
txtCustomerAddress.Text = "";
txtCustomerContact.Text = "";
conn.Close();
dataLoad();
}
Supplier
Table
update Supplier
setSupplierAddress=
'Baagbazar'
whereSupplierName='
KK Supliers'
privatevoidbtnUpdate_Click(object sender,
EventArgs e)
{
conn.Open();
String query = "Update Supplier set SupplierName =

DATABASE 2018
'" + txtSupplierName.Text + "', SupplierAddress
='" + txtSupplierAddress.Text + "',
SupplierContact ='" + txtSupplierContact.Text + "'
where SupplierID = '" + txtSupplierID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Supplier Updated
Successfully !!!");
txtSupplierName.Text = "";
txtSupplierAddress.Text = "";
txtSupplierContact.Text = "";
conn.Close();
dataLoad();
}
Purchase
Table
update Purchase
setPurchaseRate='80
'
wherePurchaseQty='5
0'
privatevoidbtnUpdate_Click(object sender,
EventArgs e)
{
conn.Open();
String query = "Update Purchase set ProductID = '"
+ txtProductID.Text + "', PurchasedOn ='" +
txtPurchasedOn.Text + "', PurchaseRate ='" +
txtPurchaseRate.Text + "', PurchaseQty ='" +
txtPurchaseQty.Text + "', SupplierID ='" +
txtSupplierID.Text + "' where PurchaseID = '" +
txtPurchaseID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Product Updated
Successfully !!!");
txtProductID.Text = "";
txtPurchasedOn.Text = "";
txtPurchaseRate.Text = "";
txtPurchaseQty.Text = "";
txtSupplierID.Text = "";
conn.Close();
dataLoad();
}
Sales Table
update Sales
setSalesDate='2018-
05-05'
whereSalesQty='2'
privatevoidbtnUpdate_Click(object sender,
EventArgs e)
{
conn.Open();
String query = "Update Sales set ProductID = '" +
txtProductID.Text + "', SalesDate ='" +
txtSalesDate.Text + "', SalesQTY ='" +
txtSalesQTY.Text+ "', CustomerID ='" +
txtCustomerID.Text + "' where SalesID = '" +
txtSalesID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,

DATABASE 2018
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("SalesDetail Updated
Successfully !!!");
txtProductID.Text = "";
txtSalesDate.Text = "";
txtSalesQty.Text = "";
txtCustomerID.Text = "";
conn.Close();
dataLoad();
}
Delete Statement
Delete statements are the statements that are used to delete the data which are inside a table.
Syntax:
Delete from <tablename>
Where condition
Examples
Product table
Table name Query in SQL Front End
Product
table
Deletefrom Product
where ProductName
='Purse'
privatevoidbtnDelete_Click(object sender,
EventArgs e)
{
DialogResult result = MessageBox.Show("Do you
want delete selected Row?", "Important",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
conn.Open();
String query = "Delete from Product where
ProductID = '" + txtProductId.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Product Deleted

DATABASE 2018
Successfully !!!");
txtProductName.Text = "";
txtSize.Text = "";
txtBrand.Text = "";
txtMinStock.Text = "";
txtUOM.Text = "";
txtSalesPrice.Text = "";
conn.Close();
dataLoad();
}
else
{
dataLoad();
}
}
Customer
table
deletefrom Customer
whereLastName='Subba'
privatevoidbtnDelete_Click(object sender,
EventArgs e)
{
DialogResult result = MessageBox.Show("Do you
want delete selected Row?", "Important",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
conn.Open();
String query = "Delete from Customers where
CustomerID = '" + txtCustomerID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Customers Deleted
Successfully !!!");
txtCustomerName.Text = "";
txtMiddleName.Text = "";
txtLastName.Text = "";
txtCustomerAddress.Text = "";
txtCustomerContact.Text = "";
conn.Close();
dataLoad();
}
else
{
dataLoad();
}
}
Supplier
table
deletefrom Supplier
whereSupplierName='Ra
j Treders'
privatevoidbtnDelete_Click(object sender,
EventArgs e)
{
DialogResult result = MessageBox.Show("Do you
want delete selected Row?", "Important",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{

DATABASE 2018
conn.Open();
String query = "Delete from Supplier where
SupplierID = '" + txtSupplierID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Supplier Deleted
Successfully !!!");
txtSupplierName.Text = "";
txtSupplierAddress.Text = "";
txtSupplierContact.Text = "";
conn.Close();
dataLoad();
}
else
{
dataLoad();
}
}
Purchase
table
deletefrom Purchase
wherePurchaseRate='15
0'
privatevoidbtnDelete_Click(object sender,
EventArgs e)
{
DialogResult result = MessageBox.Show("Do you
want delete selected Row?", "Important",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
conn.Open();
String query = "Delete from Purchase where
PurchaseID = '" + txtPurchaseID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("Product Deleted
Successfully !!!");
txtProductID.Text = "";
txtPurchasedOn.Text = "";
txtPurchaseRate.Text = "";
txtPurchaseQty.Text = "";
txtSupplierID.Text = "";
conn.Close();
dataLoad();
}
else
{
dataLoad();
}
}
Sales Table deletefrom Sales
whereSalesQty='34'
privatevoidbtnDelete_Click(object sender,
EventArgs e)
{

DATABASE 2018
DialogResult result = MessageBox.Show("Do you
want delete selected Row?", "Important",
MessageBoxButtons.YesNo,
MessageBoxIcon.Question);
if (result == DialogResult.Yes)
{
conn.Open();
String query = "Delete from Sales where SalesID =
'" + txtSalesID.Text + "'";
MessageBox.Show(query);
SqlDataAdaptersda = newSqlDataAdapter(query,
conn);
sda.SelectCommand.ExecuteNonQuery();
MessageBox.Show("SalesDetail Deleted Successfully
!!!");
txtProductID.Text = "";
txtSalesDate.Text = "";
txtSalesQty.Text = "";
txtCustomerID.Text = "";
conn.Close();
dataLoad();
}
else
{
dataLoad();
}
}
Select Statement
Syntax:
Select* from <tablename>
Table name Query in SQL Front End
Product Table select*from
Product
publicvoiddataLoad()
{
conn.Open();
String query = "Select * from Product";
SqlDataAdaptersda = newSqlDataAdapter(query, conn);
DataTable dt = newDataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
Customer
Table
select*from
Customer
publicvoiddataLoad()
{
conn.Open();
String query = "Select * from Customers";
SqlDataAdaptersda = newSqlDataAdapter(query, conn);
DataTable dt = newDataTable();

DATABASE 2018
sda.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
Supplier
Table
select*from
Supplier
publicvoiddataLoad()
{
conn.Open();
String query = "Select * from Supplier";
SqlDataAdaptersda = newSqlDataAdapter(query, conn);
DataTable dt = newDataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
Purchase
Table
select*from
Purchase
publicvoiddataLoad()
{
conn.Open();
String query = "Select * from Purchase";
SqlDataAdaptersda = newSqlDataAdapter(query, conn);
DataTable dt = newDataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
Sales Table select*from
Sales
publicvoiddataLoad()
{
conn.Open();
String query = "Select * from Sales";
SqlDataAdaptersda = newSqlDataAdapter(query, conn);
DataTable dt = newDataTable();
sda.Fill(dt);
dataGridView1.DataSource = dt;
conn.Close();
}
Conclusion:
The distinctive query languages have diverse tasks. At whatever point those queries are contribution
to RDMS utilizing orders they play out their particular tasks and create comes about. Therefore, with
the assistance of query languages data can be gotten to, recovered and changed according to the
necessities and prerequisites.
Task8/P4
Test the system against user and system requirements.
Introduction

DATABASE 2018
Database testing alludes to the checking schema, tables, columns, keys and indexes, triggers and
approving data duplication of the database under test which includes creating queries to test the
database and check its responsiveness. It is extremely significant to think about database testing to
keep up database adequately and guarantee security and quality.
In this task, different test are done to check integrity, consistency and validate database adequately.
The test conveyed, comes about created and results to be delivered are demonstrated plainly.
Unit Testing
unit testing is a product testing strategy by which singular units of source code, sets of at least one
computer program modules together with related control data, use methodology, and working
strategies, are tested to decide if they are fit for us.
Integrated Testing
Integration testing is the way toward testing the mix or interfaces among segments and teams up with
different parts of the structure like working framework, document system and hardware or interfaces
between system.
StreetTesting
street testing is the way toward choosing the limit of a computers, basis, program or device to keep
up a specific level of capability under negative conditions. The technique can incorporate
quantitative tests done in a lab, for instance, evaluating the repeat of goofs or basis crashes.
Testing of Stock Management System
I have done the testing on the system. Here I am going to present the snapshots of the testing that I
have done.
1. Insert
I have inserted the product with the following values:
ProductName= Sharpner
Brand= Nataraj
Size= small
UOM = PKT
MINStockLevel = 15

DATABASE 2018
SalesPrice = 31
Front End
Back End
After inserting the values in front end, the values were inserted successfully in the front end as well.
2. Update
I have updated the size of following values from medium to large.
ProductName = INK
Brand = Pilot
Size = Large
UOM = Bottle

DATABASE 2018
MINStockLevel = 50
SalesPrice = 195
Front End
Back End
After updating the value, when I checked in front end the value was updated there successfully too.
3. Delete
I have deleted the product with following values

DATABASE 2018
ProductName= Sharpner
Brand= Nataraj
Size= Medium
UOM = PKT
MINStockLevel = 15
SalesPrice = 31
Front End
Back End
After deleting a product while checking in the front end the product with those values were not found
that means that product was deleted successfully.
Test Log
Test
Date
Test
Description
Expected Result Actual Result
11 The insert The discourse boxwith As expected the dialogue box exploded up.

DATABASE 2018
May
,
2018
statement of
product
table was
tested.
message "product
Inserted Successfully!!!"
should exploded up in the
awaken of inserting.
11
May
,
2018
The update
statement of
product
table was
tested.
The discourse box with
the question of refresh
should fly up before the
exchange box with
message "Product
Updated Successfully!!!".
As expected the dialogue box of query popped
up before the dialogue box of message.
11
May
,
2018
The delete
statement of
product
table was
tested.
The exchange of
affirmation should fly up
before the discourse box
with query and message
of "product Deleted
Successfully!!!".
As probable the dialogue box of confirmation
exploded before the dialogue box of query and
the message.

DATABASE 2018
Conclusion
All the necessities essential for creating a patient record system was generated and product database
was created to keep the records of product, purchase, sales, customer, supplier, data was recorded.
The patient record was lastly created when the inventory management system so created was realized
after testing. Testing is very essential for certifying inventory management system effectiveness and
value. It helps to check the framework of the system and errors that might be present in the system.
Thus, testing helps to maintain database and check its reaction without beginning any error.
Task11/P5
Produce technical and user documentation.
Introduction
I have made a Stock Management System for a stationary shop which helps the proprietor and the
staffs of stationary shop to get the data and information about product, sales, purchase, supplier and
customers. Documentation just alludes to manuals, guidelines and methodology that give a suitable
direction to the correct utilize and maintenance of a created software. It helps use to utilize the

DATABASE 2018
product and get to know its features, capacities and limitations. There are basically two type of
documentation, user documentation and technical documentation.
In this task, I have created a technical and user documentation of the 'stock management system'
software that have been produced tending to every one of the prerequisites of the stationary and its
management system.
Technical Documentation
Introduction
Technical documentation refers to the information that is intended to help the maintainers of the
system (IGCSE ICT, 2014). It for the most part incorporates dealing with the framework, its
practicality and design, approval check and how information is handled.Stock Management System
is a program that reasons administrator and staffs to get definite data of product, sales, client,
supplier, purchase and many different movements through the help of program. In the event that any
customer or staff need to know the data about movements that are going on the shop, they can get it
from the program without going to stationary shop over and over. Also, they can store information
with respect to the shop in the program so they can get more data about the products, supplier,
customer, sales and purchase which are recognized with the shop.
Overview
I have created five tables in a program. The five tables that are created in programs are tables of
product, sales, supplier, purchase and customers. The five different table consists the data that are
related to the respective table like product table consists the data regarding products like productid,
product name, its size, brand and other, purchase table consists the data like purchase id, purchase
rate, purchase quantity and so on. Like this every table consists data related to them. Staffs can
update, insert, delete data from the database to program.
I have made five tables in a program. The five tables that are made in programs are tables of Product,
Sales, Supplier, Purchase and Customers. The five various table includes the information that are
identified with the certain table like Product table covers the information in regards to products like
ProductID, ProductName, its Size, Image and other, Purchase table comprises the information like
PurchaseID, Purchase rate, purchase expanse etc. Like this each table comprises information
identified with them. Staffs can update, insert, delete data from the database to program.
Constraints/Risks
User Documentation

DATABASE 2018
User documentation refers to the information that is intended to help the non-technical users who do
not need to know about how the system functions (IGCSE ICT, 2014). It for the most part
incorporates about the equipment and programming required to utilize the framework, procedure of
introducing and utilizing the framework and different information sources and output of the system.
User Manual
Welcome to the user’s documentation page of “Stock Management System”. This manual lets you to
think about the application and in addition the method for utilizing it.
Introduction
Stock management System is a program or application that lets you to know the detailed information
of a stationary shop. You can get the information about the product that you have, the customer that
you have sold product, total sales of shop, supplier from whom you get the product and the number
of product that you purchase from supplier easily. It also secure your data from outsider since you
have to login with correct password and username to extract data from table.
After short briefing about the program I am going to show you how the program is run and how can
you perform different operation inside the program.
Login Form.
Steps Screenshots
When we first open application a login form
will be seen.

DATABASE 2018
If there should be an occurrence of wrong
password or username the error message will be
seen i.e. "You have entered wrong username or
password".
we are required to login first to open forms of
product, customer, sales, purchase and supplier.
in the wake of entering correct username and
password tap the catch "Login".
In the wake of signing in you can refresh,
embed or erase the information in various table
by clicking in the menu that we like.
For Inserting data
Steps Screenshots(Examples)

DATABASE 2018
1. For inserting data, you are
required to login first with correct
username and password.
2. If Username and password is
correct then main form was
opened.
3. After logging in we can choose
the table where you want to insert
data. After choosing the table
where we want to insert, we can
insert data. To insert data, we are
required to insert the valid values
in the respective field.
4. If values are not inserted in fields
like “ProductName”,
”CustomerName” and
“SupplierName”, dialogue box
will appear with message
”ProductName is required”,
“CustomerName is required” and
“SupplierName is required”.
etc.

DATABASE 2018
5. After entering the valid data in
respective field click button
“Insert”. After clicking the insert
button dialogue box with
confirmation message of inserting
will appear.
For updating data
Steps Screenshots(Examples)
1. For inserting data, we are required
to login first with correct
username and password. In the
wake of login, we can pick the
table where we need to update
data. In the wake of choosing the
table we need to choose the data
that you need to refresh.
2. After selecting you can change the
values of the field that you want
to alter and click button
“Update”. After clicking the
update button, the dialogue box
with query of update will be seen.
3. After pressing the “OK”,
confirmation message of updating
will appear.

DATABASE 2018
4. After updating the value, when I
checked in front end the value was
updated there successfully too.
For deleting data
Steps Screenshots(Examples)
1. For deleting data from the table,
you are required to log in first.
After logging with the user name
and password you choose the
table from where we want to
delete the data.
2. After selecting the data, you click
the button “Delete”. Since the data
can be deleted by mistake
sometimes, to avoid that
confirming message is kept before
deletion of data. Confirmation
message saying “Do you want to
delete selected row” with choices
“Yes” or “No” will appear.
3. If you want to delete the row you
can click “Yes” button if not you
can click “No” button. If you
click button “Yes” the dialogue
box with query of delete will be
see.

DATABASE 2018
4. After click “OK” button in the
query dialogue box you can delete
the selected row in the respective
table. The dialogue box will arise
confirming that the selected row is
deleted from the table.
5. Here, we can see the product is
deleted successfully.
You can run the application and perform distinctive task like insert, update and delete in the
application by following the procedure that I have clarified previously. Following the above
execution, you can get diverse data from various table. This program enables you to know the
exercises like benefit, loss of the shop effortlessly as opposed to experiencing each record that are
recorded in registers.
Conclusion
The documentation gives a rule to both the specialized users and non-specialized users to be familiar
to the developed software. The specialized users can think about the support of the software and non-
specialized users who don't have detail learning of the framework can play out the tasks incorporated
into the framework. Accordingly, it is basic to make a documentation after the productive
improvement of the software.
Bibliography
Rouse, M., 2016. searchsqlserve. [Online]
Available at: https://searchsqlserver.techtarget.com/definition/SQL
[Accessed 07 May 2018].