Developing a Relational Database System for 'The Signature Textile'

Desklib provides past papers and solved assignments for students. This project details the creation of a database management system for an online textile store.

Introduction:
Due to recent advancement in information technology as well as web technology all the
enterprises and the companies are dependent on the database management system to well
organize the data, store it and retrieve it. It also creates the interface between the database and
the user. All the organizations are dependent now days on database management system foe
efficient utilization of data of the company. The company for which database management
system is generated is “The signature textile”. This company is an online shopping store for
textile products. This company has many employees with different designation and they perform
different task. All the employees creates new data on regular basis which is stored and well
organized by the database management system which makes the task of the employees easy and
prevent them to do the same work manually. Different type of database management systems are
discussed as well as compared on different parameters. Also, relational database system is
designed in MYSQL of the company mentioned and queries are also generated for each of the
specifications. MSACCESS is created for each of the specifications. Different security system of
database and methods of database testing are explained along with the software and hardware
resources required to successfully design the database management system.
LO1 Analyse different types of database management systems
P1 Compare and contrast the different types of database models.
The collection of information or various interdependent data in an electronic form that is
organized in a good manner to achieve data deletion, data access, data addition, data
modification, and data management is called a database. The data contained in the database is
organized in different forms like tables, rows or columns. Proficient or effective data querying
and the processing of workload is the primary advantage of organizing data in above-mentioned
way like rows and tables. The software or the technology which is used to manage the database
is called Data management system. The recovery of data, data optimization, and management of
data are the major functions performed by an ideal database management system.
The database model depicts the data from the database in the form of logical design, defines the
configuration of the database and describes the manner in which the data will be stored, modified
and accessed. There is a different type of database models discussed as follows:

 Hierarchical Model
 Network Model
 Entity-relationship Model
 Relational Model
Hierarchical Model:
In a hierarchical model of the database management system, the data is organized in the form of
a tree (hierarchy). The data is given a tree-like structure containing a root element. The root
element of a database model is connected with the other data and data elements. The root
element is the first element in the hierarchy which grows in the form of a tree having child nodes
and parent nodes. Child nodes can have an only a single parent node. The primary advantage of
using the hierarchical model is the relationship between different kinds of data can be depicted
easily for better understanding.
Figure 1: Example of Hierarchical DBMS model
(Studytonight.com, 2019)
Network Model:
When the database system is arranged in graphical form but child node can have more than one
parent node. Due to the presence of more than one parent node, the more elements are connected
and linked with each other. The task of accessing the data becomes more simple, convenient and
speedy as large numbers of elements are linked and connected with each other. Earlier, the
relationship model was widely used as due to its ability to depict a large number of relationships.

Figure 2: Example of Network model
(Studytonight.com, 2019)
Entity-relationship Model:
By considering the object of interest as the entity and the features of the entity as attributes, the
entity-relationship model of a database system can be generated. Different relationships are used
for every entity. A very good database can be designed by using the entity-relationship model as
it can be converted into a relational model (tabular form).
Figure 3: Example of Entity-relationship model
(Studytonight.com, 2019)

Relational Model:
Relationship model is the widely used model for a database management system. The data from
the database system is arranged in the tabular form and the relationships among the data are
formed by generating a common parameter. After generating the table, the data is stored in the
rows and columns of the tables formed. In the relational model, tables are referred to as relations.
Figure 4: Example of the relational model
(Studytonight.com, 2019)

Parameters Hierarchical Network Relational Object-oriented
Time period Mid 1960’s
to early
1970s
Late
1960’s to
mid1970’s
Early 1970’s to
present
Late 1980’s to present
Evolution 2nd
generation
2nd
generation
3rd generation 4th generation
Physical
configuration
Tree-like
structure; one
root element
is present;
the
relationship
between
parent and
child nodes;
child nodes
extends from
the root
element; one
child has a
single parent
but one
parent has
multiple
children.
A network
of
interdepen
dent data;
child
nodes can
have
different
parent and
parents
also can
have
multiple
child
nodes.
Tables; Data is
arranged in tabular
form; links are formed
by making a common
parameter field as a
characteristic of the
other related data
fields.
Data is generated as objects.
Structural
changes
Inflexible
because it is
a very
tedious task
to modify the
data if once
data is
arranged;
difficult to
recognize
data,
therefore
requires the
proper design
of the model.
Inflexible
because it
is very
difficult to
edit and
place new
data,
therefore
requires
proper
attention
during
modeling.
Flexible because every
field of the table have
different parameters;
reorganization of data
and modification is
simple; as data is
independent editing is
easy.
Flexible; programming is
speedy and simpler as
programs are generated by
using modules; these
modules have pre-organized
codes.
Programmin Programmin Programm ODBC, SQL POLKA, BETA, POOL,

