REM *************  SCRIPT TO CREATE TABLES FOR ORACLE BOOK   ****************
REM 
REM 		   	AUTHOR : P.SRIKANTH
REM  			DATE   : 16-JULY-2015 
REM			PLACE  : VIZAG.
REM 
REM *************************************************************************

connect  system/oracle

create user book identified by book
    default tablespace users 
    quota  unlimited on users
    temporary tablespace temp;
    
grant connect, resource to book;

connect book/book


REM --------- FIRST DROP ALL EXISTING TABLES ------------------

DROP TABLE PAYMENTS CASCADE CONSTRAINTS;
DROP TABLE STUDENTS CASCADE CONSTRAINTS;
DROP TABLE BATCHES CASCADE CONSTRAINTS;
DROP TABLE COURSE_FACULTY CASCADE CONSTRAINTS;
DROP TABLE FACULTY CASCADE CONSTRAINTS;
DROP TABLE COURSES CASCADE CONSTRAINTS;

CREATE TABLE  COURSES
(
   CODE		VARCHAR2(5)  CONSTRAINT COURSES_PK  PRIMARY KEY,
   NAME		VARCHAR2(30) CONSTRAINT COURSES_NAME_U UNIQUE 
                             CONSTRAINT COURSES_NAME_NN NOT NULL,
   DURATION 	NUMBER(3)    CONSTRAINT COURSES_DURATION_CHK  CHECK( DURATION >= 1),
   FEE          NUMBER(5)    CONSTRAINT COURSES_FEE_CHK CHECK( FEE >= 0 ),
   PREREQ	VARCHAR2(100)
);

CREATE TABLE  FACULTY
(
   CODE			VARCHAR2(5) CONSTRAINT FACULTY_PK PRIMARY KEY,
   NAME			VARCHAR2(30),
   QUALIFICATION	VARCHAR2(30),
   EXPERIENCE		VARCHAR2(100) 
);

CREATE TABLE  COURSE_FACULTY
(
   FACULTY_CODE		VARCHAR2(5) CONSTRAINT COURSE_FACULTY_FCODE_FK REFERENCES FACULTY(CODE),
   COURSE_CODE		VARCHAR2(5) CONSTRAINT COURSE_FACULTY_CCODE_FK REFERENCES COURSES(CODE),
   SINCEWHEN    	NUMBER(4),
   CONSTRAINT COURSE_FACULTY_PK  PRIMARY KEY(FACULTY_CODE, COURSE_CODE)
);


CREATE TABLE BATCHES
(
   CODE			VARCHAR2(5) CONSTRAINT BATCHES_PK PRIMARY KEY,
   COURSE_CODE        	VARCHAR2(5) CONSTRAINT BATCHES_CCODE_FK REFERENCES COURSES(CODE),
   FACULTY_CODE        	VARCHAR2(5) CONSTRAINT BACHES_FCODE_FK REFERENCES FACULTY(CODE),
   STDATE       	DATE CONSTRAINT BATCHES_STDATE_NN NOT NULL,
   ENDDATE     	 	DATE,
   TIMINGS      	VARCHAR2(15),
   CONSTRAINT  BATCHES_DATES_CHK  CHECK ( STDATE <= ENDDATE)
);


CREATE TABLE STUDENTS
(
   ADMNO		NUMBER(5) CONSTRAINT STUDENTS_PK PRIMARY KEY,
   BATCH_CODE        	VARCHAR2(5) CONSTRAINT STUDENTS_BCODE_FK REFERENCES BATCHES(CODE),
   NAME        		VARCHAR2(30) CONSTRAINT STUDENTS_NAME_NN NOT NULL,
   GENDER       	CHAR(1) CONSTRAINT STUDENTS_GENDER_CHK CHECK( UPPER(GENDER)  IN ('M','F')),
   DJ           	DATE,
   PHONE        	VARCHAR2(10),
   EMAIL        	VARCHAR2(30)
);


CREATE TABLE PAYMENTS
(
   INVNO        	NUMBER(5) CONSTRAINT PAYMENTS_PK PRIMARY KEY,
   ADMNO		NUMBER(5) CONSTRAINT PAYMENTS_ADMNO_FK REFERENCES STUDENTS(ADMNO),
   DP           	DATE  DEFAULT SYSDATE CONSTRAINT PAYMENTS_DP_NN  NOT NULL,
   AMOUNT       	NUMBER(5) CONSTRAINT PAYMENTS_AMOUNT_CHK CHECK (AMOUNT > 0)
);