Public Toilets Relational Database Management System

 

THE SQL PROJECT

The toilet is a place where you can relax immediately after waking up. And a place you need to visit every day for 7 to 8 times combined. Whenever you are outside of home and feel uncomfortable at any point you search for the toilet to lose some of it, sorry full of pressure. But what if you need help finding a toilet in a nearby location?

Yes, India has no toilets to calm down despite having a great Swatchch Bharat mission. There is a single public toilet for lack of people in India.

IDEA:

What an idea sir ji. It's my imagination to have a great infrastructure for public toilets.

• Where one can easily find a nearby toilet.
• Where one can use a water tap by fingertip.
• Where one can by subscription to use any toilet throughout India.
• Where one can buy perfume.
• Where one can buy sanitary pads.
• Where one can relax on the top roof dormitory for few minutes.
• Where one can stay for a long time without doorknocking.

BENEFITS:

• Satisfaction at the topmost level.
• City free from open defacification and urination.
• Excreta can be used for fertilizer manufacturing.
• Clean and hygienic chamber.

FUTURE FOCUS:

• Monitoring which user uses which toilet at which plant for how long (defecation session) can be achieved by installing GPS location and fingerprint sensors on each toilet's water tap.
• As a defecation time can be analysed to see whether any digestive problem is there or not for specific user groups.

CURRENT SITUATION:

For qualitative research, I visited nearby public toilets in my hometown and recorded the current situation of the toilets in the area.

Observations:

• Cleaning is improper and unhygienic.
• No availability of handwash, facewash, and tissue paper. Instead washing powder was given.
• Unavailability of a dustbin.
• Water source is available.
• An attendant needs to follow better practices to maintain it.
• Charging Rs. 10 to use the toilet.
• Chewing and spitting pan masala, gutkha and cigarette smoking are widely accepted.

                    

RELATIONAL DATABASE DESIGN DIAGRAM:

TABLES:

• Subscriber
• Attendant
• Plant
• Toilet
• Visit
• Payment
• Subscription

Rule Followed:

Primary key naming - PrimaryKeyName followed by PK. Ex. SubscriberIDPK.

Foreign key naming - Primary Key column name from the referenced table, followed by name of referencing table and FK. Ex. AttendantIDPlantFK.

DESCRIPTION:

Subscriber: It is a table where the following attributes of a user are stored.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE SUBSCRIBER ( SUBSCRIBERID VARCHAR2(20) CONSTRAINT SUBSCRIBERIDPK PRIMARY KEY,

FIRSTNAME VARCHAR2(13) NOT NULL,

LASTNAME VARCHAR2(14),

SUSERNAME VARCHAR2(22) NOT NULL,

SPASSWORD VARCHAR2(6) NOT NULL,

GENDER VARCHAR2(6),

DOB DATE,

PHONENO INTEGER,

EMAIL VARCHAR2(32),

AREA VARCHAR2(15),

CITY VARCHAR2(4),

PIN INTEGER,

IDPROOF VARCHAR2(15) NOT NULL,

IDPROOFNO VARCHAR2(5) NOT NULL);

(Note: Username column name changed to SUsername and Password to SPassword.)

Attendant: It is a table where attributes of attendants to a specific toilet plant are employed.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE ATTENDANT ( ATTENDANTID VARCHAR2(20) CONSTRAINT ATTENDANTIDPK PRIMARY KEY,

FIRSTNAME VARCHAR2(20) NOT NULL,

LASTNAME VARCHAR2(20),

GENDER VARCHAR2(6),

DOB DATE,

PHONENO INTEGER,

EMAIL VARCHAR2(40),

AREA VARCHAR2(20),

CITY VARCHAR2(10),

PIN INTEGER,

IDPROOF VARCHAR2(15) NOT NULL,

IDPROOFNO VARCHAR2(10) NOT NULL,

SALARY INTEGER);

Plant: It is a table where the attributes of a toilet plant are recorded.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE PLANT (PLANTID VARCHAR2(10) CONSTRAINT PLANTIDPK PRIMARY KEY,

AREA VARCHAR2(30),

CITY VARCHAR2(10),

PIN INTEGER,

ATTENDANTID VARCHAR2(10),

CONSTRAINT ATTENDANTIDPLANTFK FOREIGN KEY (ATTENDANTID) REFERENCES ATTENDANT(ATTENDANTID));

Toilet: It is a table where the attributes of a a toilet are recorded.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE TOILET (TOILETID VARCHAR2(4) CONSTRAINT TOILETIDPK PRIMARY KEY,

PLANTID VARCHAR2(10) NOT NULL,

TTYPE VARCHAR2(30),

TSIZE VARCHAR2(20),

CONSTRAINT PLANTIDTOILETFK FOREIGN KEY (PLANTID) REFERENCES PLANT(PLANTID));

Visit: It is an associate table to track visits of a subscriber to a toilet.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE VISIT (SUBSCRIBERID VARCHAR2(20) NOT NULL,

TOILETID VARCHAR2(4) NOT NULL,

VISITDATE DATE,

FEEDBACK VARCHAR2(40),

CONSTRAINT SUBSCRIBERIDVISITFK FOREIGN KEY (SUBSCRIBERID) REFERENCES SUBSCRIBER(SUBSCRIBERID),

CONSTRAINT TOILETIDVISITFK FOREIGN KEY (TOILETID) REFERENCES TOILET(TOILETID));

