ASSIGNMENT 2 FRONT SHEET
Qualification

TEC Level 5 HND Diploma in Computing

Unit number and title

Unit 04: Database Design & Development

Submission date

04/03/2023

Date Received 1st submission

Re-submission Date

04/03/2023

Date Received 2nd submission

Student Name

Tran Duc Long

Student ID

GCH210562

Class

GCH1106

Assessor name

Dinh Duc Manh

Student declaration
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’s signature
Grading grid

P2

P3

P4

P5

M2

Page 1 of 86

M3

M4

M5


D2

D3


 Summative Feedback:

 Resubmission Feedback:

2.1

2.2

Grade:
Signature & Date:

Assessor Signature:

Date:

Page 2 of 86


Table of Contents
A. Introduction ........................................................................................................................................................................... 9
B.

C.


Task 1: Develop the database system ...................................................................................................................................... 9
1.1

Final Mock-up of the application ................................................................................................................................................................... 9

1.2

Queries to create database with results ...................................................................................................................................................... 26

1.3

Database maintenance features and system security ................................................................................................................................. 34

a.

Maintenance features .................................................................................................................................................................................. 35

b.

System security ............................................................................................................................................................................................ 35

Task 2: Produce queries ........................................................................................................................................................ 37
2.1 Queries to INSERT data with illustrations of final result .................................................................................................................................... 37
2.2 Queries to UPDATE data with illustrations of final result .................................................................................................................................. 42
2.3 Queries to DELETE data with illustrations of final result ................................................................................................................................... 42
2.4 Queries to SELECT data with illustrations of final result .................................................................................................................................... 44
2.5 Advanced queries: Stored procedures, triggers, functions ............................................................................................................................... 47
a. Stored Procedures ............................................................................................................................................................................................ 47
b. Triggers............................................................................................................................................................................................................. 49
DML triggers ......................................................................................................................................................................................................... 50

DDL triggers.......................................................................................................................................................................................................... 52
c. Functions .......................................................................................................................................................................................................... 53
2.6 Evaluate the effectiveness of the database solution and improvements ......................................................................................................... 54
a. Effectiveness of database solution .................................................................................................................................................................. 54
b.

Improvements .............................................................................................................................................................................................. 56
Page 3 of 86


D. Task 3: Test the system ......................................................................................................................................................... 57
3.1 Test cases ........................................................................................................................................................................................................... 57
a. Test plan and test result................................................................................................................................................................................... 57
b. Overall evaluation of testing part. ................................................................................................................................................................... 61
3.2 Flowchart to show how the system works ........................................................................................................................................................ 61
a. System flowchart.............................................................................................................................................................................................. 61
b. Insert information flowchart............................................................................................................................................................................ 63
c. Update information flowchart ......................................................................................................................................................................... 64
d. Delete information flowchart .......................................................................................................................................................................... 65
3.3. Produce technical and user documentation ..................................................................................................................................................... 66
3.3.1. Produce technical....................................................................................................................................................................................... 66
a. Introduction ..................................................................................................................................................................................................... 66
b. ERD ................................................................................................................................................................................................................... 66
3.3.2. User documentation .................................................................................................................................................................................. 68
3.4 Evaluate any future improvements to ensure the continued effectiveness of the database system............................................................... 77

E.

a.


Scalability ..................................................................................................................................................................................................... 77

b.

Security ........................................................................................................................................................................................................ 77

c.

Monitor performance .................................................................................................................................................................................. 78

d.

Data backup ................................................................................................................................................................................................. 80

e.

User interface and usability ......................................................................................................................................................................... 81

f.

Integration with other systems.................................................................................................................................................................... 81

Conclusion ............................................................................................................................................................................ 86
Page 4 of 86


Table of Figures
Figure 1: Login interface .............................................................................................................................................................................................. 10
Figure 2: Register interface .......................................................................................................................................................................................... 11
Figure 3: Main layout ................................................................................................................................................................................................... 12

