Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Assignment 6: SQL and Queries

All assignments must be completed individually. Do not share your work or use another student’s work in anyway. 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 backup your work. Extensions will not be given for lost or corrupted 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.

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 a few 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. 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 POSTION 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 which 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).

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) In this scenario we will pretend that Manitoba (abbreviated as “MB” in the database) just passed a law that all employees working in a manufacturing business must take an online safety course. As such you would like to generate a list of all employees living in Manitoba so that you can ensure they complete this mandatory training. Create a SQL query that will produce a list of employees living in Manitoba (have a “MB” for Province in the EMPLOYEE table). Include only the employee’s FirstName, LastName, City, Address, and Postal Code and no other fields in the result. Order the results by the employee’s Hire Date from newest to oldest (the most recently hired employee should be listed first).

Hint: There is a space in the field name “Hire Date”, you will have to handle this properly in your query.

2) You just realized that you forgot to add yourself as an employee. Create a SQL query that will insert a new record into the EMPLOYEE table. Use your real FirstName and LastName and the PositionID of 1. The EmployeeID should not be provided as it will be filled in by the database (it is an AutoNumber type). You can makeup the values of the remaining fields (please don’t use your real SIN).

3) One of your employees, John Doe, just moved from London to Toronto. Create a SQL query that will update their record such that the City is now “Toronto” and the Address is now “123 Pine St”. You must use the employee’s name (John Doe) and not their EmployeeID or SIN in this query. Note that you may have more than one employee with the first name John (e.g. John Smith) and more than one employee with the last name Doe (e.g. Jane Doe), but for this query we will assume that you only have one employee with the full name John Doe.

4) HR would like you to create an SQL query to generate a list of employees who have unapproved absences. An employee is considered to have an unapproved absence if they have a value of “No” for the Approved field in the ABSENCE table. In this list include the following fields: the employee’s FirstName, LastName, AbsenceDate, Reason, and Notes. Order the results by the AbsenceDate from oldest to newest (the most recent AbsenceDate should be listed last).

Hint: this query will require an INNER JOIN.

5) HR needs to compute the payroll expenses for the “Product Line Expansion” project for 2019. Create an SQL query that creates a list of TIME_LOG entries for the “Product Line Expansion” project but only for log entries between the dates January 1st 2019 and December 31st 2019 based on the LogDate. In the result include the following fields: the employee’s FirstName and LastName, the Project Name, the LogDate, TotalHours, and OverTime. You must use the Project Name (“Product Line Expansion”) in your WHERE clause. Use the default ordering.

Hint: This query will require two INNER JOINS.

6) You would like to know how many of your employees make over $20 per hour or have an overtime rate of $30 or more. Create an SQL query to COUNT how many employees have a NormalHourlyRate of over $20 OR have a OvertimeHourlyRate equal to $30 or more.

Hint 1: This should not return a list of employees, rather it should return a single number.

Hint 2: Remember this is asking for the number or employees that meet these criteria and not the number of positions in your company that do. Will this require joining tables?

7) Your employees working on the “Sustainability Initiative” project are interested in car pooling to work. Write an SQL query that will create a list of employees assigned to the project named “Sustainability Initiative” who live in the City “London” with an address that ends in “Elm St”. Include the employee’s FirstName, LastName, Address, PhoneNumber, and Email. Order the results by Address in descending order.

Hint 1: This query will require two INNER JOINS.

Hint 2: Remember the address must end in “Elm St”. So, values such as “012 Elm St” and “456 Elm St” would count, but values such as “123 Elm St West” or “Elm St 876” would not.

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.

Furthermore, the format (how the SQL statements are written) MUST match the style shown in the notes.

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 for not following these instructions.

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.

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 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 using the Microsoft Access Query Design Tool. You are not required to follow the formatting rules of Part 1 or write the SQL in a word document for Part 2. You should save your queries directly in the database.

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.

