HW2 ER Modelling

HW2 is due 11/1 at 11:59 pm. Email digital submissions (e.g. draw ER diagram in powerpoint/open office/visio/etc or scan a nice hand drawn model) to Adam and myself with a subject of [CAPPDB] HW2 Submission. Include your name in the submission file(s).

Problem 1. Simple Movies DB

Build an ER model to capture the following requirements. Explicit relationships are not given, you must determine the relationships, cardinality, and participation requirements.

A new app is interested in storing information about movies, actors, and studios that make movies. Design an ER diagram for the following data requirements:

  • A movie has a title, run length in minutes, and a single genre (e.g. horror, comedy).
  • Movies have sequels.
  • An actor has a name, age, gender, and minimum salary. An actor must have been in at least one movie to be considered an actor.
  • A movie has a single director. Directors have name, age, and a favorite genre. An actor cannot be a director.
  • A movie is produced by studios. Each studio has a name and zip code.

Problem 2. Simple Movies II

Modify the previous ER diagram such that:

  • A movie is produced by a studio, and has a list of producers. Each producer has a name and age.
  • In a given movie an actor, director, and producer can be the same person

Problem 3. Music Store

Design a ER diagram for a database for an application that sells digital albums and songs, and allows users to create playlists.

  • A song has a title, length, an artist (e.g. band or person), and a set of composers.
  • An artist has a name and a number of members.
  • A composer has a name and age. A composer must have written at least one song.
  • An album is composed of songs, an album cover (a string), and a track order (ie SongX is track 1. SongY is track 2…).
  • Songs have an individual price. Albums have a price that is cheaper than purchasing each song individually.
  • Users have names and email addresses.
  • Users make purchases of songs and albums.
  • Users make (ordered) playlists of songs.
  • Users can review (a score of 1-5) playlists or albums, but not songs. Reviews can also have a comment.