Database Design & Development: ISMT, 1st Sem, Modern Tech Marketing

This report presents the design, development, and evaluation of a relational database system tailored for a modern tech marketing environment. It begins with an introduction to SQL and the use of SQL Server Management Studio (SSMS) for database design, including elements like schema, tables, columns, data types, keys, relationships, and diagrams. The report then details the creation of a comprehensive system design, encompassing user interface (UI) designs for login, dashboards, product forms, and employee/customer management, along with the application of data validation techniques such as data type, code, range, format, consistency, and uniqueness checks. Furthermore, it covers data normalization up to the third normal form (3NF) to ensure data integrity. The report evaluates the effectiveness of the design in relation to both user and system requirements, followed by the implementation of a fully functional relational database system, including constraints, joins, and database maintenance using SQL queries. Database security aspects like authentication, backups, and encryption are also addressed. The report concludes with the testing of the system against user and system requirements and provides technical and user documentation including flowcharts and user manuals for various system components. References and tools used are also provided.

INTERNATIONAL SCHOOL OF
MANAGEMENT & TECHNOLOGY
Database Design and Development
ASSIGNMENT COVER SHEET
STUDENT DETAILS
Student ID 2021HNDIT09 Reg No.
Family Name Dhital Given Name Achyut
Enrolment Year 2020 DEC Section A
Semester 1st Email [e-mail]
UNIT DETAILS
Unit Title Database Design &
Development Systems
Unit Code H/615/1622
Assessor Name Issued Date 03/08/2021
Assignment Title Modern tech marketing system
Assignment No 01 Submission Date 6/24/2021
Qualification BTEC HND IN COMPUTING Campus INTERNATIONAL SCHOOL
OF MANAGEMENT AND
TECHNOLOGY
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
1

Database Design and Development
STUDENT ASSESSMENT SUBMISSION AND DECLARATION
When submitting evidence for assessment, each student must sign a declaration confirming that
the work is their own.
Student Name Achyut Dhital Assessor Name Atut Gorkhali
Issue Date 3/8/2021 Submission Date 6/4/2021
Programme BTEC HND IN COMPUTING
Unit Name Database Design & Development Systems
Assignment Title Modern tech marketing system
Plagiarism
Plagiarism is a particular form of cheating. Plagiarism must be avoided at all costs and students
who break the rules, however innocently, may be penalized. It is your responsibility to ensure
that you understand correct referencing practices. As a university level student, you are expected
to use appropriate references throughout and keep carefully detailed notes of all your sources of
materials for material you have used in your work, including any material downloaded from the
Internet. Please consult the relevant unit lecturer or your course tutor if you need any further
advice.
Student Declaration
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
2
I certify that the assignment submission is entirely my own work and I fully understand
the consequences of plagiarism. I understand that making a false declaration is a form of
malpractice.
Student signature: Date: 6/4/2021
Pearson Education 2018
Higher Education Qualifications

Database Design and Development
Table of Contents
LO1 Use an appropriate design tool to design a relational database system for a substantial problem.....8
Introduction.................................................................................................................................................8
SQL:.............................................................................................................................................................8
Introduction:-..............................................................................................................................................8
SSMS (SQL Server Management studio)....................................................................................................10
SSMS Tools:-..............................................................................................................................................10
Elements of a Database.............................................................................................................................10
Database schema :....................................................................................................................................10
Table..........................................................................................................................................................11
Columns.....................................................................................................................................................12
Rows..........................................................................................................................................................12
Data Types.................................................................................................................................................12
Keys:-.........................................................................................................................................................13
Primary Keys..............................................................................................................................................13
Foreign Keys..............................................................................................................................................13
Relationships.............................................................................................................................................13
ER diagram................................................................................................................................................14
Schema Diagram........................................................................................................................................16
Data Dictionary..........................................................................................................................................18
System requirement:.................................................................................................................................24
User requirement......................................................................................................................................24
Conclusion.................................................................................................................................................25
Produce a comprehensive design for a fully functional system which includes interface and output
designs, data validations and data normalization......................................................................................26
Introduction...............................................................................................................................................26
Database Design:.......................................................................................................................................26
Integrated development environment:.....................................................................................................26
User Interface:...........................................................................................................................................27
Login Form:................................................................................................................................................28
Dashboard / Home controls:.....................................................................................................................29
Product form:............................................................................................................................................31
Employee:..................................................................................................................................................31
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
3

Database Design and Development
Customer:..................................................................................................................................................32
Ticket.........................................................................................................................................................33
Data validation:.........................................................................................................................................35
Types of Data Validation............................................................................................................................35
1. Data Type Check....................................................................................................................................35
2. Code Check............................................................................................................................................35
3. Range Check..........................................................................................................................................36
4. Format Check.........................................................................................................................................36
5. Consistency Check.................................................................................................................................36
6. Uniqueness Check..................................................................................................................................36
Data Normalization:...................................................................................................................................36
First Normal Form (1st NF)........................................................................................................................37
Second Normal Form (2nd NF)..................................................................................................................38
Third Normal Form (3rd NF)......................................................................................................................39
Conclusion:................................................................................................................................................42
Evaluate the effectiveness of the design in relation to user and system requirement..............................43
Introduction:..............................................................................................................................................43
Effectiveness of database design:..............................................................................................................43
Effectiveness of my designed interface which includes input and output designs....................................44
Effectiveness of my designed interface which includes data validation techniques..................................49
Effectiveness of my designed interface which includes normalization Database......................................50
Conclusion.................................................................................................................................................50
LO2 Develop a fully functional relational database system, based on an existing system design.............51
Introduction...............................................................................................................................................51
Database Constraints:...............................................................................................................................51
NOT NULL:.................................................................................................................................................51
UNIQUE:....................................................................................................................................................52
PRIMARY KEY:............................................................................................................................................52
FOREIGN KEY:............................................................................................................................................53
Relationship:..............................................................................................................................................53
INNER JOIN:...............................................................................................................................................55
LEFT JOIN:..................................................................................................................................................57
RIGHT JOIN:...............................................................................................................................................57
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
4

Database Design and Development
FULL JOIN:..................................................................................................................................................59
Database Design:.......................................................................................................................................60
Project:......................................................................................................................................................60
HR Employee:............................................................................................................................................61
Customer:..................................................................................................................................................61
Marketing Ticket :......................................................................................................................................61
Marketing ticket log:.................................................................................................................................61
Roles:.........................................................................................................................................................62
Marketing ticket Type:...............................................................................................................................63
Marketing ticket status type:.....................................................................................................................63
Marketing ticket priority type:...................................................................................................................64
User Interface:...........................................................................................................................................64
Login Form:................................................................................................................................................65
Dashboard / Home controls:.....................................................................................................................67
Product form:............................................................................................................................................69
Add Product Form:....................................................................................................................................71
Employee:..................................................................................................................................................73
Add Employee:..........................................................................................................................................74
Customer:..................................................................................................................................................76
Add Customer:...........................................................................................................................................77
Ticket:........................................................................................................................................................79
Add ticket:.................................................................................................................................................81
Database Maintenance:.............................................................................................................................83
SQL:...........................................................................................................................................................83
Introduction:-............................................................................................................................................83
SSMS(SQL Server Management studio).....................................................................................................84
Database design:.......................................................................................................................................84
Database query:........................................................................................................................................85
Project table:.............................................................................................................................................85
Customer table:.........................................................................................................................................86
Employee table:.........................................................................................................................................88
Ticket table:...............................................................................................................................................89
Database Security......................................................................................................................................90
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
5

Database Design and Development
Database security controls........................................................................................................................90
System hardening and monitoring............................................................................................................90
DBMS configuration...................................................................................................................................90
Application security...................................................................................................................................90
Access........................................................................................................................................................91
Database auditing......................................................................................................................................91
Authentication...........................................................................................................................................91
Backups.....................................................................................................................................................92
Encryption.................................................................................................................................................93
Relational Database:..................................................................................................................................94
Insert Data.................................................................................................................................................94
Update Data..............................................................................................................................................94
Delete Data................................................................................................................................................95
Select data.................................................................................................................................................96
Front End:..................................................................................................................................................96
Login Form:................................................................................................................................................97
Dashboard / Home controls:.....................................................................................................................97
Product form:............................................................................................................................................98
Employee:..................................................................................................................................................99
Customer:................................................................................................................................................100
Ticket:......................................................................................................................................................101
Conclusion:..............................................................................................................................................103
LO3 Test the system against user and system requirements...................................................................104
Introduction:............................................................................................................................................104
Database Testing:....................................................................................................................................104
Types of Database Testing................................................................................................................104
Software testing:.....................................................................................................................................105
Unit Testing........................................................................................................................................105
Integration Testing..................................................................................................................................107
Stress Testing...........................................................................................................................................108
Conclusion:..............................................................................................................................................109
LO4 Produce technical and user documentation.....................................................................................110
Introduction:............................................................................................................................................110
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
6

Database Design and Development
Used tools:...............................................................................................................................................110
SQL server management studio (SSMS):.................................................................................................110
Visual studio:...........................................................................................................................................111
Draw.io:...................................................................................................................................................111
Programming Technique:........................................................................................................................112
SQL:.........................................................................................................................................................112
C#:...........................................................................................................................................................113
User Documentation:..............................................................................................................................113
User manual:...........................................................................................................................................113
Login Form:..............................................................................................................................................114
Dashboard / Home controls:...................................................................................................................115
Product form:..........................................................................................................................................115
Employee:................................................................................................................................................117
Customer:................................................................................................................................................118
Ticket.......................................................................................................................................................120
Flowchart:................................................................................................................................................121
System working:......................................................................................................................................123
Conclusion:..............................................................................................................................................123
Reference links:.......................................................................................................................................124
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
7

