John Fulgoni
Kelly Gremban
Caroline O'Connor

Animal Shelter

This is a program that connects animals that are up for adoption in shelters with potential owners. Shelters register their animals. Participants can look at the animals, save favorites, and get in touch with the shelter that is housing their chosen pet.

Business Rules:
1. A shelter registers with the system, and provides their name, location, e-mail, and phone number.
2. A shelter can add available animals to the registry.
3. Animals are recorded with their name, species, breed, size, age, and medical history.
4. Animals can belong to only one shelter at time, but can belong to multiple shelters over time.
If animals change shelters, their record is deleted and a new one is created at the new shelter.
5. Pet-lovers sign up for the system with their user ID, name, password, address, e-mail, and phone number.
6. Pet-lovers can browse lists of animals by type, and save animals that they are considering into their favorites list.
7. A favorites list contains zero or many animals, and can contain animals of all types.
8. In order for the pet-lover to contact the shelter, the animal does not have to be in the favorites list.
9. A pet-lover can set up a visit time with a shelter and one or more animals.
Visits are associated with one animal, but multiple visits can happen at the same time
10. A pet-lover must visit an animal at least once before adopting it.
11. To adopt, the pet-lover reaches out to the shelter and is put in contact with an employee to assist with the adoption. Many employees at a shelter can approve adoptions, but each adoption is approved by exactly one employee.
12. For each adoption, the system produces a certificate of adoption. The certificate includes the pet-lover's name, the pet's name, the shelter, the supervising employee, and date.
13. An employee also works at one shelter

Exceptions:
1. Unfortunately, if a dog is returned to shelter, it would be entered as a brand new entry.
2. If an animal is rescued by the shelter and is missing any information, the attribute fields can be null. Null fields are marked as "unknown."

Questions:
1. Should animal types be their own entities? Are there different attributes to record?
2. If an animal changes shelters, is its entry deleted or transferred?
3. Is it a flat rate to adopt an animal from a shelter?

Data Usage
1. How many cats does a certain shelter have?
2. What shelters are close to me?
3. What is the name, breed, and background of that cute puppy I saw?
4. What kind of animals are at a certain shelter?
5. What are all the retrievers up for adoption nearby?

ER DIAGRAM
ER Diagram
NORMALIZATION DIAGRAM
3NF Diagram
FUNCTIONAL DEPENDENCIES:

Database Table
(Employee ID) --> Name
(Employee ID) --> Address
(Employee ID) --> Email
(Employee ID) --> Phone Number
(Employee ID) --> Date Hired
(Employee ID) --> Shelter ID

Shelter Table
(Shelter ID) --> Name
(Shelter ID) --> Address
(Shelter ID) --> Email
(Shelter ID) --> Phone Number

Pet Table
(Pet ID) --> Name
(Pet ID) --> Species
(Pet ID) --> Breed
(Pet ID) --> Size
(Pet ID) --> Date of Birth
(Pet ID) --> Medical
(Pet ID) --> Shelter ID

Visit Table
(Visit ID) --> Date of Visit
(Visit ID) --> Time
(Visit ID) --> Shelter ID
(Visit ID) --> Pet ID
(Visit ID) --> User ID

Pet List Table
This table has no functional dependencies.
Pet Lover Table
(User ID) --> Name
(User ID) --> Password
(User ID) --> Address
(User ID) --> Email
(User ID) --> Phone Number

Adoption Certificate Table (Certificate ID) --> Adoption Date
(Certificate ID) --> Employee ID
(Certificate ID) --> Shelter ID
(Certificate ID) --> Pet ID
(Certificate ID) --> User ID

Favorites List Table
(List ID) --> Date Made
(List ID) --> Nickname
(List ID) --> User ID


CHANGES FROM PREVIOUS DELIVERABLE:
A table was added called Pet List that groups Favorites List and Pet with primary keys List ID and Pet ID.



SAMPLE DELIVERABLE 4: DDL SCRIPT AND POPULATION

CREATION OF TABLES:

DROP TABLE IF EXISTS Employee;
DROP TABLE IF EXISTS Shelter;
DROP TABLE IF EXISTS Pet;
DROP TABLE IF EXISTS Visit;
DROP TABLE IF EXISTS Pet_List;
DROP TABLE IF EXISTS Pet_Lover;
DROP TABLE IF EXISTS Adoption_Cert;
DROP TABLE IF EXISTS Favorites_List;

CREATE TABLE Shelter (
Id INTEGER PRIMARY KEY,
Name VARCHAR(30) UNIQUE NOT NULL,
Address VARCHAR(60) UNIQUE NOT NULL,
Email VARCHAR(30) UNIQUE NOT NULL,
PhoneNum VARCHAR(10) NOT NULL
);

CREATE TABLE Employee (
Id INTEGER PRIMARY KEY,
Name VARCHAR(30) UNIQUE NOT NULL,
Email VARCHAR(30) UNIQUE NOT NULL,
PhoneNum VARCHAR(10) NOT NULL,
HireDate Date NOT NULL,
ShelterId INTEGER REFERENCES Shelter(Id)
);


CREATE TABLE Pet (
Id INTEGER PRIMARY KEY,
Name VARCHAR(15) NOT NULL,
Species VARCHAR(15) NOT NULL,
Size CHAR(1) NOT NULL
CHECK(Size IN ('L','M','S')),
DoB Date,
Medical VARCHAR(60),
ShelterId INTEGER REFERENCES Shelter(Id)
);


CREATE TABLE Pet_Lover (
Id INTEGER PRIMARY KEY,
Name VARCHAR(30) NOT NULL,
Password VARCHAR(15) NOT NULL,
Address VARCHAR(60) NOT NULL,
Email VARCHAR(30) NOT NULL,
PhoneNum VARCHAR(10) NOT NULL
);

CREATE TABLE Visit (
Id INTEGER PRIMARY KEY,
VisitDate DATETIME NOT NULL,
ShelterId INTEGER REFERENCES Shelter(Id),
PetId INTEGER REFERENCES Pet(Id),
LoverId INTEGER REFERENCES Pet_Lover(Id)
);


CREATE TABLE Adoption_Cert (
Id INTEGER PRIMARY KEY,
AdoptDate DATE NOT NULL,
EmployeeId INTEGER REFERENCES Employee(Id),
PetId INTEGER REFERENCES Pet(Id),
LoverId INTEGER REFERENCES Pet_Lover(Id)
);

CREATE TABLE Favorites_List (
Id INTEGER PRIMARY KEY,
CreatedDate DATE NOT NULL,
Nickname VARCHAR(30) NOT NULL,
LoverId INTEGER REFERENCES Pet_Lover(Id)
);

CREATE TABLE Pet_List (
ListId INTEGER REFERENCES Favorites_List(Id),
PetId INTEGER REFERENCES Pet(Id),
PRIMARY KEY(ListId, PetId)
);

POPULATING TABLES:


INSERT INTO Shelter VALUES (13, 'Rescue Shelter of York','215 Cove Road, York, MA, 19432','shelter.york@gmail.com','4118320912');
INSERT INTO Shelter VALUES (24, 'Animal and Rescue Shelter','555 Thorn Street, Beantown, PA, 23748','beanrescue@aol.com','6439821256');
INSERT INTO Shelter VALUES (5, 'Animal Rescue Center','660 Brook Avenue, Wale, CT, 54387','walerescuecenter660@aol.com','2139873872');
INSERT INTO Shelter VALUES (27, 'Pet Shelter','123 Chestnut Way, Ocean, NJ, 82372','oceanpets@gmail.com','3659247762');
INSERT INTO Shelter VALUES (9, 'A Better Chance Pet Shelter','934 West Street, Brain, PA, 19834','brain.pets.shelter@yahoo.com','9109143322');
INSERT INTO Shelter VALUES (31, 'Keystone Pet Shelter','34 Foxtrot Lane, Keystone, RI, 09122','keystone.pets@yahoo.com','2245561010');

