-- PINCODE
insert into
pincode (pin_code, city, state_place)
values
('560001', 'Bengaluru', 'Karnataka'),
('110001', 'New Delhi', 'Delhi'),
('400001', 'Mumbai', 'Maharashtra'),
('700001', 'Kolkata', 'West Bengal');
-- CAR_MODEL
insert into
car_model (brand, seats, ac, fuel_type, base_price)
values
('Toyota', 5, true, 'Petrol', 2500.00),
('Hyundai', 5, true, 'Diesel', 2200.00),
('Tata', 7, true, 'Diesel', 2800.00),
('Maruti', 4, false, 'Petrol', 1800.00);
-- OWNER
insert into
owner (owner_id, name, email, phone_no, aadhar, street, pin_code)
values
(1, 'A', '[email protected]', '9876543210', '111122223333', 'MG Road', '560001'),
(2, 'B', '[email protected]', '9876543211', '222233334444', 'Connaught Place', '110001'),
(3, 'C', '[email protected]', '9876543212', '333344445555', 'Marine Drive', '400001');
-- CUSTOMER
insert into
customer (customer_id, name, email, phone_no, aadhar, street, pin_code)
values
(1, 'c1', '[email protected]', '9000000001', '444455556666', 'Indiranagar', '560001'),
(2, 'c2', '[email protected]', '9000000002', '555566667777', 'Salt Lake', '700001'),
(3, 'c3', '[email protected]', '9000000003', '666677778888', 'Lajpat Nagar', '110001');
-- DRIVER
insert into
driver (driver_id, name, email, phone_no, aadhar, experience, street, pin_code)
values
(1, 'driver 1', '[email protected]', '9100000001', '777788889999', '5 years', 'Koramangala', '560001'),
(2, 'driver 2', '[email protected]', '9100000002', '888899990000', '3 years', 'Andheri West', '400001'),
(3, 'driver 3', '[email protected]', '9100000003', '999900001111', '7 years', 'Dwarka', '110001');
-- CAR
insert into
car (car_id, registration_no, brand, book_status, owner_id)
values
(1, 'KA01AB1234', 'Toyota', false, 1),
(2, 'DL01CD5678', 'Hyundai', true, 2),
(3, 'MH01EF9012', 'Tata', false, 3),
(4, 'WB01GH3456', 'Maruti', true, 1);
-- BOOKING
insert into
booking (booking_id, customer_id, driver_id, car_id, booking_date, pickup_date, return_date, area_radius)
values
(1, 1, 1, 1, '2026-11-01', '2026-11-02', '2026-11-05', 15.5),
(2, 2, 2, 2, '2026-11-03', '2026-11-04', '2026-11-06', 20.0),
(3, 3, 3, 3, '2026-11-05', '2026-11-06', '2026-11-10', 12.0);
-- PAYMENT
insert into
payment (payment_id, booking_id, payment_date, payment_method, payment_status)
values
(1, 1, '2026-11-01', 'UPI', true),
(2, 2, '2026-11-03', 'Card', true),
(3, 3, '2026-11-05', 'Cash', false);
/**************************************** The CAR related schemas ******************************
CAR (car_id, model, brand, seats, ac, registration_no, fuel_type, status, base_price, owner_id)
*/
-- PINCODE (pin_code, city, state)
create table pincode (
-- this is unified respected to all address
pin_code varchar(50) primary key,
city varchar(50),
state_place varchar(50)
);
-- CAR_MODEL (model, brand, seats, ac, fuel_type, base_price)
create table car_model (
brand varchar(50),
-- model varchar(50),
seats integer,
ac boolean,
fuel_type varchar(50),
base_price numeric,
-- primary key (brand, model)
primary key (brand)
);
-- OWNER (owner_id, name, phone, aadhaar_no, owner_type, street, pin_code)
create table owner (
owner_id integer primary key,
name varchar(50),
email varchar(50) unique not null,
phone_no varchar(50) unique not null,
aadhar varchar(50) unique not null,
street varchar(50),
pin_code varchar(50),
foreign key (pin_code) references pincode (pin_code)
);
-- CAR (car_id, brand, registration_no, book_status, owner_id)
create table car (
car_id integer primary key,
registration_no varchar(10) unique not null,
brand varchar(50),
book_status boolean,
owner_id integer,
foreign key (brand) references car_model (brand),
foreign key (owner_id) references owner (owner_id)
);
-- what to do with {brand --> model} FD?
/**************************************** The USER related schemas ******************************
CUSTOMER (customer_id, name, email, phone_no, aadhaar, street, city, state, pin_code)
OWNER (owner_id, name, phone, aadhaar_no, owner_type, street, city, state, pin_code)
DRIVER (driver_id, name, email, phone_no, aadhaar, experience, status, street, city, state, pin_code)
*/
-- CUSTOMER (customer_id, name, email, phone_no, aadhaar, street, pin_code)
create table customer (
customer_id integer primary key,
name varchar(50),
email varchar(50) unique not null,
phone_no varchar(50) unique not null,
aadhar varchar(50) unique not null,
street varchar(50),
pin_code varchar(50),
foreign key (pin_code) references pincode (pin_code)
);
-- DRIVER (driver_id, name, email, phone_no, aadhaar, experience, status, street, pin_code)
create table driver (
driver_id integer primary key,
name varchar(50),
email varchar(50) unique not null,
phone_no varchar(50) unique not null,
aadhar varchar(50) unique not null,
experience varchar(50) not null,
street varchar(50),
pin_code varchar(50),
foreign key (pin_code) references pincode (pin_code)
);
/**************************************** The BOOKING relation schemas ******************************
BOOKING (booking_id, customer_id, driver_id, car_id, booking_date, pickup_date, return_date, radius)
*/
create table booking (
booking_id integer primary key,
customer_id integer not null,
driver_id integer not null,
car_id integer not null,
booking_date date,
pickup_date date,
return_date date,
area_radius numeric,
foreign key (customer_id) references customer (customer_id),
foreign key (driver_id) references driver (driver_id),
foreign key (car_id) references car (car_id)
);
/**************************************** The PAYMENT relation schemas ******************************
PAYMENT (payment_id, booking_id, payment_date, amount, payment_method, payment_status)
*/
create table payment (
payment_id integer primary key,
booking_id integer not null,
payment_date date,
payment_method varchar(50),
payment_status boolean,
foreign key (booking_id) references booking (booking_id)
);