Also, Oracle Now Uses VARCHAR2, You Should Never Use Old VARCHAR. and There Were Some Places

Throughout the database, I changed all of your NUMBER elements where you define a double (a number with decimal points). It appears that you were doing it incorrectly. For instance, in one place, you had NUMBER(1,2). That is not valid. The first number is the total amount of digits. The second number is the number of those digits that are right of the decimal point. So that is saying that there are 1 total digits, 2 of which are to the right of the decimal point. Obviously, that doesn’t make sense. If what you wanted was 1 digit to the left and two digits to the right, then it should be 3,2. Three total digits, two of which are to the right of the decimal point.

Also, Oracle now uses VARCHAR2, you should never use old VARCHAR. And there were some places where you had char when you should have had VARCHAR2. For instance, genre. A CHAR field should be used when you know exactly how long the field will always be. For instance, we know that state will always be two characters or gender will always be one, or media type will always be three. However, Genre will not always be 10. When you use CHAR(10) and you have the Genre “Drama”, the field length will be 10. It will hold five characters and empty spaces for the other 5 characters. It still uses up that memory in the database. This is a horrible thing to do when working with a real database, as databases have a limit on the size of tables. With VARCHAR2(10), the value “Drama” will take up 5 characters. Since the values of the Genre column are of variable length, the datatype should also be of variable length.

/*CREATE DIRECTOR TABLE*/

CREATE TABLE DIRECTOR (

DIRECTOR_ID NUMBER(5) PRIMARY KEY,

DIRECTOR_LAST_NAME VARCHAR2(20) NOT NULL,

DIRECTOR_FIRST_NAME VARCHAR2(15) NOT NULL

);

/*CREATE ACTOR TABLE*/

CREATE TABLE ACTOR (

ACTOR_ID NUMBER(5) PRIMARY KEY,

ACTOR_LAST_NAME VARCHAR2(20) NOT NULL,

ACTOR_FIRST_NAME VARCHAR2(20) NOT NULL,

ACTOR_GENDER CHAR(1) DEFAULT 'M'

);

/*CREATE DISTRIBUTOR TABLE*/

CREATE TABLE DISTRIBUTOR (

DISTRIBUTOR_ID NUMBER(5) PRIMARY KEY,

COMPANY_NAME VARCHAR2(20) NOT NULL,

DISTRIBUTOR_CONTRACT VARCHAR2(20),

DISTRIBUTOR_PHONE NUMBER(10)

);

/*CREATE CUSTOMER_ZIP TABLE*/

CREATE TABLE CUSTOMER_ZIP (

ADDRESS_ZIP_CODE NUMBER(5) PRIMARY KEY,

ADDRESS_CITY VARCHAR2(15) NOT NULL,

ADDRESS_STATE VARCHAR2(2) NOT NULL

);

/*CREATE CUSTOMER_RECORD TABLE*/

CREATE TABLE CUSTOMER_RECORD (

CUSTOMER_ID NUMBER(5) PRIMARY KEY,

FIRST_NAME VARCHAR2(15) NOT NULL,

LAST_NAME VARCHAR2(15) NOT NULL,

ADDRESS_STREET VARCHAR2(30),

ADDRESS_ZIP_CODE NUMBER(5),

CUSTOMER_PHONE NUMBER(10),

CONSTRAINT FK_CUSTOMER_ZIP

FOREIGN KEY (ADDRESS_ZIP_CODE) REFERENCES CUSTOMER_ZIP(ADDRESS_ZIP_CODE)

);

/*CREATE LATE FEE TABLE*/

CREATE TABLE LATE_FEE (

DAYS_LATE NUMBER(3) PRIMARY KEY,

LATE_FEE NUMBER(5,2) NOT NULL

);

/*CREATE MOVIE INFORMATION TABLE*/

CREATE TABLE MOVIE_INFORMATION (

MOVIE_TITLE VARCHAR2(255) PRIMARY KEY,

DIRECTOR_ID NUMBER(5) NOT NULL,

RUNNING_LENGTH NUMBER(3) NOT NULL,

DESCRIPTION VARCHAR2(240),

GENRE VARCHAR2(10) NOT NULL,

RELEASED_DATE DATE NOT NULL,

CONSTRAINT FK_MOVIE_DIRECTOR

FOREIGN KEY (DIRECTOR_ID) REFERENCES DIRECTOR(DIRECTOR_ID)

);

