Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

Database Design and Development: A Case Study for a Retail Management System, Study notes of Law

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

2021/2022

Uploaded on 12/12/2024

nguyen-tung-anh-tung-anh
nguyen-tung-anh-tung-anh 🇻🇳

2 documents

1 / 15

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
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
Nguyen Tung Anh
Student ID
GCH190513
Class
GCH1106
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
D1
pf3
pf4
pf5
pf8
pf9
pfa
pfd
pfe
pff

Partial preview of the text

Download Database Design and Development: A Case Study for a Retail Management System and more Study notes Law 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 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

P1 M1 D

 Summative Feedback:  Resubmission Feedback:

Grade: Assessor Signature: Date: Signature & Date:

  • I. Statements of user and system requirements
    • 1 Statements of user and system requirements
    • 2 Requirements of the application
  • II. Design the relational database system.
    • 1 Analyze the requirements.
    • 2 Database design with explanations
    • 3 Review whether the database is normalized
    • 4 Data validation
    • 5 WireFrame
  • Figure 1- Information of Employees Table Of Figures
  • Figure 2-Information of Customers
  • Figure 3-Information of Orders
  • Figure 4-Information of Order Details
  • Figure 5-Information of Products
  • Figure 6-Information of Suppliers
  • Figure 7-Information of Categories..............................................................................................................................................................................
  • Figure 8-Information of Shippers
  • Figure 9-Information of Store
  • Figure 10-ERD...............................................................................................................................................................................................................
  • Figure 11-WireFrame

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.

- CATEGORIES

- ORDER DETAILS

- SHIPPERS

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