PROJECT REPORT
ON
ONLINE CAB SYSTEM
DATABASE MANAGEMENT SYSTEM
Department of Information Technology
Faculty of Technology, Dharmsinh Desai University
College Road, Nadiad-387001
Year 2016-2017
DEVELOPED BY: GUIDED BY:
SHAH DISHANT(IT-106) Prof. R M RAVAL
SHAH RAJ(IT-111)
CERTIFICATE
This is to certify that Mr. DISHANT SHAH(IT-106) and Mr.
RAJ SHAH(IT-111) student of B.Tech–IT Semester V of
DHARAMSINH DESAI UNIVERSITY, NADIAD has
successfully completed his term project in subject DATABASE
MANAGEMENT SYSTEM during the academic year 2016-
2017.
Staff in Charge Head of Department
Date: Date:
Acknowledgement
This project being the first live one undertaken by us was an
unforgettable and educative experience. We take this opportunity to
thank all those who have generously helped us to give a proper shape to
our work.
Our sincerest appreciation must be extended to DDIT, Nadiad.
We also want to Thanks Faculties of our college; they have been very
kind & helpful to us.
We are thankful to Prof. Roshni M. Raval who had given a nice
guidance & support to us to fulfil and complete the project within
duration.
Finally, I would like to thank my Head of the Department Prof.
R.S.Chhajed for his constant guidance, encouragement and moral
support.
BY
DISHANT SHAH(IT-106)
RAJ SHAH(IT-111)
Contents of PROJECT REPORT
Content Page No.
1) Project Title 1
2) Certificate 2
3) Acknowledgement 3
4) System Overview 5
-- Current system
-- Objectives of the proposed system
5) E.R.Diagram 6
-- Entities
-- Relationships
--Mapping Constraints
6) Database Schema/Table Definition 7
-- Table Name
-- Field Name
-- Datatype
-- Field size
-- Constraint (e.g. autogenerated, primary key, foreign key)
-- Validation (e.g. not null, default value)
7) Implementation 15
-- Output
8) Conclusion 24
9) Bibliography 25
DESCRIPTION
The existence of online cab services has overcome the problems of transportation
services. For those who unable to buy their own car or vehicle, car rent services
give many benefits for them to travel for any purposes. With advanced in
technology, it creates great opportunities to implement web-based car rental system
which improves and enhances the online cab service operations. Data matching
technique applied in this web-based online cab system helps users to select the
suitable car to rent based on the important requirements. Besides that, web-based
system increases the effectiveness and efficiency of the online cab service
organization in order to run their business operations and management
E-R Diagram
O_ID O_NAME CONTACT
HIRES HEAD OWNS
CNAME
DEAL WITH VID
CNAME GENDER
CHID
VNAME
DRIVER CID CUSTOMER ADRESS VEHICLE
BOOKID VTYPE
PHONE COST
GENDER
STATUS
AGE
SALARY OID
DOB
SELECT PAYS SELECT
BILLID TAMT
CID
VID
BILLING
BIILDATE
DISCOUNT
DETAILS
BOOKID
AMOUNT
BILLIID
STATUS
BOOKING
BOOKDATE
DEPOSIT
SCHEMA DIAGRAM
Relational Tables
1) Customer
Column Name Dat a T ype Nullabl e Def ault Primar y Key
CID VARCHAR2(20) No - 1
CNAME VARCHAR2(20) No - -
GENDER VARCHAR2(1) Yes - -
ADDRESS VARCHAR2(30) No - -
EMAIL VARCHAR2(30) No - -
PHONE NUMBER Yes - -
2) Owner
Column Name Dat a T ype Nullabl e Def ault Primar y Key
OID VARCHAR2(10) No - 1
ONAME VARCHAR2(20) No - -
CONTACT VARCHAR2(30) Yes - -
3) Vehicles
Column Name Dat a T ype Nullabl e Def ault Primar y Key
VID VARCHAR2(10) No - 1
VTYPE VARCHAR2(10) No - -
VNAME VARCHAR2(10) No - -
STATUS VARCHAR2(2) Yes - -
READING NUMBER Yes - -
COST NUMBER No - -
OID VARCHAR2(5) Yes - -
4) Billing
Column Name Dat a T ype Nullabl e Def ault Primar y Key
BILLID VARCHAR2(10) No - 1
CID VARCHAR2(10) Yes - -
VID VARCHAR2(10) Yes - -
BILLDATE DATE No - -
DISCOUNT NUMBER Yes - -
TAMOUNT NUMBER No - -
ADVANCE NUMBER No - -
5) Book details
Column Name Dat a T ype Nullabl e Def ault Primar y Key
BOOKID VARCHAR2(10) No - 1
BILLID VARCHAR2(10) Yes - -
BOOKDATE DATE No - -
READING NUMBER No - -
DEPOSIT NUMBER Yes - -
DISCOUNTID VARCHAR2(10) Yes - -
AMOUNT NUMBER No - -
STATUS VARCHAR2(1) Yes - -
6) Discount
Column Name Dat a T ype Nullabl e Def ault Primar y Key
DID VARCHAR2(10) No - 1
DAMOUNT NUMBER No - -
CID VARCHAR2(10) No - -
CTYPE VARCHAR2(10) Yes - -
BILLID VARCHAR2(10) Yes - -
7) Driver
Column Name Dat a T ype Nullabl e Def ault Primar y Key
CHID VARCHAR2(10) No - 1
CHNAME VARCHAR2(20) No - -
BOOKID VARCHAR2(10) Yes - -
GENDER VARCHAR2(1) Yes - -
SALARY NUMBER No - -
DOB DATE No - -
AGE NUMBER Yes - -
PHONE NUMBER No - -
OID VARCHAR2(5) Yes - -
1. CREATE TABLE CUSTOMERS
CREATE TABLE CUSTOMERS
(CNAME VARCHAR2(20) NOT NULL,
CID VARCHAR2(20) PRIMARY KEY,
GENDER VARCHAR(1),
ADDRESS VARCHAR2(30) NOT NULL,
EMAIL VARCHAR2(30) NOT NULL UNIQUE,
PHONE NUMBER);
2. INSERT DATA INTO CUSTOMERS
INSERT INTO CUSTOMERS
VALUES('AMAL PATEL','C01','M','GUNJAN VAPI','aP@gmail.com',0);
INSERT INTO CUSTOMERS
VALUES('DHRUVIL PATEL','C02','M','BALESHWAR SURAT','dp@gmail.com',0);
INSERT INTO CUSTOMERS
VALUES('DEEP BHATU','C03','M','THALTEJ AHMEDABAD','db@gmail.com',0);
INSERT INTO CUSTOMERS
VALUES('GOKUL NATH','C04','M','ANDHERI E MUMBAI','gn@aol.com',0);
INSERT INTO CUSTOMERS
VALUES('LAKSH NAYAK','C05','M','VRINDAVAN SILVASSA','ln@yahoo.com',0);
INSERT INTO CUSTOMERS
VALUES('PRATIK SOLANKI','C06','M','SATELLITE
AHMEDABAD','ps@gmail.com',0);
INSERT INTO CUSTOMERS
VALUES('SAGAR PANCHAL','C07','M','JUHU MUMBAI','sp@gmail.com',0);
3. CREATE TABLE VEHICLES
CREATE TABLE VEHICLES
(VID VARCHAR2(10) PRIMARY KEY,
VTYPE VARCHAR2(10) NOT NULL,
VNAME VARCHAR2(10) NOT NULL,
STATUS VARCHAR2(2),
READING NUMBER,
COST NUMBER NOT NULL
OID VARCHAR2(5)
FOREIGN KEY(OID) REFERENCES OWNER(OID));
4. INSERT VALUES INTO VEHICLES
INSERT INTO VEHICLES
VALUES('V01','SEDAN','HONDACIVIC','A',25000,10,1);
INSERT INTO VEHICLES
VALUES('V02','HATCHBACK','HYUNDAI i20','A',40000,8,1);
INSERT INTO VEHICLES
VALUES('V03','SUV','RANGE ROVER SPORT','A',1000,25,1);
INSERT INTO VEHICLES
VALUES('V04','LUV','BMW X1','A',15000,15,1);
INSERT INTO VEHICLES
VALUES('V05','SEDAN','SWIFT DZIRE','A',45000,8,1);
INSERT INTO VEHICLES
VALUES('V06','SEDAN','MERCEDES CLS','A',5000,20,1);
5. CREATE TABLE DRIVERS
CREATE TABLE DRIVERS
( CHNAME VARCHAR2(20) NOT NULL,
CHID VARCHAR2(10) PRIMARY KEY,
BOOKID VARCHAR2(10),
GENDER VARCHAR2(1),
SALARY NUMBER NOT NULL,
DOB DATE NOT NULL,
AGE NUMBER,
PHONE NUMBER NOT NULL
FOREIGN KEY(BOOKID) REFERENCES
BOOKDETAILS(BOOKID)
OID VARCHAR2(5)
FOREIGN KEY(OID) REFERENCES OWNER(OID));
6. INSERTING VALUES INTO DRIVERS
INSERT INTO DRIVERS VALUES
('NARAYANKARTHIKEYAN','CH001','BK01','M','20000','
05/OCT/1974',40,9624077772,1);
INSERT INTO DRIVERS VALUES
('KARUNCHANDOK','CH002','BK03','M','20000','05/NOV/1984',30,814
150772,1);
INSERT INTO DRIVERS
VALUES('STIG','CH003','BK04','M','200000','26/NOV/1990',24,8151601
231,1);
7.CREATE TABLE BILLING
CREATE TABLE BILLING
(BILLID VARCHAR2(10) PRIMARY KEY,
CID VARCHAR2(10),
VID VARCHAR2(10), BILLDATE DATE NOT NULL,
DISCOUNT NUMBER,
TAMOUNT NUMBER NOT NULL,
ADVANCE NUMBER NOT NULL,
CONSTRAINT CID_FK FOREIGN KEY (CID) REFERENCES
CUSTOMERS(CID),
CONSTRAINT VID_FK FOREIGN KEY (VID) REFERENCES
VEHICLES(VID) );
8.INSERT VALUES IN BILLING
INSERT INTO BILLING
VALUES('BL01','C01','V01','15/AUG/2015',100,1000,20);
INSERT INTO BILLING
VALUES('BL02','C02','V04','27/AUG/2015',400,4000,80);
INSERT INTO BILLING
VALUES('BL03','C05','V05','17/AUG/2015',100,2000,20);
INSERT INTO BILLING
VALUES('BL04','C03','V03','29/AUG/2015',1000,9000,0);
9.CREATE TABLE BOOKDETAILS
CREATE TABLE BOOKDETAILS
BOOKID VARCHAR2(10) PRIMARY KEY,
BILLID VARCHAR2(10),
BOOKDATE DATE NOT NULL,
READING NUMBER NOT NULL,
DEPOSIT NUMBER,
DISCOUNTID VARCHAR2(10),
AMOUNT NUMBER NOT NULL,
STATUS VARCHAR(1)
FOREIGN KEY(BILLID) REFERENCES BILLING(BILLID) );
10.INSERT VALUES IN BOOKDETAILS
INSERT INTO BOOKDETAILS
VALUES('BK01','BL01','14/AUG/2015',25000,1000,'D01',1000,'Y');
INSERT INTO BOOKDETAILS
VALUES('BK02','BL02','24/AUG/2015',15000,1000,'D02',4000,'Y');
INSERT INTO BOOKDETAILS
VALUES('BK03','BL03','14/AUG/2015',45000,1000,'D03',2000,'Y');
INSERT INTO BOOKDETAILS
VALUES('BK04','BL04','24/AUG/2015',1000,1000,'D04000,'Y');
11. CREATE TABLE OWNER
CREATE TABLE OWNER
(OID VARCHAR2(10) PRIMARY KEY,
ONAME VARCHAR2(20) NOT NULL,
CONTACT VARCHAR2(30));
12. INSERT VALUES INTO OWNERS
INSERT INTO OWNER
VALUES('1','DV CABS','helpcentre@dvcabs.com');
13. CREATE TABLE DISCOUNT
CREATE TABLE DISCOUNT
(DID VARCHAR2(10) PRIMARY KEY,
DAMOUNT NUMBER NOT NULL,
CID VARCHAR2(10) NOT NULL,
CTYPE VARCHAR2(10)
BILLID VARCHAR2(10),
FOREIGN KEY(BILLID) REFERENCES BILLING(BILLID)
FOREIGN KEY(CID) REFERENCES CUSTOMERS(CID));
14. INSERT VALUES IN DISCOUNT
INSERT INTO DISCOUNT
VALUES('D01',100 ,'C01', 'FRESH','BL01');
INSERT INTO DISCOUNT
VALUES('D02',400 ,'C02', 'SILVER’,'BL02');
INSERT INTO DISCOUNT
VALUES('D03',100 ,'C05', 'FRESH','BL03');
INSERT INTO DISCOUNT
VALUES('D04',1000 ,'C03', 'PLATINUM','BL04');
15. PROCEDURE TO GIVE QUANTITY OF AN ATTRIBUTE
DECLARE
TOTALC NUMBER;
TOTALV NUMBER;
TOTALB NUMBER;
TOTALCH NUMBER;
PROCEDURE TOTCUSTOMERS IS
BEGIN
SELECT COUNT(*) INTO TOTALC
FROM CUSTOMERS;
DBMS_OUTPUT.PUT_LINE('Total no. of Customers: ' || TOTALC);
SELECT COUNT(*) INTO TOTALV
FROM VEHICLES;
DBMS_OUTPUT.PUT_LINE('Total no. of Vehicles: ' || TOTALV);
SELECT COUNT(*) INTO TOTALB
FROM BILLING;
DBMS_OUTPUT.PUT_LINE('Total no. of Bookings: ' || TOTALB);
SELECT COUNT(*) INTO TOTALCH
FROM DRIVERS;
DBMS_OUTPUT.PUT_LINE('Total no. of Drivers: ' || TOTALCH);
END;
BEGIN
TOTCUSTOMERS();
END;
16. EXCEPTION FOR CUSTOMER NOT FOUND
DECLARE
c_id CUSTOMERS1.CID%type;
c_name CUSTOMERS.CNAME%type;
c_addr CUSTOMERS.ADDRESS%type;
c_gender CUSTOMERS.GENDER%TYPE;
BEGIN
c_id:='1';
SELECT C.CNAME, C.ADDRESS, C.GENDER INTO c_name, c_addr,
c_gender
FROM CUSTOMERS C
WHERE CID = c_id;
DBMS_OUTPUT.PUT_LINE ('Name: '|| c_name);
DBMS_OUTPUT.PUT_LINE ('Address: ' || c_addr);
DBMS_OUTPUT.PUT_LINE ('Gender: '|| c_gender);
EXCEPTION
WHEN no_data_found THEN
dbms_output.put_line('No such customer!');
END;
17. GREETING PROCEDURE
CREATE OR REPLACE PROCEDURE WELCOME
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('WELCOME TO DV CABS!');
END;
EXECUTING PROCEDURE
BEGIN
WELCOME;
END;
18. AUTO INCREMENT
TRIGGER TO AUTOINCREMENT CUSTOMER ID
CREATE SEQUENCE DINC_CID;
CREATE OR REPLACE TRIGGER CID_INCD
BEFORE INSERT ON CUSTOMERS
FOR EACH ROW
BEGIN
SELECT DINC_CID.NEXTVAL
INTO :NEW.CID
FROM DUAL;
END;
TRIGGER TO AUTOINCREMENT VEHICLE ID
CREATE SEQUENCE DINC_VID;
CREATE OR REPLACE TRIGGER VID_INCD
BEFORE INSERT ON VEHICLES
FOR EACH ROW
BEGIN
SELECT DINC_VID.NEXTVAL
INTO :NEW.VID
FROM DUAL;
END;
TRIGGER TO AUTOINCREMENT BILL ID
CREATE SEQUENCE DINC_BILLID;
CREATE OR REPLACE TRIGGER BILLID_INCD
BEFORE INSERT ON BILLING
FOR EACH ROW
BEGIN
SELECT DINC_BILLID.NEXTVAL
INTO :NEW.BILLID
FROM DUAL; END;
TRIGGER TO AUTOINCREMENT BOOKING ID
CREATE SEQUENCE DINC_BOOKID;
CREATE OR REPLACE TRIGGER BOOKID_INCD
BEFORE INSERT ON BOOKDETAILS
FOR EACH ROW
BEGIN
SELECT DINC_BOOKID.NEXTVAL
INTO :NEW.BOOKID
FROM DUAL;
END;
19. CURSOR TO PRINT BOOKING DETAILS
CREATE VIEW CURSOR2
AS
SELECT B.CID,C.CNAME, V.VNAME, B.TAMOUNT
FROM CUSTOMERS C, VEHICLES V, BILLING B
WHERE C.CID=B.CID AND V.VID=B.VID;
CREATE OR REPLACE PROCEDURE GET_DETAILS(ID IN VARCHAR2) IS DUMMYNO
VARCHAR2(10);
CURSOR DET IS
SELECT CNAME, VNAME, TAMOUNT
FROM CURSOR2
WHERE CID=ID;
NAMEOFC CURSOR2.CNAME%TYPE;
NAMEOFV CURSOR2.VNAME%TYPE;
AMOUNTT CURSOR2.TAMOUNT%TYPE;
BEGIN
OPEN DET;
IF DET%ISOPEN THEN
LOOP
FETCH DET INTO NAMEOFC,NAMEOFV,AMOUNTT;
EXIT WHEN DET%NOTFOUND;
IF DET%FOUND THEN
DBMS_OUTPUT.PUT_LINE('NAME OF CUSTOMER= '||NAMEOFC);
DBMS_OUTPUT.PUT_LINE('NAME OF VEHICLE= '||NAMEOFV);
DBMS_OUTPUT.PUT_LINE('TOTAL AMOUNT= '||AMOUNTT);
END IF;
END LOOP;
ELSE
DBMS_OUTPUT.PUT_LINE('OOPS! SORRY.');
END IF;
CLOSE DET;
END;
20.FUNCTION TO CALCULATE TOTAL AMOUNT
DECLARE
VEHID NUMBER;
METER NUMBER;
COST NUMBER;
T_AMT NUMBER;
V_ID NUMBER;
FUNCTION TOT_AMT(VEHID IN NUMBER)
RETURN NUMBER IS
TOTAL_AMOUNT NUMBER;
BEGIN
V_ID:=VEHID;
SELECT READING INTO METER
FROM VEHICLES1
WHERE VID=VEHID;
SELECT COST INTO COST
FROM VEHICLES1
WHERE VID=VEHID;
TOTAL_AMOUNT:=METER*COST;
UPDATE BILLING1
SET TAMOUNT=TOTAL_AMOUNT
WHERE VID=VEHID;
RETURN TOTAL_AMOUNT;
END;
BEGIN
T_AMT:=TOT_AMT('3');
DBMS_OUTPUT.PUT_LINE('TOTAL AMOUNT IS '||T_AMT);
END;
CONCLUSION
OUR PROJECT ON CAR RENTAL SYSTEM IS FOR
COMPUTERIZING IN A ONLINE CAB COMPANY. THE SOFTWARE
IS VERY EASY TO USE AND CALCULATE EFFICIENTLY ALL THE
TERMS MENTIONED IN A PROJECT. NOW OUR PL/SQL
FUNDAMENTALS AND LOGICS ARE VERY STRONG FOR
DATABASE.
