Managing Employees: Staff, Salaries, and Designations

Employees are the staff members assigned to a petrol pump station. Each employee record references the pump they work at via Petrolpump_No and can optionally reference a manager within the same organisation via Manager_ID — a self-referential foreign key that points back to another row in the same Employee table. A BEFORE UPDATE trigger named salary_check is intended to reject any UPDATE that sets an employee’s Salary below ₹300,000; however, the trigger as written in querry.sql contains a bug: the condition reads if salary < 300000 (a bare column name) rather than if new.Salary < 300000, and the preceding assignment set WAGE = new.WAGE references a non-existent column. The trigger’s actual behaviour depends on the MySQL version and session context — in some environments the bare salary resolves to the old row value rather than the proposed new value, so the guard may not work as intended. The intent is to prevent salaries below ₹300,000.

Field Reference

Employee_ID

varchar(10)

required

Primary key. A unique alphanumeric identifier for the employee, up to 10 characters.Example: MANG957

Emp_Name

varchar(30)

required

The employee’s full name, up to 30 characters.Example: Aman Kumar

Emp_Gender

char

A single character indicating the employee’s gender. Accepted values are M (male) or F (female).

Designation

varchar(10)

The employee’s job title, up to 10 characters.Examples: MANAGER, NOZZEL PERSON, CLEANING, COOKING, FOOD MANAGEMENT

DOB

date

The employee’s date of birth. Entered via a Streamlit date picker and stored in YYYY-MM-DD format.Example: 1992-01-21

Salary

integer

The employee’s salary in Indian Rupees (INR). Stored as an integer. At update time, the salary_check trigger is intended to reject values below ₹300,000, but the trigger condition references the bare column name salary (not new.Salary), which is a known bug in the source. Its enforcement is therefore unreliable and depends on the MySQL version in use.

Emp_Address

varchar(255)

required

The employee’s residential address, up to 255 characters.Example: Boaring road, Patna

Email_ID

varchar(100)

required

The employee’s email address, up to 100 characters.Example: aman@outlook.com

Petrolpump_No

varchar(10)

Foreign key reference to PetrolPump.Registration_No. Associates the employee with the station they work at.Example: HPC805103

Manager_ID

varchar(10)

Self-referential foreign key. Points to another Employee.Employee_ID within the same table, establishing a manager–subordinate hierarchy. Can be NULL for top-level managers or employees with no assigned manager.Example: MANG957

Add an Employee

Open the Employee table

In the left sidebar, click the Tables dropdown and select Employee.

Select the Add operation

In the CRUD Operations dropdown, select Add.The main area displays the subheader “Enter Employee Details:”.

Fill in all ten fields

Complete every field shown in the form:

Field	Input type	Required
Employee_ID	Text	✅
Emp_Name	Text	✅
Emp_Gender	Text (`M` or `F`)	✗
Designation	Text	✗
DOB	Date picker	✗
Salary	Number	✗
Emp_Address	Text	✅
Email_ID	Text	✅
Petrolpump_No	Text (FK to PetrolPump)	✗
Manager_ID	Text (FK to Employee)	✗

Submit the record

Click Add Employee Details. A green success banner confirms:

Successfully added Employee details: MANG957

The UI function in create.py:

def create_for_Employee():
    with st.container():
        Employee_ID   = st.text_input("Employee_ID")
        Emp_Name      = st.text_input("Emp_Name:")
        Emp_Gender    = st.text_input("Emp_Gender:")
        Designation   = st.text_input(" Designation:")
        DOB           = st.date_input("DOB:")
        Salary        = st.number_input("Salary:")
        Emp_Address   = st.text_input("Emp_Address:")
        Email_ID      = st.text_input("Email_ID:")
        Petrolpump_No = st.text_input("Petrolpump_No:")
        Manager_ID    = st.text_input("Manager_ID:")

    if st.button("Add Employee Details"):
        add_Employee_data(
            Employee_ID, Emp_Name, Emp_Gender, Designation, DOB, Salary,
            Emp_Address, Email_ID, Petrolpump_No, Manager_ID
        )
        st.success("Successfully added Employee details: {}".format(Employee_ID))

The INSERT statement in database.py:

def add_Employee_data(Employee_ID, Emp_Name, Emp_Gender, Designation, DOB, Salary,
                      Emp_Address, Email_ID, Petrolpump_No, Manager_ID):
    c.execute(
        'insert into Employee '
        '(Employee_ID, Emp_Name, Emp_Gender, Designation, DOB, Salary, '
        ' Emp_Address, Email_ID, Petrolpump_No, Manager_ID) '
        'values (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)',
        (Employee_ID, Emp_Name, Emp_Gender, Designation, DOB, Salary,
         Emp_Address, Email_ID, Petrolpump_No, Manager_ID)
    )
    mydb.commit()

View Employees

Open the Employee table

In the sidebar Tables dropdown, select Employee.

Select View

In the CRUD Operations dropdown, select View.

Expand the table

Click the “View all Employees” expander to see every employee row as a Pandas DataFrame with all ten columns: Employee_ID, Emp_Name, Emp_Gender, Designation, DOB, Salary, Emp_Address, Email_ID, Petrolpump_No, Manager_ID.

def read_for_Employee():
    result = view_all_Employee_data()
    df = pd.DataFrame(
        result,
        columns=[
            'Employee_ID', 'Emp_Name', 'Emp_Gender', 'Designation', 'DOB',
            'Salary', 'Emp_Address', 'Email_ID', 'Petrolpump_No', 'Manager_ID'
        ]
    )
    with st.expander("View all Employees"):
        st.dataframe(df)

