USA: +1 (725) 712-1730 
Sign in
D4: DB Management & Maintenance: Describe the nature of DB administration activities at Garden Glory and define the security scheme (using Figure 6-16, pp. 319 as a guide to describe the users, roles, and permissions etc). Encrypt the DB for Garden Glory and discuss the proper procedures for DB backup and recovery. You can also refer to the GG project questions in §6.
Ø Garden Glory has modified the EMPLOYEE table by adding a TotalHoursWorked column as follows:
EMPLOYEE ( EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel, TotalHoursWorked)
The office personnel at Garden Glory use a database application to record services and related data changes in this database. For a new service, the service-recording application reads a row from the PROPERTY table to get the PropertyID. It then creates a new row in SERVICE and updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new SERVICE record to TotalHoursWorked. This operation is referred to as a Service Update Transaction.
In some cases, the employee record does not exist before the service is recorded. In this case, a new EMPLOYEE row is created and then the service is recorded. This is called a Service Update for New Employee Transaction.
Project Question 1: Explain why it is important for the changes made by the Service Update Transaction to be atomic. Describe a scenario in which an update of TotalHoursWorked could be lost during a Service Update Transaction. Explain how locking could be used to prevent the lost update. Do you think optimistic or pessimistic locking would be better for the Service Update Transactions?
Ø Per your request, Garden Glory identifies three groups of users: managers, administrative personnel, and system administrators. Suppose further that the only job of administrative personnel is to make Service Update Transactions. Managers can make Service Update Transactions and Service Updates for New Employee Transactions. System administrators have unrestricted access to the tables.
Project Question 2: Describe processing rights that you think would be appropriate for this situation. Use the following template table as a guide and see Figure 6-16, pp.319 in textbook for an example. What problems might this security system have?
| DATABASE RIGHTS GRANTED | |||
| Table | Management | Administrative | System Administrator |
| OWNER | |||
| EMPLOYEE | |||
| SERVICE | |||
| PROPERTY | |||
Ø Garden Glory has also developed an initial procedure for backup and recovery and asks you for evaluation. The company backs up the database from the server to a second computer on its network each night. Once a month, it copies the database to a CD and stores it at a manager’s house. It keeps paper records of all services provided for an entire year. If it ever loses its database, it plans to restore it from a backup and reprocess all service requests.
Project Question 3: Do you think this backup and recovery program is sufficient for Garden Glory? What problems might occur? What alternatives exist? Describe any changes you think the company should make to this backup and recovery procedure.
Project Question 4: Create an encrypted DB for Garden Glory for added security measure. Make sure you inform the instructor about the password in your submission!
Expert Answer
Answer:1) Its important that changes done by the Service Update Transaction must be atomic, the various reasons why atomicity is required are given below:
1) If Service update transaction is not atomic then it leads to data anamolies.
2) The non atomic transaction may also leads to the incorrect or obsalete data.
3) Atomicity says that transaction should be completed successfully, it not like that some changes are done and some are lost. With atomicity we maintaine the consistency in our database.
The updation of TotalHoursWorked could be lost in case following scenarios occurs:
1) Database instance gets corrupted or stop or goes in hung state.
2) Database application crashes in between service update transaction.
3) If two concurrent transaction are adding data on same column of the Employee table.
With locking we can stop the updation lost in case of multiple concurrent transactions are updating data on same column of the table. With lock, before updating the table column, we acquire a lock and that lock get released only if the transaction is completed successfully.
