SQL Assignment #1
Assignment Information
DUE: 11:59 PM on Friday, October 30 into the D2L drop box
PURPOSE: Become familiar with 1) relational tables within a database, 2) creating tables and relationships within MySQL, and 3) creating queries using SQL language.
FILES/ MATERIALS NEEDED:
• Teleworks.xls file from D2L
• Access to the teleworks schema through MySQL in VDI
• Microsoft Word answer sheet from D2L
DELIVERABLES TO SUBMIT:
• MySQL Workbench File (firstInitial.lastName-assignment1.mwb)
• Microsoft Word answer sheet (firstInitial.lastName-TeleworksAnswers.docx)
Part I
FILES/ MATERIALS NEEDED:
• Teleworks.xls file from D2L
• Access to MySQL Workbench in VDI
DELIVERABLE TO SUBMIT:
• MySQL Workbench File (firstInitial.lastName-assignment1.mwb)
SCENARIO:
A manufacturing company, Teleworks, has been a leader in the cellphone market for the last 10 years. Other firms have imitated its product with some degree of success but Teleworks is dominant in its market since it has been around for a longer time and has a very innovative culture.
Recently, Teleworks began selling a new type of cellphone, which is more durable and has better features. Because the new phone is so advanced, Teleworks expects more calls to the customer support hotline. Thus, it needs a database to record and track customer calls.
Teleworks’ Customer Service Manager currently uses an outdated manual tracking system using Microsoft Excel for recording call center information. However, since it doesn’t support queries, she finds it difficult to answer questions about the state of customer support or to analyze common customer issues. Also, there is some redundant information present, and users are prone to make errors when keying in data because there are no checks on the type of data being keyed in.
On top of her day-to-day duties, the Customer Service Manager has been assigned to a Task Force established to improve Teleworks’ call center processes and systems. She has decided to implement a relational database to track the information.
TASK:
You have been hired to develop the database to support Teleworks’ call center efforts. You have been provided the data Teleworks is using now as a starting point.
Your first task is to create the relational database model based upon the information provided.
Using MySQL Workbench database that customer call center operators can use to enter call center data according to the type of issue, the customer, the consultant assigned, and the call’s priority. Pay attention to the details below:
1. The tables should be created based on the separate worksheets in the Excel file.
– You MUST include your first and middle initials & last name at the beginning of the table names.
– Example: tjdixonCustomers
1.
2. The column names in the Excel document contain spaces and are not formatted in a manner to make performing database queries easy. When adding fields to the tables, be sure to name the fields to not have spaces and use the notation we discussed in class (referred to as camel case).
3. Be sure that the data type for each field is appropriate for the type of data that will be entered. For example, integer type should not be specified if the data to be stored in the field is text.
4. Here are some business rules to help you create the database relationships:
– An issue can only have one customer.
– A customer can have more than one issue.
– Each issue must be assigned to one consultant.
– Each consultant can be assigned to more than one issue.
– An issue can only belong to one category.
– An issue must be assigned only one status code.
– An issue must be assigned only one priority code.
Leverage the Excel document to create tables, columns, relationships, etc. in SQL. You do not need to manually enter all the data values. You are simply creating a relational database model similar to SQL Guided Project 1.
Once you have created all the tables, columns fields, and relationships, save the MySQL Workbench file. You will upload this file to D2L after completion of Part II. It is recommended that you save to OneDrive so that you can access the file regardless of the computer you are using.
Part II
FILES/ MATERIALS NEEDED:
• Access to the teleworks schema through MySQL in VDI
• Microsoft Word answer sheet from D2L
DELIVERABLE TO SUBMIT:
• Microsoft Word completed answer sheet (firstInitial.lastName-TeleworksAnswers.docx) from D2L containing the following:
◦ Your queries copied into the answer sheet.
◦ Screenshots of the query, query results and action output showing the record count from the query for each of the queries. Do not include just the query logic itself.
SCENARIO:
The same manufacturing company from Part I, Teleworks, has created and starting using the database for recording call center information. Now that they have the database with information in it, they need queries created that will give specific information to be analyzed.
TASK:
You have been hired to develop the queries that Teleworks needs for their daily monitoring and operational needs.
Develop the following three queries. Be sure to keep a copy of the SQL logic and screenshots in the answer sheet provided on D2L. You can also save queries as .sql files to open later using the menu option File -> Save Script As.
QUERY 1: Teleworks would like to display all issues that fall under the internet/web category. List the issue ID and issue description. Do not display the category or category ID.
QUERY 2: Which consultants serve customers who live in Beverly Hills, CA? List the consultants’ last names and the customers’ contact name. Do not show the city field or the state field. Do not show redundant records. Order by the consultants’ last names.
QUERY 3: Teleworks wants to identify the customers who are experiencing the greatest number of critical issues. They only want to include issues that are currently open. List the customers’ name, zipcode, phone number, and number of critical issues each customer has open. Order by the number of critical open issues (from greatest to least).
*Please note that the data values in the Teleworks schema in SQL differ from the values in the Teleworks excel file from part 1. Thus, do not attempt to compare your query outputs with the excel file.
Once you have created all three queries, complete the Microsoft Word answer sheet. Save and close the Word file. You can now upload both the MySQL (.mwb) file from Part I and the Microsoft Word (.docx) file from Part II to the D2L dropbox.
Grading Scale
Partial credit may be granted for queries that are close to the correct answer. This will be awarded at the discretion of the instructional team.
Item
Possible Points
Complete submission with both deliverables included. Instructions followed appropriately. Appropriate file types and files are named correctly.
6
Relational Database Model
24
Query One
10
Query Two
10
Query Three
10
Total Possible
60

Please refer to the ITM 209 Syllabus for the statement on Academic Integrity and the Broad Honor Code. Your submission to the D2L dropbox implies that you adhered to these integrity guidelines. Any identified dishonesty will result in a 0 on the assignment.