Database Design for Departmental Store

The proposed department store database is intended to store all sales from any of the six stores. As a result, maintaining track of stock purchased from vendors, difficulties with the individual retail locations, and customer specific sales is required. The following assumptions are considered when designing the department store enterprise wide database:
A unified database will be kept for all retailers' transactions.
Several orders can be placed by a single consumer.
Different stores' transactions should be identifiable.
A single order may include many products.
All transactions must be recorded electronically and completed in real time.
Different users will have different rights on the data they access
The above assumptions will help in defining entities and rules that exist between them.
Identify the potential sales and department store transactions that can be stored within the database
Some of the transaction that the database can store include:
1. The database should store comprehensive records on order details such as the order number, the store id, and order particulars.
2. The database should track product purchases from vendors.
3. The database should also track user login activities so as to allow for audits. This implies that every transaction should be saved with a column showing the user who executed it.
4. The database should store a list of customer details and should not allow for duplication.
5. The database should store the list of store details including location, distinct code, and contact person.
Design a database solution and the potential business rules that could be used to house the sales transactions of the department store
Data modeling is important in ensuring a proper database that will offer a solution to the current departmental data problems is developed. According to Coronel and Morris, the design of a database is inclined towards how the said database structure is used in storing and managing end-user data. Therefore, a properly developed data model acts as a communication tool (2015). To develop a robust database solution, entities, attributes, relationships, and any constraints have to be defined.
An entity is a representation of an item, person, place, or event for which information is stored. In a database schemata, an entity is also referred to as a table. An attribute represents an entity's characteristic. A combination of multiple attributes forms a row of a table. An attribute is also called a column within a table. For instance, in the EMPLOYEE entity, Employee_name is an attribute of the EMPLOYEE entity and is meant to store the name of the employee
A relationship within a database implies how two or more entities associate with each other. Three major relationship types exist. A One-to-One relationship (1: 1) is an association where a record in one entity can only relate to one record in another entity. A One-to-Many relationship (1: M) is an association where a record in one entity can have multiple associated records in another entity. Lastly, a Many-to-Many relationship (M: M) implies an association in which two entities can contain multiple records relating to each other.
Database constraints are rules implicated on the data so as to ensure data integrity such as primary, foreign keys, data format and not null rules.

Business rules

A database designer needs to thoroughly understand an organization's activities in order to come up with unambiguous policies and procedures that are easily understandable by all members of an organization. Some of the business rules that are important in the design and development of the departmental store database include:
a) A customer may have many orders
b) An order is only generated by one customer
c) An Employee can handle many orders
d) An order is handled by only one employee
e) An employee has to exist in a department
f) A department cannot exist without an employee
g) A store has many orders
h) An order is only generated in one store
i) A product must be contained in a specific category
j) A category may have multiple products
k) A store may have multiple employees
Business rules are then translated into data models. The nouns within the business rules form the entities while verbs within the rules translate into relationships between the associated entities. For instance, from the business rule "a customer may have many orders", two entities, customer, and orders, are generated. The two are related by the verb, have.
The below table illustrates the various entities, attributes, and constraints that we can use in the design of departmental store database.
&emsp

Crow's Foot notation

The ER Model below illustrates the relationship between the entities to use in developing the departmental store database. It further proves the relationship type between the entities by utilizing the Crow's foot notation.

Research the internet for best practices of how retail stores use databases for retaining customers and increasing sales and completing:
The future of retail organization lies in customer retention and sales completion. The success of the departmental store lies in enforcing effective database marketing strategies.
i. How Big Data tools help in forecasting sales and inventory of the department store
Big Data involves tools that offer large storage for data and infrastructures necessary to store and query data in a convenient manner. When a retail store uses tools that can handle big data, they are able to perform analysis on customer lifetime value which helps in the store's retail marketing strategies (Coronel, 2013). Further, it is possible to cut on promotion costs since it becomes possible to predict the number of customers that would respond to promotions. Other notable analysis and analytics results harnessed from Big Data tools include:

