ASSIGNMENT 1 FRONT SHEET Qualification TEC Level 5 HND Diploma in Computing Unit number and title Unit 04: Database Design & Development Submission date 25/02/2023 Date Received 1st submission 25/02/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 P1 M1 D1 Page 1 of 42 Summative Feedback: Resubmission Feedback: 2.2 Grade: Assessor Signature: Date: Signature & Date: Page 2 of 42 Table of Contents A. Statements of user and system requirements.
Overview about the Problem. Requirements of the application. Design the relational database system .1 Analyse the requirements .2 Database design with explanations .3 Review whether the database is normalized. First Normal Form (1NF).
Second Normal Form (2NF). Third Normal Form (3NF) .5 Wireframe of the application. Future development plans. 42 Page 4 of 42 Table of picture Picture 1: Information of Customer.
10 Picture 2: Information of Staff. 10 Picture 3: Information of Products. 10 Picture 4: Information of Orders. 11 Picture 5: Top 5 Customers spend the most in the year.
11 Picture 6: Top 5 Products purchased the most in the year. 11 Picture 7: Top 5 staffs sell the most in the year. 12 Picture 8: Entity-relationship diagram at the conceptual level. 17 Picture 9: Complete Entity Relationship Diagram.
18 Picture 10: Login interface. 31 Picture 11: Main layout. 32 Picture 12: Menu of View option. 33 Picture 13: Example of View Customer List Layout.
34 Picture 14: Menu of add options. 35 Picture 15: Example of Add new Staff Layout. 36 Picture 16: Menu of Update options. 37 Picture 17: Update Customer layout.
38 Picture 18: Menu of Statistic options. Introduction We live in a modern society where everything is modern and innovative. Every day, there is a lot of innovation, especially in technology. When it comes to technology, we must admit that it has permeated every aspect of our lives, from simple devices such as phones and computers to complex programming applications.
Currently, digital transformation technology is gaining popular in a variety of disciplines, particularly company management models. While offline stores are progressively becoming outdated, online shops are an excellent solution for developing this model. In this assignment, I will discuss relational database, specifically how I design it, detailed explanations of its functions and characteristics, and finally an evaluation of the functions and the design. In addition, I created 8 tables in my design to easily manager, and each table has its own function and information to create a manage system for FPT SHOP.
These tables will help managers enter client information or staffs, products such as address or id, and the program will also help managers determine which consumers purchase the most, which things sell best, and which employees are the best. Statements of user and system requirements 1. Overview about the Problem FPT Shop is now experiencing difficulty in processing the database from all of its locations around the country, which is increasing as the number of stores grows which is expanding due to the growth in the number of stores. FPT shop approached my firm and requested assistance.
I decided to create a new database where people may register using their phone number or email address as their ID and place an order. Each consumer will be assigned a caregiver, and each staff will report to their management. This will assist FPT Shop in managing all data ranging from staff to customers, as well as new or additional items to the organization. As a result, in order to create a new database, I need to understand the needs of consumers, users, and staff while signing into the application and ordering and reviewing items.
Customer information will also be kept safe and secure, staffs and manager, who will oversee everything to ensure that the application always fulfills the user's requirements. Requirements of the application a. Business process • Clients and employees can access the application by inputting login information in the login screen. After logging in, customers and employees may enter their information to implement options.
• Customers who wish to swap products and information must submit a request to management in order for the exchange customer and their information to be accepted. • Once a consumer puts an order, the staff will review the order, its progress, and the client's order notes. Managers and directors may also access all data on customers, staffs, or goods, as well as statistics on which customers purchase the most, which items sell the best, and which employees are the best. Business rules • Customer, staffs, managements, accountants and directors must use a phone number or email account to log in to the application.
Customers are unable to change their own information or orders. • Staff can view order advice for customers • The manager can edit the information of the order as well as the customer. If customers want to change. They will have logged in and place an order will receive an email with a request to amend the information.
Page 7 of 42 If the request is approved by the management, the client may modify the information or erase it and reinstall a new one. • The accountant can view the store's sales statistics so that they can be submitted to the management. • The board of directors will look at the statistics of the accounting reports, then apply the reward policy for employees as well as apply discount codes to customers. Requirements ➢ The database that is developed must fulfill fundamental criteria such as ease of access, storage, management, and updating.
The system must also assure the database's security. At the same time, they must satisfy all of the scenario's criteria. ➢ Functional requirements: • Customer: o Customers may order products. o After logging in, the customer may examine product information and provide it.
o Once the manager accepts that need, the customer can add information to the order product and delete that information. • Staff: o Employees login app and can access just product and order of client. o Employees can change order status. o Employees can view customer information.
• Manager: o Administrators can accept requests to alter client information. o A manager can manage many staffs and can keep information. o Managers have the ability to regulate product, add product, and delete product. o Managers can also edit or delete information of employees as well as customers.
• Product: o When a valuable product is brought to the store, it must have a distinct brand. Page 8 of 42 o Products will be classified into certain categories, with pricing that are competitive in both the domestic and international markets. ➢ Non-functional requirements: All objects will be able to log in using their FPT username and password in 20 minutes. The web can be accessed by up to 500 users at once.
It takes less than 3 seconds for the web to respond. User story • As a Customer, I want to buy quality products on the app and have it delivered to my door to save time. So that I don't have to wait in line and wait at the store. Everything on the app is very clear like name, price, quality.
• As a Staff, I want to see product information, customer information, orders. So I logged in to the app to get information for consulting and care customer • As a Manager of FPT shop, I want to edit the information’s products. So that I logged into the system to check. Everything on the app is very clear like product's name, brands, categories, total price.
• As an accountant, I want to see product information, the number of products in stock. So that I can control revenue of shop. • As an accountant, I want to see the store's sales statistics. So that I logged into the system to check from which to apply the reward policy for employees.
Objectives (Things intended to achieve) The database must provide adequate functions, such as viewing, adding, removing, and updating data, as well as allowing users to quickly and easily access it, and it must also be safe. Output The output to the system will be the data of information of products, staffs, and customers, etc. Page 9 of 42 Picture 1: Information of Customer Picture 2: Information of Staff Picture 3: Information of Products Page 10 of 42 Picture 4: Information of Orders Picture 5: Top 5 Customers spend the most in the year Picture 6: Top 5 Products purchased the most in the year Page 11 of 42 Picture 7: Top 5 staffs sell the most in the year C. Design the relational database system 2.1 Analyse the requirements • Management, staff, retailers, and customers all utilize the app for different reasons, but the unifying goal is to deliver things to customers.
This system protects client or order information to avoid theft and invasion of privacy, so customers may feel secure when shopping on the system. If the client wishes to change the information or order, the manager's approval is necessary. Managers can also see customer and order data. The consumer can place several things in an order and the staff will always update the order status for the buyer to trust.
• I utilize the Microsoft SQL Server database to plan a database based on the company's requirements. There are 8 tables in my database, separated parts into sales and productions, each containing different functions to suit the requirements of the FPT shop. Tables include: o About sales: ✓ Customers: Holds complete client information such as ID, name, address, phone number, and so on. ✓ Staffs: provides complete employee information such as ID, name, and address, manager.
✓ Orders: including order date, shipped date, order status ✓ OrderItems: includes information such as the number of goods and the customer's total purchase amount. Page 12 of 42 ✓ Discounts: includes time-limited discount codes. o About productions: ✓ Products: displays all info associated with the product. ✓ Categories table: used to display all product categories to assist FPT Shop in controlling and following consumers.
✓ Brands: contains brands associated with each FPT store goods.2 Database design with explanations a.