INSERT INTO Pet VALUES (1,'Lily, Bear','Lab','L','2007-02-04','None',13);
INSERT INTO Pet VALUES (2,'Dolly, Boo','Poodle','M','2009-03-12','Left Leg has a Limp',13);
INSERT INTO Pet VALUES (3,'Oscar, Dean','Bull Dog','M','2006-05-02','None',13);
INSERT INTO Pet VALUES (4,'Chip, Den','Pug','S','2008-05-05','Fleas',24);
INSERT INTO Pet VALUES (5,'Husky, Bale','Chiwawa','S','2009-06-07','None',24);
INSERT INTO Pet VALUES (6,'Air, Bud','Golden Retriever','L','2008-03-03','None',5);
INSERT INTO Pet VALUES (7,'Willy, Blue','Chiwawa','S','2009-08-06','Fleas',5);
INSERT INTO Pet VALUES (8,'Black, Bob','Bull Dog','M','2010-05-07','None',5);
INSERT INTO Pet VALUES (9,'Spotty, White','Lab','M','2009-10-10','None',27);
INSERT INTO Pet VALUES (10,'Ginger, Spice','Chiwawa','S','2007-11-12','Patches of Hair Fall Out',27);
INSERT INTO Pet VALUES (11,'Maddy, Poo','Poodle','M','2008-06-06','Going Blind in One Eye',9);
INSERT INTO Pet VALUES (12,'Plop, Carr','Golden Retriever','M','2009-02-02','None',31);
INSERT INTO Pet VALUES (13,'Dummy, Nice','Pug','S','2010-09-01','Does not do well in heat',31);

INSERT INTO Employee VALUES (11,'Sally, Heart','sheart@gmail.com','4117654234','2010-09-09',13);
INSERT INTO Employee VALUES (12,'Bob, Jones','bob.jones@yahoo.com','4118734562','1997-10-11',13);
INSERT INTO Employee VALUES (22,'Jessica, Smart','jessica90@aol.com','6431236523','2003-04-12',24);
INSERT INTO Employee VALUES (23,'Owen, Don','od1987@yahoo.com','6434236712','2002-03-09',24);
INSERT INTO Employee VALUES (33,'Alex, Crick','cricka@gmail.com','2134332222','2007-03-05',5);
INSERT INTO Employee VALUES (44,'Edward, Pierce','edwardpierce@yahoo.com','3658439021','1996-10-02',27);
INSERT INTO Employee VALUES (45,'Alice, Mast','mastal@aol.com','3651236543','2005-06-02',27);
INSERT INTO Employee VALUES (55,'Sam, Jacobs','sam.jacobs@gmail.com','9108362873','2000-07-11',9);
INSERT INTO Employee VALUES (66,'Sally, Nelson','sally.n@yahoo.com','2243569182','2009-04-06',31);
INSERT INTO Employee VALUES (67,'Kevin, Hare','kevinhare12@gmail.com','2243335432','2011-01-01',31);

INSERT INTO Pet_Lover VALUES (1111,'Billy, Charm','trial45','2 Easton Terrace, York, MA, 19432','billycharm@yahoo.com','5516067707');
INSERT INTO Pet_Lover VALUES (2222,'John, Janes','doggyfav22','543 Bellevue Avenue, Portstown, NJ','jj146@gmail.com','3245671642');
INSERT INTO Pet_Lover VALUES (3333,'Caroline, Beth','cb1990','186 Paradise Avenue, Newport, CT, 31245','ceb12@aol.com','8096481343');
INSERT INTO Pet_Lover VALUES (4444,'Kelly, Moore','33rrkm','33 River Road, Middletown, PA, 34512','kmoore22@gmail.com','9934729374');

INSERT INTO Favorites_List VALUES (21,'2010-09-09','myFAV',1111);
INSERT INTO Favorites_List VALUES (31,'2011-08-02','myFavoriteList',2222);
INSERT INTO Favorites_List VALUES (41,'2011-08-06','newFavoritelist',2222);
INSERT INTO Favorites_List VALUES (51,'2012-10-03','Pets I Want',3333);
INSERT INTO Favorites_List VALUES (61,'2011-02-12','Potential Rescue',4444);
INSERT INTO Favorites_List VALUES (71,'2011-04-12','Narrowed Down Rescue List',4444);

INSERT INTO Adoption_Cert VALUES (1,'2010-09-08',22,5,2222);
INSERT INTO Adoption_Cert VALUES (2,'2010-10-10',23,10,2222);

INSERT INTO Visit VALUES (10,'2010-07-09 12:20:12',24,5,2222);
INSERT INTO Visit VALUES (20,'2010-08-02 11:23:02',27,10,2222);
INSERT INTO Visit VALUES (30,'2011-10-11 14:43:16',27,9,3333);
INSERT INTO Visit VALUES (40,'2012-03-04 15:51:05',13,3,4444);

