Store Database Management: Transactions and Sales Stored in Database

This research paper discusses the design of a comprehensive relational database for departmental stores to store transactions and sales for easy retrieval and analysis. It also explores the use of Big Data tools for sales and inventory forecasting and two stored procedures for SQL in predictive and explanatory analysis. The paper identifies various transactions and potential sales transactions that can be stored in the database, potential business rules, and database solutions. It also discusses the critical nature of a database administrator's role in IT industry organizations and security vulnerabilities and measures to minimize them.

Running head: STORE DATABASE MANAGEMENT 1
Database Manager for Store Department
Name
Institution
Course
Date
Introduction

STORE DATABASE MANAGEMENT 2
The database technology has attracted attention to the current businesses. It is commonly
used in large organizations in the storage and transfer of data such as the employee's details;
names, salary, and also the company's product information. The area, however, demands a high
level of knowledge and skills in database designing rather than its operations. A more
professional database designer is more likely to design a database that is easy to maintain. Most
of the organizations have specialization groups of database administrators specialized in
designing and maintaining databases.
The purpose of the research paper is to identify various transactions and probably the
departmental store and the potential sales transaction that can be stored in the database for easy
retrieval when the demand arises or for the update. I will design potential database rules and the
diabase solutions that can be used in the departmental store in performing various tasks such as
the data retrieval and updates. As usual, a database requires relations between the entities for
easy access or transfer of the data, and therefore the database will use the Crow's Foot notation in
showing the relationships. The Crows Foot notation is the most common tools used in showing
many relationships by use of graphical symbols (Clifford, & Bhandari, (2015). The research will
also consider other external sources to identify how the retail stores use the database in
maintaining their customers' information. It will also be essential to determine how such retail
stores retain their customers and increase sales by use of a well-designed database. Moreover,
cloud computing employs the use of a database, and, therefore, the research will also consider
how the data vendors design such a database.
The second part of the research paper will calculate the appropriateness of the use of the
distributed DBMS and identify the best techniques that should be considered in optimizing its
operation in an organization. This may include the use of current technology in designing an

STORE DATABASE MANAGEMENT 3
easier database in its maintenance. In this database, fives departments will be generated and two
examples of how uncommitted data and lost updates may occur. The database will also identify
the concurrency control factor vital within the current multiuser environment to ensure any
execution of transactions within such an environment is valid. Due to the security threats on the
database information, the research will also venture into the internet to determine the recovery
and backup solutions and any other security threats to the database that could be applied to the
department (Selinger, 2017).
CIS 515 Week 10 Dis 1
Information professionals with adequate skills are required to maintain the business
continuity of database services in an organization. Identify and assess at least three
professional skills that would be required to be successful database administrators.
According to Sumathi, & Esakkirajan (2017), there are various professional skills
required for a database administrator such as:
 Knowledge about the database itself.
 Knowledge of database query.
 Knowledge of database design.

STORE DATABASE MANAGEMENT 4
Give your opinion on what the critical nature of a database administrator's role is to IT
industry organizations. Support your answer with specific examples of functions and
responsibilities they perform.
The database administrators help in enhancing data communication. They are tasked with
the responsibility of storing and retrieving data from the database. This data is used to run on the
same or different computer.
They ensure that all the information can be accessed at any time. The administrators are
tasked with the responsibility of running queries on the data stored in the SQL database to extract
information that may be hard to find (McClure, & Kruger 2014, May).
The best database model leads to a more effective function of the industry. The database
designer determines what should be stored in the database and entities to interrelate.
CIS 515 Week 10 Dis 2
Give three security vulnerability and classify each with being technical, managerial,
cultural, or procedural. Provide your rationale.
According to Alhazmi, Malaiya, & Ray (2015), databases are vulnerable to security
threats such:
Stolen database back up. It is a managerial vulnerability of database from both the
external attacker and those within the corporation. The insiders steal the archives including the
database backups.
Deployment Botches. It is a procedural vulnerability that is caused as a result of
ignorance of the installation procedure.
Database feature abuse. It is a technical vulnerability and is associated with a missing
standard feature in the database.

STORE DATABASE MANAGEMENT 5
Suggest specific actions that could be performed to minimize the vulnerabilities. Describe
how often these measures should be performed and the order in which each should be
performed.
The stolen database backups can be mitigated by encrypting the archives frequently to
ensure inaccessibility by both the external and internal attackers within the corporation.
Before the installation, the database should be tested, and the correct procedure of
installation be followed at any time the database is installed. Failure to the test and ignorance in
installation process exposes the database to security attacks (Bireley, 2012).
Any simple errors such as failing to consider certain feature in the database by designers
expose the database to attacks. This can be mitigated by considering all the necessary features
during the time of implementation to keep the database away from any attack.

STORE DATABASE MANAGEMENT 6
Assignment
Transaction in the department store and potentials sales stored in database
Due to the current market activity which may project the increase in sales, there is the
need to design a comprehensive relational database for several new stores. In the mission to
design a useful database, several factors should be weight depending on the nature of the
database and its intended functions. After keenly analyzing all the transaction or activities to be
performed in every store department, a list of major entities can be developed to help ensure the
success of the transaction from one department to another. According to Starkey (2012), the
transactions that are performed in each department include.
Every core entity in the database will include other attributes whose information is
relevant to that department and this will make it easier to display the data in a hierarchical nature.
The main attributes contained in each entity are as shown below:

STORE DATABASE MANAGEMENT 7
The key entities in the departments are vital in providing the essential information of the
transaction that takes place in each department. According to McHugh (2017), a store contains
various department at the simplest level each with vital information for the department, and it is
staffed by employees who provide the services related to database functions.
Customers – It sells the organizations' products to the customers and also provides
inquiries to the customers which is the primary transaction in a database. After identifying and
working out on the entities in each department and their attributes, business rules are then
implemented concerned with the organizations' sales transaction and storage of the information
(McCarthy, & Dayal, 2016, June).
Database – It aims at organizing and managing the information or data, but it also needs
to ensure that the designing technology used is easy to maintain and it appropriately fits its
objective.
Potential business rules and database solution in departmental store.
Before any rule of business is setup, an organization should make sure the database
design to be implemented is effective in the functionality of the intended purpose. It should
assist in reporting and visualizing the data by ensuring no correlating relationship designed to
allow proper analysis. According to Stonebraker, & Kemnitz (2013), the rules include:

STORE DATABASE MANAGEMENT 8
Rule 1: Various codes are programmed to direct specific activities in each department.
Due to a large number of stores that sells products, there are constraints that are associated with
the transactions, and therefore only the assigned codes in each store are permitted as valid.
Rule 2: The relationship constraints within the database will be vital in ensuring that each
of the four vendors has an equal chance of supplying a product and they will only be able to
supply at least one product at a time. The rule will still, hold even if there is legacy or
discontinued vendors or products.
Entity relationship using the Crow's Foot notation.
After identifying the rules that should be applied in the database implementation, the next
step is to design the relationship among the database entities (Lohman et al., 2012). This
relationship should also consider the type of the transaction that will be shared by the two or
more entities. The relationship between the entities will be the key determinant for the effective
functioning of the database such as the data transfer from one entity to another, and, therefore, it
requires accuracy and keenness. We can state that the products and services are sold to
customers, and the customers buy the products and services. However, this does not show each
cardinality's relationship.
For an instant, how many sales transaction are carried out in a single store? By following
this procedure, it will, therefore, be easy to identify all the relationships in the six entities. Since
in the database we have six entities, they will all relate to one another giving a total of 30
relationships. According to Hanson (2015, June), the relationships are shown in the table below:

STORE DATABASE MANAGEMENT 9
Based on the six entities by five relationships, then the relationship has been captured
successfully. Next is to determine the cardinality of the relationship among the entities and
represent it graphically to define the relationship clearly. According to Stonebraker, Rowe, &
Hirohama (2011), each relationship is expressed as;

STORE DATABASE MANAGEMENT 10
 One to One (1:1)
 1 to Many (1: N)
 Many to 1 (N:1)
 Many to Many (N: N).

STORE DATABASE MANAGEMENT 11
The relationships among the entities are shown below:
Use of Big Data tools for sales and inventory forecasting
The database plays a crucial part in data storage which can be used by the researchers in
predicting the future depending on the data. The presence of a database in an organization also
provides security to the stored information as each transaction is effectively logged and is only
accessible by the authorized person. Analyzing the data helps in identifying the most profitable

STORE DATABASE MANAGEMENT 12
customers and the frequencies at which the customers buy from the department (Gehani, &
Jagadish, 2015, September). This will also be an opportunity for identifying the weaker area and
therefore applying the best strategy to increase the sales volume. There is also access for the
information concerning the customers who have only purchased the products ones, and this will
help in identifying the reason behind the customers not revisiting the store so as to solve the
problem. Perhaps there may be either compelling competing officer or a bad customer
experience.
With the implementation of the database, the customers or sales related issues are
represented to the sales and marketing teams. This team in turn makes the decision concerning
the current customer stand in the business which is vital for the organization. The extraction of
the data for the analysis also requires the database administrators. The growth of an organization
means that the database will have to grow in size, therefore, presenting the information in many
proportions. Due to the extensive database data, it, therefore, becomes difficult in analyzing a
large amount of data using the conventional database management tools and thus the Big Data
tools are considered more effective (Stonebraker, 2012). Given the constant updating stream of
the information in each table and the live nature of the database, the Big Data tool is more
considered due to its ability in manipulating and extrapolating a large amount of data (Widom, &
Finkelstein, 2011, May). The Big Data tool such as the Jaspersoft's BI Suit helps in retrieving a
large amount of data from any SQL table, and, therefore, the tool will be used to retrieve and
collect the data in accordance to the organizations' requirements. The information retrieved using
the tool allows the presentation of the data in the form of graphs and PDF which accelerates the
analysis and presentation of the analyzed report compared to the conventional reporting methods
(Lamb, 2016).

STORE DATABASE MANAGEMENT 13
Two Stored procedures for SQL in predictive and explanatory analysis
The SQL stored procedure is also used in the analysis of the data. However, this
procedure requires the database specialist as compared to the other analysis that can be
performed by statisticians after being served with the company's data. The comprehensive Big
Data suite can also be used in addition to the SQL stored procedure hence completing the SQL
statement (Ture, 2017). For an instant, we can write a certain procedure for a promotional
activity that has been conducted in an area to retrieve the information related to the customer in
that location such as making any purchase or changing the purchase frequency based on a
wildcard entry. The SQL codes are as shown below:
SQL code:
Create procedure loccheck @address nvarchar 25)
as
Select *
From dbname.customer.address
Where address like @address + ‘%'
Select *
From dbname.sales.customer
Where customer = @customer
Go
The SQL code will lead to the availability of a LocCheck which is a Stored Procedure.
The result can be reported at any time of run by performing the procedure via the below

