Problem:

Design an E$-$R Schema $($Entity$-$Relationship Diagram$)$ for keeping track of information about

movies, the cast of the movies, movie studios, and the agent of the movie stars $($Note: a cast

consists of many movie stars.$).$

Suggestion for drawing the ER diagram:

Option $1($recommended$)$: Use a drawing software such as Microsoft Visio,

draw.io$,$

ERDPlus or Lucidchart.

Option $2$: You can draw the ER diagram by hand, take a clear picture and insert it into the

Word document.

Information about the Database:

The database keeps track of the following information about a movie:

Movie title,

Film type $($e$.$g$.,$ horror, science fiction, Western, comedy, $...),$

Year

The length of the movie in Minutes.

The cast of the movies includes the 'name' of the movie stars and the 'status' of the movie star.

The domain $($value set$)$ of the status is $\{\text{'}$contract$\text{'},$ 'temporary'$\}.$ A movie obviously can have many

stars, and a movie star can be in different movies.

Whether a movie star is on contract or a temporary actor$/$actress$,$ they may or may not be

represented by an agent. An agent can represent one or more movie stars but a movie star $($if

he$/$she has an agent$)$ belongs to a single agent only $($hint: think about relationship constraints and

cardinality ratio$).$

The database keeps track of the following information of an agent: name, agency, and contact

information. Agency is the company that an agent works for. If an agent does not work for a

company, he$/$she is called an independent agent. In that case, the value of agency is

'Independent' $($as the name of the company$).$ Contact information includes: phone number and

address. An agent may have more than one phone number, and the address consists of house

number, street name, city, state, and zip code.

Each movie is owned by a studio and a studio owns many movies. Studio attributes consist of

studio name and address.

To Draw an E$-$R Schema $($E$-$R Diagram$)$ for this database, you can make appropriate assumptions

if the assumptions do not contradict the stated requirements. Make the necessary and

appropriate cardinality ratios and dependency$/$participation constraints on all the relationships

even though they are not specified in the requirements.