Database Design and Development
LO1 Use an appropriate design tool to design a relational database system for a substantial
problem.
Introduction
As indicated by the inquiry, an element relationship graph, relationship outline, and information
word reference are approached to plan. Our undertaking ought to likewise contain in any event
four interrelated tables and should meet the prerequisite of the client and the framework. In a
basic, we can characterize a data set as a coordinated assortment of information that can be
handily overseen, got to, and refreshed. It upholds capacity and control. The most utilized data
set language is Structured Query Language(SQL). A portion of the information base projects are
MySql, Microsoft Access, Microsoft Excel, and so on .
SQL:
Introduction:-
SQL is an abbreviation for Structured Query Language and is a standard relational query
language(SQL has been normalized by both ANSI and ISO) utilized for connection with
databases.
SQL was created by IBM in 1970s and has its foundations in the social variable based math
characterized by Codd in 1972. SQL usefulness goes past the social polynomial math, permitting
to recover information, embed information, alter existing information and erase information
from/to a RDBMS. SQL highlights number-crunching administrators like division, increase,
deduction and expansion, and correlation administrators (=, >=, <=). SQL likewise characterizes
a few total capacities like MAX, MIN, AVG, COUNT, and SUM.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
8

Database Design and Development
SQL characterizes numerous category, which can be partitioned into a few classes. The principal
SQL category class is for watchwords utilized for information recovery like the SELECT
category. The subsequent classification is for the SQL watchwords utilized for information
control like the INSERT, UPDATE, and DELETE SQL category. The third class is the value-
based SQL watchwords classification, including category like COMMIT and ROLLBACK.
Another SQL watchword class is the SQL Data Definition Language classification highlighting
words like CREATE and DROP. One more classification of SQL catchphrases controls the
approval and authorization parts of RDBMS (GRANT and REVOKE watchwords).
Here is the image of SQL Management system(SSMS) given below:-
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
9

Database Design and Development
SSMS (SQL Server Management studio)
SQL Server Management Studio (SSMS) is a integrated environment to deal with a SQL Server
framework. It gives a UI and a gathering of devices with rich content editors that interface with
SQL Server.
SSMS Tools:-
SSMS provides tools to configure, oversee and administer instances of Microsoft SQL Server,
and it unites a scope of graphical and visual plan devices and rich content editors to improve on
working with SQL Server. SSMS combined features come from Enterprise Manager, Query
Analyzer and Analysis Manager, alongside features included for previous arrivals of SQL
Server. It upholds the vast majority of SQL Server's maintains a single, integrated environment
for SQL Server Database Engine management and authoring.
Elements of a Database
The database schema
Schema objects
Indexes
Tables
Fields and columns
Records and rows
Keys
Relationships.
Database schema :
A schema is quite simply a group of related objects in a database. Within a schema,
objects that are related have relationships to one another. There is one owner of a schema, who
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
10

Database Design and Development
has access to manipulate the structure of any object in the schema. A schema does not represent a
person, although the schema is associated with a user account that resides in the database.
The three models associated with a schema are as follows:
The conceptual model, also called the logical model, is the basic database model, which deals
with organizational structures that are used to define database structures such as tables and
constraints.
The internal model, also called the physical model, deals with the physical storage of the
database, as well as access to the data, such as through data storage in tables and the use of
indexes to expedite data access. The internal model separates the physical requirements of the
hardware and the operating system from the data model.
The external model, or application interface, deals with methods through which users may access
the schema, such as through the use of a data input form. The external model allows relationships
to be created between the user application and the data model. Figure 1 depicts a schema in a
relational database.
Table
A table is the primary unit of physical storage for data in a database. When a user accesses the
database, a table is usually referenced for the desired data. Multiple tables might comprise a
database, therefore a relationship might exist between tables. Because tables store data, a table
requires physical storage on the host computer for the database.
Four types of tables are commonly used:
Data tables store most of the data found in a database.
Join tables are tables used to create a relationship between two tables that would otherwise be
unrelated.
Subset tables contain a subset of data from a data table.
Validation tables, often referred to as code tables, are used to validate data entered into other
database tables.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
11

Database Design and Development
Tables are used to store the data that the user needs to access. Tables might also have constraints
attached to them, which control the data allowed to be entered into the table. An entity from the
business model is eventually converted into a database table.
Columns
A column, or field, is a specific category of information that exists in a table. A column is to a
table what an attribute is to an entity. In other words, when a business model is converted into a
database model, entities become tables and attributes become columns. A column represents one
related part of a table and is the smallest logical structure of storage in a database. Each column
in a table is assigned a data type. The assigned data type determines what type of values that can
populate a column. When visualizing a table, a column is a vertical structure in the table that
contains values for every row of data associated with a particular column.
Rows
A row of data is the collection of all the columns in a table associated with a single occurrence.
Simply speaking, a row of data is a single record in a table. For example, if there are 25,000
book titles with which a bookstore deals, there will be 25,000 records, or rows of data, in the
book titles table once the table is populated. The number of rows within the table will obviously
change as books’ titles are added and removed.
Data Types
A data type determines the type of data that can be stored in a database column.
Although many data types are available, three of the most commonly used data types are
Alphanumeric
Numeric
Date and time
Alphanumeric data types are used to store characters, numbers, special characters, or nearly any
combination. If a numeric value is stored in an alphanumeric field, the value is treated as a
character, not a number. In other words, you should not attempt to perform arithmetic functions
on numeric values stored in alphanumeric fields. Design techniques such as this will be
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
12

Database Design and Development
discussed in more detail throughout the book. Numeric data types are used to store only numeric
values. Date and time data types are used to store date and time values, which widely vary
depending on the relational database management system (RDBMS) being used.
Keys:-
The integrity of the information stored in a database is controlled by keys. A key is a column
value in a table that is used to either uniquely identify a row of data in a table, or establish a
relationship with another table. A key is normally correlated with one column in table, although
it might be associated with multiple columns. There are two types of keys: primary and foreign.
Primary Keys
A primary key is the combination of one or more column values in a table that make a row of
data unique within the table. Primary keys are typically used to join related tables. Even if a table
has no child table, a primary key can be used to disallow the entry of duplicate records into a
table. For example, an employee’s social security number is sometimes considered a primary key
candidate because all SSNs are unique.
Foreign Keys
A foreign key is the combination of one or more column values in a table that reference a
primary key in another table. Foreign keys are defined in child tables. A foreign key ensures that
a parent record has been created before a child record. Conversely, a foreign key also ensures
that the child record is deleted before the parent record.
Relationships
Most databases are divided into many tables, most of which are related to one another. In most
modern databases, such as the relational database, relationships are established through the use
of primary and foreign keys. The purpose of separating data into tables and establishing table
relationships is to reduce data redundancy. The process of reducing data redundancy in a
relational database is called normalization.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
13

Database Design and Development
Three types of table relationships that can be derived are as follows:
One-to-one–One record in a table is related to only one record in another table.
One-to-many–One record in a table can be related to many records in another table.
Many-to-many–One record in a table can be related to one or more records in another table, and
one or more records in the second table can be related to one or more records in the first table.
ER diagram
An Entity Relationship (ER) Diagram is a type of flowchart that illustrates how “entities” such as
people, objects or concepts relate to each other within a system. ER Diagrams are most often
used to design or debug relational databases in the fields of software engineering, business
information systems, education and research. Also known as ERDs or ER Models, they use a
defined set of symbols such as rectangles, diamonds, ovals and connecting lines to depict the
interconnectedness of entities, relationships and their attributes.
The elements of an ERD are:
Entities
Relationships
Attributes
Steps involved in creating an ERD include:
Identifying and defining the entities
Determining all interactions between the entities
Analyzing the nature of interactions/determining the cardinality of the relationships
Creating the ERD
The ER diagram of Moderntech Marketing system is given below:-
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
14

Database Design and Development
Fig: ER Diagram (Moderntech Marketing system)
Modern Tech Marketing system contains different features and varieties which is represented in
above ER-Diagram. Which contains 4 tables which includes Customer, Employee, Ticket, and
Roles. These tables have many relationships.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
15

Database Design and Development
Customer contains Address, Is Active, fax, Email, Customer Name and Customer Id.
Employee Contains Employee Id, Employee Name, Email, Username, password, Is Approved, Is
locked out and create date.
Roles contains Role Id, Role Title, Description and Is Active.
These tables details are used by joining or using to form another information like making ticket,
ticket log and reports. This will help in to get errorless database system.
ER- diagram are mainly helpful in making any kind of software. First of all we all need to know
about the application / software requirement then with that information we need to draw a ER
diagram and with the help of ER diagram we can make application easily.
Schema Diagram
A database schema is the skeleton structure that represents the logical view of the entire database
.It defines all the constraints that should be applied for data. In the schema diagram, all the tables
of the database are with unique columns and features like primary key, foreign key, null, not
null, etc. The relationship between tables can also be seen from the schema diagram. In short, a
schema diagram describes the entities and relationship between them. It is the most important
things while developing any applications. Purpose of schema diagram are as follows:
It describes relationship between many tables.
It identifies constraints in the system.
It identifies the different tables and fields of each table.
It supports formal language which is supported by database management system.
Use of schema diagram
It is used to identify the different tables and fields of each table.
It is used to define tables, relationships, views, indexes, etc.
It is used to define how data is organized.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
16

Database Design and Development
It is used as a container of objects.
Given below is screenshot of schema diagram which I have design for my application:
The diagram above is Schema diagram of databases required in Modern Tech marketing system
which contains different tables connected in applications directly or indirectly. Home, Project,
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
17

Database Design and Development
Customer, Employee, Ticket, Ticket Log, Ticket Type, Ticket Status Type and Roles are the
table and form contents.
Some has one to one, one to many and many to many relationship. Home controls to
project ,home controls to customer, home controls to employee, home controls to ticket, ticket to
project, ticket to customer, ticket to employee, employee to role, ticket to ticket log, employee to
ticket log, ticket log to ticket status type, ticket to ticket type. These above relations have one to
many relationship. This above schema helps to make modern tech marketing system easier.
Data Dictionary
Collection of descriptions of the data objects or items in a data model for the benefit of
programmers and others user who need to refer to them is called Data Dictionary. A data
dictionary contains metadata i.e. data about the database. The data dictionary is very important as
it contains information such as what is in the database, who is allowed to access it, where is the
database physically stored etc. The users of the database normally don't interact with the data
dictionary, it is only handled by the database administrators.
The data dictionary in general contains information about the following −
Names of all the database tables and their schemas.
Details about all the tables in the database, such as their owners, their security constraints, when
they were created etc.
Physical information about the tables such as where they are stored and how.
Table constraints such as primary key attributes, foreign key information etc.
Information about the database views that are visible.
Data dictionary from the database tables used for the Modern Tech marketing system are given
below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
18

