• 
  
  • 
    
    • 
      
      • Create a Mircrosoft Word document named A05.docx. Include all diagrams, screen shots, code, and answers to questions within the following sections:
        1. Understand Data Structure and Data
        2. Customer Name and Kinds of Customers
        3. Customer Name List
        4. Count Customer Types
        5. Discussion
      • a) In the Understand Data Structure and Data section of A05.docx
        1. Create an Entity Relationship Diagram (ERD)
           1. Includes all tables related to the Customer table.
           2. Arrange and space those tables and relationships so that you can easily see the database structure.
           3. Copy and paste the ERD into A05.docx
        2. Understand the Tables
           1. Write and run a query to select all rows and fields from each of the tables in your ERD. Familiarize yourself with the data.
           2. For each table answer the following in A05.docx
              1. How many rows?
              2. Describe the table
           3. For each relationship answer the following in A05.docx
              1. Describe the relationship
              2. Does the relationship always exist? If not, when not?
      • In the Customer Name and Kinds of Customers section of A05.docx
        1. Explain why there is no customer name in the Customer table and where is the customer name?
        2. The structure and the data tells you that there is more than one type of customer, which are:
           1. Person
           2. Store
           3. Store with Contact
        3. How did I arrive at that conclusion? What fields have what values for each of the three customer types?
           1. Person -
           2. Store -
           3. Store with Contact -
      • In the Customer Name List section of A05.docx.
        1. Write and run a query to show all customers with their names in a single list ordered ascending by AccountNumber.
        2. Include the following fields.
           1. Customer AccountNumber
           2. Customer Type - results from a CASE statement that will produce a value for each type of customer. There are three types of customers: Person, Store, and Store with Contact
           3. Customer Name - results from a Case statement that will produce a value depending on each of the three types of customers.
              1. Use a CONCAT statement to combine and format first, middle, and last names that includes commas, periods, and spaces for which the results appear like: Woolridge, Richard W.
              2. Make your CONCAT statement account for all the variations (e.g., blank, middle initial, middle name, and null) in the MiddleName field
              3. Submit a screen shot in A05.docx.
              4. Submit a copy of the code in A05.docx.
        3. Create Store with Contact Customer Report
           1. Limit your query to only include Store with Contact customer type
              1. Submit a copy of the code in A05.docx.
           2. Use Visual Studio to create a report using the Store with Contact query
           3. Use Visual Studio to format these reports to include the following.
              1. Header
                 1. Student name
                 2. Report title
                 3. Date report run
              2. Make sure column headers print on every page
              3. Footer
                 1. Brief one short line description of what is on the report
                 2. Page x of y
           4. Print the report a single page wide to A05.PDF
      • In the Count Customer Types section of A05.docx.
        1. Write and run a query to Count total customers.
           1. Name the count “Total Customers”
           2. There should only be a single column and a single row resulting from this query
           3. Submit a screen shot in A05.docx.
           4. Submit a copy of the code in A05.docx.
        2. Write and run a query to Count Customers by Customer Type order by ascending Total Customers and include Customer Type and Customer Count as fields in your query.
           1. Submit a screen shot in A05.docx.
           2. Submit a copy of the code in A05.docx.
      • In the Discussion section of A05.docx.
        1. What do you now know about AdventureWorks customers?
        2. Look at the counts. What are the data points that is most important to communicate to management?
        3. Given the datapoints what customers should AdventureWorks focus on?
      • After completing this assignment use the ERD, Data Dictionary, and your new understanding to complete the Assignment Quiz related to this Assignment. Be prepared to answer the following questions in that quiz.
        1. How many tables are in your ERD?
        2. How many relationships are in your Customer ERD?
        3. How many foreign keys are in the Customer table?
        4. How many rows are in the Customer table?
        5. The name of the Customer can be found in which field?
        6. The Customer name located in the Store table is how many fields?
        7. The Customer name located in the Person table is how many fields?
        8. Why is customer not always related to person?
        9. The Customer Type field in this assignment got its values from a __________ statement.
        10. When the Customer Name field in this assignment got its values from the Person table, which field was used?
        11. Which statement permits more than one text field to be strung together into a single field?
        12. How many customers are persons?
        13. How many customers are stores?
        14. How many PersonIDs in the Customer table are null?
        15. How many customers have both person and store relationships?
        16. What percentage of AdventureWorks customers are Stores?
        17. What percentage of AdventureWorks customers are Persons?
        18. How can a customer be related to Person and to Store?
        19. When you go back and explore the ERD in detail, what field explains how some persons are individual customers and some persons are simply store contacts?

    • 
       

Answer rating: 100% (QA)

I can guide you on how to approach these tasks but I cannot directly create files diagrams or run qu... View the full answer