Business analytics

Some of the analytics include sales forecasting, proper management of inventory, reporting on sales and productivity, customer clustering and segmentation, product affinity, trends and season identification, as well as understanding any hidden patterns so as to offer proper store administration.
Stream computing
Big data tools are meant to offer real time analytics so as to support decision making in real time. IBM defines stream computing as a means to efficiently deliver real-time process analytics on the ever changing data as well as provide a descriptive and predictive analysis in support of decisions in real-time (Connor et al, 2014).

Performance track

Successful businesses track their performance to ensure proper mastery of sales seasons and trends. The proper application of technology to the organization's strategic management process, it is possible to get a clear picture of current organization position with respect to the set performance indicators. According to Stubbs, retail organizations that utilize big data tools and strategies increase their margins by above 60%. Additionally, the retailers boost the productivity of employees by 1% (2014).
Data analysis
Correct analysis of data leads to correct decisions resulting in cost saving and operational efficiency through quality solutions as well as facilitation of flexible models of work and mechanisms for data security. A well trained analytical automates the process of data cleansing, processing, and ensures recurring reports (Connor et al, 2014).

Predictive analytics

When data is analyzed properly, organizations predict their future position, customer buying patterns, and ensures Just In Time delivery. Predictive analytics ensures a retail company makes decisions while considering their future position. The retailer is then able to forecast customer behavior. This analysis is helped through the integration of statistical programs such as Excel, SAS, SPSS, Minitab, among many others (Stubbs, 2015). ii. Propose two SQL stored procedures that use SQL functions to help sales associates perform explanatory or predictive analytics
Each of the procedures below has a separate purpose. The first procedure is meant to show the product that is fast moving by showing its sales while the second procedure shows the store with the highest sales.
a) SQL Stored Procedure to determine the highest sold product
create or replace PROCEDURE TOPITEMSOLD IS
PRODUCT_CODE PRODUCT.PRODUCT_CODE%TYPE
PRODUCT_DESCRIPTION PRODUCT.PRODUCT_DESCRIPTION%TYPE;
PRODUCT_COUNT INT;
CURSOR TOPITEM IS
SELECT pro.PRODUCT_CODE,pro.PRODUCT_DESCRIPTION,COUNT(SALES.PRODUCT_CODE) AS PRODUCT_COUNT
FROM PRODUCT PRO, ORDERLINE SALES
WHERE PRO.PRODUCT_CODE = SALES.PRODUCT_CODE
GROUP BY pro.PRODUCT_CODE, pro.PRODUCT_DESCRIPTION
ORDER BY 3 DESC;
BEGIN DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('PRODUCT_CODE PRODUCT_DESCRIPTION PRODUCT_COUNT')
DBMS_OUTPUT.PUT_LINE('--------------------');
OPEN TOPITEM;
LOOP
FETCH TOPITEM INTO PRODUCT_CODE,PRODUCT_DESCRIPTION,PRODUCT_COUNT;
EXIT WHEN TOPITEM%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(PRODUCT_CODE||' '||PRODUCT_DESCRIPTION||' '||PRODUCT_COUNT);
END LOOP; DBMS_OUTPUT.PUT_LINE('------------------------');
CLOSE TOPITEM;
END;
Output for the above procedure

