Specifics

1. In this activity you are going into a company as a recently hired database administrator, data architect, or data engineer. You are a business intelligence person with a good understanding of relational database design. You are the one responsible for the data. But there has been a bit of trouble.

2. The more senior BI person was recruited away for a base salary of $200,000 at some other technical company and the data team needs you to jump into work mode right away. The person that was recruited away did not document the data system very well and the problem is that accounting and other departments rely on this information. You have the ERD and can run queries to figure out how the database is designed and what information is within the database.

3. There are a wide variety of issues and problems that you need to provide answers to based on your understanding of the data system and the data that is contained in the system.

Tasks

Your first task is to review the order data system (i.e., the North Wind database from the practice environment) and understand the whole layout of the data.

You will next answer these questions:

1. Provide a design summary of the database: what are each of the entity classes about? What are the key functions of the database system? The CFO wants to know if the data makes sense, and your team is relying on your expert judgment to decide whether the data is even organized correctly. Using the discussion thread examples (Homework #4), see if you can find one or two improvements that would make this database system even better, more efficient, or more useful.

1. The accounting department is reconciling a major financial disaster. There accounting software had a problem and did not import the data correctly for a portion of two quarters. They, accounting, need to know how much was billed during the months of July and August of 2016. Prepare a query for them that they can use to reconcile their books. Feel free to include any information that you think an accounting department would need to know relative to the data that is the entire data system. Provide your query and your rationale for its use as a source of truth for the reconciliation of the accounting system.

1. A junior colleague has serious questions about the viability of the order_details In fact, this colleague has been advocating merging that table with the orders table to make some queries easier to run. Based on the course textbook, readings, and discussion, explain how this table operates in terms of its primary key and relationships. What is this table accomplishing? Why would a junior colleague think that this order_details table should be merged with the orders table and why is this a bad idea from a logical database design?

1. The operations team needs data that is not a part of their regular data feed. This is a one-off request but its likely to turn into a new set of standard quarterly reports. They need to get a good sense of how suppliers interact with products -- and whether moving to a single supplier for a single product makes sense. How would you use this information in the suppliers and products table to help them make this decision. They honestly do not know what is even in the data because their team is also new. So, they are looking to you for advice on the data. What can you tell them about the relationships between suppliers and products?