g languages g languages
inserted with
commands;
assembler,
Fortran,
COBOL,
ADS, PL1.
ing
languages
inserted
with
commands
;
assembler,
Fortran,
COBOL,
ADS,
PL1.
DRAGOON, C++, ESP,
Ada, Object Pascal, Emerald,
O blog, Python, Loop, Visual
basics.
Advantages The simpler
depiction of
one to one
relationship
as well as
one to many
relationships.
Due to the
presence
of large
networkin
g, the
redundanc
y is
prevented;
the
relationshi
p can be
depicted
in the
form of
subsets;
speed and
effective
in terms of
storage;
works
excellent
with
multiple
relationshi
ps.
Organization of data
and relationship be
easily visualized;
simplicity in designing
due to user friendly
GUI interfaces; report
generation and
querying is easy due
availability of updated
modern tools;
arranging and entering
data in the fields are
very easy; minor
errors as copied data is
eliminated; enhance
data integrity due to
large number of
relationship as well as
enhance uniformity;
easy data
management;
improved file integrity
provides more
information from the
same amount of data;
improved productivity.
Data storage and
manipulation of complex
information is easy due
object-oriented
programming; wave forming
of data is simple as object-
oriented programs support
data structure for arrays; easy
data recovery and
concurrency; object-oriented
programs are not required to
be converted into
sublanguage of the database;
Disadvantage Multiple Navigatio The performance was Time-consuming as well as

s relationships
are not
possible; the
user needs to
be aware of
the
structuring of
data to find
other data;
data
modification
in the child
table cannot
be done until
the changes
are
implemented
in parent
nodes; more
chances of
creation of
duplicate
data.
n is
difficult
when
compared
to the
hierarchic
al model
of the
database
manageme
nt system;
maintenan
ce and
execution
of
database
are
tedious;
knowledg
e of
database
structuring
is required
to recover,
modify or
delete the
data or
informatio
n;
knowledg
e of
programs
and codes
is
necessary
for
successful
execution.
very low in the initial
time frame of
development; costly;
require other hardware
in addition;
centralization leads to
failure; complex
design; concurrency
makes the task of data
recovery and backup
quite tedious; security
pitfalls due to data
sharing with a central
database.
expensive covert the old
database management system
into object-oriented database
management system; not so
popular so every user is not
familiar; many programmers
prefer RDBMS due to
familiarity; lack of object-
oriented developers; negative
effect on the performance of
due to the complex design of
objects;
Example IMS
(Information
Airline
reservatio
MySQL (free),
PostgreSQL, Oracle,
Object Store, Versant, O2,
Jasmine, Matisse, ONTOS,

Management
System)
ns; IDMS
(Integrate
d
Database
Managem
ent
System)
Satellite
communic
ations.
Informix, Ingres,
Sybase, MS Access,
FileMaker Pro, Visual
FoxPro.
POET.
Present usage Limited use Limited
use
Widely used and
popular
Getting popular day by day

LO2 Design a database management system using a relational model to meet
client requirements.
P2 Produce a design for a relational database management system to meet client
requirements
Specifications of the company (The signature Textile):
The name of the company for which relational database system is designed is “The Signature
Textile”. The signature textile is an online textile shopping company which fulfills the demands
of the customer who prefer shopping clothes and other textile items online from their homes
without going to the respective shops in the markets. To successfully run the online application
of this online textile company the database management system is required. As per the current
scenario, the most widely used data base system is relational database system due to its
numerous advantages over other database system. Therefore, relational database system is
designed for this e- shopping company as it will meet the business specification completely.
Some of the most important parameters and specifications which are required for designing the
relational database management system for this online shopping textile company “The signature
textile” are discussed below:
 Branch employee designation details
 Company branches
 Employee details
 Customer details
 Order details
 Company product details
Branch employee designation details:
The details regarding the designation of the employees of the company should be stored in the
database of this relational; database system. The name of the employee along with the
designation and phone number is mentioned. There are three columns present containing
different data. One column contains the details of the employee name, the second column

contains the phone number of the employee and last column contains the designation of the
employee adjacent to their respective names and phone numbers.
Figure 5 Branch employee
Company branches:
As the company have different branches as different locations, so the information regarding
every branch need to stored. Every branch of the company has its branch id, branch address,
branch name and the contact details of the respective branch. The customer or user will get all
the required information easily regarding the branch he or she wants to purchase some textile
items from. There are four columns contained in the table signifying different fields. There are
four columns contained in the table of company branch database. The following details are
included in the data of company branch:
 The branch id is mentioned in the first column
 The branch name is contained in the second column
 The addresses of the respective branches where they are located are required to be filled
in the third column
 The phone number is filled as contact details of the respective branch in the fourth
column of the table of the company branch.

Figure 6 Company branch
Employee details:
As employees are the foundation of every organization, so information regarding every
employee needs to be stored in the database of the company application. There are four columns
made in the table to store the employee details in the database. The following discussed
information contained in the columns of the employee table:
 Employee id is stored in the first column.
 Name of the employee in the second column
 Joining date of the employee is meant to be stored in the third column
 The phone number of the employee as a contact details is stored in the fourth column of
the employee table of database.
Figure 7 Employee detail

Customer details:
The customer details are stored in the database to contact the customer anytime if required. There
are four columns made in the table of customer to store the information as follows:
 The customer id is stored in the first column
 The customer name is stored in the second column
 The address of the customer need to be stored in the third column
 The email address of the customers is stored as contact detail in the fourth column of the
customer table of the database of this textile online shopping company.
Figure 8 Customer detail
Order details:
The items ordered by the customer are stored in the database of the company’s software. The
table of the order details contains the three columns and the following information is entered in
the columns of the order table:
 The order id is stored in the first column
 The customer id is kept in the second column
 The product name is stored in the fourth column of the order name of the database.