b) SQL Stored Procedure to determine the highest selling store
CREATE or REPLACE PROCEDURE SALESBYSTORE IS
STORE_NO STORE.STORE_NO%TYPE;
STORE_NAME STORE.STORE_NM%TYPE;
TOTAL_SALES FLOAT;
CURSOR TOPSTORE IS
SELECT pro.STORE_NO,pro.STORE_NM,SUM(SALES.ORDER_AMOUNT) AS TOTALSALES
FROM STORE PRO, ORDERS SALES
WHERE PRO.STORE_NO = SALES.STORE_NO
GROUP BY pro.STORE_NO,pro.STORE_NM
ORDER BY 3 DESC;
BEGIN DBMS_OUTPUT.PUT_LINE('--------------------');
DBMS_OUTPUT.PUT_LINE('STORE_NO STORE_NAME TOTAL_SALES');
DBMS_OUTPUT.PUT_LINE('--------------------')
OPEN TOPSTORE;
LOOP
FETCH TOPSTORE INTO STORE_NO,STORE_NAME,TOTAL_SALES;
EXIT WHEN TOPSTORE%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(STORE_NO||' '||STORE_NAME||' '||TOTAL_SALES);
END LOOP; DBMS_OUTPUT.PUT_LINE('------------------------')
CLOSE TOPSTORE;
END;
 
Output for the above stored procedure
iii. Opinion on which of the above two ways offers greater value towards expanding business within the region.
Each of the above procedures is equally important since each provides different views on the sales position of the organization. The TOPITEMSOLD procedure is of help to the department store in determining which products make many sales. Similarly, the SALESBYSTORE procedure is meant to show the organization, which among the five retail stores makes the most sales. This helps the management to pool more resources on that specific store. However, in my opinion, the TOPITEMSOLD procedure for determining the product with most sales is more important than the specific store with more sales. In our above procedures, the most selling store only sold one product which was valued at a high cost while the most selling product cost little but more quantities were sold. As such, determining the product with more sales can further help in determining the product category and work towards either more promotion on that category or promoting products in the non-selling categories in order to increase their sales.
Database vendors that provide cloud computing services
a) Types of costs involved in implementing cloud-hosted database solution
The National Institute of Standards and Technology denotes cloud computing as an infrastructure that must provide rapid elasticity, measured service, on-demand self-service as well as broad network access. To implement cloud-hosted database solutions, an organization needs to consider some structured costs:
Software development and maintenance cost. A research conducted by Cornor, Cook, Porche, and Gonzales showed that most of the enterprise applications codes, their licenses, and future needs are not optimized for cloud and can therefore not be operated in virtualization. In addition, current application licenses are tied to specific hardware and can thus not operated in a distributed and cloud environment (2014).
Database options. Big data analytics poses the question of what database type is required and what data does such a database store. The cost of moving an enterprise program from a local storage to the cloud is dependent on the data and database involved. For instance, organizations running systems that are offer high transaction rates are implemented in Structured Query Language (SQL) running on MsSQL or Oracle. There is licensing costs associated with using Oracle database whose cost increases as the data size increases. Oracle is known to execute on single servers leading to scalability and size limitations. Such limitations on large systems has led to new file systems also called NoSQL databases in replacement of SQL. One such system is Hadoop which is open-source and allows parallel processing of large data sets (Cornor, Cook, Porche, & Gonzales, 2014).
Hardware and communications. Maintaining a data center is expensive and the costs not easy to estimate. This is because, the organization is at liberty of determining the number of virtual machines to use, the hardware type and refresh rates. In cloud systems, hardware infrastructure is already provided for by means of the resource.
Security and Privacy. Cloud programs security is a challenge since data centers are not in physical control of the organization seeking the cloud services. As such, organizations will always want to ask themselves what data can be allowed beyond their walls, and what is the costs of maintaining or letting out some of its data (Qusay, 2011).
Compatibility and Data Migration. This regards to data size, data kind, and data quality. Organizations have to understand cloud hosted solutions are not necessarily similar to locally hosted programs and migrating the data to cloud hosts may lead to dirty data or increased expenses in the effort to sanitize the data.
Personnel. Just like a traditional system, the manpower required in running a cloud program or system is similar to any other requirement for any other information system. However, in a cloud system, the cost of personnel is reduced since an organization does not have to hire all specialists for server maintenance as the cloud hosting company offers that.
Cross-Domain solutions and Classified computing. An organization has to ask to consider whether it requires the cloud hosting provider to support its communications, data, or both. Since data is unclassified, it may be possible for the cloud provider to handle while it may be risky for an organization to hand over its communications to the cloud provider. A solution to the above issue is to have CDS at the government facilities so as to step up or down information from and to the classified networks.
b) Analyze security considerations and pricing of the different cloud implementation types
Virtualization plays an important role in cloud computing since it is not possible to have cloud computing without virtualization. Applications hosted on the cloud must execute tough security measures through the use multiple complimentary defense levels that offer data protection in diverse means, circumstances, and from in-house and outside threats (Al-Roomi et al, 2013). A number of guidelines can be followed in depicting a security strategy.
i. Use filtering as an intermediate security layer between a data source and a tenant. Filtering is important when multiple tenants are accessing the same piece of a data.
ii. Assign user roles and domain groups to ensure that certain user groups access only certain functionality of a database or application (Lynch, 2011).
There exist different pricing models from cloud hosting service providers. The table below illustrates some of the commonly implemented pricing models.
Model of Pricing Approach used Fairness
Pay-as-you-go A Static price is placed by the service provider A customer may pay for more than what he needs
Subscription Static set price based on a predefined period of subscription A customer may over or underpay
Pay-for-resources Static price that is cost-based It is fair to both customer and service provider
Value-based pricing Pricing is dynamic based on the value perceived by the customers A fair approach to the producer as there is high revenue on each sold item
Cost-based pricing A dynamic model for adding profit above item cost Unfair to customers as it ignores their roles
Competition-based pricing A dynamic pricing set based on prices from other competitors Fair to consumers even though it does not consider them in the pricing
Customer-based pricing A dynamic approach where price is set based on what a customer is willing to pay Fair to customers as they are considered.
Hybrid based pricing It's both static and dynamic since the price is set based on job queue wait times. A fair model that ensures that even though prices change dynamically, the adjustments are within set static limits.

