Technical Paper: Database Administrator for Department Store

Technical Paper: Database Administrator for Department Store

CIS 515 – Strategic Planning for Database Design

Strayer University

Introduction

Dennis Express Store (DES) has as of late extended their retail business effectively. This Store maybe 80yrs old business. Following this extension, DES has confirmed that there is a requirement for an endeavor wide social database, in light of the desire for an expansion in deals. Due to the profoundly particular and basic advances that should be viewed as when structuring database, including however not restricted to arrangement, exactness, and honesty of information bolster for reports and numerous others, Database Consulting (DBC) has been errand with the planning and usage of it. As the database heads for DES, DBC will be in charge of making and keeping up a venture wide database framework that will hold the deals and stock information for the association. The planned database will help tasks in supporting its new business advertising methodology. An arrangement will be made and submitted for assessment by DES to make this database and build up a plan that lines up with the item deals activities of the association.

Potential Sales and Department Store Transactions

The retail locations deals exchanges will be held in the fundamental departmental database situated in the home office of DES. Notwithstanding which sort of exchange happens, the information will go straightforwardly into the DB and will be put away on it. There are numerous presumptions that should be possible so as to begin structuring the DB, however the principle ones are:

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

• All exchanges will be put away.
• All stock got will be recorded.
• A client can put in a few requests.
• A store can have a few requests.
• An request can contain numerous items.
• Store data will be spared.
• Product subtleties must be recorded.
• Order data will be held.
• Customer data will be put away.

DB Solution and Potential Business Rules

• Location of the actual premise where the transaction takes place
  • Store (Store_ID, Storename, Store_Loc)
• People that work in the stores and interact with the customers
  • Employee (Emp_ID, Emp_FN, Emp_LN, Emp_Add)
• The companies who supply stock to the stores
  • Vendor (Vend_ID, Comp_Name, Comp_Loc, Comp_phn_num, Pro_ID, Cost)
• People who enter the store and buy the products
  • Customer (Cust_ID, Cust_FN, Cust_LN, Cust_Add, Cust_phn_num)
• The products that are available in store for purchase.
  • The physical process of selling a product(s).

Business rules are frequently indicated just verifiably be application to express client characterized requirements; they are not another way to deal with characterize or compel a few parts of the business (Demuth, Hussmann, & Loecher, 2001). In paper, a business rule is announcements depicting a part of the firm intend to feature the business structure. For the proposed database we have the following rules:

The influenced elements by the business rules are the ones recently depicted: Store, worker, seller, client, item, and deals.

• Rule 1: Different items are being sold in different parts of the organization. Six stores altogether, which implies that a field imperative in the database should be available putting away an assigned code to each store area.
• Rule 2: Sellers should supply no less than one from every item. This would be set up whereby a solitary record in the Vendor table would should be identified with somewhere around one record in the Product table.

Entity Relations

Information is the emblematic portrayal of actualities with significance. A reality without importance is of no esteem, and a reality with the wrong significance may cause inconvenience. In this manner, the focal point of information the board must be on the significance related with the information . Thus the significance of the Entity Relationship Diagram (ERD), it is a graphical portrayal of all relations in the database, it means the importance everything being equal or elements in it. These connections are spoken to in an ERD on Figure 1. It incorporates all information components, elements, connections, (for example, 1: 1, 1:M, M: N), and cardinalities for the retail establishment database.

Figure 1: Dennis Express Stores (DES) Database ER Diagram

Retail Stores & Databases Uses: Retaining Customers/Increasing Sales

As per RetailNext, the present examination innovation enables retailers to comprehend customer conduct better and create bits of knowledge that drive organization deals and quantifiably enhance the client encounter. The outcome is an expansion in client maintenance and dedication, fortifying connections that assemble long haul esteem. They do as such by upgrading and reinforcing of the focal components affecting best line income development for physical retail and store execution. These key components are driving activity into your stores, boosting open doors for change, adjusting the staffing to movement, conveying an in-store encounter that reverberates with your client, and customizing the shopping knowledge (RetailNext, 2016).

