MIS homework

mark39

MIS3003Chapter8HomeworkInstruction.pptx

Home >Information Systems homework help >MIS homework

MIS 3003 Chapter 8

Homework Instructions

Be careful! All instructions here are not for your homework questions, but just some very similar questions!!!

Question

Assume that you have been given the task of compiling evaluations that your company’s purchasing agents make of their vendors. Each month, every purchasing agent evaluates all of the vendors that he or she has ordered from in the past month on three factors: price, quality, and responsiveness. Assume the ratings are from 1 to 5, with 5 being the best. Because your company has hundreds of vendors and dozens of purchasing agents, you decide to use Access to compile the results.

Question

In this homework, we will learn how to build relationships between three tables, and how to make query for more than one table.

We will learn “GROUP BY” function in queries, then we can do some simple statistical calculation in Access.

Question

Create a database with three tables:

VENDOR (VendorNumber, Name, Contact),

PURCHASER (EmpNumber, Name, Email), and

RATING (EmpNumber, VendorNumber, Month, Year, PriceRating, QualityRating, ResponsivenessRating).

Assume that VendorNumber and EmpNumber are the keys of VENDOR and PURCHASER, respectively. Decide what you think is the appropriate key for RATING. (3pts.)

Please carefully read the RATING table, Primary key(s) should be unique for each of records.

EmpNumber itself? VendorNumber itself? EmpNumber and VendorNumber?

All of above are not correct, because we could have same employee rate same vendor in different date.

Question

Create an empty database, close the default one.

Question

Create a new table (Create->Table Design).

In design view, add VendorNumber, Name, Contact. Make VendorNumber as primary key. （Select VendorNumber, click Design->Primary Key), Data type settings are up to you.

Save the table, name it VENDOR.

Question

Repeat the previous slide for PURCHASER Table and RATING Table. Make EmpNumber as Primary key.

For Rating Table, because you need to have unique identifier for each record, you may want to use EmpNumber, VendorNumber, Month, and Year together as primary key.

Question

Tips:

To make multiple fields as primary key, you could select all of fields, and click Desgin->Primary Key button.

As we discussed before, when you create relationships, you want to have primary key and foreign key have the same data type.

In this homework, we are creating a many to many relationship between VENDOR table and PURCHASER. The RATING table is the intersection table.

If you make VendorNumber and EmpNumber as AutoNumber (Default by Access), then in RATING table, you should make VendorNumber and EmpNumber as number. If you make them as Short Text, you should make VendorNumber and EmpNumber as Short Text.

Month should be text because when you check excel datasheet, you will find the month here is “Apr”, “Sep”, and etc. So it should be short text. For year, you can use either text or number.

Three Ratings must be number, otherwise we cannot do average or other statistic calculation.

Question

Create appropriate relationships

In Database Tools->Relationships. You should connect VendorNumber(VENDOR Table) to VendorNumber(RATING Table), EmpNumber(PURCHASER Table) to EmpNumber(RATING Table).

If you have any problem to create the table, make sure the data type is appropriate.

Question

Import the data in the Excel file Ch08Ex02_U7e.xlsx. Note that data for Vendor, Purchaser, and Rating are stored in three separate worksheets.

External Data->Excel->Ch08Ex02_U7e.xlsx.

Append a copy of the records to the table.

For each table, select each spreadsheet.

If you have problem of import RATING table, just copy and paste the data to access. (The problem is possible because of extra empty cells in excel)

Tips: You must import Rating table last, because you have create the relationship, so RATING table require VENDOR table have the existing VendorNumber, and also require PURCHASER table have EmpNumber

Question

Create a query that shows the names of all vendors and their average scores. Hint: In this and in part e, you will need to use the Group By function in your query.

Create->Query Design-> Select VENDOR table and RATING table.

Double click VendorNumber, Name and Contact fields in VENDOR table, and three Ratings in RATING table.

Uncheck the VendorNumber (We may not require to see the unique identifier in query result.

Click Totals button in the top-right in Design tab.

You will average your ratings for each VendorNumber. So in total row, choose “Group By” for VendorNumber, Name, and Contact, and choose “Avg” for three Ratings.

Run the query you will find the result.

Save the query.

Question

Create a query that shows the names of all employees and their average scores.

Do the same steps as question d. Select Purchaser Table and Rating Table, select fields EmpNumber, Name, Email, and all three Ratings.

Click Total Button.

Choose “Group By” for EmpNumber, Name, Email, and “Avg” for three ratings.

Uncheck the EmpNumber.

Run and save the query.

Question

Using the data created by your queries, what conclusions can you make about vendors or purchasers? (1pt.)

Give your answer for which vendor is the best one, which employee like to give higher scores?

Any other thinking.

Upload to d2l

I will need you to upload only the access database and your question f answers (either put it in submission or put it in a word document) to dropbox.