Payment: It is a table to track the payments of a subscriber.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE PAYMENT (PAYMENTID VARCHAR2(20) CONSTRAINT PAYMENTIDPK PRIMARY KEY,

SUBSCRIBERID VARCHAR2(20) NOT NULL,

PAYMENTDATE DATE,

PAYMENTAMOUNT INTEGER,

PAYMENTDESCRIPTION VARCHAR2(40),

CONSTRAINT SUBSCRIBERIDPAYMENTFK FOREIGN KEY (SUBSCRIBERID) REFERENCES SUBSCRIBER(SUBSCRIBERID));

Subscription: It is a table to track the payments and subscriptions of a subscriber.

Query Showing Attributes, Data Type & Constraints:

CREATE TABLE SUBSCRIPTION (SUBSCRIPTIONID VARCHAR2(20) CONSTRAINT SUBSCRIPTIONIDPK PRIMARY KEY,

PAYMENTID VARCHAR2(20) NOT NULL,

STARTDATE DATE,

ENDDATE DATE,

UNSUBSCRIBEDATE DATE,

CONSTRAINT PAYMENTIDSUBSCRIPTIONFK FOREIGN KEY (PAYMENTID) REFERENCES PAYMENT(PAYMENTID));

Questions and Queries

-- 17-10-2024

-- find which subscriber didn't visited toilet yet.

select subscriberid, firstname, lastname from subscriber

where subscriberid not in

(select distinct subscriberid from visit);

-- find top 3 attendants w.r.t salary highest to lowest

select attendantid, firstname,salary,rk_salary

from

(select atd.*,

rank() over (order by salary desc) as rk_salary

from attendant atd

order by rk_salary)

where rk_salary <=3;

-- find 5th highest salaried attendant.

select attendantid, firstname, salary, rk_salary

from

(select atd.*,

rank() over (order by salary desc) as rk_salary

from attendant atd

order by rk_salary)

where rk_salary = 5;

-- find last row of attendant table

select * from attendant

where attendantid = (select max(attendantid) from attendant);

-- Note: ensure that max() function applied on primary key column and have ascending order by default.

Use of inner join:

-- find subscriberid who visited toilet along with date.

select sb.subscriberid,sb.firstname,sb.area, v.toiletid, v.visitdate

from subscriber sb inner join visit v on

sb.subscriberid = v.subscriberid;

--find subscriberid who visited toilet along with date and plat name, order by plant area & visit date.

select s.subscriberid,s.firstname,s.area as customer_address,

v.toiletid,p.area as plant_area,v.visitdate

from subscriber s inner join visit v

on s.subscriberid = v.subscriberid

inner join toilet t

on v.toiletid = t.toiletid

inner join plant p

on t.plantid = p.plantid

order by p.area, v.visitdate;

-- find total customer visit at all toilets from dhankawadi area plant.

select count(*) as total_visits_dhankawadi from

(select s.subscriberid,s.firstname,s.area as customer_address,

v.toiletid,p.area as plant_area,v.visitdate

from subscriber s inner join visit v

on s.subscriberid = v.subscriberid

inner join toilet t

on v.toiletid = t.toiletid

inner join plant p

on t.plantid = p.plantid

order by p.area, v.visitdate)

where plant_area = 'Dhankawadi';

-- Data filtering and sorting

-- WHERE clause

-- AND, OR, and NOT operators

-- IN and EXISTS operators

-- order by clause

-- TOP and LIMIT clauses

-- find all subscribers who are from Wakad

select * from subscriber

where area = 'Wakad';

-- find all subscribers who are from Pashan & gender is Male

select * from subscriber

where area = 'Pashan' and gender = 'Male';

-- find all subscribers whose gender is Male and from Pashan area or Kothrud

select * from subscriber

where gender = 'Male' and

(area = 'Pashan' or area = 'Kothrud');

-- Note: Use parantheses correctly.

-- find all subscribers whose ID proof not in Aadhaar Card and Passport

-- use lower function.

select * from subscriber

where lower(idproof) not in (lower('Aadhaar Card'), lower('Passport'));

-- find subscribers whose area in Aundh, Baner, Warje.

-- use upper function

select * from subscriber

where upper(area) in ('AUNDH','BANER','WARJE');

-- find all subscribers who visited toilet.

select s.* from subscriber s

where exists

    (select v.* from visit v

    where v.subscriberid = s.subscriberid)

order by s.subscriberid;

-- find all subscriber not visited toilet yet.

select s.* from subscriber s

where not exists

    (select * from visit v

    where s.subscriberid = v.subscriberid)

order by subscriberid;

-- select all subscribers by DOB ascending to descending.

select * from subscriber

order by dob;

-- select all subscribers from Kothrud by DOB ascending to descending.

select * from subscriber

where upper(area) = 'KOTHRUD'

order by dob;

-- select all subscribers from Kothrud or Pashan having gender Male and order

-- by DOB ascending to descending.

select * from subscriber

where (upper(area) = 'KOTHRUD' or upper(area) = 'PASHAN')

and gender = 'Male'

order by dob;

-- Note: Use parantheses correctly for and - or condition.

-- find firstname and area of all subscribers order by area desc.

select firstname,area from subscriber

order by area desc;