Project Deliverable 6: Final Project Plan

Running head: DATABASE AND DATA WAREHOUSING DESIGN

DATABASE AND DATA WAREHOUSING DESIGN 9

Project Deliverable 3: Database and Data Warehousing Design

Introduction

An essential part of corporate intelligence, a data warehouse helps to consolidate and analyze company data. To put it simply, data warehousing is the act of gathering and analyzing data from a variety of sources in order to provide significant business insights. There are several tools and components that help businesses make strategic use of their data. When a firm stores an enormous quantity of information electronically, it's referred to as "data warehouse." Making information accessible to the public as quickly as possible is part of this process.

The data that different organizations store is stored on a variety of platforms, including data systems, while others rely on manual methods and paper-based processes. Operational systems like as information technology systems, human resource systems, websites, customer relationship management systems, management systems, and even enterprise resource planning systems are examples of data sources. Supplier data, employee data, customer data, purchases and sales, and organizational data, which may include firm loan records, capital and investment data, and management data, among other things, are all examples of data that may be gathered and held inside an organization (Wang et al., 2020). Realsys Firm, an innovative e-commerce company, needs data warehousing to assist in the transformation of raw data into useful information that can be used in decision-making processes across the organization.

It is the goal of this study to explain why an organization should have a data warehousing system. As a result, a database structure that supports the company's operations and procedures is being developed. Organizational data flow is shown in the research with the use of an Entity-Relationship Diagram (E-R Diagram) and Data Flow Diagram (DFD).

Need for Data Warehousing

By enabling company executives to easily access historical data and review previous projects that were successful or failed, data warehousing helps organizations improve their bottom line. This enables executives to identify areas where their approach may be adjusted to decrease costs, optimize efficiency, and improve sales (Anupama, Jain, Falk, Deb, & Bantilan, 2020). The Realsys Company's operating data storage facilities include a vast quantity of historical data. Extracting and interpreting this data is a time-consuming operation for the corporate analyst. The analyst will need to raise tickets with the IT department to get information; however, the procedure may take longer than anticipated and may even result in the recovery of incorrect information. One of the reasons Realsys Company need a data warehouse is to expedite the data extraction process. Data warehousing will assist in relieving the IT department of the load of data retrieval, as the data warehouse can transform the data gathered and stored into usable information that is easily accessible.

By enabling company executives to easily access historical data and review previous projects that were successful or failed, data warehousing helps organizations improve their bottom line. This enables executives to identify areas where their approach may be adjusted to decrease costs, optimize efficiency, and improve sales (Anupama, Jain, Falk, Deb, & Bantilan, 2020). The Realsys Company's operating data storage facilities include a vast quantity of historical data. Extracting and interpreting this data is a time-consuming operation for the corporate analyst. The analyst will need to raise tickets with the IT department to get information; however, the procedure may take longer than anticipated and may even result in the recovery of incorrect information. One of the reasons Realsys Company need a data warehouse is to expedite the data extraction process. Data warehousing will assist in relieving the IT department of the load of data retrieval, as the data warehouse can transform the data gathered and stored into usable information that is easily accessible.

Additionally, Realsys Company will profit from data warehousing in the following ways:

· Faster and more flexible reporting- When a data warehouse is used, end-users may create reports much more easily. Due to the strength and capabilities of a data warehouse in comparison to a source application, it will assure the development of valuable reports (Melvin, Lynch, Mirkova, & Nacey, 2020). Additionally, this improves uniformity and accuracy and often lowers corporate expenses. Data warehousing enables consumers and company executives to review reports more quickly and conveniently.

· Data security and consistency- Data warehouses provide a consistent structure to all acquired data; this enables company executives to easily assess the data and make business choices. At Realsys, a data warehouse enables management to effortlessly share information with workers and other parties. Additionally, standardizing data from several sources decreases the danger of interpretation mistake and increases the overall quality and reliability of the information. A data warehouse protects data by giving access to only authorized workers who need the information and excluding and restricting unlawful access.

· Support for operational processes- A data warehouse may assist in meeting business demands and day-to-day operations by consolidating information from various organizational departments and producing reports that assist business managers in analyzing the organization's state. For instance, a data warehouse may aid in the aggregation of financial statistics inside a major corporation such as Realsys that utilizes several software systems across its numerous divisions.

Database Schema

A database schema is a representation of all the components and logical configuration of a database. It describes the relationship between objects and information in the database. It could be in the form of visual representation or as a set of formulas known as integrity constraints that govern a database. A database schema indicates how the components that make up the database relate to one another. The process of the creation of a database schema is referred to as data modeling; it involves the use of structured query language. A physical database schema outlines how data is stored physically on a database in terms of files, tables, figures, and indices. A logical database schema, on the other hand, conveys the logical constraints that apply to the stored data.

