1. I have uploaded the assignment rubric – Read it .
2. I have uploaded the assignment instructions – READ IT CAREFULLY.
3. I have uploaded 3 videos explaining how to do the assignment in detail – WATCH THE VIDEOS.
4. I have uploaded two excel data files that you need to work on.
5. There is an answer sheet uploaded – you will write your answers in it.
BA 325 Information Literacy Assessment Rubric
Your Information Literacy assignments this term will be graded in a manner similar to how a supervisor would review your work on the job. Doing just what is required of you in a timely and reasonable fashion will let you keep your job (i.e. get some flavor of B grade from 79.5 to 89), but it won’t be enough to get you promoted (i.e. get an A or A- grade 89.5-100). Anything C+ and below means that you are at risk of losing your job because you are not meeting its requirements. See below for the criteria used to assess your assignments:
+ (A to A-) Exceeds expectations; superior work – you have impressed your boss you’re promoted!
· Analysis demonstrates nuanced insight
· Demonstrates strong mastery of the course material through application to the reading
· Appropriate linking of evidence from case & class readings to support your perspective
· Uses course concepts to respond to the question and thereby provides your take on things
· Ongoing synthesis of information
· Where appropriate – linking of real-life experience
· Well-written and follows the Executive Summary Expectations
· You impress me
(B+ to B-) Meets expectations; fulfills the assignment – you are doing a good job.
· Uses appropriate evidence from case & class readings, to support your analysis.
· Attempt to apply course ideas to the reading but the result is less than masterful – more time and analysis is required
· Where appropriate – linking of real-life experience
· Some repetition of ideas, overly wordy, or difficult to understand – meaning that it requires more than one reading to interpret your point
· Follows some, but not all, of the Executive Summary Expectations
- (C+ and below) Does not meet expectations; insufficient for assignment – you need to work harder to keep your job
· Does not link to course concepts
· Explanation of course ideas are weak and/or inaccurate
· Merely repeats descriptive information without engaging in analysis.
· Presents little or no supporting evidence; discuss issue only in terms of personal opinion.
· Writing is problematic (very difficult to understand, many usage and typographical errors)
· Does not follow most, or all, of the Executive Summary Expectations
Executive Summary
Expectations
Guidelines for Writing an Executive Summary
An executive summary is a concise summary of a business report. It restates the purpose of the report, it highlights the major points of the report, and it describes any results, conclusions, or recommendations from the report. Moreover, an executive summary should be aimed at a particular audience, one that is interested in and wants to learn more about the purpose—or message—of the main report. Also, the audience should be able to acquire the information it needs without having to read the whole report.
An Executive Summary Should…
· Be presented as a document that can stand on its own;
· Be one to three pages, depending on the length of the report;
· Be written in a formal tone, avoiding the use of first person pronouns (I, we, our, etc.)
When Writing an Executive Summary, Refer to These Guidelines:
· Clearly state the purpose of the report. Remember that your audience may not have much time, so they should know this information immediately.
· Present the major points in the same order they are written in the report. Organization is key for communicating your message. Also, avoid introducing information that is not addressed in the report.
· Summarize the results, conclusions, or recommendations made in the report. Inform your audience quickly and thoroughly instead of having them guess.
· Use headings as needed, but phrase them differently from those in the report. This will keep your summary organized while avoiding redundant language.
· Format the summary in the same way as the report.
· Reread the summary carefully and ask yourself, “Is my message clear? Did I include key recommendations? Could my audience peruse this without missing the main point? Would I be interested in the full report based on this summary?”
· Proofread and edit.
· Have a non–business person read the summary—a friend, relative, spouse. How did she/he react? What parts were confusing or unclear? Her/his reaction might be similar to that of a business person. Revise as necessary.
BA 325 Database Assignment
In this assignment, you will learn the basics of relational databases and how relational databases are structured and organized. Relational databases are a fundamental building block of modern information technology, and you interact with them on a daily basis (even if you are not aware of it). Relational databases are important ways of storing large amounts of data, and they make it easy for the user to quickly access specific portions of the stored data. In some way you will interact with relational databases in your career, and as such it will be crucial that you know the best ways to interact with them (and their limitations).
Students can be confused when first exposed to databases because they look very similar to spreadsheets, but operate in a completely different manner. For this assignment we will use both MS Excel and MS Access, and we will compare how the two programs accomplish the same tasks. We will start with Excel and use lookup functions to simulate relational database table relationships. We will then take the same data set that we will use in Excel and import it to Access. We will compare the different ways a user would gather specific data in either environment.
Intro Assignment and Superstore Assignment
The Database Assignment includes two smaller assignments: The Intro Assignment and the Superstore Assignment.
You’ll use the Intro Assignment to learn or refresh your understanding of lookup functions in Excel. You will use these functions to show how Excel can be used to gather data from different worksheet tabs, which is similar to how relational database tables communicate with each other. The Intro Assignment has guided instructions at each point, and there are screenshots and answers below all the questions. This will allow you to check your work and ensure you understand before moving on. While you go through the Intro Assignment you will be required to take 6 Screen Clippings that you will paste in the
Database Assignment Answer Sheet
.
After you complete the Intro Assignment you will begin the Superstore Assignment. As you work through the Superstore Assignment you will be prompted with questions. You will answer these questions in the
Database Assignment Answer Sheet
. Like the Intro Assignment, the Superstore Assignment has a ‘Check Figure’ section below questions 8-16. In the ‘Check Figure’ section, there are answers that you can compare to your database queries. If you get the same answers as the ‘Check Figure’ answer, it is likely your other answers for that question will be correct. While you go through the Superstore Assignment, you will be required to take 7 Screen Clippings that you will paste in the
Database Assignment Answer Sheet
.
Once you have completed both the Intro Assignment and the Superstore Assignment, and have completely filled out the
Database Assignment Answer Sheet
, you can upload your
Database Assignment Answer Sheet
Word document to the Assignments folder in D2L.
Note: I have Highlighted the beginning of each section in two different colors to denote which application you will use through that section. Yellow corresponds to Excel, and Green corresponds to Access. The very last section uses both applications, hence why it is highlighted with both colors. This is meant to help Mac users plan their time better.
Requirements
1. Intro Assignment: Follow the instructions for the Intro Assignment. Take the required 6 Screen Clippings and paste them in the designated locations in the
Database Assignment Answer Sheet
.
2. Superstore Assignment: Follow the instructions for the Superstore Assignment. Take the required 7 Screen Clippings and paste them in the designated locations in the
Database Assignment Answer Sheet
.
3. Upload your completed
Database Assignment Answer Sheet
to the Assignments folder in D2L. Name your file:
BA 325 Firstname Lastname
(use your actual name).
Data Sets
Download the following files from the Database Assignment in D2L:
· Database Assignment Answer Sheet x
· Intro Data.xlsx
· Superstore Data.xlsx
In the Excel spreadsheets, you will find ordering data for two different companies. The Intro Data contains sales related data for a company that distributes sporting goods/office equipment. The Superstore Data is an open source data set used by Tableau and contains even more detailed sales information for a national furniture distribution company.
Basic Data Dictionary for the Intro Data:
·
V#:
The Vendor ID number.
·
Vendor:
The name of the Vendor.
·
Vendor Street:
The Vendor’s street address.
·
Vendor City:
The Vendor’s City/State/Zip.
·
PO#:
The Purchase Order number.
·
PO Date:
The date the Purchase Order was received.
·
Shipper:
The Shipping company used for the order.
·
Qty:
The quantity of the specific item on the Purchase Order.
·
Item#:
The Item number
·
Item Description:
The description of the specific item.
·
Cost:
The Cost for the item (each).
·
E#:
The Employee number who processed the order.
·
Signed by:
The Employee name who processed the order.
Basic Data Dictionary for the Superstore Data:
·
Customer ID (PK):
The Customer ID number. Also, the Primary Key of the Customer Table.
·
Customer Name:
The name of the Customer.
·
Segment:
The group the Customer is associated with.
·
Employee ID (PK):
The Employee number who processed the order. Also, the Primary Key of the Employee Table.
·
Employee:
The Employee name who processed the order.
·
Sales Region:
The Region of the US the Employee is responsible for.
·
Order ID (PK):
The Order ID number. Also, the Primary Key of the Order Table.
·
Customer ID (FK):
The Customer ID Foreign Key. Found in the Order table and connects to the Customer ID (PK).
·
Employee ID (FK):
The Employee ID Foreign Key. Found in the Order table and connects to the Employee ID (PK).
·
Order Date:
The date the order was placed.
·
Ship Date:
The date the order was shipped.
·
Ship Mode:
The class of shipping used.
·
Country:
The Country the order is shipped to.
·
State:
The State the order is shipped to.
·
City:
The City the order is shipped to.
·
Postal Code:
The Postal Code the order is shipped to.
·
OrderLine ID (PK):
The OrderLine ID number. Also, the Primary Key of the OrderLine ID Table.
·
Order ID (FK):
The Order ID Foreign Key. Found in the OrderLine ID table and connects to the Order ID (PK).
·
Product ID (FK):
The Product ID Foreign Key. Found in the OrderLine ID table and connects to the Product ID (PK).
·
Quantity:
The quantity of the specific item on the OrderLine.
·
Sales:
The sale price of the specific item on the OrderLine.
·
Discount:
Any discount applied to the specific item on the OrderLine.
·
Profit:
The profit for the specific sale of the specific item on the OrderLine.
·
Profit Ratio:
The profit ratio for the specific sale of the specific item on the OrderLine
·
Order Returned:
Whether the specific item on the OrderLine was returned.
·
Product ID (PK):
The Product ID number. Also, the Primary Key of the Product Table.
·
Product Name:
The name of the Product.
·
Manufacturer:
The Manufacturer of the Product.
·
Category:
The main Category the Product falls under.
·
Sub-Category:
The Sub-Category the Product falls under.
Tools
There are two additional tools that you will use to accomplish these assignments. The Remote access to Microsoft Access is only necessary if you do NOT have MS Access on your computer (MS Access does not run on Macs):
Screen Clipping
There are a few ways to take a Screen Clipping of your Excel file and paste it into your Word document:
1. To do this from within your Word document:
· Select Insert
· Screenshot
· Screen Clipping
· Then click and drag your cursor to highlight the area you want to clip.
2. Alternatively select the Snipping Tool app from your Windows menu:
· Select New
· Click and drag to highlight the area you want to clip
· Select Copy
· Then move to your Word document and select CTRL+V to paste the clip.
INTRO ASSIGNMENT
Open the Excel file called Intro Data.xlsx. We’ll use this data to get comfortable with lookup functions in Excel. After that we will explore the Superstore Data, and then upload the Superstore Data file to Access so we can see how a relational database is similar (and different) to a spreadsheet.
The first step in any analytics assignment is to adopt an analytics mindset. Think about what you already know about the context and what questions you would like to answer as you analyze the data. Explore the spreadsheet and look at the data. In the OriginalData tab you’ll see the complete details for all the Purchase Orders. You should get comfortable with all the fields and see if you see any trends (for example, there are only two distinct vendors, and two distinct employees). Look through the remainder of the Purchase Order data to see if you notice anything else.
You should also notice the additional tabs in the worksheet. The data contained in the PO’s have been separated into individual tabs that contain the specific data relevant to each category. These tabs are organized in a similar fashion to tables in a relational database. Take a look at the tabs to see what is contained in each tab, and if it lines up with what you initially saw in the OriginalData tab.
As you work through the instructions below, think about what you can learn from the data, and after completing the assignment, do some additional exploring on your own. Also take your time to understand the analytical capabilities you’re using in Excel and Access and think how they can be used in business. The tools you learn, and practice here, will be helpful in many classes in your business program, and especially in your business career!
Requirements
Work through the instructions below. As you work, you’ll be asked to:
· Follow the steps to perform the required analysis.
· Paste 6 Screen Clippings into the
Database Assignment Answer Sheet
.
The Intro Assignment is to get you comfortable with Lookup functions in Excel, and how relational databases are structured and act. You’ll likely be going in more depth in both Excel and Access, so be prepared to struggle a little. If you are already experienced with Lookup functions and relational databases, the Intro Assignment will be a nice refresher. Answers are provided so that you can check your work. Have fun!
Section 1: VLOOKUP Introduction
1) General VLOOKUP Explanation
Lookup functions in Excel can be a powerful tool to gather specific data you are looking for. The most common one utilized is VLOOKUP, and it is generally used to find and retrieve some data from a reference table. This could be used to retrieve the address of a customer (from a customer data table), and then added to an order (using their name as the lookup reference).
Hopefully this sounds similar to how table relationships in a relational database operate. It is much simpler than how table relationships work, but as a big picture comparison it is a start. The lookup reference functions like a foreign key, in the sense that it connects you back to the reference table. The VLOOKUP function then gathers and retrieves the data from a specific column from the reference table. With VLOOKUP you are only able to retrieve one entry, while the foreign key/primary key connection makes it easy to gather more than one entry.
The basic syntax for VLOOKUP is (See Figure 1 for visual representation):
VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)
·
lookup_value
: this is the value whose information you want to gather. In our above example it would be our customer name.
·
table_array
: this is the reference table, which contains all our base, stored data. In our above example it would be our customer data table.
·
col_index_num
: this is the specific column in the reference table that contains the data you would like to retrieve. In our above example, if we wanted to retrieve a specific customer’s zip code, we would need to specify the column that contained the zip code data.
· IMPORTANT NOTE: VLOOKUP has a limitation that it only looks left to right, with the left most column being column 1, with column numbers increasing as you move to the right.
·
range_lookup
: this has to do with the type of match you are looking for. Do you want an exact match to your lookup value, or can it be a partial match? In most cases you will want an exact match. If you want an exact match you enter FALSE, while if you are ok with a partial match you enter TRUE.
Figure 1
Figure 2 shows a more simplified view of what VLOOKUP is doing:
Figure 2
2) Examine the data on the VLOOKUP tab.
· The data contains fictional sales data for a company.
· For this section of the assignment, we’ll state numbers in dollars, so we’ll write 1,592 as $1,592.
· Notice in column F there are 5 questions that we will answer using VLOOKUP.
Question 2: How many Fields are there in the Data? How many Rows are there?
4 Fields. 13 Rows.
3) Find Sales results for one employee.
· Click cell G5.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Type “John” for your
lookup_value
· Select the entire data table (A4:D17) for your
table_array
.
· Type 3, which corresponds to Net Sales, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· Your cell should now have the Net Sales for John.
Question 3: What were the Net Sales for John?
1088
4) Find the number of customers for one employee.
· Click cell G6.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Type “Jamie” for your
lookup_value
· Select the entire data table (A4:D17) for your
table_array
.
· Type 2, which corresponds to No. Customers, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· Your cell should now have the Number of Customers for Jamie.
Question 4: How many customers did Jamie have?
9
5) Find the profit for one employee.
· Click cell G7.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Type “Jessy” for your
lookup_value
· Select the entire data table (A4:D17) for your
table_array
.
· Type 4, which corresponds to Profit/Loss, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· Your cell should now have the Profit/Loss for Jessy.
Question 5: What is the profit for Jessy?
236
6) Find the number of customers for an employee NOT in the table.
VLOOKUP can only retrieve an entry if it has something to connect to. If you enter in a
lookup_value
that doesn’t have a match in the reference table, you will get an error.
· Click cell G8.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Type “Joshua” for your
lookup_value
· Select the entire data table (A4:D17) for your
table_array
.
· Type 2, which corresponds to No. Customers, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· Your cell should now have #N/A.
This shows there was an error with the function you entered in. VLOOKUP tried to look for the name “Joshua” and couldn’t find it in your column. If you get this error in a normal situation it either means you have a typo, or the entry doesn’t exist in your reference table.
Question 6: How many customers did Joshua have?
#N/A
7) Use IFERROR to display a message if VLOOKUP doesn’t find your entry.
To do this we will use a logical function and a lookup function together. When I am using logical functions with other functions, I like to build outward. This means I am working somewhat backwards, but it helps me troubleshoot errors if they come up.
· Click cell G9.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Type “Joshua” for your
lookup_value
· Select the entire data table (A4:D17) for your
table_array
.
· Type 2, which corresponds to No. Customers, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· Your cell should now have #N/A.
· Now click cell G9 again.
· First place a parenthesis to the left of the VLOOKUP.
· Now add IFERROR between the equal sign and the parenthesis left of the VLOOKUP.
· Now add a comma to the right of the far-right parenthesis.
· Now add “Not Found :-(“ (or some other humorous message).
· Close the function by adding a parenthesis to the right of your message and hit enter.
· Your cell should now have Not Found :-(, or whatever humorous message you chose. The IFERROR function can be nice because it gives a better visual indicator of why your function did not work, and can be helpful when you are using logical/lookup functions on large datasets.
Question 7: How many customers did Joshua have? Without an Error message showing.
Not Found 🙁
Intro Assignment Screen Clipping 1
· Take a Screen Clipping of all your Questions from the VLOOKUP Practice and paste it below ‘Intro Assignment Screen Clipping 1’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 1:
Intro Assignment Screen Clipping 2
· Go to the Formulas tab on the Ribbon.
· Click the Show Formulas button.
· Take a Screen Clipping of all your Questions from the VLOOKUP Practice with the formulas showing, and paste it below ‘Intro Assignment Screen Clipping 2’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 2:
Section 2: Applied VLOOKUP
8) Review other data
· Review the other tabs in the file. These tabs correspond to fictional data from a sporting goods/office equipment distribution company.
· Look at the OriginalData tab. This tab contains all the data from a series of orders.
· You should also see that the Row IDs are color coded with five colors. They have been color-coded based on the table they will be in.
· There are additional tabs (POLineItems, PO, Inventory, Vendor, and Employee) along the bottom of the worksheet that have the data sorted by their eventual table location. Notice, those tabs have the same colors as the Row IDs.
· In each of the additional tabs you will see only the relevant fields, and you should see the Primary Keys (PK) and Foreign Keys (FK). Note: Not all tables need FKs, as the FKs are only required to connect back to a specific PK where there is a table relationship in place.
· Where there are FKs, they have been color coded to indicate which table they connect back to.
· These fields are organized based on the data model for the Intro data (See Figure 3)
Figure 3
· Look at the POLineItems tab. This tab contains the data from the specific Purchase Order item lines. If this data were organized in a relational database, this would represent one table.
· Notice the PO# (FK) and Item# (FK) columns. These fields correspond to Purchase Order# and Item#, and will be used to link the POLineItems data with the Purchase Order data and Item data. They will function for us like a Foreign Key, in the sense that they will allow us to retrieve data from another location.
· Also notice the POLine# (PK) column. This field is functioning similarly to a Primary Key, and is a unique identifier for this data.
· Look at the PO tab. This tab contains the general data from the Purchase Orders. If this data were organized in a relational database, this would represent another table.
· Notice the E# (FK) and V# (FK) columns. These fields correspond to Employee# and Vendor#, and will be used to link the PO data with the Employee data and Vendor data. They will function for us like a Foreign Key, in the sense that they will allow us to retrieve data from another location.
· Also notice the PO# (PK) column. This field is functioning similarly to a Primary Key, and is a unique identifier for this data. It also is the connection used in the POLineItems tab to retrieve the Purchase Order data.
· Look at the Inventory tab. This tab contains the Item specific data. If this data were organized in a relational database this would represent one table as well.
· Notice the Item# (PK) column. This field is functioning similarly to a Primary Key, and is a unique identifier for this data. It also is the connection used in the POLineItems tab to retrieve the Item data.
· Look at the Vendor tab. This tab contains the Vendor specific data. If this data were organized in a relational database this would represent one table as well.
· Notice the V# (PK) column. This field is functioning similarly to a Primary Key, and is a unique identifier for this data. It also is the connection used in the PO tab to retrieve the Vendor data.
· Look at the Employee tab. This tab contains the Employee specific data. If this data were organized in a relational database this would represent one table as well.
· Notice the E# (PK) column. This field is functioning similarly to a Primary Key, and is a unique identifier for this data. It also is the connection used in the PO tab to retrieve the Employee data.
Question 8: How many Fields are there in the OriginalData? How many Rows are there? How many Individual Values are there? (Note: When calculating individual values be sure to NOT include the column headers in your count)
14 Fields. 16 Rows. 224 Individual Values.
9) Start Recreating the complete Intro Purchase Order
When rebuilding the full Purchase Order, it makes sense to start from the “Table” that connections work outward from. See the data model below to see how this data would be structured in a relational database. Based on the data model we will start with the POLineItems data.
· The OriginalData tab has all the details from the Purchase Order. We will use VLOOKUP to recreate the complete Purchase Order in the OrderDetails tab.
· Click the POLineItems Tab.
· Select A2:E17 and Copy your selection.
· Click the OrderDetails Tab.
· Click cell J2 and Paste your previous selection.
Question 9: How many Fields are there in the POLineItems Data? How many Rows are there? How many Individual Values are there?
5 Fields. 16 Rows. 80 Individual Values.
10) Add in the Item data
· Select cell P2.
· Go to the formulas tab and click Insert Function. Select VLOOKUP.
· Click the Up Arrow to the right to allow you to select from the tab. Click cell L2 for your
lookup_value
and hit enter.
· Click the Up Arrow to the right to allow you to select from the tab. Click the Inventory tab, select the entire data table (A1:B8) for your
table_array
and hit enter.
· Type 2, which corresponds to Item Description, for your
col_index_num
.
· Type FALSE, which gives an exact match, for your
range_lookup
.
· Click OK
· You should see
Basketball pole pad
in cell P2.
· To allow you to autofill the rest of the results you need to make two small function modifications.
· Click your VLOOKUP function and change L2 to $L2, and A2:B8 to $A$2:$B$8.
· The mixed reference for your
lookup_value
and the absolute reference for your
table_array
are important for re-using your function to retrieve different data.
· Autofill down to P17.
Question 10: How many Fields are there in the Inventory Data? How many Rows are there? How many Individual Values are there?
2 Fields. 7 Rows. 14 Individual Values.
11) Add in the PO data
· Select cell F2.
· Create a VLOOKUP in cell F2 that has the following:
· $K2 for the
lookup_value
· PO!$A$2:$E$7 for the
table_array
· 2 for the
col_index_num
· FALSE for the
range_lookup
· If you have created the function using the mixed reference and absolute reference mentioned above, you can copy cell F2 and paste it in cells G2, H2, and I2.
· Modify the function in G2 so the
col_index_num
is 3.
· Modify the function in H2 so the
col_index_num
is 4.
· Modify the function in I2 so the
col_index_num
is 5.
· You should now have
41624
showing in cell F2,
Interstate Motor Freight
showing in cell G2,
1
showing in cell H2, and
252
showing in cell I2.
· Autofill these cells down to row 17.
· You will notice odd results for the PO Date column (the results will be five-digit numbers, and not dates).
· Select the column and change the number format from General to Short Date.
Question 11: How many Fields are there in the PO Data? How many Rows are there? How many Individual Values are there?
5 Fields. 6 Rows. 30 Individual Values.
12) Use the PO data to add in the Employee data
· Select cell R2.
· Create a VLOOKUP in cell R2 that has the following:
· $H2 for the
lookup_value
· Employee!$A$2:$B$3 for the
table_array
· 2 for the
col_index_num
· FALSE for the
range_lookup
· You should now have
Ray Kramer
by showing in cell R2.
· Autofill down to row 17.
Question 12: How many Fields are there in the Employee Data? How many Rows are there? How many Individual Values are there?
2 Fields. 2 Rows. 4 Individual Values.
13) Use the PO data to add in the Vendor data
· Select cell B2.
· Create a VLOOKUP in cell B2 that has the following:
· $I2 for the
lookup_value
· Vendor!$A$2:$D$3 for the
table_array
· 2 for the
col_index_num
· FALSE for the
range_lookup
· If you have created the function using the mixed reference and absolute reference mentioned above, you can copy cell B2 and paste it in cells C2, and D2.
· Modify the function in C2 so the
col_index_num
is 3.
· Modify the function in D2 so the
col_index_num
is 4.
· You should now have
Velocity Sporting Goods
showing in cell B2,
1258 Colgrove Ave
showing in cell C2, and
Pierre, SD 57501
showing in cell D2.
· Autofill these cells down to row 17.
Question 13: How many Fields are there in the Vendor Data? How many Rows are there? How many Individual Values are there?
4 Fields. 2 Rows. 8 Individual Values.
14) Compare number of Fields, Rows, and Individual Values between the OriginalData and the data when it is stored in separate tabs
· Add the total Fields/Rows/Individual Values from all the separated tabs (Questions 9-13).
· Take note of the difference between the separated tabs and the OriginalData.
· Is this the result you expect? Why or why not?
· What do you think this says about the storage efficiency of one method vs another?
Question 14: How many Fields/Rows/Individual Values are there in the OriginalData? How many Fields/Rows/Individual Values are there between all the separated tabs? Which group is greater?
The OriginalData has: 14 Fields, 16 Rows, and 224 Individual Values.
The separated tabs have: 18 Fields, 33 Rows, and 136 Individual Values.
15) Adjust OrderDetails tab to align with the OriginalData tab
· Click the OrderDetails tab. Notice some of the columns don’t match exactly
· Highlight Column I (V# (FK)) and drag it to Column A.
· Highlight Column K (PO# (FK)) and drag it to Column E.
· Highlight Column H (E# (FK)) and drag it to Column Q.
· Highlight Column M (Qty) and drag it to Column K.
· Highlight Column P (Item Description) and drag it to Column M.
· Highlight Columns H and I and delete them.
· Highlight Column M and N and delete them.
· Your results in the OrderDetails tab should look identical as the Original Data tab (except the added (FK) to the ID fields).
· Note: This added effort moving things around has a lot to do with us using a flat file to accomplish some of the work of a relational database. It would be easier, and more efficient if we were doing this in a relational database.
Intro Assignment Screen Clipping 3
· Take a Screen Clipping of your adjusted OrderDetails tab and paste it below ‘Intro Assignment Screen Clipping 3’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 3:
Intro Assignment Screen Clipping 4
· Go to the Formulas tab on the Ribbon.
· Click the Show Formulas button.
· Take a Screen Clipping of your adjusted OrderDetails tab from Column A to Column G with the formulas showing, and paste it below ‘Intro Assignment Screen Clipping 4’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 4:
Section 3: Create your Intro Database
15) Import the Intro Data in MS Access
· We are going to go through the steps to import the Intro Data into MS Access, and setup the appropriate table relationships. We will go more in depth in the Superstore Assignment.
· Open MS Access.
· Create a Blank Database (the name does not matter for now).
· Click the External Data tab on the Ribbon.
· Click New Data Source -> From File -> Excel.
· Click Browse and find the location that you have the Intro Data file saved.
· Select it and click ok.
· In the top box you have a list of all the tabs found in the Intro Data file.
· Select the POLineItems tab and click Next.
· Make sure the First Row Contains Column Headings box is checked and click Next.
· You can leave the Field Options section alone and click Next.
· Select the option to Choose your own primary key, and make sure POLine# (PK) is selected.
· Leave the name unmodified (POLineItems) and click Finish and then Close.
Question 15: How many Fields are there in the POLineItems Table? How many Rows are there? How does it compare to the results from Question 9?
5 Fields. 16 Rows. Same results as Question 9.
16) Import the PO tab in MS Access
· The steps here will be identical to the import of the POLineItems tab except using the PO tab.
· Click the External Data tab on the Ribbon.
· Click New Data Source -> From File -> Excel.
· Click Browse and find the location that you have the Intro Data file saved.
· Select it and click ok.
· In the top box you have a list of all the tabs found in the Intro Data file.
· Select the PO tab and click Next.
· Make sure the First Row Contains Column Headings box is checked and click Next.
· You can leave the Field Options section alone and click Next.
· Select the option to Choose your own primary key, and make sure PO# (PK) is selected.
· Leave the name unmodified (PO) and click Finish and then Close.
Question 16: How many Fields are there in the PO Table? How many Rows are there? How does it compare to the results from Question 11?
5 Fields. 6 Rows. Same results as Question 11.
17) ) Import the Inventory tab in MS Access
· The steps here will be identical to the import of the POLineItems tab except using the Inventory tab.
· Click the External Data tab on the Ribbon.
· Click New Data Source -> From File -> Excel.
· Click Browse and find the location that you have the Intro Data file saved.
· Select it and click ok.
· In the top box you have a list of all the tabs found in the Intro Data file.
· Select the Inventory tab and click Next.
· Make sure the First Row Contains Column Headings box is checked and click Next.
· You can leave the Field Options section alone and click Next.
· Select the option to Choose your own primary key, and make sure Item# (PK) is selected.
· Leave the name unmodified (Inventory) and click Finish and then Close.
Question 17: How many Fields are there in the Inventory Table? How many Rows are there? How does it compare to the results from Question 10?
2 Fields. 7 Rows. Same results as Question 10.
18) ) Import the Vendor tab in MS Access
· The steps here will be identical to the import of the POLineItems tab except using the Vendor tab.
· Click the External Data tab on the Ribbon.
· Click New Data Source -> From File -> Excel.
· Click Browse and find the location that you have the Intro Data file saved.
· Select it and click ok.
· In the top box you have a list of all the tabs found in the Intro Data file.
· Select the Vendor tab and click Next.
· Make sure the First Row Contains Column Headings box is checked and click Next.
· You can leave the Field Options section alone and click Next.
· Select the option to Choose your own primary key, and make sure V# (PK) is selected.
· Leave the name unmodified (Vendor) and click Finish and then Close.
Question 18: How many Fields are there in the Vendor Table? How many Rows are there? How does it compare to the results from Question 13?
4 Fields. 2 Rows. Same results as Question 13.
19) ) Import the Employee tab in MS Access
· The steps here will be identical to the import of the POLineItems tab except using the Employee tab.
· Click the External Data tab on the Ribbon.
· Click New Data Source -> From File -> Excel.
· Click Browse and find the location that you have the Intro Data file saved.
· Select it and click ok.
· In the top box you have a list of all the tabs found in the Intro Data file.
· Select the Employee tab and click Next.
· Make sure the First Row Contains Column Headings box is checked and click Next.
· You can leave the Field Options section alone and click Next.
· Select the option to Choose your own primary key, and make sure E# (PK) is selected.
· Leave the name unmodified (Employee) and click Finish and then Close.
Question 19: How many Fields are there in the Employee Table? How many Rows are there? How does it compare to the results from Question 12?
2 Fields. 2 Rows. Same results as Question 12.
20) Establish the Table Relationships
· First, Save your file! (You will need to use Save As).
· Click Database Tools on the Ribbon, then Relationships.
· Highlight all the Tables, click Add, then Close.
· Notice that all the tables have a key symbol next to their Primary Keys (which is how MS Access denotes Unique Identifiers).
· Click your tables individually and move them to the following orientation:
· Click the Edit Relationships tab, then Create New. For each relationship we will establish the
Left Table Name
will be the table with the FK that is retrieving something from the reference table. The
Right Table Name
will be the table with the corresponding PK. The
Left Column Name
will be the specific FK you are using, and the
Right Column Name
will be the specific PK you are connecting to.
· The PO -> Employee relationship setup looks like this:
· The PO -> Vendor relationship setup looks like this:
· The POLineItems -> PO relationship looks like this:
· The POLineItems -> Inventory relationship looks like this:
· You have now established all the necessary relationships in your relational database. From here you can run Queries/Reports to retrieve data that fulfill your specified criteria.
Intro Assignment Screen Clipping 5
· Take a Screen Clipping of your finalized Relationships and paste it below ‘Intro Assignment Screen Clipping 5’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 5:
21) Create a Query to match the OrderDetails tab from above
· Click the Create tab on the Ribbon.
· Then click Query Wizard.
· Click Simple Query Wizard.
· You will now be able to select your desired Fields from any of the Tables in your database.
· Note: The order we select the Fields is important to make sure our Query has the right orientation. Review the screenshot below to see the order we want the fields.
· In this case we will replace all the Foreign Keys with their corresponding Primary Keys.
· Select the following Fields from the Vendor Table:
· Select the following Fields from the PO Table:
· Select the following Fields from the POLineItems Table:
· Note: You must select a Field from this Table after the Inventory Table as well.
· Select the following Fields from the Inventory Table:
· Select the following Field from the POLineItems Table:
· Select the following Fields from the Employee Table:
· Click Next
· Name your Query
OrderDetails Query
and click Finish.
Question 21: How many Fields are there in the OrderDetails Query? How many Rows are there? How does it compare to the results from Intro Assignment Screen Clipping 3?
14 Fields. 16 Rows. Same results as Screen Clipping 3.
Intro Assignment Screen Clipping 6
· Take a Screen Clipping of your answer for Question 21 and paste it below ‘Intro Assignment Screen Clipping 6’ in your
Database Assignment Answer Sheet
Word document.
· Your Screen Clipping should look like the one below.
Intro Assignment Screen Clipping 6:
3
SUPERSTORE ASSIGNMENT
Relational Database Introduction
At this point we will transition to investigate relational databases with more depth. As a reminder here are some of the most important definitions that apply to relational databases:
· Row: A data set representing a single item.
· Column: A specific attribute or field related to the table.
· Individual Value: The contents of an individual cell.
· Table: A collection of related data.
· Primary Key: A primary key uniquely specifies a row within a table. For an attribute to be a good primary key it must not repeat.
· Foreign Key: A foreign key is a field in a relational table that matches the primary key column of another table. It relates the two tables.
· Relationship: The type of connection between two tables. In this class we will generally see one to one, one to many, and many to many relationships. Note: Many to many relationships create issues within a relational database, so you need to create an intermediate relationship.
While we have used the lookup functions to show how one area of a worksheet can communicate with another area, the relationships in relational databases are much more robust. If you add a new record to a reference table in Excel, you will have to go back to all your lookup functions and manually change the
table_array
size. On the other hand, in a relational database, because the tables themselves are connected, the relationship can update as the contents of the tables change.
The nature of how relational databases work also makes it much easier to gather specific data of interest. When we used the lookup functions above, we were just gathering everything to re-create the Purchase Order. What if we only wanted specific entries? Maybe one Purchase Order number in particular? Maybe all the Purchase Orders that had a specific item?
It is possible to do all of these things in a flat-file, but it is much more work. Once you have your data stored in a relational database, and your table relationships set, it is very easy to dynamically gather select data.
In this assignment you will use VLOOKUP in the same way as the Intro Assignment to recreate the PO in the Superstore Data file. You will then import the tabs from the Superstore Data into MS Access to create tables in a database. You will then create the necessary relationships between the tables. Finally, you will use Queries in Access, and Filter/Sort in Excel to retrieve data that fulfills a specific criterion.
Requirements:
Work through the instructions below. As you work, you’ll be asked to:
· Follow the steps to perform the required analysis.
· Answer questions. You will save your answers to these questions in the
Database Assignment Answer Sheet
.
· Paste 7 Screen Clippings into the
Database Assignment Answer Sheet
.
Many of the instructions in the Superstore Assignment are very similar to those found in the Intro Assignment. If you get stuck, you should use the Intro Assignment as a resource to help you get un-stuck. Questions 8-16 have a ‘Check Figure’ section below each question. In the ‘Check Figure’ section, there are some answers that you can compare to your work. If you get the same answers as the ‘Check Figure’ answer, it is likely that your answers to the actual question will be correct. Remember to fill out your answers in the
Database Assignment Answer Sheet
. Have fun!
Section 1: Examine the Superstore Data.
· Open the Superstore data file and look at the Order Info tab.
· The Superstore data file is a fictional open source dataset that contains a variety of order data for a company that does wholesale equipment sales across the US.
· You should see 22 different fields that correspond to a variety of different categories.
· You should also see that there are 9994 rows (the top row doesn’t count as it is the attribute name).
· You should also see that the Row IDs are color coded with five colors. They have been color-coded based on the table they will be in.
· There are additional tabs (Customer, Employee, Order, OrderLine ID, and Product) along the bottom of the worksheet that have the data sorted by their eventual table location. Notice, those tabs have the same colors as the Row IDs.
· In each of the additional tabs you will see only the relevant fields, and you should see the Primary Keys (PK) and Foreign Keys (FK). Note: Not all tables need FKs, as the FKs are only required to connect back to a specific PK where there is a table relationship in place.
· Where there are FKs, they have been color coded to indicate which table they connect back to.
· Click the Order Recreation tab.
· You should see all the fields from all the color-coded tabs (Customer, Employee, Order, OrderLine ID, and Product).
· These fields are organized based on the data model for the Superstore data (See Figure 4)
Figure 4
1) How many Fields/Rows/Individual Values are found in the Order Info tab?
2) How many Fields/Rows/Individual Values are found in the Customer tab?
3) How many Fields/Rows/Individual Values are found in the Employee tab?
4) How many Fields/Rows/Individual Values are found in the Order tab?
5) How many Fields/Rows/Individual Values are found in the OrderLine ID tab?
6) How many Fields/Rows/Individual Values are found in the Product tab?
7) Are there more/less Individual Values in the Order Info tab, when compared to the total Individual Values found in the Customer/Employee/Order/OrderLine ID/Product tabs? How many more/less?
Section 2: Re-create your Superstore Order.
· Here, you will use the same process used in the Intro Assignment to re-create your Order.
· The OrderLine ID data is already added to the Order Re-creation tab.
· Start by using the OrderLine ID data to retrieve the data from the tables directly connected to it in the data model (Figure 4).
· Use the fields labeled with a ‘FK’ to connect to the corresponding fields labeled with a ‘PK’ to retrieve the desired data.
· Note: Remember to use a mixed reference for your
lookup_value
, and an absolute reference for your
table_array
. This will ensure you can paste your formula and only must make minor adjustments.
· After you retrieve the data from the tables connected to the OrderLine ID table, use the added FKs to populate the remaining tables.
8) What Fields are in Columns AB and AC? What are the values in AB317 and AC317 once you have retrieved the Product Data?
· Reminders:
· You will need to populate the Fields corresponding to the Product tab (Columns AA through AD).
· You will need to use the Product ID (FK) as your
lookup_value
and the data contained in the Product tab as your
lookup_array
.
Check Figure: What are the values in AB2048 and AC4096
AB2048 is Other
AC4096 is Office Suppliers.
9) What Fields are in Columns K and N? What are the values in K951 and N951 once you have retrieved the Order Data?
· Reminders:
· You will need to populate the Fields corresponding to the Order tab (Columns H through P).
· You will need to use the Order ID (FK) as your
lookup_value
and the data contained in the Order tab as your
lookup_array
.
· Remember to change the number format on both date columns once you have autofilled them.
Check Figure: What are the values in K2048 and N4096
K2048 is 2/6/2016
N4096 is Texas.
10) What Fields are in Columns E and F? What are the values in E317 and F317 once you have retrieved the Employee Data?
· Reminders:
· You will need to populate the Fields corresponding to the Employee tab (Columns E through F).
· You will need to use the Employee ID (FK) as your
lookup_value
and the data contained in the Employee tab as your
lookup_array
.
Check Figure: What are the values in E2048 and F4096
E2048 is Anna Andreadi.
F4096 is Central.
11) What Fields are in Columns B and C? What are the values in B951 and C951 once you have retrieved the Customer Data?
· Reminders:
· You will need to populate the Fields corresponding to the Customer tab (Columns B through C).
· You will need to use the Customer ID (FK) as your
lookup_value
and the data contained in the Customer tab as your
lookup_array
.
Check Figure: What are the values in B2048 and C4096
B2048 is Charles Crestani.
C2048 is Consumer.
Superstore Assignment Screen Clipping 1
· Re-orientation of Order Re-creation tab
· Compare your results in the Order Re-creation tab to the Order Info tab. You should notice you have a few additional columns (26 vs 22), and your columns are organized in a different order.
· First, highlight Columns A through H and Insert Column. This will give you the space to move things around easily.
· Reorganize your columns by the same general organization found in the Order Info tab.
· Right click and Hide each of the extra columns (All of the FKs other than Order ID (FK)).
· it is important to Hide them, and not delete them since many of your VLOOKUPs are using the results of these columns. If you were to delete them, many of your formulas would error.
· Take a Screen Clipping showing the complete top row, and at least 10 rows of data and paste it below ‘Superstore Assignment Screen Clipping 1’ in your
Database Assignment Answer Sheet
Word document.
Section 3: Create your Superstore Database
· Here, you will use the same process used in the Intro Assignment to import your Superstore Data into MS Access.
· Import each of the colored tabs (Customer, Employee, Order, OrderLine ID, and Product) into MS Access.
· Give each Table the same name as the Tab that was imported.
· Establish the Table Relationships
using the Superstore Data Model (Figure 4) and PK/FKs as a guide.
12) How many Fields/Rows are found in the Customer Table? What is the full Record for Record 256?
Check Figure: What is the full Record for Record 793?
Customer ID: ZD-21925
Customer Name: Zuschuss Donatelli
Segment: Consumer
13) How many Fields/Rows are found in the Employee Table? What is the full Record for Record 2?
Check Figure: What is the full Record for Record 4?
Employee ID: SS-04
Employee: Cassandra Brandow
Sales Region: South
14) How many Fields/Rows are found in the Order Table? What is the full Record for Record 512?
Check Figure: What is the full Record for Record 1024?
Order ID: CA-2016-116841
Customer ID (FK): TP-21130
Employee ID (FK): SS-01
Order Date: 4/14/2016
Ship Date: 4/18/2016
Ship Mode: Standard Class
Country: United States
State: Oregon
City: Springfield
Postal Code: 97447
15) How many Fields/Rows are found in the OrderLine ID Table? What is the full Record for Record 4096?
Check Figure: What is the full Record for Record 8192?
OrderLine ID: OL_ID-8192
Order ID (FK): CA-2018-133102
Product ID (FK): FUR-FU-10003247
Quantity: 2
Sales: 16.784
Discount: 60%
Profit: -22.2388
Profit Ratio: -133%
Order Returned: No
16) How many Fields/Rows are found in the Product Table? What is the full Record for Record 128?
Check Figure: What is the full Record for Record 1862?
Product ID: TEC-PH-10004977
Product Name: GE 30524EE4
Manufacturer: GE
Category: Technology
Sub-Category: Phones
Superstore Assignment Screen Clipping 2
· Establish the Table Relationships
· Review the Superstore Data Model (Figure 4) to confirm the Relationships between the Tables.
· Use Step 20 from the Intro Assignment as a guide to establish all the Table Relationships.
· Take a Screen Clipping showing all the Tables, and all the Relationships and paste it below ‘Superstore Assignment Screen Clipping 2’ in your
Database Assignment Answer Sheet
Word document.
Section 4: Gather Specific Data using Queries and Filter/Sort
· Here, you will use the same process used in the Intro Assignment to create a Query in MS Access.
· You will create a Query that contains the same Fields, and in the same order, as the Order Info Tab in the Superstore Data.
· You will then execute four Queries in MS Access and MS Excel and take Screen Clippings of both.
· MS Access Instructions
· Use the Order Info Query that you create as a template for the following four modified Queries.
· Copy the Order Info Query in the Navigation Pane four times, and name each copy Order Info Query – SC4/5/6/7 (with the number corresponding to which Screen Clipping they refer to).
· You will modify each Query in the Design View, so it fulfills your specific criteria, and only the specific Fields requested.
· To hide any of the Fields in the Query, de-select the ‘Show’ box.
· Rank your Query results Ascending/Descending by a certain Field:
· Go to the desired Field
· Click the Sort cell
· Choose Ascending/Descending
· Show ‘Top N’:
· Go to the Query Setup
· Adjust the number in the ‘Return’ box to the desired number of records
· Show one specific entry type for a certain Field:
· Go to the desired Field
· Click the Criteria cell
· Enter the desired entry in quotes
· Show Date Range:
· Go to the desired Field
· Click the Criteria cell
· Enter the desired date range in the following format: >=#Lower Range# And <=#Upper Range#
· Note: Use the equal signs if you want the Lower/Upper Range dates to be included in your Query
· Additional Query Date parameters:
https://support.office.com/en-us/article/examples-of-using-dates-as-criteria-in-access-queries-aea83b3b-46eb-43dd-8689-5fc961f21762
· MS Excel Instructions
· Use the Order Re-creation tab that you create as a template for the following four modified Queries.
· Copy the Order Re-creation tab four times, and name each copy Order Re-creation – SC4/5/6/7 (with the number corresponding to which Screen Clipping they refer to).
· You will show the desired results for each Query by making use of Filter/Sort and Hiding the columns that are not the specific Fields requested.
· Note: When you add Filters to your columns make sure you add Filters to all your columns. If you only add them to some, you will get incorrect results.
· Rank your Query results Ascending/Descending by a certain Field:
· Go to the desired Field and click the Filter/Sort arrow
· Click the Sort Smallest to Largest or Largest to Smallest
· Show ‘Top N’:
· Go to the desired Field and click the Filter/Sort arrow
· Click ‘Number Filters’
· Click Top 10, and choose the specific number of records desired
· Show one specific entry type for a certain Field:
· Go to the desired Field and click the Filter/Sort arrow
· Click the Search box
· Enter the desired entry and hit enter
· Show Date Range:
· Go to the desired Field and click the Filter/Sort arrow
· Click the ‘Select All’ box to deselect everything
· Expand the dates to the depth (Year -> Month -> Day) needed to select the Date Range you require
· Note: You will need to ensure all boxes between your start and end Date are checked (Yes, this can be annoying depending on your range and how your data is structured)
Superstore Assignment Screen Clipping 3
· Create Order Info Query.
· Here, you will use the same process used in the Intro Assignment to create a Query in MS Access.
· Make sure your Query shows all the Fields, and in the same order, as the Order Re-creation tab.
· Note: Depending on your resolution you may need to take two Screen Clippings to show all the Fields.
· Take a Screen Clipping showing the complete top row, and at least 10 rows of data and paste it below ‘Superstore Assignment Screen Clipping 3’ in your
Database Assignment Answer Sheet
Word document.
Superstore Assignment Screen Clipping 4
· Create Order Info Query showing the Top 10 Sales.
· Make sure your Query only shows the following Fields: Order ID, Order Date, Customer Name, Segment, Sales Region, Product Name, Category, Sub-Category, Sales, and Profit.
· Take a Screen Clipping of both the MS Access and MS Excel results showing the complete top row, and paste them below ‘Superstore Assignment Screen Clipping 4’ in your
Database Assignment Answer Sheet
Word document.
Check Figure: How many records with a Category of ‘Technology’ does your Query return, and what is the Order ID of the record with the lowest Sales?
8 records.
Order ID: CA-2017-143714.
Superstore Assignment Screen Clipping 5
· Create Order Info Query showing all Sales to Albuquerque, ranked by Descending Sales.
· Make sure your Query only shows the following Fields: Order ID, Order Date, Ship Date, City, Postal Code, Customer Name, Segment, Product Name, Sales, and Profit.
· Take a Screen Clipping of both the MS Access and MS Excel results showing the complete top row, and paste them below ‘Superstore Assignment Screen Clipping 5’ in your
Database Assignment Answer Sheet
Word document.
Check Figure: How many records does your Query return, and what is the Order ID of the record with the second highest Sales?
14 records.
Order ID: CA-2015-100881.
Superstore Assignment Screen Clipping 6
· Create Order Info Query showing all Sales with an Order Date between 3/2/2015 and 3/4/2015.
· Make sure your Query only shows the following Fields: Order ID, Order Date, Ship Date, Customer Name, Segment, Sales Region, Product Name, Sub-Category, Sales, and Profit.
· Take a Screen Clipping of both the MS Access and MS Excel results showing the complete top row, and paste them below ‘Superstore Assignment Screen Clipping 6’ in your
Database Assignment Answer Sheet
Word document.
Check Figure: How many records does your Query return, and what is the Order ID of the record with the highest Sales?
15 records.
Order ID: CA-2015-105648
Superstore Assignment Screen Clipping 7
· Create Order Info Query showing the Bottom 10 Profit with a Ship Date between Jan 2016 and July 2017.
· Make sure your Query only shows the following Fields: Order ID, Order Date, Ship Date, Customer Name, Segment, Sales Region, Product Name, Sub-Category, Sales, and Profit.
· Take a Screen Clipping of both the MS Access and MS Excel results showing the complete top row, and paste them below ‘Superstore Assignment Screen Clipping 7’ in your
Database Assignment Answer Sheet
Word document.
Check Figure: How many records with the Sub-Category ‘Binders’ does your Query return, and what is the Order ID of the record with the lowest Profit?
5 records.
Order ID: CA-2016-147830.
17) Bonus Question 1: Are databases are an efficient way of storing large amounts of data?
18) Bonus Question 2: How many different tables were there in the Superstore database?
Finn 2019 1
Based on Superstore data from Tableau
These three videos will help guide you and explain to you the assignment.
Video 1
Video 2
Video 3
>VLOOKUP
, 2
?
:$D$1 ,3,FALSE)
have?
8 ?
3
10 7 7 8 9 6 1
9 # 2
58 Colgrove Ave
1 5
Velocity Sporting Goods Pierre, SD 57501 328 12/16/13 Interstate Motor Freight 2 26 1 Ray Kramer 1 Ray Kramer 1 Ray Kramer 5 25 BB-538 Basketball bag 26 BB-019 Basketball pole pad 94 3 Nancy Ford 3 Nancy Ford 1/1/14 8 2 1 Ray Kramer Interstate Motor Freight 9 2 200 3 Nancy Ford /14
Interstate Motor Freight 11 22 BB-688 Portable inflation pump 69 3 Nancy Ford Interstate Motor Freight 12 13 BB-019 Basketball pole pad 94 3 Nancy Ford FB-225 Football helmet 90 3 Nancy Ford BB-926 Trainers first aid kit 30 3 Nancy Ford 1/1/14 Our truck 16 1 Desk 200 3 Nancy Ford (FK) Qty Cost 94 PO PO Date Shipper Item Description Vendor Vendor Vendor Street Vendor City Employee Signed by 1 Ray Kramer Item# (FK) Qty Cost Item# (PK) Item Description E# (PK) Signed by
BA 325 Database Assignment Answer Key
Name:
Before you do anything fill out your name on the assignment and save your file as The table has all of the questions from the Superstore Assignment. Fill in your answers to the questions in the corresponding cell in the Answer column. Below the table there is a spot for the Screen Clippings from both the Intro Assignment, and the Superstore Assignment. After you have filled out all of the answers and Screen Clippings submit the file to the Assignments folder in D2L. Q Number Question Answer Q1 How many Fields/Rows/Individual Values are found in the Order Info tab? Q2 How many Fields/Rows/Individual Values are found in the Customer tab? Q3 How many Fields/Rows/Individual Values are found in the Employee tab? Q4 How many Fields/Rows/Individual Values are found in the Order tab? Q5 How many Fields/Rows/Individual Values are found in the OrderLine ID tab? Q6 How many Fields/Rows/Individual Values are found in the Product tab? Q7 Are there more or less Individual Values in the Order Info tab, when compared to the total Individual Values found in the Customer/Employee/Order/OrderLine ID/Product tabs? How many more/less? Q8 What Fields are in Columns AB and AC? What are the values in AB317 and AC317 once you have retrieved the Product Data? Q9 What Fields are in Columns K and N? What are the values in K951 and N951 once you have retrieved the Order Data? Q10 What Fields are in Columns E and F? What are the values in E317 and F317 once you have retrieved the Employee Data? Q11 What Fields are in Columns B and C? What are the values in B951 and C951 once you have retrieved the Customer Data? Q12 How many Fields/Rows are found in the Customer Table? What is the full Record for Record 256? Q13 How many Fields/Rows are found in the Employee Table? What is the full Record for Record 2? Q14 How many Fields/Rows are found in the Order Table? What is the full Record for Record 512? Q15 How many Fields/Rows are found in the OrderLine ID Table? What is the full Record for Record 4096? Q16 How many Fields/Rows are found in the Product Table? What is the full Record for Record 128? Q17 Bonus Question 1: Are databases are an efficient way of storing large amounts of data? Q18 Bonus Question 2: How many different tables were there in the Superstore database? Screen Clippings: Intro Assignment Superstore Assignment Screen Clipping 4:
Access:
Excel:
Screen Clipping 5: Access: Excel: Screen Clipping 6: Access: Excel: Screen Clipping 7: Access: Excel: Finn 2019 1 Based on Superstore data from Tableau
2
VLOOKUP Practice
Data
Employee
No. Customers
Net Sales
Profit/Loss
Questions
Answer
Formula
Joseph
8
1
5
9
5
6
3
1. What were the net sales for
John
=VLOOKUP(“John”,$A$
4
7
John 8
1,088
397
2. How many customers did
Jamie
=VLOOKUP(“Jamie”,$A$4:$D$17,2,FALSE)
Josh
1,680
753
3. What is the profit for
Jessy
=VLOOKUP(“Jessy”,$A$4:$D$17,4,FALSE)
Jamie 9
2,
13
923
4. How many customers did Joshua have?
=VLOOKUP(“Joshua”,$A$4:$D$17,2,FALSE)
Jackie
10
1,610
579
5. How many customers did Joshua have? No Error
=IFERROR(VLOOKUP(“Joshua”,$A$4:$D$17,2,FALSE), “Not Found :-(“)
Johnson
1,540
5
70
Jonathan
1,3
16
428
Jagjit
1,799
709
Jairam
1,624
621
Jessy 6
7
26
236
Javed
2,277
966
Jimmy
7
14
22
Juno
2,682
1,023
OriginalData
V#
Vendor
Vendor Street
Vendor City
PO
PO Date
Shipper
POLine#
Qty
Item#
Item Description
Cost
E#
Signed by
25
Velocity Sporting Goods
12
Pierre, SD 57501
328
12/16/13
Interstate Motor Freight
11
BB-019
Basketball pole pad
94
1
Ray Kramer
252
1258 Colgrove Ave
60
BB-538
Basketball bag
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 328 12/16/13 Interstate Motor Freight 3 70
BB-688
Portable inflation pump
69
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 328 12/16/13 Interstate Motor Freight 4
85
BB-926
Trainers first aid kit
30
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501
329
1/1/14
Allied Trucking
3
Nancy Ford
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 329 1/1/14 Allied Trucking 6
20
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 329 1/1/14 Allied Trucking 7 60
FB-225
Football helmet
90
253
Chicago Office Supply
1411 E. Michigan
Chicago, IL 60606
330
Our truck
LT-100
Laptop computer
1000
253 Chicago Office Supply 1411 E. Michigan Chicago, IL 60606
331
1/10/14
DK-
200
Desk
253 Chicago Office Supply 1411 E. Michigan Chicago, IL 60606 331 1/10/14 Interstate Motor Freight 10 1 LT-100 Laptop computer 1000 3 Nancy Ford
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501
332
1/
15
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 332
1/15/14
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 332 1/15/14 Interstate Motor Freight 13
48
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 332 1/15/14 Interstate Motor Freight 14
34
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501 332 1/15/14 Interstate Motor Freight 15 14 BB-538 Basketball bag 26 3 Nancy Ford
253 Chicago Office Supply 1411 E. Michigan Chicago, IL 60606
333
DK-200
POLineItems
POLine# (PK)
PO#
Item# (FK)
1 328 BB-019
115
2 328 BB-538 60 26
3 328 BB-688 70 69
4 328 BB-926 85 30
5 329 BB-538 25 26
6 329 BB-019 20 94
7 329 FB-225 60 90
8 330 LT-100 2 1000
9 331 DK-200 2 200
10 331 LT-100 1 1000
11 332 BB-688 22 69
12 332 BB-019 13 94
13 332 FB-225 48 90
14 332 BB-926 34 30
15 332 BB-538 14 26
16 333 DK-200 1 200
PO# (PK)
E# (FK)
V# (FK)
328 12/16/13 Interstate Motor Freight 1 252
329 1/1/14 Allied Trucking 3 252
330 1/1/14 Our truck 1 253
331 1/10/14 Interstate Motor Freight 3 253
332 1/15/14 Interstate Motor Freight 3 252
333 1/1/14 Our truck 3 253
Inventory
Item# (PK)
BB-019 Basketball pole pad
BB-538 Basketball bag
BB-688 Portable inflation pump
BB-926 Trainers first aid kit
FB-225 Football helmet
LT-100 Laptop computer
DK-200 Desk
V# (PK)
252 Velocity Sporting Goods 1258 Colgrove Ave Pierre, SD 57501
253 Chicago Office Supply 1411 E. Michigan Chicago, IL 60606
E# (PK)
3 Nancy Ford
OrderDetails
V# (PK) Vendor Vendor Street Vendor City PO# (PK) PO Date Shipper E# (FK) V# (FK) POLine# (PK)
PO# (FK)
BA325 Firstname Lastname
(use your actual name).
Screen Clipping 1:
Screen Clipping 2:
Screen Clipping 3:
Screen Clipping 4:
Screen Clipping 5:
Screen Clipping 6:
Screen Clipping 1:
Screen Clipping 2:
Screen Clipping 3: