Actions of Database Administrator for Department Store - Case Study Example

DATABASE ADMINISTRATOR FOR DEPARTMENT STORE Database administrator for department store A database that has been designed well should be able to support consistency of the entry as well as the retrieval of data. It should also be able to minimize the duplication of data within the tables of the database. The following transactions can be stored within the database: Number of the ordered products Price paid for those products that have been ordered. Date of the order The name of the customer The name of the product The name of the salesperson in charge of receiving the ordered product The following is an Entity Relationship Diagram representing the sales transactions of the department store: Big Data is the data that surpasses the capacity of processing of the usual database system. With the help of the Big Data’s tools and applications, spotting changes in the behavior of the buyers, as well as the patterns of the sales cycle at the micro and macro stages has been made possible. Noting changes in the performance of the sales representative makes it possible for appropriate training to be conveyed to the sales representative in order to boost its impact presently and in the future (Ault, 2003).This is the most efficient approach when compared to the other strategies. The Point of Sale System is the storage area for the Big Data. From this system, one can be able to draw what is being sold, how much a commodity is sold at, who your customers are, and when the most sales are made. One can be able to retrieve sales information for any period. With this information, one can be able to establish the optimal volumes of production of the organization’s products. Having a knowledge on the rate at which commodities are being sold and how it matches up to one’s forecast of sales, enables the management to raise the level of production on the commodity that sells faster than it was expected, thus minimizing the possibility of running out of stock. It can also enable the management to stop the production of the commodity that is moving at a slower rate. In an Organization whose management is based on the changes in the patterns of the business tends to have a powerful warning system that alerts the management early enough. This is a vital aspect for any flourishing business. This enables the relevant parties of the organization to make a fast and intelligent response (Mullins, 2002). In marketing, the Big data tools can be used to recognize the ways to help in making an improvement in the marketing ROI through the right changing alignment of the contents and programs with the changing expectations of the buyer. This knowledge also enables marketing to train the sales representatives on how to have a more successful and conversant type of dialogues with the prospects. There are two SQL stored procedures for performing predictive analytics. They include: 1. The DBMS_DATA_MINING: This package enables one to construct the models of data mining and to relate them to the new data. 2. The DBMS_PREDICTIVE_ANALYTICS: This package comprises of routines used for the predictive analytics, which is the automated version of the data mining. All the activities of mining are handled within through the use of predictive analytics procedure. With the DBMS_PREDICTIVE_ANALYTICS, expansion of the business within the region is possible. This package automates the process of data mining. This package also offers a fundamental tool that enables the simplification of the process of data mining for those users that do not have the data mining know how. It provides the outlined functions: EXPLAIN- Which ranks the attributes as per their influence order, explaining the target column. PREDICT- Which predicts an attribute’s value. The following is a DBMS_PREDIVTIVE_ANALYTICS SQL code: DBMS_PREDICTIVE_ANALYTICS.EXPLAIN (data_table_name IN VARCHAR, explain_column_name IN VARCHAR, result_table_name IN VARCHAR, data_schema_name IN VARCHAR DEFAULT NULL); (Bruni & International Business Machines Corporation, 2003) The following are the requirements necessary for the implementation of the cloud-hosted solution for a database: The General Assumptions: New licenses are required. A high level of availability needs to be achieved the project. The project will not make use of the Disaster Recovery Center, rather, high quality hardware with the same specifications as the Disaster Recovery Center will be used. The Premises Assumptions: New hardware, that is the servers are required, but they will continue running on the on hand infrastructure like the firewalls, routers, among others. The present network storage will be used by the project for storing the database. The same machine will be used to host the server of the database and the virtual machines of the IIS. Virtualization with the Windows server 2012 will be used. On top of the software and the hardware costs, there will be the cost of maintenance which is approximately 20%in a year. The Hosting Cost: In the event that a hosting provider is to be used as opposed to acquiring the hardware, the organization will be required to pay a certain amount of fee on a monthly basis. The organization will not be required to purchase the Operating System of the Windows server as well as the required hardware. However, the organization will be required to acquire the SQL server Enterprise Edition. The maintenance fee will be eliminated since this is catered for by the provider of the hosting. With this option of hosting, attaining high level of availability is easy as opposed to the organization hosting its database. The total cost for implementing the cloud- hosted solution for a database is approximately $730,745, with the subscription fee accounting for about 65% of the total cost of the solution. The following are the different cloud implementation types: The Private Cloud Infrastructure: This infrastructure is more secure as compared to the other infrastructures. This is made possible by the technologies used to develop the infrastructure. This infrastructure is however expensive to acquire due to the devices required to develop this infrastructure. The Public Cloud Infrastructure: This infrastructure is not as secure as that of the private infrastructure. This is because it is being accessed by many organizations. The database is fully secure in this infrastructure. It cost however is not as expensive as that of the private infrastructure, because the infrastructure is outsourced. Cloud Computing Infrastructure: This is the infrastructure that is offered by the third party provider and is based on the policy of paying as you use the model. The following are the different cloud services options: 1. The Software as a Service (SaaS): With the SaaS, the users are able to hire the applications which are ready for use. The fixed cost for acquirement is low and the obligations can either be long or short term. 2. The Platform as a Service (PaaS): In contrast to the SaaS, the PaaS does not deliver complete services to the users. Instead, it presents a platform for development that has an in-built cloud intelligence that eliminates the need of developers to concentrate on the underlying infrastructure. The developers are only required to build applications making use of the tools they are already conversant with, they then set up the applications to the provider of the PaaS. It is then the work of the Paas Provider to enable the primary cloud capabilities needed for the cloud application. 3. The Infrastructure as a Service (IaaS): Unlike the SaaS and the PaaS, the IaaS does not provide the complete service or the platform for development. It instead provides the fundamental compute, network, and the storage infrastructures on which one can develop their own environments of SaaS or PaaS. This technique makes it easy for one to install servers that are virtual in cloud by making use of the Virtualization and automation aspect of the server. The monitoring of integrity is important in the cloud storage. With the use of the remote protocol for checking data integrity, data integrity in the cloud can be achieved. The user creates metadata. Afterwards, the user could make some alterations to the server for the purpose enforcing integrity for some files by means of the challenge response. The systems that embrace the use of the Distributed DBMS enable the aspect of distribution to be transparent to users. This system comprises of a logical database that has been divided into various fragments (Ault, 2003). Each fragment is preserved on one or more machines, which are controlled by a separate DBMS, and the computers are linked by a network for communication. Each site has the capability of processing the data that has been stored on other machines within the network. The Distributed DBMS structure is not appropriate because of the following reasons: It experiences replication of data which makes the system to be complex. The cost of acquiring the Distributed DBMS as well as the maintenance cost is high, due to its complexity. As compared to the centralized system, the security of the Distributed DBMS is not as reliable. This system makes it difficult to enforce database integrity. The following are the optimization techniques that can be used to enhance the operations of the database: Appropriate indexes should be used The unnecessary indexes should be removed The transfer of data from the client to the server is supposed to be made minimal SQL codes as opposed to program loops should be used. Tables with temporary triggers and data should be used. Employ the use of the optimization tools. The lost updates may occur in the event that two or more processes select a similar row, and then perform a row update according to the value that was selected originally. Each process is not aware of the other. The update performed last replaces the update performed by the other process, resulting to lost data. Uncommitted data may occur in the event that the second process selects the row that is running an update by another process. The second process will read uncommitted data, which may be altered by the process running the update on the row. Concurrency control is the process of allowing several processes to execute concurrently, at the same time ensuring that they do not interfere with each other. The following are some factors that can be employed to ensure that concurrency control is achieved: The Transactions It is the core element of integrity of data in the databases that are for multi-user purpose, as well as the base for all the concurrency methods. A transaction is referred to as the single portion of work that is invisible that has some effect on data. Locks Locks are used by the database to prevent the interference of transactions. Interference of transactions can occur when one transaction wants to alter data that is being updated by another transaction. Serialization This is when the transactions appear to be executed in a serial manner. This is the highest level of segregation of the processes belonging to different users, but accessing similar data. When a transaction commences, it is separated from any other transaction that may cause alteration in its data. The Rollback segments The Rollback segments are the structures in the database that can preserve data for a particular transaction in the case of experiencing a rollback. This stored data restores the rows of the database to its initial state before the other transaction was started. When one transaction starts to alter some data, it initially writes the previous data to the rollback segment. The data that has been stored in the rollback segment then provides the essential information for the purpose of performing the roll back of the transaction. The rollback segment and the redo log are different from each other. A redo log is used for logging in all the processes to the database as well as recovering of the database in case of a failure in the system, whereas a rollback segment offers a rollback for the transactions along with reading consistency. The SGA is used to cache the blocks of the rollback segments as in the case of indexes. If the rollback segments are unutilized for a long a period of time, they might be removed from the cache and copied to the disk. The System Change Number (SCN) In order to maintain data integrity in the database, its important to track the order of how the actions were performed. The mechanism is what is known as the System Change Number. This mechanism is a timestamp that is logical, that is responsible for tracking the order of the occurrence of the events. Information in the redo log is used to duplicate the transactions in their original and exact order. It is also the responsibility of the SCN to establish when clean up of the information in the rollback segments that are not needed is supposed to take place (Foot, & BMC Software, 2003) Disaster is an occurrence that causes interference to the computer processes, leading a disruption in the operations of a business. A Management System responsible for Disaster Recovery, therefore, needs to be implemented in the event that a disaster is encountered. This system can be described as a continuous planning, building, testing, and executing process for the purpose of attaining an effective continuation of the usual organization’s functions in the case encountering an interruption. The procedure of formulating a plan for disaster recovery starts with: Identifying the causes of disaster and the effects it might cause. Evaluate the probability of the disaster occurrence and the harm it may cause. Ranking them in accordance to the priority of the business. In order to attain an inclusive Disaster Recovery Plan, the following are the key components: A Critical Application Evaluation Procedures for Back-up Procedures for Recovery Procedures for Implementation Procedures for Testing A Plan Maintenance The Disaster Recovery plan is supposed to: Identify and categorize the risks that may result to encountering disasters. Describe the procedures and resources that should be put in place to ensure the continuity of the normal operations of the organization in the event of encountering a disaster. Describe the mechanism of returning the organization back to its normal functioning from its Disaster Recovery condition, after the impact of the disasters have been diminished (Ault 2003). The following are the security threats that the management of the department store database is prone to: The Excessive Privilege Abuse On granting the users or certain applications excessive rights to access the database, as opposed to giving them rights that pertains to their tasks, they might use those privileges for purposes that are inappropriate. A user or group of users, gains the excessive rights to access the database when the database administrator fails to classify and update the mechanism that controls the access privileges for every user in accordance to the task performed. This results to each user having unrestricted rights to access the database. This is dangerous to an organization because a user can alter an otherwise important record. SOLUTION: The solution to curbing this threat is by making use of an access control mechanism that is known as the query-control, which refers to the method of restricting privileges of the database to limited operations of SQL such as UPDATE, SELECT, among others. This access control mechanism is also prevents other security threats. The Privilege Elevation Malicious users may make use of the weaknesses of the software running the database platform to their advantage. They may alter the rights of access from that of a normal user to that of the administrator. The weaknesses may be as a result of SQL statements, implementations of protocol, functions that are in-built, or even in the stored procedures. SOLUTION: This threat can be prevented by combining the use of the Intrusion Prevention Systems, otherwise known as IPS ant the Query-level access control mechanism. The IPS is used to examine the traffic of the database to determine the patterns that match up to the weaknesses that are known. The IPS will then prevent any access to the procedure that is vulnerable or only prevent those procedures that have a fixed attack. The Legitimate Privilege Abuse The legitimate users may also abuse the rights they have been granted to access the database. Once data has been stored on an endpoint computer, it is subjected to theft, Trojans, among other threats. SOLUTION: The solution for dealing with this threat is enforcing access control for the database that does not apply only to some explicit queries, but applies to the framework within the access of the database. By implement the rule for applications of the client, location, time, among other elements, it becomes easy to discover those users that maliciously use the rights that they have been granted. The Vulnerabilities of Platforms The weaknesses in the Operating System and the services that have been installed on the server of the database may result to illegal access, corruption of data, or the denial of service attack. SOLUTION: A running a regular software update and using the IPS: Only the updates provided by the vendor should be allowed to run. The Injection of SQL The attacker inserts database statements that are unauthorized to a weak SQL data path. The data channels that are mostly prone to attacks include the input parameters of the web applications, and the procedures that are stored. The statements that have been inserted are then directed to the database to be implemented. This gives the unauthorized parties access to the complete database. SOLUTION: There are three mechanisms that can be merged to eliminate the injection of SQL. The techniques include the IPS, the Query –level access control and the correlation of the event. A Weak Audit Trail Weakness in the audit policy of the database makes the organization to be prone to major risks, for instance, the organization may conflict with the government’s regulatory requirements. The audit mechanisms represent the defense of a database. In the event that the unauthorized party all the other defense mechanism put in place, the audit data should be able to discover violation of data. It is this audit data that is used to associate the violation to a specific user, and afterwards it repairs the system. SOLUTION: Quality Audit appliances that are network-based are to be used. They tackle most weakness related to the audit tools that are native. Use of Audit appliances that are based on high performance for the network: These appliances operate at a fast rate with no impact on the impact of performance of the database. Since the audit appliances can operate independent of the database, it is important to separate the audit duties and the administrative duties. The Denial of Service Attacks This is when the intended users are denied access to or applications of the network. This situation can occur through the use of various techniques, for instance an attacker using the weakness of the database platform to cause the server to crash. Other examples of the denial of service attack include the overloading the resources of the server, corruption of data, flooding the network with unnecessary information, among others. SOLUTION: To prevent DOS, protection is required at various levels. Protection is vital at the network, database, and the application levels. The employment of the Query-level access control, IPS, control of the connection rate, and the control of the response timing is also important. A Weak Authentication This enables the attackers to masquerade as the legitimate database users by illegally acquiring the login credentials of the legitimate users. They can acquire the credential using a number of ways, for instance, cracking of passwords, stealing of passwords, among others. They then alter the database or steal some important information. SOLUTION: Implementing a powerful authentication This involves enforcing the policy of using strong usernames/passwords. This will help in minimizing the possibility of an organization being subjected to the risk of having a weak authentication system (Yuhanna, 2000). References Ault, M. R. (2003). Oracle DBA made simple: Oracle database administration techniques. Kittrell, N.C: Rampant TechPress. Bruni, P., & International Business Machines Corporation. (2003). DB2 for z/OS tools for databaseadministration and change management. San Jose, CA: IBM, International Technical Support Organization. Foot, C., & BMC Software. (2003). OCP instructors guide for Oracle DBA certification: A study guide to advanced Oracle Certified Professional database administration techniques. Kittrell, N.C: Rampant Techpress. Mullins, C. (2002). Database administration: The complete guide to practices and procedures. Boston: Addison-Wesley. Yuhanna, N. (2000). Oracle 8i database administration. Greenwich, CT: Manning. Read More