Q The study is about planning to prepare a detailed administration ,management plan for database, security at retail store Home, - Database System at Retail Stores Introduction The aim of the report is to help the retail store to manage, administer, and implement information security controls on its database. The database is connected to the online business solution of the retail store. It is currently having the top priority in the business and the management is aware of the information security issues stemming from the virtual world and the Internet. Currently, the database is experiencing high volume of transactions in the daytime. There is a database administrator who is also the database owner. All transactional logs are to be backed up and it also needs a point-in-time recovery solution. It also has an all-time availability group. The database must have the functionalities to manage database transactions, to maintain security, and to have database recovery plans as a part of the database recovery and business continuity solution. If there is any system downtime or system failure, the data must be recoverable. Administration Plan The database administration plan for the retail store contains the following activities and should follow relevant recommendations(Kroenke & Auer, 2013). • There must be enough normalised tables in the database so that it can model the real-world case of the retail store effectively • The database will be used by different groups of users. So, there must be suitable views for the users so that they can use the views only to access the database and to run various queries without changing the actual structures of tables in the database(Ramez Elmasri, 2016). • The database should have enough forms and reports to enter data into the tables and to generate outputs from the database • A database administrator must be able to enforce transactions management controls so that data can be updated consistently in the database. • Database administrator must be able to enforce the user access control rules to give limited access to the users. • The database administrator must be able to create new users and assign proper roles to the users. • The database administrator must be able to create a database security and backup plan. And he must be able to implement the plan as well. It is also the duty of the database administrator to keep monitoring the performance of the implemented database backup and security plan. Transaction Management Plan A transaction in a database can be used for various purposes. For the retail store database, a transaction can be for the following reasons from many others. For example(Oracle Inc., 2002), • It can be used to verify the account details of the users as supplied by the users and as assigned by the database administrator. • It can be used to ensure the purchase of an item from the catalogue • It can be used to ensure update to a stock of an item when an item is sold. • It can be used to update the stock of an item when an item is added to the stock. • It can be used to ensure financial transaction of a user when there is a purchase of an item. • It can be used to check the order tracking activities • It can be used to update the status of an order. There are many other database management related activities that can be modelled using the retail database. To keep things simple, the main activities have been covered in this report. Each of the following activities will result into one or more transaction in the database. Hence, a suitable transaction management plan must be there to handle the tasks(Coronel & Morris, 2016). The transaction management plan will be developed by the database administrator and it will also be implemented by the database administrator. The transaction management plan for the retail database must include the following. • Each transaction must be atomic in nature. That means either the whole transaction will occur or it will not occur at all. • There must be proper controls to preserve the concurrency control rules for the transactions. • There must be enough support to ensure that the database is in consistent state. That means, the values should be reflected accurately in the database once there is any operation on the database elements. • Each transaction must be done in isolation. That is, there must not be any effect of another transaction on it and there must be a consistent view available to the users even when transactions are going on in the database(Basta & Zgola, 2011). • The database must be persistent. That means, once a transaction has been completed successfully, then it must not be altered due to the reasons like power outage, or similar threats. • Each transaction on the database must follow the ACID principle. • When a database starts a transaction, then all subsequent commands in the transaction must be executed. • It needs to track and save all commands in the log file • The log file will be backed up regularly • The log files along with the database will be used to recover data if there is any loss. The process of a successful transaction on a database that starts and keeps a save point of the consistent state of the database. Then the actual operations of the transactions start. If the operations are successfully completed, then it commit and the changes are permanently saved in the database(Kroenke & Auer, 2013). If it does not complete successfully, then the database is rolled back to the save point. Then no effects of the changes from the unsuccessful transaction will remain on the database. Database Security Procedure The database security is another important aspect of the retail database. It must follow the plan as given below(Ramez Elmasri, 2016). • There must be user authentication and security mechanisms to restrict the access of the users on the database. • There must be user roles and access controls rules implemented. • The database must have encryption whenever needed. • The database must have security controls to send and receive data online. There must be the use of security protocols. For example, for financial data, there must be secure SSL. • All data should be backed up regularly according to the backup plan. The backed up data must be kept at some secure place. Backup Plan and Recovery Model The database backup and recovery model must follow the plan as given below(Coronel & Morris, 2016), • All error logs of the transactions must be saved and kept securely • Data backup must be done regularly and data must be kept on some cheap media that offer higher volume of storage at a lower cost. • All backed up data must be kept separately from the actual database. It must be kept securely. • There must be implementation of physical security to ensure that there is almost no unauthorised access to the database. Access to these tapes and media must be restricted. • Cloud based backup and recovery solutions may be used to ensure backup of data. It is a flexible and useful plan. Conclusion The retail store has understood the importance of its database management system and the database. Hence, it has planned to prepare a detailed administration and management plan for the database, its security and other aspects. In the report, there are detailed information about the database administration, management, security, and backup of the database. The readers will get an idea about the importance and complexity of the role of a database administrator from the report.