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.

This assignment is due on April 3rd at 11:55PM.

Late Policy:

• It is expected that students budget enough time to properly submit their assignments via OWL

and allow for any unforeseen technological issues. Students are expected to regularly backup their assignments and submit well before any deadline.

• Each student will be given four “late coupons” in total for the semester that may be used to submit an assignment one day late per coupon (up to a maximum of 3 days late per assignment).

• Each coupon is valid for a 24 period (11:55 pm to 11:55 pm).

• Late coupons will be applied automatically when an assignment is submitted late and tracked in the OWL gradebook.

• If a student has no late coupons remaining or submits an assignment more than 3 days late, a zero grade will be given for the assignment (no partial late marks will be given).

• Late coupons must be used before special circumstances are considered.

• After all late coupons are used, no extensions will be given for assignments except for in the event of serious medical or compassionate grounds. A student must follow the procedure for Academic Accommodation for Medical Illness as given in this document.

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

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 assume that our business is holding a company retreat for all

employees located in London, Ontario. As such you would like to generate a list of all

employees currently living in London, Ontario so you can invite them to the retreat. Create a   SQL query that will produce a list of employees with a city of London and a Province of ON. ). Include only the employee’s First Name, Last Name, Address, Postal Code, Phone Number, and EMail. Order the results by the employee’s Last Name in alphabetical order (A to Z).

2)  You just realized that you forgot to add yourself as an employee. Create a SQL statement that will update a record in the EMPLOYEE table with the Employee ID of 1 such that their First Name, Last Name, and EMail address are updated with your real name and UWO e-mail address. You should not change any other fields or records in the table.

3)  Your business is starting a new PROJECT named “CRM Development” to create a new

customer relationship management system for your business. Write a SQL statement that will insert a new record in the PROJECT table with the Project Name “CRM Development”, a

Description of “Creating a new customer relationship management system”, the StartDate of April 12th, 2024, and no EndDate given (do not give a value for this field).

Hint 1: There is a space in the field name “Project Name”, you will have to handle this properly in your SQL statement.

Hint 2: ProjectID is of type AutoNumber in the table PROJECT and is the primary key.

4)  You are looking for an existing employee to supervise the new “CRM Development” project.

Create a SQL query to generate a list of all EMPLOYEE’s who have a Job Title of Supervisor   in the POSITION table. Include in the result the employee’s First Name, Last Name, Hire Date, Job Title, and SIN. Sort the results by Hire Date newest to oldest (the most recent Hire Date should be listed first).

Hint 1: this query will require an INNER JOIN.

Hint 2: the field “Hire Date” has a space in it.

5)  HR would like you to make a list of all Employees who have an unapproved ABSENCE (i.e.

have a No value for Approved in the ABSENCE table) that occurred between 2020-01-01 and 2024-04-12 (inclusive). Write a SQL query that will return such employees and include their

First Name, Last Name, Job Title (from POSITION), Absence Reason (from ABSENCE), and Absence Date. Use the default sorting (do not sort the results).

Hint: This query will require two INNER JOINS.

6)  Your IT department is working on migrating to a new e-mail provider and they require a list of all employees with an e-mail address ending in “@example.com” who are assigned to the

Product Line Expansion project. Your SQL query must return the employee's EMail, the

employee's Role in the project (from the employee_projects table), and the employee's

Phone Number. Your query must use the Project’s Name (i.e. “Product Line Expansion”) in the WHERE clause and not the ProjectID (assume that the ProjectID can change). Use the default sorting (do not sort the results).

Hint: This query will require two INNER JOINS.

7)  Your accounting department is auditing the TIME_LOGs of your employees with log entries

with 6.5 or more Total Hours (as per the TIME_LOG table) OR have a Normal Hourly Rate

over $20 (as per the POSITION table). Write an SQL query to create a list of these employees including their SIN, Job Title (from POSITION), Log Date (from TIME_LOG), Total Hours (from TIME_LOG), and Over Time (from TIME_LOG). Sort the results by Total Hours from smallest   amount to largest amount.

Hint: This query will require two INNER JOINS.

8)  Your employees working in the “N6A” area code region are interested in carpooling to work.

Write an SQL query that will return the number of employees living in the area code starting   with “N6A”. This can be any postal code that starts with “N6A” including ones not shown in the screenshot. 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.

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

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-mailto 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) You would like to generate a list of your EMPLOYEEs and their assigned IT ASSETS. Using the Microsoft Access Query Design Tool, create a query that will return a list of IT ASSETS   which includes: the EMPLOYEE’s FirstName, LastName, Phone, EMail, Item Type,

Description, and Condition but only for ASSETS that have not yet been Decommissioned

(have a No for Decommissioned in the ASSETS table). Do not include any other fields in the result (i.e. Decommissioned and Issue Date should not be shown in the result). Order the list by the ASSET’s Issue Date in ascending order.

B) Your IT department is creating a list of devices that will need to be replaced. Create a query   using the Microsoft Access Query Design Tool that will return a list of all ASSETS that have a condition score under but not including 3 OR have an issue date before or equal to 2019- 01-01. Include the following fields: Employee’s First Name, Last Name, and EMail as well as  the ASSET’s AssetID, ItemType, Purchase Price, and Notes. Sort the results by Condition in  descending order.

C) Your business keeps a list of department heads on their website including a short biography for each department. This page has not been updated for some time and you would like to update it. To assist you with writing these, create a query using the Microsoft Access Query Design Tool that will return a list of all department heads (based on the values of DeaprtmentHeadID in DEPARTMENTS) who have a Date of Hire between 2019-01-01 and   2024-04-12 and are marked as Active in the EMPLOYEE table (have a Yes value for Active). This list should include only the EMPLOYEE’s First Name, Last Name, Degree, EMail, City,   and PersonalWebsite as well as the DEPARTMENT’s Name. Region, and Type. Sort the results by the Employee’s Date of Hire from newest employee to oldest employee.

D) Your IT department is updating their infrastructure in the Riverside Tower office building. You

need to create a list of all employees with offices in this building. Office Numbers in the

Riverside Tower building all start with the letters “RT” in the employee_assigned_to table. For   each employee in the list include the employee’s First Name, Last Name, EMail, Phone, Office  Number (from employee_assigned_to) and their department name (from DEPARTMENT). Sort the results by Department ID in ascending order but do not include Department ID in the result. It is ok if employees are listed twice if they have multiple offices in the Riverside Tower building. There is an important hinton the next page.

Hint: By default, when you add these tables to the query design tool, it will include both the relationship between EMPLOYEE and DEPARTMENT as well as the relationship between DEPARTMENT and employee_assigned_to. You only want one of these relationships. Think carefully about what each relationship means, and which ones should be included in this query. Delete any relationships in the Query Design tool that do not belong (this only deletes the relationship from the query and not from the Relationship Tool).

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 March 31st 2024 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 have noticed a large amount of bandwidth being used by http requests to

technologyforum87.com and suspect that this site could be malicious. You would like to know how many times this site has been accessed according to the WEB_TRAFFIC log to see if banning the domain is viable. Create a query using the Microsoft Access Query Design Tool that will count the number of records in the WEB_TRAFFIC table for the domain technologyforum87.com. This query should only return one record and one field that contains the number of records for technologyforum87.com. This one field should be renamed to NumAccessed.

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.

(example:

I agree with the statement “Companies should have policies to automatically refresh (replace) devices every 2-3 years” because ...

Our company policy for refreshing devices is to ...)

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