12. Write a stored function tot a l RaceTime that, given a racing number, the name of a race, and the date of a race, returns the total race time for the car specified by the racing number in the given race. If the given race does not exist, the routine should throw an appropriate error. If the specified racing number did not take part in the existing race, the routine should throw an appropriate error. In the case that not all required lap times for the (existing) car in the (existing) race are available either until race finish or retirement, the routine should throw an appropriate error. If the (existing) race was not completed by the (participating) car in the race due to retirement but all lap times were available until retirement, the routine must not throw an error but return null. The total race time should be returned as an integer denoting milliseconds. Note that this stored routine has three arguments and you must declare them in the order given above. [12 marks] Answer all of the following three questions. 1. This question concerns the logical design of a relation schema LOAN describing the borrowing of a book at a library. If a tuple is in this relation, it means that a specific copy with identification number cata logNo has been borrowed by a library member with identification number memberNo on dateOut (borrowing day) and has been returned on dateReturn (return day). If the book has not yet been returned datereturn is null. LOAN (catalogNo, dateOut, memberNo, dateReturn) (a) What would the following functional dependency mean? { dateout, memberNo } dateReturn [4 marks] (b) Give all full functional dependencies for schema LOAN, assuming that a book can be borrowed just once per day, that members can borrow many books on any given day, and that members can borrow the same book several times. [7 marks] 2. Assume a relation schema R(a,b,c,d,c,f) with primary key (a,b) and the following full functional dependencies: {a,b}d{a,b}facbcdf Write down the result of normalizing R into 3rd normal form (no explanations needed). Declare primary and foreign keys in the resulting schemas. [7 marks] 3. Consider the following schemas: Employee(staffld, name, salary, depNo) primary key staffld foreign key depNo references Department(depNo) Department(depNo,depname,totalSal) primary key depNo and the following procedural SQL statement: CREATE TRIGGER mystery AFTER INSERT ON Employee FOR EACH ROW IF NOT (NEW. depNo IS NULL) UPDATE Department SET totalsal = totalSal + NEW. salary WHERE depNo = NEW. depNo ENDIF / Explain what this trigger does without referring to its code. Describe the effect on the database that it has. [7 marks]