









Study with the several resources on Docsity
Earn points by helping other students or get them with a premium plan
Prepare for your exams
Study with the several resources on Docsity
Earn points to download
Earn points by helping other students or get them with a premium plan
Community
Ask the community for help and clear up your study doubts
Discover the best universities in your country according to Docsity users
Free resources
Download our free guides on studying techniques, anxiety management strategies, and thesis advice from Docsity tutors
A comprehensive case study on database design and development, focusing on a retail management system. It outlines the requirements, analyzes the information needs of various stakeholders, and proposes a normalized database schema with detailed explanations. The document also includes wireframe diagrams and data validation guidelines, providing a practical guide for students and professionals interested in database design.
Typology: Study notes
1 / 15
This page cannot be seen from the preview
Don't miss anything!
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 Nguyen Tung Anh Student ID GCH Class GCH110 6 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
Grade: Assessor Signature: Date: Signature & Date:
I. Statements of user and system requirements 1 Statements of user and system requirements The company approximated by FPT Shop is expanding its scale due to the increase in the number of stores. FPT Shop is currently facing. Difficulties in managing the database from all its stores quality. Therefore, to be able to help optimize the management, it is necessary to know the objects related to the business chain, the relevant units such as directors, general warehouse managers, store managers, etc. salespeople, customers, carriers, and suppliers. It is necessary to clearly define the user links in the system, and which components will have the right to check, update or delete the information of other components. 2 Requirements of the application As a CEO, I want the system to be able to list who my employees are, who are the leaders, who are the employees, they can help me manage the store easily. In addition, there are reports of regular employees, information such as date of birth, hire date, phone number and location. As a manager, I would like to add, edit and delete employee information, as well as customer information; The order is sold by which employee, and what does the order include. As an employee, I want the system to act the most is to import, export, and export customer data as well as order information, to be able to add, edit, and delete customer information, who sold to which customer, what does the sales order include. As a customer, I want to see what my order includes, who sold it to me, the name of the product; add, edit, delete your personal information such as full name, address and phone number. As a shipper, I would like to know the order information, to where it was delivered, the date the order was created and the date it was requested to be delivered; customer information such as address and phone number.
2 Database design with explanations Figure 1 - Information of Employees
Figure 2 - Information of Customers Figure 3 - Information of Orders
Figure 7 - Information of Categories Figure 8 - Information of Shippers Figure 9 - Information of Store
Figure 10 - ERD
And finally, the Orders table is also the most important when the Ids of the Employees, Customers, Shippers and OrderDetails tables will be listed here so that everyone who receives this order including the customer will know what these items are. any information. 3 Review whether the database is normalized Normalization is a database design method that begins with looking at associations between attributes. During normalization, several tests are performed to determine the best group for these qualities, and the result is a suitable set of relationships to meet the user's data needs. In the Employees The presence of a Foreign key of MgrId will cause the table to add a loop with itself, but this will not need to be split into an independent table because it will cause data redundancy. At Customers Primary key - > CusId CusId - > TitleOfCourtesy CusId - > FirstName CusId - > LastName CusId - > Address CusId - > City CusId - > Phone Shows that the attributes all depend on the primary key, the same goes for the remaining entities. Entities have been segregated to depend entirely on the primary key, using a foreign key EmpId to maintain data integrity and associated with entities. The above condition and the condition to achieve 1NF normalization, subjects have achieved 2NF normalization. At Orders
Primary key - > OrderId EmpId - > OrderId ShipId - > OrderId CusId - > OrderId OrderId - > OrderDate OrderId - > RequireDate OrderId - > ShippedDate OrderId - > ShipAddress It can be seen that the non-key data fields all depend on the primary key. The primary key must still depend on other foreign keys. Therefore, 3NF normalization has been achieved. 4 Data validation Id fields are of data type nchar(10) Name fields have data type nvarchar(20) if Vietnamese characters are inserted Date fields (date of birth, date of order,...) have type date the phone data field has the form nvarchar(10) similar to other data fields 5 WireFrame