Designing Business Data Models for Clearwater Traders Database

You have been supplied with Clearwater.sql, which contains a database creation and population script to create and populate the Clearwater Traders database. You must design business data models that will provide necessary information to data developers for creation of a first normal form staging area that contains all of the measurables (facts) and dimensions (characteristics) necessary for supply of evidence to justify the decisions of Clearwater stakeholders at the production, procurement and fulfillment functions of the business.

• A database table is said to be in first normal form when all of the data are about a single thing, and every row-column intersection contains only a single value.
• A Staging area is a place where you gather resources together before launching them on a single purpose.
• A measurable is a single fact expressed as a quantity. It is usually numeric. An example would be total sales.
• A dimension is a metric that you use to describe a measurable; in other words, it is something you express the measurement “by”. An example can be the date – “I would like to see total sales by month”.

In a Data Migration project such as this, it is always a good strategy to first discover what the stakeholders’ information requirements are, and what decisions will be made based on that information. For example, corporate ownership is primarily concerned with the equation Profit = Revenue – Cost and will therefore wish to see measurables such as quantities of product sold and totals of revenue, summarized by financial reporting periods like quarters. Their end goal is to maximize revenue and/or lower cost by introducing business process enhancements. See the supplied Re-Engineering Guidance document for more information on corporate goalsetting. Corporate management on the other hand will want to see specifics of revenue and cost measurables in operational groupings like by product, by supplier, by customer, by season, by day of the week. Note that while Procurement, Production and Fulfillment will each have a collection of data and tables that are specific to their area, those tend to be regarding their specific operational needs – receiving goods into inventory vs. maintaining inventory ready for orders vs. processing customer orders. Your process models must also consider the need for information to support those operational processes – preparing to receive goods, putting in-demand inventory up front, facilitating customer orders through marketing. 

 

Necessary data and information to match the requirements can then be identified by investigating the metadata and/or the organization’s Entity Relationship diagram to discover the business processes currently prioritized by the organization. The organization’s data dictionary will illustrate the enforcement of business norms and other rules within the structure of the database.

• Migration is the movement of data from one place to another.
• Migration projects always involve the design of a test data set that will stress the predictable limits of production data, pre-tested by query to ensure that the test data are present before the migration begins, and post-tested by query to ensure that the predicted counts of data rows and transformations, if any, emerge after the migration.
• Metadata is information about the structure, content and management of a database.
• An Entity Relationship Diagram is a diagram that depicts the Entities in a data collection, the Referential Integrity relationships connecting the entities, the names and attributes used to describe the entities, and any constraints in place to ensure data integrity.
• A business process is something a business does in order to operate as a business. Business processes are composed of business inputs, the process itself, and business outputs consequent to the operation of a process.
• A Data Dictionary is a document that describes each object and characteristic in a data collection, including the organizational justification for the existence of that object.

(1) Use artifacts such as Data Dictionary and ERD to fully explain and justify the structure and content and purpose of the Clearwater database.

(2) Design and Create a business process model to display the process interdependencies of each of the production, procurement, and fulfillment processes. Apply the re-engineering guidance document to fine tune and justify your models.

(3) Present the results of your work as a Recommendation.

(4) Present your work in a Memo format with the following sections:

a. Existing System – Describe the resources provided to you for completion of this work.

b. Statement of Requirement – Describe exactly and measurably what you must provide as the end result of this assignment.

c. Analysis – Explain step by step in a logical order why and how you did what you did in order to create the deliverables specified in the Statement of Requirement section by using the materials supplied to you in the Existing System section.

d. Recommendation – provide the deliverables you specified in your Statement of Requirement.