Higher Nationals in Computing

Database Design and

Development
ASSIGNMENT
No.1

Learner’s name: PHAM NGUYEN QUYNH NHU

Assessor name: NGUYEN VAN SON
Class: ...............GCS1005A....................
Learner’s ID: .............GCS210018...............
Subject’s ID: .................1622................
Assignment due: Assignment submitted:
 ASSIGNMENT 1 FRONT SHEET

Qualification BTEC Level 5 HND Diploma in Computing

Unit number and

Unit 04: Database Design & Development
title

Date Received 1st

Submission date
submission

Date Received 2nd

Re-submission Date
submission

PHAM NGUYEN
Student Name Student ID GCS210018
QUYNH NHU

Class GCS1005A 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

❒ Summative Feedback: ❒ Resubmission Feedback:\

Grade: Assessor Signature: Date:

Lecturer Signature:

1|Page
 Assignment Brief 1 (RQF)

Higher National Certificate/Diploma in Computing

Student Name/ID Number: PHAM NGUYEN QUYNH NHU (GCS210018)

Unit Number and Title: Unit 04: Database Design & Development

Academic Year: 2022

Unit Assessor: Nguyen Van Son

Assignment Title: Database design

Issue Date: 12/10/2022

Submission Date:

Internal Verifier Name:

Date:

Submission Format:

Format:

● The submission is in the form of an individual written report. This should be written in a concise, formal
business style using single spacing and font size 12. You are required to make use of headings, paragraphs
and subsections as appropriate, and all work must be supported with research and referenced using the
Harvard referencing system. Please also provide a bibliography using the Harvard referencing system.

Submission

● Students are compulsory to submit the assignment in due date and in a way requested by the Tutor.
● The form of submission will be a soft copy posted on http://cms.greenwich.edu.vn/.
● Remember to convert the word file into PDF file before the submission on CMS.

Note:

● The individual Assignment must be your own work, and not copied by or from another student.
● If you use ideas, quotes or data (such as diagrams) from books, journals or other sources, you must
reference your sources, using the Harvard style.
● Make sure that you understand and follow the guidelines to avoid plagiarism. Failure to comply this
requirement will result in a failed assignment.

Unit Learning Outcomes:

LO1 Use an appropriate design tool to design a relational database system for a substantial problem

2|Page
Assignment Brief and Guidance:

You are employed as a Database Developer for a large IT consultancy company. The company has been
approached by FPT Shop which is expanding due to the growth of the number of stores. FPT Shop is currently
facing difficulties in dealing with managing the database from all shops on over country. It decided to develop a
new database so that: users can register with their phone numbers as IDs and order or rate, comment for
their bought devices, shop managers can take care for their stores and director board can view all data
from all shops.
You are tasked to select one of those systems to develop database for FPT Shop. Your tasks are to:
 Work with FPT Shop to find out about current requirements for each system
 Analyse the requirements and produce clear statements of user and system requirements.
 Design a relational database system using appropriate design tools and techniques
 Develop a fully functional relational database system, based on an existing system design.
 Test the system against user and system requirements.
 Produce technical and user documentation

Part 1 (Assignment 1)
Before you start the development process, your manager has asked you to produce a report for the CEO of FPT,
containing:
1. Clear statements of user and system requirements. The system must have at least 3 user roles, including
business processes and statistical reports for FPT Shop managers.
2. The design of the relational database system using appropriate design tools and techniques. It should
contain at least four interrelated tables.
You would prefer to produce a more detailed document, so you will produce a comprehensive design for a fully
functional system which will include interface and output designs, data validations and cover data normalization.
Your manager would like on the report your assessment of the effectiveness of the design in relation to user and
system requirements.

Learning Outcomes and Assessment Criteria (Assignment 1):

Learning Outcome Pass Merit Distinction
LO1 P1 Design a relational M1 Produce a D1 Assess the
database system using comprehensive design effectiveness of the
appropriate design tools for a fully functional design in relation to user
and techniques, system which includes and system
containing at least four interface and output requirements.
interrelated tables, with designs, data
clear statements of user validations and data
and system normalisation.
requirements.

Content
3|Page
 Assignment 1
