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:
MovieID - NUMBER (6)
Title - VARCHAR2 (50)
Genre- CHAR (3)
Year - NUMBER (4)
Runtime - NUMBER (3)
***Actor ID - VARCHAR2(4)
FName - VARCHAR2(15)
LName - VARCHAR2 (20)
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.
Using SQL* Plus, create SPOOL file called [url removed, login to view] 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.
Oracle8i Personal Edition