Write and execute the SQL commands in VLABS SQL Server to create the seven tables and the primary/foreign keys for those tables.

The seven tables are on the right hand of the picture, which are:

Customer_Rentals

Financial_Transcations

Rental_Status_codes

Transaction_Types

Customers

Accounts

Payment_Methods

The sample of creating the left seven table is list below:

/* This script will create 7 tables of the FLIX2YOU database

along with primary and foreign keys */

/* Modifications to script:

(1) reversed order of drop table financial_transactions and account; 1/3/13 by glh

*/

/* Drop tables that may already exist */

/* The following code is commented out

and is available for your use if you

need to drop all the tables

DROP TABLE movie_cast;

DROP TABLE financial_transactions;

DROP TABLE accounts;

DROP TABLE customer_rentals;

DROP TABLE movies;

DROP TABLE genre_codes;

DROP TABLE format_types;

DROP TABLE video_stores;

DROP TABLE condition_codes;

DROP TABLE actors;

DROP TABLE customers;

DROP TABLE rental_status_codes;

DROP TABLE transaction_types;

DROP TABLE payment_methods;

*/

/* create tables */

/* genre_codes */

CREATE TABLE genre_codes(

genre_code int IDENTITY(1,1) NOT NULL,

genre_code_description varchar(32) NOT NULL);

/* format_types */

CREATE TABLE format_types(

format_type_code int IDENTITY(1,1) NOT NULL,

format_type_description varchar(32) NOT NULL);

/* video_stores */

CREATE TABLE video_stores(

store_id int IDENTITY(1,1) NOT NULL,

store_name varchar(32) NOT NULL,

store_address varchar(128) NOT NULL,

store_city varchar(32) NOT NULL,

store_state varchar(2) NOT NULL,

store_zip varchar(12) NOT NULL,

store_email varchar(128) NOT NULL,

other_store_details varchar(512) NOT NULL);

/* condition_code */

CREATE TABLE condition_codes(

condition_code int IDENTITY(1,1) NOT NULL,

condition_code_description varchar(32) NOT NULL);

/* actors */

CREATE TABLE actors(

actor_id int IDENTITY(1,1) NOT NULL,

actor_gender char(1) NOT NULL,

actor_first_name varchar(32) NOT NULL,

actor_last_name varchar(32) NOT NULL,

other_actor_details varchar(512) NOT NULL);

/* movies */

CREATE TABLE movies(

movie_id int IDENTITY(1,1) NOT NULL,

condition_code int NOT NULL,

format_type_code int NOT NULL,

genre_type_code int NOT NULL,

store_id int NOT NULL,

release_year int NOT NULL,

movie_title varchar(128) NOT NULL,

movie_description varchar(1024) NOT NULL,

number_in_stock int NOT NULL,

rental_or_sale_or_both tinyint NOT NULL,

rental_daily_rate money NOT NULL,

sales_price money NOT NULL);

/* movie_cast */

CREATE TABLE movie_cast(

movie_id int NOT NULL,

actor_id int NOT NULL);

/* create primary keys with ALTER TABLE statement */

ALTER TABLE genre_codes ADD CONSTRAINT pk_genre_codes PRIMARY KEY (genre_code);

ALTER TABLE format_types ADD CONSTRAINT pk_format_types PRIMARY KEY (format_type_code);

ALTER TABLE video_stores ADD CONSTRAINT pk_video_stores PRIMARY KEY (store_id);

ALTER TABLE condition_codes ADD CONSTRAINT pk_condition_codes PRIMARY KEY (condition_code);

ALTER TABLE actors ADD CONSTRAINT pk_actors PRIMARY KEY (actor_id);

ALTER TABLE movies ADD CONSTRAINT pk_movies PRIMARY KEY (movie_id);

ALTER TABLE movie_cast ADD CONSTRAINT pk_movie_cast PRIMARY KEY (movie_id, actor_id);

