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...

char (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'…