STORE DATABASE MANAGEMENT 14
command.
EXEC LocCheck @Address = ‘ ‘
The result is returned in the above code if the desired location to search is entered in the
quotes. According to Engelhardt, & Ashley (2013), an alternative that encompasses the
transaction and date can also be used in an attempt to identify the trends in the finance sector
(e.g. it can be in the form of month end, annual).
The command of the Stored Procedure:
Create procedure SalesDate
@BeginningDate DateTime.
@Endingdate datetime
as
Select
Sales.date
Sales.products
Where
Sales.date between @beginningdate and @endingdate
Go
For the information to be called and displayed, the EXEC command should be used in the
same way as the previous stored procedure. The first Stored Procedure in the above examples is
considered the best as it can relate the planned and ongoing promotional and marketing activities
that are planned (Yamakawa, Gokan, & Ohtsuji, 2012). It can be used as a method in the
marketing and promotional for obtaining immediate feedback.
Pricing structure and security considerations for cloud- hosted solution for database

STORE DATABASE MANAGEMENT 15
Some expenditures accompany the implementation of a database cloud-hosted solution.
The table below shows the pricing structure and the estimated cost that is required in the
complete implementation of the cloud-hosted database. It also includes an analysis of different
cloud computing types together with their security considerations.
How the cloud-based services fit on a mobile user environment and the technical
requirements for data integrity.
To determine the best vendor among the cloud-based service, it is important to consider
the price of the security affordability. Therefore, the Amazon EC2 has the highest security
affordable price in addition to the (PaaS) and the (IaaS). The other cloud-based services have less
affordable security price and also lower prices. The Amazon Web Service (AWS) is more
significant as it helps in reducing the cost incurred in the information technology in many ways.

