DBMS Assignment:

For the Questions $1$ and $2$ use the Humane Society database with the following relations:

Animals$($AnimalID$,$ Name, PrevOwner, DateAdmitted, Type$)$

Adopter$($SIN$,$ Name, Address, OtherAnimals$)$

Adoption$($AnimalID$,$ SIN, AdoptDate, chipNo, AdopDuration$)$

$1.$ Design a complete ER diagram for a database for Humane Society. The set of relations with

attributes are given, where

$($a$)$ The primary keys are underlined.

$($b$)$ Animals stores information about the animals currently at the Humane Society. Type refers to

animal like $($dog$,$ cat, etc$)$

$($c$)$ Adopter is the relation that holds information about animal adopters. SIN is owners ID$.$

OtherAnimals records the number of other animals that the adopter currently has at home.

$($d$)$ Attribute chipNo stores the number on the microchip that is implanted on the animal for tracking.

All other attributes are self descriptive.

$2.$ a$)$ Connect the relations given in Q$.$No$\mathrm{.}1$ of the Humane Society database with a schema diagram.

b$)$ Write a create table statement for the Adoption table. $($Assume you have already created table for

Animals and Adopter as per SQL statements$)$

c$)$ Insert a tuples for Adoption table with and without using attribute set.

d$)$ Write an SQL to Retrieve the total number of dogs that were brought to the Humane Society on $18$

April $2000.$