Database programming-1

project overview part (A) Identification of major implementation flaws in the existing system, and/or areas for improvement, including (but not limited to) o Removing repeated data, o De-normalization for optimization o changing some areas to NoSQL (if so include what type of NoSQL database). o Rational for the creation of each specific constraint (Foreign Key and Unique) and/or index. - Identification of the current Normal Form of the system. - An ER Diagram for the current design of the system, and an ER Diagram for the redesigned system. Part (B) ALTER/CREATE TABLE statements for • Changes proposed in your redesign from Part A • Commands for the missing FOREIGN KEYs, INDEXes and UNIQUEness constraints. o CREATE PROCEDUREs for • New Carer report: The hospital now wants to know which animals are with or recently returned from Carers. For every animal is should show their Accession ID, Name, Species and Taxon, and the Carer’s name, Group name and Permit number. Additionally • For animals still with a carer: it should show when they were transferred to the carer and total time with the carer to date • For animals that returned to the hospital in the last 7 days: the date returned and the total time with carer. • New Carer Group report: List all Carer groups with expired or soon to expire (in the next month) permits, order the list by most expired/soonest to expire to furthest to expire. The report should list the Care Groups name, permit number, permit expiry date, and contact name and contact phone number o Calling, and outputting as a single HTML page for each of the procedures: • Carers report • Carer Group Report o Each procedures result should be included as a table • For the carers report: rows with animals outside the hospital should be pale pink, rows with animals returned should be pale green. • For the carer groups report the rows that are expired should be RED, rows expiring in the next week should be coloured light pink, all other rows should not be coloured.