Assignment 2

--IMMANUEL PONMINISSERY
--UTEID: ijp284

/*THE FIRST STEP IS TO DROP TABLES,SEQUENCES AND INDEX. IT MIGHT RETURN AN ERROR BUT IF YOU RUN IT AGAIN, YOU WILL NOT SEE ANY ERRORS.
THE ERROR APPEARS AT FIRST BECAUSE YOU MIGHT NOT HAVE SIMILARLY NAMED TABLES AND SEQUENCES TO BEGIN WITH*/

/*PLEASE NOTE THAT IN ORDER TO GET THE DATES AND TIMES FORMATTED CORRECTLY, GO TO TOOLS>PREFERENCES>DATA>NLS AND THEN YYYY-MM-DD HH24:MI:SS
ON FORMAT*/


--DROP INDEX/SEQUENCES/TABLES
DROP INDEX VIDEO_TOPIC_NAME_IX;
DROP INDEX CC_CARD_NUMBER_IX;
DROP INDEX CARD_CARD_TYPE_IX;
DROP INDEX CC_STATE_OF_RESIDENCE_IX;

DROP TABLE LINKED_CC;
DROP TABLE COMMENT_VIDEO;
DROP TABLE VIDEO;
DROP TABLE CC;
DROP TABLE CARD;
DROP TABLE TOPIC;
DROP TABLE USER_BROWSER;

DROP SEQUENCE USER_ID_SEQ;
DROP SEQUENCE CC_ID_SEQ;
DROP SEQUENCE CARD_ID_SEQ;
DROP SEQUENCE TOPIC_ID_SEQ;
DROP SEQUENCE VIDEO_ID_SEQ;
DROP SEQUENCE COMMENT_ID_SEQ;



/*THE NEXT STEP IS TO CREATE TABLES AND SEQUENCES. THEY HAVE BEEN MADE ACCORDING TO THE ERD. THE FIRST PART ONLY COVERS CREATING
TABLES AND THE SECOND PART FOCUSES ON SEQUENCES. THE SEQUENCES HAVE BEEN MADE ACCORDING TO THE ASSIGNMENT DOCUMENTATION. IN TERMS
OF THE TOTAL NUMBER OF TABLES, THERE ARE 7. THEY ARE FOR THE USERS, CONTENT CREATORS, THEIR CARDS, THE TOPCS, THE VIDEOS, THE COMMENTS
AND A TABLE THAT LINKS NON AUTHOR CCS WITH VIDEOS*/

--CREATE SEQUENCE/TABLES
CREATE TABLE USER_BROWSER
(
    USER_ID NUMBER NOT NULL,
    FIRST_NAME VARCHAR(20) NOT NULL,
    MIDDLE_NAME VARCHAR(20), 
    LAST_NAME VARCHAR(30) NOT NULL,
    DOB DATE NOT NULL,
    EMAIL_ADDRESS VARCHAR(20) UNIQUE,
    CC_FLAG CHAR(1) DEFAULT 'N',
    CONSTRAINT USER_ID_PK PRIMARY KEY (USER_ID),
    CONSTRAINT DOB_CK CHECK (DOB<=TO_DATE('09-19-2007','MM-DD-YYYY')),
    CONSTRAINT EMAIL_ADDRESS_CK CHECK (LENGTH(EMAIL_ADDRESS)>=7)
    
);

CREATE TABLE TOPIC 
(
    TOPIC_ID NUMBER NOT NULL,
    TOPIC_NAME VARCHAR(30) UNIQUE NOT NULL,
    TOPIC_DESCRIPTION VARCHAR(100)  NOT NULL,
    USER_ID NUMBER NOT NULL,
    CONSTRAINT TOPIC_ID_PK PRIMARY KEY (TOPIC_ID),
    CONSTRAINT TOPIC_FK_USER_BROSWER FOREIGN KEY(USER_ID) REFERENCES USER_BROWSER (USER_ID)
);

CREATE TABLE CARD
(
    CARD_ID NUMBER NOT NULL,
    CARD_NUMBER NUMBER NOT NULL UNIQUE,
    USER_ID NUMBER NOT NULL,
    CARD_TYPE VARCHAR(20) NOT NULL,
    EXPIRATION_DATE DATE NOT NULL,
    SECURITY_CODE NUMBER NOT NULL,
    BILLING_ADDRESS_STREET VARCHAR(100) NOT NULL,
    BILLING_ADDRESS_CITY VARCHAR(100) NOT NULL,
    BILLING_ADDRESS_STATE CHAR(2),
    BILLING_ADDRESS_ZIP CHAR(5),
    CONSTRAINT CARD_ID_PK PRIMARY KEY (CARD_ID),
    CONSTRAINT CARD_FK_USER_BROWSER FOREIGN KEY(USER_ID)REFERENCES USER_BROWSER (USER_ID)
);

