ASSIGNMENT 2 FRONT SHEET

Qualification TEC Level 5 HND Diploma in Computing

Unit number and title Unit 04: Database Design & Development

Submission date 07/03/2023 Date Received 1st submission

Re-submission Date Date Received 2nd submission

Student Name Hoang Trung Kien Student ID GCH211057

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 Kien

Grading grid

P2 P3 P4 P5 M2 M3 M4 M5 D2 D3
❒ Summative Feedback: ❒ Resubmission Feedback:

Grade: Assessor Signature: Date:

Signature & Date:
Contents
Chapter 1 - Develop the database system .................................................................................................................................................................. 4
1.1 Final Mock-up of the application ................................................................................................................................................................... 4
1.2 Queries to create database with results ........................................................................................................................................................ 14
1.3 Data in database system ............................................................................................................................................................................... 20
Chapter 2 – Produce queries ..................................................................................................................................................................................... 21
2.1 Queries to INSERT data with illustrations of final result .................................................................................................................................. 21
2.2 Queries to UPDATE data with illustrations of final result ................................................................................................................................ 22
2.3 Queries to DELETE data with illustrations of final result ................................................................................................................................. 23
2.4 Queries to SELECT data with illustrations of final result ................................................................................................................................. 24
2.5Advanced queries: Stored procedures, triggers, functions .................................................................................................................................. 24
2.6 Evaluate the effectiveness of the database solution ........................................................................................................................................... 27
Chapter 3 – Test the system ...................................................................................................................................................................................... 27
3.1 Test cases ........................................................................................................................................................................................................... 27
3.2 Flowchart to show how the system works ......................................................................................................................................................... 29
References .................................................................................................................................................................................................................... 35
Chapter 1 - Develop the database system
1.1 Final Mock-up of the application

Figure: Main interface for staff

Figure: Information of Staff
Figure: Create Read/Update/Delete information of Products
Figure: View information of Customers
Figure: Selling
Figure: Sale Detail
Figure: Ordering
Figure: Order Detail
Figure: View More
Figure: Check Sale ID
 Figure: Number of VIP customers by Addess

1.2 Queries to create database with results

 Based on the designs approved by the CEO, a database system will be generated by the query that creates the table. They will
be displayed with the results once run.
 Figure: Query to create database FPT and table Customer
 To build a database system, a database must be created in SQL Server, FPTShop is the database created and used to store this
library system. The tables will be created by the queries so that the field names, data types, data validation are designed.

Figure: Table Customer

Figure: Query to create table Staff, Product and Sale

Figure: Table Staff

Figure: Table Product

Figure: Table Sale

Figure: Query to create table Sale_Detail, OrderDaily, OrderDetail

Figure: Table Sale_Detail

Figure: Table OrderDaily

Figure: Table OrderDetail

Figure: Final diagram

1.3 Data in database system
 The system will import the existing data, because the new system is built and the amount of data is large, the data will be
imported through the built-in tool on the SQL Server system, so these input data will not be available. using query language.

Figure: Table Customer

Figure: Table Product

Figure: Table Staff

 Figure: Table OrderDaily

Figure: Table OrderDetail

Figure: Table Sale

Figure: Table Sale_Detail

Chapter 2 – Produce queries

2.1 Queries to INSERT data with illustrations of final result
 Users can manipulate operations such as insert, delete, update on the database. With Insert, user uses Insert into to add data to
the Product and Staff tables.
 Figure: Query to insert

Figure: Result at Product

Figure: Result at Staff

2.2 Queries to UPDATE data with illustrations of final result

 With update, the record in the Staff table with StaffID = ‘Sta05’ will be edited StaffLastName and the result.

Figure: Query to update

 Figure: Result at Staff

2.3 Queries to DELETE data with illustrations of final result

 A title is deleted at Book and result

Figure: Query to delete

Figure: Result at Staff

2.4 Queries to SELECT data with illustrations of final result
 Select History sale

Figure: Query to select history sale

Figure: Result history sale

2.5 Advanced queries: Stored procedures, triggers, functions

 A stored procedure is a prepared SQL code that you can save, so the code can be reused over and over again. So if you have an SQL
query that you write over and over again, save it as a stored procedure, and then just call it to execute it.

 Example: Create procedure CheckSale

 Figure: Query to create procedure CheckSale and result

 A trigger is a stored procedure in database which automatically invokes whenever a special event in the database
occurs. For example, a trigger can be invoked when a row is inserted into a specified table or when certain table
columns are being updated.
 Example: Create trigger Receving
 Figure: Query to create trigger Receving and result

 Functions calculate an aggregate value based on a group of rows and return multiple rows for each group.
 Example: Create Funciton VIPcustomer
 Figure: Query to create Funciton VIPcustomer and result

2.6 Evaluate the effectiveness of the database solution

 Based on the requirements of the online shop, important tables have been built that are Login to log in, distinguish between
Customer and Staff, Customer to build basic information of members such as name, ID, date birth, phone number; Staff
includes ID information, name of staff; basic information of the products and the mane of products, price, and quantity.
Regarding the loan slip, 3 tables are built to take on 3 different roles, Sales displays information of the sale, sale date,
SaleDetail records details of the type of products, Refunds records information when refund the product. The datavalidation is
also set up so that the information entered is correct, the information in the secondary table will be edited or deleted if the
information in the main table is edited or deleted. Essentially, these tables make the shop easy to operate. In addition, users can
add, edit, delete, read data. Next, statistical operations use select, a procedure that makes it easy for managers to control shop
activities. Trigger helps to save Customers information when updating or deleting records. A user-friendly mock-up that has
been built along with a detailed instruction on how it works is necessary for the users to fully understand the system's features
and usage. In short, the system has met most of the user's needs

Chapter 3 – Test the system

3.1 Test cases
 To see performance, find errors and fix them, a test plan is essential for the database.
Figure: Test Case
3.2 Flowchart to show how the system works
 The main interface of the staff is displayed with 6 options, click to select the corresponding function. With options 1,2,3 users
can search for IDs to update, delete, and insert information of objects. For option 1, the user can only view the information.
Format ProdID is nvarchar(5), 'Pro[0-9][0-9]. For example: Pro10

Figure: Option 1
Figure: Option 2

Figure: Option 3
Figure: Option 4.1

Figure: Option 4.2

Figure: Option 5.1

Figure: Option 5.2

Figure: Option 6

Figure: Option 6.2

Figure: Option 6.2
References
https://www.geeksforgeeks.org/sql-trigger-student-database/ (Accessed 6th March 2023)

https://www.w3schools.com/sql/sql_stored_procedures.asp (Accessed 6th March 2023)

https://www.sqlservertutorial.net/sql-server-window-functions/ (Accessed 6th March 2023)