you group has been hired by trainingruscom to

Question

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:

1.  Course ID (PK)
2. Price
3. Instructor Name
4. Instructor Address

Note: Primary Key (PK) is COURSE ID

Identify FK, if any

TRAINEE table contains following information:

1. Trainee ID (PK)
2. Course_ID (PK)
3. Trainee name
4. 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
S145	1200	Smith	Loyola
H111	500	Morton	JHU
H123	2200	David	JHU

TRAINEE sample data

Trainee ID	Course_ID	Trainee Name	Date of training
A101	H111	Sam Paper	10/02/2009
A101	H123	Sam paper	10/03/2009
A110	S111	Mary Poppins	11/01/2009
B112	S111	Alex Johnson	11/01/2009
B112	S121	Alex Johnson	11/02/2009
B112	S145	Alex Johnson	11/06/2009
B222	H123	John Watt	10/03/2009
B222	H111	John Watt	10/01/2009
B222	S111	John Watt	11/06/2009
B222	S121	John Watt	11/02/2009
B222	S145	John Watt	10/05/2009
C111	H123	Jong Chu	10/03/2009
C111	H111	Jong Chu	10/02/2009
F456	S111	Jerry Long	11/08/2009
F555	S111	Willy Polk	11/08/2009
F555	S145	Willy Polk	11/06/2009
F555	H111	Willy Polk	10/02/2009
X123	H123	Shirley Dent	10/03/2009
X123	S111	Shirley Dent	11/06/2009

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:

 

PART B:

Develop the following queries in ACCESS and run (execute) them

1. List the course ID and its cost, format the output appropriately.

2. Give the trainee name and the number of classes each trainee is taking.

3. How many trainees are taking classes on November 6th? (Give a count)

4. Give the training sessions and their dates (make sure there is no repetition of session and corresponding date).

5. How many students are registered per training section?

6. How many courses are there?

7. How many classes John Watt is taking?

8. Give the total amount paid by Willy Polk

9. Give the names of instructors teaching on November 6th