The following tables form part of a database held in a relational database management system for a video. The database consists of the following...

The following tables form part of a database held in a relational database management system for a video. The database consists of the following tables:  

Title (titleId, name, description, type) 

Media (mediaID, titleID, type, status, copyNo, dateCreated, dateRetired, retirementReason)  

Rental (rentalId, mediaID, customerId, dateRented, dueDate, dateReturned, daysOverdue, fine) 

Customer (customerID, cardNumber, firstName, lastName, address, phone)  

Using SQL and TransSQL, formulate the following:  

a) The number of copies which have been retired for each title.  

 [3 marks] 

b) The customerID, cardNumber, first name, last name and phone of customers who are more that 10 days overdue.        [4 marks] 

 c) A procedure "updateFine" which calculates  the total fine to be paid by customer who are overdue. Each time the procedure is run the amount to be paid by each customer gets updated. A fine of $10 is applied per day.  [9 marks] 

d) A trigger that inputs details of customers in the table 'customerArchive' whenever a customer is deleted from the 'customer' table.  [4 marks]