INSERT INTO Pet_List VALUES (21,1);
INSERT INTO Pet_List VALUES (21,4);
INSERT INTO Pet_List VALUES (31,12);
INSERT INTO Pet_List VALUES (31,6);
INSERT INTO Pet_List VALUES (41,1);
INSERT INTO Pet_List VALUES (41,11);
INSERT INTO Pet_List VALUES (51,7);
INSERT INTO Pet_List VALUES (61,1);
INSERT INTO Pet_List VALUES (71,8);

SHOW CONTENTS OF TABLES IN SQL:

mysql> SELECT * FROM Shelter;
+----+-----------------------------+---------------------------------------+------------------------------+------------+
| Id | Name | Address | Email | PhoneNum |
+----+-----------------------------+---------------------------------------+------------------------------+------------+
| 5 | Animal Rescue Center | 660 Brook Avenue, Wale, CT, 54387 | walerescuecenter660@aol.com | 2139873872 |
| 9 | A Better Chance Pet Shelter | 934 West Street, Brain, PA, 19834 | brain.pets.shelter@yahoo.com | 9109143322 |
| 13 | Rescue Shelter of York | 215 Cove Road, York, MA, 19432 | shelter.york@gmail.com | 4118320912 |
| 24 | Animal and Rescue Shelter | 555 Thorn Street, Beantown, PA, 23748 | beanrescue@aol.com | 6439821256 |
| 27 | Pet Shelter | 123 Chestnut Way, Ocean, NJ, 82372 | oceanpets@gmail.com | 3659247762 |
| 31 | Keystone Pet Shelter | 34 Foxtrot Lane, Keystone, RI, 09122 | keystone.pets@yahoo.com | 2245561010 |
+----+-----------------------------+---------------------------------------+------------------------------+------------+

mysql> SELECT * FROM Employee;
+----+----------------+------------------------+------------+------------+-----------+
| Id | Name | Email | PhoneNum | HireDate | ShelterId |
+----+----------------+------------------------+------------+------------+-----------+
| 11 | Sally, Heart | sheart@gmail.com | 4117654234 | 2010-09-09 | 13 |
| 12 | Bob, Jones | bob.jones@yahoo.com | 4118734562 | 1997-10-11 | 13 |
| 22 | Jessica, Smart | jessica90@aol.com | 6431236523 | 2003-04-12 | 24 |
| 23 | Owen, Don | od1987@yahoo.com | 6434236712 | 2002-03-09 | 24 |
| 33 | Alex, Crick | cricka@gmail.com | 2134332222 | 2007-03-05 | 5 |
| 44 | Edward, Pierce | edwardpierce@yahoo.com | 3658439021 | 1996-10-02 | 27 |
| 45 | Alice, Mast | mastal@aol.com | 3651236543 | 2005-06-02 | 27 |
| 55 | Sam, Jacobs | sam.jacobs@gmail.com | 9108362873 | 2000-07-11 | 9 |
| 66 | Sally, Nelson | sally.n@yahoo.com | 2243569182 | 2009-04-06 | 31 |
| 67 | Kevin, Hare | kevinhare12@gmail.com | 2243335432 | 2011-01-01 | 31 |
+----+----------------+------------------------+------------+------------+-----------+

mysql> SELECT * FROM Pet;
+----+---------------+-----------------+------+------------+--------------------------+-----------+
| Id | Name | Species | Size | DoB | Medical | ShelterId |
+----+---------------+-----------------+------+------------+--------------------------+-----------+
| 1 | Lily, Bear | Lab | L | 2007-02-04 | None | 13 |
| 2 | Dolly, Boo | Poodle | M | 2009-03-12 | Left Leg has a Limp | 13 |
| 3 | Oscar, Dean | Bull Dog | M | 2006-05-02 | None | 13 |
| 4 | Chip, Den | Pug | S | 2008-05-05 | Fleas | 24 |
| 5 | Husky, Bale | Chiwawa | S | 2009-06-07 | None | 24 |
| 6 | Air, Bud | Golden Retrieve | L | 2008-03-03 | None | 5 |
| 7 | Willy, Blue | Chiwawa | S | 2009-08-06 | Fleas | 5 |
| 8 | Black, Bob | Bull Dog | M | 2010-05-07 | None | 5 |
| 9 | Spotty, White | Lab | M | 2009-10-10 | None | 27 |
| 10 | Ginger, Spice | Chiwawa | S | 2007-11-12 | Patches of Hair Fall Out | 27 |
| 11 | Maddy, Poo | Poodle | M | 2008-06-06 | Going Blind in One Eye | 9 |
| 12 | Plop, Carr | Golden Retrieve | M | 2009-02-02 | None | 31 |
| 13 | Dummy, Nice | Pug | S | 2010-09-01 | Does not do well in heat | 31 |
+----+---------------+-----------------+------+------------+--------------------------+-----------+

