Database data warehousing design document and project plan

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;

Entity-Relationship (E-R) Diagram

Entity relationship diagram serves as the method to deliver a conceptual model, and the rational behind developing ER diagram is to provide the logical presentation of organizational data. The ERD allows sketching of database in order to model data. ERD also identifies entities and concept within the system and delivers the relationship of these entities. One of the benefits of ERD is that the ER diagram could represent a database schema. The ERD also assists designers and analysts to gain a better understanding on the information in the database. The ERD is used to communicate the logical structure of the database to the users. Typically, an ERD describes data in the following format:

Entities,

Relationship between entities,

Attributes of entities.

The Appendix 1 reveals the ERD of Database Data Warehousing for a data company. As being presented in Appendix 1, an entity reveals the collection of entities having the same attributes. An entity is an object that an organization intends to maintain as data, and the ERD assists in assigning a name to each entity. This paper assigns names to the following entities CUSTOMER, EMPLOYEE, ORDERS PAYMENTS and PRODUCT. The ERD also reveals the Attributes that reveal the characteristics of an entity. For example, in the database warehouse, the CUSTOMER has the following attributes.