Database Design and Development
Table Name Field Data Type Field Size
for display
Null/Not
null
Constraint Description
Id Int max Not null pk Id for product
Product Tittle Varchar 150 Not null - Product
Name
Descriptin nvarchar 1000 Not null - Product
Description
Field Type Field Size
for display
Nullable Constraint Description
Id int max Not null pk, Unique ID
for customer
Name nvarchar 150 Not null - Customer
Name
Customer phone nvarchar 20 Not null - Customer
Contact No
fax nvarchar 50 Not null - Customer
Fax no.
Email nvarchar 50 Not null - Customer
Email
Name
Admin
nvarchar 150 Not null - Customer
Admin Name
Email
Admin
nvarchar 50 Null - Email of
Customer
Admin
Phone
Admin
nvarchar 20 Null - Customer
Admin
Contact no.
Name IT nvarchar 150 Null - Customer IT
Name
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
19

Database Design and Development
Email IT nvarchar 50 Null - Customer IT
Email
Phone IT nvarchar 20 Null - Customer IT
Contact No
Address nvarchar 1000 Null - Customer
Address
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
20

Database Design and Development
Field Type Field Size
for display
Nullable Constraint Description
Id int max Not null pk, Unique IDfor
employee
FullName nvarchar 50 Not null - Name of
employee
Employee Contact nvarchar 20 Not null - Contact No of
Employee
Email nvarchar 50 Not null - Email of
employee
RoleID int max Not null fk Unique code
for role
Username nvarchar 150 Null - Username for
Employee to
login
Password nvarchar max Null - Password for
Employee to
login
Is Approved bit - Not null - Approval
Is lockedOut bit - Not null - -
CreateDate datetime2 7 Not null - Created date
and time
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
21

Database Design and Development
Field Type Field Size
for display
Nullable Constraint Description
Id int max Not null pk, Unique ID
for Ticket
Type Id int max Not null fk For joining
from id type
PriorityType
Id
int max Not null fk Unique code
for Ticket
priorityType
ID
Ticket Status Id int max Not null fk Unique code
for Ticket
status ID
Tittle nvarchar 120 Not null - Tittle of
Ticket
Description nvarchar 2000 Not null - Description
of ticket
ProjectId int max Not null fk Project
detail for
assigning
ticket
customerId int max Not null fk Customer
detail for
assigning
ticket
Assigned To
Id
int max Not null fk To whome
ticket is
being
assigned
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
22

Database Design and Development
AssignedDate Datetime2 7 Null - Ticket
assigned
date
AssignedTim
e
nvarchar 10 Null - Ticket
assigned
time
CreatedBy Id int max Not null fk Creator of
new ticket
creater nvarchar 100 Not null - Person who
assigned
new ticket
Updater nvarchar max Null - Person who
update about
ticket
Updated by Id int max Null fk Unique code
for updater
All the above information on data dictionary are from the database tables which are used in
making modern tech marketing system. It includes the information of tables such as table name,
attributes, nullable, constraints and its use in application.
The data inserted in the modern tech marketing system are stored in database tables. As they are
stored and backup which will be useful in near future if the application is crashed and all data are
lost.
The ER- diagram and schema diagram helps in making application easily. Firstly these two are
made then the process of application is started. This will make time efficient. It gives a visual
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
23

Database Design and Development
beginning for database design which is used to determine information system necessities for an
organization. The ER diagram of my application in which there was 9 entities with its entities
and relationship between the entities. After that, I have to make the schema diagram for making
the application proper. It defines all the constraints that should be applied for data. It is the most
important diagram for making the application. I have made the schema diagram of the
Moderntech Marketing System which contacts 9 tables while its attributes and relationship
between tables. Finally, I have shown the data dictionary of the Modern Tech Marketing System.
It is a collection of descriptions of the data objects or items in a data model for the benefit of
programmers and others users who need to refer to them. In the data dictionary. I have included
the table name, Field (Attributes), type, length, Nullable, Constraint and description of the
application. Thus, there are the three main things which must be made before developing any
types of application.
System requirement:
Following are the system requirement which should be clear before making system:
The foreign key should be used so that the employee can access different fields and ticket can be
created.
Primary key need to be unique.
Data validation should be there per requirement.
User requirement
Following are the user requirement:-
Information stored in database should be editable from system app easily.
According to system its necessity i.e. User Login, Product, Customer, Employee, Ticket should
be generated.
Only unique data should be given to enter.
If the data which we enter are incorrect type like using alphabet in integer type then error
message should be display.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
24

Database Design and Development
Conclusion
Firstly explanation about database and its components is done also I have explained about sql
and ssms with diagram . In the same way, I have described. ER diagram and schema diagram. I
have added the picture of the ER diagram and schema diagram of my application. Also I have
explained the data dictionary which the example of the data dictionary of my application. After
that I have justify ER diagram, schema diagram and data dictionary. Finally I have described the
user requirement and system requirement of my application
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
25

Database Design and Development
Produce a comprehensive design for a fully functional system which includes interface and
output designs, data validations and data normalization.
Introduction:
We are given to develop a fully functional system including input and output function for our
database . we have to show its output and functions including data validation and normalization.
Using c# and visual studio I have made the application according to the scenario given to us. It is
one of the easiest method to do this project.
Database Design:
Database design is defined as a collection of steps that help with designing, creating,
implementing, and maintaining a business’s data management systems. The main purpose of
designing a database is to produce physical and logical models of designs for the proposed
database system. If the database it properly designed then it helps to facilitate the management of
data and also generated valuable information whereas if the database is not designed properly
then it becomes difficult to find the errors or faults and the management of data cannot be done
properly. In my context there is the database modern tech marketing system database which
contains 9 tables made for the application.
Integrated development environment:
An Integrated Development Environment (IDE) is an application that facilitates application
development. In general, an IDE is a graphical user interface (GUI)-based workbench designed
to aid a developer in building software applications with an integrated environment combined
with all the required tools at hand.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
26

Database Design and Development
Most common features, such as debugging, version control and data structure browsing, help a
developer quickly execute actions without switching to other applications. Thus, it helps
maximize productivity by providing similar user interfaces (UI) for related components and
reduces the time taken to learn the language. An IDE supports single or multiple languages.
The uses of IDE are as follows:
An IDE allows the programmer to quickly navigate to a type without worrying about the project.
It also allows the programmer to easily navigate to other members by using them as hyperlinks.
It also allows programmers to automatically generate a code depending upon previous codes.
An IDE also organizes the various imports by the programmer and also adds some appropriate
imports.
It also gives warnings while the user is typing and some error occurs.
User Interface:
The user interface (UI) is the point of human-computer interaction and communication in a
device. This can include display screens, keyboards, a mouse and the appearance of a desktop. It
is also the way through which a user interacts with an application or a website. The growing
dependence of many businesses on web applications and mobile applications has led many
companies to place increased priority on UI in an effort to improve the user's overall experience.
Types of user interfaces are as follows:
The various types of user interfaces include:
Graphical user interface (GUI)
Command line interface (CLI)
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
27

Database Design and Development
Menu-driven user interface
Touch user interface
Voice user interface (VUI)
Form-based user interface
Natural language user interface
Examples of user interfaces are as follows:
Some examples of user interfaces include:
Computer mouse
Remote control
Virtual reality
ATMs
Speedometer
Here I have designed modern tech application which contains a login form, 1
HomeControls/dashboard , 4 data show forms, five data fill/Edit form 1 about form. Modern tech
marketing system is the name assigned according to the question.
Login Form:
Login form is the first form used in Modern tech Marketing system where user verification is
done and taken straight to the home control form i.e. Dashboard. There are different options in
the dashboard asked according to user need / user Role.
The image /screenshot of the Login form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
28

Database Design and Development
After entering username and password it first verifies it checking in the database. The password
entered is encrypted so the password entered will go pass through class cypher where encryption
is done and then if the password is correct then it checks the role id of the user accordance to the
username and password entered and the dashboard is shown.
Dashboard / Home controls:
Dashboard is a form where you can get different access at a same place. We can manage and
control the other form through dashboard. It contains many buttons through which we can go to
different forms . the dashboard snap is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
29

Database Design and Development
In modern tech marketing system we need to manage product , customer ,employee and ticket so
these buttons are added. With these buttons we can access different forms. There is exit button
kept in order to exit the application and logout button in order to log out the user and takes to the
login form.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
30

Database Design and Development
Product form:
After clicking the button product in the dashboard the panel in the dashboard shows a form of
product where we can add as well as edit product and delete it. It is very important to manage
product in this application. It shows the list of the product in application. The product form is
shown below:
Here data grid view show the data extracted from the sql. There is one button which is used in
adding the data. When we click in the data row header we can update or delete the project.
Employee:
After clicking the button Employee in the dashboard the panel in the dashboard shows a form of
Employee where we can add as well as edit Employee and delete it. It is very important to
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
31

Database Design and Development
manage Employee in this application. It shows the list of the Employee in application. The
Employee form is shown below:
Here we can manage employee Add, Update, delete the employee. We can view each and every
data of employee so password stored are encrypted. Only admin and root user are allowed to use
this feature. This helps in proper security and privacy of data.
Customer:
After clicking the button Customer in the dashboard the panel in the dashboard shows a form of
Customer where we can add as well as edit Customer and delete it. It is very important to
manage Customer in this application. It shows the list of the Customer in application. The
Customer form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
32

Database Design and Development
We can manage Customer Add, Update, delete the Customer. Each and every data of Customer
can be viewed. Information of customer and the product need by customers list are stored here.
In case there are problems it will make easier to contact the customer.
Ticket:
Marketing ticketing system is very essential in a company. For the saving of time and assigning
employee a task easily ticketing system is necessary. Here I have made a ticketing system where
user can assign and view the ticket. When there is error or task comes out then the admin or root
user can assign other employee. Employee can report the updates and working progress of the
task. Following is the ticket form snapshot of my project:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
33

Database Design and Development
Here above is the ticket form which is opened when button ticket is clicked and is editable when
data row header is clicked which helps to correct the errors or change the assigned person.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
34

