------MIS Assignment 2 --- 10 points
Due Date: Oct. 7, 5 p.m. 0.5 point will be deducted for each day of delay in submitting your assignment 2.
10 points
Submission:
1. Download the zipped database BizGame, unzipped it, and renaming it with the following naming
2. Submission Deadline:
Name your database BizGame_NN_LastName_FirstName
Where NN is your section number and should follow by LastName and FirstName
Such as BizGame_01_Chen_Minder
3. You should sumit your assignment 2 by entering the answers to the following questions via the Answer_Entry_Form which you can find under the Form objects in the database. Enter your name under QuestionNum 0.
You can also view all your answer in a data sheet via the Form called Answer_Sheet such as the following:
Answer_Sheet /QueryNum / Answer /
0 / Minder Chen
1 / 125
4. You need to create a query (mostly select statements and one update statement) for each question and store the query with the following convention:
Use QNN_MeaningfulNameOfYourQuery where NN is the question number, 01, 02, 03, 04, etc.
Such as
Q01_Tutor_Count
Q02_Student_Tutored
5. Submit your assignment 2 by using the Blackboard digital drop box.
6. You will receive 0.5 for each question from Q2~11 if you have the correct answer, plus you will l get another 0.2 point for each question if you have the most accurate query.
Business Game Scenerio:
It is the autumn of 2006, the leaves are beginning to fall and you are back to school. Last year, you and a few good friends started up your own tutoring business at your College. It has proven to be a huge success and you have now decided take your business to the next level and upgrade with new laptops, software and instructor wardrobes. The local Young Entrepreneurs of Tomorrow Association (YETA) has agreed to sponsor your upgrade, provided that you bring in a year-end profit for 2006 of $20,000. You need to prepare a report for YETA, including a summary of your business performance for 2005 and an upgrading proposal for 2006, to demonstrate you can in fact meet the profit requirements. You are asked to provide some basic data by retriving or manupulating data store in an Access dataabase.
In the database, you have tracked the following data in 2005:
Tutors: student_id, given_name, family_name, course_code (the course he/she tutors), hourly_wage, status (active or inactive)
Log: dateL, month, week, tutor_student_id, hours, tutee_student_id
Also, you've obtain the following data from the college:
Marks: year (2004 - 2006), course_code, student_id, midterm, final
Tutors / Contains the information of each tutor in the company. Tutors are students themselves. Each tutor only cover one particular course and all tutors have to have a final score of 90 or more in the course in which they provide tutoring service.Log / Contains the transaction information of each tutoring session
Marks / Contains the midterm and final marks of students in Computer Science and Engineering courses during the years 2004 – 2006. The Marks table includes final marks of both tutors and tutees. In this assignment only the final column will be used. The midterm column is irrelevant.
(See appendix for a graphical representation of the tables and their relationship with each other, as well as sample data for each table.)
==
Q1: How many tutors are there in your company?
Hint: Enter the actual number as the answer, not the query you used to find the answer!
Answer: 125
SQL: Save it as a query and name it Q01_Tutor_Count
SELECT Count(*) AS Tutor_Count
FROM tutors;
This question has been answered for you!
==
Q2: How many students has your company tutored?
Hint:
1. You should query against the log table.
2. You cannot use count distinct together in Access so use distinct instead of count. The query result will list a record count at the end. Use that number as your answer.
3. (for advanced students)There is a way to get the record count in your final query. You have to first save the query that use distinct such as the one suggested in Hint #2 and then create another query againt this saved query and use the count(*) function in your select statement.
Q3:
You ask the tutees to evaluate their tutors and receive mostly positive feedback. One student, however, complained that a tutor was not knowledgeable enough in the subject area. You find this puzzling because the company requires all tutors to have achieved an average of above 90 in the course they tutor. You decide to check the tutors’ final marks in the courses they tutor.
Who is most likely the inadequate tutor? Enter his/her student ID as the answer. (There is only one tutor who is inadequate!)
Hint:
You need to query again the Tutors table and Marks table. You need to join the two table with student ID and you also need to join the two tables with the course_code.
You should jot down the answer to this question as it will be used in the next stage.
Q4:
He/she is considered an inadequate tutor by the company standards. You must talk to he/her and make sure he/she meets the standards before he/she tutors again.
Update his/her record and set the status to 'inactive' regardless of what the current status is (it could be inactive).
Hint: You need to create an SQL UPDATE statement as a “query”. You need to click the ! icon in SQL View or Design View to run the Update SQL to make the update.
Access will show you the following dialog box, click Yes button to confirm the update.
Q5:
Since you and your friends are all in the Computer Science program, your tutoring services in the past year have been limited to courses in this field.
How many different Computer Science courses are there? List all course_ID.
Hint: All CS courses course_id start with ‘CS’ use w
You cannot use count distinct together so use distinct instead of count. See http://www.w3schools.com/sql/func_count_distinct.asp
Q6:
You want to determine the potential tutoring market in computer science courses. For the purpose of market research, the term “potential tutoring market” refers to the (number of) students with a final mark of below 65 in any course.
What was the potential tutoring market in students taking computer science courses in 2006?
Hint: You should jot down the answer to this question as it will be used in a future stage.
Q7:
Your company charges tutors 15% of their earnings as the fee for providing them with tutees and work areas.
What was the profit for the month of January 2006? Round to nearest dollar and omit the dollar sign.
Hint:
1. You need to JOIN the tutors table and log table (Make sure you connect the tutour student id in these two tables)
2. Use the ROUND(expression, deci_places) function in your SELECT clause to round, where deci_places is the number of decimal places (round it to zero decimal place in this case).
3. You need hours from the log table and hourly_wage from the Marks table to calculate the profit. You need to multiple hours and hourly_wage to calculate the revenue for each tutoring session and 15% (0.15) of that revenue is your profit for that tutoring session. Use the aggregation function SUM() add all the profits in Jan. 2006 and use ROUND function to round it. Your select statement should look like
SELECT Round(Sum([hours]*[hourly_wage])*0.15, 0) AS JAN_Profit
FROM …..
Where ….
Q8:
Which month brought in the most profit?
Hint: Your query should display the result in the following format with the most profitable month listed first:
Q8_Most_Profitable_Month /month / Monthly_Profit /
OCT / 1111
JAN / 999
… / …
… / …
… / …
… / …
… / …
Q9:
What is the total profit since the company has been in operation? Round to nearest dollar and omit the dollar sign.
Hint: Use the ROUND(expression, deci_places) function to round, where deci_places is the number of decimal places.
Q10:
What is the ratio of "total profit" to "potential tutoring market" (found in Stage 6 and Stage 9)? Round answer to one decimal place.
Hint 1: You should jot down the answer to this question as it will be used in a future stage.
Hint 2: Use the ROUND(expression, deci_places) function to round, where deci_places is the number of decimal places.
Q11:
You and your colleagues realize that tutoring computer science courses only has a limited market. Therefore, you decide to expand your parameters to include Engineering courses.
Which Engineering course had the biggest potential tutoring market in 2006? Remember, the term "potential tutoring market" refers to the (number of) students with a final mark of below 65 in any course.
Hint: You should jot down the answer to this question as it will be used in a future stage.
==
Q12: (3 points)
a. (Add a new table called based on the following information (0.5 point):
Table name: course
Columns:
Course_code: Text data type with 7 characters in length
Course_name: Text data type with 50 characters in length
Define Course_code as the primary key of this table
Populate the course_code based on the running the following SQL. You have to click the ! icon on the toolbar to actually run the SQL. Switching to Datasheet View from the SQL View will show you a list of distinct course codes but it will not populate the course_code in the course table until you “execute” the SQL. Please save your SQL as Q12_Populate_Course_Code
b. Create a new form called Course_Entry_Form and then enter course information into the course table. Enter all course names (make up these course names by yourself) for the 12 courses currently in the table. Using the columnar format for the form. The form should look like the following: (0.5 points)
c. Set the primary keys of all four tables in the database. Use the Relationships tool to set all the possible the integrity contraints (foreign and primary key pairings) among all tables (tutors, marks, log, and course), yes, including the new table course that you just created. (1 point)
d. Create a master-detail form to enter tutoring logs for tutors. The form should look like the following one (1 point)
Appendix A. Database Schema
In creating your report, you will need to obtain data from the DB2Game database, which contains the Tutors table, Log table and Marks table:
Tutors / Contains the information of each tutor in the companyLog / Contains the information of each tutoring session
Marks / Contains the midterm and final marks of students in Computer Science and Engineering courses during the years 2004 - 2006
The crow's-feet in this diagram indicate one-to-many or many-to-many relationships between tables. For example, each distinct value in the student_id column of the Tutors table can occur only once in that table but many times in the Log table (one-to-many).
Appendix B. Sample Data from the Tutors Table
Query
SELECT * FROM tutors;
Result
Student_id / Given_name / Family_name / Course_code / Hourly_wage / Status040007 / Mark / Richardson / CS1TA3 / 13.50 / Active
040020 / Donald / Andrews / CS1MD3 / 13.50 / Active
040032 / Michael / Griffin / CS1SA3 / 17.50 / Active
040053 / Jose / Canning / CS1BA3 / 15.00 / Active
040077 / Helen / Smith / CS1MD3 / 15.00 / Active
040138 / Gregory / James / CS1FC3 / 15.00 / Active
040143 / Julie / Kober / CS1TA3 / 17.50 / Active
040162 / Aaron / Turner / CS1MA3 / 15.00 / Active
040179 / Gallen / Rodriguez / CS1FC3 / 17.50 / Active
040181 / Marie / Hutchison / CS1BA3 / 17.50 / Active
…
Appendix C. Sample Data from the Log Table
Query
SELECT * FROM log;
Result
DateL / Month / Week / Tutor_student_id / Hours / Tutee_student_id10/15/2005 / OCT / 1 / 050778 / 1.50 / 060615
10/15/2005 / OCT / 1 / 050150 / 4.00 / 060190
10/15/2005 / OCT / 1 / 050621 / 2.50 / 060084
10/15/2005 / OCT / 1 / 040077 / 3.00 / 060629
10/15/2005 / OCT / 1 / 040218 / 3.00 / 060778
10/15/2005 / OCT / 1 / 040231 / 3.00 / 060333
10/15/2005 / OCT / 1 / 050890 / 3.00 / 060906
10/15/2005 / OCT / 1 / 040617 / 1.00 / 060834
10/15/2005 / OCT / 1 / 050433 / 1.00 / 060478
10/15/2005 / OCT / 1 / 050279 / 1.50 / 060822
…
Appendix D. Sample Data from the Marks Table
Query
SELECT * FROM marks;
Result
Year / Course_code / Student_id / Midterm / Final2004 / CS1FC3 / 040021 / 92 / 85
2004 / CS1FC3 / 040022 / 78 / 73
2004 / CS1FC3 / 040024 / 93 / 84
2004 / CS1FC3 / 040029 / 75 / 75
2004 / CS1FC3 / 040030 / 92 / 92
2004 / CS1FC3 / 040032 / 71 / 65
2004 / CS1FC3 / 040034 / 65 / 68
2004 / CS1FC3 / 040038 / 85 / 85
2004 / CS1FC3 / 040041 / 75 / 72
2004 / CS1FC3 / 040044 / 61 / 65
…
Appendix E. DDL Create Statements that Creates the Tables
CREATE TABLE marks (
year integer not null,
course_code char(7) not null,
student_id char(7) not null,
midterm integer not null,
final integer not null);
==
CREATE TABLE tutors (
student_id char(7) not null primary key,
given_name char(15) not null,
family_name char(15) not null,
course_code char(7) not null,
hourly_wage Money not null,
status char(10));
==
CREATE TABLE log (
dateL date not null,
month char(5) not null,
week integer not null,
tutor_student_id char(7) not null,
hours single not null,
tutee_student_id char(7) not null,
unique (log_date, tutor_student_id, tutee_student_id));
==
The following example works with ORACLE and Microsoft SQL server but not with Microsoft Access.
The keyword DISTINCT and COUNT can be used together to count the number of distinct results.
Syntax
SELECT COUNT(DISTINCT column(s)) FROM tableExample
With this "Orders" Table:
Company / OrderNumberSega / 3412
W3Schools / 2312
Trio / 4678
W3Schools / 6798
Example
SELECT COUNT(Company) FROM OrdersResult:
4Example
SELECT COUNT(DISTINCT Company) FROM OrdersResult:
3