I - Statements of user and system requirements
1 - Overview about the Problem
Information technology has grown tremendously in recent years. especially since both
developedanddeveloping countries have begun the process of digitizing all industries and sectors.
FPTRetail, theleading information technology company in Vietnam, developed the most profitable retail
chainin2012.With more than 5000 employees and more than 6,000 stores spread across 63 provinces in
Vietnam. Because it is difficult for the operator to manage all the data of all FPT transaction points.
Inaddition, theboard of directors is aware of the growing situation of databases and computer software, so
theywant tobe able to create a database with all information about the stores nationwide. own company
tobeabletounderstand their business situation. Due to understanding the above situation, I was hired
bytheexecutiveboard to build a database for their company. In the database, management wants to have
customer information, employee information, product information, billed information and all customer
reviewsabout the product. Furthermore, store managers wish they could manage all their businesses
withease. Management can access all data and store managers can manage their own locations. They
alsorequireadatabase that provides security requirements and quick login for all workers.
2 - Requirements of the application
We are required by the Board of Directors to build a database that will be used to process all
datafromstores around the country and assist shop management in better managing employees,
commodities, andcustomers. Basic needs for the database include quick access, accurate information
storage, andthegreatest security measures. Staff information and customer information should be the
database's twomostcrucial entries if the Board of Directors is to administer all the stores more effectively
than I believetheycan. Additionally, for the Board of Directors and the shop manager to be able to
improve customer service, product information, invoice information that has been exported, and customer
assessment information regarding the product are also crucial. It is preferable to draw in more clients.
II – Design the relational database system
1 - Analyze the requirements.
Sale management database has relations as following:
Login (StaffID, Password, No). It contains account information of database.
+ StaffID can help manager know which staff login to the system.
+ Password can help manage the security of database information.
+ No can help manager know how many people can enter the database.
Customer (CustomerID, CustomerName, CustomerPhone, CustomerAddress). It storestheinformation of
customers.
+ CustomerID can help managers find customer information easily.
+ CustomerName can help managers know the customer name.
+ CustomerPhone can help managers know the customer phone to contact easily.
+ CustomerAddress can help managers know the address to be able to deliver if the customer needs.
Staff (StaffID, StaffName, StaffPhone, StaffEmail, StaffAddress, StaffFunction). It storestheinformation
of staff.

4|Page
+ SatffID can help managers find staffs information easily.
+ StaffName can help managers know the staff name.
+ StaffPhone can help managers know the staff phone to contact easily.
+ SatffEmail can help managers know the staff email to contact easily.
+ StaffFunction can help managers know the function of each person in the store for easymanagement.
Product (ProductID, ProductName, Price, Quantity). It stores the information of product.
+ ProductID can help managers find products information easily.
+ ProcuctName can help manages know the product name.
+ Price can help managers know the product price.
+ Quantity can help managers know the amount of inventory in the warehouse.
Bill (BillID, StaffID, CustomerID, Date). It stores the information of bill.
+ BillID can help managers find bills information easily.
+ StaffID can help managers know which staff have sold that product to the customer.
+ CustomerID can help managers know which customer have bought that product.
+ Date can help managers know the date the customer has bought.
Bill Detail(BillID, ProductID, Quantity). It stores the information of bill deatail.
+ BillID can help the manager know the bill that customers have bought.
+ ProductID can help managers can manage the number of products in the warehouse.
+ Quantity can help managers know the number of customers bought.
Rate (CustomerID, ProductID, Evaluation). It stores the information of product evaluationofcustomers.
+ CustomerID can help managers know which customers have evaluated.
+ ProductID can help managers hnow which products are evaluated by customers.
+ Evaluation can help managers know the customer's evaluation points about the product.
2 - Database design with explanations.
a. Logical design

5|Page
 Figure 1: logical design
b. Physical design
Table: Login
Field Name Data Type Allow Nulls Description
SatffID varchar(6) x Staff code, primary key, foreign key
Password char(10) x Password of staff to login the database
No int x The number of staff
Table: Customer
Field Name Data Types Allow Nulls Description
CustomerID varchar(6) x Customer code, primary key
CustomerName varchar(50) Full name of customer
CustomerPhone varchar(10) x Phone number of customer
CustomerAddress varchar(50) x Address information of customer
Table: Staff
Field Name Data Types Allow Nulls Description
StaffID varchar(6) x Staff code, primary key
StaffName varchar(50) Full name of staff
StaffPhone varchar(10) Phone number of staff
StaffEmail varchar(50) x Email address of staff
StaffAddress varchar(50) Address information of staff