Schemas are the basic structural element or component of a database; in most database setups, read and write rights are applied to objects or users established at the schema level (Namdeo & Suman, 2020). For instance, a corporation database may have people with varying responsibilities. Each user is assigned a schema, however access rights to multiple schemas are provided separately per user role and according to the granularity of permissions to users who are not assigned the home schema. The majority of database administration solutions do not provide a list of schemas; instead, they provide a list of databases and users. Regardless of the tool used, not all independent entities and relationships included in the source data are put together in the database schema under the same relation.

The database schema shown below depicts user information and their assigned responsibilities inside the organization. Additionally, it reveals the database structure in which their personal data is kept. All data is saved in distinct tables according to its intended usage. Unique constraints such as primary keys and foreign keys enable developers to distinguish each user in the database from the others, as well as their personal data, access permissions, and roles. A primary key identifies a record in a table uniquely, for example, an employee company ID. Due to the fact that this data is unique to certain users, the main key is required. On the other hand, a foreign key refers to a field in one table that is a primary key in another table.

Figure 1: Database Schema

Entity Relationship Diagram (ERD)

This diagram is called an entity relationship diagram (ERD), and it is used in database design throughout the development process. An ERD consists of a variety of symbols and connectors that represent the system's most important entities and their interconnections (Ordonez & Bellatreche, 2019).

Figure 2: Entity Relationship Diagram

Flow of Data

This diagram shows how data moves from source to destination, or storage facility, and back again. An input-output data flow diagram (DFD) shows how a system processes data. An easy-to-understand depiction of the database and system's interconnections is provided by the data flow diagrams Business analysts utilize data flow diagrams to evaluate the current and future state of a system. It is important to utilize diagrams as an aid in highlighting the database's potential shortcomings, vulnerabilities, and structural faults (LIU, YANG, & LI, 2019). In a diagram, the data flow is represented by notations.

· A process is the site where data transformation and processing takes place. The notation shows how entering data flows are transformed into exiting data flows.

· Notations in the data storage. Data stores are the places where the process's data is kept safe and accessible. As the name suggests, data files are a slang term for the system's data storage units.

· A data flow is a conduit via which information packets travel. Information is sent in the form of an arrowhead that appears at the end of the flow connection.

· Objects outside the system that the system interacts with are referred to as "external entities," and are represented in various ways by individuals, systems, or subsystems. The system's inputs and outputs are generated and received by external entities.

Figure 3: Data Flow Diagram

The flow of Data for use in the Data Warehouse

The graphic below depicts the flow of data into the data warehouse and its subsequent processing.

Figure 4: Flow of Data used in the Data Warehouse

Conclusion

Data warehousing, in conclusion, provides several benefits to businesses. Realsys management will benefit from the usage of data warehousing in the process of decision and strategy making since the management will be able to acquire and consume reports more quickly and effectively as a result of this. A data warehouse will assist Realsys Company in obtaining reliable and trustworthy information at their convenience; this will allow management and even staff to have access to data without any delays, ensuring that company operations do not halt or get stale.

References

Anupama, G., Jain, R., Falk, T., Deb, U., & Bantilan, C. (2020). Data warehousing for Open Data sharing and decision support in agriculture: a case study of the VDSA Knowledge Bank and its development process. International Journal of Information Technology, 1-9.

Erkayaoglu, M., & Dessureault, S. (2019). Improving mine-to-mill by data warehousing and data mining. International Journal of Mining, Reclamation and Environment, 33(6), 409-424.

LIU, Y., YANG, Q., & LI, Z. (2019). Cloud computing development environment: from code logic to dataflow diagram. SCIENTIA SINICA Informationis, 49(9), 1119-1137.

Melvin, S. M., Lynch, T. B., Mirkova, N., & Nacey, M. L. (2020). Real time data tracking, analytics data warehousing, and front end reporting system. In: Google Patents.

Namdeo, B., & Suman, U. (2020). Performance Analysis of Schema Design Approaches for Migration from RDBMS to NoSQL Databases. In Advances in Data and Information Sciences (pp. 413-424): Springer.

Ordonez, C., & Bellatreche, L. (2019). Enhancing ER Diagrams to View Data Transformations Computed with Queries. Paper presented at the DOLAP.

Wang, T., Qiu, L., Sangaiah, A. K., Liu, A., Bhuiyan, M. Z. A., & Ma, Y. (2020). Edge-Computing-Based Trustworthy Data Collection Model in the Internet of Things. IEEE Internet of Things Journal, 7(5), 4218-4227.