Database Management for Online Shopping

This assignment delves into the application of database management systems for online shopping businesses. It examines how databases store and manage crucial information like customer details, orders, products, payments, and employee data. The report highlights the Entity-Relationship (ER) model's significance in database design, illustrating relationships between entities through a diagram. It also emphasizes the creation of technical and user documentation for the designed database.

Database Design & Development
1

Contents
INTRODUCTION...........................................................................................................................3
P1 Design a relational database system by using the appropriate design tool, technique and
containing the interrelated tables.................................................................................................3
Entity:..........................................................................................................................................4
D1 Effectiveness of design in the relation to system and user requirement................................8
P2 Develop the database system with the evidence of user interface, output and data
validations and querying across multiple tables..........................................................................9
M1 Implement the functional database system that involves system security and database
maintenance...............................................................................................................................15
D2 Evaluate the effectiveness of database solution in relation of user and system requirement.
...................................................................................................................................................15
P3 Implement a query language into the relational database system........................................16
P4/ M4 Discuss about the test system against user and system requirements...........................26
P5/ M5 Produce technical and user documentation..................................................................28
D3 Discuss the Improvement in future which require to ensure the continued effectiveness of
database system.........................................................................................................................29
CONCLUSION..............................................................................................................................30
REFERENCES..............................................................................................................................31
2

INTRODUCTION
Database is a collection of inter related data which help for inserting, deletion and
retrieval of data from the database system. It is also organised the data in the form of tables,
reports, schemas and views etc. It useful for managing the large amount of data and information
in the database system. This report will discuss about the Fashion outlet which is based on the
online shop to design the database for increasing the productivity and profitability. It can be
designed the database system that store the information regarding the consumer, orders, items
and payment, employee details etc. It allows to user access the information about the product that
provided by the online shop for Fashion outlet expansion.
Furthermore, this report will discuss about the Entity relationship model which required
for designing database because it is determined the important entities and create a relationship
between them. It will describe the ERD diagram for the entities along its attributes through
Normal forms. Moreover, it will produce the technical and user documentation that can use for
the designed database.
P1 Design a relational database system by using the appropriate design tool, technique and
containing the interrelated tables.
An Entity relationship diagram is basically set entity that stored in the database system. It
is a type of high level conceptual data model which based on the real entities to create
relationship with one or more. It helps to analyse the requirement of data in systematic manner to
produce the well-designed database system.
 Entity relationship Model allows to construct the database design and also considered the
requirement of system.
 It is very easy to use as the graphical tool for the purpose of data modelling.
 It is widely used in the database design and representing the logical structure of DBMS.
 It useful to determine the entities that exists in the database system and create relationship
between them.
3

Figure 1: Entity relationship Diagram
Entity:
An entity is a type of object in the system that create model and store information in the
database. It is generally applicable to recognise the concepts, abstract which are relevant to the
database. An entity is the analogues to the table in the relational model.
There are several type of entities
Attributes:
An attributes is an item of data which are stored in the form of entity. Each and every
entity are containing the specific attributes. It can be represented in the ER model to represent in
the appropriate manner.
4

Data Dictionary:
Entities Attributes Data type
Customer  Customer_id (PK)
 Customer_name
 Customer_email
 Customer_phone no
 Customer_address
 Order_id (FK)
Integer
Varchar
Varchar
Integer
Varchar
integer
Order  Order_id (PK)
 Order_type
 Order_status
 Order_name
Integer
Character
Varchar
varchar
Product  Product _id (PK)
 Product_name
 Product_price
 Product_type
 Product_weight
 Category_id (FK)
 Customer_id (FK)
Integer
Varchar
Number
Varchar
Real
Integer
integer
Payment  Payment_id (PK)
 Amount
 Payment_type
Integer
Number
varchar
Categories  Category_id (PK)
 Category_name
 Category_type
 Payment_id (FK)
Integer
Varchar
Text
integer
Table:1
5

