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 04/03/2023 Re-submission Date 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 M3 M4 M5 D2 D3 Page 1 of 86 Summative Feedback: Resubmission Feedback: 2.2 Grade: Assessor Signature: Date: Signature & Date: Page 2 of 86 Table of Contents A. Task 1: Develop the database system .1 Final Mock-up of the application .2 Queries to create database with results .3 Database maintenance features and system security.
Task 2: Produce queries .1 Queries to INSERT data with illustrations of final result .2 Queries to UPDATE data with illustrations of final result .3 Queries to DELETE data with illustrations of final result .4 Queries to SELECT data with illustrations of final result .5 Advanced queries: Stored procedures, triggers, functions .6 Evaluate the effectiveness of the database solution and improvements. Effectiveness of database solution. Task 3: Test the system. Test plan and test result.
Overall evaluation of testing part.2 Flowchart to show how the system works. Insert information flowchart. Update information flowchart. Delete information flowchart.
Produce technical and user documentation .4 Evaluate any future improvements to ensure the continued effectiveness of the database system. User interface and usability. Integration with other systems. 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. 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. 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 Figure 12: Add new Order Layout Page 21 of 86 Figure 13: Add New Product Layout Page 22 of 86 • When the user clicks the Update button, the following UI appears. Items related to Staff information, Product information, Order information, Customer information, to edit information by clicking on the option. Figure 14: Menu of Update options Page 23 of 86 • When the user hits Update Customer, the required information will be displayed.
After entering the necessary information, the user will click the Update button to complete the task. Cases like Staffs, Order, Product are similar. Figure 15: Update Customer layout Page 24 of 86 • This is the UI that shows when the user clicks the Stats button. The statistics and other actions of the system will be shown.
Users can choose View or Update for new updates. Figure 16: Menu of Statistic options Page 25 of 86 1.2 Queries to create database with results The query that builds the table will establish a database system based on the designs authorized by the CEO. The database is created as the first stage in constructing the system. The database is created as the first stage in constructing the system.
I use Create Table statement to create table of system Figure 17: Create Database of system I'll design two schemas, sales and production, to partition the system into two distinct levels. Figure 18: Create schemas of system Next to, as shown in the design, we must construct the data tables. Figure 19: Create Brands table Page 26 of 86 Figure 20: Create Categories table Figure 21: create Products table Page 27 of 86 Figure 22: Create Discounts table Figure 23: Create Customers table Page 28 of 86 Figure 24: Create Staffs table Figure 25: Create orders table Page 29 of 86 Figure 26: Create OrderItems table The primary and foreign key constraints have been appropriately named, along with the unique constraint. I use update/delete cascade to show the relationship between parent-child tables.
This mean that allow to delete or update rows in the child table corresponding to the deleted or updated row in the parent table.