In this project you will perform the physical design and implementation using SQ

Question

In this project you will perform the physical design and implementation using SQL Data Definition Language (DDL) and     proceed with populating the Mom and Pop Johnson Video Store database via Data Manipulation Language (DML) SQL commands.

Each of the steps below requires a SPOOL file to be submitted. Be sure your SPOOL file contains your SQL statements along with the Oracle responses and/or displayed results. Do NOT submit your SQL script files. Only submit your output SPOOL files. If you are using iSQL*Plus you must screen snapshots as necessary of your SQL and the results.

Project 2 Details:

Submit your SPOOL file(s) showing that all SQL in your SQL script file worked properly. Show the actual SQL statements executed and the results the SQL produced below the code. Do NOT submit your SQL script files. Also, submit all of your .sql files.

Explanation / Answer

Program:

CREATE TABLE MOVIES ( MOVIES_ID NUMBER(7, 0) NOT NULL , DVD_ID NUMBER(7, 0) , VIDEO_ID NUMBER(7, 0) , CONSTRAINT MOVIES_PK PRIMARY KEY ( MOVIES_ID ) ENABLE );

CREATE TABLE DVD ( DVD_ID NUMBER(7, 0) NOT NULL , CUSTOMER_ID NUMBER(7, 0) , FEES_ID NUMBER(7, 0) , PRICE_ID NUMBER(7, 0) , RENTAL_ID NUMBER(7, 0) , CONSTRAINT DVD_PK PRIMARY KEY ( DVD_ID ) ENABLE );

CREATE TABLE VIDEO ( VIDEO_ID NUMBER(7, 0) NOT NULL , CUSTOMER_ID NUMBER(7, 0) , FEES_ID NUMBER(7, 0) , PRICE_ID NUMBER(7, 0) , RENTAL_ID NUMBER(7, 0) , CONSTRAINT VIDEO_PK PRIMARY KEY ( VIDEO_ID ) ENABLE );

CREATE TABLE MOVIE_COPIES ( COPIES_ID NUMBER(7, 0) NOT NULL , RENTAL_ID NUMBER(7, 0) , AVAILABLE_RENT NUMBER(7, 0) , CONSTRAINT MOVIE_COPIES_PK PRIMARY KEY ( COPIES_ID ) ENABLE );

CREATE TABLE STORE ( STORE_ID NUMBER(7, 0) NOT NULL , SUPPLIER_ID NUMBER(7, 0) , CATALOG_ID NUMBER(7, 0) , CONSTRAINT STORE_PK PRIMARY KEY ( STORE_ID ) ENABLE );

CREATE TABLE CUSTOMER ( CUSTOMER_ID NUMBER(7, 0) NOT NULL , RENTAL_ID NUMBER(7, 0) , FIRST_NAME VARCHAR2(20) , LAST_NAME VARCHAR2(20) , CONTACT_INFO VARCHAR2(200) , CONSTRAINT CUSTOMER_PK PRIMARY KEY ( CUSTOMER_ID ) ENABLE );

CREATE TABLE MOVIE_SUPPLIER ( SUPPLIER_ID NUMBER(7, 0) NOT NULL , MOVIES_ID NUMBER(7, 0) , TYPE_DISTRIBUTED VARCHAR2(20) , ELECTRONIC_CATALOG VARCHAR2(20) , CONSTRAINT MOVIE_SUPPLIER_PK PRIMARY KEY ( SUPPLIER_ID ) ENABLE );

CREATE TABLE MOVIE_RENTAL ( RENTAL_ID NUMBER(7, 0) NOT NULL , DVD_ID NUMBER(7, 0) , VIDEO_ID NUMBER(7, 0) , COPIES_ID NUMBER(7, 0) , AVAILABLE_RENT NUMBER(7, 0) , DESCRIPTION VARCHAR2(50) , TOTAL_ORDERS NUMBER(7, 0) , TOTAL_PRICE NUMBER(7, 0) , "DATE_RENTED" DATE , CONSTRAINT MOVIE_RENTAL_PK PRIMARY KEY ( RENTAL_ID ) ENABLE );

CREATE TABLE ELECTRONIC_CATALOG ( CATALOG_ID NUMBER(7, 0) NOT NULL , MOVIES_ID NUMBER(7, 0) , MOVIE_LIST VARCHAR2(20) , TYPE_DISTRIBUTED VARCHAR2(20) , CATALOG_QUANTITY NUMBER(7, 0) , CONSTRAINT ELECTRONIC_CATALOG_PK PRIMARY KEY ( CATALOG_ID ) ENABLE );

CREATE TABLE FEES ( FEES_ID NUMBER(7, 0) NOT NULL , CUSTOMER_ID NUMBER(7, 0) , LATE_FEE NUMBER(7, 0) , REWOUND_FEE NUMBER(7, 0) , TOTAL_PRICE NUMBER(7, 0) , CONSTRAINT FEES_PK PRIMARY KEY ( FEES_ID ) ENABLE );

CREATE TABLE TRANSACTION_PRICE ( PRICE_ID NUMBER(7, 0) NOT NULL , CUSTOMER_ID NUMBER(7, 0) , TOTAL_PRICE NUMBER(7, 0) , CONSTRAINT TRANSACTION_PRICE_PK PRIMARY KEY ( PRICE_ID ) ENABLE );

alter table "YOURNAME"."MOVIES" add constraint fk_dvd foreign key("DVD_ID") references "DVD"("DVD_ID")

alter table "YOURNAME"."MOVIES" add constraint fk_video foreign key("VIDEO_ID") references "VIDEO"("VIDEO_ID")

alter table "YOURNAME"."MOVIES" add constraint uk_movies_dvdid unique("DVD_ID")

alter table "YOURNAME"."DVD" add constraint fk_customer foreign key("CUSTOMER_ID") references "CUSTOMER"("CUSTOMER_ID")

alter table "YOURNAME"."DVD" add constraint fk_fees foreign key("FEES_ID") references "FEES"("FEES_ID")

alter table "YOURNAME"."DVD" add constraint fk_price foreign key("PRICE_ID") references "TRANSACTION_PRICE"("PRICE_ID")

alter table "YOURNAME"."DVD" add constraint fk_rental foreign key("RENTAL_ID") references "MOVIE_RENTAL"("RENTAL_ID")

alter table "YOURNAME"."MOVIE_COPIES" add constraint fk_movies_rental foreign key("RENTAL_ID") references "MOVIE_RENTAL"("RENTAL_ID")

alter table "YOURNAME"."STORE" add constraint fk_supplierid foreign key("SUPPLIER_ID") references "MOVIE_SUPPLIER"("SUPPLIER_ID") alter table "YOURNAME"."STORE" add constraint fk_catalogid foreign key("CATALOG_ID") references "ELECTRONIC_CATALOG"("CATALOG_ID")

alter table "YOURNAME"."CUSTOMER" add constraint fk_customer_rentalid foreign key("RENTAL_ID") references "MOVIE_RENTAL"("RENTAL_ID")

alter table "YOURNAME"."MOVIE_SUPPLIER" add constraint fk_ms_moviesid foreign key("MOVIES_ID") references "MOVIES"("MOVIES_ID")

alter table "YOURNAME"."MOVIE_RENTAL" add constraint fk_dvdid foreign key("DVD_ID") references "DVD"("DVD_ID")

alter table "YOURNAME"."MOVIE_RENTAL" add constraint fk_videoid foreign key("VIDEO_ID") references "VIDEO"("VIDEO_ID")

alter table "YOURNAME"."MOVIE_RENTAL" add constraint fk_copiesid foreign key("COPIES_ID") references "MOVIE_COPIES"("COPIES_ID")

alter table "YOURNAME"."ELECTRONIC_CATALOG" add constraint fk_movieid foreign key("MOVIES_ID") references "MOVIES"("MOVIES_ID")

alter table "YOURNAME"."FEES" add constraint fk_customerid foreign key("CUSTOMER_ID") references "CUSTOMER"("CUSTOMER_ID")

alter table "YOURNAME"."TRANSACTION_PRICE" add constraint fk_tp_customerid foreign key("CUSTOMER_ID") references "CUSTOMER"("CUSTOMER_ID")

begin execute immediate 'create or replace trigger movies_autoincrement_trigger '||chr(10)|| ' before insert on "YOURNAME"."MOVIES" '||chr(10)|| ' for each row '||chr(10)|| 'begin '||chr(10)|| ' if inserting then '||chr(10)|| ' if :NEW."MOVIES_ID" is null then '||chr(10)|| ' select MOVIES_ID.nextval into :NEW."MOVIES_ID" from dual; '||chr(10)|| ' end if; '||chr(10)|| ' end if; '||chr(10)|| 'end;'||chr(10); end;

Related Questions

Navigate

• Browse (All)
• Browse I
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.