Update an Employee

Open the Employee table

In the sidebar Tables dropdown, select Employee.

Select Update

In the CRUD Operations dropdown, select Update.A “Current Employee details” expander shows the current data, and an “Employee to Edit” selectbox lists every Employee_ID.

Select the employee

Choose the target Employee_ID from the dropdown. All nine editable fields are pre-filled with that employee’s current values.

Edit the fields

Modify any of the nine editable fields: Emp_Name, Emp_Gender, Designation, DOB, Salary, Emp_Address, Email_ID, Petrolpump_No, or Manager_ID.

Save changes

Click Update Employee.

On success, a green “Successfully updated” message is displayed.
If the salary_check trigger fires (intended for salaries below ₹300,000 — see the Warning below for the known trigger bug), the update is rejected and the exception is displayed inline via st.exception(err).

The salary_check database trigger is intended to reject any UPDATE that sets Salary to a value less than ₹300,000. However, the trigger as written in querry.sql uses if salary < 300000 — a bare column name — instead of if new.Salary < 300000. Combined with an assignment that references a non-existent column (new.WAGE), the trigger’s actual behaviour depends on the MySQL version. In some environments it may not fire reliably. The trigger error message is "Error: Insufficient Salary For Living" and, when it does fire, the exception is caught by st.exception(err) and displayed inline.

The update_for_Employee() function in update.py wraps the database call in a try/except to catch and surface trigger errors:

if st.button("Update Employee"):
    try:
        edit_Employee_data(
            new_Emp_Name, new_Emp_Gender, new_Designation, new_DOB, new_Salary,
            new_Emp_Address, new_Email_ID, new_Petrolpump_No, new_Manager_ID,
            Employee_ID
        )
        st.success("Successfully updated")
    except Exception as err:
        st.exception(err)

The UPDATE SQL executed by edit_Employee_data() in database.py:

UPDATE Employee
SET Emp_Name=%s,
    Emp_Gender=%s,
    Designation=%s,
    DOB=%s,
    Salary=%s,
    Emp_Address=%s,
    Email_ID=%s,
    Petrolpump_No=%s,
    Manager_ID=%s
WHERE Employee_ID=%s

Delete an Employee

Open the Employee table

In the sidebar Tables dropdown, select Employee.

Select Remove

In the CRUD Operations dropdown, select Remove.

Choose the employee to delete

Select the target Employee_ID from the “Employee to delete” selectbox. An inline warning is displayed:

Do you want to delete :: MANG957

Confirm deletion

Click Delete Employee. On success the message “Employee has been deleted successfully” is shown and the updated table appears in the “Updated data” expander.

def delete_data_Employee(selected_Employee):
    c.execute('DELETE FROM Employee WHERE Employee_ID="{}"'.format(selected_Employee))
    mydb.commit()

Self-Referential Manager Hierarchy

The Manager_ID column is a self-referential foreign key — it stores the Employee_ID of the employee who manages this record. This allows the system to model a multi-level reporting structure entirely within the single Employee table. How it works:

A top-level manager can list themselves as their own Manager_ID (or the column can be left NULL).
Subordinates set their Manager_ID to the Employee_ID of their direct manager.

Example from the seed data at pump HPC805103:

Employee_ID	Emp_Name	Designation	Manager_ID
`MANG957`	Aman Kumar	MANAGER	`MANG957` (self)
`FOED452`	Sheela Reddy	FOOD MANAGEMENT	`MANG957`
`FDEW353`	Saideepak Reddy	NOZZEL PERSON	`MANG957`
`DRHD746`	Hima Ullal	COOKING	`FOED452`
`FDNG652`	Hradha Nayar	NOZZEL PERSON	`FDEW353`

In this hierarchy, MANG957 (Aman Kumar) is the station manager. FOED452 (Sheela Reddy) and FDEW353 (Saideepak Reddy) report directly to him. DRHD746 (Hima Ullal) reports to Sheela Reddy, and FDNG652 (Hradha Nayar) reports to Saideepak Reddy — forming a two-level chain below the manager.

Seed Data

The following seven employee records are inserted by create_table.sql:

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, Haryana',
                                                     'animesh@gmail.com',     'OIL380013', NULL),
  ('FDEW353',  'Saideepak Reddy',   'M', 'NOZZEL PERSON',   '2000-06-30', 40000,
   'Lodwadih, Topchanchi, Jharkhand',               'saideepak@outlook.com', 'HPC805103', 'MANG957');

To view the full definition of the salary_check trigger — including the exact condition it enforces — see the Triggers & Functions reference page.

Get Started

Database

User Guide

Advanced

Managing Employees: Staff, Salaries, and Designations

Field Reference

Add an Employee

View Employees

Update an Employee

Delete an Employee

Self-Referential Manager Hierarchy

Seed Data

Build docs developers (and LLMs) love

Get Started

Database

User Guide

Advanced

Documentation Index

​Field Reference

​Add an Employee

​View Employees

​Update an Employee

​Delete an Employee

​Self-Referential Manager Hierarchy

​Seed Data

Build docs developers (and LLMs) love

Field Reference

Add an Employee

View Employees

Update an Employee

Delete an Employee

Self-Referential Manager Hierarchy

Seed Data