moviedbnew_data.sql

# TEST DATA --

/* Data for Person table */ INSERT INTO Person (first_name, last_name, lives_in) VALUES('Victor' , 'Fleming', 'NY'); INSERT INTO Person (first_name, last_name, lives_in) VALUES('George' , 'Lucas', 'LA'); INSERT INTO Person (first_name, last_name, lives_in) VALUES('James' , 'Cameron', 'LA');

/* Data for Movie table */ INSERT INTO Movie (title, release_date, director) VALUES ('Gone with the Wind', '1939/01/01', 1); INSERT INTO Movie (title, release_date, director) VALUES('Star Wars', '1977/01/01', 2);

/* Data for Reviewer table */

INSERT INTO Reviewer (reviewer, worked_for) VALUES (2, 'New York Times'); INSERT INTO Reviewer (reviewer, worked_for) VALUES (2, 'Washington Post'); INSERT INTO Reviewer (reviewer, worked_for) VALUES (3, 'Hollywood Reporter');

/* Data for Rating table */

INSERT INTO Rating (rID, mID, stars, rating_date) VALUES (1, 1, 3, '1980/02/02'); INSERT INTO Rating (rID, mID, stars, rating_date) VALUES (3, 2, 3, '1988/12/12');

1. Create a new database named moviedbnew. Remember to use DROP database command before your CREATE command. Similarly use DROP Table command before CREATE command for each table. 2. Create a table named Movie with following attributes and types: a. mID - This field will store movie ID in the form of an integer. This should have adisplay width of 5. You can assume that your database will store information on maximum of 50000 movies. Choose data type accordingly. b. title - This field will store movie name. You can assume that movie name willnot be longer than 100 characters. Choose data type accordingly. C. release_date - This field will store the movie release date. d. director - This field will store the an integer value (pld value from the Person's table). 3. Create a table named Person with the following attributes and types: a. pID - This field will store person ID in the form of an integer. You can assumethat your database will store information about maximum of 30000 people. Choose data type accordingly. b. first_name - This field will store the first name of the person. You can assumethat the first_name will be maximum 70 characters long. c. last_name --- This field will store the last name of the person. You can assumethat the last_name will be maximum 70 characters long. d. lives_in - This field will store the person's residence state information. Example - TN, NY, CA etc. 4. Create a table named Reviewer with the following attributes and types: a. rID - This field will store reviewer ID. You can assume that your database willstore maximum of 100,000 entries in this table. b. reviewer This field will store the an integer value (plD value from the Person'stable). C. worked_for This field will store the name of companies like NYT', 'Washington Post' etc. You can assume that the company name will be maximumof 70 characters long. 5. Create a table named Rating with the following attributes and types: a. rID - This field will store reviewer ID from reviewer's table. b. mID - This field will store movie ID from the movie table. C. stars - This field will store the number of stars given by the reviewer to a movie. The possible values are 1,2,3,4,5. d. rating_date - This field will store the date and time information about the review submitted by reviewer. You should now have the following schema. Make sure that you have correct datatypes on all the attributes: a. Movie(mlD, title, release_date, director) b. Person(pID, first_name, last_name, lives_in) C. Reviewer(rID, reviewer, worked_for) d. Rating (rID, MID, stars, rating_date) Place the following constraints on the attributes and tables as listed: Key Constraints: Modify the tables with following key constraints. Primary/Unique Keys 1. mID is Primary key for Movie table. This field will have auto_increment. 2. (title, release_date) is a unique key for Movie table 3. ID is Primary key for Reviewer table. This field will have auto_increment. 4. PID is Primary key for Person table. This field will have auto_increment. 5. (rID, MID, rating_date) is Primary key for Rating table Foreign Keys 6. Movie table a. director is Foreign key that refers to person table's pls attribute. 7. Reviewer table a. reviewer is Foreign key that refers to person table's pld attribute 8. Rating table a. rID is Foreign key that refers to Reviewer table rls attribute b. mld is Foreign key that refers to Movie table mld attribute NOT NULL Constraints 1. No attributes can be NULL in Person table 2. Rating stars may not be NULL in rating table After creating your tables, insert the data from the moviedbnew_data.sql file (that is provided to you) in various tables. You should be successfully able to insert all data if your constraints are correct