Homework 2 q Due the midnight of April

Homework #2 q Due the midnight of April 15 th. q 100 Points q In this homework, you create an SQL script to build the MOVIES database according to the schema shown in the next page. q Your script must be a text file (. txt). No other file formats (Word, Powerpoint, PDF, etc. ) will be accepted. q You must also meet all the constraint requirements given later. q You can use either My. SQL or Oracle as the platform CS 450 HW 2 1

The Movies Database varchar(40) char(1) char(9) PERSON Name Sex Imdb_nm date varchar(40) B_date D_date B_place char(9) varchar(60) char(9) varchar(12) Actor Movie Char_name Actor Movie Function PLAYS_IN FILM CS 450 HW 2 INVOLVES_IN varchar(60) char(9) dec(4, 0) char(4) Title Imdb_tt R_year Rating 2

Submission Send an email to the TA (ykim 9@gmu. edu): – Subject: 450 HW 2 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 (My. SQL or Oracle). – Your SQL script as an attachment, named as Lastname Last 4 Digits. Of. GID-hw 2. txt • For example, Joe Smith’s GMU ID ends with 1234, and his attachment must be named Smith-1234 -hw 2. txt CS 450 HW 2 3

Platform q My. SQL – On Windows: follow the instructions in Lecture 07 for installation. – On Linux: Sorry, you are own your own q 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. CS 450 HW 2 4

Answer Outline q In the first step, use the “create database MOVIES; ” command to create the database. q Use the “use MOVIES; ” command to switch to MOVIES as the default database. q Use the “create table” command to create the 4 tables: PERSON, FILM, INVOLVES_IN, and PLAYS_IN. q If necessary, use the “alter table” command to add foreign keys. q Use the “show columns from table” command to show the schemas of the three tables. CS 450 HW 2 5

Constraint Requirements q Primary keys of the tables must be specified. q All primary key attributes cannot be null. q All foreign keys in the database schema must be defined. q 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. q In the FILM table, title cannot be null. q In the PLAYS_IN table, character_name is defaulted to be “unknown”. CS 450 HW 2 6

Hints q First, you may want to practice SQL commands interactively in the “My. SQL Command Line Client” q While developing/testing your script (with Notepad for instance), copy-and-paste the commands in the script to the My. SQL client window and the commands will be executed. q Check the outputs of in the window for correctness. q 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. CS 450 HW 2 7