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.

The PetrolPump Management System enforces a fully normalised relational model through twelve explicit foreign key constraints defined in Backend/relation.sql. These constraints ensure that no orphaned records can exist — an employee cannot reference a non-existent pump, an invoice cannot reference a non-existent customer, and so on. Indexes are added on all foreign key columns before the constraints themselves are declared, which improves join performance and satisfies InnoDB’s requirement that referenced columns be indexed. Together, the constraints form a tightly coupled graph of six primary entities connected by four join tables.

​
Complete relation.sql

SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
START TRANSACTION;
SET time_zone = "+00:00";

ALTER TABLE `Tanker`
   ADD KEY `Petrolpump_No` (`Petrolpump_No`);

ALTER TABLE `Employee`
   ADD KEY `Petrolpump_No` (`Petrolpump_No`),
   ADD KEY `Manager_ID` (`Manager_ID`);

ALTER TABLE `Invoice`
   ADD KEY `Date` (`Date`),
   ADD KEY  `Customer_Code` (`Customer_Code`);

ALTER TABLE `Sales`
   ADD KEY `Petrolpump_No` (`Petrolpump_No`);

ALTER TABLE `Owns`
   ADD CONSTRAINT `Owns_ibfk_1` FOREIGN KEY (`Registration_No`) REFERENCES `PetrolPump` (`Registration_No`),
   ADD CONSTRAINT `Owns_ibfk_2` FOREIGN KEY (`Owner_Name`) REFERENCES `Owners` (`Owner_Name`);

ALTER TABLE `Tanker`
   ADD CONSTRAINT `Tanker_ibfk_1` FOREIGN KEY (`Petrolpump_No`) REFERENCES `PetrolPump` (`Registration_No`);

ALTER TABLE `Employee`
   ADD CONSTRAINT `Employee_ibfk_1` FOREIGN KEY (`Petrolpump_No`) REFERENCES `PetrolPump` (`Registration_No`),
   ADD CONSTRAINT `Employee_ibfk_2` FOREIGN KEY (`Manager_ID`) REFERENCES `Employee` (`Employee_ID`);

ALTER TABLE `Invoice`
   ADD CONSTRAINT `Invoice_ibfk_1` FOREIGN KEY (`Date`) REFERENCES `Sales` (`Date`),
   ADD CONSTRAINT `Invoice_ibfk_2` FOREIGN KEY (`Customer_Code`) REFERENCES `Customer` (`Customer_Code`);

ALTER TABLE `Sales`
   ADD CONSTRAINT `Sales_ibfk_1` FOREIGN KEY (`Petrolpump_No`) REFERENCES `PetrolPump` (`Registration_No`);

ALTER TABLE `Contacts`
   ADD CONSTRAINT `Contacts_ibfk_1` FOREIGN KEY (`Employee_ID`) REFERENCES `Employee` (`Employee_ID`);

ALTER TABLE `Serves`
   ADD CONSTRAINT `Serves_ibfk_1` FOREIGN KEY (`Employee_ID`) REFERENCES `Employee` (`Employee_ID`),
   ADD CONSTRAINT `Serves_ibfk_2` FOREIGN KEY (`Customer_Code`) REFERENCES `Customer` (`Customer_Code`);

ALTER TABLE `Sales_Manage`
   ADD CONSTRAINT `Sales_Manage_ibfk_1` FOREIGN KEY (`Employee_ID`) REFERENCES `Employee` (`Employee_ID`),
   ADD CONSTRAINT `Sales_Manage_ibfk_2` FOREIGN KEY (`Sales_No`) REFERENCES `Sales` (`Sales_No`),
   ADD CONSTRAINT `Sales_Manage_ibfk_3` FOREIGN KEY (`Date`) REFERENCES `Sales` (`Date`);

COMMIT;

​
Constraint Explanations

​
Owns — PetrolPump ↔ Owners (many-to-many)

Owns_ibfk_1 and Owns_ibfk_2 together enforce that every row in the Owns join table must reference a valid pump registration number and a valid owner name. This is the only purely many-to-many relationship in the schema: a single pump (e.g. HPC805103) can have multiple partners (Pawan Kumar, Avinash Shankar, Vikash Kumar Tarun), and a single owner can hold stakes in more than one pump.
The Owns relationship is the only many-to-many join in the database. It uses both Registration_No and Owner_Name as a composite primary key, preventing the same owner from being recorded twice for the same pump.

​
Tanker.Petrolpump_No → PetrolPump.Registration_No

Tanker_ibfk_1 ensures every tanker record is associated with a real pump outlet. A tanker belongs to exactly one pump — you cannot insert a tanker row with a Petrolpump_No that does not already exist in PetrolPump. The column is nullable, so tankers that have not yet been assigned to a pump can still be stored.

​
Employee.Petrolpump_No → PetrolPump.Registration_No

Employee_ibfk_1 enforces that every employee must be attached to a real, registered petrol pump. Like tankers, the column is nullable to allow for employees who are temporarily unassigned.

​
Employee.Manager_ID → Employee.Employee_ID (self-referential hierarchy)

Employee_ibfk_2 is a self-join on the Employee table. Every Manager_ID value must correspond to a valid Employee_ID in the same table. This models the organisational hierarchy: nozzle persons report to managers, managers may reference themselves as their own manager. Employees with no manager have NULL in this column.

​
Invoice.Date → Sales.Date

Invoice_ibfk_1 ties every invoice to a date on which a sales shift actually occurred. Because Date is part of the composite primary key on Sales, this constraint guarantees that no invoice can be generated for a day with no recorded nozzle activity.

​
Invoice.Customer_Code → Customer.Customer_Code

Invoice_ibfk_2 links each invoice to a registered customer. The column is nullable, accommodating walk-in or anonymous purchases that should still be recorded.

​
Sales.Petrolpump_No → PetrolPump.Registration_No

Sales_ibfk_1 ensures that every daily sales record belongs to a valid pump. This constraint is what makes cross-pump revenue reporting possible — sales can always be traced back to a specific outlet.

​
Contacts — Employee phone numbers (multi-valued attribute)

Contacts_ibfk_1 ensures that every phone number row in Contacts references a valid employee. This table exists because an employee can have more than one contact number; normalisation requires pulling repeated groups into a separate table rather than adding multiple phone columns to Employee.

​
Serves — Employee ↔ Customer (many-to-many)

Serves_ibfk_1 and Serves_ibfk_2 jointly enforce that both sides of a “serves” relationship must exist. Only nozzle persons who actually dispensed fuel to a customer should appear here, making this table useful for accountability and customer-service analytics.

​
Sales_Manage — Employee ↔ Sales (three-way composite FK)

Sales_Manage carries three foreign keys. Sales_Manage_ibfk_1 links to an employee, and Sales_Manage_ibfk_2 + Sales_Manage_ibfk_3 jointly reference the composite primary key (Sales_No, Date) on the Sales table. This accurately reflects that a sales record is uniquely identified by both its number and its date.
Attempting to DELETE a row from PetrolPump will be blocked by MySQL if any row in Employee, Tanker, or Sales still references that pump’s Registration_No via their respective foreign key constraints. You must delete or re-assign all dependent child rows before the parent pump record can be removed.

​
Entity Relationship Diagram

The diagram below shows the directional flow of foreign key references across all ten tables. Arrows point from the child (the table holding the FK column) toward the parent (the table holding the referenced PK). 
PetrolPump ←──── Tanker
     ↑
     ├──── Employee ────→ Employee (Manager)
     │          │
     │       Contacts
     │       Serves ────→ Customer ←── Invoice
     │       Sales_Manage
     │
     └──── Sales
           ↑
        Invoice (via Date)

Owns: PetrolPump ←──→ Owners
ConstraintChild TableChild ColumnParent TableParent Column
Owns_ibfk_1OwnsRegistration_NoPetrolPumpRegistration_No
Owns_ibfk_2OwnsOwner_NameOwnersOwner_Name
Tanker_ibfk_1TankerPetrolpump_NoPetrolPumpRegistration_No
Employee_ibfk_1EmployeePetrolpump_NoPetrolPumpRegistration_No
Employee_ibfk_2EmployeeManager_IDEmployeeEmployee_ID
Invoice_ibfk_1InvoiceDateSalesDate
Invoice_ibfk_2InvoiceCustomer_CodeCustomerCustomer_Code
Sales_ibfk_1SalesPetrolpump_NoPetrolPumpRegistration_No
Contacts_ibfk_1ContactsEmployee_IDEmployeeEmployee_ID
Serves_ibfk_1ServesEmployee_IDEmployeeEmployee_ID
Serves_ibfk_2ServesCustomer_CodeCustomerCustomer_Code
Sales_Manage_ibfk_1Sales_ManageEmployee_IDEmployeeEmployee_ID
Sales_Manage_ibfk_2Sales_ManageSales_NoSalesSales_No
Sales_Manage_ibfk_3Sales_ManageDateSalesDate

Build docs developers (and LLMs) love

Get started for freeTalk to us