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 doesnt 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 calls priority. Pay attention to the details below:

• 1. The tables should be created based on the separate worksheets in the Excel file.
     • The table names MUST HAVE YOUR INITIALS & LAST NAME at the beginning of the name. Example: tjdixonCustomers
  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.

In addition, leverage the Excel document containing sample data for the tables to help create the relationships. The tables show the primary and foreign keys as columns in the tables.

Once you have created all the tables, relationships and fields, save the MySQL Workbench file and upload to D2L. It is recommended that you save to OneDrive so that you can access the file from home or the lab if needed.

Category ID Category 1 Hardware/Phone 2 Software/Voicemail 3 Internet/Web IssueCategory Consultants Customers Issues Status Priority + ConsultantID First Name Last Name E-mail Address 1 Tyrone Banks tbanks@teleworks.com 2 Nicole Williams nwilliams@teleworks.com 3 Prescott Martin pmartin@teleworks.com 4 Jameson Lyons jlyons@teleworks.com 5 Deborah Zimmerman dzimmerman@teleworks.com 6 Yoshio Long ylong@teleworks.com 7 Hiram Paul hpaul@teleworks.com 8 Nell Simon nsimon@teleworks.com 9 Dean Conner dconner@teleworks.com 10 Deacon Solis dsolis@teleworks.com 11 Claire Cameron ccameron@teleworks.com Job Title | Business Phone Senior Consultant (490) 177-7685 Senior Consultant (249) 578-8770 Senior Consultant (836) 329-0317 Junior Consultant (732) 297-4554 Junior Consultant (653) 746-9275 Hardware Engineer (691) 215-6715 Hardware Engineer (930) 773-1006 Software Engineer (831) 625-9022 Software Engineer (643) 812-5568 Senior Consultant (332) 237-8327 Hardware Engineer (879) 640-0050 Mobile Phone (419) 523-5750 (640) 133-3203 (997) 266-1131 (745) 720-5002 (835) 674-7730 (546) 511-3490 (560) 595-1719 (712) 608-9289 (255) 547-3298 (637) 299-3143 (436) 678-2363 IssueCategory Consultants Customers Issues Status Priority + Stal DC WA CA NM NY MI WA MI M LA MT Customer 1-1 Contact Name 1 Dana X. Rosario 2 Stewart Z. Hyde 3 Velma B. Mcmillan 4 Olympia Z. Hester 5 Kyla Y. Howe 6 Willa Q. Harrington 7 Veronica R. Page 8 Salvador E. Stafford 9 Xenos G. Compton 10 Tashya S. Chan 11 Sean A. Reilly 12 Maggie N. Davenport 13 Leandra Q. Foreman 14 Megan J. Atkinson 15 Sybil J. Rush 16 Dominic O. Bailey 17 Gary H. Blanchard 18 Oleg J. Knowles 19 Gray V. Clements 20 Wang F. Duke 21 Jamalia A. Byrd 22 Vaughan B. Buckley 23 Amal K. Travis 24 Deacon Q. Hart 25 Maggy F. Miles 26 Taylor V. Dawson 27 Cyrus R. Brooks Address Ap #548-3814 Auctor Street 112-1471 Dui. Avenue Ap #244-6969 Sed Av. 105-134 Nec, Road Ap #830-2087 Tortor St. 2866 Dictum Av. 5052 Eu, Road 957-8312 id, St. P.O. Box 720. 1178 Ullamcorper. SL 7135 Libero. Road 547 Ultrices St. P.O. Box 971.9801 Ut Rd. Ap #143-5881 Vulputate Rd. 4264 Dignissim St. Ap #579-598 Convallis Rd. 1901 Magna. Rd. P.O. Box 896, 8407 Risus. St. 665-9171 Erat. Ave 1975 Uma. Avenue Ap #934-4273 Egestas. Ave 6173 Hendrerit Rd. P.O. Box 622,4358 Orci Rd. P.O. Box 216.7778 Dui St. Ap #202-8507 Quis Av. P.O. Box 430, 2975 Gravida Street 709-6860 Quam Rd. P.O. Box 992, 7355 Non St. City Washington Kirkland Beverly Hills Albuquerque Albany Grosse Pointe Benton Harbor Walla Walla Lansing Traverse City Michigan Butte San Francisco Syosset Seattle Grand Rapids Detroit Lander Mackinac Island Beverly Hills Wherever Ames Santa Fe Anchorage Traverse City Seattle Shelby CA NY WA MI MI Postal Cod 20016 98034 90210 87110 12054 48230 49022 99362 48917 49684 44445 59801 94117 11791 98128 49506 48214 82520 49757 90210 77777 98938 92067 99508 49584 98128 49455 Phone (202) 555-3145 (206) 555-4113 (323) 555-8762 (206) 555-4116 (517) 555-6097 (313) 555-1724 (313) 555-0033 (206) 555-4124 (517) 555-4120 (231) 555-3729 (444) 344-2553 (206) 555-4114 (206) 555-4121 (212) 528-5833 (206) 555-4112 (616) 555-4123 (313) 555-2841 (206) 555-4117 (906) 555-4118 (323) 555-8762 (217) 334-4553 (224) 339-3232 (619) 555-4578 (206) 555-4122 (231) 555-4115 (465) 555-3829 (231) 774-2000 MI CA IN OH CA AK WA MI MI 28 Cherokee B. Melendez 493-8715 Condimentum St. 29 Miriam H. Roberts 336-7939 Accumsan Rd. 30 Hedda E. Lancaster 217-8515 Imperdiet Avenue 31 Alexandra N. Elliott Ap #205-6032 Montes, Rd. East Michigan Birmingham Alexandria Saint Louis MI VA MO 48823 48009 22305 63104 (517) 555-4119 (248) 555-7560 (703) 555-1843 (314) 555-0594 Status ID Category ID Priority ID Customer ID Consultant ID 20 20 won on WNW 30 NNNNNNNNNNNNNN 27 19 20 WNNW 28 10 10 8 27 Issue ID Issue Description 1 Wifi not connecting. 2 Cannot download apps. 3 Alarm clock does not work 4 Ringer does not work. 5 Camera is not working. 6 One key is broken 7 Screen-brightness not adjusting. 8 Cannot delete apps. 9 Locked out of account. 10 Wifi not connecting. 11 Wifi not connecting. 12 Can not access menu on phone. 13 Wifi not connecting. 14 Browser not loading. 15 Browser not loading. 16 Cannot delete apps. 17 Can not dial out of area. 18 Locked out of phone. 19 Alarm clock does not work 20 Browser not loading. 21 Cannot download apps. 22 Browser not loading. 23 Camera is not working. 24 Locked out of phone. 25 Cannot download apps. 26 Phone automatically turning off. 27 Can not access Web from phone. 28 Cannot access Web from phone. 29 Screen is frozen. 30 Can not access Web from phone. 31 Wifi not connecting. 32 Can not access menu on phone. 33 Ringer does not work. 34 Can not access Web from phone. 35 Phone will not turn on. 36 Can not access menu on phone. 37 Cannot delete apps. 38 Screen is frozen. 39 Locked out of phone. 40 Lost password. 41 Lost password. 42 Camera is not working. 43 Will not connecting. K I ssueCategory Date Opened 1/1/19 1/3/19 1/3/19 1/6/19 1/8/19 1/8/19 1/11/19 1/12/19 1/13/19 1/15/19 1/16/19 1/17/19 1/19/19 1/21/19 1/22/19 1/24/19 1/26/19 1/26/19 1/30/19 1/31/19 2/1/19 2/3/19 2/6/19 2/7/19 2/7/19 2/7/19 2/9/19 2/9/19 2/10/19 2/11/19 2/11/19 2/13/19 2/14/19 2/18/19 2/21/19 2/21/19 2/22/19 2/24/19 2/26/19 2/28/19 3/2/19 3/3/19 3/3/19 25 24 GN 17 NNNNNNNNN W ON ONNAN BOONNNNN Consultants Customers Issues Status Priority + StatusID Status 1 Open 2 Closed Consultants Customers issues Status Priority + BE IssueCategory Ready Normal View Sumo PriorityID Priority Level 1 Critical 2 High 3 Moderate 4 Standard 5 Low Consultants Customers Issues Status Priority + + Normal View IssueCategory Ready Category ID Category 1 Hardware/Phone 2 Software/Voicemail 3 Internet/Web IssueCategory Consultants Customers Issues Status Priority + ConsultantID First Name Last Name E-mail Address 1 Tyrone Banks tbanks@teleworks.com 2 Nicole Williams nwilliams@teleworks.com 3 Prescott Martin pmartin@teleworks.com 4 Jameson Lyons jlyons@teleworks.com 5 Deborah Zimmerman dzimmerman@teleworks.com 6 Yoshio Long ylong@teleworks.com 7 Hiram Paul hpaul@teleworks.com 8 Nell Simon nsimon@teleworks.com 9 Dean Conner dconner@teleworks.com 10 Deacon Solis dsolis@teleworks.com 11 Claire Cameron ccameron@teleworks.com Job Title | Business Phone Senior Consultant (490) 177-7685 Senior Consultant (249) 578-8770 Senior Consultant (836) 329-0317 Junior Consultant (732) 297-4554 Junior Consultant (653) 746-9275 Hardware Engineer (691) 215-6715 Hardware Engineer (930) 773-1006 Software Engineer (831) 625-9022 Software Engineer (643) 812-5568 Senior Consultant (332) 237-8327 Hardware Engineer (879) 640-0050 Mobile Phone (419) 523-5750 (640) 133-3203 (997) 266-1131 (745) 720-5002 (835) 674-7730 (546) 511-3490 (560) 595-1719 (712) 608-9289 (255) 547-3298 (637) 299-3143 (436) 678-2363 IssueCategory Consultants Customers Issues Status Priority + Stal DC WA CA NM NY MI WA MI M LA MT Customer 1-1 Contact Name 1 Dana X. Rosario 2 Stewart Z. Hyde 3 Velma B. Mcmillan 4 Olympia Z. Hester 5 Kyla Y. Howe 6 Willa Q. Harrington 7 Veronica R. Page 8 Salvador E. Stafford 9 Xenos G. Compton 10 Tashya S. Chan 11 Sean A. Reilly 12 Maggie N. Davenport 13 Leandra Q. Foreman 14 Megan J. Atkinson 15 Sybil J. Rush 16 Dominic O. Bailey 17 Gary H. Blanchard 18 Oleg J. Knowles 19 Gray V. Clements 20 Wang F. Duke 21 Jamalia A. Byrd 22 Vaughan B. Buckley 23 Amal K. Travis 24 Deacon Q. Hart 25 Maggy F. Miles 26 Taylor V. Dawson 27 Cyrus R. Brooks Address Ap #548-3814 Auctor Street 112-1471 Dui. Avenue Ap #244-6969 Sed Av. 105-134 Nec, Road Ap #830-2087 Tortor St. 2866 Dictum Av. 5052 Eu, Road 957-8312 id, St. P.O. Box 720. 1178 Ullamcorper. SL 7135 Libero. Road 547 Ultrices St. P.O. Box 971.9801 Ut Rd. Ap #143-5881 Vulputate Rd. 4264 Dignissim St. Ap #579-598 Convallis Rd. 1901 Magna. Rd. P.O. Box 896, 8407 Risus. St. 665-9171 Erat. Ave 1975 Uma. Avenue Ap #934-4273 Egestas. Ave 6173 Hendrerit Rd. P.O. Box 622,4358 Orci Rd. P.O. Box 216.7778 Dui St. Ap #202-8507 Quis Av. P.O. Box 430, 2975 Gravida Street 709-6860 Quam Rd. P.O. Box 992, 7355 Non St. City Washington Kirkland Beverly Hills Albuquerque Albany Grosse Pointe Benton Harbor Walla Walla Lansing Traverse City Michigan Butte San Francisco Syosset Seattle Grand Rapids Detroit Lander Mackinac Island Beverly Hills Wherever Ames Santa Fe Anchorage Traverse City Seattle Shelby CA NY WA MI MI Postal Cod 20016 98034 90210 87110 12054 48230 49022 99362 48917 49684 44445 59801 94117 11791 98128 49506 48214 82520 49757 90210 77777 98938 92067 99508 49584 98128 49455 Phone (202) 555-3145 (206) 555-4113 (323) 555-8762 (206) 555-4116 (517) 555-6097 (313) 555-1724 (313) 555-0033 (206) 555-4124 (517) 555-4120 (231) 555-3729 (444) 344-2553 (206) 555-4114 (206) 555-4121 (212) 528-5833 (206) 555-4112 (616) 555-4123 (313) 555-2841 (206) 555-4117 (906) 555-4118 (323) 555-8762 (217) 334-4553 (224) 339-3232 (619) 555-4578 (206) 555-4122 (231) 555-4115 (465) 555-3829 (231) 774-2000 MI CA IN OH CA AK WA MI MI 28 Cherokee B. Melendez 493-8715 Condimentum St. 29 Miriam H. Roberts 336-7939 Accumsan Rd. 30 Hedda E. Lancaster 217-8515 Imperdiet Avenue 31 Alexandra N. Elliott Ap #205-6032 Montes, Rd. East Michigan Birmingham Alexandria Saint Louis MI VA MO 48823 48009 22305 63104 (517) 555-4119 (248) 555-7560 (703) 555-1843 (314) 555-0594 Status ID Category ID Priority ID Customer ID Consultant ID 20 20 won on WNW 30 NNNNNNNNNNNNNN 27 19 20 WNNW 28 10 10 8 27 Issue ID Issue Description 1 Wifi not connecting. 2 Cannot download apps. 3 Alarm clock does not work 4 Ringer does not work. 5 Camera is not working. 6 One key is broken 7 Screen-brightness not adjusting. 8 Cannot delete apps. 9 Locked out of account. 10 Wifi not connecting. 11 Wifi not connecting. 12 Can not access menu on phone. 13 Wifi not connecting. 14 Browser not loading. 15 Browser not loading. 16 Cannot delete apps. 17 Can not dial out of area. 18 Locked out of phone. 19 Alarm clock does not work 20 Browser not loading. 21 Cannot download apps. 22 Browser not loading. 23 Camera is not working. 24 Locked out of phone. 25 Cannot download apps. 26 Phone automatically turning off. 27 Can not access Web from phone. 28 Cannot access Web from phone. 29 Screen is frozen. 30 Can not access Web from phone. 31 Wifi not connecting. 32 Can not access menu on phone. 33 Ringer does not work. 34 Can not access Web from phone. 35 Phone will not turn on. 36 Can not access menu on phone. 37 Cannot delete apps. 38 Screen is frozen. 39 Locked out of phone. 40 Lost password. 41 Lost password. 42 Camera is not working. 43 Will not connecting. K I ssueCategory Date Opened 1/1/19 1/3/19 1/3/19 1/6/19 1/8/19 1/8/19 1/11/19 1/12/19 1/13/19 1/15/19 1/16/19 1/17/19 1/19/19 1/21/19 1/22/19 1/24/19 1/26/19 1/26/19 1/30/19 1/31/19 2/1/19 2/3/19 2/6/19 2/7/19 2/7/19 2/7/19 2/9/19 2/9/19 2/10/19 2/11/19 2/11/19 2/13/19 2/14/19 2/18/19 2/21/19 2/21/19 2/22/19 2/24/19 2/26/19 2/28/19 3/2/19 3/3/19 3/3/19 25 24 GN 17 NNNNNNNNN W ON ONNAN BOONNNNN Consultants Customers Issues Status Priority + StatusID Status 1 Open 2 Closed Consultants Customers issues Status Priority + BE IssueCategory Ready Normal View Sumo PriorityID Priority Level 1 Critical 2 High 3 Moderate 4 Standard 5 Low Consultants Customers Issues Status Priority + + Normal View IssueCategory Ready