Answer all the questions for the GARDEN GLORY PROJECT Case study at the end of chapter 6 (Database Concepts book).

Instructions:

Read Carefully: You are required to read the Garden Glory Project Case Study at the end of Chapter 6 thoroughly before beginning your analysis.

Answer All Questions: Respond to each question presented in the case study. Make sure your answers are clear, well-thought-out, and comprehensive, demonstrating your understanding of the material covered in Chapter 6.

Below are the questions and the Garden Glory Project.

The following Garden Glory database design is used in Chapter 3:

OWNER (OwnerID, OwnerName, OwnerEmailAddress, OwnerType) OWNED_PROPERTY (PropertyID, PropertyName, Street, City, State, ZIP, OwnerID) GG_SERVICE (ServiceID, ServiceDescription, CostPerHour) EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel) PROPERTY_SERVICE (PropertyServiceID, PropertyID, ServiceID, ServiceDate,

EmployeeID, HoursWorked)

The referential integrity constraints are:

OwnerID in OWNED_PROPERTY must exist in OwnerID in OWNER PropertyID in PROPERTY_SERVICE must exist in PropertyID in OWNED_

PROPERTY ServiceID in PROPERTY_SERVICE must exist in ServiceID in GG_SERVICE EmployeeID in PROPERTY_SERVICE must exist in EmployeeID in EMPLOYEE

Garden Glory has modified the EMPLOYEE table by adding a TotalHoursWorked column:

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 OWNED_PROPERTY table to get the PropertyID. It then creates a new row in GG_SERVICE and updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new PROPERTY_SERVICE record to TotalHours Worked. 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 such a case, a new EMPLOYEE row is created, and then the service is recorded. This is called a Service Update for New Employee Transaction.

A) Explain why it is important for the changes made by the Service Update Transaction to be atomic.

B) Describe a scenario in which an update of TotalHoursWorked could be lost during a Service Update Transaction.

C) Assume that many Service Update Transactions and many Service Update for New Employee Transactions are processed concurrently. Describe a scenario for a nonrepeatable read and a scenario for a phantom read.

D) Explain how locking could be used to prevent the lost update in your answer to part B.

E) Is it possible for deadlock to occur between two Service Update Transactions? Why or why not? Is it possible for deadlock to occur between a Service Update Transaction and a Service Update for New Employee Transaction? Why or why not?

F) Do you think optimistic or pessimistic locking would be better for the Service Update Transactions?

G) Suppose 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. Describe processing rights that you think would be appropriate for this situation. Use Figure 6-23 as an example. What problems might this security system have?

H) Garden Glory has developed the following procedure for backup and recovery. 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 portable USB drive and stores it at a managers 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. 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 system.

I) Suppose a frequent query at Garden Glory is to request information about an employee based on the employees cell phone number. The office staff has noticed that this query appears to be running very slowly. What is one thing you can do, as DBA, to improve the response time of this query?

J) The staff at Garden Glory has heard about cloud databases and is intrigued by the possibility of not having to worry about software purchases and upgrades, hardware upgrades, backups, and other important DBA functions. The staff has asked you to help it decide whether to move its database to the cloud using a DBaaS model.

1. Describe how a cloud database could make Garden Glorys database management tasks easier for its staff and how it might affect the necessity of hiring a DBA. Be specific.
2. Describe how a cloud database could make Garden Glorys database more cost-effective. Include in your answer a specific estimate from an actual cloud provider based on your best estimates of Garden Glorys resource usage and performance/availability requirements.
3. Recommend and justify specific choices regarding replication/availability and security/encryption for Garden Glorys data if it chooses to store it in a cloud-based DBMS. For example, your answer should include answers and justifications for at least the following questions: How many replicas should there be, and where should they be stored? How, when, and where should data be encrypted, if at all?

Please answer them all ASAP. Thank You!