Data warehousing design for a data collection company

Database Data Warehousing Design

Data warehousing is a powerful tool that business organizations use to enhance competitive advantages. Data warehousing supports business decision by collecting, organizing and consolidating data for analysis and reporting using tools such as OLAP (online analytical processing ) and data mining. Typically, the relational database technology is generally being used to design a data warehousing and a relational database is a database having collection of tables, and organized based on the relational model. One of the main goals of business organizations is to use data to leverage their strategic competitive advantages. Typically, the data warehousing has become a strategic priority because of the enormous amount of data that organizations need to analyze. Using a relational database, organizations have been able to develop a data warehousing that could assist management to make effective decision. (Microsoft, 2012).

Fundamental objective of this paper is to design the data warehousing for a data collection company using a relational database. In the database framework, the paper incorporates data warehousing to enhance greater understanding of what data warehousing can deliver to the company.

Data Warehouse Tool

The essence of data warehousing is to provide superior performances to business intelligence. Typically, data warehouse organizes data efficiently to assist businesses to make effective decision-making. OLAP is an effective data warehouse tool that organizes data into multidimensional cubes, which assists in summarizing data in various ways. For example, a query request of the quantity sold for range of product across different geographical regions could be answered within few seconds. The inherent performances of data warehouse are the ability to rapidly summarize the data for analytical queries. (Tera Data, 2012).

Moreover, the data warehousing delivers data mining tool that applies a complex algorithm and sophisticated technology to analyze data for decision-making. The paper provides the database schema that supports the data warehousing requirements.

Creating a Database Schema

Database schema is a text-based or graphical representation used to generate database physical model. The database schema consists of the following tables for the Data Collection Company as presented below:

The database schema:

Customers: stores customer's data

Products: stores a list of services delivered.

Product Lines: stores a list of product and service line category.

Orders: stores orders placed by the customers.

OrderDetails: stores the order line items of each order.

Payments: stores payments made by the customers based on customer's account.

Employees: stores all employee information, which includes organizational unit structure.

Offices: stores sale office data.

Using the information above, the paper provides the following database schema.

