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:


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)



MovieID-NUMBER (6)



MovieID-NUMBER (6)




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 [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

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

Habilidades: Engenharia, MySQL, Oracle, PHP, Arquitetura de software, Teste de Software

Veja mais: www imdb com, www imdb, use case include, problem summary, one-stop plus, one stop plus, notice letter, letter of notice, imdb com, family id, crime by design, musical engineering, definition of format, definition format, use case, stop animation, sql commands, science fiction, NOI, Mystery, mys, mus, initials, horror, horror movie

Acerca do Empregador:
( 17 comentários ) United States

ID do Projeto: #2961166

Concedido a:


See private message.

$25.5 USD em 3 dias
(153 Comentários)

2 freelancers estão ofertando em média $34 para esse trabalho


See private message.

$42.5 USD in 3 dias
(81 Comentários)