NEW YORK STATE FBLA DATABASE DESIGN & APPLICATIONS PRODUCTION TEST 2009

PLEASE DO NOT OPEN THIS TEST UNTIL DIRECTED TO DO SO Test Directions

1. Each printout should include a header or footer containing your name, school, and job number. 2. You will have one hour to complete this test once all directions are read. After the test begins, no help or additional time will be given to you concerning the normal operation of the equipment. However, if your machine fails, call the proctor. 3. Job No. 1 2 3 4 5 6 Task Object Diagram Entity-Relationship Diagram Database and Table Creation Unresolved Query Unresolved Calls Report Technician Workload Report Point Value 25 15 20 20 10 10

To assist you in knowing what to print throughout this test, instructions are included in each job for what you are to print. 4. You are not allowed to bring in any reference materials and you are not allowed to use a calculator. 5. Proofread all work. Your documents will be evaluated by a panel of judges. All decisions of the judges are final. 6. When you have completed the exam, you will need to turn in all materials to the site proctor, including this test and all printoutsincluding those you do not wish judged.

NYS FBLA 2009 DATABASE DESIGN & APPLICATIONS PRODUCTION

7. Remember that you will be required to complete the second portion of the competition, an objective test covering relevant theory, vocabulary, and application knowledge, during the State Leadership Conference.

NYS FBLA 2009 DATABASE DESIGN & APPLICATIONS PRODUCTION

Background Information: The I Need a Tech call center is currently logging its calls by hand-completing a paper template. When a customer calls, the customer service representative creates a help desk ticket on the paper template example shown below. When a technician is assigned and the problem gets resolved, the technician completes the help desk ticket. Because several tickets have been getting lost, the handwriting can be difficult to read, and there is no way of keeping track of work done or customer information, I Need a Tech has hired you to create a database to track help desk calls. Technicians: Technician ID 1 2 3 4 Technician Name Stacey Kim Patrick OConnelly Terrance Bryant Kathleen Pierce Technician Phone 212-555-1000 716-555-2000 315-555-3000 607-555-4000 Technician E-mail skim@ineedatech.com poconelly@ineedatech.com tbryant@ineedatech.com kpierce@ineedatech.com

Current Help Desk Ticket Template:

I Need a Tech Help Desk Ticket

Customer ID: Customer Name: Address:

City: State:

Zip Code: Phone Number:

To Be Completed by Technician Only: Description of Problem: Call Date: Call Time:

Assigned Technician: Date Resolved: Resolution Description:

NYS FBLA 2009 DATABASE DESIGN & APPLICATIONS PRODUCTION

Existing Customer Tickets: Customer ID 1 Frank Hall Customer Name Address City State Zip Code Phone Number 45 Mt. Ryan Street Albany NY 12201 518-5555891 Computer wont recognize new Bluetooth mouse but will recognize the keyboard 1/2/09 8:46 pm Terrance Bryant

2 Cindy Loo 82345 Rt. 36 Corning NY 14830 607-5559835 New monitor displays unreadable words; pictures are OK

3 Bradley Smy 939 Linda Lane Kenmore NY 14217 716-5557604 Printer wont print correct colors

Description of Problem

Call Date Call Time Assigned Technician Date Resolved Resolution Description

12/20/08 6:10 pm Kathleen Pierce 12/31/08 Updated video driver and reinstalled monitor driver

2/1/09 9:01 am Patrick OConnelly 2/2/09 Changed ink cartridges

4 Marsha Stoney 14 Tea Timer Road Brooklyn NY 11210 718-5554428 Computer wont download anything from Internet; Generic Process Host Win32 error message appears 1/22/09 4:58 am Stacey Kim

5 Enrique Rodriquez 543 W. Indy Circle Ogdensburg NY 13669 315-5557111 Trying to connect a game steering wheel but cant find correct type of connection on CPU 2/14/09 11:30 pm Terrance Bryant 2/28/09 Provided customer with 15-pin to 9-pin serial port adapter

6 John Dims, Jr. One Main Street Canisteo NY 14823 607-5550103 Just moved computer to a different room and cant reconnect modem properly 12/29/08 1:15 am Kathleen Pierce

NYS FBLA 2009 DATABASE DESIGN & APPLICATIONS PRODUCTION

Job 1Object Diagram (25 points) Complete the object diagram below. The objects and their attributes can be obtained from the Current Help Desk Ticket Template shown above. Identify appropriate primary and foreign keys for each object by writing PK (Primary Key) and FK (Foreign Key) to the left of the attribute. This job can be either created on a computer or written in the boxes below. Be sure to include this job with the rest of the test problems.

Customer

Call

Technician

Job 2Relationship Diagram (15 points) Complete the relationship diagram below. Fill in the object name in the appropriate rectangles. Add relationship lines between the objects and note the nature of the relationship. This job can be either created on a computer or written in the boxes below. Be sure to include this job with the rest of the test problems.

NYS FBLA 2009 DATABASE DESIGN & APPLICATIONS PRODUCTION

Job 3Database Creation (20 points) 1. Create a new database named Help Desk and create the Customer, Call, and Technician tables using the information you identified in Job 1 to help you add the appropriate fields for each table. Identify appropriate data types for each field. Save each table. 2. Populate the tables with the information for the technicians and the call ticket data in this test. 3. Establish the relationships between tables. 4. Print 3-A: The Customer table. 5. Print 3-B: The Call table. 6. Print 3-C: The Technician table. 7. Print 3-D: The relationship between the tables.

Job 4Unresolved Calls Query (20 points) 1. Management wants a report that will track which help desk call tickets are not resolved in order to follow up with the technician. Create a query that displays the help desk call tickets that are not resolved. Include Customer ID, Customer Name, Customer Phone, Call Date, Call Time, and Is the Problem Resolved in this query. 2. Sort the query results by the Customer ID. 3. Save and name the query Unresolved Calls 4. Print 4: Unresolved Calls query.

Job 5Unresolved Calls Report (10 points) 1. Since queries are not intended to be analyzed and presented, create a report based on the Unresolved Calls query created in Job 4. Include Call Date, Call Time, Customer ID, Customer Name, Phone, and Is the Problem Resolved. 2. Sort the report by the most recent calls. 3. 4. Save and name the report Unresolved Calls. Print 5: Unresolved Calls report.

Job 6Technician Workload Report (10 points) 1. Create a report to help management keep track of the technicians work loads. The report should display the Technician ID, Technician Name, Customer Name, City, Customer Phone, Call Date, and Is the Problem Resolved. 2. Group the report by Technician ID with the Technician Name included in the grouping. 3. 4. Save and name the report Technician Workload. Print 6: Technician Workload report.