Pricing Table

Lab Assignment - General Instructions

1. The purpose of this assignment is to get some hands-on experience and gain confidence in the
   basics of setting up data base tables, how to query those tables, and make reports. One of the
   easiest and most popular database software is Microsoft Access, which is what we will use to do
   this assignment.
2. The assignment needs to be done with Microsoft Access Database software only. You may
   use either the Office 2007 or the Office 2010 version of Access software.
3. Submitting Instructions: After you have completed the different parts of the assignment in
   Microsoft Access, you should have a single ".mdb" file or an ".accdb" file which, when you
   open, would show the following: (1) tables as part of the answers for part a and b; (2) queries for
   parts c and d. As usual, use the Blackboard assignment submission feature to submit to the
   assignment drop box.
4. You should start working on the assignments way ahead of the due date, so ample time is
   available to seek and obtain the necessary help, do additional work, etc. Do not wait till the last
   week or two to do this assignment, unless you are proficient in Access.
   Access Lab - Additional hints.
5. This problem is not hard if you understand what the problem is asking. So first read and
   understand the problem well.
6. For parts a and b, if you follow all the steps in the question you can make the tables very
   easily and populate them with the required data.
7. Part c is straight forward. But some students get “type mismatch” error when they try to join
   tables on columns that do not match, such as for example, “course” with “technical.”
8. Here are two big hints for part c:
   a. You must “join” the Pricing Table and Course Table on a common column.
   b. The common column on which you should join the tables is “technical.”
9. For part d also, I am giving two hints:
   a. You need to calculate the student per day cost. In other words what will each course
   cost the student per day. The student per day cost can be calculated by taking into
   account the Price per Day, Duration and Capacity. The resultant table should contain two
   columns: the course name and student per day cost.
   b. Some students get confused because it says to schedule two half day classes in one
   day. To help them further, here is the formula (another BIG hint I am giving away!):
   Student per day cost:[Price per day] * [Duration] / [Capacity]
10. One mistake we see in some submissions is that students make the resultant table and then
    simply query it. So, for students to do well on parts c and d, they should know how queries work
    in Access and how to join tables.
    There! I have almost done the problem for you!!! Well, not quite. These hints and suggestions
    will only be useful if you have read and understood the problem and have made an attempt to do
    it, and you have done the online tutorial on Access carefully, which explains everything you
    need to know – trust me – on how to make tables, how to populate them, how to query them,
    how to join them etc.

Complete the following exercises as individual or group projects that apply chapter concepts to real-world businesses. Course Table
You have the responsibility for managing technical training classes within your organization. These classes fall into two general types: highly technical training and end-user training. Software engineers sign up for the former, and administrative staff sign up for the latter. Your supervisor measures your effectiveness in part according to the average cost per training hour and type of training. In short, your supervisor expects the best training for the least cost. To meet this need, you have negotiated an exclusive on-site training contract with Hands-On Technology Transfer (1101'1) Inc. (www.traininghott.com), a high-quality technical training provider. Your negotiated rates are reproduced below in the pricing table. A separate table contains a sample list of courses you routinely make available for your organization. a. Using these data, design and populate a table that includes basic training rate information. Designate the •echnicar field type as 'Yes/No" (Boolean). b. Using these data, design and populate a course table. Designate the CourselD field as a 'Primary Key" and allow your database to automatically generate a value for this field. Designate the "Technical" field type as "Yes/No" (Boolean). c. Prepare a query that lists each course name and its cost per day of training. d. Prepare a query that lists the cost per student for each class. Assume maximum capacity and that you will schedule two half-day classes on the same day to take full advantage of 11()TI"s per-day pricing schedule.
Technical Yes No
Pricing Table Price per Day S2,680 S2,144
°Wacky.
15
30
Course 11) Course Name 1 ASP Programming 2 nu. Programming 3 PHP Programming 4 Microsoft Word-Advanced S Microsoft Excel-Advanced
Duration Technical 5 Yes 5 Yes 4 Yes .5 No .5 No

2. Training-Cost Management I laving determined the cost per student for each of the classes in the previous problem, you now must carefully manage class registration. Because you pay the same flat rates no matter how many students attend (up to capacity), you want to do all you can to ensure maxi-mum attendance. Your training provider, I lands-On Technology Transfer Inc., requires two weeks' notice in the event that you need to reschedule a class. You should make sure your classes are at least two-thirds full before this deadline. You should also make sure you send timely reminders m all attendees so that they do not forget to show up. Use the database you created in Problem Ito perform the following activities: a. Using the information provided in the sample below, add a course schedule table to your training data-base. Designate the SchedulelD field as a "Primary Key" and allow your database program to generate a value for this field automatically. Slake the CourselD field a number field and the StanDate field a date field. b. Using the information provided in the sample below, add a class roster table to your training database. Make the Schedulell) field a number field. Slake the Reminder and Confirmed fields both "Yes/No" (Boolean) fields.