OVERVIEW This lab provides you the opportunity to create tables, primary keys an
ID: 3805383 • Letter: O
Question
OVERVIEW
This lab provides you the opportunity to create tables, primary keys and foreign keys with the use of SQL commands. The lab will utilize the FLIX2YOU problem.
In the FLIX2YOU product document, there is an Entity Relationship Diagram (ERD) showing the current database schema for FLIX2YOU. You will also find a script file named FLIX2YOU_current.txtPreview the documentView in a new window The execution of the script file in SQL Server Management Studio will create seven of the fourteen tables for the FLIX2YOU database. The script will create the tables along with the primary and foreign keys for the seven table displayed on the left hand side of the ERD.
PART 1
The first part of this lab you will use the script providedPreview the documentView in a new window and execute the script in your own folder in VLABS SQL Server.
PART 2
The second part of the lab you will create a data dictionary for the remaining seven tables (right hand side of the FLIX2YOU ERD). The data dictionary can be a formatted Word document or an Excel spreadsheet file. Please follow the format of the sample data dictionary found on table 3.6 on page 92 of the textbook. Then you will write and execute the SQL commands in VLABS SQL Server to create the seven tables and the primary/foreign keys for those tables.
You will submit a document that includes ALL the SQL commands that you execute AND ALL the messages in SQL Server from the execution of the commands. This includes the execution of the script that has been provided to you for the first part AND the SQL commands you created for the second part of the lab.
For this lab, you will submit (1) the document containing all the SQL commands and the resulting messages from the execution and (2) the data dictionary file.
(**** Script *****)
/* Script to build tables for FLIX2YOU .. current schema before revision */
/* Gary Heberling May 2, 2012 */
/* For IST210 world campus Penn State University */
/* 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 Store id store name store address Movies store phone store email other store details fk formal type code genre type code Condition Codes Condition code movie title condition description eg Rental. Used for Sale. rental or sale or both rental daily rate Format Types sale price Gformat type code format type description eg DVD, Video, Genre Codes Movie Cast Ogenre code genre description Factor id eg Comedy, Western Actors actor id actor gender actor last name other actor details Flix2You ERD (Initial Design: July 2002) Customer Rentals customer id Orental status code rental date out rental amount due other renta details Financial Transactions Qtransaction id item tentaLid Le previous transaction id transaction type code transaction date transaction comment Rental Status Codes Transaction Types transaction type code rental status code rental status description transaction type description eg Payment, Refund. eg Overdue. Customers Ocustomeu id date became member customer first name customer last name customer dob Accounts customer id payment method code account details Payment Methods Payment method code payment method description eg Cash, CC (Credit Card)Explanation / Answer
Hi,
Execute the code given for the first 7 tables in the same order. Drop the tables (if they exist) and create the 7 tables as mentioned. Execute alter table commands for setting up the primary key and secondary keys.
The code below creates the remaining 7 tables as mentioned in the ER diagram.
/* Create Tables */
/* Create Customer_Retails Table */
CREATE TABLE Customer_Retails (
item_rental_id int IDENTITY(1,1) NOT NULL,
customer_id int NOT NULL,
movie_id int NOT NULL,
rental_status_code int NOT NULL,
rental_date_out Date NOT NULL,
rental_date_returned date NOT NULL,
rental_amount_due money NOT NULL,
other_rental_details varchar(512) NOT NULL);
/* Create Financial_Transactions Table */
CREATE TABLE Financial_Transactions (
transaction_id int IDENTITY(1,1) NOT NULL,
account_id int NOT NULL,
item_rental_id int NOT NULL,
previous_transaction_id int NOT NULL,
transaction_type_code int NOT NULL,
transaction_date date NOT NULL,
transaction_amount money NOT NULL,
transaction_comment varchar(512) NOT NULL);
/* Create Customer Table */
CREATE TABLE Customers (
customer_id int IDENTITY(1,1) NOT NULL,
member_vn int NOT NULL,
membership_number int NOT NULL,
date_became_member date NOT NULL,
customer_first_name varchar(32) NOT NULL,
customer_last_name varchar(32) NOT NULL,
customer_address varchar(128) NOT NULL,
customer_phone int NOT NULL,
customer_email varchar(128) NOT NULL,
customer_dob date NOT NULL);
/* Create Accounts Table */
CREATE TABLE Accounts (
account_id int IDENTITY(1,1) NOT NULL,
customer_id int NOT NULL,
payment_method_code int NOT NULL,
account_name varchar(32) NOT NULL,
account_details varchar(128) NOT NULL);
/* Create Rental_Status_Codes Table */
CREATE TABLE Rental_Status_Codes (
rental_status_code int IDENTITY(1,1) NOT NULL,
rental_status_description varchar(32) NOT NULL);
/* Create Transaction_Types Table */
CREATE TABLE Transaction_Types (
transaction_type_code int IDENTITY(1,1) NOT NULL,
transaction_type_description varchar(32) NOT NULL);
/* CREATE Payment_Methods Table */
CREATE TABLE Payment_Methods (
payment_method_code int IDENTITY (1,1) NOT NULL,
payment_method_description varchar(32) NOT NULL);
/* create primary keys with ALTER TABLE statement */
ALTER TABLE Customer_Retails ADD CONSTRAINT pk_item_rental_id PRIMARY KEY (item_rental_id);
ALTER TABLE Financial_Transactions ADD CONSTRAINT pk_transaction_id PRIMARY KEY (transaction_id);
ALTER TABLE Customers ADD CONSTRAINT pk_customer_id PRIMARY KEY (customer_id);
ALTER TABLE Accounts ADD CONSTRAINT pk_account_id PRIMARY KEY (account_id);
ALTER TABLE Rental_Status_Codes ADD CONSTRAINT pk_rental_status_code PRIMARY KEY (rental_status_code);
ALTER TABLE Transaction_Types ADD CONSTRAINT pk_transaction_type_code PRIMARY KEY (transaction_type_code);
ALTER TABLE Payment_Methods ADD CONSTRAINT pk_payment_method_code PRIMARY KEY (payment_method_code);
/* create foreign keys */
ALTER TABLE Customer_Rentals ADD CONSTRAINT fk_customer_id_customers FOREIGN KEY(customer_id) REFERENCES Customer (customer_id);
ALTER TABLE Customer_Rentals ADD CONSTRAINT fk_movie_id_movie_cast FOREIGN KEY(movie_id) REFERENCES Movie_Cast (movie_id);
ALTER TABLE Customer_Rentals ADD CONSTRAINT fk_rental_status_code_rental_status_codes FOREIGN KEY(rental_status_code) REFERENCES Rental_Status_Codes (rental_status_code);
ALTER TABLE Financial_Transactions ADD CONSTRAINT fk_account_id_Accounts FOREIGN KEY(account_id) REFERENCES Accounts (account_id);
ALTER TABLE Financial_Transactions ADD CONSTRAINT fk_item_rental_id_customer_rentals FOREIGN KEY(item_rental_id) REFERENCES Customer_Rentals (item_rental_id);
ALTER TABLE Financial_Transactions ADD CONSTRAINT fk_previous_transaction_id_financial_transactions FOREIGN KEY(previous_transaction_id) REFERENCES Financial_Transactions (previous_transaction_id);
ALTER TABLE Financial_Transactions ADD CONSTRAINT fk_transaction_type_code_transaction_types FOREIGN KEY(transaction_type_code) REFERENCES Transaction_Types (transaction_type_code);
ALTER TABLE Accounts ADD CONSTRAINT fk_customer_id_customers FOREIGN KEY(customer_id) REFERENCES Customers (customer_id);
ALTER TABLE Financial_Transactions ADD CONSTRAINT fk_payment_method_code_payment_methods FOREIGN KEY(payment_method_code) REFERENCES Payment_Methods (payment_method_code);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.