PetrolPump Database Schema: All Tables and Columns

The PetrolPump Management System database is built on 11 MySQL tables that together model every entity and relationship in a petrol pump operation. Seven of these are primary entity tables — PetrolPump, Owners, Employee, Customer, Invoice, Tanker, and Sales — each representing a real-world object with its own primary key. The remaining four are relational join tables — Owns, Contacts, Serves, and Sales_Manage — which encode the many-to-many and multi-valued relationships between the primary entities. All tables use the InnoDB storage engine with the utf8mb4 character set, giving full Unicode support including emoji.

Primary Entity Tables

PetrolPump

Stores registration and location information for each petrol pump outlet. Registration_No is the primary key and is referenced as a foreign key across Tanker, Employee, Sales, and Owns.

Registration_No

varchar(10)

required

Primary key. Unique registration identifier for the pump (e.g. HPC805103).

Petrolpump_Name

varchar(50)

required

Trading name of the petrol pump outlet. Cannot be NULL.

Company_Name

varchar(30)

Parent petroleum company (e.g. Hindustan Petroleum Corporation). Nullable.

Opening_Year

int(5)

Year the outlet opened for business. Nullable.

State

varchar(30)

Indian state where the pump is located. Nullable.

City

varchar(40)

required

City of operation. Cannot be NULL.

Sample data:

INSERT INTO `PetrolPump` (`Registration_No`, `Petrolpump_Name`, `Company_Name`, `Opening_Year`, `State`, `City`) VALUES
('HPC805103', 'Sumaraj Petroleum', 'Hindustan Petroleum Corporation', 2016, 'Bihar', 'Hisua'),
('BP110054', 'Rajinder Service Station', 'Bharat Petroleum', 2012, 'Delhi', 'CENTRAL DELHI'),
('IOC560008', 'Madhu Enterprises', 'Indian Oil Corporation', 2008, 'Karnataka', 'Banglore'),
('OIL380013', 'Perusahaan Minyak and Gas Bumi', 'Oil India Limited', 2006, 'Gujarat', 'Ahmedabad'),
('RPL673573', 'Tamarassery Reliance Retail Outlet', 'Reliance Petroleum Limited', 2013, 'Kerala', 'Thamarasserry');

Owners

Represents individual or partnership owners of one or more petrol pumps. Owner_Name is the primary key, and the Partnership field records that owner’s percentage stake. An owner can be linked to multiple pumps via the Owns join table.

Owner_Name

varchar(20)

required

Primary key. Full name of the owner.

Contact_NO

char(10)

required

10-digit contact phone number. Cannot be NULL.

DOB

date

Date of birth of the owner. Nullable.

Gender

char

Single-character gender indicator (M or F). Nullable.

Address

varchar(255)

Residential address of the owner. Nullable.

Partnership

int(5)

required

Ownership percentage stake held by this owner. Cannot be NULL.

Sample data:

INSERT INTO `Owners` (`Owner_Name`, `Contact_NO`, `DOB`, `Gender`, `Address`, `Partnership`) VALUES
('Pawan Kumar',       '9431073500', '1971-01-03', 'M', 'Friends colony more,Patna,Bihar', 35),
('Avinash Shankar',   '8783249500', '1973-07-15', 'M', 'Buddha colony,Patna,Bihar', 25),
('Vikash Kumar Tarun','7486249500', '1975-02-05', 'M', 'Tapeshwer Path,Boring road,Patna,Bihar', 45),
('Nirmal Sethi',      '6427894500', '1999-09-11', 'F', 'Pritam Nagar, Paldi, Ahmedabad, Gujarat', 70),
('Neerja Bhanot',     '5963154800', '2000-02-24', 'F', 'Quarters, Sarojini Nagar,New Delhi', 55);

Employee

Holds all staff members across every pump outlet. Employees belong to a pump via Petrolpump_No and may report to a manager via the self-referential Manager_ID column. The Contacts join table extends this entity with multiple phone numbers.

Employee_ID

varchar(10)

required

Primary key. Unique identifier for the employee (e.g. MANG957).

Emp_Name

varchar(30)

required

Full name of the employee. Cannot be NULL.

Emp_Gender

char

Single-character gender indicator (M or F). Nullable.

Designation

varchar(10)

Job role (e.g. MANAGER, NOZZEL PERSON, COOKING). Nullable.

DOB

date

Date of birth. Nullable.

Salary

int(20)

Monthly salary in Indian Rupees. Nullable. Subject to the salary_check trigger (minimum ₹3,00,000).