Database Design and Development
Data validation:
Data validation refers to the process of ensuring the accuracy and quality of data. It is
implemented by building several checks into a system or report to ensure the logical consistency
of input and stored data.
In automated systems, data is entered with minimal or no human supervision. Therefore, it is
necessary to ensure that the data that enters the system is correct and meets the desired quality
standards. The data will be of little use if it is not entered properly and can create bigger
downstream reporting issues. Unstructured data, even if entered correctly, will incur related costs
for cleaning, transforming, and storage.
Types of Data Validation
There are many types of data validation. Most data validation procedures will perform one or
more of these checks to ensure that the data is correct before it is stored in the database.
Common types of data validation checks include:
1. Data Type Check
A data type check confirms that the data entered has the correct data type. For example, a field
might only accept numeric data. If this is the case, then any data containing other characters such
as letters or special symbols should be rejected by the system.
2. Code Check
A code check ensures that a field is selected from a valid list of values or follows certain
formatting rules. For example, it is easier to verify that a postal code is valid by checking it
against a list of valid codes.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
35

Database Design and Development
3. Range Check
A range check will verify whether input data falls within a predefined range. For example,
latitude and longitude are commonly used in geographic data. A latitude value should be
between -90 and 90, while a longitude value must be between -180 and 180. Any values out of
this range are invalid.
4. Format Check
Many data types follow a certain predefined format. A common use case is date columns that are
stored in a fixed format like “YYYY-MM-DD” or “DD-MM-YYYY.” A data validation
procedure that ensures dates are in the proper format helps maintain consistency across data and
through time.
5. Consistency Check
A consistency check is a type of logical check that confirms the data’s been entered in a logically
consistent way. An example is checking if the delivery date is after the shipping date for a
parcel.
6. Uniqueness Check
Some data like IDs or e-mail addresses are unique by nature. A database should likely have
unique entries on these fields. A uniqueness check ensures that an item is not entered multiple
times into a database.
Data Normalization:
Normalization is the process of organizing data in a database. This includes creating tables and
establishing relationships between those tables according to rules designed both to protect the
data and to make the database more flexible by eliminating redundancy and inconsistent
dependency.
Database normalization can essentially be defined as the practice of optimizing table structures.
Optimization is accomplished as a result of a thorough investigation of the various pieces of data
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
36

Database Design and Development
that will be stored within the database, in particular concentrating upon how this data is
interrelated.
Following are the examples of data normalization:
First Normal Form (1st NF)
In 1st NF
The table cells must be of a single value.
Eliminate repeating groups in individual tables.
Create a separate table for each set of related data.
Identify each set of related data with a primary key.
Definition:
An entity is in the first normal form if it contains no repeating groups. In relational terms, a table
is in the first normal form if it contains no repeating columns. Repeating columns make your
data less flexible, waste disk space, and makes it more difficult to search for data.
In 1NF relation, the order of tuples (rows) and attributes (columns) does not matter.
Following is the example:
Order Customer Contact Person Total
1 Amar Manish 134.23
2 Preeti Rohan 521.24
3 Amar Manish 1042.42
4 Amar Manish 928.53
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
37

Database Design and Development
The above relation satisfies the properties of relation and is said to be in first normal form (or
1NF). Conceptually it is convenient to have all the information in one relation since it is then
likely to be easier to query the database.
Second Normal Form (2nd NF)
In 2nd NF
Remove Partial Dependencies.
Functional Dependency: The value of one attribute in a table is determined entirely by the value
of another.
Partial Dependency: A type of functional dependency where an attribute is functionally
dependent on only part of the primary key (primary key must be a composite key).
Create a separate table with the functionally dependent data and the part of the key on which it
depends. The tables created at this step will usually contain descriptions of resources.
Definition: A relation is in 2NF if it is in 1NF and every non-key attribute is fully dependent on
each candidate key of the relation.
Following is the example:
The following relation is not in Second Normal Form:
Order Customer Contact Person Total
1 Amar Manish 134.23
2 Preeti Rohan 521.24
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
38

Database Design and Development
3 Amar Manish 1042.42
4 Amar Manish 928.53
In the table above, the order number serves as the primary key. Notice that the customer and total
amount are dependent upon the order number -- this data is specific to each order. However, the
contact person is dependent upon the customer. An alternative way to accomplish this would be
to create two tables:
Customer Contact Person
Amar Manish
Preeti Rohan
Order Customer Total
1 Amar 134.23
2 Preeti 521.24
3 Amar 1042.42
4 Amar 928.53
The creation of two separate tables eliminates the dependency problem. In the first table, contact
person is dependent upon the primary key -- customer name. The second table only includes the
information unique to each order. Someone interested in the contact person for each order could
obtain this information by performing a Join Operation.
Third Normal Form (3rd NF)
In 3rd NF
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
39

Database Design and Development
Remove transitive dependencies.
Transitive Dependency A type of functional dependency where an attribute is functionally
dependent on an attribute other than the primary key. Thus its value is only indirectly determined
by the primary key.
Create a separate table containing the attribute and the fields that are functionally dependent on
it. The tables created at this step will usually contain descriptions of either resources or agents.
Keep a copy of the key attribute in the original file.
A relation is in third normal form if it is in 2NF and every non-key attribute of the relation is
non-transitively dependent on each candidate key of the relation.
Following is the example:
Company City State ZIP
ABC Ltd. Kathmandu Bagmati 44600
XYZ Ltd. Chitwan Bagmati 44200
ASD Ltd. Butwal Lumbini 32914
The above table is not in the 3NF.
In this example, the city and state are dependent upon the ZIP code. To place this table in 3NF,
two separate tables would be created -- one containing the company name and ZIP code and the
other containing city, state, ZIP code pairings.
Company ZIP
ABC Ltd. 44600
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
40

Database Design and Development
XYZ Ltd. 44200
ASD Ltd. 32914
City State ZIP
Kathmandu Bagmati 44600
Chitwan Bagmati 44200
Butwal Lumbini 32914
This may seem overly complex for daily applications and indeed it may be. Database designers
should always keep in mind the tradeoffs between higher level normal forms and the resource
issues that complexity creates.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
41

Database Design and Development
Conclusion:
In this task, I have firstly explained about the database design. It is the process of organizing
data according to the database model. After that I have described the IDE with its uses. It is a
graphical user interface based workbench which is designed to help a programmer in developing
software with an integrated environment combined with all the required tools at hand. In the
same way I have explained the user interface. It is a medium from which a person interacts with
an application or websites. After that I have shown the entire user interface like login page,
dashboard, Project , customer, employee, ticket. Also I have explained about the data validation.
Finally, I have explained about the data normalization with its types and example. It is the
process in which data attributes within a data model are organized for increasing the cohesion of
entity types.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
42

Database Design and Development
Evaluate the effectiveness of the design in relation to user and system requirement.
Introduction:
According to the task we are given the evaluation of effectiveness of the design in relation to
user and system requirement. The effective evaluation requires that whatever the ideas I present
must be evidenced. The evidence should be presented in the form of examples or detailed
research work. Input and output designs are also needs to be evaluated as well as data validation
technique and normalization. User requirement is the most necessary thing developer should
know because programs are developed for users. So the programmer should go accordance to the
time and what users would love. This will help to increase the demand of the application in the
market.
Effectiveness of database design:
Modern tech marketing system is the application for modern tech company which is IT Company
in Nepal which deals with various software’s and applications and gives IT support. I was given
the task of developing the application which can give the authentication security, role based
authorization security, management of the employee, management of the product, create
marketing/ sales ticket as well as modification of the ticket and remark it.
For the completion of the application I have used varieties of tools such as visual studio, SSMS
and draw.io. Firstly for the easiness of application and for the user and system requirement
fulfilment I have started with making schema diagram, ER-diagram, data dictionary which will
help me in doing program without errors and confusion. There are 9 tables made in database
which is used in application to store the data and information of product, customer, employee,
ticket and ticket log. There are different tables as above name and for their assist there are other
tables such as roles which will help employee table in selecting roles, ticket type, ticket status
etc. There are many forms in this application. 1 login form, 1 Dashboard, 8 forms with view
form and add/edit form of employee, product, customer and ticket (2 forms each). This forms
and designs are made accordance to modern tech. Login security is high. If the user enters wrong
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
43

Database Design and Development
username or password then the system will not log you in. The password stored is decrypted and
needs to be encrypted so if anyone finds the database then also he/she cannot access the system
without encrypting. The data in the other employee, product, customer and ticket can be added,
updated, and deleted. Dashboard connects the entire application and can have access to all of
them. This will help user and make them easy to work. User requirement and system requirement
is now fulfilled.
Effectiveness of my designed interface which includes input and output designs
There are 9 tables made in database which is used in moderntech marketing system to store the
data and information of product, customer, employee, ticket and ticket log. There are different
tables as above name and for their assist there are other tables such as roles which will help
employee table in selecting roles, ticket type, ticket status etc. There are many forms in this
application. 1 login form, 1 Dashboard, 8 forms with view form and add/edit form of employee,
product, customer and ticket (2 forms each). Login and data entered and data saved as well as
deleted and updated is shown below of only one form for example.
Here is the example as well as snapshot of the application:
Here is the add function:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
44

Database Design and Development
The add button saves the data in the database and is showed in data grid view in database. The
data entered in project title, is active and product description is added successfully which is
shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
45

Database Design and Development
The above shown list last value is added and is saved successfully and now is the turn to update
after clicking in the row header it and is shown below :
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
46

Database Design and Development
The above shows the application updated data successfully which will help when we sometime
add error data. And grid view data is also updated and is shown below:
Now the function of delete remains and the data which we don’t need can be deleted. This will
help making application data cleaner. Here is the example of delete function from the application
which is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
47

Database Design and Development
Here the system has successfully deleted the data which is selected and data grid view does not
contain that data can be seen below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
48

Database Design and Development
Effectiveness of my designed interface which includes data validation techniques
Data validation refers to the process of ensuring the accuracy and quality of data. It is
implemented by building several checks into a system or report to ensure the logical consistency
of input and stored data. In MT-marketing system application, I have used data validation for
checking the validity of data which is included in application. Let’s take an example of the login
page of my application for explaining data validation more clearly. The snapshot of the login
page after entering the wrong password is given below:
Data validation is very much used in the username and password field. They should match in
order to get logged in. system security depends in this login so it is necessary to validate the data
and should give access to the correct information only.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
49