STORE DATABASE MANAGEMENT 16
With the AWS such it is possible to pay only for the resources consumed by replacing the
upfront capital expenses with the low variable cost (Keohane, 2016).
Appropriateness of the distributed DBMD structure and its optimization techniques.
Theoretically, all the requirements for the proposed database will be met. As mentioned
earlier, due to the increased number of transactions as the increased customers, the database will
require constant connectivity due to the rapid increase in the size of the database. The larger
database will have to be split across multiple systems through the distributed database
management system (Abiteboul, 2014). Here, all the entities will be combined and represented as
one database. The single database configuration will be optimized for Scalability and High
Availability. Moreover, it will be optimized for being backed up by the best practices and
disaster recovery mechanisms.
Due to some of the risk associated with the database, it is important to set up a mirrored
database with failover by utilizing the SQL server built-in technologies (Goldman, & Widom,
2015). This helps in the database backup for the disaster recovery purpose. It also helps in the
continued functioning of the other databases if the main database server becomes unavailable or
stops functioning as a result of various reasons. According to He, & Naughton (2016), a further
Setup of a copy of the Backup Logs along with the central database regular copies reduces the
chances of data loss in case of failure of the functioning of a database.
Two examples on how uncommitted data and lost updates may occur.
It is essential to understand that due to the frequencies of the data transmission, it is
possible for the loss of the updates or the data not being committed successfully. This case is
commonly experienced in the big databases that encompass multiple-user environments.
According to Florescu, & Kossmann (2010), a data loss occurs when two or more transactions in