c) Rank the cloud services options of Software as a Services, Platform as a Service and Infrastructure as a Service in terms of functionality, mobility, and ability to offer distributed transaction concurrency. Compare how these services fit mobile users environment and determine technical provisions that are needed to ensure data integrity.
The model Software as a Service is essentially designed for users operating over the web. This model emphasizes much on managing security with the cloud service provider. The model is mostly used to implement customer relationship management services. SaaS is low-risk as it essentially deals with software and does not consider hardware or storage capacities. Mobile users can easily have access to these applications. The cloud provider handles the installation, maintenance, and upgrades of the program.
Another model is Platform as a Service that involves services and tools provided to ensure quick and efficient code development and application deployment. PaaS contains more applications specific to a given purpose for helping an organization build its customized services. PaaS is further a multi tenant offering ensuring that multiple companies may share a service. The organizations must thus prove their trust to their providers and other organizations they share source codes and applications (Al-Roomi et al, 2013).
The building blocks for cloud services is the Infrastructure-as-a-service model. IaaS comprises highly automated computing resources that are complemented by a robust cloud space and network capacity that is either metered, available on demand or self-provisioned. The providers of IaaS offer cloud servers and other resources by means of a dashboard and API. IaaS allows a client to develop and deploy web-scale applications, PaaS, and SaaS.
Evaluate whether the use of a distributed DBMS structure is appropriate and identify the optimization techniques that should be factored in to enhance the operations of the database in your design
The essence of having a distributed database system is to ensure performance through parallel execution where multiple users access same data distributed in different devices in order to ensure quick response and enhanced system performance.
Advantages of Distributed Database structure
•Performance improvement
• Extensibility in that it is possible to add new nodes
• Individual nodes are autonomous
• The user has no information of the physical location of the data.
• Multiple tables in different locations are joinable and updatable.
• Distributed databases offer high-security levels.
• The failure of one node does not compromise the integrity of the distributed database.
Provide at least two (2) examples of how lost updates and uncommitted data may occur, keeping in mind that five (5) stores will be generating transactions.
Multi-user environments allow for users to read and update the same piece of data concurrently. Some implementation problems include Lost updates and data inconsistencies due to uncommitted data.