Big Data

With the assistance Big Data instruments, retailers can outflank their friends fiscally while enhancing consumer loyalty. Investigation assume a pivotal job in the information stream conspire inside a retail association. Information is assembled through numerous gadgets in a retailer store, including POS machines, yet since it is a tremendous measure of information; it is trying for a retailer to settle on key choices dependent on it without executing huge information examination systems. The essential goal is to change over this crude or huge information into a significant examination, normally in the methods for reports. The information can be converted into deals and profitability revealing, determining, stock administration, advertise bin investigation, item liking, client bunching, client division, distinguishing pattern, recognizing regularity and understanding concealed examples for misfortune anticipation and store organization. A portion of the investigations that can be accomplished by the social event enormous information are:

Regardless of whether the intention is to give a more astute shopping background that impacts the obtaining choice of clients, to drive extra income, or to convey customized pertinent continuous offers to clients, Big Data offers chances to retailers to remain in front of the opposition (Van Rijmenam, 2014).

• Predictive Analysis: encourages a retail association to improve its basic leadership controls by taking a gander at the future with systematic unbending nature. Prescient examination holds the way to exploiting these open doors with the end goal that retailers can expand their capacity to estimate their client’s conduct and plan appropriately. Information investigation abilities cover numerous conceivable examinations, utilizing measurable programming, for example, SPSS, SAS, Excel and Minitab.
• Inventory Management: causes a retailer to keep least stock without coming up short on stock. A scientific group utilizes the intensity of cutting edge factual programming, supercomputers, and refined science to give noteworthy bits of knowledge to the client. Progressed scientific systems, recipes and factual strategies are utilized to anticipate the future interest for an item. This investigation considers the effect of occasion, regularity and pattern impact.
• Business Analysis: gets bits of knowledge from the organized information, for example, deals and efficiency revealing.
• Data examination: helps in the basic leadership process with operational productivity, spares costs by giving amazing arrangements and encourages adaptable working models and best in class information security.

Explanatory or Predictive Analytics

Coming up next is a SQL put away system that will help deals partners perform informative or prescient examination. The procedure, named TOP_Store, makes a report of which departmental store the most noteworthy deals, allowing the board to see which store is pitching more in contrast with alternate stores. It utilizes the capacity CURSOR, which gets records from the Orders table and Store table and sorts them by Store_NM. In the event that there are no records discovered, it will show the yield as TOPStore%NOTFOUND; and it will leave the LOOP. Figure 2 demonstrates a print screen of the working SQL code for system TOP_Store.

CREATE OR REPLACE PROCEDURE TOP_Store IS

Ord_Amt Orders.Ord_Amt%TYPE;

Store_NM Store.Store_NM%TYPE;

CURSOR TOPStore is

SELECT Store_NM, Sum(Ord_Amt) as Ord_Amt

FROM Orders ORD,Store ST

WHERE ORD.Store_ID=ST.Store_ID

GROUP BY Store_NM

ORDER BY 2 DESC;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘————————‘);

DBMS_OUTPUT.PUT_LINE(‘STORE_NM Tot_Ord_Amt ‘);

DBMS_OUTPUT.PUT_LINE(‘————————‘);

OPEN TOPStore;

LOOP

FETCH TOPStore INTO Store_NM,Ord_Amt;

EXIT WHEN TOPStore%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(Store_NM||’ ‘||Ord_Amt);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘————————‘);

CLOSE TOPStore;

END;

Figure 2. Working SQL code for procedure TOP_Store

A similar sort of process can be made to decide the best items sold in the stores. Coming up next is a SQL put away methodology, named TOP_Products, creates a report of which item is the most astounding merchant. It grants the executives to look at the deals all through all items in all stores. Essentially to the Top_Store, it utilizes the capacity CURSOR and sorts them by Prod_NM. Figure 3 demonstrates a print screen of the working SQL code for methodology TOP_Products.

