Final Project Plan

Database and Data Warehousing Design

Thien Thai

CIS599

Professor Wade M. Poole

Strayer University

Feb 20, 2020

Database and Data Warehousing Design

Introduction

Technology has highly revolutionized the world of business –hence presenting more challenges and opportunities for businesses. Companies which fail to embrace and incorporate technology in their operations risks being edged out of the market due to stiff competition witnessed in the market today. On the flipside, cloud-based technology allows businesses to “easily retrieve and store valuable data about their customers, products, and employees.” Data is an important component that help to support core business decisions. In today’s highly competitive and constantly evolving business world, embracing cloud-based technology business managers an opportunity to make informed and result-oriented decisions regarding day-to-day organizational operations (Dimitriu & Matei, 2015).

Notably, business growth and competitiveness depends on its ability to transform data into information. Data warehousing and adoption of relational databases are some of cloud-based technologies which have positively impacted on businesses. The two technologies have had a strategic value to companies –helping them to have the extra edge over their competitors. Both data warehousing and relational databases help businesses to “take smart decisions in a smarter manner.” However, failure to adopt these cloud-based technologies has hindered business executives’ ability to make experienced-based and fact-based decisions which are vital to business survival. Both “databases and data warehouses are relational data systems” which serve different and equally crucial roles within an organization. For instance, data warehousing helps to support management decisions while relational databases help to perform ongoing business transactions in real-time. Basically, embracing cloud-based technologies within the organization will help to give the company a competitive advantage in the market. However, the adoption and maintenance of such technologies require full support and endorsement of the business management. Organizational management must understand the feasibility, functionality, and the importance of embracing such technologies. Movement towards relational databases and data warehousing requires a lot of funding –hence the need to convince the management to support and fund them. This paper seeks to explore the concepts of data warehousing, relational databases, their importance to the business, as whey as their design.

“Importance of Data Warehousing and Relational Databases”

Today, technology has changed the market landscape. Business are striving to adopt cloud-based technology in order to improve efficiency in business functions –among them analytical queries as well as transactional operations. Both relational databases and data warehousing are two indispensable cloud-based technologies which can offer unparalleled benefits to the organization as far as data manipulation, management, and analysis is concerned.

Relational databases use OLTP to allow businesses “store current transactions and enable fast access to specific transactions for ongoing business processes” (Letkowski, 2015). Conversely, data warehouse uses OLAP to “store large quantities of historical data and enable fast, complex queries across all data” (Sohrabi et al., 2016). Generally, the two technologies are both relational data software though they serve different purposes. However, both relational databases and data warehouses can be used to improve internal organizational functions for the purpose of enhancing efficiency and competitiveness.

Relational databases offer the following benefits:

· Accuracy: Data stored in relational databases is reliable and accurate since the issue of data redundancy is eliminated.

· Flexibility: Relational databases are very flexible and they allow users to conduct multiple complex queries in an easily and timely manner.

· Trustworthy: The model used in relational databases is quite simple and reliable. The issue of data integrity is well catered for.

· Collaboration: It allows all the team members in an organization to access the same database with minimal strain.

· Security: The organizational management can instruct database managers to allow only a few selected members of the team to access specific data from the database. Basically, only authorized users can be allowed to access the data stored in relational databases –hence ensuring data security (Munir & Anjum, 2018). Thus, this data cannot be manipulated quite easily.

· Real-time data update: Relational databases are quite fast and effective –hence allowing real-time data manipulation and updates.

On the other hand, data warehousing is perceived as the most advanced business intelligence tool which has “gained a lot of popularity in recent times.” Data warehouse has increasingly become vital to business since they help to:

· Support business decisions: Currently, successful businesses rely on informed decision making in regard to various issues affecting the organization. Data warehousing offers decision makers with data-driven facts that facilitate the decision-making process. Through queries and analysis of the data that exists in data warehouses, business managers gain key insights which guide the process of making fact-based decisions which help the business to stay ahead of the competitors (Shahid et al., 2016).

· Ensure consistency: Notably, data warehouses consist of data collected from various sources using a uniform format. Thus, data in warehouses in standardized –something that helps “to reduce the risk of error in interpretation and improves overall accuracy.” This helps to provide reliable and unbiased data that can be used to facilitate informed decisions.

· Enhance efficiency: Data warehouses carry the organization’s history can be accessed quite easily by clicking a few buttons. This helps to make comparisons which ensures that improved decisions are made in future.

· Generate High ROI: Data warehouses help to save the company from unnecessary costs hence improving the overall return on investment.

Database Schema

Effective data management requires proper designing of a data schema. Poorly designed schemas are hard to maintain and administer. Basically, the following will help to design a relational database schema:

· Focus on Business Requirements: All parts of businesses processes must be captured in a database schema. The business useful information must be captured in the design

· Security Definition: The security of the schema must be given a top priority in order to protect the data in the database. Defining the security level will help to clear give or deny users access to the tables.

· Physical layout: This helps to clearly define the data environment and attributes. All the objects must be mapped before engaging in actual creation of the schema.

· Naming conventions: It is equally vital to define and name the attributes surrounding each element. Data types also must be named correctly in order to support “successful development and query performance.”

Figure 1: Database Schema

Database Tables

Primary key is used to identify each unique row in a database table. In the product table for instance, Product ID is the primary key that identified each unique coke diet product.

A foreign key can be used to bring out the relationship between the above two tables hence allowing the user to navigate between “two instances of an entity” (Cleve et al., 2015). For instance, the two tables can be combined to establish the flavor of each product.

For instance:

Notably, foreign key is used to maintain the integrity of data in the tables through a concept known as “Referential Integrity.”

Normalization of the two tables:

Entity-Relationship (ER)

This shows the relationship between two entities –customer and order. Customer ID and Order Id are two unique attributes linking the two.

Figure 2: Entity Relationship

Data Flow Diagram