Lost Update

It involves the loss of data after an update occurs. This can occur if two people access same data and each writes into the same data. One of the writes will be avoided leading to a lost update. Data is definitely lost. Concurrent transactions trying to update the same element of data in a database can lead to a lost update.

Uncommitted Data

This is a result of a transaction reading data that is open to another transaction and is not committed. A transaction may want to access data in a certain row whereby the row is in write mode by another transaction and the "write" process is uncommitted. As such, the transaction accessing the row will read data before it is committed by the other transaction and will lead to wrong figures being presented.

Lost Update Case Scenario

Consider the illustration below involving two concurrent transaction where one update product quantity by adding 100 and the other reduces the quantity by 30.
PRD_QTY= PRD_QTY +100
PRD_QTY= PRD_QTY -100
Time Transaction number Steps Value stored
1 T1 Read PRD_QTY 35
2 T1 PRD_QTY=35+100
3 T1 Write PRD_QTY 135
4 T2 Read PRD_QTY 135
5 T2 PRD_QTY=135-30
6 T2 Write PRD_QTY 105
Time Transaction number Steps Value stored
1 T1 Read PRD_QTY 35
2 T2 Read PRD_QTY 35
3 T1 PRD_QTY=35+100 135
4 T2 PRD_QTY=35-30 135
5 T1 Write PRD_QTY(Lost Update)
6 T2 Write PRD_QTY 105
Uncommitted Data Case Scenario
Consider the case below where one transaction update product quantity on hand by adding 100 then rollbacks while transaction two is updating the product quantity by subtracting 30.
PRD_QTY= PRD_QTY + 100 (ROLLBACK)
PRD_QTY= PRD_QTY - 30
Time Transaction number Steps Value stored
1 T1 Read PRD_QTY 35
2 T1 PRD_QTY=35+100
3 T1 Write PRD_QTY 135
4 T1 ***Rollback** 35
5 T2 Read PRD_QTY 35
6 T2 PRD_QTY=35-30
7 T2 Write PRD_QTY &5
Time Transaction number Steps Value stored
1 T1 Read PRD_QTY 35
2 T1 PRD_QTY=35+100
3 T1 Write PRD_QTY 135
4 T2 Read PRD_QTY(Read of uncommitted data) 135
5 T2 PRD_QTY=135-30
6 ;T1 ***Rollback** 35
7 T2 Write PRD_QTY 105
Determine the concurrency control factors that can be used to ensure valid execution of transactions within the current multiuser environment.
Every database transaction must maintain the AIDS properties namely: Atomicity, Isolation, Durability, and Serializability properties. Below are a number of methods to ensuring concurrency control.

Use of Locks

A lock guarantees that the current transaction is granted exclusive use of an item. The transaction is granted a lock before it accesses the data. The lock is only released upon transaction completion. Most DBMS enforce locking mechanism usually managed by a lock manager. Different lock levels exist such as database-level locks, table-level locks, page-level locks, and row-level locks. The locks are usually indicated by the lock granularity (Thomasian, & Alexander, 2010). Oracle allows for row level and table level locking.
Row Level Lock. This lock prevents another process from locking or updating a row until current transaction is completed or rolled back.
Table Level Lock. This lock involves a transaction locking and gaining exclusive right to a database entity. No other transaction has access to write to the table until the transaction completes operating on the table. Other processes can only read but not write to that table. However locks can lead to deadlocks.

Use of Time Stamping Methods

This method involves assigning a globally unique time stamp to every transaction in order to provide transaction order and to avoid conflicting operations. This method provides two properties namely uniqueness and monotonicity.

Use of Optimistic Methods