CREATE OR REPLACE PROCEDURE TOP_Products IS

Prod_ID Product.Prod_ID%TYPE;

Prod_NM Product.Prod_NM%TYPE;

Prod_Cnt int;

CURSOR TOPProduct is

SELECT Prod_NM,count(ORD.Prod_ID) as Prod_Cnt

FROM Product PROD, Orders ORD

WHERE PROD.Prod_ID = ORD.Prod_ID

GROUP BY Prod_NM

ORDER BY 2 desc;

BEGIN

DBMS_OUTPUT.PUT_LINE(‘——————–‘);

DBMS_OUTPUT.PUT_LINE(‘PROD_NM Prod_Cnt ‘);

DBMS_OUTPUT.PUT_LINE(‘——————–‘);

OPEN TOPProduct;

LOOP

FETCH TOPProduct INTO Prod_NM,Prod_Cnt;

EXIT WHEN TOPProduct%NOTFOUND;

DBMS_OUTPUT.PUT_LINE(Prod_NM||’ ‘||Prod_Cnt);

END LOOP;

DBMS_OUTPUT.PUT_LINE(‘————————‘);

CLOSE TOPProduct;

END;

Figure 3. Working SQL code for procedure TOP_Products.

Which Procedure is More Valuable?

Evaluating the estimation of explicit methodology is extreme, since esteem is a relative term, as well as in light of the fact that we should gauge and dissect it in the logical casing of a predetermined situation. For instance, as an administrator of a specific store, I can discover the methodology Top_Products exceptionally important, not just on the grounds that it will enable me to figure out which is my most astounding vender, yet in addition since I can contrast my numbers and different stores, and strategize to knock up offers of the items that are not moving also. Then again, as a provincial supervisor, accountable for every one of the stores, both Top_Stores and Top_Products are profitable, not just on the grounds that I can monitor deals in each store, however I can likewise decide to either move the stock from one store to the next, or to decide eventually if an item is moving in a specific district. I trust that the two systems grow similarly significant, explicitly, on the grounds that business examination should be possible through every one, except joining them the two yields an increasingly exact and precise situation per district, subsequently settling on them deliberately profitable for basic leadership with regards to business development in the region.

Cloud Computing Services and Vendors

Cloud Services

Distributed computing is a rising business framework that guarantees to dispense with the requirement for keeping up costly processing offices by organizations and foundations alike (Iosup, et al., 2011). Using virtualization and asset time-sharing, mists fill in as a solitary arrangement of physical assets an extensive client base with various necessities (Iosup, et al., 2011). Among the numerous kinds of distributed computing administrations conveyed inside or by outsider specialist organizations, the most well-known are (Iosup, et al., 2011):

•Software as a Service (SaaS): Software authorizing and conveyance demonstrate in which programming is authorized on a membership premise and is midway facilitated (Butler, 2013).

•Infrastructure as a Service (IaaS): gives virtualized registering assets over the Internet (Butler, 2013).

•Platform as a Service (PaaS): gives a stage enabling clients to create, run, and oversee applications without the unpredictability of building and keeping up the foundation regularly connected with creating and propelling an application (Butler, 2013).

In the aggressive market that we live these days, numerous sellers guarantee been progressively secure, quicker and dependable than the other (Butler, 2013). Enormous names in registering have diverse stages they offer to fulfill the interest of cloud administrations to both private companies and huge organizations (Butler, 2013). Microsoft, Google, IBM and Amazon are among those enormous names offering all the diverse flavors out distributed computing administrations (Butler, 2013). DES is generally little contrasted with different contenders, and despite the fact that there is plan for more development, it will profit in assessing littler and similarly anchored and solid suppliers or merchants (Butler, 2013). We will think about two suppliers and the diverse administrations they offer (Butler, 2013).

Pricing

First GigeNET Cloud, among different highlights offer Auto DDOS Protection, IPv6 bolster and pre-arrangement, flexible IP’s and private VLAN administrations, Windows, Linux and FreeBSD Support, superior ZFS based SSD stockpiling, and Amazon Compatible APIs (GigeNET Cloud, 2016). Table 1.1 compares the different price options for GigeNET Cloud services.

Then again, M5 Internet Hosting offers cloud facilitating administrations through the offering of an IaaS cloud (M5 Internet Hosting, 2016). As per their offering depictions, M5 Cloud enables you to make virtual machines inside minutes, utilizing an advantageous web interface or an open API (M5 Internet Hosting, 2016). Virtual machines and circles can be hot-moved, empowering us to lead equipment support with zero downtime to you (M5 Internet Hosting, 2016). Furthermore, M5 Cloud’s Standard Primary stockpiling is as of now multiple times as quick as Amazon EBS! It additionally offers custom format creation, stack adjusted bunching, SSH key administration, and the utilization of private systems administration between hubs (M5 Internet Hosting, 2016).

Table 1.2 compares the different price options for M5 Cloud Services.

Table 1.2 M5 Cloud Services Pricing

Security

Enormous enterprise and officials in them tend just to think in numbers, which arrangements are most financially savvy is constantly present in deciding to which benefit it winds up actualizing (Takabi, Joshi, and Ahn, 2010). The truth of the matter is that cost is dependably a worry, however with regards to distributed computing; a standout amongst the most investigated occasions must be security (Takabi, Joshi, and Ahn, 2010). Both security and protection issues present as the key barricade to the quick selection of distributed computing (Takabi, Joshi, and Ahn, 2010). The purpose behind this worry is on the grounds that as a rule, the association or client does not have neighborhood access to the databases, per the meaning of cloud benefits, the databases dwell somewhere else, and commonly the security isn’t duty of the clients, yet the suppliers (Takabi, Joshi, and Ahn, 2010). A portion of the contemplations that should be tended to before picking the cloud are (Takabi, Joshi, and Ahn, 2010).

•The merchant has a safe portal condition (Takabi, Joshi, and Ahn, 2010).

•The merchant’s entryway is ensured by a legitimate outsider (Takabi, Joshi, and Ahn, 2010).

•The merchant’s security act is upheld by arrangements and procedures (Takabi, Joshi, and Ahn, 2010).

•The seller’s security act is bolstered by direct specialized controls (Takabi, Joshi, and Ahn, 2010).

•The seller will advise of security episodes (Takabi, Joshi, and Ahn, 2010).

•The seller will help with security examinations and legitimate disclosure (Takabi, Joshi, and Ahn, 2010).

•Scalability (Takabi, Joshi, and Ahn, 2010).

•Data Restoration and Recovery (Takabi, Joshi, and Ahn, 2010).

Distributed Database Management System (DDBMS)

•Data is put away in various hubs of the framework (Tamer Özsu and Valduriez, 1996).

• A conveyed database (DDB) is a gathering of different, coherently interrelated databases disseminated over a PC arrange (Tamer Özsu and Valduriez, 1996). A dispersed database the board framework (DDBMS) is at that point characterized as the product framework that allows the administration of the dispersed database and makes the circulation straightforward to the clients (Tamer Özsu and Valduriez, 1996). There are numerous points of interest of having dispersed DBMS versus a concentrated DBMS, yet numerous arrangements with the way that the information can be put away in different places as once (Tamer Özsu and Valduriez, 1996). Obviously, that represents a circumstance where the support of the database can get confused, and accompanies extra difficulties to the extent the mix and improvement of all hubs in an appropriated framework (Tamer Özsu and Valduriez, 1996). A portion of the benefits of having a circulated database are:

•It is consistent to the client, paying little respect to where the information is put away, locally or remotely (Tamer Özsu and Valduriez, 1996).

•Data from various areas can be joined and refreshed (Tamer Özsu and Valduriez, 1996).

•There is a higher security on an appropriated framework (Tamer Özsu and Valduriez, 1996).

