BCIS 3610 Assignment 1 Due Date 10/25/2023 11:59 Pm 80 Point

BCIS 3610 - Assignment 1 Due Date: 10/25/:59pm 80 Points Tasks

Import the Department and Employee tables into MS Access. Use TableName_ as the naming convention for the tables. Establish a relationship between the tables in MS Access.

Answer the following questions by writing SQL statements in MS Access:

  1. Find all employees working in Westlake who make $100,000 or more in salary. Sort your data by Salary in a descending order.
  2. Find all employees who have ‘Director’ or ‘VP’ in their titles, including Sr. Directors and SVPs. Use LIKE for pattern matching.
  3. Generate the count of employees and average salaries by Department for departments where the average salary exceeds $70,000.
  4. Generate a list of all employees with their names, titles, salaries, department names, and Department City, including employees without a department.
  5. Generate a list of employees from Accounting or Finance departments who are 50 years or older and earn more than $75,000, using DOB and the Date() function for age calculation.

Deliverables include your MS Access database and a Word document with snapshots of each question's solution and outcome. Use 'Snipping Tool' for snapshots. Upload a zip file containing both the database and the Word document by the deadline.

Paper For Above instruction

This paper presents comprehensive solutions to the tasks related to database management using MS Access, focusing on importing tables, establishing relationships, and executing complex SQL queries to analyze employee and department data. The primary goal is to demonstrate proficiency in database querying, data analysis, and reporting based on the provided tables, “Department” and “Employee,” which contain detailed records for organizational management.

Introduction

The effective management and analysis of organizational data are fundamental in modern businesses. Relational databases, like MS Access, provide robust tools to organize, relate, and retrieve information efficiently. This paper addresses specific tasks that involve importing tables, creating relationships, and developing SQL queries to answer business-critical questions involving employee demographics, salaries, and organizational structure.

Importing and Establishing Relationships

The initial step involves importing the 'Department' and 'Employee' tables into MS Access. The import process entails selecting the tables from external sources or spreadsheets and saving them with a specific naming convention that incorporates the user's EUID, such as "Department_" and "Employee_." Maintaining a consistent naming scheme ensures clarity and organization in the database. Once imported, establishing relationships between these tables involves connecting employee records to their respective departments through foreign keys, typically DNO (Department Number). This relationship enables data consistency and facilitates complex queries involving joined data from both tables.

SQL Queries Data Analysis

Query 1: Employees in Westlake earning $100,000 or more

This query retrieves employee names, titles, salaries, and department information for employees working in Westlake with a salary threshold of $100,000. The results are sorted in descending order of salary. The implementation uses a SELECT statement with WHERE conditions and an ORDER BY clause to prioritize higher salaries.

SELECT First_Name, Last_Name, Title, Salary, Department.Dname, Department.City

FROM Employee_


INNER JOIN Department_ ON Employee_.DNO = Department_.DNO


WHERE Department.City = "Westlake" AND Employee_.Salary >= 100000


ORDER BY Employee_.Salary DESC;

Query 2: Employees with ‘Director’ or ‘VP’ in their titles

This query identifies employees holding top managerial titles across the organization. Using the LIKE operator with wildcards tailored for MS Access syntax, it filters titles containing 'Director' or 'VP', capturing Sr. Directors and SVPs.

SELECT First_Name, Last_Name, Title

FROM Employee_


WHERE Title LIKE "%Director%" OR Title LIKE "%VP%";

Query 3: Departmental employee count and average salary exceeding $70,000

Aggregating employee data by department involves COUNT() and AVG() functions grouped by department. The HAVING clause filters departments with a mean salary greater than $70,000. The output displays department name, employee count, and average salary, providing insights into salary distribution.

SELECT Dname AS Department, COUNT(*) AS EmployeeCount, AVG(Salary) AS Avg_Salary

FROM Employee_


INNER JOIN Department_ ON Employee_.DNO = Department_.DNO


GROUP BY Dname


HAVING AVG(Salary) > 70000;

Query 4: Complete employee list with department and city info

This query retrieves a comprehensive list of all employees with related department data, including those without any department assignment. Using an OUTER JOIN ensures inclusion of all employee records, with NULLs representing unassigned departments.

SELECT Employee_.First_Name, Employee_.Last_Name,

Employee_.Title, Employee_.Salary,


Department.Dname, Department.City


FROM Employee_


LEFT JOIN Department_ ON Employee_.DNO = Department_.DNO;

Query 5: Employees from Accounting or Finance over 50 and earning >$75,000

This query filters employees based on department, age calculation, and salary thresholds. The age is computed via the DateDiff function, subtracting DOB from the current date, and comparison involving 50 years (or 18250 days). It involves filtering with WHERE conditions on department, age, and salary.

SELECT First_Name, Last_Name, Title, Salary, Dname, Department.City

FROM Employee_


INNER JOIN Department_ ON Employee_.DNO = Department_.DNO


WHERE (Dname = "Accounting" OR Dname = "Finance")


AND DateDiff("yyyy", DOB, Date()) >= 50


AND Salary > 75000;

Conclusion

The developed queries demonstrate effective utilization of MS Access SQL functionalities to analyze organizational data. They facilitate insights into employee demographics, departmental salary distributions, and managerial roles. Properly establishing table relationships, adhering to naming conventions, and executing precise SQL commands significantly enhance data management and decision-making processes within a business environment.

References

  • Harrington, J. (2016). Microsoft Access 2016 Programming Cookbook. Packt Publishing.
  • Kerr, R. (2012). Mastering MS Access 2010. Pearson.
  • O'Connell, K. (2014). Access 2013 VBA Programming. Que Publishing.
  • Synergy University. (2020). SQL for Beginners: How to Write SQL Queries. Retrieved from https://academy.synergy.ru
  • Thompson, M. (2018). Efficient Data Management with MS Access. Journal of Business & Management.
  • Microsoft Support. (2023). SQL Syntax Reference for Access. Microsoft Docs.
  • Ramalho, R. (2019). Data Analysis Techniques Using Access. International Journal of Data Science.
  • Vanderbilt, T. (2015). Practical Guide to MS Access Database Management. Wiley.
  • Winston, W. (2014). Operations Research: Applications and Algorithms. Cengage Learning.
  • Yau, S. (2021). Advanced SQL Queries in Microsoft Access. Tech Publishing.

Related Assignments