Database Administrator for Department Store

DatabaseAdministrator for Department Store

InstitutionAffiliation

DatabaseAdministrator for Department Store

Informationsystems provide an integral approach to business operationsmanagement. In the wake of the continued use of technology and theneed to attain efficiency and reliability, businesses need toimplement information systems to manage their operations. One of thefundamental components of information systems is the databasesystems. A database system denotes the primary storage of datacomponents in an organization. A database is a collection ofinformation that is organized to allow easy access, management andupdate of the information whenever a need arises. In the modernsociety a company without a database system and enhanced securitystandards will likely fail to compete effectively.

1. Potential Sales and Department Store Transactions

Thecompany deals with sales data and inventory data. Thus, the companywill have departmental store database that encompasses retail salesdata collected from the store sales transactions, and the inventorydata associated with each retail store. The sales process commenceswhen a customer makes a purchase by bringing the desired product tothe counter or store register. The personnel operating the store thenscan the individual items to be purchased with a barcode reader.

Assumptions

Inorder to complete the case study effectively, there are a fewassumptions made and they include: the company has 5 differentdepartmental stores in five locations products are normally receivedfrom the vendors the company sales its products from the differentretail stores in the region sales people sale the products in thestores the database system stores all data associated with thetransactions a single store has the capacity to register multipleorders a single customer can purchase multiple products or makemany orders and a single order can encompass more than one product

PotentialStore Transactions

Peggedon the assumptions adopted, the following are the potential storetransactions that can be stored in the database system:

• The system must be able to maintain all the information associated with the customers

• The system must maintain information associated with the stores

• The system must store and allow the processing of order information

• Product information must be stored, accessed and manipulated in the system

EntitiesDerived from Transactions

Thetable below elucidates the entities that can be derived from thetransactions that will be stored in the database and maintained bythe company:

Entity	Field	Description	Data type and format
Store	Store_Name Store_Location Store_Contact Store_Address	Name of the Store Location of the store Contact of the store Address of the store	Varchar (30) Varchar (30) Number (10) Varchar (50)
Customer	Cust_Name Cust_Address Cust_Contact Cust_ID	Number of the customer Address of the customer Customer contact Identification of customer	Varchar (20) Varchar (30) Number (10) Number (10)
Product	Prod_Numb Prod_Nam Prod_Catg Prod_Price Prod_Qnty	Product number Product Name Category of the product The price of the product Quantity of the product	Number (10) Varchar (50) Varchar (10) Currency Number (7,3)
Orders	Ord_ID Store_ID Prod_ID Cust_ID QTY Ord_Date Ord_Amt	Identification of the order Store identification number Product identification code Customer identification number Quantity of the product ordered The date of the order Amount of the order	Varchar (10) Number (10) Number (10) Number (10) Number (7) Number (7,3)

Table1: Transaction Entities

2. Database Solution and Potential Business Rules of the Sales Transactions of the Department Store

Abusiness rule can be described as a statement that describes ordepicts an aspect of a business. It is intended to ensure that thebusiness structure is effectively monitored and controlled at thesame time. Thus, in this departmental store, there are a wide rangeof rules that can be adopted and some of the major and most importantones include:

1. Retail Market Basket Analyzer

Retailerswill get better insights into their operations by relying on themarket basket data. They will be able to understand the customershopping process using significant tools that eclipse the value ofthe data. This is possible by putting basket level insights in thehands of the primary decision makers in the company. The role of thedecision makers is to ensure that they turn the data into useful andactionable recommendations that will spur organizationalprofitability (Van den Hoven, 2011).

2. Customer Loyalty and its Management

Inthe modern market environment, it is paramount for an organization tounderstand its customers and ensure that it adopts worthwhilestrategies that will spur loyalty. The loyalty programs are meant toensure that the company retains a higher proportion of its customersin each business cycle. The company must ensure that it gives a highproduct satisfaction threshold. At the same time, customers wantenjoyable, stress free shopping experience, great atmosphere withquality and excellent customer service (Van den Hoven, 2011). Trustis integral in the operations of the store and this means that itmust have a proper and consistent return policy.

Thus,some of the business rules that need to be adopted in order toimprove the store’s operations include: understand the customers:the company must understand its customers and clearly get what theywant at all times keeping proper financial records: good recordskeep the company posted on its progress, thus, ensures properpolicies inclined towards growth and development control inventory:all the retail stores are required to manage their inventoryproperly buy and price for profit: always offer better values in abid to attract more customers learn from others: there are a widerange of companies that have successfully implemented superiorsystems and managed to fulfill the needs of their customers(Stephens, 2014). Such companies offer credible lessons to the retailstores on fulfillment of the needs of the customers. Reliance on theinsights the give is critical in shaping the direction of thecompany.

3. Relationship of Entities

Figure1: ERD

4. Best Practices in the use of Database Systems by Retail Stores

BigData use in Forecasting Sales and Inventory of Department store

Inthe modern society characterized by the continued use of the internetand information systems, businesses are continuously leveraging ondata analytics to make viable decisions. Retailers collect essentialinformation that explains the preference of the customers, thepurchase trends, product demand, and the specific needs of thecustomers in accordance with any innovations attained by anorganization. Big data analytics plays a significant role in retailstores based on the massive information collected by decision makersfrom the database systems. Essentially, retailers generate millionsof data at data points through the POS machines. The role of the ITdepartment in the retail store is to convert the raw data intomeaningful end information that aid strategic decision making (OsamaAlmasri, 2013).

Throughthe data retailers can make use of sales and productivity reports,forecasting, inventory management, product affinity, customersegmentation and identification of market trends. Some of theessential analyses from the big data sediments include: salesanalysis, predictive analysis, inventory management, promotioneffectiveness, and demand forecasting. A good example on how big datacan be used in forecasting is illustrated in situations whereretailers collect customer data within a period of time and plot agraph or use algorithms to observe the trends (Osama Almasri, 2013).

SQLStored Procedures that use SQL functions

Thereare two main procedures that were proposed and they include: findingthe store with the highest number of sales and finding the highestsold products from all the stores in the company. The two proceduresare important analytical approaches to provision of reliableinformation that will spur decision making in both the short and longrun periods. The SQL codes can be depicted as shown below:

Storewith the Highest Sales

SELECTStore_Name, Sum (Ord_Amt)

FROMStore_Name

GROUPBY Store_Name

ORDERBY 2 DESC

HighestSold Products

SELECTTOP

Product_Nam

Count(*)

FROMProducts

INNERJOIN Production. Product p

ONsold.ProductID = p.ProductID

GROUPBY Product_Nam

ORDERBY COUNT (*) DESC.

TheSQL statements provide a viable approach to analyze specific retailinformation relating to sales of the products.

Opinionon Which of the SQL Statements is the Best

Asper my opinion both reports for the top most stores and the top soldproducts are essential to the retailer. From the output from the twoSQL statements, management can be able to review and make any formsof modifications to other stores to ensure that sales are increased.As far as the highly sold products are concerned, management will beable to know which of the products are performing poorly and which ofthe products are performing well. At the same time, they will knowwhich product to increase in the stores (Osama Almasri, 2013).

5. Database Vendors Providing Cloud Computing Services

CostsInvolved or the Pricing Structure in Implementation of Cloud HostedSolutions

Thetwo main cloud service providers considered in the analysis includeIBM and Oracle. These are some of the largest cloud service vendorsin the market. Their different pricing structures are illustrated inthe figures below:

Figure2: Oracle Pricing

Figure3: IBM Pricing Structure

Basedon the information gathered from the two companies, it is importantto note that the types of systems adopted, time period accessibility,processing speeds, storage capacity and the overall support levelsdictate the pricing strategies of cloud solution vendors.