Database Design and Development
Effectiveness of my designed interface which includes normalization Database
Normalization is the process of organizing data in a database. This includes creating tables and
establishing relationships between those tables according to rules designed both to protect the
data and to make the database more flexible by eliminating redundancy and inconsistent
dependency. In the MT-marketing system application, I have developed different columns for
managing the different records. Due to this I have inserted the data according to the
normalization. It has helps to reduce the redundancy of data in my application. In the application,
if I insert again the same data then the message appears that “data is already exists”. Due to this,
we could be aware while inserting the data. The normalization is also used for making the data of
my application fast, accurate and efficient for producing the data as expected. It has also played a
vital role of effectiveness of my designed interface
Conclusion
In this task, I have firstly explained about the effectiveness of database design I have developed
considering the system requirement and user requirement. I have included the entire requirement
which was send by top management of MT-marketing system. After that I have explained about
the effectiveness of my designed interface which includes input and output designed. I have
included the example of project add, update and delete with diagram to make it clear. In the same
way, I have described about the effectiveness of my designed interface which includes data
validation and normalization. I have included the example of login module with diagram to make
it clear.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
50

Database Design and Development
LO2 Develop a fully functional relational database system, based on an existing system
design.
Introduction
In this task, we were given to implement all the tables by applying constraints like primary key,
foreign key, unique, not null,, etc. After that, we have to produce a evidence our proposed
database with respect to user interface, output and data validation. Also there must be querying
across multiple tables must be applied through the use of different types of joins like inner join,
left join, right join, etc.
Database Constraints:
Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a
table. The whole purpose of constraints is to maintain the data integrity during an
update/delete/insert into a table. In this tutorial we will learn several types of constraints that can
be created in RDBMS.
Following are the constraints with examples:
NOT NULL:
NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t
provide value for a particular column while inserting a record into a table, it takes NULL value
by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot
have NULL values.
Here is the example of not null:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
51

Database Design and Development
The above query is of table project which is used in MT-Marketing system. There are many not
nulls in this table in order to keep data saturated and filtered which will make proper data or
complete data.
UNIQUE:
UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has
a unique constraint, it means that particular column cannot have duplicate values in a table.
Here is the example of unique key:
The above query is of table Employee which is used in MT-Marketing system. There is a unique
key in contact because name can be same but phone number of employees cannot be same. This
will help in storing of proper data of employee.
PRIMARY KEY:
Primary key uniquely identifies each record in a table. It must have unique values and cannot
contain nulls. In the below example the ROLL_NO field is marked as primary key, that means
the ROLL_NO field cannot have duplicate and null values.
Here is the example of primary key:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
52

Database Design and Development
The above query is of table roles which is used in MT-Marketing system. There is a primary key
which will help in going through other tables.
FOREIGN KEY:
Foreign keys are the columns of a table that points to the primary key of another table. They act
as a cross-reference between tables.
Here is the example of foreign key:
The above is the foreign key used in MT-marketing system which will help in connecting
through different tables in order to share data.
Relationship:
In relational database design, a relationship is where two or more tables are connected together
because they contain related data. This enables users to run queries for related data across
multiple tables
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
53

Database Design and Development
ONE-TO-ONE
This type of relationship allows only one record on each side of the relationship. The primary
key relates to only one record (or none) in another table. For example, in a marriage, each spouse
has only one other spouse. This kind of relationship can be implemented in a single table and
therefore does not use a foreign key.
ONE-TO-MANY
A one-to-many relationship allows a single record in one table to be related to multiple records in
another table. Consider a business with a database that has Customers and Orders tables.
A single customer can purchase multiple orders, but a single order could not be linked to
multiple customers. Therefore the Orders table would contain a foreign key that matched the
primary key of the Customers table, while the Customers table would have no foreign key
pointing to the Orders table.
MANY-TO-MANY
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
54

Database Design and Development
This is a complex relationship in which many records in a table can link to many records in
another table. For example, our business probably needs Customers and Orders tables, and likely
also needs a Products table.
Again, the relationship between the Customers and Orders table is one-to-many, but consider the
relationship between the Orders and Products table. An order can contain multiple products, and
a product could be linked to multiple orders since several customers might submit an order that
contains some of the same products. This kind of relationship requires three tables at a minimum.
INNER JOIN:
The INNER JOIN keyword selects all rows from both the tables as long as the condition
satisfies. This keyword will create the result-set by combining all rows from both the tables
where the condition satisfies i.e value of the common field will be same.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
55

Database Design and Development
Syntax: select * from ACCOUNT inner join ACC_TRANSACTION on
ACCOUNT.ACCOUNT_ID = ACC_TRANSACTION.ACCOUNT_ID
output:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
56

Database Design and Development
LEFT JOIN:
This join returns all the rows of the table on the left side of the join and matching rows for the
table on the right side of join. The rows for which there is no matching row on right side, the
result-set will contain null. LEFT JOIN is also known as LEFT OUTER JOIN.
Syntax:
select * from Project left join MarketingTicketType on Project.ProjectId = TicketTypeId
Output:
RIGHT JOIN:
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right
side of the join and matching rows for the table on the left side of join. The rows for which there
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
57

Database Design and Development
is no matching row on left side, the result-set will contain null. RIGHT JOIN is also known as
RIGHT OUTER
Syntax: select * from ACCOUNT right join ACC_TRANSACTION on
ACCOUNT.ACCOUNT_ID = ACC_TRANSACTION.TXN_DATE
Output:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
58

Database Design and Development
FULL JOIN:
FULL JOIN creates the result-set by combining result of both LEFT JOIN and RIGHT JOIN.
The result-set will contain all the rows from both the tables. The rows for which there is no
matching, the result-set will contain NULL values.
Syntax: select * from ACCOUNT full join ACC_TRANSACTION on
ACCOUNT.ACCOUNT_ID = ACC_TRANSACTION.TXN_DATE
Output:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
59

Database Design and Development
Database Design:
Database design is defined as a collection of steps that help with designing, creating,
implementing, and maintaining a business’s data management systems. The main purpose of
designing a database is to produce physical and logical models of designs for the proposed
database system.
Here I have designed a database Marketing System with different tables in it. It contains tables:
Project, HREmployee, Customer, MarketingTicket, Marketingticketlog, Roles,
MarketingticketType, MarketingticketStatusType, MarketingticketPriorityType.
Project:
The above figure is of table Project from database marketing system. It is used in project field in
MT-Marketing system. It contains different field as necessary to the system. The data entered in
the system are saved updated and deleted in here from the system.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
60

Database Design and Development
HR Employee:
The above figure is of table HR Employee from database marketing system. It is used in
employee field in MT-Marketing system. It contains different field as necessary to the system.
The data entered in the system are saved updated and deleted in here from the system.
Customer:
The above figure is of table customer from database marketing system. It is used in Customer
field in MT-Marketing system. It contains different field as necessary to the system. The data
entered in the system are saved updated and deleted in here from the system.
Marketing Ticket :
The above figure is of table Marketing Ticket from database marketing system. It is used in
Marketing Ticket field in MT-Marketing system. It contains different field as necessary to the
system. The data entered in the system are saved updated and deleted in here from the system.
Marketing ticket log:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
61

Database Design and Development
The above figure is of table Marketing ticket log from database marketing system. It is used in
marketing ticket field in MT-Marketing system. It contains different field as necessary to the
Marketing ticket. It is used as in ticketing system.
Roles:
The above figure is of table Roles from database marketing system. It is used in employee field
in MT-Marketing system. It contains different field as necessary to the system. It helps to select
specific data in the role field of employee.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
62

Database Design and Development
Marketing ticket Type:
The above figure is of table marketing ticket type from database marketing system. It is used in
marketing ticket field in MT-Marketing system. It contains different field as necessary to the
system. It helps to select specific data in the ticket type field of marketing ticket.
Marketing ticket status type:
The above figure is of table marketing ticket status type from database marketing system. It is
used in marketing ticket field in MT-Marketing system. It contains different field as necessary to
the system. It helps to select specific data in the ticket status type field of marketing ticket.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
63

Database Design and Development
Marketing ticket priority type:
The above figure is of table ticket priority type from database marketing system. It is used in
marketing ticket field in MT-Marketing system. It contains different field as necessary to the
system. It helps to select specific data in the ticket priority type field of marketing ticket.
User Interface:
The user interface (UI) is the point of human-computer interaction and communication in a
device. This can include display screens, keyboards, a mouse and the appearance of a desktop. It
is also the way through which a user interacts with an application or a website. The growing
dependence of many businesses on web applications and mobile applications has led many
companies to place increased priority on UI in an effort to improve the user's overall experience.
Types of user interfaces are as follows:
The various types of user interfaces include:
• Graphical user interface (GUI)
• Command line interface (CLI)
• Menu-driven user interface
• Touch user interface
• Voice user interface (VUI)
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
64

Database Design and Development
• Form-based user interface
• Natural language user interface
Examples of user interfaces are as follows:
Some examples of user interfaces include:
• Computer mouse
• Remote control
• Virtual reality
• ATMs
• Speedometer
Here I have designed modern tech application which contains a login form, 1 HomeControls /
dashboard , 4 data show forms, five data fill/Edit form 1 about form.
This forms are for data management of the MT-marketing system. Data can be added updated
deleted and can be viewed as well.it will help to save time and efficiency is increased. I have
designed this with the help of visual studio. The user interface of the MT-marketing system is
given below:
Login Form:
Login form is the first form used in Modern tech Marketing system where user verification is
done and taken straight to the home control form i.e. Dashboard. There are different options in
the dashboard asked according to user need / user Role.
The image /screenshot of the Login form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
65

Database Design and Development
After entering username and password it first verifies it checking in the database. The password
entered is encrypted so the password entered will go pass through class cypher where encryption
is done and then if the password is correct then it checks the role id of the user accordance to the
username and password entered and the dashboard is shown.
Here is the source code of the above UI given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
66

Database Design and Development
Dashboard / Home controls:
Dashboard is a form where you can get different access at a same place. We can manage and
control the other form through dashboard. It contains many buttons through which we can go to
different forms. The dashboard snap is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
67

Database Design and Development
In modern tech marketing system we need to manage product, customer, employee and ticket so
these buttons are added. With these buttons we can access different forms. There is exit button
kept in order to exit the application and logout button in order to log out the user and takes to the
login form.
The source code of the home controls is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
68

Database Design and Development
Product form:
After clicking the button product in the dashboard the panel in the dashboard shows a form of
product where we can add as well as edit product and delete it. It is very important to manage
product in this application. It shows the list of the product in application. The product form is
shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
69

Database Design and Development
Here data grid view show the data extracted from the SQL There is one button which is used in
adding the data. When we click in the data row header we can update or delete the project.
The source code of product form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
70

Database Design and Development
Add Product Form:
This add product form is the form for inserting data / products. There are different fields in
which we can insert data about product and then save, update and delete data. The snapshot of
add product form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
71

Database Design and Development
The fields shown above takes the data entered by user and then passes it to the database through
queries. And can be edited from here after selecting the data. The Source code of the form is
given below:
Employee:
After clicking the button Employee in the
dashboard the panel in the dashboard shows a
form of Employee where we can add as well as
edit Employee and delete it. It is very important
to manage Employee in this application. It shows
the list of the Employee in application. The
Employee form is shown below:
Here we can manage employee Add, Update,
delete the employee. We can view each and every
data of employee so password stored are
encrypted. Only admin and root user are allowed
to use this feature. This helps in proper security
and privacy of data. The source code of the Employee is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
72

Database Design and Development
Add Employee:
This add Employee form is the form for inserting data /information of employee. There are
different fields in which we can insert data about employee and then save, update and delete
data. The snapshot of add employee form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
73

Database Design and Development
The fields shown above takes the data entered by user and then passes it to the database through
queries. And can be edited from here after selecting the data. The Source code of the form is
given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
74

Database Design and Development
Customer:
After clicking the button Customer in the dashboard the panel in the dashboard shows a form of
Customer where we can add as well as edit Customer and delete it. It is very important to
manage Customer in this application. It shows the list of the Customer in application. The
Customer form is shown below:
We can manage Customer Add, Update, delete the Customer. Each and every data of Customer
can be viewed. Information of customer and the product need by customers list are stored here.
In case there are problems it will make easier to contact the customer. The source code of this
form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
75

Database Design and Development
Add Customer:
This add customer form is the form for inserting data /information of customer. There are
different fields in which we can insert data about customer and then save, update and delete data.
The snapshot of add customer form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
76

Database Design and Development
The fields shown above takes the data entered by user and then passes it to the database through
queries. And can be edited from here after selecting the data. The Source code of the form is
given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
77

Database Design and Development
Ticket:
Marketing ticketing system is very essential in a company. For the saving of time and assigning
employee a task easily ticketing system is necessary. Here I have made a ticketing system where
user can assign and view the ticket. When there is error or task comes out then the admin or root
user can assign other employee. Employee can report the updates and working progress of the
task. Following is the ticket form snapshot of my project:
Here above is the ticket form which is opened when button ticket is clicked and is editable when
data row header is clicked which helps to correct the errors or change the assigned person. The
source code of the above form is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
78

Database Design and Development
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
79

Database Design and Development
Add ticket:
This add ticket form is the form for inserting data /information of ticket. There are different
fields in which we can insert data about ticket and then save, update and delete data. The
snapshot of add ticket form is given below:
The fields shown above takes the data entered by user and then passes it to the database through
queries. And can be edited from here after selecting the data. The Source code of the form is
given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
80

Database Design and Development
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
81

Database Design and Development
Database Maintenance:
Database maintenance helps to keep a database running smoothly. SSMS is an popular example
with which we can maintain and make databases. The maintenance of the database should be
done by professional or who has good knowledge about it because there is fear of data loss if
unprofessional do the work. Some can be done by anyone who has little knowledge about it.
SQL:
Introduction:-
SQL is an abbreviation for Structured Query Language and is a standard relational query
language(SQL has been normalized by both ANSI and ISO) utilized for connection with
databases.
SQL was created by IBM in 1970s and has its foundations in the social variable based math
characterized by Codd in 1972. SQL usefulness goes past the social polynomial math, permitting
to recover information, embed information, alter existing information and erase information
from/to a RDBMS. SQL highlights number-crunching administrators like division, increase,
deduction and expansion, and correlation administrators (=, >=, <=). SQL likewise characterizes
a few total capacities like MAX, MIN, AVG, COUNT, and SUM.
SQL characterizes numerous category, which can be partitioned into a few classes. The principal
SQL category class is for watchwords utilized for information recovery like the SELECT
category. The subsequent classification is for the SQL watchwords utilized for information
control like the INSERT, UPDATE, and DELETE SQL category. The third class is the value-
based SQL watchwords classification, including category like COMMIT and ROLLBACK.
Another SQL watchword class is the SQL Data Definition Language classification highlighting
words like CREATE and DROP. One more classification of SQL catchphrases controls the
approval and authorization parts of RDBMS (GRANT and REVOKE watchwords).
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
82

Database Design and Development
SSMS(SQL Server Management studio)
SQL Server Management Studio (SSMS) is a integrated environment to deal with a SQL Server
framework. It gives a UI and a gathering of devices with rich content editors that interface with
SQL Server. The snapshot of the SSMS is given below:
Database design:
Database design is defined as a collection of steps that help with designing, creating,
implementing, and maintaining a business’s data management systems. The main purpose of
designing a database is to produce physical and logical models of designs for the proposed
database system. If the database it properly designed then it helps to facilitate the management of
data and also generated valuable information whereas if the database is not designed properly
then it becomes difficult to find the errors or faults and the management of data cannot be done
properly. In my context there is the database modern tech marketing system database which
contains 9 tables made for the application.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
83

Database Design and Development
Database query:
A database query is a similar action that is most closely associated with some sort of CRUD
(create, read, update, delete) function. A database query is a request to access data from a
database to manipulate it or retrieve it.
This allows us to perform logic with the information we get in response to the query. There are
several different approaches to queries, from using query strings, to writing with a query
language, or using a QBE like GraphQL or REST.
The tables and the query used in MT-Marketing system is given below:
Project table:
Here is the query and the table of Project Table given below:
The above is the query with table and can be added, updated and deleted manually.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
84

Database Design and Development
Customer table:
Here is the query and the table of Customer Table given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
85

Database Design and Development
The above is the query with table and can be added, updated and deleted manually.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
86

Database Design and Development
Employee table:
Here is the query and the table of Employee Table given below:
The above is the query with table and can be added, updated and deleted manually.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
87

Database Design and Development
Ticket table:
Here is the query and the table of Ticket Table given below:
The above is the query with table and can be added, updated and deleted manually.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
88

Database Design and Development
Database Security
Database security encompasses a range of security controls designed to protect the Database
Management System (DBMS). The types of database security measures your business should use
include protecting the underlying infrastructure that houses the database such as the network and
servers), securely configuring the DBMS, and the access to the data itself. Here are the ways of
database security:
Database security controls
Database security encompasses multiple controls, including system hardening, access, DBMS
configuration, and security monitoring. These different security controls help to manage the
circumventing of security protocols.
System hardening and monitoring
The underlying architecture provides additional access to the DBMS. It is vital that all systems
are patched consistently, hardened using known security configuration standards, and monitored
for access, including insider threats.
DBMS configuration
It is critical that the DBMS be properly configured and hardened to take advantage of security
features and limit privileged access that may cause a misconfiguration of expected security
settings. Monitoring the DBMS configuration and ensuring proper change control processes
helps ensure that the configuration stays consistent.
Application security
Database and application security framework measures can help protect against common known
attacker exploits that can circumvent access controls, including SQL injection
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
89

Database Design and Development
Access
A primary outcome of database security is the effective limitation of access to your data. Access
controls authenticate legitimate users and applications, limiting what they can access in your
database. Access includes designing and granting appropriate user attributes and roles and
limiting administrative privileges.
Database auditing
Monitoring (or auditing) actions as part of a database security protocol delivers centralized
oversight of your database. Auditing helps to detect, deter, and reduce the overall impact of
unauthorized access to your DBMS.
Authentication
Database security measures include authentication, the process of verifying if a user’s credentials
match those stored in your database, and permitting only authenticated user access to your data,
networks, and database platform.
Here in my application the authentication of the user is done and the password is encrypted. It
helps the data and user security. The application will take the data entered in the login page and
then checks and verifies from the database and then it will direct you to the home if data entered
is matched. Following given is the authentication done by my application:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
90

Database Design and Development
After entering wrong password or username the message box shows “Username or password
incorrect”. This is how authentication is done in MT-marketing system.
Backups
A data backup, as part of your database security protocol, makes a copy of your data and stores it
on a separate system. This backup allows you to recover lost data that may result from hardware
failures, data corruption, theft, hacking, or natural disasters.
Backup of the database used in MT-marketing system is also done and is given below:
This backup of the database will help to prevent data loss in near future. If the data is lost or
crashed then we can restore the data. That’s why data backup is important.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
91

Database Design and Development
Encryption
Database security can include the secure management of encryption keys, protection of the
encryption system, management of a secure, off-site encryption backup, and access restriction
protocols.
Encryption is also done in MT-marketing system. It will help in system and password security.
Even if the database is hacked/stolen then no one can get the password until the encryption code
is known. And the encryption code is only known by the some reliable employee of the
company. Following is the encryption Cesar of MT-marketing system:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
92

Database Design and Development
Relational Database:
In relational databases, table will have relationship with each other. In other words, data have
relationship and due to those relationships, there are some rules we need to follow if we want our
database operate properly. Example, in one to many relationship, we must input data into the
tables that are on the one side first and alter remove data in the tables that are on the many side
first
Insert Data
After creating the tables now, We need to input the actual data into our database so that we can
manage the data later. In order to do so SQL provide us with INSERT INTO syntax. It is used to
insert new records into a table like shown below.
Update Data
After creating the tables and inserting data now, we need to update the actual data into our
database so that we can manage the data later. In order to do so .SQL provide us with Update
syntax. It is used to update records into a table like shown below.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
93

Database Design and Development
Delete Data
After creating the tables and inserting data now, we need to delete the data into our database so
that we can manage the data later. In order to do so .SQL provide us with delete syntax. It is used
to delete records into a table like shown below.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
94

Database Design and Development
Select data
Data can be selected accordance to the user. It can be used in search bar in the application. Time
can be saved using this method and we can get exact that data we need. The screenshot of the
select query and its result is given below:
Front End:
The front end of a website or application refers to the part that users see and interact with. That
said, front-end development refers to the creation and management of the front end of a website
or application.
This includes making sure the various coding is working and being actively presented to those
interacting with a webpage. For example, a front-end developer would work to ensure all of a
website's fonts, menus and buttons are in proper working order for users to interact with. This
means that while web design is focused on the look of a website, front-end development is how
these designs and "looks" are shown on the website itself. Here following are some front end of
MT-marketing system:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
95

