Er diagram for departmental store management system

Running Head: DB ADMINISTRATOR FOR DEPARTMENT STORE 1

DB ADMINISTRATOR FOR DEPARTMENT STORE 16

Technical Paper: Database Administrator for Department Store

Introduction

Sugar Hill Department Store (SHDS) have recently expanded their retail business successfully. Following this expansion, SHDS have determined that there is a need for an enterprise-wide relational database, because of the expectation of an increase in sales. Because of the highly specialized and critical steps that need to be considered when designing aa database, including but not limited to deployment, accuracy, and integrity of data support for reports and many others, Best Database Consulting (BDBC) has been task with the designing and implementation of it. As the database administrators for SHDS, BDBC will be responsible for creating and maintaining an enterprise-wide database system that will hold the sales and inventory data for the organization. The designed database will help operations in supporting its new business marketing strategy. A plan will be created and submitted for evaluation by SHDS to create this database and establish a design that aligns with the product sales initiatives of the organization.

Potential Sales and Department Store Transactions

The retail stores sales transactions will be held in the main departmental database located in the headquarters of SHDS. Regardless of which type of transaction takes place, the data will go directly into the DB and will be stored on it. There are many assumptions that can be done in order to start designing the DB, but the main ones are:

· All transactions will be stored.

· All merchandise received will be recorded.

· A customer can place several orders.

· A store can have several orders.

· An order can contain many products.

· Store information will be saved.

· Product details must be recorded.

· Order information will be retained.

· Customer information will be stored.

With the preliminary data gathered, here is the list of entities that will be needed to create the database:

· Location of the actual premise where the transaction takes place

· People that work in the stores and interact with the customers

· The companies who supply stock to the stores

· People who enter the store and buy the products

· The products that are available in store for purchase.

· The physical process of selling a product(s).

DB Solution and Potential Business Rules

“Business rules are often specified only implicitly be application to express user-defined constraints; they are not a new approach to define or constrain some aspects of the business” (Demuth, Hussmann, & Loecher, 2001). In paper, a business rule is a statement describing a portion of the firm plan to highlight the business structure. For the proposed database we have the following rules:

· Rule 1: Multiple products are being sold in various branches of the company. Six stores in total, which means that a field constraint in the database needs to be present storing a designated code to each store location.

· Rule 2: Vendors should supply at least one from each product. This would be established whereby a single record in the Vendor table would need to be related to at least one record in the Product table.

The affected entities by the business rules are the ones previously described: Store, employee, vendor, customer, product, and sales.

Entity Relations

Data is the symbolic representation of facts with meaning. A fact without meaning is of no value, and a fact with the wrong meaning may cause trouble. Therefore, the focus of data management must be on the meaning associated with the data (Li & Chen, 2009). Hence the importance of the Entity Relationship Diagram (ERD), it is a graphical representation of all relations in the database, it translates to the meaning of all components or entities in it. These relationships are represented in an ERD on Figure 1. It includes all data elements, entities, relationships (such as 1: M, 1:1, M: N), and cardinalities for the department store database.

Figure 1: Sugar Hill Department Stores (SHDS) Database ER Diagram

Retail Stores & Databases Uses: Retaining Customers/Increasing Sales

According to RetailNext, today's analytics technology empowers retailers to understand shopper behavior better and develop insights that drive company sales and measurably improve the customer experience. The result is an increase in customer retention and loyalty, strengthening relationships that build long-term value. They do so by enhancing and strengthening of the central elements impacting top line revenue growth for physical retail and store performance. These key elements are driving traffic into your stores, maximizing opportunities for conversion, aligning the staffing with traffic, delivering an in-store experience that resonates with your customer, and personalizing the shopping experience (RetailNext, 2016).

Big Data

With the help Big Data tools, retailers can outperform their peers financially while improving customer satisfaction. Analytics play a crucial role in the data flow scheme within a retail organization. Data is gathered through many devices in a retailer store, including POS machines, but since it is an immense amount of data, it is challenging for a retailer to make strategic decisions based on it without implementing big data analytics techniques. The primary objective is to convert this raw or big data into a meaningful analysis, usually in the means of reports. The data can be translated into sales and productivity reporting, forecasting, inventory management, market basket analysis, product affinity, customer clustering, customer segmentation, identifying trend, identifying seasonality and understanding hidden patterns for loss prevention and store administration. Some of the analyses that can be achieved by the gathering big data are:

· Predictive Analysis: helps a retail organization to enhance its decision-making powers by looking at the future with analytical rigidity. Predictive analytics holds the key to taking advantage of these opportunities such that retailers can increase their ability to forecast their customer's behavior and plan accordingly. Data analytics capabilities cover many possible analyses, using statistical software such as SPSS, SAS, Excel and Minitab.

· Inventory Management: helps a retailer to keep minimum inventory without running out of stock. An analytical team uses the power of advanced statistical software, supercomputers, and sophisticated mathematics to give actionable insights to the customer. Advanced mathematical techniques, formulas and statistical methods are used to predict the future demand for a product. This analysis considers the impact of holiday, seasonality and trend effect.

· Business Analysis: helps to get insights from the structured data, such as sales and productivity reporting.

· Data analysis: helps in the decision-making process with operational efficiency, saves costs by providing high-quality solutions and facilitates flexible working models and state of the art data security.

“Whether the purpose is to provide a smarter shopping experience that influences the purchasing decision of customers, to drive additional revenue, or to deliver tailor-made relevant real-time offers to customers, Big Data offers opportunities to retailers to stay ahead of the competition” (Van Rijmenam, 2014).

Explanatory or Predictive Analytics

The following is an SQL stored procedure that will help sales associates perform explanatory or predictive analytics. The process, named TOP_Store, creates a report of which departmental store the highest sales, permitting management to view which store is selling more in comparison to the other stores. It uses the function CURSOR, which fetches records from the Orders table and Store table and sorts them by Store_NM. If there are no records found then it will display the output as TOPStore%NOTFOUND; and it will exit the LOOP. Figure 2 shows a print screen of the working SQL code for procedure TOP_Store.

Figure 2. Working SQL code for procedure TOP_Store

The same type of process can be created to determine the top products sold in the stores. The following is an SQL stored procedure, named TOP_Products, generates a report of which product is the highest seller. It permits management to compare the sales throughout all products in all stores. Similarly to the Top_Store, it uses the function CURSOR and sorts them by Prod_NM. Figure 3 shows a print screen of the working SQL code for procedure TOP_Products.

Figure 3. Working SQL code for procedure TOP_Products.

Which Procedure is More Valuable?

Quantifying the value of specific procedures is tough, not only because value is a relative term, but also because we will have to measure and analyze it in the contextual frame of a specified scenario. For example, as a manager of a particular store, I can find the procedure Top_Products highly valuable, not only because it will help me determine which is my highest seller, but also because I can compare my numbers with other stores, and strategize to bump up sales of the products that are not selling as well. On the other hand, as a regional manager, in charge of all the stores, both Top_Stores and Top_Products are valuable, not only because I can keep track of sales in each store, but I can also determine to either move the inventory from one store to the other, or to determine ultimately if a product is selling in a particular region. I believe that both procedures develop equally valuable, specifically, because sales analysis can be done through each one, but combining them both yields a more precise and accurate scenario per region, hence making them strategically valuable for decision making when it comes to business expansion in the area.

Cloud Computing Services and Vendors

Cloud Services

Cloud computing is an emerging commercial infrastructure that promises to eliminate the need for maintaining expensive computing facilities by companies and institutes alike. Through the use of virtualization and resource time-sharing, clouds serve as a single set of physical resources a large user base with different needs (Iosup, et al., 2011). Among the many types of cloud computing services delivered internally or by third-party service providers, the most common are:

· Software as a Service (SaaS): Software licensing and delivery model in which software is licensed on a subscription basis and is centrally hosted.

· Infrastructure as a Service (IaaS): provides virtualized computing resources over the Internet.

· Platform as a Service (PaaS): provides a platform allowing customers to develop, run, and manage applications without the complexity of building and maintaining the infrastructure typically associated with developing and launching an app (Butler, 2013).

In the competitive market that we live nowadays, many vendors claim been more secure, faster and reliable than the other. Big names in computing have different platforms they offer to satisfy the demand of cloud services to both small businesses and big corporations. Microsoft, Google, IBM and Amazon are amongst those big names offering all the different flavors out cloud computing services. SHDS is relatively small compared to other competitors, and even though there is plan for more growth, it will benefit in evaluating smaller and equally secured and reliable providers or vendors. We will be comparing two providers and the different services they offer.

Pricing

First GigeNET Cloud, amongst other features offer Auto DDOS Protection, IPv6 support and pre-configuration, elastic IP's and private VLAN services, Windows, Linux and FreeBSD Support, high-performance ZFS based SSD storage, and Amazon Compatible APIs (GigeNET Cloud, 2016). Table 1.1 compares the different price options for GigeNET Cloud services.

Table 1.1 GigeNET Cloud Services Pricing

Disk

Memory

Core(s)

Bandwidth

Price

20GB SSD

512 MB

1

333 GB/month

$14.95/Month

30GB SSD

1 GB

1

667 GB/month

$23.21/Month

