SQL* Case Problem

Design a database of extensive movie collection. Several copies of some movies in different formats (VHS or DVD). These relations are created: Movie (_MovieID_, Title, Genre, Year, Runtime) Actor (_ActorID_, FName, LName) Appearance (_ActorID, MovieID_) Collection (_ItemID_, MovieID, Format, Quantity, Comments) Datatypes for each field: ***_Movie_*** MovieID - NUMBER (6) Title - VARCHAR2 (50) Genre- CHAR (3) Year - NUMBER (4) Runtime - NUMBER (3) ***Actor ***Actor ID - VARCHAR2(4) FName - VARCHAR2(15) LName - VARCHAR2 (20) ***_Appearance _***ActorID-VARCHAR2(4) MovieID-NUMBER (6) ***_Collection _***ItemID-NUMBER(4) MovieID-NUMBER (6) Format-CHAR(3) Quantity-NUMBER(2) Comments-VARCHAR2(50) Each movie has 6 digit number (created by you), which is assigned when entered in the database. The Year field contains year movie was released. Runtime field shows length of the movie in minutes (integer). Genre field contains three letter abbreviation of genre name. ACT for action ADV for adventure ANI for animation COM for comedy CRI for crime DOC for documentary DRA for drama FAM for family FAN for fantasy HOR for horror MUS for musical MYS for mystery NOI for film-noir ROM for romance SCI for science fiction SHO for short THR for thriller WAR for war WES for western The ActorID field contains actor's initials and one-or two digit number, such as SAC1. Appearance table shows actors who appeared in movie. For Collection table, the ItemID field contains unique four digit number (created by you), which is assigned whenever a new item is entered in database. ## Deliverables Using SQL* Plus, create SPOOL file called [login to view URL] and complete: 1. Use CREATE TABLE command to create the Movie table. Be sure to use primary key constraint. 2. Use CREATE TABLE command to create the Actor table. Be sure to use primary key constraint. 3. Use CREATE TABLE command to create the Appearance table. Be sure to include primary and foreign key constraint. 4. Use CREAT TABLE command to create the Collection table. Be sure to include the primary and foreign key constraints. 5. Use DESCRIBE command to look at the summary of each table's definition. If you notice any mistakes in the table definitions, either delete and recreate the table or use the ALTER TABLE commands to correct the mistakes. For 6-9, use INSERT, DELETE, and UPDATE commands to ensure that all of the data has been entered correctly. You can use movies that you own or visit www.imdb.com. 6. Add at least 5 movies to Movie table. 7. Add at least 1 actor from each movie into the Actor table. 8. Add records to the Appearance table showing in which movies each actor appeared. 9. Add each movie to the Collection table. At least one of the movies should appear in the database twice, once in VHS format and once in DVD. 10. Once all data has been input, use SELECT to display all of the data in each table. When all problems are complete, stop the SPOOL file using the SPOOL OFF command. ## Platform Oracle8i Personal Edition