Database Design and Development
Login Form:
Login form is the first form used in Modern tech Marketing system where user verification is
done and taken straight to the home control form i.e. Dashboard. There are different options in
the dashboard asked according to user need / user Role.
The image /screenshot of the Login form is given below:
After entering username and password it first verifies it checking in the database. The password
entered is encrypted so the password entered will go pass through class cypher where encryption
is done and then if the password is correct then it checks the role id of the user accordance to the
username and password entered and the dashboard is shown.
Dashboard / Home controls:
Dashboard is a form where you can get different access at a same place. We can manage and
control the other form through dashboard. It contains many buttons through which we can go to
different forms . the dashboard snap is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
96

Database Design and Development
In modern tech marketing system we need to manage product , customer ,employee and ticket so
these buttons are added. With these buttons we can access different forms. There is exit button
kept in order to exit the application and logout button in order to log out the user and takes to the
login form.
Product form:
After clicking the button product in the dashboard the panel in the dashboard shows a form of
product where we can add as well as edit product and delete it. It is very important to manage
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
97

Database Design and Development
product in this application. It shows the list of the product in application. The product form is
shown below:
Here data grid view show the data extracted from the sql. There is one button which is used in
adding the data. When we click in the data row header we can update or delete the project.
Employee:
After clicking the button Employee in the dashboard the panel in the dashboard shows a form of
Employee where we can add as well as edit Employee and delete it. It is very important to
manage Employee in this application. It shows the list of the Employee in application. The
Employee form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
98

Database Design and Development
Here we can manage employee Add, Update, delete the employee. We can view each and every
data of employee so password stored are encrypted. Only admin and root user are allowed to use
this feature. This helps in proper security and privacy of data.
Customer:
After clicking the button Customer in the dashboard the panel in the dashboard shows a form of
Customer where we can add as well as edit Customer and delete it. It is very important to
manage Customer in this application. It shows the list of the Customer in application. The
Customer form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
99

Database Design and Development
We can manage Customer Add, Update, delete the Customer. Each and every data of Customer
can be viewed. Information of customer and the product need by customers list are stored here.
In case there are problems it will make easier to contact the customer.
Ticket:
Marketing ticketing system is very essential in a company. For the saving of time and assigning
employee a task easily ticketing system is necessary. Here I have made a ticketing system where
user can assign and view the ticket. When there is error or task comes out then the admin or root
user can assign other employee. Employee can report the updates and working progress of the
task. Following is the ticket form snapshot of my project:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
100

Database Design and Development
Here above is the ticket form which is opened when button ticket is clicked and is editable when
data row header is clicked which helps to correct the errors or change the assigned person.
Effectiveness of database solution
Modern tech marketing system is the application for modern tech company which is IT Company
in Nepal which deals with various software’s and applications and gives IT support. I was given
the task of developing the application which can give the authentication security, role based
authorization security, management of the employee, management of the product, create
marketing/ sales ticket as well as modification of the ticket and remark it.
For the completion of the application I have used varieties of tools such as visual studio, SSMS
and draw.io. Firstly for the easiness of application and for the user and system requirement
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
101

Database Design and Development
fulfilment I have started with making schema diagram, ER-diagram, data dictionary which will
help me in doing program without errors and confusion. There are 9 tables made in database
which is used in application to store the data and information of product, customer, employee,
ticket and ticket log. There are different tables as above name and for their assist there are other
tables such as roles which will help employee table in selecting roles, ticket type, ticket status
etc. There are many forms in this application. 1 login form, 1 Dashboard, 8 forms with view
form and add/edit form of employee, product, customer and ticket (2 forms each). This forms
and designs are made accordance to modern tech. Login security is high. If the user enters wrong
username or password then the system will not log you in. The password stored is decrypted and
needs to be encrypted so if anyone finds the database then also he/she cannot access the system
without encrypting. The data in the other employee, product, customer and ticket can be added,
updated, and deleted. Dashboard connects the entire application and can have access to all of
them. This will help user and make them easy to work. User requirement and system requirement
is now fulfilled.
Conclusion:
Development of the database system with evidence of user interface, output and data validations,
and querying across multiple tables is done and shown above. Implementation of a query
language into the relational database system is also shown. Implementation of a fully functional
database system which includes system security and database maintenance is explained with
figure and necessary proofs. Meaningful data has been extracted through the use of query tools to
produce appropriate management information.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
102

Database Design and Development
LO3 Test the system against user and system requirements
Introduction:
Here we are given to test the application in this task with proper documentation and proof. We
need to do different tests in order know the errors remaining in the application. This will help the
developer In the future and testing system ensure the quality of the application. Here I have to do
the testing of the MT-marketing system with proper documentation and screenshot and is done
below:
Database Testing:
Database testing is the process of checking that everything is in order with regards to the
database. This is done under a controlled testing environment. The process is also known as
back-end testing or data testing.
Database testing can take different forms through the various tools on offer. However, the
overall results all need to remain the same. With so many different features, factors, and
processes that go into a database, testers need to understand the key concepts to this process. You
should always be sure to test the database properly when developing any form of software.
Types of Database Testing
Function and structure are what determines what type of testing has to be performed on a
database system. On that basis of these factors, database testing can be further divided into
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
103

Database Design and Development
Structural database testing – Components of the database that are not visible to users have to
be tested. For that, structural database testing can be used. Another benefit of this type of testing
is that it can be used for the validation of the database.
Functional testing – There are a lot of functionalities associated with the database from a user
perspective. White box and black box testing are the types of testing used for this purpose
Non-functional testing – Risk factor of the database, requirements, performance, etc. of the
database has to put to test and that’s the non-functional factor of any database.
Software testing:
Software testing are the different approaches and ways of ensuring that a software application in
particular is fully tested. Software testing include everything from unit testing individual
modules, integration testing an entire system to specialized forms of testing such as security and
performance.
Unit Testing
The Unit testing part of a testing methodology is the testing of individual software modules or
components that make up an application or system. These tests are usually written by the
developers of the module and in a test-driven-development methodology they are actually
written before the module is created as part of the specification. Each module function is tested
by a specific unit test fixture written in the same programming language as the module.
Selecting some column from project table (testing):
The evidence is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
104

Database Design and Development
Output Analysis
The columns which are selected from the
project table is shown in the table after
execution of query.
The test log which is done shows a positive
result. When I executed the query, the
columns which were selected from the project
tables were shown
Testing : Entering the primary key twice while inserting into project table.
Evidence:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
105

Database Design and Development
Output Analysis
When I entered the primary key twice and try
to save it then the message appear that
duplicate entry for primary key. The data was
not saved as expected.
The test log which is done shows a positive
result. When I entered the primary key twice
and try to save it then the message appear that
duplicate entry for primary key. The data was
not saved as expected
Test includes support for the management and execution of automated unit tests. With its library
of plugins and extensions for all of the major unit test frameworks Test allows the test manager
to ensure that there is full coverage of all program code and that all unit tests have passed.
Integration Testing
The Integration testing part of a testing methodology is the testing of the different
modules/components that have been successfully unit tested when integrated together to perform
specific tasks and activities (also known as scenario testing or End to End testing (E2E)). This
testing is usually done with a combination of automated functional tests and manual testing
depending on how easy it is to create automated tests for specific integrated components.
Testing: User login by entering username and password and directing to the dashboard:
After entering the username and password it will direct to dashboard:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
106

Database Design and Development
Output Analysis
When we type the correct email and password
of the user in login form then the dashboard is
opened.
The test log which is done shows a positive
result. As expected, the dashboard opens when
I have typed the correct email and password of
user in login form
Test includes support for storing, managing and coordinating integration tests across various
modules and components. In addition, specifically for E2E and scenario tests, you can assign
different test cases in a single test scenario to different testers so that they can work together on
an integrated test.
With its library of plugins and extensions for different automated functional testing tools
including automation platform, Test is the perfect solution for managing your integration testing.
Stress Testing
There are several different types of performance testing in most testing methodologies, for
example: performance testing is measuring how a system behaves under an increasing load (both
numbers of users and data volumes), load testing is verifying that the system can operate at the
required response times when subjected to its expected load, and stress testing is finding the
failure point(s) in the system when the tested load exceeds that which it can support.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
107

Database Design and Development
Testing: Checking the login form by entering wrong password:
Output Analysis
the dashboard does not open when I have typed
the wrong password of user in login form. It
has shown the message that “ Username or
password is incorrect”.
The test log which is done shows a positive
result. As expected, the dashboard does not
open when I have typed the wrong password of
user in login form. It has shown the message
that “sorry, the Userename or password is
incorrect”
SpiraTest includes support for storing, managing and coordinating your performance, load and
stress testing activities. With its library of plugins and extensions for different automated
performance testing tools including LoadRunner, JMeter and NeoLoad, SpiraTest is the perfect
solution for managing your performance testing.
Conclusion:
Above I have explained and shown the tests and results of the application and database. It is
necessary for the quality of application. it helps makes the application after doing different tests
and analyzing. Here above tests of the tables and databases is completed successfully and then
test of the application is completed successfully. Hence the testing of application and database is
completed successfully.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
108

Database Design and Development
LO4 Produce technical and user documentation
Introduction:
We are given to produce technical and user documentation in this task. With the user manual and
different related fields. Also we have to explain all the application which helped in making the
application. Technical documentation is a type of documentation which describes handling,
architecture and also functionality of a technical product under development whereas user
documentation is a type of documentation which gives a customer the information which they
needed for using the product. Different tools are used with different technique. Visual studio,
SSMS, draw.io are some tools used and c#, and SSMS are the technique used.
Used tools:
Following are the tools used in making MT-Marketing system
SQL server management studio (SSMS):
SQL Server Management Studio (SSMS) is a integrated environment to deal with a SQL Server
framework. It gives a UI and a gathering of devices with rich content editors that interface with
SQL Server. It is used in making databases which stores data of the application. The snapshot of
the SSMS is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
109

