The solution:

  1. multiple relations - superclass and subclasses

    ROLE(role)
    MOVIE(title,year,country,runningTime)
    RATING(rating,country)
    MOVIE(title,year,country,runningTime, directorFirstName, directorLastName)
    WRITES(firstName,lastName,capacity,title,year,country)
    WORKS_ON(firstName,lastName,job,title,year,country)
    IS_RATED(title,year,country,rating,ratingCountry)
    ACTS_IN(firstName,lastName,role,title,year,country,creditedAs)
    PERSON(firstName,lastName,birthdate,birthplace)
    DIRECTOR(firstName,lastName)
    WRITER(firstName,lastName)
    ACTOR(firstName,lastName)
    CREW(firstName,lastName)
        
  2. multiple relations - subclasses only

    ROLE(role)
    MOVIE(title,year,country,runningTime)
    RATING(rating,country)
    MOVIE(title,year,country,runningTime, directorFirstName, directorLastName)
    WRITES(firstName,lastName,capacity,title,year,country)
    WORKS_ON(firstName,lastName,job,title,year,country)
    IS_RATED(title,year,country,rating,ratingCountry)
    ACTS_IN(firstName,lastName,role,title,year,country,creditedAs)
    DIRECTOR(firstName,lastName,birthdate,birthplace)
    WRITER(firstName,lastName,birthdate,birthplace)
    ACTOR(firstName,lastName,birthdate,birthplace)
    CREW(firstName,lastName,birthdate,birthplace)
        
  3. single relation - single type attribute

    ROLE(role)
    MOVIE(title,year,country,runningTime)
    RATING(rating,country)
    MOVIE(title,year,country,runningTime, directorFirstName, directorLastName)
    WRITES(firstName,lastName,capacity,title,year,country)
    WORKS_ON(firstName,lastName,job,title,year,country)
    IS_RATED(title,year,country,rating,ratingCountry)
    ACTS_IN(firstName,lastName,role,title,year,country,creditedAs)
    PERSON(firstName,lastName,birthdate,birthplace,jobtype)
        
  4. single relation - multiple type attributes

    ROLE(role)
    MOVIE(title,year,country,runningTime)
    RATING(rating,country)
    MOVIE(title,year,country,runningTime, directorFirstName, directorLastName)
    WRITES(firstName,lastName,capacity,title,year,country)
    WORKS_ON(firstName,lastName,job,title,year,country)
    IS_RATED(title,year,country,rating,ratingCountry)
    ACTS_IN(firstName,lastName,role,title,year,country,creditedAs)
    PERSON(firstName,lastName,birthdate,birthplace,isDirector,isWriter,isActor,isCrew)
        

The "single relation single type attribute" approach is not applicable when the specializations can overlap (e.g. a person can be both a director and an actor). Disjoint vs overlap was omitted from the diagram, but outside knowledge indicates that "overlap" is the appropriate thing.

The "multiple relations subclasses only" approach means birth date and place information is repeated in each relation that a particular person belongs to, both multiple relations approaches mean that a person's name may be stored in as many as five different places (PERSON, DIRECTOR, WRITER, ACTOR, CREW).

This leaves the "single relation multiple type attributes" approach as the best in this situation - it avoids the problems mentioned above and since Director, Writer, Actor, and Crew don't have any attributes of their own, there isn't the problem of PERSON having attributes not applicable to a particular person.



Explanation:

  1. Each entity type becomes a relation with the attributes of the entity type and a primary key corresponding to the key of the entity type.

    	  ROLE(role)
    	  MOVIE(title,year,country,runningTime)
    	  RATING(rating,country)
    	
  2. There are no weak entities.

  3. There aren't any binary 1:1 relationships.

  4. Binary 1:N relationship types can be mapped using the foreign key approach (adding the relationship information to the N side) or the cross-reference approach. Both are fine for Directs because there is no total participation.

    	  MOVIE(title,year,country,runningTime, directorFirstName, directorLastName)
    
  5. Binary M:N relationships are mapped using the cross reference approach. The attributes of the new relation are the primary key attributes of the participating entity types, and all attributes form the primary key.

    	  WRITES(firstName,lastName,capacity,title,year,country)
    	  WORKS_ON(firstName,lastName,job,title,year,country)
    	  IS_RATED(title,year,country,rating,country)
    
  6. There aren't any multivalued attributes.

  7. The cross-reference approach is used for n-ary relationships.

    	  ACTS_IN(firstName,lastName,role,title,year,country,creditedAs)
    	    

    It is not necessary to include role in the primary key because an actor can only have one role in a particular movie - thus, the actor and movie are sufficient to identify a tuple in ACTS_IN. A similar argument can be made for firstName and lastName.

    1. "Multiple relations - superclass and subclasses" means that each of Person, Director, Writer, Actor, and Crew get their own relations.

      	    PERSON(firstName,lastName,birthdate,birthplace)
      	    DIRECTOR(firstName,lastName)
      	    WRITER(firstName,lastName)
      	    ACTOR(firstName,lastName)
      	    CREW(firstName,lastName)
      	  
      • firstName and lastName in DIRECTOR, WRITER, ACTOR, CREW refer to the corresponding attributes in PERSON
    2. "Multiple relations - subclasses only" means that there is not a separate Person relation.

      	    DIRECTOR(firstName,lastName,birthdate,birthplace)
      	    WRITER(firstName,lastName,birthdate,birthplace)
      	    ACTOR(firstName,lastName,birthdate,birthplace)
      	    CREW(firstName,lastName,birthdate,birthplace)
      	  
    3. Single relation - single type attribute

      	    PERSON(firstName,lastName,birthdate,birthplace,jobtype)
      	    
    4. Single relation - multiple type attributes

      	    PERSON(firstName,lastName,birthdate,birthplace,isDirector,isWriter,isActor,isCrew)
      	    
  8. There aren't any category types.