Write a database trigger to enforce the following business rules in the sample Bookshop database as shown in Figure 1. Make sure that appropriate and informative feedback is given to the user if the relevant insertion or update fails. i. Sydney Book House updates the weekly roster for employees on each Friday for the following week starting form Monday. It also keeps record of all employee roster data in a separate table named as DutyRosterHistory; that is when a roster of an employee is updated, a corresponding row is inserted into the DutyRosterHistory table, where the column, WeekStarting is the following Monday. For example, if a roster for an employee is updated on Friday, Oct 20, 2017, the value of the column, WeekStarting will be Oct 23. 2017 (the following Monday) ii. SBH also emphasizes on fairness to employees, and has a policy that no employee is allocated working shift on consecutive weekends. For example, if Franz Kafkas current roster shows work shift on either Saturday or Sunday or on both days, he cannot have any work shift on weekends in the updated roster.

Publisher Employee EmployeeID CHAR(8) Branch BranchID INT PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARGAR (30) OPublisherContactio AR(10) Branch Name VARCHAR(45) EmployeeName VARCHAR(45) EmployeeType VARCHAR(20) Supervisor. EmployeeID CH Branch Address VARCHAR Num berOfEm ployees INT DutyRoster EmployeeID CHAR(8) Inventory Branch_BranchID INT Book_BookID INT BranchID INT AvailableQuantity INT tWorkingShifID INT DutyRosterHistory EmployeelD Char (8) BranchID INT WorkingShiftID INT Weekstarting VARCHAR(15) Book WorkingShift WorkingShifID INT BookID INT Writes Author AuthorID INT Book_BookID INT SequenceNum ber INT > BookTitle VARCHAR(45) BookType VARCHAR(45) > Book Price DECIMAL (8,2) 1sPaperback VARCHAR(3) WorkingShiftweekDay VARCHAR(15) Author AuthoriD INT workingShiftStartTime TIME(6) workingShiftEndTm e TIME (6) dutyT ype VARCHAR(20) AuthorName VARCHAR(45) Auth orE m ail Address V ARCHAR(45) Publisher-Publisher!D CHAR(3) Indexes Figure 1 : Relational Data Model Publisher Employee EmployeeID CHAR(8) Branch BranchID INT PublisherID CHAR(3) PublisherName VARCHAR(45) PublisherCity VARGAR (30) OPublisherContactio AR(10) Branch Name VARCHAR(45) EmployeeName VARCHAR(45) EmployeeType VARCHAR(20) Supervisor. EmployeeID CH Branch Address VARCHAR Num berOfEm ployees INT DutyRoster EmployeeID CHAR(8) Inventory Branch_BranchID INT Book_BookID INT BranchID INT AvailableQuantity INT tWorkingShifID INT DutyRosterHistory EmployeelD Char (8) BranchID INT WorkingShiftID INT Weekstarting VARCHAR(15) Book WorkingShift WorkingShifID INT BookID INT Writes Author AuthorID INT Book_BookID INT SequenceNum ber INT > BookTitle VARCHAR(45) BookType VARCHAR(45) > Book Price DECIMAL (8,2) 1sPaperback VARCHAR(3) WorkingShiftweekDay VARCHAR(15) Author AuthoriD INT workingShiftStartTime TIME(6) workingShiftEndTm e TIME (6) dutyT ype VARCHAR(20) AuthorName VARCHAR(45) Auth orE m ail Address V ARCHAR(45) Publisher-Publisher!D CHAR(3) Indexes Figure 1 : Relational Data Model