Content uploaded by Dănuț-Octavian Simion
Author content
All content in this area was uploaded by Dănuț-Octavian Simion on May 14, 2017
Content may be subject to copyright.
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
61
APPLICATIONS FOR BUSINESSES THAT USES RELATIONAL
DATABASES
Lecturer PhD.,D -Octavian SIMION
Athenaeum University, Bucharest, Romania
e-mail: danut.simion@univath.ro
Univ.Prof. PhD.,Emilia VASILE
Athenaeum University, Bucharest, Romania
e-mail: rector@univath.ro
Abstract
The paper presents a database production model designed as a
warehouse star that contain dimensions like deposits, raw materials, stocks,
products, producer, locations, time and a fact table with foreign keys and
measures. This model optimize the activity of a business based on a
production activity in the way that it can store large amount of data in a
historical way that can be the base for future scenarios with key values
changed by the decision maker. The decision maker analyses a large
spectrum of reports and choose what indicators to observe and what
ount of
data and trends. Database applications for business improve the efficiency
in managing large quantity of data in the sense for storage, updates, queries,
interaction with the users and also getting answers through reports. The
schema specific to a database is very flexible and permits adding or
removing columns and also adding and removing entities. This feature is
very useful when the relational database schema is transformed in a data
warehouse shaped as a star with dimensions and a fact table. This model
permits advanced queries and the usage of rollup and drill down objects
specific to the business intelligence tools that offer quick responses to the
complex answers. To a production business the choice of a database
application designed and implemented as data warehouse star model,
bennefits from all the advantage of storage and also a superior and complex
tool for building queries.
Keywords: Database storage, business intelligence tools, business
production model, data warehouse star model, SQL queries and reports,
rollup and drill down objects.
JEL Classification: C23, C26, C38, C55, C81, C87
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
62
1. Introduction
A database application for a production business envolves storage of
a large amount of data and the management of entities like products, stocks,
raw material, deposits, producers and locations. This model also permits the
interaction with the users and allow to the decision makers to build complex
queries and reports that respond to a various questions. The architecture of a
system aimed at its components and how they interact, types and operations
allocated to each component. For an interactive decision support system
architecture includes the following subsystems: data management
subsystem, management subsystem models and sub-dialog user. Data
management subsystem consists of the following elements: database
management system database oxidase, data dictionary and declarative query
language.
The database is built to meet the information requirements of the
system and data is a collection of interrelated operated by one or more users
in one or more applications. The database is no internal data, external data
and personal data [1], [5].
Internal data from the current activities of the organization and operations of
the various functional departments image. Data external economic
information circulated nationally and internationally and usually come from
the industrial sector of which the company, the legal regulations. Personal
data is data that relates to the behavioral aspects of decision-makers in
making decisions. Whatever their nature, data is stored in relational
databases (transactional system data) or data warehouse, built on subjects of
interest. In current systems, the company's intranet, are increasingly present
data accessible through web browsers and multimedia objects, such as maps,
images, sounds.
The data source, internal or external, data is extracted and managed
by a management system database. The management of the database
depends on the data organization. In most cases there is a system SGBS
transactional relational data management system and a database for
multidimensional data warehouses created. The data dictionary is a catalog
of all the data in the database. It contains data definitions, data sources and
their intrinsic significance. The data dictionary operations are allowed to
add new data, deletion or retrieval of existing information according to
certain criteria. Most often the data dictionary used in the first phase of
decision-making, data mining to identify their problems and opportunities.
Declarative query language offer data interrogation facilities. The SQL
language is used, which accepts requests for data from other systems [2], [4].
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
63
2. The specification of data storage in databases managed by the
Subsystem management model consists of the following components:
base models, the management models, dictionary models and processor
execution and integration patterns
Base models kit contains models that enable analysis of the facts and the
solution choice under the conditions required by the user. It is the
component that differentiates interactive decision support systems from
other systems. The models are domain-specific (financial, statistical,
forecasting) models and can be classified into strategic, tactical and
operational models models. The models assist strategic decision-maker in
developing the overall strategy of the firm, on the development issues of
corporate objectives, choice of location of the equipment; analyze the
impact of environment on activity of the organization.
Tactical models are applied to the organizational subsystems and assist the
user in taking deciziilorprivind allocation and management of resources of
the subsystem, planning model for promoting products. The models are used
currently operational and transactional system aims of the organization,
credit approval of a plan of production, quality control.
Base management system allows creating new models models using
programming languages, update and modify existing models, establish
interdependencies between models. Manage in a logical manner a variety of
models to consistency of the data model and provides integration of system
information components in the application [3], [6].
The dictionary is a catalog of all models modelelelor containing the
definitions used, the main functions of their scopes. The processor execution
and integration of models must be viewed through the prism functions
performed by him, thus:
Processors execution models interpret instructions received from the
user and sends management system models; check the conduct of the
programs that are built models.
Combines processor integration operations in several models depending
on the requirements of decision making and decision support system
integrates other applications.
The subsystem contains a dialog with the user management system
user interface and a processor that takes inputs through outputs and provides
command languages through language presentation. It is the only system
component with which the user works directly.
Defining an effective interface should consider choosing devices
Input / Output, design screens, the format of the data and information.
Generators interactive decision support systems provide multiple interface
styles: menu-driven interaction design, question-answer style, dialogue
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
64
based on natural language processing, graphical user interface. Choice is an
option team decision and depends on the method that ensures information
management; the real complexity of the system will be implemented.
Decision support requires a permanent dialogue with the user, so
that the interface has a much greater importance than other systems. The
user, person or group of persons through the role they play in making the
decision, is considered part of the system. It is involved in all phases.
Studying the specific context, correctly defining the problem and lead to
choosing an alternative from a set of possible solutions. Quality and
efficiency of decision depends on how they react in the context of decision-
making, how the adopted solutions [2], [4].
Managers or specialists in various professional fields, expects the
system conclusions or details. It is working in teams constituted for a period
of time, according to some temporary tasks. In complex situations there are
analysts arrange the connections with managers that use the decision
support systems, being the persons who have knowledge about managerial
problems, but experience in decision support technologies. Harmonization
with the environment in which they work, transferring responsibility to
lower levels, seeks the participation of all the success of the business.
Communication between managers and other employees,
communicating with other sources of information is achieved precisely by
this component dialog. And so, interactive decision support systems are no
longer used only for planning, organization and coordination but also for
inter-personal communication, establishment and execution of daily tasks.
The functionality of these systems relies on the use of data stored in
the database. Existing data are organized, coordinated, integrated and stored
to give the user a complete view of reality.
Operational data, subject of daily transactions are stored in relational
databases. Management systems using relational databases, data are
processed to obtain information.
Synthesis, analysis and interpretation of data are necessary to support
decisions involving merging, categorizing, grouping data correlation and
existing in accordance with its intended purpose. Synthesis is a process
intermediate turn data into information, a process by which data is
centralized by certain criteria.
The analysis highlights the relationship to structures, causal and
functional between data synthesized. The simplest form of analysis is
comparing the data with similar data synthesized. In addition, information
can acquire quality when using techniques of graphical representation that
makes these correlations, observation techniques analytical data based on
mathematical theories, comparing actual data with the theoretical products
of a hypothetical model or observation techniques automatic based on data.
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
65
Interpretation follows the descriptive power of the model. Calls on
knowledge of general and specific fundamental associated with the domain
and existing expertise [1], [3].
Systems architecture components of interactive data-driven decision
support, the most important is the data management subsystem. Data from
internal and external sources make up an analytical database, which
contains analytical indicators which reflect the performance of the analyzed
system, which allows evaluation of the system analyzed in a
multidimensional manner. By performing a diverse set of operations on
transactional data, custom views are provided to the user on the stored data.
Decision support systems are a natural progression from reporting
information systems transaction processing systems. These systems are
interactive, accounting information systems, ICT-using decision models and
specialized databases to assist managers in decision-making processes. Thus,
they are different from transaction processing systems, which focuses on the
processing of transactions and data generated by business. They also differ
from information reporting systems that focus on providing pre-specified
reports for managers, reports that help for making complex decisions.
Instead, decision support systems provide managers information in
an interactive session or in an ad hoc way. Such a system provides
analytical modeling, data retrieval systems and information presentation
capabilities that allow managers to generate the information needed to make
decisions in an interactive computerized. For example, spreadsheet
applications allow a manager to receive interactive responses to ad hoc
requests for sales or profit forecasts formulated within analitice1 models.
The answers differ from those pre-specified information reporting
systems.
When using a decision support system, managers investigates
experimental alternatives and receives information based on a set of
alternative assumptions. Thus, policymakers should not specify a priori
information requirements, the system interactively assisting them to find the
information they need.
System information executive management systems are built on
strategic management information needs. Managers procure the necessary
information from several sources, including letters, notes, journals and
reports made manually or through computer systems, in meetings, telephone
conversations and social activities. The main purpose of information
systems is to provide executive decision makers of the organization that
provides strategic management quick and easy access to information about
critical factors in achieving the strategic objectives. Such schemes involve
the use of graphic representations and fast access to content databases for
information about the current status and trends of components designed. A
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
66
database containing all the necessary information about objects involved in
a lot of applications, logical relations between this information and the
proper processing techniques. Databases and integration of data occurs,
meaning that many files are taken together, eliminating redundant possible
that information. It also allowed simultaneous access to the same data,
which are found in the same place or are, distributed spatially more persons
of different preparations, each with personal working style [2], [6].
When analyzing the information needs of an organization, we have
mainly focused on the identification of entities, attributes and relationships.
We can look at an entity as a separate object such as a person, a
department, a concept or event that belongs to an organization to be
represented in the database. The attribute is a property that describes some
aspect of the object that we want to record and the relationship refers to an
association between various entities. Thus we can say that the database
contains entities, attributes, and relationships that are logical linked.
Depending on what is highlighted graphically, use two types of
architectures:
Component architecture - offers an insight into the elements that form
a system database, but also the inter-dependencies between them.
Specific components of the architecture components are:
data - are organized in a database, comprising:
software - is for the establishment and operation of the database and
contains:
ase;
system databases.
- Auxiliaries - are components contributing to the functioning of the entire
system and database:
a set of automatic procedures (routines) and manuals;
legal and administrative regulations;
hardware means used;
persons involved in the categories of users;
Architecture tiered structure database system on three levels and offers
an insight into the organization and its functioning.
- Conceptual level - is given by the database administrator vision on
the data. Related to this level, we can mention the following:
- administrator performed the conceptual structure of the database,
possibly using the tools provided by a DBMS;
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
67
- conceptual structure is obtained using a specific data model for the
database, and a design technique as appropriate;
- conceptual structure within the system is a representation of reality
that database transcribed;
- vision of the database administrator is independent of applications
to be developed (logical independence);
- conceptual level is the result of conceptual schema;
- realization of the scheme corresponds to a modeling activities
because it is a transposition in abstract terms of real world entities;
- once defined conceptual schema must be confronted with the real
world for identifying and resolving inconsistencies or omissions;
because of its comprehensive, unitary, it is recommended that the
conceptual scheme to be managed by a single person;
lated to this
level may present the following:
- scheduler performs application programs for describing and
manipulating data written in a DBMS;
- implements programs external structure (logic) data;
- external structure is derived from the conceptual structure;
- external structure of the vision of the database programmer for a
particular application;
- vision programmer is independent of technical support information
(physical independence);
- logical level is the result of external schema as part of the
conceptual scheme, implemented using a DBMS;
The physical level - is given the vision of the analyst, engineer, and system
on the data and is intended to describe how the data are stored in the
database. Related physical level we can mention the following:
- system analyst whose responsibility it is to achieve internal
structure (physical);
- internal structure is inferred from external according to techniques
and methods for allocation on a physical medium;
- described the internal structure of information data on physical
media;
- physical Layer is the result of internal diagram (physical) which is
defined in terms of files and records;
- scheme implementation is done using internal file management
system within the DBMS's and / or operating system, the
management of physical peripherals;
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
68
3. Developing an informatics system based on a relational database for
production designed as a data warehouse
The process of building a data warehouse envolves the analysis of data.
Extract information in order to obtain information for decision making.
Basically there are two steps: designing and populating data. Design is the
stage where the data warehouse model is chosen, depending on the complexity
of the system real user requirements and data structure existing in the company
such as databases, Excel spreadsheets and so on [3], [5].
Building a data warehouse there are three models: type star, snowflake
patterns type and constellation type models. Conceptual models are
multidimensional and designed to organize data necessary decision-making
process on issues. The models may change depending on the context,
presenting the data in a structure bed, easily designed and accessible to end
users.
In such a model is highlighted:
quantitative data centralized called measures of activity
quantitative criteria for centralized aggregation, referred sizes
relational table that stores the measures identified by the facts
dimensions is called table
Tables where aggregation criteria has explicit codes, called type tables
list. Facts associated table.
The star is the type of aggregation criteria when codes are explained in
type tables list. Using data from lists, star type structure enables higher levels
of aggregation on the initial size [4], [6].
Data warehouse star
The eastern type constellation when several schemes that use the same
type star catalogs. The advantage is that the same warehouse can store different
facts that have certain common coordinates and therefore share the same lists.
Deposit constellation
The type is snowflake if any alternative classifications for the same
code by integrating undersize and alternative dimensions. To analyze the
evolution of the value of Supplies Company based on several criteria required
of users, you can define a data warehouse type star.
In figure 1 is described a star warehouse model for production:
In such a model the dimensions have a corresponding key in the fact
tables (ex. Id_producer primary key from Producers has a corresponding key
in Fact production foreign key). This model permits to create complex query
by simply choose the attributes from dimensions and a measure from the fact
table. It also is possible to create graphics based on queries that contain
attributes from dimension tables and measures from the fact table.
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
69
Inserting data into dimensions can be made through an insert SQL
command:
INSERT INTO PRODUCERS (ID_PRODUCER, PRODUCER_NAME,
PRODUCER_TYPE, PRODUCER_DETAILS, PRODUCER_RATE) VALUES (1,
Figure 1 A star warehouse model for production
Inserting data into the fact table is made also through an insert SQL
command based on a trigger fired when inserted data into dimensions is:
INSERT INTO FACT_PRODUCTION (ID_FACT_PRODUCTION, ID_DEPOSIT,
ID_PRODUCER, ID_PRODUCER_LOCATION, ID_PRODUCT, ID_RAW_MATERIAL,
ID_STOCK, NR) VALUES (1, 1, 4, 5, 3, 1, 2, 1);
Creating a query into a star model warehouse:
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
70
Figure 2 Building a query in a star warehouse model for production
The query code:
SELECT DISTINCTROW DEPOSITS.DEPOSIT_NAME,
DEPOSITS.DEPOSIT_LOCATION,
PRODUCERS_LOCATIONS.PRODUCER_COUNTRY,
PRODUCERS_LOCATIONS.PRODUCER_ADDRESS, PRODUCTS.PRODUCT_NAME,
PRODUCTS.PRODUCT_PRICE, RAW_MATERIALS.RAW_MATERIAL_NAME,
First(PRODUCERS.PRODUCER_NAME) AS [First Of PRODUCER_NAME],
Sum(STOCKS.ACTUAL_STOCK) AS [Sum Of ACTUAL_STOCK]
FROM STOCKS INNER JOIN (RAW_MATERIALS INNER JOIN (PRODUCTS INNER
JOIN (PRODUCERS_LOCATIONS INNER JOIN (PRODUCERS INNER JOIN
(DEPOSITS INNER JOIN FACT_PRODUCTION ON DEPOSITS.[ID_DEPOSIT] =
FACT_PRODUCTION.[ID_DEPOSIT]) ON PRODUCERS.[ID_PRODUCER] =
FACT_PRODUCTION.[ID_PRODUCER]) ON
PRODUCERS_LOCATIONS.[ID_PRODUCER_LOCATION] =
FACT_PRODUCTION.[ID_PRODUCER_LOCATION]) ON
PRODUCTS.[ID_PRODUCT] = FACT_PRODUCTION.[ID_PRODUCT]) ON
RAW_MATERIALS.[ID_RAW_MATERIAL] =
FACT_PRODUCTION.[ID_RAW_MATERIAL]) ON STOCKS.[ID_STOCKS] =
FACT_PRODUCTION.[ID_STOCK]
GROUP BY DEPOSITS.DEPOSIT_NAME, DEPOSITS.DEPOSIT_LOCATION,
PRODUCERS_LOCATIONS.PRODUCER_COUNTRY,
PRODUCERS_LOCATIONS.PRODUCER_ADDRESS, PRODUCTS.PRODUCT_NAME,
PRODUCTS.PRODUCT_PRICE, RAW_MATERIALS.RAW_MATERIAL_NAME;
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
71
Based on the queries it can be built reports that helps the decision
makers to choose what direction should have their actions. An example of a
report based on the query above is represented in the image bellow:
Figure 3 A report based on a query in a star warehouse model for
production
This kind of warehouse for production can improves orders and the
stocks are always suplied with raw material. The responsible person with
supply can choose what column should appear in the report and see what
suplier has the raw material he needs for production. This flexibility is offered
by the business intelligence tools in obtaining various results and create many
types of reports for choosing the proper solution [4], [6].
Conclusions
Using databases for information systems that apply to businesses
environments has advantages in managing production, financial, accounting
and provisioning subsystems. Having data stored in a history of time in a
database helps to build scenarious based on the knowledge accumulated and
allows the decidents to avoid blocking and bad management based on
previous experiences [1], [3]. The SQL language permits to query hystorical
data in different periods of time that are useful in building future strategies
in which An
Internal Auditing & Risk Management ________________ Anul XII, Nr.1(45), March 2017
72
information system for production has an physical architecture that mirrors
all the main components, from raw materials to products and also has an
interaction layer that may possible to evaluate an before situation and also to
build scenarious that make possible future production that indicate the
growth. A star warehouse model for production is flexible at the physical level
in the sense that offer the posibility to add new dimensions or new measures
and at the presentation level permits to build numerous reports that can reflect
different scenarious by simply changing some key values an give other
perspectives to the decision maker [2], [5]. This type of information system is
interoperable with other system for financial, sales, accounting, marketing and
management, and so it has inputs from other external environments, such as
other companies that provide raw materials and offer outputs for the internal
structures of an company such as sales or to the external markets and other
companies that are competitive in the business environment.
References
1. E.O. de Brock, F. Remmen, J.C. Wortmann,
product-types and bills-of-material: application of a set-theoretic
approac http://dx.doi.org/10.1016/B978-0-444-88658-3.50006-4,
2015;
2. Ana L.D. MURR, Jose M.V. de CASTILHO, Clesio S. dos SANTOS,
Claudio WALTER
http://dx.doi.org/10.1016/B978-0-444-88658-3.50007-6, 2015;
3. El Youssefi, M. Rhorab, S. Taghboulit, R. Soenen -
relationship model for computer-
http://dx.doi.org/10.1016/B978-0-444-88658-3.50009-X, 2015;
4. Ming Wang Environment (IDDE)
http://dx.doi.org/10.1016/B978-0-444-88658-3.50013-1, 2015;
5. Ramon Barquin, ,
http://searchdatamanagement.techtarget.com/, 2014;
6. Detlef M. Weber, Colin L. Moodie,
information management systems - a requirement for computer
integrated manufacturing and assembly
http://dx.doi.org/10.1016/B978-0-444-88658-3.50014-3, 2014;
7. URI: http://www.itdev.co.za/production_system.asp
8. URI: https://www.teamdesk.net
9. URI: http://www.zapmeta.ws
10. URI:http://www.acronymfinder.com/Database-Systems-for-
Advanced-Applications.html