Database Design

Part 2. Database Design

The project must include at least 5
tables, and should have no more than 10 tables.

1.   
List
the entities of your database.

2.   
Present
the business rules that determine connectivity, using the format requested for
the Unit 2 Assignment. Do not give rules for links to associative entities;
simply give the M:N relationship rules and the name of the associative entity
you will use for that relationship.

Example:

Apartment – Tenant

One apartment may currently be occupied by one or more tenants.
(many)

One tenant may occupy many apartments (but only one at a
time). (many)

Apartment M:N Tenant; AE: ApartmentTenant

3.   
Draw
the ERD; copy the drawing into your Word project document.





  

Part
1. Database Definition


The
purpose of this database is to keep track of pets taken in by the animal rescue
group that I belong to, Another Life Animal Rescue. It will include data on
pets being surrendered, where pets are being fostered, as well as information
on adoptions. To keep things simple, I haven't included medical history and vet
visits or the financial aspect of those or adoption/surrender fees. I just
wanted to set something up that would keep track of where pets came from, where
they are housed while with us, and where they go when adopted. The database
would enable us to enter information on a pet surrendered to us, ascertain if
we have placed that pet before, keep track of where the pet is being fostered,
and enter information when the pet has been adopted. The group would be able to
pull reports of all pets currently in foster homes and available for adoption,
as well as on surrenders or adoptions during a given time period.

 

Define the purpose of the database. Who
will use it, etc.?

At least 6 sentences

 

Part
2. Database Design


List of Tables (Entity Types):

Pet

FosterHome

FosterPlacement (associative)

ClientFamily

Adoption (associative)

Surrender (associative)

Business Rules:



Pet –
FosterHome


A pet may have many foster homes
(but only one at a time).

A foster home may house many
pets.

Pet M:N FosterHome.  Associative Entity:  FosterPlacement

 

ClientFamily
– Pet (Adoption)


A client family may adopt many
pets.

An pet may be adopted by many
client families (but only one at a time).

ClientFamily M:N Pet.  Associative Entity:  Adoption

 

ClientFamily
– Pet (Surrender)


A client family may surrender
many pets.

A pet may be surrendered by many
client families (but only one at a time).

ClientFamily M:N Pet.  Associative Entity:  Surrender

  

Question
1


Find
a form on the Web and follow the bottom up database design approach. Please
include the website URL and an image of this form.


1.           
Find all the attributes
on the form.

2.           
Establish the
dependencies (determinants).

3.           
Group attributes that
have a common determinant into an entity type; name it.

4.           
Find directly-related
entity type pairs.

5.           
Determine the
connectivity for each pair.

6.           
Draw the ERD.

7.           
Review the ERD and
update to be in 3NF if ERD from step 6 is not in 3NF.


Powered by