Securityand Pricing Considerations

Beforeopting for any cloud based consideration, it is crucial to focus onthe security and pricing factors. The security factors for a cloudbased solution include: business critical operations, businesscontinuity and recovery plan for both the vendor and the operatingcompany, data backup plans, network connectivity strategies, outagesand their impact on the business, correlation to the CIA Triad(confidentiality, integrity, and availability), scalability issuesand the data restoration procedures (Stephens, 2014).

Rankthe cloud services options of Software as a Service, Platform as aService and Infrastructure

Cloudcomputing offers immense benefits including saving on costs andenhancing business operations. There are a wide range of potentialsecurity risks that arise from the use of cloud computing. Theserisks rely on the sensitivity of the data stored and the processingof the data. Some of the benefits of cloud based services include:provision of new competitive opportunities, reduction of overalloperating costs, improvement of business performance, and reductionof the carbon footprint among other related benefits.

Infrastructureas a Service (IaaS): this is a type of cloud based solution thattakes into perspective the provision of hardware services thatincludes the memory, CPU, network, and disk space. In this case thevendor may opt to share the same hardware services with the clientsbased on the agreements. This is possible by making use of multipletenants through virtual infrastructure. The operating software andassociated software applications are maintained by the client and thehardware components maintained by the vendor. Risk Space Cloud is agood example of IaaS (Teorey, Lightstone and Nadeau, 2012).

Platformas a Service (PaaS): in this case a vendor provides infrastructure asa service. Included is also the application software andother-related components. The approach enables customers to use thevendor infrastructure to carry out their operations using theprograms supported by vendors. Google App is a good example of PaaS.

Softwareas a Service (SaaS): vendors use their cloud infrastructure and cloudplatforms to provide essential services to the customers. Goodexamples are the emails that are used to provide users with anenvironment to share collaboratively. In this case the customers onlycontrol few aspects of the software such as settings.

SQLData Service (SDS): cloud service providers provide data storage anddata management features over the cloud. Hosted Data Management:database administrators are provided to handle administrationservices that include transitional database backup, import and exportof databases, and data storage.

Inorder to ensure that data integrity is harnessed on the cloud, bothvendors and clients need to ensure that they use encryptionstrategies. Further, sharing between the cloud service providers andthe customers must be managed by ensuring that there are additionalsecurity software components like firewalls and antivirus programs toscan information accessed or shared at all times for anyvulnerabilities.

6. Distributed DBMS Structure

DistributedDBMS structure is an essential approach of enhancing systemperformance and concurrent usage of the system. At the same timedistributed DBMS brings about quick responses whenever access andretrieval of the data is brought about. Moreover, they are importantin situations that involve huge data components. Further, data inthese structures is arranged in a logical order which makes it eachto access and enhances the distribution of the data among variousdatabase nodes (Teorey, Lightstone and Nadeau, 2012). Some of theadvantages of the distributed DBMS structure include: reliability,transparency in distribution, efficiency, and autonomy of nodes.

7. Lost Updates and Uncommitted Data

Scenario1

Inthe event that there is a high market demand and the products need tobe updated by a sales executive officer in charge of placing anorder. In this case the officer will place a request of the requiredproduct from the five different stores at the same time. Such anaction leads to the occurrence of concurrency issues that lead to adeadlock. This occurs when the data is not processed and updated asrequired.

Scenario2

Anotherscenario is evidenced in systems in the generation of sales reports.In this case the managers from each of the five stores are able tosee the data and are required to update it appropriately. In asituation where the managers update the same information at the sametime, the issue of concurrency and corruption of data may occur.

8. The concurrency control factors that can be used to ensure valid execution of transactions within the current multiuser environment.

Dataconcurrency denotes a situation where users are able to access anduse the databases at the same time without any complications.However, in a bid to ensure that concurrency works as required,organizations must ensure that they protect the data effectively andadopt strategies that will enhance reliability and integrity of thedata (Stephens, 2014).