mysql> SELECT * FROM Pet_Lover;
+------+----------------+------------+-----------------------------------------+----------------------+------------+
| Id | Name | Password | Address | Email | PhoneNum |
+------+----------------+------------+-----------------------------------------+----------------------+------------+
| 1111 | Billy, Charm | trial45 | 2 Easton Terrace, York, MA, 19432 | billycharm@yahoo.com | 5516067707 |
| 2222 | John, Janes | doggyfav22 | 543 Bellevue Avenue, Portstown, NJ | jj146@gmail.com | 3245671642 |
| 3333 | Caroline, Beth | cb1990 | 186 Paradise Avenue, Newport, CT, 31245 | ceb12@aol.com | 8096481343 |
| 4444 | Kelly, Moore | 33rrkm | 33 River Road, Middletown, PA, 34512 | kmoore22@gmail.com | 9934729374 |
+------+----------------+------------+-----------------------------------------+----------------------+------------+

mysql> SELECT * FROM Visit;
+----+---------------------+-----------+-------+---------+
| Id | VisitDate | ShelterId | PetId | LoverId |
+----+---------------------+-----------+-------+---------+
| 10 | 2010-07-09 12:20:12 | 24 | 5 | 2222 |
| 20 | 2010-08-02 11:23:02 | 27 | 10 | 2222 |
| 30 | 2011-10-11 14:43:16 | 27 | 9 | 3333 |
| 40 | 2012-03-04 15:51:05 | 13 | 3 | 4444 |
+----+---------------------+-----------+-------+---------+

mysql> SELECT * FROM Adoption_Cert;
+----+------------+------------+-------+---------+
| Id | AdoptDate | EmployeeId | PetId | LoverId |
+----+------------+------------+-------+---------+
| 1 | 2010-09-08 | 22 | 5 | 2222 |
| 2 | 2010-10-10 | 23 | 10 | 2222 |
+----+------------+------------+-------+---------+

mysql> SELECT * FROM Favorites_List;
+----+-------------+---------------------------+---------+
| Id | CreatedDate | Nickname | LoverId |
+----+-------------+---------------------------+---------+
| 21 | 2010-09-09 | myFAV | 1111 |
| 31 | 2011-08-02 | myFavoriteList | 2222 |
| 41 | 2011-08-06 | newFavoritelist | 2222 |
| 51 | 2012-10-03 | Pets I Want | 3333 |
| 61 | 2011-02-12 | Potential Rescue | 4444 |
| 71 | 2011-04-12 | Narrowed Down Rescue List | 4444 |
+----+-------------+---------------------------+---------+

mysql> SELECT * FROM Pet_List;
+--------+-------+
| ListId | PetId |
+--------+-------+
| 21 | 1 |
| 21 | 4 |
| 31 | 6 |
| 31 | 12 |
| 41 | 1 |
| 41 | 11 |
| 51 | 7 |
| 61 | 1 |
| 71 | 8 |
+--------+-------+




CHANGES FROM PREVIOUS DELIVERABLE:

Updated Normalization Diagram
3NF Diagram


Employee Table
(Employee ID) --> Name
(Employee ID) --> Address
(Employee ID) --> Email
(Employee ID) --> Phone Number
(Employee ID) --> Date Hired
(Employee ID) --> Shelter ID

Visit Table
(Visit ID) --> Date/Time
(Visit ID) --> Shelter ID
(Visit ID) --> Pet ID
(Visit ID) --> User ID

Adoption Certificate Table
(Certificate ID) --> Adoption Date
(Certificate ID) --> Employee ID
(Certificate ID) --> Pet ID
(Certificate ID) --> User ID
/*removed Shelter ID */