CREATE TABLE CC
(
    CC_ID NUMBER NOT NULL,
    USER_ID NUMBER NOT NULL,
    FIRST_NAME VARCHAR(20) NOT NULL,
    LAST_NAME VARCHAR(30) NOT NULL,
    CARD_NUMBER NUMBER NOT NULL,
    USERNAME VARCHAR(20) NOT NULL,
    EMAIL_ADDRESS VARCHAR(20) NOT NULL UNIQUE,
    BILLING_ADDRESS VARCHAR(100) NOT NULL,
    TIER_CC VARCHAR(20) DEFAULT 'FREE',
    COUNTRY_OF_RESIDENCE VARCHAR(20) NOT NULL,
    STATE_OF_RESIDENCE CHAR(2) NOT NULL,
    MOBILE_PHONE_NUMBER CHAR(12) NOT NULL,
    CONSTRAINT CC_ID_PK PRIMARY KEY (CC_ID),
    CONSTRAINT CC_FK_USER_BROWSER FOREIGN KEY(USER_ID) REFERENCES USER_BROWSER (USER_ID),
    CONSTRAINT CC_FK_CARD FOREIGN KEY(CARD_NUMBER)REFERENCES CARD (CARD_NUMBER)
);

CREATE TABLE VIDEO
(
    VIDEO_ID NUMBER NOT NULL,
    SUBTITLE VARCHAR(20) NOT NULL,
    TITLE VARCHAR(200) NOT NULL,
    TOPIC_ID NUMBER NOT NULL,
    TOPIC_NAME VARCHAR(30)  NOT NULL,
    DATE_UPLOADED DATE NOT NULL,
    LENGTH_OF_VIDEO NUMBER NOT NULL,
    SIZE_VIDEO NUMBER NOT NULL,
    VIEWS_VIDEO NUMBER DEFAULT 0,
    LIKES NUMBER DEFAULT 0,
    REVENUE_EARNED NUMBER DEFAULT 0,
    CC_ID NUMBER NOT NULL,
    CONSTRAINT VIDEO_ID_PK PRIMARY KEY (VIDEO_ID),
    CONSTRAINT VIDEO_FK_TOPIC FOREIGN KEY(TOPIC_ID) REFERENCES TOPIC (TOPIC_ID),
    CONSTRAINT VIDEO_FK_TOPIC_NAME FOREIGN KEY(TOPIC_NAME) REFERENCES TOPIC (TOPIC_NAME),
    CONSTRAINT VIDEO_FK_CC FOREIGN KEY(CC_ID)REFERENCES CC (CC_ID)
);

CREATE TABLE COMMENT_VIDEO
(
    COMMENT_ID NUMBER NOT NULL,
    USER_ID NUMBER NOT NULL,
    COMMENT_CONTENT VARCHAR(200) NOT NULL,
    COMMENT_TIMESTAMP DATE NOT NULL,
    VIDEO_ID NUMBER NOT NULL,
    CONSTRAINT COMMENT_ID_PK PRIMARY KEY (COMMENT_ID),
    CONSTRAINT COMMENT_VIDEO_FK_VIDEO FOREIGN KEY(VIDEO_ID) REFERENCES VIDEO (VIDEO_ID),
    CONSTRAINT COMMENT_VIDEO_FK_USER_BROWSER FOREIGN KEY(USER_ID) REFERENCES USER_BROWSER (USER_ID)
);

/*THE TABLE BELOW IS TO LINK NON AUTHOR CCs TO VIDEOS CREATED BY AN AUTHOR CC*/
CREATE TABLE LINKED_CC
(
    CC_ID NUMBER NOT NULL,
    VIDEO_ID NUMBER NOT NULL,
    CONSTRAINT LINKED_CC_PK PRIMARY KEY(CC_ID,VIDEO_ID),
    CONSTRAINT LINKED_CC_FK_CC FOREIGN KEY(CC_ID) REFERENCES CC (CC_ID),
    CONSTRAINT LINKED_CC_FK_VIDEO FOREIGN KEY(VIDEO_ID) REFERENCES VIDEO (VIDEO_ID)
);

--SEQUENCES
/*THESE SEQUENCES CREATE THE AUTO GENERATED IDS FOR USER, CC, CARD, TOPIC, VIDEO AND COMMENT. THEY ARE ALL INCREMENTED BY 1. THEY
START AT SENSIBLE NUMBERS FOR A SMALL VIDEO PLATFORM. HENCE THE SEQUENCE FOR VIDEO, TOPIC AND COMMENTS BEGIN WITH SMALL NUMBERS.*/

