GARDEN GLORY PROJECT QUESTIONS the following Garden glory database design is use
ID: 3568037 • Letter: G
Question
GARDEN GLORY PROJECT QUESTIONS
the following Garden glory database design is used in chapter 3:
OWNER(OwnerID, OwnerName, OwnerEmail, OwnerType)
Property(PropertyID, PropertyName, street, city, state, zip,OwnerID)
Employee(employeeID, LastName, firstName, cellphone, experienceLevel)
Service(propertyID,EmployeeID, ServiceDate, Hoursworked)
the referential integrity constraints are:
OwnerID in property must exist in OwnerID in Owner
PropertyID in service must exist in PropertyID in property
EmployeeID in service must exist in employee ID in employee.
Garden Glory has modified the employee table by adding a TotalHoursWorked column:
Employee(EmployeeID, LastName, FirstName, cellphone, experienceLevel)
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 applciation reads a row from the PROPERTY table to get the PropertyID. it then creates a new row in SERVICE and udates TotalHoursWorked in Employee by adding the HoursWorked value in the new SERVICE record to TotalHoursworked. this operation is referred to as a service updata transaction.
In some casesm 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 Service Update Transactions?
can someone help me answer questions A-F, thanks.
Explanation / Answer
a)
Service is the table which table which is referring Property table for PropertyID and Employee table for EmployeeID. Any changes made in service table will affect the other two tables it is referring. That is, as the total hours worked will dependent on the number of hours worked of service, the values updated by service process will replect in employee table. If this was not atomic, every time the Service process runs, the tables on which it is depending or referring must be updated manually and there are chances of missing of data.
Hence, it is always better to habe the referential tables atomic.
b)
When the service update transaction is run, every time the number of hours will be updated and this will reflect on the TotalHoursWorked field of Employee table. Even when the service update is run for new employee, the table will be updated with new Employee details with TotalHoursWorked.
Hence, there is no such scenario where the TotalHoursWorked will be missed.
But, in some cases when different users are trying to run the service update transaction, there are chances of data loss. That is, when a first person is running the transaction and the second one is also running the transaction, the table will have only the older entries and the second person can see only these entries and this may cause to update the table wrongly and may cause missed entries in Employee table for TotalHoursWorked.
c)
In case of service update transactions are running simultaneously with new service update for new employees. The non repeatable read scenario will be the case where every time the service update runs the data will be updated in Employee table as well for their TotalHoursWorked. When the new Employee is encountered, the Employee table will be updated with new EmployeeID and corrsponding details. There will not be any repeatable read scenarios. In case of different people running the service update transaction, there might be the situation when the service is currently in process with a person and other person is trying to run the service update. In this case the second person will only see the previous value and the new HoursWorked will be added to this old value.
Hence, phatom read situation occurs.
d)
Locking is the mechanism which helps in preventing the lost update scenario which is stated in part ‘b’ of this solution. When a lock is povided for a user running the service transaction, the other person will not be able to run the same transaction which results in serial updation of the data into the tables. Once the user one completes the transaction and updation is done, then the other person can use run the transaction which will not allow the data loss.
Hence, Locks can be used to prevent the data loss scenario.
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.