Question

Draw a complete ERD of a database design to meet their needs

First Urban Rescue (FUR ) takes in cats and dogs that are no longer wanted in their old home , and tries to find them new homes . For each pet taken in by FUR , the pet is given a name if it doesn't already have one . The breed , name , approximate age , height , weight , and date arrived at FUR is recorded for every pet that comes in . For each breed , FUR keeps the breed name , the normal size of that breed (" very small ", "small ", medium ", large ", or " very large ) , a general description of the breed , and whether it is a canine or feline breed . Every pet is associated with one and only one breed . FUR may have several pets of a given breed . It is possible that there is data about certain breeds for which FUR has never had a pet of that breed . One of the first things that FUR does with new pets is to ensure that they receive the proper vaccinations . For each vaccination available , the disease that it prevents , the type of vaccine , and a description of the vaccination are recorded . A pet will typically receive many vaccinations , although some pets don't need any . A vaccination can be given to several different pets , although some new vaccinations are occasionally developed that haven't been used on any pet yet . Each time a pet receives a vaccination , the date administered must be recorded . When a person adopts a pet , the person's name and address are recorded along with the adoption date of that pet . A person can adopt several pets . Unfortunately , adoptions do not always work out , so a pet can be returned . If a pet is returned , the date of return for that adoption must be recorded . Therefore , it is possible for a pet to be adopted more than once . Not all pets get adopted . Only people that have adopted a pet are recorded in the system

Answer 1

Please find below the steps to create ERD for the mentioned ‘First Urban Rescue (FUR)’ organisation:

Step 1: List entities:

1. PET: The details of the pet taken by FUR

2. BREED: The breed of the pet

3. VACCINE: The vaccine to be given to the pet

4. PEOPLE: The people who adopt the pet

Step 2: List associated relationships among entities:

1. Each pet has exactly one breed but a breed has zero or many pets of its type. This is M:1 relationship between pet and breed.

2. Pet gets none or many vaccines and a vaccine is also given to none or many pets, it is M:N relationship with additional attribute ‘vaccine_date’. It will be stored in new entity PETVACCINE.

3. Pet can be adopted none or many times by single Person where details as (adopt_Date, return_Date) are to be recorded.

Step 3: Add attributes:

1. PET( pet_id, breed_id , name , approximate age , height , weight , date_arrived )

2. BREED(breed_id,name, size, description, type); where type says wether it is canine or feline

3. VACCINE(vaccine_id, prevented_disease, description, type)

4. PERSON(person_id, person_name, person_Address, adoption_date, return_date)

Step 4: Final ERD:

**PK is primary key and FK is foreign key