information system

INSY 2301 – Information Systems – Fall 2017

Comprehensive Access Assignment

Due Date: Tuesday, November 21, 11pm The policy on late assignments is stated in the syllabus.

Deliverable: You are to upload to Blackboard the Access Database containing all the work required in this

assignment. The database should be named

PLENUM.yourfirstname.yourlastname

where you substitute your first name for yourfirstname and your last name for yourlastname.

This is an individual assignment. Any electronic duplication of another person’s work—in whole or in

part—is expressly prohibited. The Gabelli School Academic Integrity Policy will be invoked in the event of

violations.

Important Advice: Read the entire assignment before beginning to work on it.

Our company, Plenum, Incorporated, which is in the data analytics business, has decided to use Access to

create a relational database to keep track of our employees and clients. Here are the data we have collected:

Employees

Last Name First Name Employee ID Department Annual Salary

Dunaway Faye A1234 Accounting $154,330

Williams Tennessee G2342 Accounting $225,000

Beatty Warren K8495 Shipping $360,000

Your Last Name Your First Name L9901 IT $999,999

Williams Vanessa M8978 Shipping $267,500

Presley Elvis P5544 Sales $1

Williams Serena T9894 Sales $345,000

Kimmel Jimmy U3456 Accounting $77,800

Presley Priscilla Z9876 IT $343,000

Clients

Firm Name Address State Credit

COSTCO 4567 Executive Drive OR Good

Fordham U. 441 E. Fordham Road NY Excellent

Kmart 1010 Upper Sears Drive IL Fair

Target 431 Hanging in There Blvd. CA Poor

Tino's 2410 Arthur Avenue NY Fair

Walmart 1298 Lower Price Street TX Okay

Each client has one representative in our company. Some of our employees are client representatives, while

others are not.

Currently, Elvis Presley has been assigned three clients: Tino’s, Target, and Kmart; Warren Beatty has been

assigned COSTCO and Walmart; and Priscilla Presley has been assigned Fordham.

Your boss has given you the following assignment. Read the whole assignment before you begin.

1. For Employee L9901 replace the first and last names with YOUR first and last name.

2. Create (that is, define the structure of) the necessary tables to define this database in Access. Give the entire database this name: PLENUM.yourfirstname.yourlastname

Be sure to designate appropriate primary keys and to set appropriate field types and sizes. For salary,

use field type currency.

IMPORTANT HINTS:

- Planning the structure of your database--e.g., sketching it out on paper--would be a good way to start before defining it in Access.

- Make sure that if you use a foreign key, it is of the same data type as the field to which you are linking.

- Ask yourself what type of relationship it is (1-1, 1-many, many-to-many?)

- Hint: See Microsoft Access Lab Exercises #2 and #3 if you need help.

3. Create a relationship that is needed for this database. Be sure to enforce referential integrity.

4. Enter the data for employees and client firms into the database if you have not done so already.

5. Use the Query capability to answer the following questions. For each question, include only the information requested and show fields in the order they are listed in the question. Save your query in

the database. (Name your queries by question--e.g., 5a, 5b, and so forth.).

(Hint: Do not worry about the format of the results, but do make sure that all the data show--i.e.,

nothing is chopped off. You can just drag the boundary line that divides the columns in datasheet view

to change the width of columns in a datasheet.)

IMPORTANT: Your queries should be general enough to answer these questions correctly no matter what data are in the database. For example, even if your data already happen to be in the

order requested by a particular question, still perform the sort command. (If this were a large database,

you would have no way of knowing that the data were already in the correct order.)

a. Display, in alphabetical order, the full names and annual salaries of all employees earning more than $165,000 per year. (Hint: Pay attention to first and last names. Another Hint: Enter it as 165000

without $ or ,)

b. Display the full names and annual salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line).

c. Display the full names and MONTHLY salaries of all employees, listing them in order of decreasing salary (i.e., the highest paid person goes first and so on down the line). (Hint: See

pages 156-161 of the (SV) New Perspectives on Microsoft Access lab manual.)

Do not worry if your display for the monthly salary column is not very nice, with lots of decimal

places. But if you want to clean this up, right click in this column in Design View and then choose

"Properties." You will get a "Property Sheet" pane on the right side of your screen. Select

"Currency" as the "Format" and 0 for "Decimal Places."

d. Display the full names and the annual salaries of all employees in Shipping (don’t show the Shipping field).

e. Display a list of departments, showing for each department how many employees it has (that is, the count of employees) and their average salary.

f. For the Accounting department, display the count of employees and their average salary (don’t show the Department field).

g. Display a list of client firms matched with their representatives. (Make sure to display all the fields pertaining to these clients and employees, but do not display any field twice.)

h. Display a list of client firms matched with their representatives and sorted by the representatives' employee IDs. Show only the firm name, the representative's first and last names, and the

representative's ID.

i. For those employees who are client representatives, list (in alphabetical order) their full names and how many firms they are assigned.

6. Be sure to close your database when you are finished. Your database must be closed to upload it to Blackboard.