Emp_Address

varchar(255)

required

Residential address of the employee. Cannot be NULL.

Email_ID

varchar(100)

required

Email address used as a secondary lookup key in queries. Cannot be NULL.

Petrolpump_No

varchar(10)

Foreign key → PetrolPump.Registration_No. The pump this employee works at. Nullable.

Manager_ID

varchar(10)

Self-referential foreign key → Employee.Employee_ID. The direct manager of this employee. Nullable (top-level managers may reference themselves).

Sample data:

INSERT INTO `Employee` (`Employee_ID`, `Emp_Name`, `Emp_Gender`, `Designation`, `DOB`, `Salary`, `Emp_Address`, `Email_ID`, `Petrolpump_No`, `Manager_ID`) VALUES
('FOED452', 'Sheela Reddy',    'F', 'FOOD MANAGEMENT', '1989-11-28', 45000,  'dakbangla choraha,patna',                               'sheela@gmail.com',    'HPC805103', 'MANG957'),
('DRHD746', 'Hima Ullal',      'F', 'COOKING',         '1995-04-18', 25000,  'Bikram Road, Patna',                                    'hima@gmail.com',      'HPC805103', 'FOED452'),
('MANG957', 'Aman kumar',      'M', 'MANAGER',         '1992-01-21', 65000,  'Boaring road, patna',                                   'Aman@outlook.com',    'HPC805103', 'MANG957'),
('FDNG652', 'Hradha Nayar',    'F', 'NOZZEL PERSON',   '1987-08-09', 35000,  'Pandit Bigha, Gaya',                                    'hradha@hotmail.com',  'HPC805103', 'FDEW353'),
('FDSNG43', 'Hemant',          'M', 'CLEANING',        '1995-01-23', 20000,  'Kanvada, Magrol road, Surat',                           'hemant@gmail.com',    'OIL380013', NULL),
('SNGED76', 'Animesh',         'M', 'NOZZEL PERSON',   '1982-08-13', 45000,  'Industrial Development Area, Sector 16, Gurugram',      'animesh@gmail.com',   'OIL380013', NULL),
('FDEW353', 'Saideepak Reddy', 'M', 'NOZZEL PERSON',   '2000-06-30', 40000,  'Lodwadih, Topchanchi, Jharkhand',                       'saideepak@outlook.com','HPC805103','MANG957');

Customer

Represents customers who purchase fuel at the pump. Customer records link to Invoice (for their purchase history) and Serves (for which employees attended them).

Customer_Code

varchar(10)

required

Primary key. Unique identifier for the customer (e.g. SFG252).

C_Name

varchar(30)

required

Full name of the customer. Cannot be NULL.

Phone_No

char(10)

10-digit contact number. Nullable.

Email_ID

varchar(100)

Email address. Nullable.

Gender

char

Single-character gender indicator (M or F). Nullable.

City

varchar(50)

City of residence. Nullable.

Age

int(3)

Age in years. Nullable.

Sample data:

INSERT INTO `Customer` (`Customer_Code`, `C_Name`, `Phone_No`, `Email_ID`, `Gender`, `City`, `Age`) VALUES
('SFG252', 'Akash',   '6542589700', 'akash@gmail.com',    'M', 'Bihar',     27),
('GHE785', 'Praneet', '7539514600', 'praneet@yahoo.com',  'M', 'Orissa',    59),
('FJD253', 'Chetan',  '8426951300', 'chetan@hotmail.com', 'M', 'Bengalore', 24),
('OUI325', 'Ayush',   '7618425500', 'ayush@outlook.com',  'M', 'Kota',      18),
('CGM235', 'Vinesh',  '6794324600', 'vines@pesu.pes.edu', 'M', 'Kolkata',   54),
('BFR426', 'Anamika', 9569731800,   'anamika@gmai.com',   'F', 'Jharkhand', 26);

Invoice

Records each fuel sale transaction. Every invoice is tied to a date (which must exist in the Sales table) and optionally to a customer. Payment type, fuel details, discount, and total price are all captured here.

Invoice_No

varchar(10)

required

Primary key. Unique invoice identifier (e.g. XC34).

Date

date

required

Date of the transaction. Cannot be NULL. Foreign key → Sales.Date.

Payment_Type

varchar(20)

required

Mode of payment (e.g. Cash, UPI, Credit Card, Debit Card). Cannot be NULL.

Fuel_Amount

float(15)

