Reference no: EM132153788
Training Database Assignment -
You group has been hired by TrainingrUs to keeps track of training revenues. TrainingrUS 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.
TRAINEE table contains following information:
- Trainee ID (PK)
- Course_ID (PK)
- Trainee name
- Date of training
Note: PK is (Trainee ID, Course_ID)
FK is Course ID
Training session signed for is the same as Course ID in COURSE table.
PART A -
- Develop the tables in ACCESS database Systems.
- Identify PK and FK of each table (can do in WORD).
Table Name
|
COURSE
|
TRAINING
|
Primary Key (PK)
|
|
|
Foreign Key (FK)
|
|
|
Submit this table in Part A (section d) 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 c)
Justify why it is 1:1 or 1:m or m:n
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
10. Create an Enrollment report of Dates and Overall total number of students taking classes on those dates, i.e., 10/02/2009; 10/03/2009.
Create a REVENUE report to provide date, session IDs, number of students and total revenue for TrainingrUs. Format the output appropriately.
Part A:
a. Tables in ACCESS (see section b of part B below)
b. Table contents of each table in ACCESS (print screen shot of contents)
c. Relationships in ACCESS (print screen shot of contents)
d. PK and FK table
Part B:
a. Build Queries in ACCESS and run them (Print screen shots of queries and their output in ACCESS)
b. actual ACCESS database with queries (save the database and submit the access database file with .mdb extension in 2003 or accdb extension in 2007). This will also satisfy section "a" of PART A since your tables will be there on the same file.
Attachment:- Assignment File.rar