/*CREATE MOVIES TABLE*/

CREATE TABLE MOVIES (

MOVIE_ID NUMBER(6) PRIMARY KEY,

MOVIE_TITLE VARCHAR2(255) NOT NULL,

DISTRIBUTION_SERIAL NUMBER(10),

MEDIA_TYPE CHAR(3) NOT NULL,

CONSTRAINT FK_MOVIE_INFO

FOREIGN KEY (MOVIE_TITLE) REFERENCES MOVIE_INFORMATION(MOVIE_TITLE)

);

This table changed because it is a weak entity. A weak entity exist to facilitate a many-to-many relationship between two strong entities (actor, and movies_information). Weak entities should not have a new primary key defined, as doing so is a violation of basic normal form rules. There is already a full candidate key in place. The combination of the movie_title and the actor_id is a full candidate key. Therefore, adding a different primary key is a violation of normal form. The Primary Key is a composite key consisting of both the movie_title and the actor_id. All weak entities are done this way and you will see it done this way in all professionally built databases.

/*CREATE MOVIE ACTORS TABLE*/

CREATE TABLE MOVIE_ACTORS (

MOVIE_TITLE VARCHAR2(255) NOT NULL,

ACTOR_ID NUMBER(5) NOT NULL,

MOVIE_CHARACTER VARCHAR2(30) NOT NULL,

CONSTRAINT PK_MOVIE_ACTORS PRIMARY KEY (MOVIE_TITLE, ACTOR_ID),

CONSTRAINT FK_MA_TITLE

FOREIGN KEY (MOVIE_TITLE) REFERENCES MOVIE_INFORMATION(MOVIE_TITLE),

CONSTRAINT FK_MA_ACTOR

FOREIGN KEY (ACTOR_ID) REFERENCES ACTOR(ACTOR_ID)

);

/*CREATE AWARDS TABLE*/

CREATE TABLE AWARDS (

ACADEMY_AWARD_ID NUMBER(5) PRIMARY KEY,

MOVIE_TITLE VARCHAR2(255) NOT NULL,

AWARD_FOR VARCHAR2(15) NOT NULL,

AWARD_YEAR DATE NOT NULL,

CONSTRAINT FK_AWARD_TITLE

FOREIGN KEY (MOVIE_TITLE) REFERENCES MOVIE_INFORMATION(MOVIE_TITLE)

);

/*CREATE DISTRIBUTION LIST TABLE*/

CREATE TABLE DISTRIBUTION_LIST (

DISTRIBUTION_ID NUMBER(5) PRIMARY KEY,

MOVIE_ID NUMBER(6) NOT NULL,

DISTRIBUTOR_ID NUMBER(5) NOT NULL,

CATALOG_DESCRIPTION VARCHAR2(240) NOT NULL,

UNIT_PRICE NUMBER(5,2) NOT NULL,

CONSTRAINT FK_DL_MOVIE

FOREIGN KEY (MOVIE_ID) REFERENCES MOVIES(MOVIE_ID),

CONSTRAINT FK_DL_DIST

FOREIGN KEY (DISTRIBUTOR_ID) REFERENCES DISTRIBUTOR(DISTRIBUTOR_ID)

);

This table changed to remove the distributor id. This table already references the distribution id, which has the distributor id in it. Therefore, putting the distributor id into this table is redundant. Technically it is a violation of normal form in that it is a transitive dependency. This is a major fault and should not be included in the table.

/*CREATE ORDERS TABLE*/

CREATE TABLE ORDERS (

ORDER_ID NUMBER(5) PRIMARY KEY,

DISTRIBUTION_ID NUMBER(5) NOT NULL,

NUMBER_OF_ITEMS NUMBER(2) NOT NULL,

PRICE_TOTAL NUMBER(6,2) NOT NULL,

CONSTRAINT FK_ORDER_DIST

FOREIGN KEY (DISTRIBUTION_ID) REFERENCES DISTRIBUTION_LIST(DISTRIBUTION_ID)

);

/*CREATE MOVIE LIST TABLE*/