CREATE TABLE 'customers' (

'customerNumber' int (12) NOT NULL,

'customerName' varchar (55) NOT NULL,

'contactLastName' varchar (45) NOT NULL,

'contactFirstName' varchar (45) NOT NULL,

'phone' varchar (45) NOT NULL,

'addressLine1' varchar (45) NOT NULL,

'addressLine2' varchar (45) DEFAULT NULL,

'city' varchar (45) NOT NULL,

'state' varchar (45) DEFAULT NULL,

'postalCode' varchar (18) DEFAULT NULL,

'country' varchar (45) NOT NULL,

'salesRepEmployeeNumber" int (12) DEFAULT NULL,

"creditLimit' double DEFAULT NULL,

PRIMARY KEY ("customerNumber"),

KEY "salesRepEmployeeNumber" ('salesRepEmployeeNumber'),

CONSTRAINT "customers_ibfk_1" FOREIGN KEY ("salesRepEmployeeNumber") REFERENCES 'employees' ("employeeNumber")

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'employees' (

'employeeNumber' int (12) NOT NULL,

'lastName' varchar (45) NOT NULL,

'firstName' varchar (45) NOT NULL,

'extension' varchar (11) NOT NULL,

'email' varchar (90) NOT NULL,

'officeCode' varchar (12) NOT NULL,

'reportsTo' int (12) DEFAULT NULL,

'jobTitle' varchar (45) NOT NULL,

PRIMARY KEY ("employeeNumber"),

KEY "reportsTo" ("reportsTo"),

KEY "officeCode" ('officeCode'),

CONSTRAINT "employees_ibfk_2" FOREIGN KEY ("officeCode') REFERENCES 'offices' ('officeCode'),

CONSTRAINT "employees_ibfk_1" FOREIGN KEY ("reportsTo") REFERENCES "employees" ('employeeNumber')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'offices' (

'officeCode' varchar (12) NOT NULL,

'city' varchar (45) NOT NULL,

'phone' varchar (45) NOT NULL,

'addressLine1' varchar (45) NOT NULL,

'addressLine2' varchar (45) DEFAULT NULL,

'state' varchar (45) DEFAULT NULL,

'country' varchar (45) NOT NULL,

'postalCode' varchar (18) NOT NULL,

'territory' varchar (12) NOT NULL,

PRIMARY KEY ('officeCode')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'orderdetails' (

'orderNumber' int (12) NOT NULL,

'productCode' varchar (16) NOT NULL,

'quantityOrdered' int (12) NOT NULL,

'priceEach' double NOT NULL,

'orderLineNumber' smallint (8) NOT NULL,

PRIMARY KEY ("orderNumber','productCode"),

KEY "productCode" ("productCode"),

CONSTRAINT "orderdetails_ibfk_2" FOREIGN KEY ("productCode") REFERENCES 'products' ('productCode'),

CONSTRAINT "orderdetails_ibfk_1" FOREIGN KEY ("orderNumber') REFERENCES 'orders' ("orderNumber')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'orders' (

'orderNumber' int (11) NOT NULL,

'orderDate' date NOT NULL,

'requiredDate' date NOT NULL,

'shippedDate' date DEFAULT NULL,

'status' varchar (15) NOT NULL,

'comments' text, 'customerNumber' int (11) NOT NULL,

PRIMARY KEY ('orderNumber'),

KEY "customerNumber" ("customerNumber"),

CONSTRAINT "orders_ibfk_1" FOREIGN KEY ("customerNumber") REFERENCES 'customers' ('customerNumber')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'payments' (

'customerNumber' int (12) NOT NULL,

'checkNumber' varchar (45) NOT NULL,

"paymentDate" date NOT NULL,

"amount" double NOT NULL,

PRIMARY KEY ("customerNumber," 'checkNumber'),

CONSTRAINT "payments_ibfk_1" FOREIGN KEY ("customerNumber") REFERENCES 'customers' ('customerNumber')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

CREATE TABLE 'productlines' (

'productLine' varchar (45) NOT NULL,

'textDescription' varchar (4800) DEFAULT NULL,

"htmlDescription" mediumtext, 'image' mediumblob,

PRIMARY KEY ('productLine')

) ENGINE="InnoDB DEFALT CHARSET"=latin1;

CREATE TABLE 'products' (

'productCode' varchar (20) NOT NULL,

'productName' varchar (80) NOT NULL,

'productLine' varchar (55) NOT NULL,

'productScale' varchar (15) NOT NULL,

'productVendor' varchar (55) NOT NULL,

"productDescription" text NOT NULL,

"quantityInStock" smallint (6) NOT NULL,

"buyPrice" double NOT NULL,

'MSRP' double NOT NULL,

PRIMARY KEY ("productCode"),

KEY "productLine" ('productLine'),

CONSTRAINT "products_ibfk_1"FOREIGN KEY ("productLine") REFERENCES 'productlines' ('productLine')

) ENGINE="InnoDB DEFAULT CHARSET"=latin1;

Creating a Data Flow Diagram (DFD)

Data flow diagram (DFD) reveals the various components in the database. Typically, DFD is a graphical illustration used to understand the information process within the information systems. The fig 1 reveals the data flow diagram for a data company.

Fig 1: Data Flow Diagram (DFD)

Section 2: Project Plan

This section provides the project plan and the detailed project plan is presented in the table and Gantt chart below.

PROECT PLAN FOR DATA WAREHOUSING

64 days?

Mon 03/06/13

Wed 28/08/13

1

INFRASTRUCTURE PHASE

15 days?

Wed 05/06/13

Mon 24/06/13

1.1

Project Organization

3 days?

Thu 06/06/13

Sat 08/06/13

1.2

High-level Business Requirements

5 days?

Mon 10/06/13

Fri 14/06/13

3

1.3

Business Requirement Prioritization

4 days?

Mon 17/06/13

Thu 20/06/13

4

1.4

Project Scope and Plan

2 days?

Fri 21/06/13

Mon 24/06/13

5

1.5

Building Technical Infrastructure

4 days?

Tue 25/06/13

Fri 28/06/13

6

2

DEISIGN PHASE

20 days?

Mon 01/07/13

Fri 26/07/13

7

2.1

Architectural Design

5 days?

Mon 01/07/13

Fri 05/07/13

2.2

Database Design

2 days

Wed 10/07/13

Thu 11/07/13

10

2.3

Data Warehousing Design

4 days?

Mon 15/07/13

Thu 18/07/13

13

2.4

Data Security Design

5 days?

Mon 08/07/13

Fri 12/07/13

2.5

Financial Data Mart

4 days?

Mon 15/07/13

Thu 18/07/13

2.6

Sale Data Mart

3 days?

Fri 19/07/13

Tue 23/07/13

14

3

IMPLEMENTATION PHASE

23 days?

Mon 29/07/13

Wed 28/08/13

3.1

Database Implementation

6 days?

Thu 01/08/13

Thu 08/08/13

3.2

Data Warehousing Implementation

2 days?

Fri 09/08/13

Mon 12/08/13

18

3.3

Data security Implementation

4 days?

Tue 13/08/13

Fri 16/08/13

19

3.4

Financial Data Mart Implementation

3 days?

Mon 19/08/13

Wed 21/08/13

20

3.5

Sale Data Mart Implementation

3 days?

Thu 22/08/13

Mon 26/08/13

21

3.6

Launching Data Warehousing Online

2 days

Tue 27/08/13

Wed 28/08/13

22

Fig 2: Gantt chart for the Project Plan.