CREATE SEQUENCE USER_ID_SEQ
    START WITH 1000000 INCREMENT BY 1;

CREATE SEQUENCE CC_ID_SEQ
    START WITH 1000000 INCREMENT BY 1;
    
CREATE SEQUENCE CARD_ID_SEQ
    START WITH 1000000 INCREMENT BY 1;

CREATE SEQUENCE TOPIC_ID_SEQ
    START WITH 1000 INCREMENT BY 1;

CREATE SEQUENCE VIDEO_ID_SEQ
    START WITH 1000 INCREMENT BY 1;
    
CREATE SEQUENCE COMMENT_ID_SEQ
    START WITH 1000 INCREMENT BY 1;    
    
--INSERT DATA
/*DATA INSERTED AS PER REQUIREMENTS. BONUS QUESTION FOR CREATING TOPIC TABLE ALSO COMPLETED. AN EXAMPLE OF THE LINKING TABLES
IS ALSO SHOWN*/
--Create 6 users where 4 have cc accounts
INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'BEVO',NULL,'MCCOMBS',TO_DATE('09-19-2007','MM-DD-YYYY'),'bevo@utexas.edu','Y');

INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'AGGIE','JOHN','MAYS',TO_DATE('05-05-1995','MM-DD-YYYY'),'AGGIE@tamu.edu','Y');

INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'REV',NULL,'TAMU',TO_DATE('06-05-2006','MM-DD-YYYY'),'AGGIEMASCOT@tamu.edu','Y');

INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'REAL','TEXAN','AGGIE',TO_DATE('06-05-2006','MM-DD-YYYY'),'agster@tamu.edu','Y');

INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'JIM',NULL,'HALPERT',TO_DATE('12-08-1975','MM-DD-YYYY'),'JH@DUNDER.COM',DEFAULT);

INSERT INTO USER_BROWSER
VALUES(USER_ID_SEQ.NEXTVAL, 'MICHAEL','GARY','SCOTT',TO_DATE('11-11-1965','MM-DD-YYYY'),'SCARN@DUNDER.COM',DEFAULT);

COMMIT;

--CREATE Credit cards
INSERT INTO CARD
VALUES(CARD_ID_SEQ.NEXTVAL, 147258369147, 1000000, 'VISA', TO_DATE('11-11-2023','MM-DD-YYYY'),357,'123 SPEEDWAY', 'AUSTIN', 'TX', '74453');

INSERT INTO CARD
VALUES(CARD_ID_SEQ.NEXTVAL, 258369147258, 1000001, 'VISA', TO_DATE('10-10-2023','MM-DD-YYYY'),569,'125 AGGIEWAY', 'COLLEGE STATION', 'TX', '77486');

INSERT INTO CARD
VALUES(CARD_ID_SEQ.NEXTVAL, 369147258369, 1000001, 'AMERICAN EXPRESS', TO_DATE('10-10-2024','MM-DD-YYYY'),896,'125 AGGIEWAY', 'COLLEGE STATION', 'TX', '77486');

INSERT INTO CARD
VALUES(CARD_ID_SEQ.NEXTVAL, 852369714852, 1000003, 'MASTERCARD', TO_DATE('05-10-2024','MM-DD-YYYY'),596,'207 MCCOMBS WAY', 'AUSTIN', 'TX', '77486');

INSERT INTO CARD
VALUES(CARD_ID_SEQ.NEXTVAL, 753951852456, 1000002, 'VISA', TO_DATE('05-06-2030','MM-DD-YYYY'),596,'218 MCCOMBS WAY', 'AUSTIN', 'TX', '77486');

COMMIT;

--CREATE CONTENT CREATION ACCOUNTS

INSERT INTO CC
VALUES(CC_ID_SEQ.NEXTVAL, 1000000, 'BEVO', 'MCCOMBS', 147258369147,'bevomc','bevo@utexas.edu','123 SPEEDWAY, AUSTIN, TX 74453',DEFAULT,'USA','TX','136925874125');

INSERT INTO CC
VALUES(CC_ID_SEQ.NEXTVAL, 1000002, 'REV', 'TAMU', 753951852456,'revt','AGGIEMASCOT@tamu.edu','218 MCCOMBS WAY, AUSTIN, TX, 77486','GENERAL','USA','TX','123657984623');