This approach assumes that most of the database operations never conflict. Thus, transactions are executed to completion without any restrictions. The approach has three phases namely read, validation, and write phase.
Research the Internet for backup and restoration recovery solutions and database security threats that could be applicable to the department store
a. Suggest at least two options that could be made available to provide disaster management functions for the database system within the retail environment
It is vital for the retail store to have business continuity and disaster recovery plans in the event a database fails or unprecedented overwrites occur. Oracle offers products towards database disaster recovery such as the Oracle Golden Gate and Oracle Data Guard. Other recovery options include:

Database Backup

RMAN is both command-line and GUI and interfaces with Oracle server sessions so as to perform backup and recovery activities on the database. It further maintains a history of previously performed backups.
b. Assess the types of security threats that may exist when managing the department store database and suggest measure(s) that can be performed to minimize these threats particular to retail.
The departmental store database is prone to many security threats some of which are expounded below:
i. Input Injection such as SQL injection and NoSQL injections. SQL injection involves the use of special characters in the SQL queries to unravel vulnerabilities in databases while NoSQL injection targets flat file platforms such as Hive or MapReduce (Bhargava & Lilien, 2004).
ii. Malware. In the event that the departmental stores are interconnected via the internet, the store's database may be exposed to advanced attacks over the internet. Infection in one of the networked devices makes the whole organization prone to the attack.
iii. Weak audit trails. Every database should be accompanied by automated database transaction recording to comply with regulations and to keep an audit of all transactions within a database (Coy, 1996).
iv. Lack of proper privilege management for database users.
v. Lack of password policies. It is important to enforce password expiry policies for users accessing data to avoid the use of same password for long.
vi. Exposure of backup storage media. The media used to store database backups should be kept safe from access by other users other than the specified persons.
vii. Employee negligence due to limited internal security controls.
Enforceable measures that can be utilized include the following:
i.Setting up correct database access user privileges to avoid excessive rights.
ii. Calculate risk scores based on vulnerability so as to institute mitigation measures.
iii. Periodically check for compromised endpoints and review existing control measures.
iv. Open only specific ports if a database is to be accessed over the internet.
v. Conduct automated auditing procedures that are not user controlled.
vi. Always educate the employees on measures to ensuring system and database security.
 

References

Al-Roomi, M., Al-Ebrahim, S., Buqrais, S., & Ahmad, I. (2013). Cloud Computing Pricing Models: A Survey. International Journal of Grid and Distributed Computing, 6(5), 93-106. doi:10.14257/ijgdc.2013.6.5.09
Bhargava, B., & Lilien, L. (2004). Vulnerabilities and Threats in Distributed Systems. Distributed Computing and Internet Technology, 146-157. doi:10.1007/978-3-540-30555-2_18
Connor, K., Cook, I. P., Porche, I., Gonzales, D., & Rand Corporation. (2014). Cost considerations in cloud computing.
Coronel, C., Morris, S., Rob, P., & Coronel, C. (2013). Database principles: Fundamentals of design, implementation, and management. Mason, OH: South-Western.
Coy, S. P. (1996). Security Implications of the Choice of Distributed Database Management System Model: Relational vs. Object-Oriented (Doctoral dissertation, University of Maryland). Retrieved from http://csrc.nist.gov/nissc/1996/papers/NISSC96/paper072_073_074/SCO_.PDF
Lynch, J. (2011, September). IBM Systems Magazine - Cloud's Promise. Retrieved from http://ibmsystemsmag.com/ibmi/trends/cloud-computing/cloud_consideration/
Qusay, H. F. (2011). Demystifying Cloud Computing. The Journal of Defense Software Engineering, 16-21.
Stubbs, E. (2014). Big data, big innovation: Enabling competitive differentiation through business analytics. Hoboken, NJ: Wiley.
Thomasian, & Alexander. (2010). Database Concurrency Control: Methods, Performance, and Analysis. Gardners Books.