Unformatted Attachment Preview
(Note: you can do it independently or in group of 2. If you want to do it
independently, e-mail me. I will be forming groups.)
DUE DATE: March 29, 2016
Make sure to list names of ONLY those group members who
participated in the project. If there is a member who is NOT
participating, let me know immediately.
Achievement and Learning Center (410-8375383)
You group has been hired by TrainingrUs.com to keeps track of training revenues.
TrainingrUS.com wants you to keep scheduling.
COURSE table contains following information:
Course ID (PK)
Note: Primary Key (PK) is COURSE ID
Identify FK, if any
TRAINEE table contains following information:
Trainee ID (PK)
Date of training
Note: PK is (Trainee ID, Course_ID)
Identify FK, if any
Training session signed for is the same as Course ID in COURSE table
Following sample data is available:
COURSE Sample data
TRAINEE sample data
Develop the tables in ACCESS database Systems
Identify PK and FK of each table (can do in WORD)
Table 1 (identify PK and FK relationships)
Primary Key (PK)
Foreign Key (FK)
Submit this table as Part A (section 2) requirement to be submitted see below
Show the relationship (1:1 or 1:m or m:n) between the following:
COURSE and TRAINEE in ACCESS and include a print screen (see under
submission PART A, section 3)
Justify why it is 1:1 or 1:m or m:n
Develop the following queries in ACCESS and run (execute) them Q 1-12, q 13 bonus)
List the Course IDs.
Give the name of instructor teaching course H225.
Give the classes offered on October 5, 2015
Give the number of classes taught by each instructor
How many courses each student is taking?
Give the training sessions and their dates (make sure there is no repetition of
session and corresponding date).
7. How many classes Shirley Dent is taking?
8. Give the total amount paid by Shirley dent.
9. Give the total amount paid by Sam Paper and Shirley Dent
10. Give the names of instructors teaching on October 02, 2015
11. Give the total revenue generated by course H111. Hint: count number of times
H111 repeats in trainee table; adds price for those occurrences for course H111
from course table. Ex:
If 5 students take course S145 and price is 1200 each, the their revenue will be
1200*5 = 6000 (or 1200 added 5 times)
12. Give the total number of students taking classes in November. Hint:use between
to get november count, i.e., between date AND date 2
Give the total salary of each instructor.
Ex: If an instructor taught 5 students in course H221 and 2 students in H111, then
their salary would be
5*800(price of H221)+2*500(price of H111)= $5000
14. Create an Enrollment report of Dates and Overall total number of students taking
classes on those dates, i.e., 10/02/2015; 10/03/2015. see the template below:
Enrollment Report for TrainingrUs.com
Number of students taking
10 (for illustration only)
5 (not actual number for illustration
Overall Total Number of Students:
15. Create a REVENUE report to provide date, session IDs, number(count) of students
and total revenue for TrainingrUs.com. Format the output appropriately.
To be submitted: (in SAKAI only; do not e-mail)
Post ONE project per group in SAKAI under
database with group member names on the first
Create a zip file with all the files (see Appendix B—
assignment 1– on how to create zip files)
Part A: (in WORD file)
1. Table contents of each table in ACCESS (print screen shot of contents)
2. PK and FK table (submit Table 1)
3. Relationship among ENTITIES (1:1 or 1:m or m:n) and Justify those
1. Build Queries in ACCESS and run them (Print screen shots of queries and their
output in ACCESS)
actual ACCESS database with queries and report. (filename.accdb)
TO create Print screen
You can create print screen shots of your output by pressing ctrl and Print
Screen keys (this copies in your clip board) and then pasting it in a word
file. Similar to any copy and paste operation. This is the WORD file you
will submit will print screens and PART A. if you know “PAINT” you can
crop the screen shots to look nice.
You will have TWO files in ONE zip file:
ONE WORD file (Part A and Part B; section 1)
ONE ACCESS file with .mdb or .accdb extension (Part B)
Appendix A: Grading Criteria
1. Develop tables in
2. Identify PK & FK of
3. Table relationships
COURSE) in ACCESS
and its justification
Queries 1 through 12 and their
Query 14 report and its output
Q 15 (report)
40 + 4 (BONUS; q13 and q
Purchase answer to see full
Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.
You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.Read more
Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.Read more
Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.Read more
Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.Read more
By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.Read more