Expert Answer:IS200 MS Access Project

  

Solved by verified expert:View the MAIN assignment file, Access_Project.pdf, for complete assignment instructions.Reference the WildOutfittersDataModel.pptx file to assist you in creating the database.Use the guide CIS200 MS Access Project Guide SP19.pdf to help you complete the assignment.Note that this guide is NOT the main assignment file! It is a supplement only. Make sure you read the Access_Project.pdf document to fully complete the assignment.
wildoutfittersdatamodel.pptx

access_project.pdf

Don't use plagiarized sources. Get Your Custom Essay on
Expert Answer:IS200 MS Access Project
Just from $10/Page
Order Essay

cis200_ms_access_project_guide_sp19.pdf

Unformatted Attachment Preview

tblEmployee
tblClass
*EmpID
*ClassID
EmpLastName
EmpFirstName
EmpPosition
EmpPhone
EmpEmail
ClassDate
ClassStartTime
ClassEndTime
ClassLocation
_________
_________
_________
tblCustomer
tblCourse
*CourseID
CourseTitle
CourseType
CourseDesc
What should the foreign keys be here?
*
= Unique Identifiers
(become the primary
keys in your tables)
*CustID
CustLastName
CustFirstName
CustAddress
CustCity
CustState
CustZip
CustPhone
CustEmail
= One to Many
Relationship
EmpID
EmpLastName
EmpFirstName
EmpPosition
EmpPhone
EmpEmail
1
Felton
Luca
Instructor
970-1231234
lfelton@wfi.com
2
Shumer
Anya
Instructor
912-7771214
ashumer@wfi.com
3
Tucker
Ramon
Training
Manager
404-7771215
rtucker@wfi.com
4
5
CourseID
CourseTitle
CourseType
CourseDesc
1
Intro to Backpacking
All-Day
Discussion of trip planning,
equipment lists, and proper
packing techniques.
2
Basic Caving
Half-Day
Safety and lighting fundamentals.
You will get muddy. Dress
appropriately.
3
Bouldering Fundamentals
Half-Day
Rock climbing basics applied to
short (20ft-30ft) but difficult
problems.
4
Introduction to Kayaking
All-Day
Learn the fundamentals of wet
exit, rolling, and basic paddle
technique.
5
CustID
CustLName
CustFName
CustAddress
CustCity
CustState
CustZipCode
CustPhone
CustEmail
1
Turner
Taylor
678 Maple
Circle
Austin
TX
02108
(617) 8761234
TT@nphil.com
2
Morgan
Kyle
18 River
Street
Savannah
GA
31401
(912) 2345678
KM@minerva.org
3
Williams
Ada
524 Zion
Street
Queens
NY
10022
(212) 7463524
AW@qwlgm.com
4
5
ClassID
ClassDate
ClassStartTime
ClassEndTime
ClassLocation
1
07/07/2019
0900
1200
Granite State Park
?
?
?
2
07/16/2019
0900
1100
In-Store
?
?
?
3
03/15/2019
0930
1230
Troll Caverns
?
?
?
4
04/07/2019
0830
1600
Granite State Park
?
?
?
5
6
7
8
9
10
Foreign
key?
Foreign
key?
Foreign key?
This Project is Due: Tuesday, March 12, by 11:59pm.
Your project will be submitted through Canvas.
Note: You do not have to write out and submit the answers to the interpretative
questions for any of the parts of this project. However, the solutions you create in
Access should enable you to answer those questions.
Academic Integrity Policy:
The entire file that you submit for this and your other CIS200 computer projects
should be your own individual work! Keep in mind the Academic Integrity policy for
CIS200 and Colorado State University.
Important! You are NOT to receive ANY outside assistance on the computing
projects other than from a CIS200 Teaching Assistant or Dr. Serrano without prior
approval from your instructor.
File Instructions
For this project you will complete and submit one file: an Access database file that you
build and use based on the data model that is available for download.
Name your files based on your first initial and then your last name. For example,
if your name is Frodo Baggins, your Access filename would be: FBaggins.accdb.
**Submit your completed file through the Canvas system.
Microsoft Access Project: Managing a Small Business
Microsoft Access Project: Managing a Small Business
By reading the business case sections and completing the four parts of this project
(Parts A, B, C, and D) you will:
 Extend your knowledge of Microsoft (MS) Access tables, forms, queries, and
reports.
 Understand the relationship between the logical model of a database and the
physical implementation of that database.
 Understand how to implement referential integrity and discover its purpose.
 Learn how to apply MS Access to solve business problems.