40GB SSD

2 GB

1

1333 GB/month

$40.22/Month

60GB SSD

4 GB

2

2667 GB/month

$72.69/Month

80GB SSD

8 GB

4

53333 GB/month

$144.59/Month

On the other hand, M5 Internet Hosting offers cloud hosting services through the offering of an IaaS cloud. According to their offering descriptions, M5 Cloud allows you to create virtual machines within minutes, using a convenient web interface or an open API. Virtual machines and disks can be hot-migrated, enabling us to conduct hardware maintenance with zero downtime to you. “Plus, M5 Cloud’s Standard Primary storage is already six times as fast as Amazon EBS!” It also offers custom template creation, load-balanced clustering, SSH key management, and the use of private networking between nodes. (M5 Internet Hosting, 2016). Table 1.2 compares the different price options for M5 Cloud Services.

Table 1.2 M5 Cloud Services Pricing

Instance Size

RAM

CPUs

Min. CPU

$/hour

$/Month

M5 Small

2GB

1

650MHz

$0.016438356

$12

M5 Medium

4GB

2

1GHz

$0.049315068

$36

M5 Large

8GB

4

1GHz

$0.098630137

$72

M5 XLarge

16GB

4

2GHz

$0.197260274

$144

M5 2XLarge

32GB

8

2GHz

$0.394520548

$288

M5 4XLarge

64GB

16

2GHz

$0.789041096

$576

M5 8XLarge

120GB

16

2.6GHz

$1.578082192

$1,152

Security

Big corporation and executives in them tend only to think in numbers, which solutions is most cost-effective is always present in determining to which service it ends up implementing. The fact is that price is always a concern, but when it comes to cloud computing, one of the most scrutinized events has to be security. Both safety and privacy issues pose as the key roadblock to the fast adoption of cloud computing (Takabi, Joshi, & Ahn, 2010). The reason for this concern is because more often than not, the organization or customer does not have local access to the databases, per the definition of cloud services, the databases reside elsewhere, and many times the security is not responsibility of the customers, but the providers. Some of the considerations that need to be addressed before opting for the cloud are:

· The vendor has a secure gateway environment.

· The vendor’s gateway is certified by an authoritative third party.

· The vendor’s security posture is supported by policies and processes.

· The vendor’s security posture is supported by direct technical controls.

· The vendor will notify of security incidents.

· The vendor will assist with security investigations and legal discovery.

· Scalability

· Data Restoration and Recovery

Distributed Database Management System (DDBMS)

A distributed database (DDB) is a collection of multiple, logically interrelated databases distributed over a computer network. A distributed database management system (DDBMS) is

then defined as the software system that permits the management of the distributed database and makes the distribution transparent to the users (Tamer Özsu & Valduriez, 1996). There are many advantages of having distributed DBMS vs. a centralized DBMS, but many deals with the fact that the data can be stored in multiple places as once. Of course, that pose a situation where the maintenance of the database can get complicated, and comes with additional challenges as far as the integration and optimization of all nodes in a distributed system. Some of the advantages of having a distributed database are:

· Data is stored in different nodes of the system

· It is seamless to the user, regardless of where the data is stored, locally or remotely.

· Data from different locations can be joined and updated.

· There is a higher security on a distributed system.

· It also can be accessed, updated and maintained over the network.

· If one of the nodes fails, the integrity is maintained.

Lost Updates and Uncommitted Data – Five (5) Stores Generating Transaction

The issues when dealing with a multiuser environment are related to the fact that at any given time, two or more users can be accessing the same resources. Two of the most common are: data corruption or resource contention, and performance or speed. Data corruption deals with users accessing and changing data simultaneously, and it may result in corrupted data. It often happens when one user changes data, and another user overwrites it immediately after it has been modified. On the other hand, performance and speed issued can occur when a poorly coded and designed database runs slower as it processes more data and has more users accessing it. It is commonly known as the bottleneck effect (Noabeb , 2015).

In every multiuser environment, there is always the possibility of corrupt data as well as other problems like when updates are lost, and there is inconsistency at the data level.

· Lost Update: When two concurrent transactions are trying to update the same data element. The database cannot report on data that does not exist; hence, data is lost.

· Uncommitted Data: An uncommitted dependency reads data that has not been committed yet and is changed by other transactions. For example, accessing a row that is being updated by others; when a transaction reads the data of another transaction which has not been committed.

Lost Updates and Uncommitted Data Scenarios

There are specific scenarios that can be described in SHDS database proposal, having in consideration that there will be multiple stores accessing the centralized database.

