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