Project!

Databases and Data warehousing

Name

Institutional Affiliations

Section 1: Design Document

A database is an organized collection of data that has been organized so that it can easily be accessed, managed, as well as, updated. Generally, databases are set up so that one set of software programs allows users to access to all. Table formats that are made up of row and columns are used to store information. A relational database is a collection of data items that are organized as a set of formally-described tables from which data can be accessed or reassembled. While data warehouse is an electronic storage of a large amount of information, and are stored in a manner that is secure, reliable, and easy to manage and retrieve. Usually, data warehouse is the only option for data Collection Company that has a huge collection of data; however, it is not much structured like relational databases. For example, every moment users visit a single page in a website that generates thousand records to be saved.

Data can be outdated; however, it cannot be deleted given that in few months down the line, the data will be used for analysis, which will then generate the future prospects of a business. The analysis may depend on the efficiency; that is how it has been arranged to give clear picture for the analysis and how the data is collected. From Management point, need for use of relational databases and warehousing helps companies to avoid serious challenges in terms of intense competition. For example, in a case where a supermarket has not implemented a data warehouse and at the end finds it very difficult to analyze the products sold, what is not sold, when the sale goes up, and several other queries. These can be seen as attracting challenges because organization has to make decision as to whether, a particular product is a hit or not.

A database schema refers to logically grouping of objects such as tables, views, and stored procedures. Generally, a database schema is the skeleton structure representing the logical view of the entire database. In a company’s business and processes, there are entities such as offices, employees, payments, OrderDetails, Orders, customers and product Lines. Entities in relation to database refer to a single person, place, or thing about which data can be stored. Product lines table will store a list of product and service line category, Orders table will store orders placed by customers, OrderDetails table will store the order line items of each order, Payments table will store payments made by the customers based on the customer’s account, Employees table will store all employees information that includes organizational unit structure, and lastly is the office table which will store sale office data. The following are database tables for the above stated entities that support the company’s business and processes.

CREATE TABLE ‘Products’ (

‘productCode’ varchar(15) NOT NULL,

‘productName’ varchar(70) NOT NULL,

‘productLine’ varchar(50) NOT NULL,

‘productScale’ varchar(10) NOT NULL,

‘productVendor’varchar(50) NOT NULL,

‘productDescription’ Text,

‘addressLine2’ varchar(45) DEFAULT NULL,

‘quantityInStock’smallint (6) NOT NULL,

‘buyPrice’ double,

‘MSRP’ double,

PRIMARY KEY (“customerCode”),

KEY “quantityInStock” (‘quantityInStock’),

CONSTRAINT “productsibfk1” FOREIGN KEY (‘productName’) REFERENCES ‘products’ (“productName”)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

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 “customersibfk1” FOREIGN KEY (‘slaesRepEmployeeNumber’) REFERENCES ‘employees’ (“employeeNumber”)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

In the above customer table, the PRIMARY KEY is “customerNumber”. ‘customerNumber’ in the above table is one of the attributes that uniquely identifies the customer. The FOREIGN KEY is ‘salesRepEmployeeNumber’

CREATE TABLE ‘employees’ (

‘employeerNumber’ int(12) NOT NULL,

‘jobTitle’ varchar(45) NOT NULL,

‘lastName’ varchar(45) NOT NULL,

‘firstName’ varchar(45) NOT NULL,

‘email’ varchar(90) NOT NULL,

‘reporysTo’ int(12) DEFAULT NULL,

‘officeCode’ varchar(12) NOT NULL,

‘postalCode’ varchar(18) DEFAULT NULL,

PRIMARY KEY (“employeeNumber”),

KEY “officeCode” (salesRepEmployeeNumber),

CONSTRAINT “employeesibfk2” FOREIGN KEY (‘officeCode’) REFERENCES ‘offices’ (‘officeCode’)

CONSTRAINT “employeesibfk1” FOREIGN KEY (‘reportsTo’) REFERENCES ‘employees’ (‘employeeNumber’)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

CREATE TABLE ‘offices’ (

‘office Code’varchar(12) NOT NULL,

‘ccity’ varchar(45) NOT NULL,

‘phone’ varchar(45) NOT NULL,

‘addressLine1’ varchar(45) NOT NULL,

‘addressLine2’ varchar(45) DEFAULT NULL,

‘country’ varchar(45) NOT NULL,

‘state’ varchar(45) DEFAULT NULL,

‘city’varchar (45) NOT NULL,

‘postalCode’ varchar(18) NOT NULL,

‘territory’ varchar(12) NOT NULL,

PRIMARY KEY (“officeCode”),

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

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 “orderdetailssibfk2” FOREIGN KEY (“cproductCode”), REFERENCES ‘products’ (‘productCode’)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

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 “ordersibfk1” FOREIGN KEY (“customerNumber”), REFERENCES ‘customers’ (‘customerNumber’)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

CREATE TABLE ‘payments’ (

‘customerNumber’ int(12) NOT NULL,

‘checkNumber’ varchar (45) NOT NULL,

‘paymentDate’ date NOT NULL,

‘Amount’ double NOT NULL,

PRIMARY KEY (‘customerNumber’ ‘

KEY “checkNumber” (“checkNumber”),

CONSTRAINT “paymentsssibfk2” FOREIGN KEY (“checkNumber”), REFERENCES ‘payments’ (‘checkNumber’)

) ENGINE=”InnoDB DEFAULT CHARSET”= latin 1;

The database schema created below shows the entities and attributes of the company’s business and processes

EMPLOYEE

· employeeNumber’ int(12)

· jobTitle’ varchar(45)

· lastName’ varchar(45)

· firstName’ varchar(45)

· email’ varchar(90)

· reporysTo’ int(12)

· officeCode’ varchar(12)

· postalCode’ varchar(18)

INDIXES

PRODUCTS

· productCode varchar(15)

· productName varchar(70)

· productLine varchar(50)

· productScale varchar(10)

· productVendor varchar(50)

· productDescription Text,

· addressLine2 varchar(45)

· quantityInStock smallint (6)

· buyPrice’ double,

· MSRP’ double,

INDIXES

CUSTOMERS

· customerNumber int(12)

· customerName varchar(55)

· contactLastName

· varchar(45)

· contactFirstName

· varchar(45)

· phone varchar(45)

· addressLine1 varchar(45)

· addressLine2 varchar(45)

· city’varchar (45)

· state varchar(45)

· postalCode varchar(18)

· country varchar(45)

· salesRepEmployeeNumber int(12)

· creditLimit double

INDIXES

ORDERS

· orderNumber’ int(11)

· orderDate’ date

· requiredDate’ date

· shippedDate’ date

· status’ varchar(15)

· comments’ text,

· customerNumber’ int(11)

INDIXES

OFFICE

· office Code varchar(12)

· city varchar(45)

· phone varchar(45)

· addressLine1 varchar(45)

· addressLine2 varchar(45)

· country varchar(45)

· state varchar(45)

· city varchar (45)

· postalCode’ varchar(18)

· territory varchar(12)

INDIXES

ORDERDETAILS

· orderNumber int(12)

· productCode varchar(16)

· quantityOrdered int(12)

· priceEach double

· orderLineNumber smallint(8)

INDIXES

PAYMENT

· customerNumber int(12)

· checkNumber varchar (45)

· paymentDate date

· Amount double

INDIXES

Below is an Entity-Relationship (E-R) Diagram of the company’s business and process of the above database schema

Customers

Products

Payment

Order

Office

Employee

OrderDetails

Warehouse

orders

contains

checks

Places

Stores

makes

has

1:M 1:M

1:M

1:M 1:M

1:M

Below, is the description of the above E-R Diagram

* 1 instance of a warehouse stores 0 to many products

* 1 instance of a customer orders 1 to many products

* 1 instance of a customer places 1 to many orders

* 1 instance of an order contains 1 to many OrderDetails

* 1 instance of an employee checks 1 to many OrderDetails

* 1 instance of a customer makes 0 to many payments

* 1 instance of an office has one warehouse

Data Flow Diagrams (DFD) helps in identifying business processes. It looks how data flows through a system and concerned about things like where data will come from and stored. The other importance of DFDs is that, they are useful tools that help in defining boundaries of the system, as well as, have the ability to represent the system at different levels. Below is a DFD relating to tables of the database schema discussed previously. The DFD diagram also shows the flow of data both inputs and outputs for the use of a data warehouse. The diagram maps data between source systems, operational systems, as well as, data warehouses.

Customers

3 Customer 2

Warehouse

2

Receive Order

3

Collect payment

1 Order

Customers 2

2 Invoices

1

Ship products

Section 2: Revised Project Plan

One of the critical factors for project success is having a well-developed project plan. There are various step approaches to follow when creating a project plan which includes; project description, project objectives, project management plan purpose, project deliverables, project milestones, project Roles and Responsibilities, project scope management, project time management, and many others. In this case, we shall update the project plan template, from Project Deliverables 2: Business Requirements with at least three new project tasks each consisting five to ten subtasks.

Some of the new project tasks in project deliverables for the business requirements include the stakeholder requirements, solution requirements and transitional requirements. Stakeholders requirements refers to user needs or user requirements some of the subtasks performed here are, the users’ requirements are documented using use cases and event-response tables. The important and difficult step of designing a software product is determining what the user wants, this is because users often are unable to communicate the entirety of their needs, and the information they provide may also be incomplete (Parker, 2012). For transitional requirements, the subtasks involved during the project development include; data conversion and migration, user acceptance testing, production turnover and transition, user preparation and transition and user access and security rights. Solution requirements, on the other hand, describe the characteristics of a solution that meet the business requirements and stakeholder requirements (Lannon, 2014). Subtasks in solution requirements in project development include validation, user interactions, promotion of tools and engines and many others.

References

Lannon, R. (2014). Four requirements that make a difference in creating solutions. Retrieved on 12 Feb, 2016 from http://www.batimes.com/articles/four-requirements-that-make-a-difference-in-creating-solutions.html

Parker, J. (2012). Business Requirements vs. Functional Requirements. Retrieved on 12 Feb, 2016 from http://enfocussolutions.com/business-requirements-vs-functional-requirements/