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
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
ALTER/CREATE TABLE statements for
• Changes proposed in your redesign from Part A
• Commands for the missing FOREIGN KEYs, INDEXes and UNIQUEness
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.