PROJECT INTRODUCTION
Data and databases are at the heart of many business information systems. If you think
about the daily interactions you have with organizations, you will discover that many of
these interactions generate data that organizations use to create value for you and for
them. For example, when you shop at a retail grocery store, data is gathered about your
purchases and your method of payment. If you choose to pay with a debit or credit card,
then additional data may be gathered about you and your purchase. Some stores even
offer a discount if you use a customer rewards card. This enables the store to gather
data about your specific purchases and to provide value to you through coupons and
other sales promotions. It also enables store management to make better decisions
about product selection, pricing, and promotions. This project will focus on how a retail
firm might achieve its goals of generating new business and increasing customer
satisfaction by providing a knowledge-oriented service to its customers. You will create
the database that will allow this company to accomplish these goals.
General Business Case
WildOutfitters, Inc. is a well-established national outdoor-sports retail products chain
with a local store in your area. It carries a complete line of name brand and locally
produced equipment and supplies for outdoor enthusiasts. Through customer surveys,
WildOutfitters discovered that many of its customers, who are beginners, buy equipment
without knowing how to use it properly. Rather than view this as a problem,
WildOutfitters sees this as an opportunity to provide better service to its customers and
to gain a business advantage over its competitors. WildOutfitters is going to offer “how
to” courses for its customers. During these courses, customers will learn how to use the
equipment they purchased, but they will also learn about other equipment they should
purchase. WildOutfitters will use this free (to the customers) service to increase sales
and customer satisfaction. Isaac Timberlake, a student at your business school, is an
intern at WildOutfitters, and Sara Johns, the store manager, asks Isaac to create a
Course Management System that she can use to track the courses customers take, and
the employees who teach the courses. Isaac understands the business requirements,
but he needs your help in designing the database that will be at the heart of the Course
Management System. The end result of your collaboration with Isaac will be the
Microsoft Access Project: Managing a Small Business
relational database and associated features specified in the MS Access Project
instructions that follow.
Project Data Model Instructions: You will need to download the PowerPoint file
WildOutfittersDataModel.pptx from the Access assignment in Canvas. This file
contains the (incomplete!) data model you will reference in order to create your
database. (NOTE: You will need to complete the data model before using it to create
the database!) There are several slides in the presentation. The first slide is the data
model, and the remaining slides are examples of how your tables might look.
Remember, you will have to use MS Access to create the tables, and you will have to
add additional data to meet project requirements.
Project Naming Conventions: Access uses several database objects to perform
different functions. For example, a table object stores data, and a form object helps you
input data. In general, we will refer to the database objects simply as tables, forms,
queries, and reports. Each of these objects may have similar names, such as the
Customer Table, the Customer Form, etc. In order to prevent confusion we will ask you
to add a prefix to all object names. Table 1 shows the database object, the
corresponding prefixes, and an employee-related example.
Database Object
Prefix
Example
Table
tbl
tblEmployee
Form
frm
frmEmployee
Query
qry
qryEmployee
Report
rpt
rptEmployee
Table 1 – Customary Prefixes for Database Objects
PART A – Creating the Tables
Part A Problem Definition: Isaac welcomed the challenge of creating an information
system that will help create business value for Wild Outfitters, Inc. This project focuses
on the database component of that IS, and accomplishing this project will enable you to
help Isaac by applying and refining the IS skills you’ve learned. Before you can build the
database, you will have to understand the data that must be captured by the system
and sketch out how this data will be stored. You should try this on your own and then
compare your model to the one found in the WildOutfittersDataModel.pptx file.
Part A Problem Requirements: Remember, tables are the objects that MS Access
uses to store data. Your first task in this part of the project is to help Isaac by using
Microsoft Access to create the required tables (see the WildOutfittersDataModel.pptx
file). You should use input masks for fields where appropriate (e.g., zip code, phone,
etc.). If you do not know what an input mask is, use MS Access’ Help function (or an
online search) to find out. Next, you will need to populate the tables with data. You
should create the data yourself following the examples in the
WildOutfittersDataModel.pptx file. You should create at least ten records (rows) in
Microsoft Access Project: Managing a Small Business
the class table, and five records each in the employee, customer, and course
tables. For each table, you should use the example data and then make up your own
data to reach the number of records required. In addition, you must also add data as
needed to test your queries (Part C) and demonstrate that they are working correctly.
Before you enter data into the tables, do Part B of the project. Lastly, you will need to
create the appropriate relationships between the tables. When creating relationships,
you should ensure that referential integrity is enforced.
PART B – Inputting Data into the Database – Creating Forms
Part B Problem Definition: Isaac discovered that rather than enter data directly into
the tables he could use the MS Access database objects known as forms to enter data
into the database. He used the forms that you created to make data entry more efficient
and less error prone.
Part B Problem Requirements: Use the Form Wizard to create a form that will allow
you to input data into each of the tables. You should have one form for each table.
Although the forms you create are useful tools for entering data, when a customer
makes a purchase, it would be useful to have the option to sign them up for a class.
Ensure to look ahead at the queries and make sure that the data that you input
will fit the criteria of the queries. (Ensure that if a query wants you to retrieve
information about a particular instructor in a particular month that you make an
entry that will satisfy this requirement).
PART C – Creating Different Views of Data for decisions – Creating
Database Queries
Part C Problem Definition: Ms. Sara Johns asked Isaac to find out which employees
are leading which courses and classes, and when they are leading them. She also
wants to know which customers are taking classes and when. Lastly, she wants to be
able to view the data for each instructor and for each month. Although the data stored in
your database is organized into tables, looking at all of the tables to answer her
questions or to help her make decisions is inefficient. Fortunately, MS Access provides
an object, the query, which makes it easy for you to view selected subsets of your data.
In other words, Access makes it easy for you to ask and answer questions about your
data and then use this information to make business decisions.
For the purposes of this project, you can think of a query as a question you ask of your
database. The visually-oriented tool you will use is known as the Query Design tool.
Access provides another tool to create queries, although this tool is actually a computer
language. Structured Query Language (SQL) is a standardized language for querying
databases. With it you can create complex queries of almost any database, however
using SQL is beyond the scope of your project requirements.
Microsoft Access Project: Managing a Small Business
Part C Problem Requirements: Using the Design View, create the following queries.
You may need to add more data to your database so that you can test you query
results.
Query 1 – What customers have signed up to take classes from which instructors?
This query should return the data in your database for all courses, classes
(date, start and end times, location), customers taking classes, and
employees leading classes.
Query 2 – Who is taking and leading a course in July 2019? (This query should
return the course name, classes (date, start and end times, location),
customers taking classes, and employees leading classes for July 2019
only. Be sure to include at least three classes in your data tables that
take place in July 2019 and a few that don’t in order to test your
query.)
Query 3 – What classes is Luca Felton teaching in April 2019? (This query should
return the courses and classes that Luca Felton is leading in April 2019
only. Be sure to include at least one class for Luca in April 2019 and
one that is not in order to test your query.)
Query 4 – What classes are being led by either Anya Shumer or Ramon Tucker in
March 2019? (This query should return all of the courses and classes that
Anya Shumer and Ramon Tucker are leading in March 2019 only. Be
sure to include at least one class for Anya and Ramon in March 2019
and classes for them outside the month of March 2019 in order to
test your query.)
PART D – Outputting Data to Decision Makers and Other Knowledge
Workers – Creating Reports
Part D Problem Definition: Queries are useful tools for asking questions of your data
and for transforming data into information that is useful to decision makers. However,
the standard query table view is not as useful or as easily readable as most users need
or want. Ms. Johns asked Isaac to organize the views of WildOutfitters data and present
it in a well-organized form.
Part D Problem Requirements: To organize the views of WildOutfitters data and
present it in a well-organized form, Isaac will use the MS Access Report object. A
Report can be based on a table or a query or a combination of tables and queries;
however, for Part D of this project you need only to create a report based on Query 2
from Part C above. You can use the Report Wizard to create your report. The report
should show all of the data contained in Query 2. When responding to the Report
Wizard’s prompts, you should select to view your data by class, add “class date” as the
grouping level, and sort the detailed records by customer last name in ascending order.
The layout and style choices are up to you. Finish the report and name it “rptJulyQuery.”
Microsoft Access Project: Managing a Small Business
Review your report and note the “database speak” used for the title and headings. Since
the purpose of a well-crafted report is to effectively and efficiently convey useful
information, you will need to edit your report. Here are the minimum requirements for
Part D of the project.
1. Change the orientation of the report from portrait to landscape.
2. Change the title of the report to “WildOutfitters July 2019 Classes.”
3. Move the customer first name label and data so that they are next to each other,
last name then first name.
4. Transform all field names into easily readable headings, e.g., CourseTitle should
become Course Title, and ClassStartTime should become Start Time, etc.
CIS200 MS Access Project Supplement
(NOTE: NOT the main assignment file!! See the MS Access Project file.)
1. Make sure you thoroughly read the main assignment file, Access_Project.pdf. Keep this document
open/available nearby for reference.
2. Finish the data model, determining which foreign keys are needed:
3. Based on the completed data model, create your tables in MS Access. Create the Class table last.
Make sure you save your tables as labeled in the data model (tblEmployee, tblCustomer, tblCourse,
and tblClass). Use Design View to create and set up your fields, selecting the proper data types.
NOTE: The foreign keys (FK_???) are
wrong as shown. You must set up the
foreign keys based on the data model.
Make sure to select the proper data
type for each field.
Primary keys: AutoNumber
Foreign keys: Number
1
4. Where appropriate, use input masks for your fields.
5. Save your tables as labeled in the data model (tblEmployee, tblCustomer, tblCourse, and tblClass).
6. Create relationships between the tables by going to Database Tools (tab) → Relationships.
7. In the Relationships window, select all tables using the Shift key and selecting all tables.
8. Click “Add”
9. Drag and drop to rearrange the tables.
2
10. Drag and drop from the PK to the corresponding FK to create a relationship.
11. Make sure to select “Enforce Referential Integrity” when you create relationships.
12. When you are finished creating relationships, they should look as follows:
13. Close the Relationships window and SAVE the layout of the relationships.
3
Now you are ready to enter data, and you will do this using forms.
14. Click on the table for which you are creating a form. For every table, there will be a corresponding
form, and you will create one form at a time.
15. Go to the Create tab and select Form Wizard.
16. Click the double arrows to select all fields.
17. Click “Next” and select the layout you want.
18. Click “Next” and relabel the form with the prefix “frm” (e.g., frmCustomer).
19. Click “Finish” and repeat these steps until a form is created for each table.
20. Use the forms to enter data into the tables. (If you happened to have already entered data directly
into the tables, the data should populate in the forms.)
a. Enter data in the Class form LAST.
b. You should enter at least five records for Employee, Course, and Customer.
c. You should enter at least ten records for Class.
21. In the Class form, for the FK fields, you will need to enter the numbers that correspond with the IDs
of the employees, customers, and courses. This is why you enter data into the Class form/table last.
a. For example, if the PK_EmployeeID for Luca Felton is 1, if Luca Felton is to teach a class, you
will enter 1 in the FK_EmployeeID field for the class she is teaching. You do the same to enter
FK data for Customer and Course per class record.
b. Look ahead to the queries (Access_Project.pdf) to make sure your data entry can be used for
the queries.
22. Save all of your data entry. Once you are finished with the data entry, you can move on to the
queries.
4
NOTE: Remember that we perform queries to retrieve meaningful information, so you want to include
fields in your queries that represent meaningful information. It’s fine if you include IDs but you will also
want names and course titles/names, so you can make better sense of the information retrieved.
23. To create a query, go to the Create tab and select “Query Design.”
24. Select all tables using the Shift key and selecting all tables.
25. Click “Add.” The tables should appear with the relationships linking the tables.
26. For Query 1, drag and drop the field names from the tables to the field row at the bottom of the
screen.
27. Click “Run”
to run the query.
28. Check the results to ensure the query returned the information needed.
29. Save the query according to the assignment instructions (qry1). When you close the query window,
you will be prompted to save the query.
5
Starting with Query 2, specific criteria are required for the queries. The next step will demonstrate how
to create Query 4, which is the most complex query. With this knowledge, you should be able to create
Queries 2 and 3.
30. Query 4 asks, “What classes are being led by either Anya Shumer or Ramon Tucker in March 2019?”
In this query, you need to enter criteria on the lower portion of the Query Design screen to return
information that only includes these two instructors teaching in March 2019 (but, before you try this,
read further to learn why the below criteria can be improved).
HOWEVER, while the above query will work, techni …
Purchase answer to see full
attachment