Figure 4: Menu of view option ..................................................................................................................................................................................... 13
Figure 5: View Customer List Layout............................................................................................................................................................................ 14
Figure 6: View Staff List Layout .................................................................................................................................................................................... 15
Figure 7: View order list layout .................................................................................................................................................................................... 16
Figure 8: View Product List Layout............................................................................................................................................................................... 17
Figure 9: Menu of add options..................................................................................................................................................................................... 18
Figure 10: Add new Staff layout ................................................................................................................................................................................... 19
Figure 11: Add New Customer Layout ......................................................................................................................................................................... 20
Figure 12: Add new Order Layout ................................................................................................................................................................................ 21
Figure 13: Add New Product Layout ............................................................................................................................................................................ 22
Figure 14: Menu of Update options ............................................................................................................................................................................. 23
Figure 15: Update Customer layout ............................................................................................................................................................................. 24
Figure 16: Menu of Statistic options ............................................................................................................................................................................ 25
Figure 17: Create Database of system ......................................................................................................................................................................... 26
Figure 18: Create schemas of system .......................................................................................................................................................................... 26
Figure 19: Create Brands table .................................................................................................................................................................................... 26
Figure 20: Create Categories table .............................................................................................................................................................................. 27
Figure 21: create Products table .................................................................................................................................................................................. 27
Figure 22: Create Discounts table ................................................................................................................................................................................ 28
Figure 23: Create Customers table .............................................................................................................................................................................. 28
Figure 24: Create Staffs table....................................................................................................................................................................................... 29
Figure 25: Create orders table ..................................................................................................................................................................................... 29
Figure 26: Create OrderItems table ............................................................................................................................................................................. 30
Figure 27: Result of creating table ............................................................................................................................................................................... 31
Figure 28: Table Brands ............................................................................................................................................................................................... 31
Figure 29: Table Categories.......................................................................................................................................................................................... 31
Page 5 of 86


Figure 30: Table Products ............................................................................................................................................................................................ 31

Figure 31: Table Discounts ........................................................................................................................................................................................... 32
Figure 32: Table Customers ......................................................................................................................................................................................... 32
Figure 33: Table Staffs.................................................................................................................................................................................................. 33
Figure 34: Table Orders................................................................................................................................................................................................ 33
Figure 35: Table OrderItems ........................................................................................................................................................................................ 33
Figure 36: Final diagram............................................................................................................................................................................................... 34
Figure 37: Create user login of system and result ....................................................................................................................................................... 35
Figure 38: Queries to create roles Manager and Staff to access ................................................................................................................................. 36
Figure 39: Role access of Staff ..................................................................................................................................................................................... 36
Figure 40: Role access Manager................................................................................................................................................................................... 37
Figure 41: Queries to insert table Brands and result ................................................................................................................................................... 38
Figure 42: Queries to insert table Categories and result ............................................................................................................................................. 38
Figure 43: Queries to insert table Products and result ................................................................................................................................................ 39
Figure 44: Queries to insert table Customer and result .............................................................................................................................................. 39
Figure 45: Queries to insert table Discount and result ................................................................................................................................................ 40
Figure 46: Queries to insert table Staffs and result ..................................................................................................................................................... 40
Figure 47: Queries to insert table Orders and result ................................................................................................................................................... 41
Figure 48: Queries to insert table OrderItems and result............................................................................................................................................ 41
Figure 49: Data before update ..................................................................................................................................................................................... 42
Figure 50: Queries to update data ............................................................................................................................................................................... 42
Figure 51: Data after update ........................................................................................................................................................................................ 42
Figure 52: Data before delete ...................................................................................................................................................................................... 43
Figure 53: Queries to delete ........................................................................................................................................................................................ 43
Figure 54: Data after delete ......................................................................................................................................................................................... 43
Figure 55: Queries to staff's information and result.................................................................................................................................................... 44
Figure 56: Queries to Customer's information and result ........................................................................................................................................... 44
Figure 57: Queries to Order's information and result ................................................................................................................................................. 45
Figure 58: Queries to all Discount code and result ...................................................................................................................................................... 45
Figure 59: Queries to product's information and result .............................................................................................................................................. 46
Page 6 of 86