•It additionally can be gotten to, refreshed and kept up over the system (Tamer Özsu and Valduriez, 1996).

•If one of the hubs comes up short, the honesty is kept up (Tamer Özsu and Valduriez, 1996).

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 .

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 Updates and Uncommitted Data Scenarios

• 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.

There are specific scenarios that can be described in DES 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 . 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:

Backup and Restoration Recovery Solutions

• 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.

For DES 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:

Database Security Threats

• 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 ; database backups can be done within the graphical user interface provided by Oracle and can be restored in case of a database crash.

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 DES.

Security Threats

Security Mitigation Methods

• 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.

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.

Conclusion

• 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.

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.

Reference

Butler, B. (2013). PaaS Primer: What is platform as a service and why does it matter? A sit-down with Sacha Labourey, founder and president of PaaS company CloudBees. Retrieved from Network World: http://www.networkworld.com/article/2163430/cloud-computing/paas-primer–what-is-platform-as-a-service-and-why-does-it-matter-.html

Demuth, B., Hussmann, H., & Loecher, S. (2001). OCL as a Specification Language for Business Rules in Database Applications. In B. Demuth, H. Hussmann, S. Loecher, M. Gogolla, & C. Kobryn (Eds.), ≪UML≫ 2001 — The Unified Modeling Language. Modeling Languages, Concepts, and Tools (Vol. 2185, pp. 104-117). Springer Berlin Heidelberg. doi:10.1007/3-540-45441-1_9

Dooley, K. (2002). Designing Large-scale LANs. O’Reilly.

GigeNET Cloud. (2016). About GigeNET. Retrieved from GigeNET: https://www.gigenetcloud.com/

Imperva. (2015). Top Ten Database Security Threats. Retrieved from Imperva: http://www.imperva.com/docs/wp_topten_database_threats.pdf

Iosup, A., Delft, Ostermann, S., Nezih Yigitbasi, M., Prodan, R., Fahringer, T., & Epema, D. (2011). Performance Analysis of Cloud Computing Services for Many-Tasks Scientific Computing. IEEE Transactions on Parallel and Distributed Systems, 22(6), 931 – 945. doi:10.1109/TPDS.2011.66

Li, Q., & Chen, Y. (2009). Entity-Relationship Diagram. In Q. Li, & Y. Chen, Modeling and Analysis of Enterprise and Information Systems (pp. 125-139). Springer Berlin Heidelberg. doi:10.1007/978-3-540-89556-5_6

M5 Internet Hosting. (2016). IaaS Cloud. Retrieved from M5 Hosting: https://www.m5hosting.com/services/iaas-cloud.php

Noabeb , L. (2015). Multi-User Applications. Retrieved from Databasedev: html

Oracle. (2016). Oracle Recovery Manager (RMAN). Retrieved from Oracle: http://www.oracle.com/technetwork/database/features/availability/rman-overview-096633.html

RetailNext. (2016). Boost Retail Performance and Improve the In-store Experience. Retrieved from RetailNext: http://retailnext.net/en/webinar/boosting-retail-performance-leveraging-technologies-to-increase-sales-and-improve-the-in-store-experience/

Stearns, R., Lewis, P., & Rosenkrantz, D. (1976). Concurrency control for database systems. 17th Annual Symposium on Foundations of Computer Science (pp. 19-32). Houston, TX, USA: IEEE. doi:10.1109/SFCS.1976.12

Takabi, H., Joshi, J., & Ahn, G.-J. (2010). Security and Privacy Challenges in Cloud Computing Environments. IEEE Security & Privacy, 8(6), 24-31. doi:10.1109/MSP.2010.186

Tamer Özsu, M., & Valduriez, P. (1996, March). Distributed and parallel database systems. ACM Computing Surveys (CSUR), 28(1), 125-128.

Van Rijmenam, M. (2014). Think Bigger: Developing a Successful Big Data Strategy for Your Business. Fayetteville, GA, USA: AMACOM Div American Mgmt Assn.