Place your order
(550 words)

Approximate price: $22

Calculate the price of your order

550 words
We'll send you the first draft for approval by September 11, 2018 at 10:52 AM
Total price:
$26
The price is based on these factors:
Academic level
Number of pages
Urgency
Basic features
  • Free title page and bibliography
  • Unlimited revisions
  • Plagiarism-free guarantee
  • Money-back guarantee
  • 24/7 support
On-demand options
  • Writer’s samples
  • Part-by-part delivery
  • Overnight delivery
  • Copies of used sources
  • Expert Proofreading
Paper format
  • 275 words per page
  • 12 pt Arial/Times New Roman
  • Double line spacing
  • Any citation style (APA, MLA, Chicago/Turabian, Harvard)

Our guarantees

Delivering a high-quality product at a reasonable price is not enough anymore.
That’s why we have developed 5 beneficial guarantees that will make your experience with our service enjoyable, easy, and safe.

Money-back guarantee

You have to be 100% sure of the quality of your product to give a money-back guarantee. This describes us perfectly. Make sure that this guarantee is totally transparent.

Read more

Zero-plagiarism guarantee

Each paper is composed from scratch, according to your instructions. It is then checked by our plagiarism-detection software. There is no gap where plagiarism could squeeze in.

Read more

Free-revision policy

Thanks to our free revisions, there is no way for you to be unsatisfied. We will work on your paper until you are completely happy with the result.

Read more

Privacy policy

Your email is safe, as we store it according to international data protection rules. Your bank details are secure, as we use only reliable payment systems.

Read more

Fair-cooperation guarantee

By sending us your money, you buy the service we provide. Check out our terms and conditions if you prefer business talks to be laid out in official language.

Read more

Order your essay today and save 30% with the discount code ESSAYSHELP