Case 1; If there is a product that has a high demand amongst all stores, sales representative throughout the multiple sores can by placing orders and accessing the record of the database that maintains a general inventory amongst all stores. If all of the representatives access this data at the same time, there is a possibility of data been overwritten, resulting in data loss.

Case 2 ; In the management level, a general manager requests information to all store managers, for example, a sales report. The general management wants them to generate this report and update the centralized database. There will be uncommitted date if all store managers try to produce the report at the same time and try updating the records. The current record will get uncommitted when the other transaction takes place by the different managers.

Concurrency Controls for Valid Execution of Transactions

Concurrency control is that portion of a database system that is concerned with deciding what actions should be taken in response to requests by the individual application programs to read and write into the database (Stearns, Lewis, & Rosenkrantz, 1976). The most common concurrency control is locking of data. The idea behind this method is to lock the element or data that has been accessed by a user, to restrict any modification by another user when is accessed by the initial one. There are different levels of locking in Oracle:

· Database Level: The database level locks the entire database; it avoids the table data been accessed by multiple transactions.

· Table Level: Lock each table at a time; two operations can be performed concurrently on two separate tables; however only one transaction can execute on any given table.

· Row Level: prevents other processes from updating the record and locks one record at a time.

Backup and Restoration Recovery Solutions

For SHDS it is of the most importance to have a disaster recovery plan and proper backup and restoration recovery solutions in place. For this particular scenario, with the database been centralized, as database administrators, we need to avoid any single point of failure scenario. A single point of failure (SPOF) is a part of a system that, if it fails, will stop the entire system from working (Dooley, 2002). For our scenario, there are a few solutions for the backup and recovery of the database on the systems:

· Tape Backups: The data is backed up using tape media on a regularly scheduled basis, various levels of backups are done such as full level 0 backups, incremental backups, and on-demand backups. A new backup will be done whenever there is an upgrade of the Oracle database to a new version. It will be restored back to its original state if there are any issues with the new version.

· Server Clustering: Server clustering will provide for two or more systems built and configured; this will guarantee server redundancy, where if one of the systems goes down the data can be failed over to the other system and avoid and outage in the system and its availability.

· Oracle Recovery Manager (RMAN): provides a comprehensive foundation for efficiently backing up and recovering the Oracle database. It is designed to work intimately with the server, providing block-level corruption detection during backup and restore (Oracle, 2016); database backups can be done within the graphical user interface provided by Oracle and can be restored in case of a database crash.

Database Security Threats

There are many reasons that databases are attacked by intruders these days. When many people are accessing the data, the chances of data theft increases. In the past, database attacks were prevalent but were less in number as hackers hacked the network more to show it was possible to hack and not to sell proprietary information. Another reason for database attacks is to gain money selling sensitive information, which includes credit card numbers and personally identifiable information (PII) like Social Security Numbers. Many samples of security threads will be relevant to our database design. Following is a few examples of both security threats and mitigation methods that will need to be addressed when deploying the database for SHDS.

Security Threats

· SQL Injection: with a successful SQL injection attack someone could get unrestricted access to an entire database.

· Malware: an advance attack that blends multiple tactics, such as spear phishing emails by the intruders to penetrate organizations and steal sensitive data.

· Limited Security Expertise and Education: Internal security controls are not keeping pace with data growth and many organizations are ill-equipped to deal with a security breach. Often this is due to the lack of expertise required to implement security controls, enforce policies, or conduct incident response processes.

· Unmanaged Sensitive Data: Forgotten databases may contain sensitive information, and new databases can emerge in application testing environments without visibility to the security team.

· Denial of Service (DoS): a general attack category in which access to network applications or data is denied to intended users.

Security Mitigation Methods

I've always said that if there is a reason you want to protect data for been revealed, there must be someone trying to get their hands on it. In other words, it is better to be safe than sorry. There are six different categories of solutions to the security threats presented above. In general, all deal with applying proper database rights management, monitoring, and protection of all data present in the database.

· Discovery and Assessment: locate where database vulnerabilities and critical data reside.

· User Rights Management: identifies excessive rights over sensitive data.

· Monitoring and Blocking: protect databases from attacks, unauthorized access, and theft of data.

· Auditing: helps demonstrate compliance with industry regulations.

· Data Protection: Ensures data integrity and confidentiality.

· Non-Technical Security: instills and reinforces a culture of security awareness and preparedness.

Conclusion

The design of Sugar Hill Department Sores distributed database is a challenging one, but by following proper procedure to planning, designing and implementation of all presented concepts, the process will not be of high burden. The result of this steps will be a well-designed, reliable and secured database system that will meet all requirements presented by the stakeholders for Sugar Hill Department Stores. It will provide room not only for growth in the future, but also for meeting the parameters in the ever changing for of database design and data security.