Transcript Homework 2
Homework #2
Due the midnight of April 15th.
100 Points
In this homework, you create an SQL script to build
the MOVIES database according to the schema
shown in the next page.
Your script must be a text file (.txt). No other file
formats (Word, Powerpoint, PDF, etc.) will be
accepted.
You must also meet all the constraint requirements
given later.
You can use either MySQL or Oracle as the platform
CS450 HW2
Page 1
The Movies Database
varchar(40) char(1) char(9)
PERSON
Name
Sex
Imdb_nm
date
date
varchar(40)
B_date
D_date
B_place
char(9)
char(9)
varchar(60)
char(9)
char(9)
varchar(12)
Actor
Movie
Char_name
Actor
Movie
Function
PLAYS_IN
FILM
CS450 HW2
INVOLVES_IN
varchar(60)
char(9)
dec(4,0)
char(4)
Title
Imdb_tt
R_year
Rating
Page 2
Submission
Send an email to the TA ([email protected]):
– Subject: 450 HW2 from your name
• Replace the italic part by your own name
– Message body:
• Give your full name and the last 4 digits of GMU ID.
• Specify the platform you use (MySQL or Oracle).
– Your SQL script as an attachment, named as
LastnameLast4DigitsOfGID-hw2.txt
• For example, Joe Smith’s GMU ID ends with 1234, and
his attachment must be named
Smith-1234-hw2.txt
CS450 HW2
Page 3
Platform
MySQL
– On Windows: follow the instructions in Lecture 07
for installation.
– On Linux: Sorry, you are own your own
Oracle
– Read the instructions in
http://labs.ite.gmu.edu/reference/faq_oracle.htm to
setup your Oracle account.
– Contact the TA for further assistance.
CS450 HW2
Page 4
Answer Outline
In the first step, use the “create database
MOVIES;” command to create the database.
Use the “use MOVIES;” command to switch to
MOVIES as the default database.
Use the “create table” command to create the 4
tables: PERSON, FILM, INVOLVES_IN, and
PLAYS_IN.
If necessary, use the “alter table” command to
add foreign keys.
Use the “show columns from table”
command to show the schemas of the three tables.
CS450 HW2
Page 5
Constraint Requirements
Primary keys of the tables must be specified.
All primary key attributes cannot be null.
All foreign keys in the database schema must be
defined.
In the PERSON table,
– Name and B_date (birth date) cannot be null.
– B_date must be greater/later than or equal to 1800-01-01.
– D_date (death date) is defaulted to be null.
In the FILM table, title cannot be null.
In the PLAYS_IN table, character_name is
defaulted to be “unknown”.
CS450 HW2
Page 6
Hints
First, you may want to practice SQL commands
interactively in the “MySQL Command Line Client”
While developing/testing your script (with Notepad
for instance), copy-and-paste the commands in the
script to the MySQL client window and the
commands will be executed.
Check the outputs of in the window for correctness.
Keep in mind that every test will have to start with an
empty state --- use the “drop table” or “drop
database” commands to make sure if it.
CS450 HW2
Page 7