6|Page
 StaffFunction varchar(25) x Function of staff in the store
Table: Product
Field Name Data Types Allow Nulls Description
ProductID varchar(6) x Product code, primarykey
ProductName varchar(50) Name of product
Price float x Price of product
Quantity int x Quantity of product
Table: Bill
Field Name Data Types Allow Nulls Description
BillID varchar(6) x Bill code, primary key
StaffID varchar(6) x Staff code, foreign key
CustomerID varchar(6) x Customer code, foreign key
Date date Bill date
Table: BillDetail
Field Name Data Types Allow Nulls Description
BillID varchar(6) x Bill code, primary key, foreign key
ProductID varchar(6) x Product code, primary key, foreignkey
Quantity int x Quantity of bills
Table: Rate
Field Name Data Types Allow Nulls Description
CustomerID varchar(6) x Customer code, primarykey, foreign key
ProductID varchar(6) x Product code, primarykey, foreign key
Evaluation text x Evaluation of product
3 - Review whether the database is normalized
At the request of the Board of Directors, I provided a data sheet with four tables that
includedcustomerinformation, staff information, export invoice information, product information, and
customer evaluationinformation about that product. As a result, I discovered a number of its flaws, such
as the fact that thecustomer information board, employees, and items can be repeated when there is too
much informationloaded, making it impossible to update the database. Furthermore, data access is
complicated. As a result, I discovered a solution by standardizing data, which allows us to reduce the
chance of superfluousdata,maintain consistency, and enable staff to quickly update and access. Data may
be simply exported. Normalization is a database design process that begins with an examination of the
relationships between 9|Pagecharacteristics (also known as functional dependencies). A variety of tests
are run during normalizationtodetermine the optimum grouping for these attributes and, as a consequence,
a suitable set of relationstomeet the user's data demands. We can standardize data in three ways: the first
normal form(1NF), thesecond normal form (2NF), and the third normal form (3NF). There are no
repeated groups inthefirstnormal form since only single values are permitted at the intersection of each
rowand column. Removethe repeating group and construct two new relations to normalize a relation that
has a repeatinggroup. Forunique identification, the primary key of the new connection is a mix of the
primary key of theoriginalrelation and an attribute from the newly generated relation. The relation must

7|Page
first be in 1NFtobeinthesecond normal form. If and only if the primary key has a single attribute, the
relation is automaticallyin2NF. If the relation contains a composite primary key, each non-key
characteristic must be entirelyrelianton the entire primary key rather than a portion of the primary key
(i.e., there must be no partial dependency or augmentation). The connection must be in second normal
formto be in thirdnormal form.Furthermore, any transitive dependencies must be eliminated; a non-key
property cannot be functionallyreliant on another non-key attribute.
For example, not yet The First Normal Form (1NF) as follows:

The information of customers has been corresponded with the customer's ID, customer name,
customeremail, and customer address, as shown in the above table. Examples of The First Normal (1NF)
asfollows:

For example, not yet The Second Normal Form (2NF) as follows:

According to the above table, we can see that it has two main courses: StaffID and Function. At
therequest of the second normal form, the attributes must depend entirely on the main key as the
employee'sname, the employee's email and the employee's address regardless of the main key attribute.
Thefollowing is the correct example of The second normal form (2NF):

8|Page
For example, not yet The Third Normal Form (3NF) as follows:

According to the table above, we can see the two names of the product and the price does not
dependontwo main courses: the customer's ID and the product's ID. But they depend on the North's
bridgeproperties, if we look up the ID product we can know the name and price of the product. The
followingisthe correct example of The third normal form (3NF):

4 - Data validation
Before utilizing, importing, or otherwise processing data, it is necessary to validate it to
ensureitscorrectness and quality. Depending on the target restrictions or objectives, many methods of
validationmight be conducted. Validation is a type of data purification. To reduce project flaws, data must
bevalidated for quality, clarity, and specificity. Without verifying data, you risk making judgments
basedonfaulty data that is not precisely indicative of the scenario at hand. While it is critical to
evaluatedatainputs and values, it is also crucial to validate the data model itself. If the data model is not
properlydesigned or developed, you may encounter problems while attempting to use data files in
variousprograms and software.
Table: Login
Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
StaffID PK, FK1 Like ‘[FS][0-9][0-9][0-9][0-9]…’
Password Like ‘[A-Z][a-z][0-9]…’
No Like ‘[A-Z][0-9][0-9][0-9][0-9]…’
Table: Customer

