Expert answer:Database Design/SQL Worksheet

Solved by verified expert:The instructions are on each of the word documents. Please follow them and complete the worksheets. Let me know if you have any questions. Thank you again!pinkbike42
animal_shelter_scenario_updated_spring_2017.docx

Unformatted Attachment Preview

Due Tuesday, March 9, 2017 Beginning of Class.
An animal shelter is a place where the main business is housing and finding permanent
homes for animals, mainly dogs and cats, and the occasional other type of animal (bird,
snake). Many activities occur, including recordkeeping, adoption, and financial transactions.
Your consulting company will create the database design for this animal shelter. When an
animal comes into the shelter, we assign it an id, a temporary collar and collect information.
For all animals, we track name, approximate age, health, spayed or neutered, reason being
given up, whether good with children, trained, the person giving it up or a stray. For cats,
identify long or short hair. For dogs, identify the breed and the size: small, medium, or large.
After the required information is noted, the animal is assigned to a cage. Once an animal is
adopted, we update its status and the associated information for the adopter.
We track who has adopted the animals. For new animal adopters we track name, address,
phone number, email, dog/cat preference, animal adopted, and number of children in their
house, and assign an ID. Persons who come into the shelter are classified as adopters or
non-adopters (volunteers or newsletter only). For non-adopters who would like to receive the
newsletter, track name, address, email, and phone number. A non-adopter who comes into
the shelter can fill out a card to receive the newsletter. We remove records of adopters for
whom newsletters were returned.
We need to track in which cage an animal is in and obtain regular lists of all un-adopted cats
and dogs so we can send emails to our non adopters and so that each week, a list of all cats
and dogs that are not adopted are posted online in the respective “Cat Lovers” and “Dog
Lovers” pages of the website. For the emails, we would send only dog information to people
who like dogs and cat information to people who like cats.
We do not require employees of the shelter to be part of the design, but we do need to know
which employee received the animal at the shelter. No need to tie it to our employee system
– we just want to capture the name.
We also need to know the date and time the animal was taken in at the shelter and also any
unusual information (was the date a holiday?) or special notes (“general weather conditions”
or “schools finish this week for summer.”) about the date the animal is adopted because
we’re trying to find trends that affect animal adoption. We need this information so that we
can give good reports to our staffing manager about the number of employees needed for
each shift and on special days of the year (you don’t need to design information about “shifts”
for the employees as we already have that in the employee system).
After an animal has been taken into the shelter, we make sure that they have been given all
of the vaccinations. If an animal has been here a long time, we may administer many
vaccinations (like puppies getting their first and second set of shots).
********************************************************************************************************
1) On a separate sheet, create the ERD for the scenario above
2) On a separate sheet, list any business rules that you cannot model in your ERD.
3) Fill out the physical charts below for the following entities:
a. Animal
b. Shot Record
c. Vaccine
(The number of rows in the physical chart does not indicate how many attributes there are.)
Physical Layout Charts (Table Instance chart)
(Use one layout per entity. (You can add or delete rows as needed-right mouse)
PK-primary key
* – means required
FK-foreign key
o – means optional
Name of ENTITY: _______________________
Key type Optionality Column Name
(PK,FK)
( * or o)
Column Data Type
(num,char,date)
Name of ENTITY: _______________________
Key type Optionality Column Name
(PK,FK)
( * or o)
Column Data Type
(num,char,date)
Name of ENTITY:___________________
Key type Optionality Column Name
(PK,FK)
( * or o)
Column Data Type
(num,char,date)
…
Purchase answer to see full
attachment

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades