Animal Rescue Application
The course project is intended to give students an opportunity to participate in all phases of the development of a database application. The development of the application will require you to use ER modeling techniques, relational modeling techniques, SQL, and to access your database via a web application. Projects are to be done in groups of no more than 3 individuals. The groups will remain the same for the entire course. Students may choose to work alone or in smaller groups, but no compensation will be made for this when marking. All assignments will be marked equally, regardless of the number of people in the group.
The entire project requirements are stated here. For the first deliverable, you only need to hand in your Entity-Relationship diagram, a list of assumptions made, and the Relational Schema (the DDL file used to build your application — which should include insert statements showing the data that you have loaded into your database).
Project Requirements
The database that you develop will be for tracking animals that are taken to the SPCA/Humane Society, rescued by animal rescuers and housed at independently owned shelters and (hopefully) eventually adopted by a family. You will track the location(s) of the animals, the people involved, and the adoptions of the animals. Of course, this application lends itself nicely to finding cute pictures of cats, dogs, bunnies and cute rodents, but along the way, you will hopefully learn some database skills.
Data Requirements
Animals end up at a local branch of the SPCA either by animal control workers who find animals wandering free without an owner or they may be dropped off by owners who can no longer care for them. The hope is that someone will adopt the animal from the SPCA, however, this doesn’t always happen. In this case, animals are often rescued by animal rescuers (such as No Paw Left Behind) — usually volunteer organizations that are notified when an animal has been at the SPCA for too long and is in danger of being euthanized. The rescuers arrange for a shelter to take the animal temporarily until a home can be found for the animal (such as Save Me Dog Rescue). The difference between the rescuers and the shelter — the rescuers transport the animal from the SPCA and locate a shelter who will board the animal until it is adopted. There are many people involved in this operation — people that work for the branches of the SPCA, volunteers that run the rescue operations, volunteers that help the rescuers (by transporting animals from one location to another — we’ll call these “drivers”) and shelter owners/workers as well as the people who adopt the animals.
There are many branches of the SPCA — each has a name, an address and a telephone number. There are many rescue organizations, each with a name, address and phone number. Similarly, there are many shelters, each with a name, address, phone number and website URL, as well as a listing of the types of animals that they will accept and the maximum number of each type of animal that they can house.
Animals to be considered are dogs, cats, rabbits and rodents. (You can add others if you wish). Each animal in Canada will be assigned a unique identifier. You can choose what this looks like. Every animal in the database will begin in one of the SPCA branches. They will arrive on a particular date and leave the shelter on a particular date. (We will assume that each and every one of the animals leaves the shelter alive and well). You will track where the animal goes — they may be rescued by a rescuer from the SPCA and taken to a shelter, they may go directly to a shelter or they may be adopted. If any money changes hands (ie. someone pays to adopt the animal from the SPCA), this should be recorded. Money may be paid by a rescuer, a shelter or an adoptive family to the SPCA to get an animal out of the SPCA. Money may also be paid to adopt an animal from a shelter. Animals are not adopted directly from a rescuer — only from the SPCA and the shelters. (You do not have to keep track of where the animal was adopted from — only that it was adopted & by whom). Donations, however, may be given by the public to the SPCA, Rescuers, or to Shelters. We need to keep track of these donations — who gave the donation, on what date and how much the donation was for. Of course, the same people may donate to many different organizations.
Statistics are kept about the animal’s vet visits. The information stored for each vet visit includes the name of the vet, the condition they were seen for, their weight and the date of the vet visit. This information is deleted from the system once an animal has been adopted.
Drivers are volunteers and are associated with Rescuers. They are identified by their names (we’ll assume unique names) and have an emergency phone number. As well, we keep track of the license plate of their vehicle and their driver’s license number. We keep track of which drivers have driven each animal and where they took the animal from (from which SPCA to which Shelter). We have other employees that we will keep track of — the employees at each SPCA branch, each shelter and each rescue organization. Each employee has a unique name, a phone number and an address. We also keep track of the owner (who is also considered an employee) of each of the shelters and the rescue organizations. Of course we need to keep track of where each employee works.
For those adopting animals, we maintain some information such as the last name of the family, a phone number, an address, the amount they paid for the animal and of course the id of the animal that they adopted. Families may, of course, adopt more than one animal. We will assume that once an animal is adopted everyone lives happily ever after and the animal is never returned to the shelter or the SPCA.
Functional Requirements
Your application should implement the following functionality. It does not have to be flashy — functionality is the key! Your application must use PHP and must be able to work with (almost) any DBMS (therefore you should be using PDOs, not mysqli functions). Your application must support the following operations
• display the information about all the animals currently housed in a given SPCA location
• move an animal from one location to another (and update the information accordingly)
• show all the information for all drivers associated with a particular rescue organization.
• for a particular donor, show which organizations they donated to and the total amount donated (over their lifetime)
• show the total amount donated for 2018 to a selected organization.
• allow someone to adopt a pet — show the pets available at a selected shelter and update the information required for the adoption. Be sure to account for any payment made to the shelter for the animal.
• show the animals that went from the SPCA directly to a shelter (ie. they did not go through the rescue organization).
• show how many animals were rescued during 2018 (by any rescue organization).
These requirements are a minimum. You may find that you need to add additional data and or functionality to make your application realistic, or to demonstrate that it works.
Project Deliverables
Projects should be done in groups of 2 or 3 individuals. The groups will remain the same for all project deliverables. Group members will be assigned the same mark for each project deliverable. Sign up for groups on OnQ prior to the first deliverable. Students may choose to work alone but no compensation will be made for this when marking.
Hand in each of the database design and final report deliverables as single pdf files using OnQ. One submission per team is sufficient.
Deliverable 1: Database Design and Implementation
Design the ER schema for your database. Show all constraints and state any assumptions that you make which are not specified in the requirements. As you design your schema, keep in mind the Functional Requirements. A good design both represents the necessary data and facilitates effective use of the data. Capture as many of the constraints as possible in your design. Your ER diagram may be hand-drawn, but it must be legible. You may use any notation as long as it is clear what your diagram represents. Include a legend with your diagram.
Convert the ER schema for your application to a relational schema using the algorithm shown in class. Please note, there are tools that exist that translate ER schemas to Relational Schemas automatically. However, they may not translate to the same Relational Schema as you would end up with by using the algorithm shown in class. Your project will be marked using the algorithm shown in class — other translations will be considered incorrect. Make sure you indicate the primary key and any foreign keys, as well as NOT NULL constraints, for each table. Create a database instance for your schema in MySQL and populate your database with some reasonable data. A few tuples for each table is sufficient, however you will save yourself time later if you add a reasonable amount of data at this point.
To build your schema, you should use a script that you will import into MySQL.
Hand in the following for Deliverable #1:
• A list of all your assumptions
• Your ER schema.
• The DDL for your relational schema – ie. the script that you used to build your database. This should also show the data that you have imported into your database.
Please make a single PDF containing all of the above documents and hand this in online. Also hand in your DDL file as a text document. This DDL should run without error to create the database tables.
Deliverable 2: Application Demonstration (end of term)
Write PHP programs that implement at least 4 of the functional requirements outlined above. You may need to add more data to your database to adequately demonstrate all the functionality and to make the results more interesting. You may assume that user input is correct so input syntax checking can be minimal. You must, however, handle cases where queries return no results. As the minimum, your program can simply use prompted input and formatted output from the browser, however, more attention to the interface design will yield better results (and higher marks).