Project Deliverable 3: Database And Data Warehousing 802660

Project Deliverable 3: Database and Data Warehousing Design

This assignment consists of two (2) sections: a design document and a revised project plan. You must submit both sections as separate files for the completion of this assignment. Label each file name according to the section of the assignment it is written for. Additionally, you may create and/or assume all necessary assumptions needed for the completion of this assignment.

One of the main functions of any business is to be able to use data to leverage a strategic competitive advantage. This feat hinges upon a company’s ability to transform data into quality information. The use of relational databases is a necessity for contemporary organizations; however, data warehousing has become a strategic priority due to the enormous amounts of data that must be analyzed along with the varying sources from which data comes.

Since you are now the CIO of a data-collection company which gathers data by using Web analytics and operational systems, you must design a solution overview that incorporates data warehousing. The executive team needs to be clear about what data warehousing can provide the company.

Section 1: Design Document

1. Write a four to six (4-6) page design document in which you:

  1. Support the need for data warehousing within your company and elaborate on the best practices that the company will adhere to.
  2. Create a schema that supports the company’s business and processes. Explain and support the database schema with relevant arguments that support the rationale for the structure. Note: The minimum requirement for the schema should entail the tables, fields, relationships, views, and indexes.
  3. Create an Entity-Relationship (E-R) Diagram relating the tables of your database schema through the use of graphical tools in Microsoft Visio or an open-source alternative such as Dia. Note: The graphically depicted solution is not included in the required page length but must be included in the design document appendix. Explain your rationale behind the design of your E-R Diagram.
  4. Create a Data Flow Diagram (DFD) relating the tables of your database schema through the use of graphical tools in the required page length but must be included in the design document appendix. Illustrate the flow of data including both inputs and outputs for the use of a data warehouse. The diagram must map data between source systems, data warehouses, and specified data marts. Note: The graphically depicted solution is not included in the required page length.

Your assignment must follow these formatting requirements: be typed, double spaced, using Times New Roman font (size 12), with one-inch margins on all sides; citations and references must follow APA or school-specific format. Include a cover page with assignment title, your name, professor’s name, course title, and date. The cover page and references are not included in the required page count. Include charts or diagrams created in MS Visio or Dia as an appendix of the design document. All references to these diagrams must be included in the body of the design document.

Paper For Above instruction

As the Chief Information Officer (CIO) of TIRES PLUS, a company specializing in tire and automotive services, integrating a robust data warehousing system is essential to harness the vast amount of data accumulated from web analytics and operational systems. The strategic deployment of data warehousing will enable TIRES PLUS to analyze vast datasets effectively, leading to more informed decision-making, enhanced customer insights, and competitive advantage in the automotive service industry.

Justification for Data Warehousing at TIRES PLUS

TIRES PLUS collects extensive operational data, including sales transactions, inventory levels, customer service interactions, and web analytics. Without a centralized system, this data remains fragmented, hindering comprehensive analysis. Implementing a data warehouse consolidates data from various sources, enabling efficient querying and reporting, which supports strategic initiatives such as targeted marketing, inventory management, and customer relationship management.

Data warehousing adheres to best practices, including adhering to ETL (Extract, Transform, Load) processes that ensure data quality and consistency. Regular data validation, data cleansing, and normalization are crucial in maintaining the integrity of the warehouse. Additionally, employing dimensional modeling techniques (star schema) facilitates fast query performance and user-friendly data analysis for managerial reporting.

Designing the Database Schema and Justification

The database schema for TIRES PLUS includes core entities such as Customers, Transactions, Services, Inventory, and Web Analytics Data. The schema supports business processes like sales transactions, inventory updates, customer engagement, and web behavior analysis.

The primary tables include:

  • Customers: customer_id (PK), name, contact_info, demographics.
  • Transactions: transaction_id (PK), customer_id (FK), date, total_amount, payment_method.
  • Services: service_id (PK), description, price, duration.
  • Inventory: inventory_id (PK), part_number, description, quantity_available, supplier_info.
  • WebAnalytics: web_id (PK), session_id, customer_id (FK), page_viewed, timestamp, referral_source.

Relationships between these tables support business processes such as linking customers to transactions and web sessions. Proper indexing on foreign keys and frequently queried fields ensures quick access and efficient data retrieval.

Entity-Relationship (E-R) Diagram and Rationale

The E-R diagram visually depicts relationships: Customers to Transactions (one-to-many), Customers to WebAnalytics (one-to-many), and Transactions to Services (many-to-many via a linking table, e.g., Transaction_Services). The model supports detailed customer purchase histories, web behavior tracking, and service details, enabling comprehensive customer insights.

The diagram emphasizes normalization to reduce redundancy and improve data integrity, while allowing for scalability as the company’s data needs grow.

In constructing the diagram in Visio, relationships are visually represented with crow's feet notation for clarity. This supports maintenance and future schema modifications.

Data Flow Diagram (DFD) and Data Flow Mapping

The DFD for TIRES PLUS illustrates data movement from source systems — web servers and operational databases — into staging areas, then into the data warehouse, and finally into data marts for specific analyses like sales, inventory, or customer behavior.

The data flow begins with data extraction from transactional and web sources, transformation and cleaning in ETL processes, loading into centralized warehouse, and subsequent distribution to data marts tailored for marketing, inventory, and service managers. This setup facilitates real-time reporting and historical trend analysis.

The diagram visually demonstrates how data flows through each component, enabling stakeholders to understand the system’s capabilities and how they can leverage the data for operational excellence.

Conclusion

Implementing a data warehousing solution at TIRES PLUS will significantly enhance data accessibility, analytical capacity, and strategic decision-making. The carefully designed schema, ER model, and data flow architecture will support the company’s growth and competitive positioning in the automotive service industry.

References

  • Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The Definitive Guide to Dimensional Modeling. John Wiley & Sons.
  • Inmon, W. H. (2005). Building the Data Warehouse. John Wiley & Sons.
  • Loshin, D. (2009). Mastering Data Warehouse Design: Relational and Dimensional Techniques. Morgan Kaufmann.
  • Golfarelli, M., & Rizzi, S. (2009). Data Warehouse Design: Modern Principles and Methodologies. Springer.
  • Kimball, R., Ross, M., Thornthwaite, W., Mundy, J., & Becker, B. (2012). The Data Warehouse Lifecycle Toolkit. John Wiley & Sons.
  • Vassiliadis, P., & Simoudis, E. (2008). Data Warehousing: Concepts, Techniques, and Applications. Springer.
  • Watson, H. J. (2007). Data Management: Databases & Data Warehousing. Pearson Education.
  • Imhoff, C., Galpin, C., & Pastore, N. (2010). Business Intelligence/dData Warehousing, 2nd Edition. Microsoft Press.
  • Rajaraman, A., & Ullman, J. D. (2011). Mining of Massive Datasets. Cambridge University Press.
  • Chaudhuri, S., & Dayal, U. (1997). An Overview of Data Warehousing and Business Intelligence Technology. Communications of the ACM, 40(9), 64-70.

Related Assignments