PART $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 KE$7-3)$

STEP $2$: CREATED & NAMED YOUR DATABASE $(($Figure KE$7-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 KE$7)..........$

PART $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 KE$6-14$ for these table specs $$ Second table design $($Normalized$)$ Notice I added an EmployeeID to the employee table in Figure KE$6-14$ as the primary key $$ because we do not use names as primary keys, as too

many people have the same names!

PART $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 $2-$A Step $4$ above$)$ to show the logical relationships among all FIVE

Tables using the following logic:

$1.$ One PROSPECT can have many PHONES

$2.$ One PROSPECT can have many CONTACTS

$3.$ One EMPLOYEE can have many CONTACTS

PART $2-$D: EXTEND the Volunteer Prospect Data Entry Form $($created in Part $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 $2-$A Step $5.$

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 $2-$E: $1.$ WRITE $2$ MORE BUSINESS QUESTIONS:

Business Question #$2($BQ #$2)$

Business Question #$3($BQ #$3)$

$2.$ CREATE $2$ MORE QUERIES: From these Business Questions, using the Query Wizard

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 $2-$F: Using the Report Wizard:

$1.$ CREATE REPORT #$2$ from QUERY #$2$ above, using the Report Wizard

$2.$ 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 $2-$G:

$1.$ Write BUSINESS DECISION #$2($BD #$2)$ from REPORT#$2$

$2.$ Write BUSINESS DECISION #$3($BD #$3)$ from REPORT #$3$