Project Deliverable 3: Database And Data Warehousing 107322

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 transform data into information. The use of relational databases and data warehousing has gained recognition as a standard for organizations. A quality database design makes the flow of data seamless. The database schema is the foundation of the relational database. The schema defines the tables, fields, relationships, views, indexes, and other elements. The schema should be created by envisioning the business, processes, and workflow of the company.

Since your company is an innovative Internet-based company, movement toward data warehousing seems to be one of the most viable options to give your company a competitive advantage; however, these concepts must be explained to the executive board in a manner to garner support.

Section 1: Design Document

Write a six to ten (6-10) page design document in which you:

  • Support the need for the use of relational databases and data warehousing. From a management standpoint, it may be important to show the efficiencies that can be gained for executive oversight.
  • Create a database 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. The schema should entail the tables, fields, relationships, views, and indexes.
  • Identify and create database tables with appropriate field-naming conventions. Then, identify primary keys and foreign keys, and explain how referential integrity will be achieved.
  • Normalize the database tables to third normal form (3NF).
  • Identify and create an Entity-Relationship (E-R) Diagram relating the tables of the database schema through graphical tools (e.g., Microsoft Visio or Dia). The diagram should be included in the appendix.
  • Explain the rationale behind the design of the E-R Diagram.
  • Identify and create a Data Flow Diagram (DFD) relating the tables of your database schema through graphical tools. The diagram should be included in the appendix.
  • Explain the rationale behind the design of the DFD, illustrating data flow including inputs and outputs for the data warehouse. The diagram should map data between source systems, operational systems, data warehouses, and data marts.

Your assignment must follow these formatting requirements: typed, double-spaced, Times New Roman font (size 12), with one-inch margins. Citations and references must follow APA format. Include a cover page with the assignment title, your name, your professor’s name, course title, and date. The cover page and references are not included in the page count. Include diagrams in the appendix and reference all figures in the text.

Section 2: Revised Project Plan

Use Microsoft Project to update the project plan (both summary and detailed views), extending the plan from Project Deliverable 2: Business Requirements, by adding three to five (3-5) new project tasks. Each new task should include five to ten (5-10) subtasks to expand project planning and execution details.

Paper For Above instruction

The development of a robust database and data warehousing system is essential for modern Internet-based companies seeking to leverage their data for strategic advantage. This report delineates the critical aspects of designing an effective relational database schema, complemented by data warehousing components to enhance business intelligence and operational efficiency.

Introduction

In an increasingly data-driven business environment, the importance of well-structured databases and comprehensive data warehouses cannot be overstated. Relational databases serve as the backbone of transactional operations, ensuring data integrity, consistency, and rapid access to information. Concurrently, data warehouses aggregate diverse data sources, enabling analytical processes and strategic decision-making. For an innovative Internet-based firm, integrating these systems facilitates efficient management, enhanced customer insights, and competitive differentiation.

Rationale for Using Relational Databases and Data Warehousing

Relational databases are vital for handling day-to-day business transactions due to their ability to support structured data, enforce data integrity, and facilitate quick retrieval through optimized querying. They underpin essential functions such as user management, sales processing, and inventory control. Data warehousing complements these by consolidating historical and current data from various operational systems, creating a comprehensive view of business metrics. This setup supports advanced analytics, trend analysis, and reporting, offering management real-time insights and strategic foresight (Kimball & Ross, 2013).

Design of the Database Schema

The database schema centers around core entities reflective of the company's business model, including Customers, Orders, Products, and Employees. Tables are created with clear naming conventions such as 'Customer,' 'Order,' 'Product,' and 'Employee,' emphasizing clarity and consistency.

Tables and Fields

  • Customer: CustomerID (PK), Name, Email, Phone, Address
  • Order: OrderID (PK), CustomerID (FK), OrderDate, TotalAmount
  • Product: ProductID (PK), Name, Description, Price, StockQuantity
  • Employee: EmployeeID (PK), Name, Position, Department, ContactInfo

Primary keys (PK) uniquely identify records within each table, while foreign keys (FK) establish relationships—e.g., CustomerID in the Order table relates to CustomerID in Customer. Referential integrity is preserved through enforced foreign key constraints, ensuring data consistency across related tables.

Normalization and Keys

All tables are normalized to the third normal form (3NF). This process eliminates redundant data, ensures each non-primary attribute depends solely on the primary key, and organizes relationships efficiently. For example, customer contact details reside in the Customer table without duplication, and all related data are connected via primary and foreign keys.

Entity-Relationship Diagram

The ER diagram visually represents table relationships. For instance, a one-to-many relationship exists between Customers and Orders, as one customer can place multiple orders. The diagram clarifies data dependencies, ensuring integration and integrity in development.

Design Rationale for ER Diagram

The ER diagram mirrors business processes, linking customers to their orders, Products to categories, and employees to their responsibilities. This logical structure supports efficient data retrieval, reporting, and ensures data integrity across operational and analytical systems.

Data Flow Diagram

The DFD illustrates data movement from source systems (e.g., user inputs, external feeds) through operational databases into data warehouses. It depicts data collection, transformation, and storage processes, mapping inputs such as customer transactions and product updates to outputs like sales reports and strategic dashboards. This visualization underscores how data flows enable timely decision-making and business agility.

Conclusion

Implementing a well-designed relational database schema aligned with a data warehouse architecture provides the foundation for efficient business operations and advanced analytics. By normalizing data, establishing clear relationships, and mapping data flows, the company can achieve improved data integrity, accessibility, and strategic insight, ultimately leading to competitive advantage in the digital marketplace.

References

  • Kimball, R., & Ross, M. (2013). The Data Warehouse Toolkit: The definitive guide to dimensional modeling (3rd ed.). John Wiley & Sons.
  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of database systems (7th ed.). Pearson.
  • Inmon, W. H. (2005). Building the data warehouse. John Wiley & Sons.
  • Saracco, A., & Sillitti, A. (2018). Data warehousing and business intelligence. Information Systems, 75, 114-121.
  • Golfarelli, M., & Rizzi, S. (2009). Data warehouse design: Modern principles and methodologies. Wiley.
  • Rob and Coronel. (2007). Database systems: Design, implementation, & management (8th ed.). Course Technology.
  • Sharma, S. K., & Sharma, S. (2020). Database management systems: Concepts, design, and applications. Pearson.
  • Matthes, V., & Gschwandtner, P. (2022). Data governance and security in data warehousing. Journal of Data Management, 19(2), 55-70.
  • Kim, W., & Simons, A. (2014). Building data warehouses using the dimensional modeling approach. McGraw-Hill Education.
  • Abell, A. (2019). Implementing effective data flow architectures for business intelligence. International Journal of Information Systems, 52, 13-27.

Related Assignments