INSTRUCTIONS
This MidTerm Part 3 is to be done on your own, as an individual assessment, that is, this is not a group assignment.
The University has excitedly announced that all CapU students now have free, unlimited access to LinkedIn Learning – an
enhanced version of Lynda.com.
We will be using this medium for PART 3.1 of the Mid Term Part 3, as the training LAB for the database material required to
complete PART 3.2 of the MidTerm Part 3, given from page 2 of this document.
MID-TERM – PART 3.1: DATABASE TRAINING VIDEOS as ASYNCHRONOUS LEARNING MATERIAL
Final Grade contribution = [(LinkedIn Learn Grade received ∕ 100) x 5%]
Go to the Cap U Student eLearn website.
Click on Linkedin Learning
You may be asked to log in with your linked in password put it in then it will ask you for your Capilano userID as
single sign-on. But you have to key in your LinkedIn password and it will then take you to the site. You do not
have to pay for a subscription for this,
Search for Access2019 Essential Training – Instructor Adam Wilbert
https://www.linkedin.com/learning/access-2019-essential-training/
Go to Contents (Left side panel) and click on it – A drop down of all the video contents will be displayed. It covers the
following:
Create a Database
Create Database Tables
Building an Entity Relationship Diagram (ERD) in MS Access
Building a Data Entry Form in MS Access
Building Queries in MS Access
Building Reports in MS Access
Go through each of the videos in the order given.
➢ Take each of the chapter quizzes.
Note: You will receive a separate certificate for completing the videos & quizzes. SAVE it to your laptop.
➢ At the end, TAKE the EXAM.
Note: You will only get a certificate for the EXAM if you get 70% or more in the Exam – (so take a print screen copy of
the exam results, especially for those of you who got below 70% – let us hope this is no-one – so I can calculate your
final course grade %)
➢ Then usethe buttons on your keyboard for [Alt] [PrintScreen] (hold them down together) and SAVE the
Mark% you received on your laptop. (So I can see your grades)
➢ Then GET THE EXAM CERTIFICATE and SAVE it on your laptop.
Put all three files into one PDF document file (1. VIDEO QUIZZES COMPLETION CERTIFICATE, 2. Print Screen of your Exam MARK & 3.
EXAM CERTIFICATE) and put the PDF file in the MT#3 drop box on eLearn (Moodle) as your submission for the Part 3.1 of the MT#3.
Name the file [your name], [student number], [MT3-PART3.1]. These certificates are accepted by Industry and can be put on your
resume.
ONCE YOU ARE FINISHED WITH THE LINKEDIN VIDEO TRAINING MATERIAL & EXAM,
CONTINUE ON THE NEXT PAGE FOR PART 3.2
2
MID-TERM #3 – PART 3.2
Total Part 3.2 Mark = 30
Part 3.2 contribution to Final Grade = (Mark received ∕ 30) x 15%
Mid-Term #3-Part 3.2 will be the based on the Database concepts we are studying in Chapter 5 and expanded in
Knowledge Extensions 6 & 7, and the training videos give above from LinkedIn Learning.
INSTRUCTIONS
Mid-Term #3 will be the based on the MS ACCESS Database work we will be doing as the practical LAB work for the
course. Pre-Reading is in your text book as follows: on pgs 130- 189 (Chapter 5 and expanded in Knowledge Extensions 6
& 7)- Pre-Reading must be done by each student individually.
Your practical exercises for this for this Mid Term #3 is taken from our required text– Experiencing MIS – Chapter 5 –
Knowledge Extension 6 (Sets the Business Scenario for the creation of the Prospect to Volunteer Database (Begins with
“Who will Volunteer” ) & Knowledge Extension 7, which shows you the HOW TO’s for using Microsoft 2016 to actually
create and work with the Prospect to Volunteer relational database“
We will use Microsoft Access Database Management Software [DBMS] to build out a Prospect to Volunteer Database.
During this exercise, you will be wearing some of the many hats we see in the Business Information Systems world. These
are:
Business Analyst: Collect and form user requirements and data needs
Database Administrator: Create, load and update database structures needed to store and manipulate company data
Business Forms Developer: Create data entry forms that are meaningful and presentable for Business Clerks
Business Reports Writer: Create Reports that are suitable to be shown to Business Managers
Manager/Decision Maker: Take action, make decisions using data and information from your Company’s database.
The end result is that you, as Manager of fund-raising, for a local public television station, say Knowledge Network Station,
are quite pleased with the data your Information Systems Staff and Business Users have collected for you to analyse. Now
you are certain that it has helped you improve your volunteer management to better staff your fund-raising drives and to
increase your donations from happy Donors. But… now that you have gotten such great results, you are already seeing new
areas and requirements that you can collect data for and do analysis with, for your next fund-raiser. You have decided that
you will be working closely with your Information Systems staff going forward to see these come to fruition for next year
and years to come!!
To build the Volunteer Prospect Database you will need to create various Tables and their logical relationships with the MS
Access relational database management system (DBMS). The structure of the Database must reflect the way in which the
television station Staff/Users think about workflows/activities for the fund-raiser.
First you need to build a data model showing which data will sit in which table and how they relate or flow from one table
or tables to the next. The Data Model for Volunteer Prospect Database would therefore have the following five key
Database Tables/Entity Classes we will need:
PROSPECT: To record all volunteer prospects.
WORK: To record work that the Prospect has performed for the Television Station.
PHONE: To record multiple phone number for each prospective volunteer.
CONTACT: To record contacts that you, the Manager, or your Employees, have made with prospective volunteers.
EMPLOYEE: To record the employee who contacts the prospective volunteer(s).
3
NB: See pg 168 of the textbook, Figure KE6-14, for all five (5) of these table specs – Second table design (Normalized)
For the data model shown please note:
Underline means the Primary (Unique) Key of the Table
Italics means the Foreign Key (the primary key of another table placed in the existing table)
Underline & Italics [example] means it is both part of a primary key [or a compound primary key]
and a foreign key for another table.
LET US BEGIN……….
PART 3.2-A: Working through a thin slice example, of what we will need to do for the Report – Knowledge Ext #7. I
have provided a starter database for the class. For Steps 1 through 6.
STARTER DATABASE CONTENTS:
STEP 1: OPENED the MS ACCESS (Figure KE7-3)
STEP 2: CREATED & NAMED YOUR DATABASE ((Figure KE7-4)
STEP 3: Created the first 2 Tables = PROSPECT & WORK as follows:
PROSPECT: (ProspectID, Name, Street, City, Province, Postal Code, P-EmailAddress)
WORK: (ProspectID, W-Date, W-Time, W-Notes, NumCalls, TotalDonations)
STEP 4: Created an ENTITY RELATIONSHIP DIAGRAM with these 2 Tables
showing the logical relationship where One PROSPECT can perform many WORK
STEP 5: Created a DATA ENTRY FORM – to put more data into the 2 Tables
NOW to create the following using the relevant sections in the text book
STEP 6: Ask a BUSINESS QUESTION #1 (BQ #1) – using attributes & data of 2 Tables (not in the text book)
STEP 7: Create a QUERY #1 – using the BUSINESS QUESTION #1 asked above. (Simple query)
STEP 8: Create a REPORT #1 – Introduce: Group & Sort plus Sub-Totals & Grand Totals
STEP 9: Make a BUSINESS DECISION #1 (BD #1) from the information in the REPORT #1 (not in the text book)
** Now we have the hang of Data, DB, DBMS, ERD, Data Entry Forms, Queries, Reports and Business Decision!!**
4
NOW LET US EXTEND OUR KNOWLEDGE (beyond what is shown in KE7) ……….
PART 3.2-B
Using THE PROSPECT DATA ENTRY FORM given in the Starter Database:
a) Add 3 more Prospects records to your Prospect Table,
(these are in addition to the 7 given to you in the starter database). Therefore, you should have at least 10
prospects in all
NOTE: for these three new Prospects, fill in the attributes/columns with your own information.
b) Fill in at least 1 record of Work-Date and Work-Time information for all 10 of your prospects, in your WORK
TABLE sub-form
THEN
EXTEND your current two-table database to include 3 ADDITIONAL tables – EMPLOYEE; CONTACT & PHONE tables
with the following columns/attributes for each:
EMPLOYEE: (Employee-ID, EmployeeName, Phone, E-EmailAddress
CONTACT: (ProspectID, C-Date, C-Time, C-Notes, EmployeeID)
PHONE: (ProspectID, PhoneType, PhoneNumber)
Make up your own data for each of the columns for these remaining three tables. Please make sure the data you
make up are meaningful in the context of the Business Scenario set in Knowledge Extension 6 .
For the Employee Table, put between 5 to 7 employee’s information in the table (i.e. it should have between 5 to
7 rows/records in the table.
NOTE 1: The Employee is different to the Prospect/Volunteer, so do not put the same names in the Employee Table
that exist in the Prospect Table.
NOTE 2: See Figure KE6-14 for these table specs – Second table design (Normalized) – Notice I added an EmployeeID to the employee table in Figure KE6-14 as the primary key – because we do not use names as primary keys, as too
many people have the same names!
PART 3.2-C: EXTEND your Entity Relationship Diagram [ERD], i.e. add the 3 new tables to the ERD (from the starter
database which we built with the PROSPECT & WORK tables in Part 3.2-A Step 4 above) to show the logical relationships
among all FIVE Tables using the following logic:
- One PROSPECT can have many PHONES
- One PROSPECT can have many CONTACTS
- One EMPLOYEE can have many CONTACTS
PART 3.2-D: EXTEND the Volunteer Prospect Data Entry Form (created in Part 3.2-A Step 5), to allow the User/
Employee to populate all five tables with data. To do this use the Form wizard to:
a) Add the Employee, Contact and Phone tables to the existing Data Entry Form, created in Part 3.2-A Step - Note the Contact and Phone Tables are sub-forms of the Prospect Table and
the Contact Table is also a sub-form of the Employee Table
5
Part 3.2-E: 1. WRITE 2 MORE BUSINESS QUESTIONS:
Business Question #2(BQ #2)
Business Question #3 (BQ #3) - CREATE 2 MORE QUERIES: From these Business Questions,
QUERY #2 – To be taken from 3 OR 4 Tables – Middle level complexity query
QUERY #3 – To be taken from ALL 5 Tables – Most Complex Query – here you will calculate Average
donations inside of the Design View of the Report. ( See Appendix A at the end of this doc on how to
calculate the Average Donations from within a Report.
Part 3.2-F: Using the Report Wizard: - CREATE REPORT #2 from QUERY #2 above, using the Report Wizard
- CREATE REPORT #3 from QUERY #3 above, using the Report Wizard – Calculate the AVG Donation in this
report (See Appendix A on how to do this)
Part 3.2-G: - Write BUSINESS DECISION #2 (BD #2) from REPORT#2
- Write BUSINESS DECISION #3 (BD #3) from REPORT #3
TOTAL REPORT MARKS = [Your Report Grade/30 x 15%]
TOTAL MARKS for the Database training on LinkedIn Learning = [Your transcript grade/100 * 5%]
FINAL Course Grade for Mid Term #3 = (TOTAL REPORT MARKS + TOTAL MARKS for DB Trng)