Database Administrator for Department Store - Essay Example

Database Administrator for a Departmental Store Database Administrator for a Departmental Store The departmental stores retail expansion creates the need for an enterprise-wide relational database model more specifically to manage the projected increase in sales as the resultant effect of the current marketing strategy. The objective of this paper is to design a database solution and the potential business rules that could be used to house the sales transactions of the department store. In designing, the database several necessary steps, procedures and precautions will be considered as the database should be employed to support both the accuracy, confidentiality, integrity of the business information as well as eliminate any form of redundancy in then data. The data model should also help the various retail offices communicate. A clear and thorough analysis of the functions, processes and transactions at the retail offices reveals the following entities. Customers: they are any organization, individuals or anything that buys products from the departmental store. Products: this is goods that are stored in the organization waiting for purchase. Store: the physical place to which the transactions occur Vendors: this is the people or groups that supply products to the organization. Employees: These are the employed people that work in the stores to transact the deals with the customers Sales: this is the activity of selling/buying a product These form the principal entities of the database. Each of the entities has their attributes and information as shown below. Customers: CustomerID, Name, Address, Phone number Products: Price, Type, Manufacturer Employee: employeeID, Name, address, sales Products: date, customer Employee: store sales supplies buys customer *customerID Address Phone number Rules 1. An employee can sell one or more products 2. The product can only be sold by one and only one employee 3. A supplier can only supply to one and only one employee 4. A customer can buy one or more products 5. The product can only be bought by one and only one customer (Brain, 2012) Cardinality Employee to supplier 1:1 Employee to products M: 1 Customer to products m: 1 These forms the primary attributes of the entities above. They also give the necessary information about any transaction involving the entities. Potential sales of the departmental store include the products the business is engaged in. The transactions include payment, the type of commodities, the full payment, and the total number of items in the retail shops. Retail stores use databases in retaining customers and increasing sales through various methods. The fundamental issue is making the products ordered available and up-to-date. This is best done by involving the customers in the database creation process. This ensures that customer’s needs are met. The created database allows parallel customer access without any restriction. Retail stores also make sure that there exists a locking mechanism so that the customers can use them to lock their goods. They ensure that the database is transparent, easy to learn and use and they also solve any hitch in the database (Fenn, 2014). The stores ensure that the pricing of the products in the database is in line with customer status. In developing the database they have the consumer’s wants and economic status in mind so that they can retain them and persuade others to join. In addition they asses their customers to find out about their preferences and go on to find the best alternative so that the database information is in line with customer needs. Retail stores also make sure that their database contains big data that is analyzed together with custom enterprise data, the stores can come up with a cautious and instinctive perceptive of their stores, which will in turn improve productivity, and strengthen the competitive advantage. They make the database able to get big data perform an analysis. The database consists of transactions that store the whole history of the purchases made by the consumers including the price, date, customer details and catalogs. Customer preferences are also stored in there (Hughes, 2014). There are two main approaches to sales forecasting: 1. Qualitative 2. Quantitative and or judgmental Organizations use the two approaches together. In the quantitative approach, you estimate a significant amount of something whereas qualitative approach you emphasize on numbers to forecast. Sales history (analysis of the market, products and customer) is a vital tool in sales forecasting. Moreover, it is the foundation for inventory forecasting. Open model time-series sales forecasting technique deals with sales history. Therefore, data can be used to predict patterns in sales prediction whereas exponential smoothing compares preceding forecast to the present sales to obtain a false figure that is then employed in predicting future sales. The trend can also be used in sales forecasting by aiding in the preparation of departmental database. It makes sure that adequate inventories are ordered, and enough shipping staff is on hand of high sales. MS Excel is an example as it enhances users to organize sales history data for forecasting. The customer details are organized together, and various functions such as averages can be performed on them to predict future purchases. Sales forecasting calculations are very simple. Difficulty only arises in maintaining a comprehensive and correct financial records to make the calculations. Vital information for the calculation of sales forecast is: the sales amount for each commodity broken down to each month of the year, the external factors influencing sales and the fluctuations of prices of raw materials. The usual sales forecast calculation process with no obtainable sales is to base the forecast on the performance of parallel business who sell such like commodities. The simplest sales forecasting method is the annual sales forecast. Making the assumption that the sales are moderately stable you only account for inflation. It is calculated by adding the previous year’s sales and multiplied by the rate of inflation that is equal to the next year’s sales forecast. Typically, sales change according to time. Consequently, the sales predict is broken down month by month. The next thing is to assess the recent years of sales numbers to calculate the percentage of the year’s total sales for every month. In creating sales forecast, there arise some components that are to be added to the calculation. This includes sales contracts that cannot be renewed and political adjustments that can have an impact on government contracts. In circumstances where the retail has no verified sales, sales forecast is vital in persuading investors. It is wise to base the forecasts on businesses that sell to the same customer demographic and reside on the same location. For the retail sales there needs to be an understanding of the average monthly or annual sales volume per square foot for the retail space. With this information, it is easier to fine tune to the size of your store. Inventory forecasting is an active and innovative plan intended at the provision of expected stock level to meet the demand at any time. Pro-activeness is the step taken in preface to an expected event. There are three methods that can be adopted to conduct inventory forecasting. They are Intuitive, Extrinsic and Intrinsic. The intuitive relies on the subjective judgment and timely views that aren’t sequentially based. The intrinsic method relies on history and states that a similar historical event may reoccur. The extrinsic method, on the other hand, relies on activities on other areas and accepts the proportionality theory. Forecasting helps in optimizing business process through objective decision making. It is always easier and more accurate to predict for a group of products than for a single product. The prediction horizon must be realistically small as the bigger it is, the more the errors that can be made. Security is a primary concern for many companies. Whenever vulnerabilities appear in the business, it becomes tempting for external entities to attack. Businesses should, therefore, put in place counter measures for their cloud computing services as its execution over the internet makes it vulnerable to attacks. Consumer protection must be a priority in cloud computing. The developers should ensure that user data cannot be changed and extracted by one person. The two ways of security are: 1. Certifications and restrictive user access Restrictive access is a logical mode of security gauge that comes in forms of passwords and usernames test to complex log in forms. Nonetheless, application programs should have additional security mechanisms such as IP particular applications programs and user timeouts. The main shortcoming in this security mechanism is to limit the access rights of the user. Each user is manually given security permission to protect access to their information. Certifications are vital in user certification. Software engineers should allow security specialists to offer certifications for their software’s. This assures users that the product is duly tested. This is the major undoing of cloud computing as external security checks can open the organizations secrets on cloud computing. However, this can be sacrificed for the safety of the users. Away from user protection against the various forms of attacks, the data should be protected. In this part, both the cloud computing software and hardware should be analyzed unequivocally. Cloud computing hardware requires several modes of security protection. The location of the data center should be chosen based on its security together with its proximity to users and controllers. It must also be protected from extreme weather conditions and physical attacks (“A Revolution” , 2013). With the two in mind, some manual parts must be provided for enhanced security. This includes manually closing the application to prevent further unauthorized access. The security is not only to focus on prevention but also a provision of recovery options. Threats are objects that can cause harm. These threats may result in difficulties in running the stores. Susceptibility of the operating system may result to attack of the database. Transactions are automated sequences of steps. The store database keeps the sales and inventory data of the store. Database security threats that are possible at the store include security component in any way needs to cover access control, vulnerability, application access, inference, and auditing mechanisms. The most vital measure of protecting data is restraining access to the data. This can be done through verification, authorization confidentiality and access control. These three mechanisms are specifically different but usually used in combination with a focus on access control for granularity in assigning privileges to particular things and users. For example, most database systems use some type of verification, like username and password to restrict access right to the system. Access control is a vital notion in security. It restricts events on objects to particular users. In retail store database security, the objects relate to data objects e.g. the columns and tables together with sql procedures. Database control access is necessary and can be enacted by giving access to only one of them. Excessive privileges occur when users have more control than they deserve. A particular database user can eventually have access to excessive privileges when the database designer does not describe access privilege control mechanisms for every user type. Database users can abuse official database privileges for illegitimate reasons. This can be done by either linking with another client such as Microsoft excel. Database attackers can also exploit the database platform software vulnerability to alter access privileges to an administrator. These vulnerabilities can be in stored procedures or even sql statements. For example, a user can exploit the weakness in functions to gain administrator rights. With access to the administrator account, they can transfer funds and do other illegal things. Susceptibility of a platform together with the extra services installed on the database server can lead to data corruption or even unauthorized access. The structured query language injection attack in which the architect necessarily pushes illegitimate database procedures into a weak structured query language data channel. This includes stored functions. The inserted functions are run in the database thus allowing the intruder access to the database Weak audit trail can result to susceptibility of departmental store database. Electronic records of sensitive database transaction are part of database deployment. Weak audit policies are a severe risk to the database such as regulatory or deterrence. The audit mechanism should help in detecting and recovering of departmental store data and information. Data audits help discover violations and link to users. DBMS are databases whose storage devices are not linked to computer CPU. They can be stored in computers in the same network (Gartner, 2013). DBMS manages data as though the data is stored in one computer (oracle, 2014). It synchronizes the data in a periodical way, thus making sure that the users have concurrent access to the data. There needs to be an interaction between the administrators and the users of a distributed system as though it was in one place. This provision eliminates programming requirements as the intended goal is obtained. The users should know how the data has been fragmented. DBMS gives assurance that there is no interference from the users’ simultaneous access. Database optimization can be achieved by various ways. The user has to be aware of when they need to optimize the database. However, they must first consider the load speed as it affects the rate. The optimization key should be according to the performance, profiling method, having a close examination of the execution plans query, and also standard optimizations should be considered. These include looping queries, taking only needed columns should be picked. Lost update problem is also known as the multiple update problem. This problem occurs when the data written by a transaction is overwritten by another transaction or when two transactions access the same database get their operations interleaved thus making the data contained in the database inaccurate. The problem can be illustrated by the following. That there exists two sections A and B., they both read a record and try to update it at the same time. This will have the effect that the second update will overwrite the first write. There is a problem here because they both know old values. A then updates the value followed by B. A’s update is lost as it is overwritten by B’s. Shared locks are one of the solutions to this. This uses batch processing whereby the locking system implementation is done finally, and there is the use of two-phase locks. Uncommitted data is known as dirty data. It occurs when data that is written by one transaction is translated by another transaction before it is committed by the other. It occurs when one transaction is given the go ahead to make out the intermediate results of the previous transaction before it is committed. This is best explained by the following. There are two connections A and B. A reads an object that B has updated. B, therefore, rolls back the transaction and the data which has not been committed that is known by connection A leads to the uncommitted dependency situation. Problem arises because permission was given to A to go ahead and read the intermediate results of connection B before the termination of connection B occurred. The solution to this problem is to block connection A from reading the results until a termination in B occurs or a rollback has occurred. Concurrency is the coordination of concurrent accesses in a database in a multi-user database system. Concurrency control allows the users access the data as if it’s only them accessing the database. The major challenge in having this functionality is about blocking updates made by a user interfering with other users. The main reasons for the popularity of DBMS is that the users can access data stored in different computers. Ways of controlling existence of concurrency in databases include 1. Optimistic that involves a delay in checking the integrity rules till the end of the transactions. This is done cautiously to ensure that there does not exist any blocking in read and write operations. 2. Pessimistic in which it’s made sure that the transaction of operations. It is done when the rules are violated. It reduces the performance of the system 3. The semi-optimistic in which some system may be blocked similar to the pessimistic approach. It is always possible that other systems may be still running i.e. all systems are not blocked. With the transaction type as the concurrency factor, it makes sure that the rollback or commit command issued completes the transaction. When a transaction is committed, permanence occurs into all in the transaction. When there is a rollback all the changes are reverted thus they are lost, and the database returns to its final form at the last successful operation. Hence during a transaction the database engine examines the changes done in the database from the start of the process. If it finds any inconsistent changes then it rolls back. Data backup and restoration rely on physical files that make up the database. The two suggested methods for backup are: 1. Automatic backup storage management 2. Backup script In the automatic backup storage management, the backup retention policy is retained thus enabling in the giving of redundant level in the database protection. The backups are managed automatically by the database in their areas of recovery. The backup script, on the other hand, performs the backups online of the database. Online backups continue when the database is running while offline backups run when the database is mounted in offline mode the scripts puts the database in its proper state. In conclusion, database threats include the following: access by unauthorized persons, attacks from brute wall or firewall attacks, inappropriate use or vulnerability in the database analysis and encryption from stolen electronic gadgets such as PDAs and computers. Solutions include putting in place stern policies regarding database access. Making sure that monitoring and hardware procedures are done on a regular basis. Reference AVNET. A Revolution Upon Us. (2013, March 13). Retrieved from http://www.crn.com/news/cloud/240150619/the-100-coolest-cloud-computing- vendors-of-2013.htm Brain, C. (2012). Database Systems: Design, Implementation, & Management. New York: Cengage. Retrieved from http://books.google.co.ke/books?id=QWLpAgAAQBAJ&pg=PA45&lpg=PA45&dq= Crow%EF%BF%BDs+Foot+notation&source=bl&ots=eJtUCY1DKy&sig=lwGFKv4 vRMuR5NREf1x6ZOoUsrk&hl=en&sa=X&ei=XYkTVMOKAZDlaP3kgsgE&redir_ esc=y#v=onepage&q=Crow%20s%20Foot %20notation&f=false Fenn, D. (2014). 10 Ways to Get More Sales From Existing Customers. Retrieved from http://www.inc.com/guides/2010/08/get-more-sales-from-existing-customers.html Gartner. (2013). DDBMS (distributed database management system). Retrieved from http://www.gartner.com/it-glossary/ddbms-distributed-database-management-system/ Hughes, A. (2014). Keeping Track of Retail Customers. Retrieved from http://www.dbmarketing.com/2010/03/keeping-track-of-retail-customers/ oracle. (2014). Oracle7 Server Concepts Manual. Retrieved from http://docs.oracle.com/cd/A57673_01/DOC/server/doc/SCN73/ch21.htm Read More