BTEC FPT INTERNATIONAL COLLEGE

INFORMATION TECHNOLOGY
ASSIGNMENT 1
UNIT: Database Design

STUDENT : Nguyen Tuong Vi

CLASS : BDAF-2005-1

STUDENT ID : BDAF190016

SUPERVISOR : Ho Van Phi

DaNang, October 2020

 INSTRUCTOR/ SUPERVISOR/ ASSESSOR

Grading grid

P1 M1 D1

Assignment Feedback

Formative Feedback: Assessor to Student

Action Plan

Summative feedback

i
 ACKNOWLEDGMENTS
Today, the strong development of information technology makes computers im-
portant in all areas of the system. Most of the features of the computer are being fully ex-
ploited. Today computers are mainly used in programming, complex management, data
processing, commerce, science, ...

For the business, the application of information technology in sales management is

a very correct and effective solution to the increasing demand for shopping in the market.
Stemming from that fact, I chose the topic: "Building Tuong Vi clothing store management
system" as the subject to study this subject.

ii
 ASSURANCE

I certify that this assignment is my own work, based on my personal study and that I
have acknowledged all material and sources used in its preparation, whether they be books,
articles, reports, lecture notes, and any other kind of document, electronic or personal com-
munication. I also certify that this assignment has not previously been submitted for assess-
ment in any other unit, except where specific permission has been granted from all unit
coordinators involved, or at any other time in this unit, and that I have not copied in part or
whole or otherwise plagiarised the work of other persons.

Learners declaration

I certify that the work submitted for this assignment is my own and research
sources are fully acknowledged.

Student signature: Date:

iii
 TABLE OF CONTENT

INSTRUCTOR/ SUPERVISOR/ ASSESSOR ............................................................ i

ACKNOWLEDGMENTS ........................................................................................... ii

ASSURANCE ...........................................................................................................iii

TABLE OF CONTENT............................................................................................. iv

CHAPTER 1 DATABASE DESIGN ........................................................................ 1

1.1 Introduction to the problem. .......................................................................... 1

1.2 Disadvantage of current system ...................................................................... 1

1.3 Requirements of a new system ....................................................................... 1

1.4 SRS (System Requirement Specification) .................................................... 1

3.1.1 A detailed description of the functions in the system .............................. 1

3.1.2 Non-functional requirements for the system ........................................... 2

3.1.3 Software to build the system .................................................................. 2

CHAPTER 2 SYSTEM OVERVIEW ...................................................................... 3

2.1 Normalization .................................................................................................. 3

3.1 Logical design (ERD) ...................................................................................... 7

CHAPTER 3 PHYSICAL DESIGN ......................................................................... 9

3.1 Database Design .......................................................................................... 9

3.1.1 Table NHANVIEN ................................................................................... 9

3.1.2 Table KHACH .......................................................................................... 9

3.1.3 Table HOADON ...................................................................................... 9

3.1.4. Table HANG .......................................................................................... 10

3.1.5. Table CHITIETHOADON ....................................................................... 10

4.1 Create database ............................................................................................ 11

4.1.1 Created database ....................................................................................... 11

4.1.2 Table NHANVIEN ................................................................................... 11

4.1.3 Table KHACH ......................................................................................... 11

iv
 4.1.4 Table HOADON ...................................................................................... 12

4.1.5 Table HANG ........................................................................................... 12

4.1.6 Table CHITIETDONHANG...................................................................... 13

4.2 Diagram ......................................................................................................... 13

CONCLUSION ........................................................................................................ 15

REFERENCES ....................................................................................................... 16

v
 CHAPTER 1 DATABASE DESIGN
1.1 Introduction to the problem.

Nowadays, the application of information technology and computerization are consid-

ered as one of the very important factors to bring decisive in the operation of governments,
organizations or individuals to make a breakthrough. strong destruction.

Through the survey of the sales job at the Tuong Vi clothing store, the building of a
store management system is extremely important. The operation of a large-scale store will
be enhanced and expanded if a good management system is built. Derived from this rea-
son, I chose the topic: "Building a management system for Tuong Vi clothing store" to ana-
lyze and design the system.

1.2 Disadvantage of current system

- Currently, the store management is through books, so the management takes a lot of
time and manpower

- In each time statistical report is required, it takes a long time to do statistics

- Dropping documents often happens

- Some management jobs only use excel software.

1.3 Requirements of a new system

• The system requirements should be able to manage information such as:

- Customer management
- Manage items in the store
- Manage sales invoices
- Employee manager
- Statistic
1.4 SRS (System Requirement Specification)

3.1.1 A detailed description of the functions in the system

- Customer management function: This function allows the store manager to manage
information about customers who have purchased at the store. At the bottom of the store
owner can add customer information, correct and delete customer information and search
for customer information.
- Goods management function: This function allows the store manager to manage
information about purchased goods at the store. At the bottom of the store owner can add
goods information, correct and delete information about the goods and search for infor-
mation about the goods.

Perfomed Student: Nguyen Tuong Vi 1

 - Invoice management function: This function allows the store manager to add invoice
information, edit information, delete, search invoice information.
- Employee Management Function: This function allows to manage employee infor-
mation working in the store. Managers can add, edit, delete, and search for information of
employees working at the store
- Statistical Reporting: This function allows managers to view sales statistics reports
in stores.
3.1.2 Non-functional requirements for the system

This management system main functions such as:

Storing fully customer information, employees, receipts, details, products in the most
scientific way. Searching for information is also easy to make statistics, easy access with
just a few steps on the computer is possible. In addition, the security of the program is
relatively high to avoid intrusion by hackers. Fully meet the functions that users require
practically.
3.1.3 Software to build the system

- The database management system SQL Server runs on the Windows operating system

Perfomed Student: Nguyen Tuong Vi 2

 CHAPTER 2 SYSTEM OVERVIEW

2.1 Normalization

The clothing store management system at Tuong Vi clothing store needs to store the
following information:
- Items sold by many staff and under different sales invoices. In order to store product
information, it is necessary to store information such as: product code, product name, quan-
tity, unit of goods, input unit price,….
- When entering customer information, store information such as: customer code, customer
name, phone number, address.
- Storing employee information including information such as employee name, employee
code, gender, address ,. . .
- To store invoice information, information such as: invoice code, items in the invoice, quan-
tity, unit price, amount, date of sale, customer information, ...
After data analysis, the following information should be managed:

Mahoadon Makhachhang Tenkhachhang Dia- SDT Ngay

chiKhachhang

DT001 KH01 Tuong Vi Hanoi 0900989899 20-11-2020

Mahang Tenhang Donvi Manhanvien Tennhanvien Sodien

thoai
Nhanvien

SP01, SP02 Quan jean, kep Chiec, Day NV01 Pham Hong 0989888989
toc

Diachinhan Emailnhan Gia

vien vien

Dangnang abc@gmail.com 90.000,

80.000

1NF
It can be seen that the table above has many cells with multiple values (product code,
product name, identity,….). 1NF should not be satisfied. To ensure 1NF, the above table
should be restated as follows:

Perfomed Student: Nguyen Tuong Vi 3

 Mahoa Makhach Tenkhach Diachikhach SDT Ngay
don hang hang hang

DT001 KH01 Tuong Vi Hanoi 0900989899 20-11-2020

DT01 KH01 Tuong Vi Hanoi 0900989899 20-11-2020

Mahang Tenhang Donvi Manhanvien Tennhanvien Sodienthoainhan

vien

SP01 Quan jean, Chiec NV01 Pham Hong 0989888989

kep toc

SP02 Kep toc Day NV01 Pham Hong 0989888989

Diachi Email Soluong Gia

nhanvien nhanvien

Dangnang abc@gmail. 3 90.000

com

Dangnang abc@gmail. 2 80.000

com

After separating values in cells, the Product Code column is no longer the primary
key because the Invoice Code values overlap. Therefore, we need to redefine the primary
key for the table above. It can be seen that if the values of the invoice encoding column and
the product code are combined, all remaining values can be determined. Therefore, the
main keys are: Invoice Code and Product Code.
2NF
After seeing that the word 1NF contains the component keys, the invoice code and
the product code.
To ensure that the 2NF properties dependent on the member key need to be divided
into new tables.
Table 1 table 1 2NF

Mahang Mahoadon Soluong

Sp01 DT01 3

This table has 2 main keys: Ma hang and Ma hoa don

Table 1 table 2 2NF

Mahoa Makhachhang Tenkhach Diachikhach SDT Ngay

don hang hang

DT001 KH01 Tuong Vi Hanoi 0900989899 20-11-

2020

Perfomed Student: Nguyen Tuong Vi 4

 Manhan Tennhanvien Sodien Diachinhan Emailnhanvien
vien thoai
vien
nhan
vien

NV01 Pham Hong 0989888989 Dangnang abc@gmail.com

This table has 1 primary key is the Ma hoa don

Table 3 table 3 2NF

Mahang Tenhang Donvi Gia

SP01 Quan jean, kep toc Chiec 90.000

This table has 1 primary key is Ma hang

3NF
From table 1 2NF, we see that this table does not contain dependencies for
bridging functions. Meet 3NF standard.

Mahang Mahoadon So luong

Sp01 DT01 3

The main keys are Ma hang and Ma hoa don

The foreign keys are Ma hang and Ma hoa don
From table 3 2NF, we see that this table contains no dependencies for bridging func-
tions. Meet 3NF standard.

Mahang Tenhang Donvi Gia

SP01 Quan jean, kep Chiec 90.000

toc

The main key is Ma hang

From table 2 2NF, we see that this table contains the bridging function dependencies.
The bridging function dependencies are Ma Hoa Don -> Makhach hang -> ten khach hang,
dia chi, sdt -> Ma hoa don -> Ma nhan vien, dia chi, phone number, Gmail.
We get the following tables:

Mahoadon Makhachhang Manhanvien Ngay

Perfomed Student: Nguyen Tuong Vi 5

 DT001 KH01 NV01 20-11-2020

The main key is Ma hoa don

The foreign key is Ma khach hang, Ma nhan vien

Makhachhang Tenkhachhang DiachiKhachhang SDT

KH01 Tuong Vi Hanoi 0900989899

The main key is Ma khach hang

Manhan Tennhanvien Sodienthoain- DiachiNhanvien Emailnhanvien

vien hanvien

NV01 Phạm Hong 0989888989 Dangnang abc@gmail.com

The main key is Ma nhan vien

After normalizing 3NF we divide it into 5 tables as follows:

Table Nhan vien

Manhanvien Tennhanvien Gioitinh DiachiN- So- Ngaysinh

hanvien dienthoai

NV01 Cao Ngan Nu Danang 0900987878 25/07/1995

Table Hoa don

Mahoadon Manhanvien Ngayban Makhachhang

HD01 NV01 25/11/2020 KH01

Table chi tiet hoa don

Mahoadon Mahang Soluong

HD01 H01 1

Table Khach

Perfomed Student: Nguyen Tuong Vi 6

 Makhach Tenkhach Dia- Sodienthoai
chiKhachhang

KH01 Nhung Hanoi 0398676767

Table Hang

Mahang Tenhang Soluong DVT Gia

H01 Ao thun 1 Chiec 90.000

3.1 Logical design (ERD)

• Explanation of ERD:

When customers buy goods at the store, the store manager will ask for customer
information to store customer information in the system and create invoices for customers.
Since a customer can come to the store for more than one time, the relationship is 1-n.
Likewise, the salesperson at the store can invoice the customers for purchases many times,
so the relationship between the employee and the invoice is one many.

Perfomed Student: Nguyen Tuong Vi 7

 In order to sell at the store conveniently, the store needs to manage the items
in the store. When a customer makes a purchase, information about the goods purchased
in the store is required. In order to store information about the relationship between the
goods and the invoice, it is necessary to have a 1-commodity relationship that can have
many different orders, so this relationship is 1-n.

• The reason the ERD fits the system

According to the system design requirements are relational database design. but
according to this ERD diagram the tables are closely related. The system makes storing
information easy and convenient, providing full fields to store data in the most detailed way.

Perfomed Student: Nguyen Tuong Vi 8

 CHAPTER 3 PHYSICAL DESIGN

3.1 Database Design

The database includes tables to store different data information to manage. The data
is clearly managed for better data preservation.
Using the SQL Server database management system to manage the database. The
database includes 5 tables as follows: Nhanvien, Khachhang, Hang, Hoadon,Chtiethoadon
3.1.1 Table NHANVIEN

The table includes 6 columns which are Manhanvien, Tennhanvien, Gioitinh, Dia-
chiNhanvien, SdtNhanvien and ngaysinh

Table's primary key: Manhanvien

Constraint: Ngaysinh < getdate()

Name Data type Allow null Note

Manhanvien Nvarchar(10) NO PK

Tennhanvien Nvarchar(50) NO

Gioitinh Nvarchar(50) NO

DiachiNhanvien Nvarchar(50) NO

SdtNhanvien Nvarchar(20) NO

ngaysinh date NO Ngaysinh < getdate()

3.1.2 Table KHACH

Table's primary key: Makhach

The table includes 4 columns which are Makhach, Tenkhach, DiachiKhachhang, sdt