Figure 60: Queries to items of order and result .......................................................................................................................................................... 46
Figure 61: Queries top 5 the good staffs last year and result...................................................................................................................................... 47
Figure 62: Queries to top 5 most purchased customer last year and result ............................................................................................................... 48
Figure 63: Queries to top 5 most popular produtcs .................................................................................................................................................... 49
Figure 64: Create table to contain change customer's information ............................................................................................................................ 50
Figure 65: DML trigger to monitor insert, update delete actions of Customer ........................................................................................................... 51
Figure 66: Test and result of DML trigger .................................................................................................................................................................... 51
Figure 67: create table to contain change customer's information ............................................................................................................................ 52
Figure 68: create DDL trigger to display in ChangeLogs table ..................................................................................................................................... 52
Figure 69: Test and result of DDL trigger ..................................................................................................................................................................... 53
Figure 70: Create function fn_FindManageInfo to find the number of staffs each manager holds............................................................................ 54
Figure 71: Result function ............................................................................................................................................................................................ 54
Figure 72: Create index to improve system ................................................................................................................................................................. 56
Figure 73: Test index .................................................................................................................................................................................................... 57
Figure 74: Flowchart of system .................................................................................................................................................................................... 62
Figure 75: Flowchart add information in tables........................................................................................................................................................... 63
Figure 76: Flowchart update information in tables ..................................................................................................................................................... 64
Figure 77: Flowchart delete information in tables ...................................................................................................................................................... 65
Figure 78: ER diagram .................................................................................................................................................................................................. 67
Figure 79: User doc 01 ................................................................................................................................................................................................. 68
Figure 80: User doc 02 ................................................................................................................................................................................................. 69
Figure 81: User doc 03 ................................................................................................................................................................................................. 70
Figure 82: User doc 04 ................................................................................................................................................................................................. 71
Figure 83: User doc 05 ................................................................................................................................................................................................. 72
Figure 84: User doc 06 ................................................................................................................................................................................................. 73
Figure 85: User doc 07 ................................................................................................................................................................................................. 74
Figure 86: User doc 08 ................................................................................................................................................................................................. 75
Figure 87: User doc 08 ................................................................................................................................................................................................. 76

Figure 88: Use firewall to security ............................................................................................................................................................................... 77
Figure 89: SQL Server profiler ...................................................................................................................................................................................... 78
Page 7 of 86


Figure 90: Connect SQL Server Profiler ........................................................................................................................................................................ 79
Figure 91: Fill information trace................................................................................................................................................................................... 79
Figure 92: Event trace .................................................................................................................................................................................................. 80
Figure 93: Back up my data .......................................................................................................................................................................................... 80
Figure 94: Back up data of this system ........................................................................................................................................................................ 81
Figure 95: Connect this database with visual studio ................................................................................................................................................... 82
Figure 96: Connect data successfully ........................................................................................................................................................................... 83
Figure 97: Query data product by C# ........................................................................................................................................................................... 85
Figure 98: Result data of product in console application ............................................................................................................................................ 86

Page 8 of 86


A. Introduction
In this assignment, an online shop system database was implemented on SQL server using query language. This database
offers, permits changing, removing, and entering important information to manage the shop such as customer and staff
personal information, login, product information, and discount code. It is also equipped with data to assist administrators in
managing and monitoring the store's status. A comprehensive mock-up, as well as a relational database, has been
developed and exhibited here. Bugs are also fixed by testing. Lastly, a technical database was created to assist users in
utilizing this system based on the mock-up.

B. Task 1: Develop the database system
1.1

Final Mock-up of the application

In the first report, I thoroughly examined the FPT shop's users and features. A comprehensive blueprint of the
system was created based on the existing mock-up designs from the previous assignment. In order to design and
enhance the system, I created a user interface in this report that combines the system's functionalities. The FPT shop
system interfaces are as follows:
• The first is the system's login interface, which includes a login and sign-up button:

Page 9 of 86


Figure 1: Login interface
Page 10 of 86


•

The next is register interface:

Figure 2: Register interface
Page 11 of 86


•

The main interface of the system, including 4 main functions view, add, update, and statistics

Figure 3: Main layout
Page 12 of 86


•


When the user clicks the view button, the following UI appears. Items related to Staff information, Product
information, Order information, Customer information, and so on will be shown, with users able to inspect
information by clicking on the option.

Figure 4: Menu of view option
Page 13 of 86


•

When the user selects Customer List, the interface will appear like this.

Figure 5: View Customer List Layout
Page 14 of 86


•

When the user selects Staff List, the interface will appear like this.

Figure 6: View Staff List Layout
Page 15 of 86


•

The same is true of the Product List and Order List. By hitting the Back button on each interface, the user will be
able to return to the previous screen.


Figure 7: View order list layout
Page 16 of 86


Figure 8: View Product List Layout
Page 17 of 86


•

This is the UI that shows when the user clicks the Add button. Users can input relevant information by clicking on
options. Items that lead to more information about the Staff, Customers, products, and so on will be shown.

Figure 9: Menu of add options
Page 18 of 86


•

When the user hits Add Staff, the required information will be displayed. After entering the necessary
information, the user will click the Add button to complete the task.

Figure 10: Add new Staff layout
Page 19 of 86


•

Customer, Product, Order are all examples of information that may be added


Figure 11: Add New Customer Layout
Page 20 of 86