Primary key:
A primary key is a type of special relational database column that can design to uniquely
determine the table records. It must contain unique values for each row or information and data.
It has a minimal attribute in table that uniquely identifies the tuples in the form of tables.
As per above table, it can be identified the primary key in each database tables.
In customer table:
 Primary key: Customer_id
In order table:
 Primary key: order_id
In product table
 Primary key: product_id
In Payment table:
 Primary key: payment_id
Foreign Key:
A Foreign key is a field in one table that uniquely determine the row of another or same
table. It can be defined in another table as a foreign key. It ensures the referential integrity of the
data and information.
For Example-s
In customer table:
Foreign key: order_id
It has created a relationship with order table because in order table, id is a primary key
that represented as foreign key into another table. It has created one to many relationships
between them.
In product table, customer_id is a foreign key in table so that it has created a relationship
between customer and product table.
6

Normalization:
Database normalisation is a type of process and method of structuring the relational
database system of Fashion outlet (Birch, Gwinnett and Walker, 2018). In order to reduce the
data redundancy and help to improve the data integrity.
1. First Normal Form
2. Second Normal Form3. Third Normal Form
First Normal Form
1NF is a property which create relation in the database system if the domain of every
attributes contain the atomic values and each attributes contain only single value in the system. It
is the simplest way to satisfy the requirement which allows the product price contain one or more
values in database table.
Second Normal Form
2NF is another normal form in database that can fulfil the requirement when it does not
have any prime attributes and its functionality dependents on the subset of candidate key. It must
satisfy the first Normal form.
7

Third Normal Form
3NF is basically used to normalise the database design to reduce the redundancy or
duplication of data. It is also ensured the referential integrity by ensuring second normal form.
D1 Effectiveness of design in the relation to system and user requirement.
The effectiveness of design in the relation which need to develop the documents that
consists the need and requirement of users. In this way, it should include the overall description
of every task in appropriate manner (Coury and Semmel, 2018). It has required for designing an
effective database system that can fulfill the need of users. User easily access the details related
the fashion cloths, item through the database system. Without having effective design, it is not
possible to maintain the processing of system. It ensures that capture the system requirement for
understanding the
8

P2 Develop the database system with the evidence of user interface, output and data validations
and querying across multiple tables.
It is developing the database of Fashion outlet online shop which provide the better
system to manage and control the overall functionality in proper manner (Goulet and et.al.,
2018). It is also stored and collect the information and data of fashion outlet. It provides the
graphical user interface that required for people to know about the technical knowledge which
help to access information in effective manner.
It shown the process of user interface which occurs when the user can access the
information through database system. In this way, database Engine has quickly processed the
functionality to perform the task in proper manner.
Data Validation:
It is an essential for Fashion company to validate the data that can use store in the
database system. It can be used the database engine to analyse and determine through SQL to
enter correct or wrong. It helps to manage and control the data that can applicable in the system.
It is developing the proper assumption related the software development life that adopt the
process to add more features in database. It should be used the SQL Server Management studio
that help to create and develop the database view and store the information in the form of tables
in it.
9

Output:
Database tables
Category
10

Customer table:
11

Order table:
12

Payment table:
13

Product table:
14

M1 Implement the functional database system that involves system security and database
maintenance.
It is designed the functional database system that can implement some security aspects
which required for protecting against other obstacles that affect the entire system. Initially, it is
used the different type of platform that perform specific role in the overall database system. They
are concerned about the security and privacy in the database system related back and recovery,
confidentiality and data integrity etc.
A database administrator is responsible for managing the overall data resources that
required in the system. It has designed the physical database to perform task such as monitoring
performance, security enforcement and database performance etc.
D2 Evaluate the effectiveness of database solution in relation of user and system requirement.
The effectiveness of database solution has generated the specific relation to the system and
user requirement because it is based on the resources to develop the effective database system. It
is also considered the requirement of users and afterward, it can be designed the dynamic
database system. Sometimes, it is the better solution to resolve the complication due to
understand the user need and implement in the process executions.
15