/* end of primary key creation */

/* create foreign keys */

ALTER TABLE movie_cast ADD CONSTRAINT fk_Movie_cast_actors FOREIGN KEY(actor_id)

REFERENCES actors (actor_id);

ALTER TABLE movie_cast ADD CONSTRAINT fk_movie_cast_movies FOREIGN KEY(movie_id)

REFERENCES movies (movie_id);

ALTER TABLE movies ADD CONSTRAINT fk_movies_condition_codes FOREIGN KEY(condition_code)

REFERENCES condition_codes (condition_code);

ALTER TABLE movies ADD CONSTRAINT fk_movies_format_types FOREIGN KEY(format_type_code)

REFERENCES format_types (format_type_code);

ALTER TABLE movies ADD CONSTRAINT fk_movies_genre_codes FOREIGN KEY(genre_type_code)

REFERENCES genre_codes (genre_code);

ALTER TABLE movies ADD CONSTRAINT fk_movies_video_Stores FOREIGN KEY(store_id)

REFERENCES video_Stores (store_id);

/* END OF SCRIPT */

Video Stores Fk store_id Flix2You-ERD (Initial Design: July 2002) store_name store address store phone store_email other_store details Movies Customers ercustomer_id Pkmovie_id Fcondition_code Fr format type_code Fik genre_type_code F store_id Customer_Rentals PRitem_rental_id FR customer_id Fkmovie_id @vental-status-code member_yr membership_number date_became member customer first _name customer_ last_name customer address customer_phone customer_email customer dob Condition_ Codes release_year movie title movie_description number_in_stock rental or sale_or_both rental daily rate sale_price Pcondition_code condition_description eg Rental, Used for Sale rental date out rental _date_ returned rental_amount due other_rental_details Format_Types PRformat_type_code format_ type_description eg DVD, Video. Financial Transactions Pktransaction_id account-id @item-rental-id Fr previous transaction id Frtransaction_type_code Accounts PBaccount_id FR customer id FRpayment_method_code Genre_Codes Movie Cast Pk genre_code movie_ id PF actor id account _name account_details genre_description eg Comedy, Western transaction_date transaction_amount transaction_comment Actors PR actor_id Transaction_Types Payment Methods Rental Status_ Codes PRrental status_code actor_gender actor_first_name actor_ last_name other actor_details Pk transaction_type_code Pk payment_method_code transaction_type_description rental status_description eg Overdue. payment_method_description eg Cash, CC (Credit Card). eg Payment, Refund Video Stores Fk store_id Flix2You-ERD (Initial Design: July 2002) store_name store address store phone store_email other_store details Movies Customers ercustomer_id Pkmovie_id Fcondition_code Fr format type_code Fik genre_type_code F store_id Customer_Rentals PRitem_rental_id FR customer_id Fkmovie_id @vental-status-code member_yr membership_number date_became member customer first _name customer_ last_name customer address customer_phone customer_email customer dob Condition_ Codes release_year movie title movie_description number_in_stock rental or sale_or_both rental daily rate sale_price Pcondition_code condition_description eg Rental, Used for Sale rental date out rental _date_ returned rental_amount due other_rental_details Format_Types PRformat_type_code format_ type_description eg DVD, Video. Financial Transactions Pktransaction_id account-id @item-rental-id Fr previous transaction id Frtransaction_type_code Accounts PBaccount_id FR customer id FRpayment_method_code Genre_Codes Movie Cast Pk genre_code movie_ id PF actor id account _name account_details genre_description eg Comedy, Western transaction_date transaction_amount transaction_comment Actors PR actor_id Transaction_Types Payment Methods Rental Status_ Codes PRrental status_code actor_gender actor_first_name actor_ last_name other actor_details Pk transaction_type_code Pk payment_method_code transaction_type_description rental status_description eg Overdue. payment_method_description eg Cash, CC (Credit Card). eg Payment, Refund