Develop the following queries in ACCESS and run (execute) them Q 1-12, q 13 bonu

  

assignment_2_2016_wb.docx

Unformatted Attachment Preview

Don't use plagiarized sources. Get Your Custom Essay on
Develop the following queries in ACCESS and run (execute) them Q 1-12, q 13 bonu
Just from $10/Page
Order Essay

Database Assignment
(Assignment 2)
(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.
Resources available:

http://www.gcflearnfree.org/office2013/access2013
Achievement and Learning Center (410-8375383)
(35 points)
Training Database
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)
 Price
 Instructor Name
 Instructor Address
Note: Primary Key (PK) is COURSE ID
Identify FK, if any
TRAINEE table contains following information:
 Trainee ID (PK)
 Course_ID (PK)
 Trainee name
 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
Course
ID
S111
S121
S155
H111
H123
H125
H221
H225
Price ($)
Instructor Name
2850
3200
1200
500
2200
1200
800
1450
Harry
Polly
Smith
Morton
David
Polly
Lionel
Lionel
Instructor
Address
UB
UB
Loyola
JHU
JHU
UB
UB
UB
TRAINEE sample data
Trainee ID
Course_ID
Trainee Name
Date of
training
A101
A101
A110
B112
B112
B112
B222
B222
B222
B222
B222
C111
C111
F456
F555
F555
F555
X123
X123
X123
X123
H111
H123
S111
S111
S121
S155
H123
H111
S111
S121
S155
H123
H111
S111
S111
S155
H111
H123
S111
H125
H225
Sam Paper
Sam paper
Mary Poppins
Alex Johnson
Alex Johnson
Alex Johnson
John Watt
John Watt
John Watt
John Watt
John Watt
Jong Chu
Jong Chu
Jerry Long
Willy Polk
Willy Polk
Willy Polk
Shirley Dent
Shirley Dent
Shirley dent
Shirley dent
10/02/2015
10/03/2015
11/01/2015
11/01/2015
11/02/2015
11/06/2015
10/03/2015
10/01/2015
11/06/2015
11/02/2015
10/05/2015
10/03/2015
10/02/2015
11/08/2015
11/08/2015
11/06/2015
02/11/2015
10/03/2015
11/06/2015
02/11/2015
10/02/2015
PART A:


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)
Table Name
Primary Key (PK)
Foreign Key (FK)
COURSE
TRAINING
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
PART B:
Develop the following queries in ACCESS and run (execute) them Q 1-12, q 13 bonus)
1.
2.
3.
4.
5.
6.
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
13. Bonus
(2 points)
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
Date
Number of students taking
classes
10/02/2015
10 (for illustration only)
10/3/2015
5 (not actual number for illustration
only)
etc..
Overall Total Number of Students:
xxxx
BONUS:
(2 points)
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
page.
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
relationships
Part B:
1. Build Queries in ACCESS and run them (Print screen shots of queries and their
output in ACCESS)
2.
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
Requirements
PART A:
Maximum Points
1. Develop tables in
ACCESS
2. Identify PK & FK of
each table
3. Table relationships
(TRAINEE and
COURSE) in ACCESS
and its justification
PART B:
Queries 1 through 12 and their
output
Query 14 report and its output
7
BONUS
Q 13
Q 15 (report)
TOTAL
3
3
24
3
3
40 + 4 (BONUS; q13 and q
15)
Your Points

Purchase answer to see full
attachment

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

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.

Money-back guarantee

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

Zero-plagiarism guarantee

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

Free-revision policy

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

Privacy policy

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

Fair-cooperation guarantee

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

Order your essay today and save 30% with the discount code ESSAYSHELP