SQL Database in Decision Making: Auditing and Business Processes

This report provides a comprehensive overview of SQL databases and their critical role in data-driven decision-making. It begins with an introduction to SQL, its history, and its future, highlighting its capabilities in accessing and manipulating data for various purposes, including auditing. The report delves into the application of SQL in auditing, explaining the components of SQL Server Audit and Database Audit. Furthermore, it explores the use of SQL databases in decision-making, discussing primary and foreign keys and the use of basic SELECT queries. It outlines a step-by-step process for making decisions using SQL and provides insights into transforming business queries into SQL queries, including understanding table relationships, handling LEFT joins, knowing result sets, breaking up queries, and managing complex aggregations and formulas. The report also covers how business processes leverage SQL for decision-making, emphasizing error testing, calculation transparency, and sequential and set processing. Finally, it references relevant academic sources to support the information presented.

SQL Database in Decision
Making

Introduction to SQL Database
SQL means Structured Query Language
Enables Accessing and Manipulation of data.
Executes queries against a database.
Retrieves data from a database.
Inserts records inside a database.
Any sort of data manipulation, such as,
updating data, deleting data, manipulating
tables and data, is enabled with SQL.
Primarily used for Auditing purposes.

History of SQL
 Structured English Query Language
(SEQUEL) was developed by IBM
Corporation, Inc.
SEQUEL later became SQL. However, it is
still pronounced as SEQUEL. In 1979,
Relational Software, Inc., renamed now as
Oracle, introduced the first commercially
available implementation of SQL.
SQL is utilized as a hugely accepted standard
RDBMS language.

Future of SQL
SQL will provide solutions to graphical queries and
objects, providing solutions to complex relationships.
Queries processed are adaptive in nature. This would
enable unparalleled performances in the future.
In future, SQL would even support Python and R
languages, for providing complex data analysis solutions
SQL will provide deep learning on GPUs.

SQL Database in Auditing
It is made up of two components
– SQL Server Audit and either of
Server Audit and Database Audit.
SQL Server Audit – Created in
Master Database, this server
level object define places to
enlist logs.
Server Audit – Existing at a
server level, this is used for
tracking activities occurring at
server levels.
Database Audits – Existing
within an individual database,
used for tracking activities in a
database, such as, data
operations and schema changes.

SQL Database for Data-driven Decision
Making
Primary and Foreign Key
These are the basic components for relational
database theory. Primary keys generate entity
integrity. They identify unique entity instances (Yuan &
Bahrami, 2017). Referential integrity is enforced by
Foreign keys, since they complete association between
two entities.
To achieve basic data model for decision-making:
Primary key attributes should be identified
Primary keys and relationships are validated
Primary keys are migrated to be converted into foreign
keys.

Basic Select Queries – for data driven
Decision-making
Helps in retrieving
more than zero rows
from one or more tables
or databases.
It shows information in
data view.
It is used for decision
making purposes, since
sorting out entities from
a table or database
begins with selecting
queries.

Steps for decision making in SQL
Identify the needs of making decision.
Analyse the options available to solve the
issues.
Select a feasible solution to address the
problem.
Refer the available methods to implement
decisions.
Implement the decision to the problem
noticed from the retrieved data.
Monitor the impacts of the decision taken.

Continued:

Transforming business queries to SQL
queries
Understand the table relationships -
1. Most will be one to many.
2. Identify the "many" table.
3. Identify the fields required for joins.
Think about LEFT join scenarios -
1. Selecting all the entities from a database by
tables.
2. Identify any inconsistencies in the data
produced in a consistent manner.

Continued:
Knowing the result set-
1. In a spreadsheet, at least one correct record
for query must be entered one by one
manually.
2. Queries are written in a simple enough form
to identify how many records should be
returned (Morrison et al., 2015).
3. Testing of the queries are done by both the
methods to make sure joining a new table
doesn't alter the result.

Continued:
Breaking up of queries into small parts -
1. Mostly, a query is not written entirely at once.
2. Complex queries can sometimes just be a
collection of simple queries.
Mixed levels of aggregation are checked
thoroughly-
1. Monthly, quarterly and year-to-date values in
the same result set needs to be implemented.
2. Calculation is done separately in queries
grouped on different values.

Continued:
Knowing the need for UNION
1. It is easier to break up subgroups into their
own select statements.
2. It is easier to write a query and union to
another query.
3. Each query would contain their own logic.
4. To include items from different tables in
different rows is an obvious use of queries.

Continued:
Complex/Nested formulas -
1. It is essential to consistently indent and
include the use of multiple lines.
2. Correct records are selected first.
3. Complex formulas are calculated with the
addressing of multiple queries.
4. Seeing the values used in the formulas helps
to spot areas accounted for NULL values and
handling the use of zero as a divisor.

How business processes use SQL for
decision-making
Error testing is done at first, to test and
verify is there is an error in the business
process or not.
Calculation Transparency are checked
thoroughly.
Sequential Processing is used to look out for
options available for the solution.
Further issues are checked out through the
Set Processing methods.
Scale and Complexity is also checked for the
issue identified.

References
Morrison, J. J., Hostetter, J., Wang, K., & Siegel,
E. L. (2015). Data-driven decision support for
radiologists: re-using the National Lung
Screening Trial dataset for pulmonary nodule
management. Journal of digital
imaging, 28(1), 18-23.
Yuan, J., & Bahrami, A. (2017). U.S. Patent
Application No. 14/809,573.

Thank You!