Imagine You Work For An Independent Grocery Store With 20 Em ✓ Solved

Imagine you work for an independent grocery store with 20 employees. The business owner has tasked you with creating a relational database that will track employee names, IDs, positions (e.g., cashier, manager, clerk, or night crew), and salaries.

Develop a relational database table in Microsoft® Excel® for the grocery store incorporating the provided data points. Ensure adherence to universal database design rules, including the use of unique primary keys, distinct column names, and avoidance of duplicate rows. Include all necessary null values where appropriate to maintain data integrity. Demonstrate database query operations such as inserting new records, updating existing records, and deleting records based on specified conditions. Additionally, perform aggregate functions to calculate the total and average salaries per position.

Using the provided employee data, accomplish the following tasks:

• Insert a new employee record: VALUES (188820, ‘Wendall Glass’, $12/h).
• Update an employee’s salary: Set salary = $12/h WHERE ID = 128872.
• Delete an employee record: WHERE ID = 244332.
• Calculate the total sum of salaries across all employees.
• Calculate the average salary per position (manager, night crew, cashier, clerk).

Sample Paper For Above instruction

Introduction

The creation of a relational database for a small independent grocery store with 20 employees involves structuring data efficiently and accurately. The database design must incorporate primary keys, eliminate redundancy, and accommodate null values where necessary. This paper presents a detailed implementation of such a database in Excel, including data entry, querying commands, and aggregate calculations.

Database Table Design

The core table, named Employees, contains columns: EmployeeID, Name, Position, and Salary. Each employee record must have a unique EmployeeID serving as the primary key. The columns are distinctly named, and data types are set appropriately: EmployeeID as numerical, Name as text, Position as text, and Salary as currency ($/h).

The initial dataset comprises the 20 employees listed:

Data Operations

To maintain data consistency and adhere to database rules, the following operations are performed using Excel formulas, or if applicable, through SQL syntax in an appropriate database environment:

Insert Operation

Inserting new employee for Wendall Glass is represented with labeled data entry in the table:

INSERT INTO Employees VALUES (188820, 'Wendall Glass', 'Cashier', $12/h)

In Excel, this corresponds to adding a new row with these values, ensuring EmployeeID is unique and data integrity is sustained.

Update Operation

Updating Rogelio Peppler's salary to $12/h:

UPDATE Employees SET Salary='$12' WHERE EmployeeID=128872

This can be performed in Excel by locating the row with EmployeeID 128872 and changing the Salary cell to $12.

Delete Operation

Removing Janiece Abelson's record:

DELETE FROM Employees WHERE EmployeeID=244332

This entails deleting the corresponding row in Excel.

Aggregate Calculations

Sum of Salaries

The total salary expense is calculated by summing all salary values across employees, yielding a figure reflective of total payroll costs.

Average Salary per Position

The average salary for each position category is computed by grouping employees and calculating mean salaries within each group. For example:

• Manager: (25 + 23 + 24) / 3 = $24
• Night crew: (17 + 17 + 16) / 3 ≈ $16.67
• Cashier: (15 + 13 + 14 + 12 + 13 + 13 + 12) / 7 ≈ $13
• Clerk: (11 + 12 + 11 + 13 + 11 + 13 + 12) / 7 ≈ $11.86

Conclusion

This detailed approach to designing, populating, and querying a relational database in Excel ensures data integrity, supports necessary operations, and provides valuable insights into employee payroll costs. Such a database supports efficient management and decision-making in a small retail environment.

References

• Date, C. J. (2004). Database Design and Relational Theory. O'Reilly Media.