This assignment has two steps. In step one, you will develop an entity-relationship diagram (ERD) for the attached specifications in English. In step two, you will create an entity-relationship diagram from the relational tables I provide. For both steps clearly identify the entity sets, relationship sets, multiplicity (i.e., many-one, many-many, etc.), attributes, keys, and constraints in your model, using the notations and diagramming rules as described in the textbook and in class.

The textbook — and our examples in the lectures — uses the “Stanford” style of E/R modelling. There are many, many “dialects” of E-R. However, for consistency, stay with the textbook's dialect.

Warning: A different textbook was used previously in this course which used a different dialect (the “Wisconsin” dialect) of E/R. There is lots of “3421” materials around with E/R examples written in the Wisconsin dialect. Feel free to use these for studying and for reference, but be aware of the differences. And do your work in the Stanford dialect.

Keep in mind that the case is designed to simulate a real life system-analysis situation; hence, the conversations are unstructured, and sometimes fuzzy. It is your responsibility — as an analyst / designer — to translate what you heard / read into an ERD.

The Zoo has many types of animals. Every type has a unique name. Every animal of the same type has a unique animal ID. Animals in two types may have the same animal ID. Animals also have age and gender. Animals may have diseases. The beginning time and the duration of a disease need to be recorded. A disease has a unique name. A type keeper takes care of only one type of animals. Every type may have many type keepers. A type keeper may or may not be familiar with diseases. But every disease must be handled by at least one type keeper. Type keepers have name, employee ID, ssn, address and phone number. Every animal is in a cage. Some cages may be empty. Every cage has a cage ID, space and height. A cage keeper may take care of many cages. Every non-empty cage must have at least one cage keeper. Empty cages don’t need any cage keepers. Cage keepers have name, employee ID, ssn, address and phone number.

• Create an ER diagram for these specifications (6 points)
• Which of the specifications stated above cannot be represented in the diagram? Quote exactly from the text above without adding any additional comments (2 points)

Consider the following relational schema (primary keys are undelined):

Person(p#,name,birthdate,nationality,gender)

Actor(p#,aguild#)
	FK (p#) refs Person

Director(p#,dguild#)
	FK (p#) refs Person

Writer(p#, wguild#)
	FK (p#) refs Person

Studio(name)

ScreenPlay(title,year)

Authored(title,year,writer)
	FK (title, year) refs ScreenPlay
	FK (writer) refs Writer (p#)

Movie(title,studio,year,genre,director,length)
	FK (studio) refs Studio (name)
	FK (title, year) refs ScreenPlay
	FK (director) refs Director (p#)

Cast(title,studio,year,role,actor,minutes)
	FK (title, studio, year) refs Movie
	FK (actor) refs Actor (p#)

Affiliated(director,studio)
	FK (director) refs Director (p#)
	FK (studio) refs Studio (name)

• Convert this schema back to the ER diagram (8 points)
• Assume that there is an FD: name,birthdate,gender -> nationality in Person relation. Answer the following questions:
  • Is Person in BCNF? If not, show the relations after the decomposition of Person into BCNF (2 points)
  • Is Person in 3NF? If not, show the relations after the decomposition of Person into 3NF (2 points)