P3 Implement a query language into the relational database system.
SQL is a structural query language that can applicable in the database for creating tables
and establish the relational database system (Goulet and et.al., 2018). It plays important role to
communicate with database because they have specified SQL statement to perform task such as
insert, update, delete and retrieve data or information in the database system. SQL is a domain
specific language that can use in programs designed for managing and controlling the data in the
relational database system.
Query designs:
16

17

18

SQL Statements:
Create:
19

Insert:
20

21

Solve Queries:
Write SQL command to list the all employee with their full name, salary and gender.
Write SQL command to show all type of employees’ title and their specific department name.
22

Write SQL command to represent the full name, gender who belong to the department number of
“d004”.
Write SQL command to show all departments and its managers.
23

Write SQL command to show the list of departments managers who were hired after 1986.
Write SQL command to change the date of birth of employee and also assumed the employee
just phoned in her/his last name.
24

Write SQL command to delete the record of employees those who belong to the department
‘d004’ and id 10003.
Create database view to list the full name of all employees their department and salaries.
25

P4/ M4 Discuss about the test system against user and system requirements.
Testing is an approach to investigate the overall performance of database and test the
overall design that can develop by designer. It has performed the activities to check the results
and outcome that match with expected result (Hogan, 2018). In this way, it ensures to free the
defect in overall database system. It is an essential for database system to find the error, bugs and
also understand the missing need in database system. It is implementing the user interface testing
and database testing to control the functionality in effective manner.
There are different types of testing applicable in the database system to control and manage
in the functionality of system.
Back End database testing- this type of testing is mainly applicable in the database
system because there are many items hidden from users. It is mainly focused on the internal
process and memory to handle the task in proper manner. The designer will choose MS SQL
server database to store the information in database system. This type of back end testing is very
important because it help to manage the deadlock, data loss and corruption etc.
GUI testing- GUI testing is referred as the process of testing entire system of graphical
user interface. It includes to check the screens with proper control such as Tool bar, menu bar
and dialog boxes etc. This type of testing is required for designer because it help to user when
access the information through database system (Kralj and Hren, 2018). Graphical User Interface
testing are needed to perform different task to maintain position, length and acceptances of
characters that must execute to intend the functionality of application.
Database Schema testing- It is implementing in the database to perform validation and
verification process. It can implement on the database tables to check the availability of column,
rows in the DBMS. It is applicable to verify and check the services that can use in the system.
The database system has a large amount of data and information to collect in the form single
Database. It is also validating and verifying the value that enter in the database table.
Procedural testing- The Procedural testing is a process that understand the requirement
of system. It can identify the proper documents to perform the operation through instructions. It
is normally come in the form of design pattern. The testing procedures are based on the testing
practices, technique and process to ensure the database system is tested before deployment or
release. It is typically contained the resources and schedule to test the entire functionality of
database application of online Fashion industry. It has performed the action in step by step to
26

process for checking the speed and performance of system. This type of testing is required in the
programming because it has generated the large number of bugs and affect the overall
functionality. It is based on the hidden error generated in programming language.
Functional testing- It involves the checking functionality of database form user point of
view. It is the most common testing that can useful to handle the complex situation and also
maintain the error free environment in the system (Zhao, 2018). It can easily verify the functions
in the system and operate in proper manner.
The Database can be designed for the fashion outlet industry to store and collect the
information regarding the online terms. It is basic aims for checking the triggers, tables and data
schemas. It also includes the complex queries which are possible because of Structure language
to hold the entire operation of database. It useful for developing the dynamic database system for
fashion brand in global marketplace. It helps to access the information from user side.
Test Description Expected outcome Actual Outcome
Data Schema testing It is displaying expected
outcome to generate the
number of rows in the database
system that created through
SQL (structure Query
language)
Database Table and Column
Testing
It is generated the correct
result and outcome in the form
of database table. It ensures
the column name which is
right or wrong.
27

