Imagine You Work For An Independent Grocery Store Wit 545079

Imagine you work for an independent grocery store with 20 employees

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. Create a relational database table in Microsoft® Excel® for the grocery store using the provided data points. Apply the universal rules (unique primary keys, unique column names, no duplicate rows) to your table. Include all necessary null values.

Display in the table the requested database queries: INSERT INTO Account: VALUES (188820, ‘Wendall Glass’, $12/h); UPDATE Account: Set salary = $12/h WHERE ID = 128872; DELETE FROM Account WHERE ID = 244332; Calculate the grocery store’s SUM salary and the AVG salary per position. Data Points:

  • Blanche Dishner – 438927 - Manager - $25/h
  • 372991 – Night crew - $17/h
  • Luciano Sloney – 300126 – Night crew - $17/h
  • Dorris Goodsell – Cashier - $15/h
  • Sybil Fain – 119325 – Clerk - $11/h
  • Shaun McNeil – 200743 – Cashier - $13/h
  • Olen Wigley – 222345 – Cashier - $14/h
  • Dovie Seawood – 109321 – Clerk - $12/h
  • Magdalen Mullaney – 256921 – Cashier
  • Salena Souza – 185549 – Clerk - $12/h
  • Paige Alvarez - 132943 – Clerk - $11/h
  • Cassy Lundin – 391285 – Night crew
  • Tajuana Stoval - 491621 – Manager – $23/h
  • Joaquina Piasecki – 185392 – Clerk - $13/h
  • Janiece Abelson – 244332 – Cashier - $13/h
  • Rogelio Peppler – 128872 – Clerk - $11/h
  • Fatima Haymaker – 100321 - $13/h
  • Rico Fairbank – 318821 – Night crew - $16/h
  • Trey Peloquin – 488326 – Manager - $24/h
  • Paulene Thibeau – 193931 – Clerk - $12/h

Paper For Above instruction

Creating a relational database for an independent grocery store involves structuring employee data to efficiently manage and analyze workforce information. Utilizing Microsoft Excel® as the platform, a well-designed table must be implemented that adheres to fundamental database principles: each employee should have a unique primary key, column names must be distinct, duplicate rows avoided, and null values appropriately used to account for missing data. This approach ensures data integrity, facilitates accurate queries, and supports effective decision-making.

To start, a database table named "Employees" will be constructed with columns for Employee ID, Name, Position, and Salary. The Employee ID serves as the primary key, ensuring each record is uniquely identifiable. All column names are unique identifiers, and the data set is entered without duplication, consistent with database normalization rules.

The initial dataset, consisting of 20 employees with their respective details, is transcribed into the Excel table. Missing data for certain employees, such as missing salaries, are represented with null or blank cells. This prevents misinterpretation of data and maintains compliance with database standards.

Sample data entries include:

  • Blanche Dishner, ID: 438927, Position: Manager, Salary: $25/h
  • Luciano Sloney, ID: 300126, Position: Night crew, Salary: $17/h
  • Dorris Goodsell, ID: missing if undefined, Position: Cashier, Salary: $15/h

In addition to storing and viewing data, the database must support specific SQL operations. These are simulated through Excel's capabilities since Excel does not natively process SQL commands, yet the queries can be conceptually represented or executed via VBA or external tools.

The insertion command: INSERT INTO Account VALUES (188820, 'Wendall Glass', $12/h) adds a new employee record for Wendall Glass. The update command: SET salary = $12/h WHERE ID = 128872 modifies the salary of the employee with ID 128872. The delete command: DELETE FROM Account WHERE ID = 244332 removes the employee with ID 244332 from the dataset.

Calculating the sum and average salaries involves summing all salary entries (converting the hourly pay rates into numeric values when necessary) and computing averages per position category. These calculations are performed using Excel formulas such as SUM and AVERAGE, grouped by position, to support payroll analysis and resource planning.

In conclusion, constructing this relational database in Excel provides a practical, accessible solution for the grocery store to efficiently manage employee data. Proper application of database principles, combined with targeted queries and calculations, allows the store to maintain accurate records, incorporate updates seamlessly, and derive valuable insights regarding their workforce compensation structure.

References

  • Connolly, T., & Begg, C. (2015). Database Systems (6th ed.). Pearson.
  • Rob, P., & Coronel, C. (2007). Database Systems: Design, Implementation, and Management. Course Technology.
  • Coronel, C., & Morris, S. (2015). Database Systems: Design, Implementation, & Management. Cengage Learning.
  • Hoffer, J. A., Ramesh, V., & Topi, H. (2016). Modern Database Management. Pearson.
  • Silberschatz, A., Korth, H. F., & Sudarshan, S. (2010). Database System Concepts. McGraw-Hill Education.

Related Assignments