1 / 7

Movies Database SQL Script Homework

Create an SQL script for building the MOVIES database. Follow schema requirements, constraints, and submission guidelines. Practice using MySQL or Oracle platform to complete the assignment.

mbegay
Download Presentation

Movies Database SQL Script Homework

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 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

  2. The Movies Database varchar(40) char(1) char(9) date date varchar(40) PERSON Name Sex Imdb_nm 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 INVOLVES_IN dec(4,0) char(4) varchar(60) char(9) FILM Title Imdb_tt R_year Rating

  3. Submission Send an email to the TA (ykim9@gmu.edu): • 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 asLastnameLast4DigitsOfGID-hw2.txt • For example, Joe Smith’s GMU ID ends with 1234, and his attachment must be named Smith-1234-hw2.txt

  4. 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.

  5. 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.

  6. 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”.

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

More Related