Diagram a conceptual (ER) data model for the following case. Be sure to indicate your entity names, cardinalities, and primary ids. It is not necessary to label your relationships unless it adds clarity. Account for the attributes shown here but it is not necessary to add additional attributes. Draw a model using pen and paper and then upload it. Do not use a Lucid chart or Visio.

 

            The Ram Zoo is a small zoo located at the Veterinary Teaching Hospital at CSU.  The zoo executive committee and some of the funding agencies require specific reports about the zoo inventory and management practices. Susan Smith, director of wildlife biology research at the zoo, is responsible for managing the overall animal inventory and monitoring activities. She has 21 staff members who work for the zoo; 6 are zoo employees, 2 are interns, and 13 are student volunteers. Sue needs to match each staff member to the most appropriate task, so in addition to the typical employee attributes, she must track multiple skills and species expertise for all staff members. 

Sue needs to record information about the multiple exhibit areas, the various species that reside within the exhibits (several may share an exhibit, some cannot share exhibit space), species category and description, and typical habitat. A particular animal or collection of animals may move from one exhibit to another over time. The staff are assigned to an exhibit based on their skill set and are used to record animal behaviors through an observation log. Each exhibit has a name and description, primary donor information and a list of the current animals sharing the exhibit. Each animal has an ID, name, species, gender, age, origin and an indicator of whether the animal can share exhibit space. The observation log is used to record dates and times of activities such as aggression, nesting, courting, feeding, mating, etc. All medical treatments of the animals are maintained in a separate clinical system.  
Q2. A liquor distributor sells multiple categories of beverages to retail liquor stores. The distributor tracks the following attributes for its different kinds of beverages:

 

Beer:                SKU, Name, Style, Brewery, City, CaseQuantity, ABV, Size, UnitPrice, Package, Cost

Wine:               SKU, Name, Varietal, Style, Winery, City, CaseQuantity, %Alc, Size, Package, Importer, UnitPrice, Year, Cost

Spirits:             SKU, Name, Style, Distiller, City, SubStyle, Proof, Size, Package, Importer, Year, UnitPrice, Cost

NABev:           SKU, Name, Style, City, Size, UnitPrice, Package, Cost

 

Each beverage represents only one of these categories.

• Show both a conceptual and logical data model below to represent this situation.  
•  What is this advanced form of ERD called?
• What are the values in the domain of the Beverage Category?

CONCEPTUAL MODEL                                                                      LOGICAL MODEL
Draw a model using pen and paper and then upload it. Do not use a Lucid chart or Visio.

Q4. Using the transformation rules, how many relations will it take to transform this hypothetical ERD. MV stands for multi-valued attributes, TD stands for transitive dependency and 2NF stands for a second normal form violation.

 

____________________  Relations

 

If you apply the domain concept to each of the multivalued attributes then how many total relations will be required? _____________ Relations

 

MY 7+ 2NF MV TD MV