CREATE TABLE MOVIE_LIST (

TITLE_ID NUMBER(10) PRIMARY KEY,

MOVIE_ID NUMBER(6) NOT NULL,

RENTAL_PRICE NUMBER(5,2) NOT NULL,

DISCOUNT_PRICE NUMBER(5,2) NOT NULL,

CONSTRAINT FK_ML_MOVIE

FOREIGN KEY (MOVIE_ID) REFERENCES MOVIES(MOVIE_ID)

);

/*CREATE RENTAL RECORD TABLE*/

CREATE TABLE RENTAL_RECORD (

RENTAL_RECORD_ID NUMBER(10) PRIMARY KEY,

TITLE_ID NUMBER(10) NOT NULL,

CUSTOMER_ID NUMBER(5) NOT NULL,

RENTED_DATE DATE NOT NULL,

RETURNED_DATE DATE,

DAYS_LATE NUMBER(3),

DAMAGED_FEE NUMBER(3,2),

TAX NUMBER(4,2),

TOTAL_COST NUMBER(5,2),

CONSTRAINT FK_RR_TITLE

FOREIGN KEY (TITLE_ID) REFERENCES MOVIE_LIST(TITLE_ID),

CONSTRAINT FK_RR_CUSTOMER

FOREIGN KEY (CUSTOMER_ID) REFERENCES CUSTOMER_RECORD(CUSTOMER_ID),

CONSTRAINT FK_RR_DAYS

FOREIGN KEY (DAYS_LATE) REFERENCES LATE_FEE(DAYS_LATE)

);

/*INSERTS*/

INSERT INTO CUSTOMER_ZIP VALUES (15220, 'Pittsburgh', 'PA');

INSERT INTO CUSTOMER_ZIP VALUES (90210, 'Beverly Hills', 'CA');

INSERT INTO CUSTOMER_RECORD VALUES (11111, 'Mildred', 'Hubble', '123 East Street', 90210, 7245551234);

INSERT INTO CUSTOMER_RECORD VALUES (22222, 'Greg', 'Fox', '917 West Street', 15220, 4125557777);

INSERT INTO DIRECTOR VALUES (1, 'SCOTT', 'RIDLEY');

INSERT INTO MOVIE_INFORMATION VALUES ('Legend', 1, 114, 'A young man must stop the Lord of Darkness', 'Fantasy', '18-APR-1985');

INSERT INTO DISTRIBUTOR VALUES (1, 'Moves R Us', '10 Year', 4125559999);

INSERT INTO MOVIES VALUES (1, 'Legend', 1, 'DVD');

INSERT INTO DISTRIBUTION_LIST VALUES (1, 1, 1, 'Movie about Unicorns', 1.15);

INSERT INTO MOVIE_LIST VALUES (1, 1, 1.15, 1.00);

INSERT INTO RENTAL_RECORD VALUES (1, 1, 11111, '01-JAN-2013', NULL, NULL, 0, 0.07, 1.22);

/*a. Retrieve all of your customers' names, account numbers, and addresses (street and zip code only), sorted by account number.*/

SELECT CUSTOMER_ID, FIRST_NAME, LAST_NAME, ADDRESS_STREET, ADDRESS_ZIP_CODE

FROM CUSTOMER_RECORD

ORDER BY CUSTOMER_ID;

/*b. Retrieve all of the videos rented in the last 30 days and sort in chronological rental date order.*/

SELECT RR.RENTED_DATE, MI.MOVIE_TITLE

FROM MOVIE_INFORMATION MI

JOIN MOVIES M

ON MI.MOVIE_TITLE = M.MOVIE_TITLE

JOIN MOVIE_LIST ML

ON M.MOVIE_ID = ML.MOVIE_ID

JOIN RENTAL_RECORD RR

ON ML.TITLE_ID = RR.TITLE_ID

ORDER BY RR.RENTED_DATE ASC;

/*c. Produce a list of your distributors and all their information sorted in order by company name.*/

SELECT DISTRIBUTOR_ID, COMPANY_NAME, DISTRIBUTOR_CONTRACT, DISTRIBUTOR_PHONE

FROM DISTRIBUTOR

ORDER BY COMPANY_NAME ASC;

/*d. Update customer names to change their maiden names to married names. You can choose which records to update.*/

UPDATE CUSTOMER_RECORD SET LAST_NAME = 'Potter' WHERE LAST_NAME = 'Hubble';

/*e. Delete customers from the database. You can choose which records to delete.*/

DELETE FROM CUSTOMER_RECORD WHERE CUSTOMER_ID = 22222;