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.

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)

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

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

  1. Reads a row from the PROPERTY table to get the PropertyID
  2. Creates a new row in SERVICE
  3. Updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new SERVICE record to TotalHoursWorked.

PropertyID / PropertyName / Street / City / State / Zip
101 / Eastlake Building / 123 Eastlake / Seattle / WA / 98119
102 / Elm St Apts / 4 East Elm / Lynnwood / WA / 98223
103 / Jefferson Hill / 42 West 7th St / Bellevue / WA / 98040
PropertyID / EmployeeID / ServiceDate / HoursWorked
101 / 1 / 2013-1-12 / 4
103 / 1 / 2013-1-13 / 2
EmployeeID / LastName / FirstName / CellPhone / ExperienceLevel / TotalHoursWorked
1 / Smith / John / 206-555-1234 / Senior / 6

Service Update for New Employee Transaction – If an employee record does not exist

  1. Reads a row from the PROPERTY table to get the PropertyID
  2. Create a new EMPLOYEE record
  3. Creates a new row in SERVICE
  4. Updates TotalHoursWorked in EMPLOYEE by adding the HoursWorked value in the new SERVICE record to TotalHoursWorked.

5.  PropertyID / PropertyName / Street / City / State / Zip
101 / Eastlake Building / 123 Eastlake / Seattle / WA / 98119
102 / Elm St Apts / 4 East Elm / Lynnwood / WA / 98223
103 / Jefferson Hill / 42 West 7th St / Bellevue / WA / 98040
EmployeeID / LastName / FirstName / CellPhone / ExperienceLevel / TotalHoursWorked
1 / Smith / John / 206-555-1234 / Senior / 6
2 / Jones / George / 206-555-5678 / Junior / 0
PropertyID / EmployeeID / ServiceDate / HoursWorked
101 / 1 / 2013-1-12 / 4
103 / 1 / 2013-1-13 / 2
102 / 2 / 2013-1-13 / 3
EmployeeID / LastName / FirstName / CellPhone / ExperienceLevel / TotalHoursWorked
1 / Smith / John / 206-555-1234 / Senior / 6
2 / Jones / George / 206-555-5678 / Junior / 3

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.)

Answer:

  1. User 1 Selects the data from a record to update
  2. A query or change inserts data, committing
  3. User 1 makes a change. (Data does not commit)
  4. User 2 Does not see updated changes and adds to HoursWorked for same values before User 1 updates and commits the new data
  5. User 1 updates after User 2 does, dropping User 2 edits, or causing an error.

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.

Answer:

  1. User 1 Reads the record, LOCKING the record (Or the database field or other lockable element...)
  2. User 2 attempts to reads the record, and is served a message “another user has locked this message for editing” or is not able to write to the record
  3. User 1 Adds an increase to HoursWorked
  4. User 2 Does not see updated changes and waits while User 1 updates
  5. User 1 Updates and frees the record or RELEASES LOCK
  6. User 2 accesses information

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.

Answer:

  1. A read occurs on a given property ID, a Service Update Transaction updates
  2. User selects rows and deletes some or all of them
  3. The service Update transaction occurs on the new employee record (if all data deleted), picking up the record for the propertyID, but incorrectly reading the lack of data as 0 (if all default starting values are null or 0)
  4. This type of read occurs because the data from the employee update was deleted and queried.

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.

Answer:

  1. A query is run by User 1 on the transaction record
  2. Service rows inserted
  3. Query is run on the same record.

5. Should optimistic or pessimistic locking be used in this example and why?

In this example, optimistic locking would be the obvious choice over pessimistic locking, first, because records and data should not have many users attempting to edit the information, and changes are unlikely to conflict. Pessimistic locking also has processing time drawbacks in that each transaction would lock up the related records, and largely disallow concurrent processing.