Quantity of fuel dispensed in litres. Nullable.

Fuel_Type

varchar(15)

Type of fuel purchased (e.g. PetrolE10, Diesel, Gasoline91). Nullable.

Discount

int(5)

Discount applied to the transaction (percentage or flat amount). Nullable.

Total_Price

float(10)

required

Final billed amount in Indian Rupees after any discount. Cannot be NULL.

Customer_Code

varchar(10)

Foreign key → Customer.Customer_Code. Nullable (walk-in customers may not be registered).

Sample data:

INSERT INTO `Invoice` (`Invoice_No`, `Date`, `Payment_Type`, `Fuel_Amount`, `Fuel_Type`, `Discount`, `Total_Price`, `Customer_Code`) VALUES
('XC34', '2022-11-20', 'Cash',        7,    'PetrolE10',  10,   640.83,  'BFR426'),
('NR43', '2022-11-20', 'UPI',         5.4,  'Gasoline91', NULL, 578.07,  'GHE785'),
('MN34', '2020-06-30', 'Credit Card', 15.8, 'Diesel',     7.5,  1284.51, 'OUI325'),
('FG43', '2022-10-27', 'UPI',         4.9,  'Gasoline91', 5,    498.32,  'SFG252'),
('DS85', '2019-08-19', 'Debit Card',  6.8,  'Diesel',     NULL, 597.65,  'OUI325');

Tanker

Represents fuel tankers that supply each pump. Each tanker carries a single fuel type, and its total bill can be computed using the TOTAL_AMOUNT stored function (Fuel_Price × Fuel_Amount).

Tanker_ID

varchar(10)

required

Primary key. Unique identifier for the tanker (e.g. BR6872).

Capacity

float(10)

Maximum storage capacity of the tanker in litres. Nullable.

pressure

float(10)

Operating pressure of the tanker. Nullable.

Fuel_ID

varchar(10)

required

Internal identifier for the fuel batch. Cannot be NULL.

Fuel_Amount

float(15)

Current quantity of fuel held in the tanker in litres. Nullable.

Fuel_Name

varchar(20)

Human-readable fuel name (e.g. PetrolE10, Diesel, CNG, Kerosene). Nullable.

Fuel_Price

float(5)

required

Price per litre in Indian Rupees. Cannot be NULL. Used by TOTAL_AMOUNT function.

Petrolpump_No

varchar(10)

Foreign key → PetrolPump.Registration_No. The pump this tanker supplies. Nullable.

Sample data:

INSERT INTO `Tanker` (`Tanker_ID`, `Capacity`, `pressure`, `Fuel_ID`, `Fuel_Amount`, `Fuel_Name`, `Fuel_Price`, `Petrolpump_No`) VALUES
('BR6872', 5000,  550,  'A1234', 513.50,  'PetrolE10',  101.72, 'HPC805103'),
('JK2611', 1000,  845,  'L7363', 238.24,  'Kerosene',    77.03, 'OIL380013'),
('MP4928', 5000,  1545, 'K5363', 1200.95, 'CNG',         99.50, 'BP110054'),
('JH7523', 10000, 3500, 'Z6353', 751.89,  'Diesel',      87.89, 'HPC805103'),
('UP9875', 15000, 785,  'R4743', 576.26,  'Gasoline91', 107.05, 'OIL380013');

Sales

Records daily nozzle-level sales readings at each pump. The composite primary key (Sales_No, Date) allows the same sales record identifier to appear on multiple dates. Sales_Manage links each sales record back to the employee who managed it.

Sales_No

varchar(10)

required

Part of the composite primary key. Unique sales record identifier (e.g. FGHGE32).

Date

date

required

Part of the composite primary key. Date of the sales shift. Cannot be NULL. Referenced as FK by Invoice.Date and Sales_Manage.Date.

Nozzel_No

int(4)

required

Nozzle number at the pump that was active for this record. Cannot be NULL.

Starting_Reading

int(7)

required

Meter reading at the start of the shift in litres. Cannot be NULL.

Ending_Reading

int(7)

required

Meter reading at the end of the shift in litres. Cannot be NULL.

Total_Sales

float(10)

required

Total volume of fuel sold during the shift (litres). Cannot be NULL.

Sales_Amount

float(10)

required

Total revenue for the shift in Indian Rupees. Cannot be NULL.

Petrolpump_No

varchar(10)

Foreign key → PetrolPump.Registration_No. The pump at which sales occurred. Nullable.

Sample data:

INSERT INTO `Sales` (`Sales_No`, `Date`, `Nozzel_No`, `Starting_Reading`, `Ending_Reading`, `Total_Sales`, `Sales_Amount`, `Petrolpump_No`) VALUES
('FGHGE32', '2022-11-20', 1, 45687, 49782, 17584.45, 106.52,  'HPC805103'),
('MVBER67', '2022-11-20', 2, 48325, 53842,  4253.45, 205.5,   'OIL380013'),
('IUOSF98', '2019-08-19', 2, 12757, 23454,  1254.71,  89.45,  'HPC805103'),
('GDZJD24', '2019-08-19', 1, 62725, 68725,  5466.45, 125.85,  'OIL380013'),
('QWRGH87', '2022-11-22', 3, 12758, 19758,  7854.65, 425.25,  'HPC805103');

Relational Join Tables

These four tables model multi-valued attributes and many-to-many relationships. They carry no standalone meaning outside of the entities they connect, and their primary keys are always composite.

Owns

Implements the many-to-many relationship between petrol pumps and their owners. A single pump can have multiple partners, and a single owner can hold stakes in multiple pumps. Both columns together form the composite primary key.

Registration_No

varchar(10)

required

Part of composite primary key. Foreign key → PetrolPump.Registration_No.

Owner_Name

varchar(20)

required

Part of composite primary key. Foreign key → Owners.Owner_Name.

Sample data:

INSERT INTO `Owns` (`Registration_No`, `Owner_Name`) VALUES
('HPC805103', 'Pawan Kumar'),
('HPC805103', 'Avinash Shankar'),
('HPC805103', 'Vikash Kumar Tarun'),
('OIL380013', 'Nirmal Sethi'),
('OIL380013', 'Vikash Kumar Tarun'),
('BP110054',  'Neerja Bhanot'),
('BP110054',  'Pawan Kumar');

Contacts

Models the multi-valued attribute of employee phone numbers. Because an employee can have more than one contact number, this is stored as a separate table rather than as a column on Employee.

Employee_ID

varchar(10)

required

Part of composite primary key. Foreign key → Employee.Employee_ID.

Contact_NO

char(10)

required

Part of composite primary key. A 10-digit phone number belonging to the employee.

Sample data:

INSERT INTO `Contacts` (`Employee_ID`, `Contact_NO`) VALUES
('MANG957', '6299337300'),
('MANG957', '8540074600'),
('FOED452', '6256575800'),
('FOED452', '9678225400'),
('FDSNG43', '8312243800'),
('FDNG652', '5249785500');

Serves

Captures the many-to-many relationship between employees and customers — specifically, which employees have served which customers at the pump.

Employee_ID

varchar(10)

required

Part of composite primary key. Foreign key → Employee.Employee_ID.

Customer_Code

varchar(10)

required

Part of composite primary key. Foreign key → Customer.Customer_Code.

Sample data:

INSERT INTO `Serves` (`Employee_ID`, `Customer_Code`) VALUES
('FDEW353', 'SFG252'),
('FDEW353', 'CGM235'),
('FDEW353', 'BFR426'),
('FDNG652', 'SFG252'),
('FDNG652', 'CGM235');

Sales_Manage

Links employees to the specific sales shift records they managed. The three-column composite primary key (Employee_ID, Sales_No, Date) reflects the composite primary key of the Sales table.

Employee_ID

varchar(10)

required

Part of composite primary key. Foreign key → Employee.Employee_ID.

Sales_No

varchar(10)

required

Part of composite primary key. Foreign key → Sales.Sales_No.

Date

date

required

Part of composite primary key. Foreign key → Sales.Date.

Sample data:

INSERT INTO `Sales_Manage` (`Employee_ID`, `Sales_No`, `Date`) VALUES
('FDEW353', 'FGHGE32', '2022-11-20'),
('FDEW353', 'IUOSF98', '2019-08-19'),
('FDNG652', 'QWRGH87', '2022-11-22'),
('SNGED76', 'GDZJD24', '2019-08-19'),
('SNGED76', 'MVBER67', '2022-11-20');

Get Started

Database

User Guide

Advanced

PetrolPump Database Schema: All Tables and Columns

Primary Entity Tables

Relational Join Tables

Build docs developers (and LLMs) love

Get Started

Database

User Guide

Advanced

Documentation Index

​Primary Entity Tables

​Relational Join Tables

Build docs developers (and LLMs) love

Primary Entity Tables

Relational Join Tables