Consider the following relational database for the Central Zoo. Central Zoo wants to maintain information about its animals, the enclosures in which they live, and its zookeepers and the services they perform for the animals. In addition, Central Zoo has a program by which people can be sponsor of animals. Central Zoo wants to track its sponsors, their dependents, and associated data.

 

Each animal has a unique animal number and each enclosure has a unique enclosure number. An animal can live in only one enclosure. An enclosure can have several animals in it or it can be currently empty. A zookeeper has a unique employee number. Every animal has been cared for by at least one and generally many zookeepers; each zookeeper has cared for at least one and generally many animals. Each time a zookeeper performs a specific, significant service for an animal the service type, date, and time are recorded. A zookeeper may perform a particular service on a particular animal more than once on a given day.

 

A sponsor, who has a unique sponsor number and a unique social security number, sponsors at least one and possibly several animals. An animal may have several sponsors or none. For each animal that a particular sponsor sponsors, the zoo wants to track the annual sponsorship contribution and renewal date. In addition, Central Zoo wants to keep track of each sponsor's dependents. A sponsor may have several dependents or none. A dependent is associated with exactly one sponsor.

 

 

 

  Enclosure

Number

  

Type

  

Location

  

Size

 Date

Built

  ENCLOSURE Relation

 

  Animal

Number

  

Species

 Animal

Name

  

Gender

 Country

Of Birth

  

Weight

 Enclosure

Number

  ANIMAL Relation

 

  Employee

Number

 Employee

Name

  

Title

 Year

Hired

  ZOOKEEPER Relation

 

  Animal

Number

 Employee

Number

 Service

Type

  

Date

  

Time

  CARES FOR Relation

 

  Sponsor

Number

 Social Security

Number

 Sponsor

Name

  

Address

  

Telephone

  SPONSOR Relation

 

  Animal

Number

 Sponsor

Number

 Annual

Contribution

 Renewal

Date

  CONTRIBUTION Relation

 

  Sponsor

Number

 Dependent

Name

  

Relationship

 Date of

Birth

  DEPENDENT Relation

 

 

a.      Design a multidimensional database using a star schema for a data warehouse for the Central Zoo business environment. The subject will be "sponsorship" which represents a particular sponsor sponsoring a particular animal. Be sure to keep track of the annual contribution and the renewal date for the sponsorship. Assume that the annual contribution can change with the renewal date, i.e. it can be different in different years. ( do not design database)

my keys are the relation names.id e.g.  contribution.id

how would I write the script for changing the renewal date or notify when notification of renewal is needed?