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 Hoang Hai Long Student ID GCD191311

Class GCD0807A Assessor name Ho Van Phi

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:
2.1

Grade: Assessor Signature: Date:

Signature & Date:
ASSIGNMENT
Table of Contents
I INTRODUCT........................................................................................................................................................................................ 6
II Design a relational database system using appropriate design tools and techniques, containing at least four interrelated tables, with
clear statements of user and system requirements................................................................................................................................ 7
1 Scenario of the chosen system........................................................................................................................................................ 7
2 Practical implementation process.................................................................................................................................................... 7
3 advantages and disadvantages in the implementation process.......................................................................................................7
4 This database must have the following functions............................................................................................................................. 8
6 SRS (System Requirement Specification)....................................................................................................................................... 8
III Logical design................................................................................................................................................................................... 10
1 Entity Relationship Diagram (ERD)................................................................................................................................................ 10
IVExplanation....................................................................................................................................................................................... 13
1 Database Entity.............................................................................................................................................................................. 13
2 Relationship................................................................................................................................................................................... 13
3 Explain my ERD............................................................................................................................................................................. 13
V Physical design................................................................................................................................................................................. 16
1 3NF Normalization......................................................................................................................................................................... 16
2 Functional presentation of the tables............................................................................................................................................. 17
VI Diagram........................................................................................................................................................................................... 23
Figure 1.................................................................................................................................................................................................. 6
Figure 2................................................................................................................................................................................................ 11
Figure 3................................................................................................................................................................................................ 14
Figure 4................................................................................................................................................................................................ 15
Figure 5................................................................................................................................................................................................ 15
Figure 6................................................................................................................................................................................................ 16
Figure 7................................................................................................................................................................................................ 18
Figure 8................................................................................................................................................................................................ 18
Figure 9................................................................................................................................................................................................ 19
Figure 10.............................................................................................................................................................................................. 19
Figure 11.............................................................................................................................................................................................. 20
Figure 12.............................................................................................................................................................................................. 20
Figure 13.............................................................................................................................................................................................. 21
Figure 14.............................................................................................................................................................................................. 21
Figure 15.............................................................................................................................................................................................. 22
Figure 16.............................................................................................................................................................................................. 22
Figure 17.............................................................................................................................................................................................. 23
I INTRODUCT.
Today, consumers are gradually tending to buy smartphones in major stores. Consumption market, rapid development of the
consumer goods industry Enterprises such as (TGDD, ihub, shopdunk,…) have grasped that trend and launched many stores,
supermarket chains to meet the urgent needs of user.

With that growth, the question of how to manage your supermarket sales effectively and simply is a headache for many managers.
My iHub partner, they asked me to design a database to manage their goods, employees, and customers.

Figure 1
II Design a relational database system using appropriate design tools and techniques, containing at
least four interrelated tables, with clear statements of user and system requirements.

1 Scenario of the chosen system.

I am a Database Developer staff member for an IT consulting firm. Now I have to develop a database of the sales system of a
phone supermarket

The database that stores employee information includes the following information: MANV (employee code), TENNV (employee
name), NGAYSINH (date of birth), DIACHI (address), SDT (phone number). Customers include the following information: MAKH
(customer code), TENKH (customer name), DIACHI (address), SDT (phone number). The invoice includes the following information:
SOHD (invoice number), NGAYLAPHD (invoice date), TENNV (employee's name), TENMH (product name), MAKH (customer
code). The item includes the following information: MAMH (item code), TENMH (product name), DVT (unit of calculation). Type of
goods includes the information: MALOAI (type code), TENLOAI (type name), DVT (unit of calculation). Area includes the
information: MAKV (area code), TENKV (area name).

The purpose of the system development was to make sales easily and easily retrieve customer information for the most
convenient machine maintenance.

2 Practical implementation process.

Managing the operations of the store is the daily work of the employees and managers at that store.

Store management includes jobs such as sales, collecting money, daily income reporting, checking merchandise. Each time
transaction at the counter, employees must print invoices and give them to customers, then write the name of the product, quantity
and quantity so that at the end of the day, employees must summarize the revenue paid to the manager.

3 advantages and disadvantages in the implementation process.

Advantages: This job does not require highly qualified staff, low operating costs, taking notes is also very familiar to many
people so the job will be easy and does not take too much effort.
Disadvantages: Currently, with the rapid and rapid development of information technology, book managers are outdated and have
the following main disadvantages:

Updating and correcting information takes a long time because the customer's information is so much.

Manual management method reduces work efficiency, takes a lot of effort.

Safety is not high, not catching up with the technology of the times.

We need to help ihub improve a management system that is designed with full functionality and because of the above
disadvantages, a DATA BASED system needs to be built for more optimal management.

4 This database must have the following functions:

Catch the stock and replenish the missing goods quickly.

Pay attention to the employee's results.

Quickly import and export data.

Real-time high alarm system.

6 SRS (System Requirement Specification)

System Requirements Specification. A software requirements specification (SRS) includes in-depth descriptions of the software
that will be developed. A system requirements specification (SyRS) collects information on the requirements for a system.
“Software” and “system” are sometimes used interchangeably as SRS.

Requirements of the units related to management, operation and maintenance

perform internal operations and use services provided outside the system.

Analysis and design of a store management system is to create a management system

The store includes the following functions:

- Employee manager
- Customer management
- Manage export and import
- Manage the stock and quantity of remaining stock
- Manage the sales per month of the supermarket

Identify the main functions of the database:

- Store employee information

- Store customer information
- import and export in stock
- Update new product information
- Statistics and reports on employee metrics
- Import and export data easily
- Secure customer information

Non-functional requirements: are constraints and conditions for functional requirements of the system such as time constraints,
performance constraints, usage standards ... These requirements image affects the quality, usability of the system and has a direct
impact on user satisfaction, thus determining the success of the system.

Usability: is the level of use and the satisfaction of users such as: in accordance with needs; Easily to

learn how to use it; User interface; Ability to access and exploit;

Confidence: Mature; Ready; Fault tolerance; Resilience; Time between failures interrupt operation

of the system;

Information security: Confidential; Integrity; Accuracy. Low maintenance costs.

Works well 24/24.

SQL Server configuration:

Os: Windows 7 (32 bit or 64 bit) or higher. CPU:

Pentium IV or higher.

Ram: At least 1GB.

Free disk space: At least 10GB.

NET Framework: Version 3.5 SP1.

IIILogical design.

1 Entity Relationship Diagram (ERD).

An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database. An entity in this context is an object,
a component of data. An entity set is a collection of similar entities. These entities can have attributes that define its properties.

The store's information system includes the following entities:

- Staff
- Customer
- Bill
- Product
- Invoice details
 Figure 2

Attributes of the entity:

Employee Entities:

Employees include the following information: FULLNAME (employee's name), DATE OF BIRTH (date of birth), ADDRESS
(address), PHONE (phone number).

Customer entity:
Customers include the following information: FULLNAME (customer name), ADDRESS (address), TELEPHONE (phone
number).

Invoice entity:

The invoice includes the following information: BILLID (invoice code), DATE (date of issue), STAFF (employee code),
CUSTOMERID (customer code).

Entity item:

Item includes the following information: ITEMID (product code), ITEMNAME (product name), AMOUNT (quantity).

Legal entity invoice details:

Type of goods includes the information: ITEMID (item code), BILLID (invoice code), AMOUNT (quantity), PRICE.

Relationships of tables

The relationship between an employee and an invoice is a one-to-many relationship. Because an employee sells many bills, an
employee only prints one bill.

The relationship between a customer and an invoice is a one-to-many relationship. Because an invoice can only be purchased by one
customer. However, a customer can have multiple invoices.

The relationship between the invoice and the item is a many-to-many (n-n) relationship, not in compliance with the ERD's rules, so
I created a BILLDETAIL table to separate it into a One - Many ( 1-n) to provide hardness and accuracy in a database

creation.
IVExplanation.

1 Database Entity.
Database entity is a thing, person, place, unit, object or any item about which the data should be captured and stored in the form of
properties, workflow and tables. While workflow and tables are optional for database entity, properties are required (because entity
without properties is not an entity).

2 Relationship.
A relationship is established between two database tables when one table uses a foreign key that references the primary key of another
table. This is the basic concept behind the term relational database.

3 Explain my ERD.
EMPLOYEES, including the following entities:

STAFF TABLE.

- FULLNAME: Displays employee's name

- BIRTHDAY: to accurately supplement employee's information.
- ADDRESS: Provide the employee's address and location
- PHONE: provide the employee's phone number.
 Figure 3

BILL includes the following attributes:

Customer ID: This is the foreign key in the INVOICE table to refer to the Customer code in the Customers Table.

Date of issue: Date of invoice

StaffID: This is a foreign key in the BILL table that displays the employee code in the employee.

Delivery Date: displays the date the customer receives the product
 Figure 4

CUSTOMER, Include the following entities:

- FullName: Describe the client's full name.

- Address: Contact address of the customer.
- Phone: Customer's phone number

-
Figure 5

ITEMS includes the following entities:

ItemID: This is the primary key that distinguishes one item from another.

NameItem: Indicates the name of the item corresponding to the order number. Unit:

Indicate the price of the item

Figure 6

V Physical design

1 3NF Normalization.
Based on the ERD in the above section, the Database includes tables with the following relationships:

- STAFF ( FULLNAME, BIRTHDAY, ADDRESS, PHONE).

- CUSTOMER ( FULLNAME, ADDRESS, PHONE).
- ITMES ( ITEMNAME, AMOUNT, UNIT PRICE).
- BILL (BILLID, DATE, CUSTOMERID, STAFFID).
- BILLDETAIL (ITEMID, BILLID, AMOUNT,PRICE).
2 Functional presentation of the tables.
EMPLOYEE Table: The Employee Table is used to store information about the employee entities of the Human Resources
Department of the store management system.

Table of employees
FULLNAMEST (nvarchar (50)), BIRTHDAY (smalldatetime) with born conditional date must be less than the current date 'Check
(BIRTHDAY <Getdate ())', ADDRESS (nvarchar (50)), TELEPHONE (nvarchar ( 20)).

Numerica Allow
Column Data Constraint
l order Nulls
Name Type s
1 MANV nvarchar(1 PK No
0)
2 HOTENNV nvarchar(5 No
0)
3 Address nvarchar(2 No
0)
4 SDT nvarchar(1 No
0)
 Figure 7

Figure 8

Table of product

The Products Table: The Products Table is used to get product information about the products under the product. The Products table
has attributes (columns) such as product code, product name, product unit price, ... Details of the Products table are described in the
following table:

Numerica Column Name Data Constr Allow

l order Type aints Nulls
1 MASP nvarchar(1 PK No
0)
2 TENSP nvarchar(5 No
 0)
3 DONVITINH nvarchar(1 No
0)
4 DONGIA int check No
(UnitPrice>0)

Figure 9

Figure 10

Table of Customer

Customers Table: The Customers table is used to identify customer information about customers that belong to customers. The
Customers table has attributes (columns) such as customer code, customer name, date of birth, address, phone number, email, ...
Details of the Customers table are described in the following table:
 Numerical Column Name Data Type Constraint Allow
order s Nulls
1 MAKH nvarchar(10) PK No
2 HOTENKH nvarchar(50) No
3 DIACHI nvarchar(100) No
4 SĐT nvarchar(10) No

Figure 11

Figure 12

Bill of table

Invoice Table: Invoice table is used to bill fields. The invoice table has attributes (columns) such as invoice code, invoice date,
customer code, employee code, department, ... Invoice table details are described in the following table:
Numerica Allo
Column Data Type Constrain
l order w
Name ts
Nulls
1 MAHD nvarchar(10) PK No
2 MAKH nvarchar(10) No
3 MANV nvarchar(10) FK No
4 NGAYLA smalldatetim check(NGAYLAP<getd No
P e a te())
5 NGAYNHA smalldatetim check(NGAYLAP<getd No
N HANG e a te())

Figure 13

Figure 14
Invoice details table

Invoice Details Table: Invoice details table is used to invoice fields. Invoice DETAIL table has attributes
(columns) such as invoice code, invoice date, customer code, employee code, department, ... Invoice
table details are described in the following table:

Numerical Allow
Column Name Data Type Constraints
order Nulls
1 MAHD nvarchar(10) PK No
2 MASP nvarchar(10) PK No
3 DONVITINH nvarchar(10) No
4 SOLUONG int No

Figure 15

Figure 16
VI Diagram.

Figure 17
Index of comments

2.1 + Good points:

- P1: The database solution contains interrelated tables, with clear statements of user and system requirements, ER Diagram and normalization statements (3NF)
and describing how the system works.

+ Weak points:
- There are some mistakes in the report such as: format, margin,…

+ Should do
- Study hard