Reference no: EM134044169

Developing Data Access Solutions

Assessment Item 1 - Tutorial Part B (Individual)

Develop an Python, PyQt5, and MySQL-based Employee Management System that allows users to perform CRUD (Create, Read, Update, Delete) operations and search for employee records.

Task

1) Implement the GUI:

Design the GUI to look and function identically to the Customer Management System, with the following updates:
The table should display the following columns:
EmployeeID
LastName
FirstName
BirthDate
Notes
Input fields for:
LastName: Single-line text input.
FirstName: Single-line text input.
BirthDate: Date picker widget.
Notes: Multi-line text area.
Buttons for actions:
Add Employee
Update Employee
Delete Employee
Search by Last Name
Refresh Table

2) Database Operations:

Perform CRUD operations on the Employees table in the Northwind database using Database Management DBMS operations:
Fetch All Records: Retrieve and display all employee records.
Add a New Employee: Insert a new employee into the database.
Update an Employee: Modify existing employee details.
Delete an Employee: Remove an employee from the database.
Search by Last Name: Filter employee records based on a partial match of the last name.

3) Add Confirmation Dialogs:

Display confirmation messages for Add, Update, Delete, and Search actions.
Example: "Are you sure you want to delete EmployeeID 5?"

4) Input Validation:

Ensure the following fields are mandatory for Add and Update:
LastName
FirstName
BirthDate

5) Requirements:

Database Table: Use the Employees table from the Northwind database with the following schema:
EmployeeID (INT, Primary Key)
LastName (VARCHAR)
FirstName (VARCHAR)
BirthDate (DATE)
Notes (TEXT)

6) Widgets:

Use QDateEdit for the BirthDate field.
Use QTextEdit for the Notes field.

7) Functionality:

The application should replicate the Customer Management System's features but operate on the Employees table.

8) Expected Outcome:

The GUI should match the appearance of the Customer Management System.
The table should display:
EmployeeID, LastName, FirstName, BirthDate, and Notes.
Buttons should perform the corresponding CRUD operations.
A search field should allow filtering by LastName.
All actions should show confirmation dialogs, and validation should prevent incomplete data entry.