INSERT INTO CC
VALUES(CC_ID_SEQ.NEXTVAL, 1000003, 'REAL', 'AGGIE', 852369714852,'reALAG','agster@tamu.edu','207 MCCOMBS WAY, AUSTIN, TX, 77486','BUSINESS','USA','TX','169856987432');

INSERT INTO CC
VALUES(CC_ID_SEQ.NEXTVAL, 1000001, 'AGGIE', 'MAYS', 369147258369,'AGMAYS','AGGIE@tamu.edu','125 AGGIEWAY, COLLEGE STATION, TX, 77486',DEFAULT,'USA','TX','19658763210');

COMMIT;

--CREATE TOPICS FOR VIDEOS

INSERT INTO TOPIC
VALUES(TOPIC_ID_SEQ.NEXTVAL,'40 ACRES','THESE ARE VIDEOS ABOUT UT',1000004);

INSERT INTO TOPIC
VALUES(TOPIC_ID_SEQ.NEXTVAL,'THE OFFICE','BLOOPERS FROM THE GREATEST COMEDY',1000005);

COMMIT;

--CREATE VIDEOS

INSERT INTO VIDEO
VALUES(VIDEO_ID_SEQ.NEXTVAL,'FRENCH','BEVO HISTORY',1000,'40 ACRES', TO_DATE('2006-09-01 23:22:20','YYYY-MM-DD HH24:MI:SS'),50,124,DEFAULT,DEFAULT,DEFAULT,1000000);

INSERT INTO VIDEO
VALUES(VIDEO_ID_SEQ.NEXTVAL,'SPANISH','SEASON 4 BLOOPERS',1001,'THE OFFICE', TO_DATE('2020-09-21 23:22:20','YYYY-MM-DD HH24:MI:SS'),20,100,DEFAULT,DEFAULT,DEFAULT,1000002);

INSERT INTO VIDEO
VALUES(VIDEO_ID_SEQ.NEXTVAL,'FRENCH','SEASON 3 BLOOPERS',1001, 'THE OFFICE',TO_DATE('2020-09-01 10:55:30','YYYY-MM-DD HH24:MI:SS'),19,96,1500,178,56,1000001);

INSERT INTO VIDEO
VALUES(VIDEO_ID_SEQ.NEXTVAL,'ARABIC','TOP DWIGHT MOMENTS',1001,'THE OFFICE', TO_DATE('2009-09-01 09:20:31','YYYY-MM-DD HH24:MI:SS'),15,150,200000,17080,600,1000001);

COMMIT;
--CREATE COMMENTS

INSERT INTO COMMENT_VIDEO
VALUES(COMMENT_ID_SEQ.NEXTVAL,1000004,'DWIGHT IS THE WORST!',TO_DATE('2009-09-01 09:30:31','YYYY-MM-DD HH24:MI:SS'),1003);

INSERT INTO COMMENT_VIDEO
VALUES(COMMENT_ID_SEQ.NEXTVAL,1000003,'WHAT IS A DWIGHT?',TO_DATE('2009-09-01 09:35:31','YYYY-MM-DD HH24:MI:SS'),1003);

INSERT INTO COMMENT_VIDEO
VALUES(COMMENT_ID_SEQ.NEXTVAL,1000001,'WE HAVE MORE HISTORY',TO_DATE('2006-09-03 10:35:31','YYYY-MM-DD HH24:MI:SS'),1000);

INSERT INTO COMMENT_VIDEO
VALUES(COMMENT_ID_SEQ.NEXTVAL,1000005,'HOLLY IS ET',TO_DATE('2020-09-03 10:35:31','YYYY-MM-DD HH24:MI:SS'),1001);

COMMIT;

/*DEMOSTRATING HOW non- creator CCs are linked to videos. This is used to link vidoes to content creators who did not create the
specific video*/

INSERT INTO LINKED_CC
VALUES(1000001,1002);

COMMIT;

--Create INDEX

/*CREATING INDEX ON COLUMNS THAT ARE FOREIGN KEYS BUT NOT PRIMARY KEYS*/

CREATE INDEX VIDEO_TOPIC_NAME_IX
    ON VIDEO(TOPIC_NAME);

CREATE INDEX CC_CARD_NUMBER_IX
    ON CC(CARD_NUMBER);

/*CREATING TWO OTHER INDEXES. THE FIRST INDEX TO UNDESTAND THE DISTRIBUTION OF CARD TYPES AND THE OTHER CAN BE USED TO SEE WHICH
CONTENT CREATORS COME FROM WHICH STATES*/


CREATE INDEX CARD_CARD_TYPE_IX
    ON CARD(CARD_TYPE);

CREATE INDEX CC_STATE_OF_RESIDENCE_IX
    ON CC(STATE_OF_RESIDENCE);