STORE DATABASE MANAGEMENT 17
the databases choose the same row, and by being unaware of any ongoing transaction and based
on the original value, the data gets updated. The two or more different transactions from different
originalities get updated as one update and thus overwriting the previous update. Therefore, data
loss is said to have occurred.
There is also a scenario where the second transaction of data in the databases can select a
row that contains another transaction with the ongoing update. Due to the interruption of the data
being updated, the update can be affected, therefore, changing the original transaction. The
uncommitted data is consequently said to have occurred (McClure, & Kruger, 2014, May).
To ensure all the transactions are executed correctly, it is vital to alleviate the issues of data loss
and uncommented data through the use of SQL statement, row version. It helps in issuing a
stamp to each database table as a unique identity that ensures automatic updates and accurate
recording of data. Besides, with the need to set the concurrency model as required, the Java
Database Connectivity (JDBC) can also be used. For an instant, the Transparent Data Encryption
is used a JDBC drive to secure the database by setting the concurrency as its mechanism. This
also results in the encrypted database that ensures the authentication mechanisms and the
relevant keys are safely stored. Such procedures ensure that without the encrypting key, it cannot
be possible to view or access the data even with the physical access of the database (Chan, &
Cheung, 2011).
The concurrency control factors that ensures execution of instructions area valid in a
multi-user environment.
CONCUR_SS_SCROLL_LOCKS
The read-write model of the database setting helps in maintaining row integrity by
locking each row during each transaction. This ensures that the update of the row can be done

STORE DATABASE MANAGEMENT 18
once the previous operation in the database has been completed and the row unlocks (Lee, Low,
& Wong, 2012, October). The JDBC contains available varying models of concurrency, but this
can, however, be updated depending on the requirements of the business. Although there are
security threats related to the database, the methods should address the technical concern
associated with the successful capture of each transaction and the database. It must be
remembered that the database contains an extensive data related to a specific customer such as
the pricing and also the employee's data that is vital to the organization and so its protection is
required.
In ensuring the access, control, and permissions to the database are not compromised by
being accessed by an unauthorized person, various mechanisms can be applied. One of the best
and the easiest method is to ensure that the database only contains the relevant information and
the access to the data are only controlled by a specific person (Neuman, 2014). Any alteration to
the data should be highly under secure and to the only authorized and well qualified and
experienced person. The organization should also ensure that it adheres to the government
regulatory by allowing data auditing.
Option for availing disaster management function in a retail environment database.
The disaster recovery options include:
 Log shipping.
 Database mirroring
 Failover clustering
Security threats associated with database in management store and the appropriate
measures to minimize them

STORE DATABASE MANAGEMENT 19
According to Shulman, & Co-founder (2016), the retail industries have some common
security threats such as: Spam/spim/spit, Virus, Spyware, Spoofing, Botnet, Keystroke logging
(keylogging), Worm, Adware, Trojan horse, Denial-of-service attack (dos attack), and the
Blended threat. There are some regulators and the standard bodies forcing for the vital security
requirements to avoid penalties imposed by for example the credit card providers. This will help
in minimizing the security threats.

STORE DATABASE MANAGEMENT 20
References
Abiteboul, S., Quass, D., McHugh, J., Widom, J., & Wiener, J. L. (2014). The Lorel query
language for semistructured data. International journal on digital libraries, 1(1), 68-88.
Alhazmi, O. H., Malaiya, Y. K., & Ray, I. (2015). Measuring, analyzing and predicting security
vulnerabilities in software systems. Computers & Security, 26(3), 219-228.
Bireley, W. R., Curran, L. R., Lin, F. L., Lobo, A. B., & Wong, J. Q. (2012). U.S. Patent No.
6,115,703. Washington, DC: U.S. Patent and Trademark Office.
Chan, M. Y., & Cheung, S. C. (2011). Testing Database Applications with SQL Semantics.
In CODAS (Vol. 99, pp. 363-374).
Clifford, P., & Bhandari, R. (2015). U.S. Patent Application No. 10/488,592.
Engelhardt-Cronk, K., & Ashley, D. (2013). U.S. Patent Application No. 10/034,090.
Florescu, D., & Kossmann, D. (2010). Storing and querying XML data using an RDMBS. IEEE
Data Engineering Bulletin, Special Issue on, 1060(22), 3.
Gehani, N. H., & Jagadish, H. V. (2015, September). Ode as an Active Database: Constraints
and Triggers. In VLDB (Vol. 91, pp. 327-336).
Goldman, R., & Widom, J. (2015). Dataguides: Enabling query formulation and optimization in
semistructured databases. Stanford.
Hanson, E. N. (2015, June). Rule condition testing and action execution in Ariel. In ACM
SIGMOD Record (Vol. 21, No. 2, pp. 49-58). ACM.