Stored procedure testing It is testing the store procedure
functionality and also build
one or more set of data in
positive or negative. It is
generating data set to build the
large amount of information
store in the system.
Table: 1
P5/ M5 Produce technical and user documentation.
Technical and user documents are main part of every system because it helps to determine
the resources, tool and platform necessary to store all information. It is the most important part of
database to split into division for developing database. A core aspects of developer to maintain
the development process in different steps and phases (Ye and et.al., 2018). It is collecting the
large amount of data convert into a single set of protect against the faults and bugs. These are
different type of process follows to develop the database in step by step.
 Establishing the requirement which involves consultation and agreement between the
different stakeholders. It expressed the statement of requirements in the database system.
 Afterward, it analysis the situation to consider the appropriate statement which consider
to represent in the form of documentation. It is based on the formal representation of
system that should be expressed (Zhao, 2018).
 Design starts after the specification, it can produce the document of design and mention
the full description of database hardware or software needs. It is also describing that how
system should be developed.
 Implementation can occur the computer system as per the given document design and
taking into account the system environment. In this way, it easily operates the specific
hardware, software during development.
28

 Testing is also considered the important factor that implement with system against the
document of design and requirement of specification. It displays the errors, bugs that
necessary to review of analysis, design or implementation process.
 Maintenance is dealing with the changes in the requirement and implementation of
environment, it always concern to fix the bugs and porting the system to new
environment. It analysis the overall changes required, design of solution and implement
the new concept in development. It is based on the life time maintenance to handle
system in proper manner.
D3 Discuss the Improvement in future which require to ensure the continued effectiveness of
database system.
It is basically continuous process that can extend as per requirement of resources and
other necessary features applicable in the term of management. For making the new database to
change the environment because it helps the Fashion outlet industry to manage and store the
information in the form dataset. It is collecting the data into table format so that it can easy for
user to access the result or outcome. For Further enhancement, it has implemented the queries to
resolve the complication during program executions.
29

CONCLUSION
As per discussion, it concluded that Database management is a software that can useful for
managing and controlling information related the online shopping enterprise in the form of
system. It helps for performing different functions such as insertion., deletion and retrieval of
data from the database system.
It Summarised that store the information of consumer, orders, items and payment,
employee details in the database system that designed as per requirement of case study. It allows
to user access the information about the product that provided by the online shop for Fashion
outlet expansion. Furthermore, this report is discussed about the Entity relationship model which
required for designing database because it is determined the important entities and create a
relationship between them because ERD diagram for the entities along its attributes through
Normal forms. Moreover, it produces the technical and user documentation that can use for the
designed database.
30

REFERENCES
Book and Journals
Birch, I., Gwinnett, C. and Walker, J., 2018. Aiding the interpretation of forensic gait analysis:
development of a features of gait database. Science and justice. 58(1). pp.78-82.
Coury, B.G. and Semmel, R.D., 2018. 11 Supervisory Control and the Design of Intelligent User
Interfaces. Automation and human performance: Theory and applications. p.132.
Goulet, A. and et.al., 2018, August. Development of an integrated geotechnical database and
associated analysis tools for excavation design in seismically active underground mines.
In 52nd US Rock Mechanics/Geomechanics Symposium. American Rock Mechanics
Association.
Goulet, A. and et.al., 2018. Development of an integrated geotechnical database and associated
tools for excavation design in seismically active underground mines.
Hogan, R., 2018. A practical guide to database design. Chapman and Hall/CRC.
Kralj, D. and Hren, N., 2018, January. Framework project of database development: Record
keeping of protective equipment. In 7. Međunarodni stručno-znanstveni skup" Zaštita na
radu i zaštita zdravlja"/7th International Professional and Scientific Conference"
Occupational safety and health".
Ye, Y. and et.al., 2018. Design and development of a CNC machining process knowledge base
using cloud technology. The International Journal of Advanced Manufacturing
Technology. 94(9-12). pp.3413-3425.
Zhao, M., 2018. Development of a Database-Driven Management System for Retail Food
Packaging Eye Tracking Studies.
31

32