9|Page
 Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
CustomerID PK Like ‘[A-Z][a-z][0-9][0-9]…’
CustomerName Like ‘[A-Z][a-z][a-z][a-z]…’
CustomerPhone Like ‘0[1-9][0-9][0-9][0-9]…’
CustomerAddress Like ‘[0-9][0-9][0-9][0-9][0-9][A-Za-za-za-
za-z][A-Za-za-za-za-z][A-Za-za-za-za-z][A-
Za-za-za-za-za-z][0-9]’
Table: Staff
Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
StaffID PK Like ‘[FS][0-9][0-9][0-9][2-9][0-9][0-9][0-9]’
StaffName Like ‘[A-Z][a-z][a-z][a-z]…’
StaffPhone Like ‘0[1-9][0-9][0-9][0-9]…’
StaffEmail Like ‘%@%.fpt.vn’
Table: Product
Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
ProductID PK Like ‘[A-Z][A-Z][0-9][0-9]…’
ProductName Like ‘[A-Z][a-z][a-z][a-z]…’
Price >0
Quantity >0
Table: Bill
Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
BillID PK Like ‘[B][0-9][0-9]…’
StaffID FK Like ‘[FS][0-9][0-9][0-9][2-9][0-9][0-9][0-9]’
CustomerID Fk Like ‘[A-Z][a-z][0-9][0-9]…’
Date < = getdate()
Table: BillDetail
Field Name Primary key (PK) or Data Validation
Foreign Key (FK)
BillID PK, FK1 Like ‘[B][0-9][0-9]…’
ProductID PK, FK2 Like ‘[A-Z][A-Z][0-9][0-9]…’
Quantity >0
Table: Rate
Field Name Primary key (PK) or Data Validation

10 | P a g e
 Foreign Key (FK)
CustomerID PK, FK1 Like ‘[A-Z][a-z][0-9][0-9]…’
ProductID PK, FK2 Like ‘[A-Z][A-Z][0-9][0-9]…’
Evaluation Like (‘Very Poor’, ‘Poor’, ‘Average’,‘Good’,
‘Excellent)
5 - Wireframe of the application
The system will have six main functions, such as a login system that prevents outsiders fromtryingtologin
to the system, the primary menu of the program so that users can choose the desired function,
adding,removing, store information of the data that the user wants to edit or view the store's information,
andthefunctions of adding, removing, hold data of the information the user wants to update or
perspectivethestore's information.

Figure 2: Login interface

11 | P a g e
 Figure 3: Main interface

Figure 4: Information of Staff

12 | P a g e
Figure 5: Information of Customer

Figure 6: Information of Product

13 | P a g e
Figure 7: Information of Rate

Figure 8: Information of Bill

14 | P a g e
Figure 9: Information of Bill’s detail

15 | P a g e
 References

1. Eng, N. and Watt, A. (2014) Chapter 12 Normalization, Database Design 2nd Edition. BCcampus.
Available at: https://opentextbc.ca/dbdesign01/chapter/chapter-12-normalization/ (Accessed:
October 14, 2022).
2. Simplilearn (2022) What is normalization in SQL with examples? 1NF, 2NF, 3NF and BCNF,
Simplilearn.com. Simplilearn. Available at: https://www.simplilearn.com/tutorials/sql-tutorial/what-
is-normalization-in-sql (Accessed: October 14, 2022).
3. What is data validation: Definition: Informatica India (no date) What is Data Validation: Definition |
Informatica India. Available at: https://www.informatica.com/in/services-and-training/glossary-of-
terms/data-validation-definition.html (Accessed: October 14, 2022).
4. FPT retail (2022) Wikipedia. Wikimedia Foundation. Available at:
https://vi.wikipedia.org/wiki/FPT_Retail (Accessed: October 14, 2022).
5. VỀ FPT (no date). Available at: https://fpt.com.vn/vi/ve-fpt (Accessed: October 14, 2022).

16 | P a g e