Palms Zoo currently tracks its animals using a spreadsheet, and wants to upgrade to using a relational database. You have been tasked with this conversion, and need to convert this flat field design into a relational database design.

Ruby Palms Zoo has given you its current spreadsheet design, which contains the following fields (columns).

Animal ID

Animal Name

Birth Date

Acquisition Date

Species Scientific Name

Species Common Name

Species Description

Daily Food Need in Pounds

Food Purchased Through Date

Exhibit 1 Name

Exhibit 1 Revenue

Exhibit 1 Participated

Exhibit 2 Name

Exhibit 2 Revenue

Exhibit 2 Participated

Exhibit 3 Name

Exhibit 3 Revenue

Exhibit 3 Participated

Ruby Palms Zoo has also given you the following business rules, which are incomplete and are not all necessarily structural.

The date animals are acquired by the zoo is recorded.

Every animal has a species.

Every species requires an average number of pounds of food per day.

Food is purchased in bulk for each animal, and the zoo tracks how long the food will last for the animal.

Three special fundraising exhibits are offered each year.

Animals are selected to participate in the exhibits, and whether or not each animal participated in an exhibit is tracked.

1. To get started, create a complete list of business rules that you will use as a basis for your logical diagram. Your list of business rules should specify all entities, relationships, optionality constraints, and plurality constraints, and must make provision for the data and business rules given to you by Ruby Palms Zoo. There can be many solutions that correctly make provision for the given data and business rules.

2. Now create a logical entity-relationship diagram (ERD) that illustrates the relational database design described by the business rules in #1. All entities should be normalized to BCNF. Recall that logical ERDs contain SQL-based constraints on the attributes, including primary and foreign key constraints.