Download Database Design and Development for FPT Shop - Prof. Tran and more Exercises Data Communication Systems and Computer Networks in PDF only on Docsity!
ASSIGNMENT 1 FRONT SHEET
Qualification TEC Level 5 HND Diploma in Computing
Unit number and title Unit 04: Database Design & Development
Submission date Date Received 1st submission
Re-submission Date Date Received 2nd submission
Student Name Student ID
Class 1622 RE SP24 Assessor name
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 D
❒ Summative Feedback: ❒ Resubmission Feedback:
Grade: Assessor Signature: Date:
Signature & Date:
I. Statements of user and system requirements.
1. Overview about the problem.
As a data developer working at FPT Corporation, I was assigned the project of upgrading and developing the database system for FPT shop because the company is currently expanding its development and database system. Are you experiencing some difficulties in using and managing... During the meeting we decided to develop a new database so that: users can register using their phone number as ID and order or rate, comment on purchased equipment, store managers can take care of their stores, and management can view all data from all stores.
2. Requirements of the application.
1.2. Application requirements Reason: FPT Shop is expanding its scale due to the increase in the number of stores. FPT Shop is currently having difficulty managing the database from all stores nationwide. Requirements: The database must be easy to use, store, manage and update. The database must also be easily accessible. Objective: The database should include features such as Create/Read/Update/Delete (CRUD). Scope (Goal to achieve): Set up the system so that data can be stored in a certain structure with high consistency. Helps stores conveniently create, store, search and use data accurately and quickly. Role: Directors: can log in to view all product information, reviews, customers, managers, and employees to be able to grasp the situation as well as manage subordinates more easily. Can add managers, employees as well as delete. Manager: who can view information about customers, products, product quantities, prices, employees, can only add or remove necessary information such as employees and products. Customers: everyone buys products and experiences services, information is stored in the system, after purchasing, customers can leave a review for the product.
Staff: who advise customers to buy and choose products, creators who enter, delete, and add customer information and the products they buy into the system.
II. Design the relational database system.
1. Analyse the requirements.
Based on the system's requirements, I have designed a database with 4 user roles including: senior officer (director), manager, employee and user. In this database design, there are 9 tables with different contents to meet the assigned requirements. 9 tables include: Shop, Product, Oder, OderDetail, Director, Manager, Staff, Customer and Feedback table.
- Shop: Display all information about shop ( Name, Adress, Director, Manager )
- Product: Display all information about product include: ProductID, Product Name, Product Detail, Feed back,...
- Oder: Contains information about customer order requests at the store, order date, and delivery date.
- OderDetail: Display details of customer order requests at the store including, Product, quantity and price
- Director: Displays director information. The person with the highest position can access and view all data.
- Manager: Displays all manager information at the store.
- Staff: Displays all information of employees working at the store. These employees will be added by the manager.
- Customer: Displays information of customers who buy products from the store.
- FeedBack: Displays all customer reviews for the product in the store after purchase.
B. Explanation. My ERD diagram have 8 tables. The OrderDetail Table is a connection table between the Oder and Product tables. This table includes order information such as order code, product code, order quantity and total order price. My EDR have 3 types relasionship:
- One to one ( 1 – 1 )
- One to many ( 1 – M )
- Many to many ( M – M ) Relationships between entities: - Shop & Product: One shop have one to multiple product ( 1 – M ) - Manager & Shop: One shope have one manager ( 1 – 1 ) - Director & Manager: The director manages one or more managers in the store system ( 1 – M )
- Customer & Oder: 1 customer can place many orders but those orders can only be placed by 1 customer. A multiple-choice relationship because the relationship belongs to a single customer and a customer can place multiple orders at the same time( 1 - M ).
- Product & Oder: A product can have one or more Orders and an order can contains one or many Products. This relationship is implemented using “OrderDetail” Table as a connect table, which have foreign keys of both Product and Order table ( M – M ).
- Staff & Oder : 1 staff can manage multiple orders but those orders can only be managed by 1 staff. Because one staff can manage a certain product or products, and a product can be managed by staff (1 – M ).
- Manager & Product: Only one Manager is employed to operate a shop. Manager is freely managing every product in that shop, but only in the shop they have been sent to ( 1 – 1 ).
3. Data validation.
Table Attributes Data types Null PK,FK Format/Constrains/Domain value Shop ShopID INT Not Null PK ID of Shop ManagerID INT ID of manager ShopName VARCHAR(255) Name of shop ShopAddress VARCHAR(255) Address of shop Product ProductID INT Not Null PK ID of product ShopID INT ID of shop ProductName VARCHAR(255) Name of product ProductDetail VARCHAR(255) Detail of product ProductPrice DECIMAL(10,2) Price of product Staff StaffID INT Not Null PK ID of Staff ShopID INT FK ID of Shop StaffName VARCHAR(255) Name of staff StaffPhoneNo VARCHAR(255) 0xxxxxxxxx StaffAdress VARCHAR(255) Adress of staff Manager ManagerID INT PK ID of manager ManagerName VARCHAR(255) Name of manager ManagerPhoneNo VARCHAR(255) 0xxxxxxxxx
References
There are no sources in the current document.
