Geography homework help

Geography homework help. 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.docx
  • 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:
      • 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?

 

Geography homework help