DB.sql

CREATE TABLE  ADMIN (ADMIN_ID Integer PRIMARY key, ADMIN_NAME VARCHAR2(30) NOT NULL, 
	EMAIL VARCHAR2(25), 
	ANUMBER VARCHAR2(12), 
	PASSWORD VARCHAR2(25) NOT NULL
   );

CREATE TABLE  PASSENGER (PASSENGER_ID integer primary key, 
	PASSEN_NAME VARCHAR2(30) NOT NULL, 
	ADDRESS VARCHAR2(70) NOT NULL, 
	DATEOB VARCHAR2(15) NOT NULL, 
	GENDER VARCHAR2(7) NOT NULL, 
	CITY VARCHAR2(15) NOT NULL, 
	CNUMBER VARCHAR2(15) NOT NULL, 
	EMAILID VARCHAR2(25) not NULL, 
	PASSWORD VARCHAR2(20) NOT NULL);

CREATE TABLE  BUSROUTE 
   (ROUTE_ID NUMBER PRIMARY KEY, 
	DSTATION VARCHAR2(15), 
	ASTATION VARCHAR2(15), 
	VSTATION VARCHAR2(30), 
	DISTANCE VARCHAR2(5)
   );


CREATE TABLE  FEEDBACK 
   (	PASSENGER_ID NUMBER, 
	PFEEDBACK VARCHAR2(90), 
	 PRIMARY KEY (PASSENGER_ID), 
	 FOREIGN KEY (PASSENGER_ID) REFERENCES  PASSENGER (PASSENGER_ID) ON DELETE CASCADE
   );


CREATE TABLE  BUSRESERVE
   (	RESERVE_ID NUMBER, 
	BUS_ID NUMBER, 
	SOURCE VARCHAR2(20)NOT NULL, 
	DESTINATION VARCHAR2(20) NOT NULL, 
	SEAT_RESERVE NUMBER NOT NULL, 
	SEAT_NUMB VARCHAR2(20) not NULL, 
	JOURNEY_DATE VARCHAR2(18)not NULL, 
	TOTAL_PRICE VARCHAR2(10) not NULL, 
	PASSENGER_ID NUMBER NOT NULL, 
	PRIMARY KEY (RESERVE_ID, BUS_ID, PASSENGER_ID), 
UNIQUE (RESERVE_ID), 
	FOREIGN KEY (BUS_ID) REFERENCES  BUS (BUS_ID) ON DELETE CASCADE, 
	FOREIGN KEY (PASSENGER_ID) REFERENCES  PASSENGER (PASSENGER_ID) ON DELETE CASCADE
   );


CREATE TABLE  PAYDETAILS 
   (	RESERVE_ID NUMBER, 
	CARD_NUMBER NUMBER NOT NULL, 
	CARD_NAME VARCHAR2(30) NOT NULL, 
	AMOUNT NUMBER NOT NULL, 
	 PRIMARY KEY (RESERVE_ID), 
	 FOREIGN KEY (RESERVE_ID) REFERENCES  BUSRESERVE (RESERVE_ID) ON DELETE CASCADE
   );

CREATE VIEW  BUSDETAILS AS select bus_id , dstation, astation,noofseat, bustype, dep_time, ar_time,price from BUSROUTE r, BUS b where r.route_id = b.route_id;