Database Design and Development PDF

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