Skip to main content

Documentation Index

Fetch the complete documentation index at: https://mintlify.com/Adarsh275/PetrolPump-Management-System/llms.txt

Use this file to discover all available pages before exploring further.

Beyond the schema and relationships, the PetrolPump Management System embeds business logic directly in the MySQL server through a trigger, a stored function, and two stored procedures defined in Backend/querry.sql. The trigger enforces a wage floor policy at the database level so it cannot be bypassed by any client. The stored function encapsulates a billing calculation that the Streamlit UI surfaces through a Python wrapper. The procedures package frequently-used reporting queries that can be executed from any MySQL client or, with minor wiring, from the application’s Custom Query runner.

​
Trigger: salary_check

The salary_check trigger fires BEFORE UPDATE on every row of the Employee table. Its purpose is to enforce a minimum salary policy: no employee’s salary may be set below ₹3,00,000 (three lakh rupees). If an UPDATE statement would result in a salary value under that threshold, the trigger raises a user-defined SQL error with SQLSTATE '45000', which causes the entire update to be rolled back by MySQL. 
DELIMITER $$
CREATE TRIGGER salary_check 
BEFORE UPDATE
ON Employee FOR EACH ROW
BEGIN
  DECLARE WAGE INT(7);
  DECLARE error_msg VARCHAR(225);
  SET error_msg = ('Error: Insufficient Salary For Living');
  SET WAGE = new.WAGE;
  IF salary < 300000 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = error_msg;
  END IF;
END $$
DELIMITER ;
The trigger as written in querry.sql contains two bugs. First, SET WAGE = new.WAGE references new.WAGE, but the Employee table has no column named WAGE — the actual column is Salary — so this assignment will raise an error at runtime. Second, the condition IF salary < 300000 refers to a bare identifier salary instead of new.Salary, meaning the comparison checks an undeclared variable (which defaults to NULL) rather than the incoming salary value. As a result the trigger as defined in querry.sql may not behave as intended: the SET WAGE line will fail before the salary check is ever reached.

​
How it surfaces in the Streamlit UI

When a user opens the Update → Employee form and submits a salary value below 300,000, mysql.connector propagates the MySQL SIGNAL as a Python exception. The update.py module catches this and renders it directly in the Streamlit interface using st.exception(err), so the user sees the message Error: Insufficient Salary For Living inline without a server crash.

​
Test query

The following statement from querry.sql can be used to verify the trigger is active. Because 15000 < 300000, the trigger should fire and the update should be rejected: 
UPDATE Employee SET Salary = 15000 WHERE Email_ID = "sfeer334";

​
Dropping the trigger

If you need to remove the salary floor restriction (for example, during development seeding with low test values), drop the trigger with: 
DROP TRIGGER salary_check;
Dropping salary_check removes the database-level wage protection entirely. Any UPDATE on Employee.Salary will then succeed regardless of value. Re-create the trigger before deploying to a production environment.

​
Stored Function: TOTAL_AMOUNT

TOTAL_AMOUNT is a deterministic stored function that calculates the total fuel bill for a given tanker by multiplying its FUEL_PRICE per litre by the FUEL_AMOUNT currently in the tanker. It accepts a single VARCHAR(10) argument — the Tanker_ID — and returns a FLOAT representing the total cost in Indian Rupees. 
DELIMITER $$
CREATE FUNCTION `TOTAL_AMOUNT`(`TID` VARCHAR(10)) RETURNS FLOAT
    DETERMINISTIC
BEGIN
    DECLARE BILL FLOAT;
    DECLARE RATE FLOAT;
    DECLARE VOL  FLOAT;

    SET RATE = (SELECT FUEL_PRICE  FROM TANKER WHERE TANKER_ID = TID);
    SET VOL  = (SELECT FUEL_AMOUNT FROM TANKER WHERE TANKER_ID = TID);
    SET BILL = RATE * VOL;

    RETURN BILL;
END$$
DELIMITER ;

​
Calling the function directly in MySQL

SET @p0='BR6872';
SELECT `TOTAL_AMOUNT`(@p0) AS `TOTAL_AMOUNT`;
For tanker BR6872 (PetrolE10, 513.50 L at ₹101.72/L) this should return approximately 52,231.34.
You can replace BR6872 with any valid Tanker_ID from the Tanker table. The function will look up that tanker’s current FUEL_PRICE and FUEL_AMOUNT at call time, so it always reflects the latest stored values.

​
Python wrapper in database.py

The Streamlit UI exposes this function under the Query → Function menu. The Python wrapper TOTAL_Amount(tanker_id) in database.py executes the two-statement sequence (a SET followed by a SELECT) using the shared cursor c and returns the result set to the calling Streamlit page:
database.py
def TOTAL_Amount(tanker_id):
    query = "SET @p0='{}';".format(tanker_id)
    c.execute(query)
    query = "SELECT `TOTAL_AMOUNT`(@p0) AS `TOTAL_AMOUNT`;"
    c.execute(query)
    result = c.fetchall()
    return result
The function returns a list of tuples (e.g. [(52231.34,)]). The Streamlit page unpacks and displays this value to the user.

​
Stored Procedures

​
Procedure p()

p() performs an INNER JOIN between PetrolPump and Employee on the shared pump registration number. It returns the Registration_No of every pump that has at least one employee record — pumps with no staff are excluded by the inner join semantics. 
DELIMITER $$
CREATE PROCEDURE p()
BEGIN
  SELECT PetrolPump.Registration_No
  FROM PetrolPump
  INNER JOIN Employee
    ON PetrolPump.Registration_No = Employee.Petrolpump_No;
END $$
Call syntax: 
CALL p();

​
Procedure Modify()

Modify() retrieves total_sales and sales_amount from the Sales table for all records where the transaction month is November (MONTH(date) = 11). This is useful for generating end-of-month November revenue summaries across all pumps. 
DELIMITER $$
CREATE PROCEDURE Modify()
BEGIN
  SELECT total_sales, sales_amount
  FROM sales
  WHERE MONTH(date) = 11;
END $$
Call syntax: 
CALL Modify();
Both procedures are defined in Backend/querry.sql and must be created in MySQL before they can be called. They are not currently wired to any Streamlit UI page, but they can be invoked from a MySQL client or integrated into the application’s Custom Query runner by adding a c.callproc() or c.execute("CALL p();") call inside a new database.py helper function.

Build docs developers (and LLMs) love

Get started for freeTalk to us