Description
1. Write SQL queries for each of the tasks using MySQL workbench.
2. You cannot use Workbench’s GUI to create database schemas and tables – you must write your own SQL statements.
3. Follow the underscore convention when naming your database schema, entities and attributes.
4. Save your work as an SQL script – name your script LastName_Assignment#.extension
5. Submit your work via CourseWeb.
Consider the relational database schema diagram below. It consists of 3 entities – movies, actors, and locations.
Because movies and actors have a many-to-many relationship and movies and locations have a many-to-many relationship, your schema would also have 2 junction tables – movies_actors and movies_locations. Each table’s logical structure is described below:
movies
Field name Primary Key Data type Is null? Is auto-increment?
movie_id yes INT no yes
title no VARCHAR(200) no no
release_date no DATETIME no no
plot_description no VARCHAR(4000) no no
actors
Field name Primary Key Data type Is null? Is auto-increment?
actor_id yes INT no yes
first_name no VARCHAR(100) no no
last_name no VARCHAR(100) no no
birth_date no DATETIME no no
biography no VARCHAR(1000) no no
locations
Field name Primary Key Data type Is null? Is auto-increment?
location_id yes INT no yes
location_name no VARCHAR(100) no no
street_address no VARCHAR(150) no no
city no VARCHAR(100) no no
state no CHAR(2) no no
zip no VARCHAR(5) no no
movies_actors
Field name Primary Key Data type Is null? Is auto-increment?
movie_id no INT no no
actor_id no INT no no
movies_locations
Field name Primary Key Data type Is null? Is auto-increment?
movie_id no INT no no
location_id no INT no no
Task 1 (5 points): Create a database called movie_tracker using CREATE DATABASE statement.
Task 2 (25 points): In database movie_tracker, create the following entity tables:
1. movies
2. actors
3. locations
Each tables logical structure should correspond to the descriptions provided in this assignment. Use CREATE TABLE statement.
Task 3 (15 points): In database movie_tracker, create the following junction tables:
1. movies_actors
2. movies_locations
Use CREATE TABLE statement to create junction tables. Make sure to create appropriate foreign keys – each table will have two foreign keys. Use ALTER TABLE statement to create foreign keys.
Task 4 (20 points): For each entity table, insert at least 3 rows using INSERT statement:
1. At least 3 movies in the movies table
2. At least 3 actors in the actors table
3. At least 3 locations in the locations table
You can make up your own data for the INSERT statements.
Task 5 (10 points): For each junction table, create at least 2 relationships (insert at least two rows of appropriate IDs).
Task 6 (5 points): Write a SELECT statement to display top 2 actors sorted by actor’s last name.
Task 7 (5 points): Write a SELECT statement to display location name, street address and city sorted by location name is descending order.
Task 8 (5 points): Write a SELECT statement to display movies released between two dates of your choice.
Task 9 (5 points): Write an UPDATE statement to update zip code for all locations to 15217
Task 10 (5 points): DELETE one row of your choice from actors table. Be careful – make sure to write a correct WHERE clause.
