Public Toilets Relational Database Management System - The Starting Point

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.

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

Do you want to see this? Click here - Complete Project