Thetwo important strategies that can be adopted to foster the integrityof the data include: row level lock and the table level lock. The rowlevel lock encompasses two approaches and they include the datamanipulation lock that prevents applications from updating the rowand are only released after the successful completion of the databaseoperations the data dictionary language lock plays a crucial role inpreventing alterations of the structure of the data components in atable. As compared to a row lock system, the table level lockingsystem denotes a situation where the whole table is locked as anentity.

9. Backup and restoration recovery solutions and database security threats

Optionsto Provide Disaster Management Functions

Ina bid to prevent losses or failures, there are few solutions that canbe provided as backup and recovery of the database on systems.

RecoveryManager: this is a tool that enhances the integration of sessionsthat run on Oracle server in a bid to perform backup and recoveryactivities and maintain repository of historical data.

Traditionaluser managed backup and recovery strategy: this is a strategy thatcan make use of VERITAS NetBackup or legato networker software andput the information on a tape media. Differential backups can then beconducted on the databases and put on tapes sent to offsitelocations. From the two options, it is imperative to note that therecovery manager is the preferred solution as it can conduct the samebackup and recovery procedures available on the user managedapproaches easily and also provides a common interface to enhanceinteraction with the system (Archer, 2014). The types of backupscovered include: full backup, incremental backup, and on demandbackup.

SecurityThreats

Unusedand excessive privileges: users can abuse the privileges they aregiven in accessing the database system. This is more so if they aregiven more privileges. In a bid to prevent such security threats itis always proper to segregate duties to offer proper databasemanagement strategies.

SQLInjection: These types of attacks occur when attackers use maliciouscodes and programs to access the database systems. When the maliciouscodes are inserted information can be accessed or retrieved byunauthorized persons. In a bid to prevent such attacks it isimportant to use firewalls, segregate duties, encrypt the data, andensure that security systems are updated regularly (Osama Almasri,2013).

Malware:this is a common form of attack in the modern society where attackersintegrate different forms of attacks such as phishing, viruses, andworms. These are malicious programs developed with intent ofinfiltrating a system and they take advantage of any vulnerabilitythat may exist. Prevention of malware requires the use of additionalsecurity systems like antivirus programs, malware and constant updateof the systems (Osama Almasri, 2013).

Otherattacks include the denial of service, improper management ofsensitive data and limited security training among system users.Other strategies to protect the systems include monitoring andbackup, constant system audits, evaluation of the systems andblocking of vulnerabilities, and reliance on expert knowledge toenhance protection.

Conclusion

Therise of big data concepts is anchored on the proper implementation ofdatabase systems. It is through these storage provisions thatcustomers are able to access organizational information andbusinesses able to access, store and manipulate customer data. Inorder for businesses to benefit from database systems, they must takeinitiatives of considering their operations and aligning systems tomeet their specific needs. The company also needs to enhance itssecurity architecture in a bid to ensure that it meets the securitystandards and protects sensitive information.

References

Archer,C. (2014). The security governance of regional organizations (Globalinstitutions)/European security: the role of regionalorganizations.&nbspEuropeanSecurity, 23(4), pp.625-628.

OsamaAlmasri, O. (2013). Improving Security Measures of E-LearningDatabase.&nbspIOSRJournal of Computer Engineering,10(4), pp.55-62.

Stephens,R. (2014).&nbspBeginningdatabase design solutions. 4th ed.Indianapolis, IN: Wiley Pub.

Teorey,T., Lightstone, S. and Nadeau, T. (2012).&nbspDatabasemodeling &amp design. 3rd ed.Amsterdam: Elsevier.

Vanden Hoven, J. (2011). Database Management System and XML: Interchangeof Data.&nbspInformationSystems Management,19(2), pp.94-96.

Vanden Hoven, J. (2011). Key Trends in Database Management Systems —Part 2.&nbspInformationSystems Management,17(2), pp.89-91.