Database Design and Development
Visual studio:
Visual Studio, also known as Microsoft Visual Studio and VS, is an integrated development
environment for Microsoft Windows. It is a tool for writing computer programs, websites, web
apps, and web services. It includes a code editor, debugger, GUI design tool, and database
schema designer, and supports most major revision control systems. Here is the snapshot of
visual studio:
Draw.io:
Draw.io is proprietary software for making diagrams and charts. The software allows you to
choose from an automatic layout function, or create a custom layout. They have a large selection
of shapes and hundreds of visual elements to make your diagram or chart one-of-a-kind. The
drag-and-drop feature makes it simple to create a great looking diagram or chart.it compiles
online and can make different things containing different shapes. It is used in making different
flowchart, schema diagram, ER-diagram which is used in application.
The snapshot of draw.io is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
110

Database Design and Development
Programming Technique:
Here the programming techniques done in application is shown below:
SQL:
SQL stands for Structured Query Language. SQL is used to communicate with a database.
According to ANSI (American National Standards Institute), it is the standard language for
relational database management systems. SQL statements are used to perform tasks such as
update data on a database, or retrieve data from a database. Some common relational database
management systems that use SQL are: Oracle, Sybase, Microsoft SQL Server, Access, Ingres,
etc. Although most database systems use SQL, most of them also have their own additional
proprietary extensions that are usually only used on their system. However, the standard SQL
commands such as "Select", "Insert", "Update", "Delete", "Create", and "Drop" can be used to
accomplish almost everything that one needs to do with a database. This tutorial will provide you
with the instruction on the basics of each of these commands as well as allow you to put them to
practice using the SQL Interpreter.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
111

Database Design and Development
C#:
C# is a Windows programmer go-to language. It's the preferred language for any desktop or web
application that runs on a Windows computer. C# is a part of the ASP.NET framework, so you'll
hear it mentioned along with VB.NET. You have these two language options when you decide to
learn Windows platform development. If you prefer C-style language syntax, C# is the direction
you should go when learning Windows development.
Visual Studio is the main IDE for any Microsoft development. You can even work with SQL
statements within Visual Studio, although Management Studio is preferred.
User Documentation:
User documentation refers to the documentation for a product or service provided to the end
users. The user documentation is designed to assist end users to use the product or service. This
is often referred to as user assistance. The user documentation is a part of the overall product
delivered to the customer.
Traditionally user documentation was provided as a user guide, instruction manual or online
help. However, user documentation is increasingly being delivered online today. This has
enabled technical writers to be more imaginative in how they assist users.
User documentation is important because it provides a avenue for users to learn:
1. how to use your software
2. features of your software
3. tips and tricks of your software
4. how to resolve common problems with your software
Without user documentation, a user may not know how to do the above things.
User manual:
User manual are those documentation, collection of feature or any specific thing of application or
thing with its feature and procedure of using it. It is used while someone wants to understand the
function and procedure of any application it is made for.it should be easy and simple to
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
112

Database Design and Development
understand. Here I am going to make user of MT-Marketing system explaining different function
making easy to use. User manual of application and its function is shown below:
Login Form:
Login form is the first form used in Modern tech Marketing system where user verification is
done and taken straight to the home control form i.e. Dashboard. There are different options in
the dashboard asked according to user need / user Role.
The image /screenshot of the Login form is given below:
After entering username and password it first verifies it checking in the database. The password
entered is encrypted so the password entered will go pass through class cypher where encryption
is done and then if the password is correct then it checks the role id of the user accordance to the
username and password entered and the dashboard is shown. And if the username or password is
not correct then it will not take you to any page. You can create new account by signing up and
that needs to be verified first by admin user.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
113

Database Design and Development
Dashboard / Home controls:
Dashboard is a form where you can get different access at a same place. We can manage and
control the other form through dashboard. It contains many buttons through which we can go to
different forms. The dashboard snap is given below:
In modern tech marketing system we need to manage product, customer, employee and ticket so
these buttons are added. With these buttons we can access different forms. When we click in
customer button it will take to the customer form. There is exit button kept in order to exit the
application and logout button in order to log out the user and takes to the login form.
Product form:
After clicking the button product in the dashboard the panel in the dashboard shows a form of
product where we can add as well as edit product and delete it. It is very important to manage
product in this application. It shows the list of the product in application. The product form is
shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
114

Database Design and Development
Here data grid view show the data extracted from the sql. There is one button which is used in
adding the data. When we click in the data row header we can update or delete the project. The
button add new project which can be seen in the above form will direct the user to the add
project form from which we can add/insert data. The form is shown below:
Here we can insert data of project and store in database. As well as the button delete deletes the
data and update updates the data. Button clear clears the fields in the form and button back
cancels the process and closes the form to go to previous one.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
115

Database Design and Development
Employee:
After clicking the button Employee in the dashboard the panel in the dashboard shows a form of
Employee where we can add as well as edit Employee and delete it. It is very important to
manage Employee in this application. It shows the list of the Employee in application. The
Employee form is shown below:
Here we can manage employee Add, Update, delete the employee. We can view each and every
data of employee so password stored are encrypted. Only admin and root user are allowed to use
this feature. This helps in proper security and privacy of data. The button add employee which
can be seen in the above form will direct the user to the employee edit form from which we can
add/insert data. The form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
116

Database Design and Development
Here we can insert data of employee and store in database. As well as the button delete deletes
the data and button update updates the data. Button clear clears the fields in the form and button
back cancels the process and closes the form to go to previous one.
Customer:
After clicking the button Customer in the dashboard the panel in the dashboard shows a form of
Customer where we can add as well as edit Customer and delete it. It is very important to
manage Customer in this application. It shows the list of the Customer in application. The
Customer form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
117

Database Design and Development
We can manage Customer Add, Update, delete the Customer. Each and every data of Customer
can be viewed. Information of customer and the product need by customers list are stored here.
In case there are problems it will make easier to contact the customer. . The button add customer
which can be seen in the above form will direct the user to the customer edit form from which
we can add/insert data. The form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
118

Database Design and Development
Here we can insert data of customer and store in database. As well as the button delete deletes
the data and update updates the data. Button clear clears the fields in the form and button back
cancels the process and closes the form to go to previous one.
Ticket:
Marketing ticketing system is very essential in a company. For the saving of time and assigning
employee a task easily ticketing system is necessary. Here I have made a ticketing system where
user can assign and view the ticket. When there is error or task comes out then the admin or root
user can assign other employee. Employee can report the updates and working progress of the
task. Following is the ticket form snapshot of my project:
Here above is the ticket form which is opened when button ticket is clicked and is editable when
data row header is clicked which helps to correct the errors or change the assigned person. . The
button add /assign ticket which can be seen in the above form will direct the user to the add
ticket form from which we can add/insert data. The form is shown below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
119

Database Design and Development
Here we can create ticket and store in database. As well as the button delete deletes the data and
update updates the data. Button clear clears the fields in the form and button back cancels the
process and closes the form to go to previous one.
Flowchart:
A flowchart is a diagram that depicts a process, system or computer algorithm. They are widely
used in multiple fields to document, study, plan, improve and communicate often complex
processes in clear, easy-to-understand diagrams. Flowcharts, sometimes spelled as flow charts,
use rectangles, ovals, diamonds and potentially numerous other shapes to define the type of step,
along with connecting arrows to define flow and sequence. They can range from simple, hand-
drawn charts to comprehensive computer-drawn diagrams depicting multiple steps and routes.
The flowchart of MT-marketing system is given below:
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
120

Database Design and Development
The above is the flowchart of mt-marketing system and has shown its containing’s and how data
is accessed and where it is.
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
121

Database Design and Development
System working:
MT-marketing system is made by me following the tasks and functions provided by modern tech
and its condition. I have made the application like modern tech wanted like it has authentication
of user system security data store and managing their data. There is login form which
authenticates the user by taking username and password then it takes to the dashboard from
where many function can be accessed. There are product, employee, customer, ticket, exit, report
and logout button. The product, employee, Ticket and customer button will redirect to their add
form and data can be entered in the form and stored in database. The button exit helps you to quit
the application and sign out will take you to the login form.
Conclusion:
Here in this task I have made technical an d user documentation. User manual is also done which
helps in running program and assist the user. It is needed for the initial users who don’t know
anything about application. The application function are also mentioned. In this task the data
flow diagram / flowchart is made and shown which will help to understand the flow of the
application and in this way the procedure of application and its documentation ends here.`
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
122

Database Design and Development
Reference links:
 https://www.techopedia.com/definition/1179/data-manipulation-language-dml
 https://www.techopedia.com/definition/10283/data-valida
 https://searchsqlserver.techtarget.com/definition/database
 https://www.techopedia.com/definition/29388/database-backup
 https://www.tutorialcup.com/dbms/constraints.htm
 https://www.tutorialspoint.com/dbms/dbms_data_schemas.htm
 https://www.techopedia.com/definition/29841/database-security
 https://www.tutorialspoint.com/software_testing_dictionary/database_testing.htm
 https://www.tutorialspoint.com/database_testing/database_testing_types.htmDatabase
2018
 https://www.tutorialspoint.com/database_testing/database_testing_types.htm
 https://www.techopedia.com/definition/2435/internet-protocol-address-ip-address
 https://www.w3schools.com/sql/sql_intro.asp
 https://www.techopedia.com/definition/1213/join
 https://searchoracle.techtarget.com/definition/MySQL
 https://www.techopedia.com/definition/24438/relationship-databases
 https://www.techopedia.com/definition/16431/software-development
 https://www.techopedia.com/definition/4685/user-interface-ui
 https://www.techopedia.com/definition/15740/visual-studio-net
 https://www.lucidchart.com/pages/what-is-a-flowchart-tutorial
 https://www.visual-paradigm.com/tutorials/data-flow-diagram-dfd.jsp
 https://www.guru99.com/non-functional-testing.html
 https://tallyfy.com/uml-diagram/
 https://whatis.techtarget.com/definition/Data-Definition-Language-DDL
 https://searchmicroservices.techtarget.com/definition/data-dictionary
 https://searchsqlserver.techtarget.com/definition/normalization
 https://searchdatamanagement.techtarget.com/definition/entity-relationship-diagramERD
 https://searchwindowsserver.techtarget.com/definition/RODC-read-only-domain
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
123

Database Design and Development
 https://searchsoftwarequality.techtarget.com/definition/stress-testing
 https://searchsoftwarequality.techtarget.com/definition/unit-testing
 https://searchmicroservices.techtarget.com/definition/user-interface-UI
Achyut Dhital (HND/Frist Semester), Dec-Sec-A
124