STORE DATABASE MANAGEMENT 21
He, J. S. K. T. G., & Naughton, C. Z. D. D. J. (2016). Relational databases for querying XML
documents: Limitations and opportunities. Very Large Data Bases: Proceedings, 25,
302.
Keohane, S. M., Mcbrearty, G. F., Mullen, S. P., Murillo, J., & Shieh, J. M. H. (2016). U.S.
Patent No. 6,983,352. Washington, DC: U.S. Patent and Trademark Office.
Lamb, C., Landis, G., Orenstein, J., & Weinreb, D. (2016). The ObjectStore database
system. Communications of the ACM, 34(10), 50-63.
Lee, S. Y., Low, W. L., & Wong, P. Y. (2012, October). Learning fingerprints for a database
intrusion detection system. In European Symposium on Research in Computer
Security (pp. 264-279). Springer, Berlin, Heidelberg.
Lohman, G. M., Lindsay, B., Pirahesh, H., & Schiefer, K. B. (2012). Extensions to Starburst:
Objects, types, functions, and rules. Communications of the ACM, 34(10), 94-109.
McCarthy, D., & Dayal, U. (2016, June). The architecture of an active database management
system. In ACM Sigmod Record (Vol. 18, No. 2, pp. 215-224). ACM.
McClure, R. A., & Kruger, I. H. (2014, May). SQL DOM: compile time checking of dynamic
SQL statements. In Software Engineering, 2005. ICSE 2005. Proceedings. 27th International
Conference on (pp. 88-96). IEEE.
McClure, R. A., & Kruger, I. H. (2014, May). SQL DOM: compile time checking of dynamic
SQL statements. In Software Engineering, 2005. ICSE 2005. Proceedings. 27th International
Conference on (pp. 88-96). IEEE.
McHugh, J., Abiteboul, S., Goldman, R., Quass, D., & Widom, J. (2017). Lore: A database
management system for semistructured data. SIGMOD record, 26(3), 54-66.

STORE DATABASE MANAGEMENT 22
Neuman, D. (2014). Designing databases as tools for higher-level learning: Insights from
instructional systems design. Educational Technology Research and
Development, 41(4), 25-46.
Selinger, P. G., Astrahan, M. M., Chamberlin, D. D., Lorie, R. A., & Price, T. G. (2017, May).
Access path selection in a relational database management system. In Proceedings of the
1979 ACM SIGMOD international conference on Management of data (pp. 23-34).
ACM.
Shulman, A., & Co-founder, C. T. O. (2016). Top ten database security threats. How to Mitigate
the Most Significant Database Vulnerabilities.
Starkey, J. A. (2012). U.S. Patent No. 8,224,860. Washington, DC: U.S. Patent and Trademark
Office.
Stonebraker, M. (2012). The integration of rule systems and database systems. IEEE
Transactions on Knowledge and data Engineering, 4(5), 415-423.
Stonebraker, M., & Kemnitz, G. (2013). The POSTGRES next generation database management
system. Communications of the ACM, 34(10), 78-92.
Stonebraker, M., Rowe, L. A., & Hirohama, M. (2011). The implementation of
POSTGRES. IEEE transactions on knowledge and data engineering, 2(1), 125-142.
Sumathi, S., & Esakkirajan, S. (2017). Fundamentals of relational database management
systems (Vol. 47). Springer.
Ture, M., Krishnaprasad, M., Davis, M., Hsin, C., Bhavsar, M., Koide, H., ... & Bhatkar, S.
(2017). U.S. Patent Application No. 11/680,558.
Widom, J., & Finkelstein, S. J. (2011, May). Set-oriented production rules in relational
database systems. In ACM SIGMOD Record (Vol. 19, No. 2, pp. 259-270). ACM.

STORE DATABASE MANAGEMENT 23
Yamakawa, S., Gokan, H., & Ohtsuji, A. (2012). U.S. Patent No. 6,357,015. Washington, DC:
U.S. Patent and Trademark Office.