Name Data type Allow null Note

Makhach Nvarchar(10) NO PK

Tenkhach Nvarchar(50) YES

DiachiKhachhang Nvarchar(50) YES

sdt Nvarchar(20) YES

3.1.3 Table HOADON

The table's primary key: Mahoadon

The tables includes 4 colums which are MaHD, Manhanvien,Ngayban, Ma khach.
Constraint: Ngayban<getdate.
Perfomed Student: Nguyen Tuong Vi 9
 Name Data type Allow null Note

MaHD Nvarchar(10) NO PK

Manhanvien Nvarchar(10) YES

Ngayban Datetime YES Ngayban < getdate()

Makhach Nvarchar(10) YES

3.1.4. Table HANG

The table's primary key: Mahang

The table includes 7 columns which are Mahang, Tenhang, Soluong,Dvt, dongi-
anhap, Anh,ghichu
Constraint: Soluong > 0, Dongianhap > 0

Name Data type Allow null Note

Mahang Nvarchar(10) NO PK

Tenhang Nvarchar(50) YES

Soluong int YES Soluong > 0

Dvt Nvarchar(50) YES

dongianhap Float YES Dongianhap > 0

Anh Nvarchar(50) YES

ghichu Nvarchar(50) YES

3.1.5. Table CHITIETHOADON

The main key of the table: mahoadon, mahang

The table includes 6 columns which are MaHD, Mahang, Soluong, Dongia, Giamgia.
Constraint: Soluong >0, Dongia > 0, Giamgia > 0

Name Data type Allow null Note

MaHD Nvarchar(10) NO PK

Mahang Nvarchar(10) NO PK

Soluong int YES Soluong > 0

Dongia Float YES Dongia >0

Perfomed Student: Nguyen Tuong Vi 10

 Giamgia Float YES Giamgia >0

4.1 Create database

4.1.1 Created database

4.1.2 Table NHANVIEN

CREATE TABLE NHANVIEN(

Manhanvien nvarchar(10) Primary key,
Tennhanvien nvarchar(50) not null,
Gioitinh nvarchar(50) not null,
DiachiNhanvien nvarchar(50) not null,
SdtNhanvien nvarchar(20) not null,
ngaysinh date default getdate() not null)
GO

4.1.3 Table KHACH

CREATE TABLE Khach(

Makhach nvarchar(10) Primary key,
Tenkhach nvarchar(50) null,
Diachi nvarchar(50) null,
Sdt nvarchar(20) null)

Perfomed Student: Nguyen Tuong Vi 11

 GO

4.1.4 Table HOADON

CREATE TABLE HOADON(

MaHD nvarchar(10) Primary key,
Manhanvien nvarchar(10) null,
Ngayban datetime check( Ngayban < getdate()),
Makhach nvarchar(10) null)
GO

4.1.5 Table HANG

CREATE TABLE HANG(

Mahang nvarchar(10) Primary key,
Tenhang nvarchar(50) null,
Soluong int check(Soluong> 0) null,
Dvt nvarchar(50) null,
dongianhap float check(dongianhap> 0) null,
Anh nvarchar(50) null,
Ghichu nvarchar(50) null)
GO

Perfomed Student: Nguyen Tuong Vi 12

 4.1.6 Table CHITIETDONHANG

CREATE TABLE CHITIETHOADON(

MaHD nvarchar(10) Foreign key references
HOADON(MaHD) on delete cascade on update cascade,
Mahang nvarchar(10) Foreign key references
HANG(Mahang) on delete cascade on update cascade,
Constraint PKCHITIETHOADON Primary key(MaHD, Mahang),
Soluong int check( Soluong> 0) null,
Dongia float check( Dongia> 0) null,
Giamgia float check(Giamgia> 0) null)
GO

4.2 Diagram

Perfomed Student: Nguyen Tuong Vi 13

Perfomed Student: Nguyen Tuong Vi 14
 CONCLUSION

I learned and how to manage groups and build groups

I work with a team to build a more perfect compost; my team name is Group 4.
While working as a team, I contributed the following:

• Find out information about all sections of the article

• Assist members in completing their work

• Create team agenda

• Call on the team members to gather to complete work together

• Create a comfortable atmosphere for the group

- Details of my work, I think I did the following well

• Call on all members to participate in the debate

• I have tried to find information and assist all members in finding information.

Thanks for reading my assignment!

Perfomed Student: Nguyen Tuong Vi 15

 REFERENCES

Perfomed Student: Nguyen Tuong Vi 16