Database Assignment
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:
https://www.youtube.com/watch?v=jaM8UmpwH_c
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 |
Price ($) |
Instructor Name |
Instructor Address |
|
S111 |
2850 |
Harry |
UB |
|
S121 |
3200 |
Polly |
UB |
|
S155 |
1200 |
Smith |
Loyola |
|
H111 |
500 |
Morton |
JHU |
|
H123 |
2200 |
David |
JHU |
|
H125 |
1200 |
Polly |
UB |
|
H221 |
800 |
Lionel |
UB |
|
H225 |
1450 |
Lionel |
UB |
TRAINEE sample data
|
Trainee ID |
Course_ID |
Trainee Name |
Date of training |
|
A101 |
H111 |
Sam Paper |
10/02/2015 |
|
A101 |
H123 |
Sam paper |
10/03/2015 |
|
A110 |
S111 |
Mary Poppins |
11/01/2015 |
|
B112 |
S111 |
Alex Johnson |
11/01/2015 |
|
B112 |
S121 |
Alex Johnson |
11/02/2015 |
|
B112 |
S155 |
Alex Johnson |
11/06/2015 |
|
B222 |
H123 |
John Watt |
10/03/2015 |
|
B222 |
H111 |
John Watt |
10/01/2015 |
|
B222 |
S111 |
John Watt |
11/06/2015 |
|
B222 |
S121 |
John Watt |
11/02/2015 |
|
B222 |
S155 |
John Watt |
10/05/2015 |
|
C111 |
H123 |
Jong Chu |
10/03/2015 |
|
C111 |
H111 |
Jong Chu |
10/02/2015 |
|
F456 |
S111 |
Jerry Long |
11/08/2015 |
|
F555 |
S111 |
Willy Polk |
11/08/2015 |
|
F555 |
S155 |
Willy Polk |
11/06/2015 |
|
F555 |
H111 |
Willy Polk |
02/11/2015 |
|
X123 |
H123 |
Shirley Dent |
10/03/2015 |
|
X123 |
S111 |
Shirley Dent |
11/06/2015 |
|
X123 |
H125 |
Shirley dent |
02/11/2015 |
|
X123 |
H225 |
Shirley dent |
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 |
COURSE |
TRAINING |
|
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
PART B:
Develop the following queries in ACCESS and run (execute) them Q 1-12, q 13 bonus)
1. List the Course IDs.
2. Give the name of instructor teaching course H225.
3. Give the classes offered on October 5, 2015
4. Give the number of classes taught by each instructor
5. How many courses each student is taking?
6. 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:
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 |
Maximum Points |
Your Points |
|
PART A: |
||
|
1. Develop tables in ACCESS |
7 |
|
|
2. Identify PK & FK of each table |
3 |
|
|
3. Table relationships (TRAINEE and COURSE) in ACCESS and its justification |
3 |
|
|
PART B: |
||
|
Queries 1 through 12 and their output |
24 |
|
|
Query 14 report and its output |
3 |
|
|
BONUS |
||
|
Q 13 |
3 |
|
|
Q 15 (report) |
||
|
TOTAL |
40 + 4 (BONUS; q13 and q 15) |
"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.
Discount Code: CIPD30
Click ORDER NOW..
