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
LastnameLast4DigitsOfGID-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