Assignment $4$ is to be completed at the end of Module $4.$ It is worth $9\%$ of your final mark, and is graded with a total of $20$ marks.

This project involves designing a database to represent a personal movie collection. It should allow for the capture of data about each movie and the principal people involved as members of the cast and crew. It is suggested you use Microsoft Excel to work with the sample data required in each question. The Internet Movie Database at www$.$imdb.com is an excellent resource for sample data $($no need to cite$).$

Work to be Submitted

Submit a $.$zip file named COMP$3611\_$Assignment$4$ that contains:

Excel spreadsheet containing the work from questions $1,3,$ and $5$

Diagrams from question $3$ and $5$

A document containing the answers to questions $2$ and $4$

Instructions

Design the database in an MS Excel spreadsheet as a single relation called Movie. It should contain an ID$,$ Title, Year Released, Genre $($e$.$g$.,$ sci$-$fi$,$ comedy, thriller, etc.$),$ Rating $($e$.$g$.,$ G$,$ PG$,$ R$,$ etc.$),$ Format $($e$.$g$.,$ VHS$,$ DVD$,$ MP$3,$ etc.$),$ a free$-$form Comments field, plus the main cast and crew members. Add the following entry plus at least two of your own: $(2$ marks$)$

ID: $1$

Title: Star Trek

Year: $2009$

Genre: Sci$-$Fi

Rating: PG

Format: DVD

Director: J$.$J$.$ Abrams

Starring: Chris Pine $($Capt$.$ Kirk$),$ Zachary Quinto $($Mr$.$ Spock$)$ Zoe Saldana $($Uhura$),$ Karl Urban $($Bones McCoy$)$

What normal form is this design? Why? What should be done to correct the design? $(3$ marks$)$

Draw your corrected design as a logical ERD showing attributes and multiplicities. This must be done as a drawing $($use IE Notation in Oracle Data Modeler$).$ No need to include physical data types. Modify the sample data from step $1$ in a new page of the spreadsheet to match this design. $(5$ marks$)$

For the corrected design, please answer the following: $(5$ marks$)$

What normal form is this corrected design? Why?

Can it handle a movie being coded for multiple Genres $($e$.$g$.$ Star Trek is both Sci$-$Fi and Action$)?$

Does it have minimal data changes if a Genre name was changed $($e$.$g$.,$ Thriller becomes Suspense$)?$

Can it handle a person being associated with a movie multiple ways $($e$.$g$.,$ Clint Eastwood both directed and played the role of Bill Munny in $$Unforgiven$)?$

Can it handle a person playing multiple characters $($e$.$g$.,$ Eddie Murphy in $$The Nutty Professor$$ played seven different roles: Sherman Klump, Buddy Love, Lance Perkins, etc.$)?$

What further corrections need to be made to this design? Why?

Finalize your design as a logical ERD showing attributes and multiplicities. This must be done as a drawing $($use IE Notation in Oracle Data Modeler,$).$ There is no need to include physical data types. Modify your sample data from step $3$ to a new page of the spreadsheet, and include the following: $(5$ marks$)$

The additional movies referenced in step $4.$

Add two additional records of your own. $($Hint: $$The Matrix$$ would be one good choice with the Wachowski siblings having multiple crew roles.$)$