DELIVERABLE 5: DML QUERIES

SELECT STATEMENT QUERIES:

Query
What is the name, breed, and background of that cute puppy I saw?
(Original Question 3)

SELECT name, species, medical FROM Pet;

Result Set
+---------------+-----------------+--------------------------+
| name | species | medical |
+---------------+-----------------+--------------------------+
| Lily, Bear | Lab | None |
| Dolly, Boo | Poodle | Left Leg has a Limp |
| Oscar, Dean | Bull Dog | None |
| Chip, Den | Pug | Fleas |
| Husky, Bale | Chiwawa | None |
| Air, Bud | Golden Retrieve | None |
| Willy, Blue | Chiwawa | Fleas |
| Black, Bob | Bull Dog | None |
| Spotty, White | Lab | None |
| Ginger, Spice | Chiwawa | Patches of Hair Fall Out |
| Maddy, Poo | Poodle | Going Blind in One Eye |
| Plop, Carr | Golden Retrieve | None |
| Dummy, Nice | Pug | Does not do well in heat |
+---------------+-----------------+--------------------------+

Query
What kind of animals are at a certain Shelter?
(Original Question 4)

SELECT p.species, s.name FROM Pet p
JOIN Shelter s ON (p.shelterid = s.id)
WHERE s.name = "Animal Rescue Center";

Result Set
+-----------------+----------------------+
| species | name |
+-----------------+----------------------+
| Golden Retrieve | Animal Rescue Center |
| Chiwawa | Animal Rescue Center |
| Bull Dog | Animal Rescue Center |
+-----------------+----------------------+

Query
What Retrievers are up for adoption?
(Original Question 5)

SELECT * FROM Pet WHERE species like "%Retrieve";

Result Set
+----+------------+-----------------+------+------------+---------+-----------+
| Id | Name | Species | Size | DoB | Medical | ShelterId |
+----+------------+-----------------+------+------------+---------+-----------+
| 6 | Air, Bud | Golden Retrieve | L | 2008-03-03 | None | 5 |
| 12 | Plop, Carr | Golden Retrieve | M | 2009-02-02 | None | 31 |
+----+------------+-----------------+------+------------+---------+-----------+

Query
How many pets are in each Shelter?

SELECT s.id, s.name, count(p.id) FROM Pet p
LEFT OUTER JOIN Shelter s ON (s.id = p.shelterid)
GROUP BY s.id;

Result Set
+------+-----------------------------+-------------+
| id | name | count(p.id) |
+------+-----------------------------+-------------+
| 5 | Animal Rescue Center | 3 |
| 9 | A Better Chance Pet Shelter | 1 |
| 13 | Rescue Shelter of York | 3 |
| 24 | Animal and Rescue Shelter | 2 |
| 27 | Pet Shelter | 2 |
| 31 | Keystone Pet Shelter | 2 |
+------+-----------------------------+-------------+

Query
Find all the pets that have a pending adoption certificate and the date that they were adopted
SELECT p.name, a.adoptdate FROM Pet p
JOIN Adoption_Cert a ON (p.id = a.petid);

Result Set
+---------------+------------+
| name | adoptdate |
+---------------+------------+
| Husky, Bale | 2010-09-08 |
| Ginger, Spice | 2010-10-10 |
+---------------+------------+

Query
Find all the visits by John Janes, the names and IDs of the dogs he visited and the name of the Shelter it took place at.

SELECT v.id as VisitId , VisitDate as Date, s.name as ShelterName, p.id as PetID, p.name as PetName FROM Visit v
JOIN Pet p ON (v.petid = p.id)
JOIN Shelter s ON (v.shelterid = s.id)
JOIN Pet_Lover l ON (v.loverid = l.id)
WHERE l.name = "John, Janes";

Result Set
+---------+---------------------+---------------------------+-------+---------------+
| VisitId | Date | ShelterName | PetID | PetName |
+---------+---------------------+---------------------------+-------+---------------+
| 10 | 2010-07-09 12:20:12 | Animal and Rescue Shelter | 5 | Husky, Bale |
| 20 | 2010-08-02 11:23:02 | Pet Shelter | 10 | Ginger, Spice |
+---------+---------------------+---------------------------+-------+---------------+


CHANGES FROM PREVIOUS DELIVERABLE:

No changes were made to the previous deliverable.