University of MarylandCMIS 320 Assignment 3| Complete Solutions

SQL>
SQL> --Create customers table
SQL> CREATE TABLE customers(
2 customer_id VARCHAR2(10) PRIMARY KEY,
3 lastname VARCHAR2(20) NOT NULL,
4 firstname VARCHAR2(20) NOT NULL,
5 street VARCHAR2(20) NOT NULL,
6 city VARCHAR2(20) NOT NULL,
7 state CHAR(2) NOT NULL,
8 zip_code CHAR(5),
9 phone VARCHAR2(12) UNIQUE
10 );

Table CUSTOMERS created.

SQL>
SQL> --Create rentals tableSQL>
CREATE TABLE rentals(
2 moive_id VARCHAR2(10) PRIMARY KEY,
3 customer_id VARCHAR2(10)
4 CONSTRAINT fk_customer_id_rentals
5 REFERENCES customers,
6 rental_date DATE NOT NULL,
7 due_date DATE NOT NULL,
8 standard_fee VARCHAR2(5) NOT NULL,
9 fees VARCHAR(7)
10 );

Table RENTALS created.SQL>
SQL> --Create movies tableSQL>
CREATE TABLE movies(
2 movie_id VARCHAR2(10) PRIMARY KEY,
3 title VARCHAR2(30) NOT NULL,
4 rating VARCHAR2(5) NOT NULL,
5 year VARCHAR2(5) NOT NULL,
6 genre VARCHAR2(15) NOT
NULL7 );

Table MOVIES created.SQL>
SQL> --Create media forms tableSQL>
CREATE TABLE media_forms(
2 dvdvideo_id VARCHAR2(10) PRIMARY KEY,
3 title VARCHAR2(30),
4 distrib_num VARCHAR2(10),
5 movie_id VARCHAR2(10)
6 CONSTRAINT fk_movie_id_media_forms
7 REFERENCES movies
8 );

Table MEDIA_FORMS created.SQL>
SQL>
SQL> --Create lead roles tableSQL> CREATE
TABLE lead_roles(
2 movie_id VARCHAR2(10) PRIMARY KEY,
3 title VARCHAR2(30),
4 lead_roles VARCHAR2(40),
5 awards VARCHAR2(20)
6 );

, Table LEAD_ROLES created.

SQL>
SQL> --Create electronic catalogs tableSQL> CREATE
TABLE e_catalogs(
2 catalog_num VARCHAR2(10) PRIMARY KEY,
3 dvdvideo_id VARCHAR2(10) NOT NULL,
4 genre VARCHAR2(15) NOT NULL,
5 price VARCHAR2(5) NOT NULL,
6 catalog_year VARCHAR2(5) NOT NULL,
7 title VARCHAR2(30) NOT NULL
8 );

Table E_CATALOGS created.SQL>
SQL> --Create distributors tableSQL>
CREATE TABLE distributors(
2 distrib_num VARCHAR2(10) PRIMARY KEY,
3 catalog_num VARCHAR2(10) NOT NULL,
4 distrib_name VARCHAR2(30) NOT NULL,
5 distrib_phone VARCHAR2(12) UNIQUE,
6 whole_sale_rate VARCHAR2(5) NOT NULL,
7 genres VARCHAR2(15) NOT NULL
8 );

Table DISTRIBUTORS created.SQL>
SQL> SPOOL OFF




SQL> SET ECHO ON
SQL>
SQL> --Customers table rowsSQL>
INSERT INTO customers
2 VALUES ('100', 'Washington', 'Mike', '11 A St', 'Phila', 'PA',3 '19120', '111-
111-1111');

1 row inserted.

SQL>
SQL> INSERT INTO customers
2 VALUES ('200', 'Smith', 'Igor', '22 B St', 'Phila', 'PA',3 '19121', '222-
222-2222');

1 row inserted.

SQL>
SQL> INSERT INTO customers
2 VALUES ('300', 'Hanson', 'Destiny', '33 C St', 'Phila', 'PA',3 '19122', '333-
333-3333');

1 row inserted.

SQL>
SQL> INSERT INTO customers