CS1032
Assignment 6: SQL and Queries
All assignments must be completed individually. Do not share your work or use another student’s work in any way. Assignments are to be completed without the help of a private tutor. If you need assistance with this assignment please post on the course forums, e-mail a TA/instructor, or attend an office/consulting hour.
See OWL Assignment tab for assignment due date and late policy.
You MUST back-up your work. Extensions will not be given for lost or corrupt files. This is especially important if you are using MyVLab (should only be needed for Apple computer users). If you will be using MyVLab, you MUST understand how to transfer files between your local computer and MyVLab before attempting this assignment. Do not save directly to the Z: drive, save to the H: drive and copy your work to the Z: drive only when backing up or you are ready to submit.
For Part 1 you MUST write the SQL by hand. Using any tool to automatically generate the SQL such as the query design tool will result in a 0 grade for this assignment. You must follow the strict formatting guidelines for Part 1 to show that your SQL was created by hand.
Part 1
Situational Setup
It has been several years since your business was launched and it is experiencing tremendous success. You now have departments in Winnipeg, Toronto, and Montreal as well as dedicated teams of employees. To manage these employees, track their time worked, absences, and the projects they are working on, you have created a Human Resources (HR) database (HR_Database.accdb). An ERD of this database is shown below:
The ”FK” beside an attribute name, denotes that this attribute is a foreign key.
The following is a brief description of the tables in this database:
EMPLOYEE: Stores information about your employees. SIN is the employee’s social insurance number, PositionID is the foreign key for the relationship shown between the EMPLOYEE and POSITION tables.
POSITION: This table contains a collection of records which describe the different positions in your business including their Job Title, their Hourly Rate, and if applicable their Overtime Rate.
ABSENCE: This table records employee absences due to illness, lateness, or vacation time. Reason is one of “Illness”, “Late”, “Vacation”, or “Unknown”. Approved is a Yes/No field that denotes if HR approved this absence and Notes is an optional field that allows HR to record additional notes about the absence. EmployeeID is the foreign key for the relationship with the EMPLOYEE table.
TIME_LOG: This table stores the number of hours an employee has worked on a given date and project. LogDate is the date the hours were worked. ProjectID is a foreign key for the relationship between PROJECT and TIME_LOG which denotes the project this work was for. OverTime is a Yes/No field that denotes if this was overtime work.
PROJECT: This table contains a record for each project your business currently has employees working on. StartDate is the date this project started and EndDate is the date the project ended (or will end) but only if known.
employee_projects: This relationship (junction) table represents the many-to-many relationship between PROJECTs and EMPLOYEEs. The Role field describes what the employee’s role is in this project (it may be different for each project the employee is assigned to and is not necessarily the same as their JobTitle).
You can find a copy of this database, named HR_Database.accdb, attached to the assignment on OWL. If when opening the file, you get an error about the content being blocked, please follow the instructions in the Trusting Microsoft Access Databases.pdf document to trust the file.
Note that on the final exam you will be expected to write SQL without having a database to test it in. It is recommended that you try writing the SQL in this part without the database first and then using the database after to check your work. Don’t forget that everything for part 1 must be submitted as a word document, anything in a database for part 1 will not be marked.
Example Data
The following screenshot on the next page shows an example of the data that might be contained in these tables. Keep in mind that your SQL queries must work for any valid data in the database and not just the values shown.
Hint: You can tell the difference between Text types and Number types by how the values are justified. Numbers are right justified, and text is left justified (just like in Excel). Check boxes are Yes/No type, and dates are Date/Time type.
SQL Statements
For this part you must create one single SQL statement for each of the following questions. Your SQL statement must be written in a Microsoft Word document by hand and not generated using a tool such as the Query Design Tool. The SQL statements in your Word document must follow the strict formatting guidelines described in the next section. A zero mark will be given for any statement that is created using the Query Design Tool or does not follow the formatting guidelines.
1) The first employee in the EMPLOYEE table is a place holder named “John Doe” and has the Employee ID of 1. You would like to replace this employee with yourself. Write a SQL query that will update the record for the employee with the Employee ID of 1, such that their first and last name are changed to your first and last name. Your query should also update their e-mail to your real @uwo.ca e-mail address. Do not alter any other fields in the record.
2) As part of calculating employee benefits you need to create a listing of all employees hired in the years 2018 to 2019. That is any employee with a Hire Date between January 1st 2018 and December 31st 2019 (inclusive). Write an SQL query that returns a list of employees with hire dates in this range and ensure the result is sorted by Hire Date from oldest hire to newest hire. Only include the fields FirstName, LastName, Hire Date, SIN, and EMail in the result.
Hint: There is a space in the field name “Hire Date”, you will have to handle this properly in your SQL statement.
3) Your business is considering hiring an employee in a new position, titled “Social Media Coordinator”, who will manage all of your business’s social media accounts. Before starting the hiring process, you would like to add this new position to your database. Write an SQL query that will insert a new record into the POSITION table which the JobTitle of “Social Media Coordinator”, a NormalHourlyRate of $18.62 per hour, and an OvertimeHourlyRate of $27.93 per hour. The PositionID should be set automatically by Access and not specified in your query (as it is an AutoNumber type).
4) You have noticed that a number of your employees live in close proximity and thought it might be a good idea to start a carpool. Write an SQL query that will create a list of any employee that lives on Elm St or who lives on Maple Ave. That is, include anyone with an address ending in “Maple Ave” or “Elm St”. Only include the fields FirstName, LastName, PostalCode, Province, and Address in the result. Sort the results by LastName in reverse alphabetical order.
5) HR would like you to make a list of all Employees who have an unapproved ABSENCE (i.e. that have a No value for Approved in the ABSENCE table). Write an SQL statement that will return only the following fields for employees with unapproved absences: EmployeeID, FirstName, LastName, Reason, AbsenceDate, and Notes. Use the default sorting (do not sort the results).
Hint 1: this query will require an INNER JOIN.
Hint 2: An employee may be listed multiple times if they have multiple unapproved absences.
6) Your accounting department is auditing your employees’ overtime hours. They need a list of all employees who have more than 6 Total Hours (as per the TIME_LOG table) that were marked as OverTime (i.e. have a Yes value for OverTime in the TIME_LOG table). Only employees that meet both criteria should be included. In the result include only the fields LogDate, TotalHours, the Employee’s First and Last Names, and from the POSITION table their JobTitle and OvertimeHourlyRate. Sort the results by TotalHours from largest number of hours to fewest.
Hint: This query will require two INNER JOINS.
7) Your IT department is working on migrating to a new e-mail provider and they require an estimate of how many employees will need to be transferred. You have decided to start with a limited pilot program involving only the employees that are part of the “Product Line Expansion” project and have an email ending in “@example.com”. Create an SQL query that will return a count of the number of employees that meet these two criteria based on the employee's who are assigned to the “Product Line Expansion” in the employee_projects table. Your query must use the Project’s Name (i.e. “Sustainability Initiative”) in the WHERE clause and not the ProjectID (assume that the ProjectID can change).
Hint 1: This query will require two INNER JOINS.
Hint 2: Your query should only return a single record containing one field with the number of employees. Your query should not return a list of employees.
Formatting Guidelines
At the top of your word document, you must have your full name, student number, and username.
Each SQL statement must be clearly labeled with a number that identifies which question it is for from the last section.
The format (how the SQL statements are written) MUST match the style. shown in the tutorial slides and video. Each SQL reserved word MUST appear on their own line and in capital letters in the document. Each SQL Statement MUST be indented as shown below. You will be given a zero mark for the question otherwise.
Example:
SELECT
something
FROM
( somewhere
INNER JOIN
somewhere else ON some condition )
WHERE
some condition is true
This is non-optional. You MUST use this standard.
You MUST write the SQL without the use or aid of any electronic method.
For example: You can NOT use MS Access Query Builder to create the SQL graphically and then copy or type in the result to your Word document. You will be given a zero grade on this part if the SQL is not written by hand.
Save your SQL statements in the Word file named "youraccountname_sql.docx” where youraccountname is your western username and attach the file to your submission alongside the files from the other parts of this assignment. Your submission must be a word file for this part and not a database. Only properly formatted Word files will be marked for Part 1.
Part 2
Situational Setup
With the expansion of your business, a problem has slowly started to develop as you have acquired more employees. Each of your employees is assigned zero or more computing devices (smart phones, tablets, desktop computers, laptops, etc.) but your IT department has begun having trouble tracking who currently has what device and when these devices need to be replaced as they are at the end of their life cycle.
To resolve this, you have instructed your IT department to create a simple Microsoft Access database to keep track of your technology assets, which employee has which device, when they were issued the device, what departments each employee belongs to, and what websites the device has accessed (to ensure your employees are not viewing anything inappropriate at work such as cooking recipes, travel blogs, and adorable cat videos). You now wish to use this database and your SQL knowledge to query the database and obtain some data about your business.
Query Design Tool
For Part 2 of this assignment, you must use the provided IT_Database.accdb database and create queries graphically using the Microsoft Access Query Design Tool. You are not required to follow the formatting rules of Part 1 or write the SQL. You should save your queries directly in the database and not in a word document for this part.
Download a fresh copy of the IT_Database.accdb the databases attached to Assignment 6 on OWL. Open it and explore the database. Take a look at the tables, the fields and data they contain, and the relationships shown in the relationship tool. Each field should be documented with a description that describes the field and the information it stores.
If Access is giving an error message about the content of the file being blocked or locked, please follow the steps in the Trusting Microsoft Access Databases.pdf document to trust and unlock the file.
Once you have a good understanding of the database, manually change the first and last name of the employee with an ID of 0001 to your first and last name. Also update their e-mail to your Western e-mail address ending in @uwo.ca. The other values in this record should remain the same. You can do this by hand, you do not have to use a query or SQL statement.
Create a query using the Graphical Query Design Tool in the Create tab in MS Access for each of the following. (You MUST name each query object using the names: Query A, Query B, Query C, Query D, Query E, Query F, and Query G respectively).
A) Your IT department is considering replacing any devices issued on or before 2019-01-01 with a condition score below 4. Using the Graphical Query Design Tool, create a query that lists all ASSETS issued on or before 2019-01-01 (inclusive), that have a Condtion score below 4 (exclusive), and are not already Decommissioned (i.e. have a No value for the Decommissioned field). Only include the fields Description, Manufacturer, Condition, IssueDate, and Notes. Sort the results by IssueDate from the oldest issue date to the newest issue date.
B) A new vulnerability has been discovered in the Virtual Private Network (VPN) software your business is using. Your IT department needs a list of all employees and devices with VPN access so they can update the software. Using the Graphical Query Design Tool, create a query that lists all ASSETS that have not been Decommissioned and have VPN access (have a Yes value for VPN in the ASSETS table). Include the following fields in the result AssetID, ItemType, and Description (all from ASSETS) as well as information about the employee it was assigned to including Last and First Name, Phone number, and Email (from EMPLOYEE). Sort the result by the employee’s date of hire (oldest dates of hire first) but do not include the date of hire in the result.
C) Your HR department would like to create a directory that contains the contact information of the employees for the IT and Admin departments. Using the Graphical Query Design Tool, create a query that lists all employees of the IT or the Admin department (based on the Type field in the DEPARTMENTS table). Include the following fields in the result: Department Name, Department Region, the Employees Role and Office number in the department (from the employee_assigned_to table), and the Employee’s First and Last Name, Phone number, Email, City, and HomeAddress. Sort the results by the Department Name in alphabetical order.
D) You would like to know who is accessing blocked websites in your business. Create a query that will list all WEB_TRAFFIC for any website listed in the WEBSITE_BLOCK_LIST table. In this list include the following fields: the FirstName and LastName of the Employee who is assigned the ASSET, the ASSET’s AssetID and its Description, and the LogID, DateAccessed, Domain, and FullURL from WEB_TRAFFIC. Only include employees who are active (have a Yes value for the Active field in the EMPLOYEE table). Sort the results by the DateAccessed field from the WEB_TRAFFIC table such that newest log entries are first.
Hint: Remember that joining tables creates a union of the records. This means if you join the tables WEB_TRAFFIC and WEBSITE_BLOCK_LIST on Domain, only the records common to both tables will be included (this is what we want in this case as we only want blocked domains).
E) You would like to know if it is time to update your WEBSITE_BLOCK_LIST to remove expired entries. Using the Graphical Query Design Tool, create a query that counts the number of records with an ExpiryDate before July 15th, 2024 (exclusive). This query should only return one record and one field that contains the number of expired records. This one field should be renamed to NumExpired.
Make sure you save each query in the database and rename the file to yourusername_IT_Database.accdb where yourusername is your western username and submit the file through OWL with the files for the other parts.
Part 3: Information Systems Questions regarding your Company
Create an MS Word document and complete the following questions pertaining to the business you described in Assignment 1. Use the same format for the word document as in past assignments.
Each answer must be comprehensive (more than one sentence). Each answer requires at least four sentences. The entire part 3 should be at least 400 words. It is expected that some thought and explanation is included in this section.
Question 1
For this question, assume that your business has a large Microsoft Access Database which includes all relevant information pertaining to your business. What kinds of reports and queries would you be required to run routinely to effectively run your business?
Identify a report or query for two of the following frequencies (you must suggest two reports/queries using two of the frequencies below):
- A report you would want to run daily or weekly
- A report you would want to run monthly or quarterly
- A report you would want to run annually
For each report/query, you must state the following:
- If this will be a report (created with the Access Report tool) or if it will be query (created with the Query Design Tool or a manually entered SQL statement).
- The purpose of the report/query (how will it be used)
- The data you will require for the report/query (be specific on fields, etc) as this must relate back to your specific company.
You are not required to actually make the reports/queries you pick for this question. Simply describe in your own words each of the two reports and/or queries you are suggesting. Only your written description is required.
Question 2
Explain whether you agree or disagree with the following statement and why. Also explain what your business’s policy would be for refreshing devices.
Companies should have policies to automatically refresh (replace) devices every 2-3 years.
For full marks, you must fully address the question and explain your answer and your business policy. Simply stating “I agree” or “I disagree” without elaborating will not be worth any marks.
Once you have completed both questions save the word document as yourusername_yourcompanyname_A6.docx where yourusername is your western username and yourcompanyname is the name of your company from the past 5 assignments.