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.
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
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:
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).
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.
Relationship of Entities
Figure1: ERD
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).
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.
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.
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.
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.
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. EuropeanSecurity, 23(4), pp.625-628.
OsamaAlmasri, O. (2013). Improving Security Measures of E-LearningDatabase. IOSRJournal of Computer Engineering,10(4), pp.55-62.
Stephens,R. (2014). Beginningdatabase design solutions. 4th ed.Indianapolis, IN: Wiley Pub.
Teorey,T., Lightstone, S. and Nadeau, T. (2012). Databasemodeling & design. 3rd ed.Amsterdam: Elsevier.
Vanden Hoven, J. (2011). Database Management System and XML: Interchangeof Data. InformationSystems Management,19(2), pp.94-96.
Vanden Hoven, J. (2011). Key Trends in Database Management Systems —Part 2. InformationSystems Management,17(2), pp.89-91.