Database Assignment Part 1 – Worksheet:

Read and enter the required information on this page before you proceed to the next

Overall Instructions:  Thisdatabase assignment consists of two parts.

• Part 1: complete the work as instructed in this worksheet and submit your work to Canvas.
• Part 2: at Canvas, enter answers to questions based on the work you complete in Part 1.
• Both parts are required and must be submitted for credit on this assignment.

This worksheet includes the instructions to complete Part 1 of the assignment.  Read and follow it carefully.  Complete it in its entirety.

• Perform the activities as instructed in this worksheet:
  • Step 1, Explore and document the database.
  • Step 2, Draw a relationship view of the database.
  • Step 3, Develop queries &create two reports based on queries.
• Enter the results of the activities in the areas provided as instructed in this worksheet.
• Submit your completed work to the Assignment Part 1 in Canvas. You will submit the following files. Do not change the file types, and be sure to name them as instructed:
  • This worksheet file, saved as LastFirst_DBWorksheet where last is your last name and first is your first name.
  • The Report Word file LastFirst_Customer
  • The Report Excel file
• It is your responsibility to submit your work on time and to verify that your files upload completely. See Canvas for the due date.
• Once you have submitted your work for Part 1 of this assignment, proceed to complete Part 2.

STEP 1:  Explore the database

Review the structure and data in the Company database.  Identify andrecord the primary keys, foreign keys (if any), and the total number of recordsfor each table in the database, adding rows as neededhere to accommodate them.The first table is completed for you.

STEP 2:  Draw a relationship view of the database

Based on your findings in Step 1, generate a figure showing the relationships among the tables in the Company database.

• Use the Relationship Toolbox posted at the Canvas assignment to create your drawing.
  • Include all the tables in the database and label each with the table name, the primary key, and foreign keys (if any).
  • Draw lines between the related tables to show the direct relationships.
• When your drawing is complete, paste it as a picture in the frame here. If you need help with this action, ctrl+click here Paste Special:

STEP 3:  Develop queries and Create reports

Query 1:  Develop a query that will produce an employee list of all employees who no longer work at the Company.  Include their last name and first name, their job title, their city, and sort the output by city.

Query 2:  Develop another query that includes the employees who currently work at the Company and are in the USA.  Include their last name and first name, their job title, their hire date, and sort the output by their title.

Query 3:  Develop a query to produce output that lists customers and includes their last and first names, their region, and the country where they live. Include on the list only customers who do not live in the USA and who have a last name that starts with S.  Sort the output by country, then by last name.

Query 4:  Develop a query that will produce a list of products.  Include in the output the category ID, the product name, units in stock, and units on order.  Restrict the output to include only products that are in the categories with ID number 10, 11, and 12.  Sort the query by the category ID and then by the product name.

Query 5:  The Sales department wants to know how many customer orders were placed during the months of February and March in the year 2013.  They want the list to show the employee ID who processed the order and then the number of orders processed by that employee, and name that column as “Order Count”.

Query 6:  The Sales department wants a list that will identify the product with the largest gross sales over all sales.  They want the output to include the product name and its total gross sales amount (sales before discount) named as “Gross Sale.” Sort the query output in descending order by the gross sale, then by the product name.

Query 7:  The Sales department wants to know the total quantity of products on each customer order.  The output needs to include the order ID, the last name, city, and country of the customer, and then the total quantities ordered, named as “Products Ordered.” Sort the query output in descending order by the number of products ordered, then by the customer country.

Query 8 & Report:  Create a new Word document, and name it as LastFirst_Customer where Last is your last name and first is your first name.  Continue to create a report by following these next steps.

1. In the first row in the Word document, add the title Non-US Customers by Income Bracket.
2. Return to the DB Browser application and generate a query that includes the customer last name, income bracket, city, and country, and include records of customers who do not live in the USA and who are in an income bracket of 7 or 8.
3. Sort the output by customer income bracket in descending order, and alphabetically by country and then by last name.
4. Run the query, and select all records in the output.
5. Copy the output with headers and paste it into the Word document below the report title.
6. The pasted query output is now recognized by Word to be a Word table. Edit the column headers of the Word table to be Last Name, Income Bracket, City, and Country.
7. Use your creativity to choose design and format changes to make the report look professional and easy to read.
8. Save your work and include the Word file report that you have just created with your Canvas submission for this assignment.

Query 9& Report:  Create a new Excel document, and name it as LastFirst_GrossSale where Last is your last name and first is your first name.  Continue to create a report by following these next steps.

1. Click in the first cell (Cell A1) of the Excel document and add the title Product Gross Sales.
2. Return to the DB Browser application and generate a query like Query 6 but restrict the output to only include products offered by suppliers with IDs 3 and 4. Keep the rest of the query the same (same attributes, calculated column, and sort order).
3. Run the query, and select all records in the output.
4. Copy the output with headers.
5. Click in Cell A2 of your Excel document and paste the output into the worksheet.
6. Edit the column headers of the table to be Product Nameand Gross Sale.
7. Format the Gross Sale column as Accounting.
8. Use your creativity to choose design and format changes to make the report look professional and easy to read.
9. Save your work and include the Excel file report that you have just created with your Canvas submission for this assignment.

This is the end of Part 1.
Follow the instructions to submit your work
to Canvas. Then Proceed to Part 2.