Assignment 14
You may want to switch to draft mode so the page breaks don’t display: ctrl + alt + n
The following Garden Glory database design is used for this lab. Please note that Garden Glory has modified the EMPLOYEE table by adding a TotalhoursWorked column.
The following Garden Glory database design is used for this lab. Please note that Garden Glory has modified the EMPLOYEE table by adding a TotalhoursWorked column.
OWNER (OwnerID, OwnerName, OwnerEmail, OwnerType)
PROPERTY (PropertyID, PropertyName, Street, City, State, Zip, OwnerID)
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel, TotalHoursWorked)
SERVICE (PropertyID, EmployeeID, ServiceDate, HoursWorked)
PROPERTY (PropertyID, PropertyName, Street, City, State, Zip, OwnerID)
EMPLOYEE (EmployeeID, LastName, FirstName, CellPhone, ExperienceLevel, TotalHoursWorked)
SERVICE (PropertyID, EmployeeID, ServiceDate, HoursWorked)
R.I. Constraints:
OwnerID in PROPERTY must exist in OwnerID in OWNER
PropertyID in SERVICE must exist in PropertyID in PROPERTY
EmployeeID in SERVICE must exist in EmployeeID in EMPLOYEE
PropertyID in SERVICE must exist in PropertyID in PROPERTY
EmployeeID in SERVICE must exist in EmployeeID in EMPLOYEE
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 will perform 1 of 2 transactions:
Service Update Transaction – For an existing employee
Reads a row from the PROPERTY table to get the PropertyID
Creates a new row in SERVICE
Updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new SERVICE record to TotalHoursWorked.
Service Update for New Employee Transaction – If an employee record does not exist
Reads a row from the PROPERTY table to get the PropertyID
Create a new EMPLOYEE record
Creates a new row in SERVICE
Updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new SERVICE record to TotalHoursWorked.
1. If the Service Update Transaction is not atomic, an update of TotalHoursWorked could be lost during a Service Update Transaction. Describe (don’t draw) the steps of a scenario where the TotalHoursWorked update is lost. In your descriptions, use words like: Read, Write or Insert, Increase, Decrease, Set, and Delete. Also, you can indicate User 1 or User 2. (The numbering is to help you order the answer. You may need more or less steps than the numbers given.)
2. If record locking is used in the above scenario, the lost update can be prevented. Restate the steps from the scenario above including record locking to show how the problem was prevented. In your steps, use the words from above, and include Lock and Release Lock.
3. Assume many ‘Service Update Transactions’ and many ‘Service Update for New Employee Transactions’ are processed concurrently. Describe the steps of a scenario where a non-repeatable read occurs.
4. Assume many ‘Service Update Transactions’ and many ‘Service Update for New Employee Transactions’ are processed concurrently. Describe the steps of a scenario where a phantom read occurs.
5. Should optimistic or pessimistic locking be used in this example and why?