Once you have a good understanding of the database, manually change the first and last name of the employee with an ID of 0005 to your first and last name. 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) You would like to generate a list of your department heads and their contact information. Using the Microsoft Access Query Design Tool, create a query that will return a list of department heads which includes: their FirstName, LastName, Phone, EMail, City, HomeAddress, Department Name, Type, and Region. Order the list by Department Name in ascending order.

B) You are considering replacing all devices that have a condition score of 3 or higher (i.e. 3, 4, or 5) or were issued before January 1st 2021. Create a query that will return a list of all ASSETS matching these criteria that have been assigned to your employees and include details about who the device is assigned to. Include the following fields: ItemType, Description, Condition, IssueDate, Purchase Price, Decommissioned, Employee’s FirstName, LastName, and EMail. Sort the results by the IssueDate in descending order.

C) In the last query (Query B) you noticed that some of the devices in the list were already decommissioned. Create a query that references Query B but only includes ASSETs in the list which have not been decommissioned (that have a Yes/checked box for Decommissioned). Include all fields that were included in Query B and keep the ordering the same.

D) You have made several changes to your business’s virtual private network (VPN) used by your departments located in Winnipeg and need a list of client devices to update the VPN configuration on. Create a query that will return a list of all ASSETs used by any department located in Winnipeg which uses a VPN (have a Yes/Checked box in the VPN field). In the result include the ASSET’s AssetID, ItemType, Description, the EMPLOYEE’s OfficeNumber (in the Winnipeg Sales Office), FirstName, LastName, and the DEPARTMENT’s Name. Sort the results by the ASSET’s Purchase Price in descending order.

Hint 1: Think critically about the result you are getting for this query. Are any records being omitted that should be included? If yes, you may have included a relationship in your query design tool that should not be there. You can remove a relationship just for this query by selecting it in the query design tool and clicking the delete key.

Hint 2: Are department heads relevant to this query?

E) 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 if it was using a VPN, and the LogID, DateAccessed, Domain, and FullURL from WEB_TRAFFIC. Only include records for websites on the WEBSITE_BLOCK_LIST which have an ExpiryDate after November 1st 2023 OR have a blank (Null) ExpiryDate. Sort the results by DateAccessed in ascending order.

Hint 1: a criterion of “Is Null” will include blank records for a field.

Hint 2: 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.

F) You suspect that someone may be using your devices when you are not in the office. Create a query that will list the WEB_TRAFFIC for only your devices. In this result include only the ASSET’s AssetID, ItemType, Description, and from WEB_TRAFFIC LogID, DateAccessed, Domain, and FullURL. Use your real FirstName and LastName in the criteria and not your EmployeeID. You can safely assume you are the only employee with this full name. Sort the results by DateAccessed in ascending order.

Hint: Remember, you were asked to manually replace the FirstName and LastName of the Employee with the ID of 005 with your real FirstName and LastName.

G) A vulnerability was recently discovered on all iPhone devices, and you need to generate a list of these devices so that IT can update their software. Create a query that will list all ASSETs with the word “iPhone” anywhere in the description field. This query should work even if new iPhone versions are added to the database. For example, it should include assets with a description of “Apple iPhone 8”, “Apple iPhone XR”, “Apple iPhone 11”, or even combinations that are not yet in the database such as “Dan’s iPhone 73” or “Max’s iPhone XLS”. Do not include any decommissioned devices in this list. In this list include the following fields: AssetID, Description, Notes, and the EmployeeID for the employee to whom it is assigned.

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 a 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.

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.

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.

Submission Instructions:

You must upload and submit, via the CS1032 OWL Site, the following three (3) files:

• yourusername_sql.docx

• yourusername_IT_Database.accdb

• yourusername_yourcompanyname_A6.docx

where yourusername is your western username and yourcompanyname is the name of your company from the past 5 assignments.

It is your responsibility to make sure you submitted the correct files and to regularly backup your work. No extensions will be given for uploading the wrong file or for lost or corrupt files.