data base week 4

Meena_CS660_IP3.docx

Home >Computer Science homework help >data base week 4

DATABASE SYSTEMS DEVELOPMENT & IMPLEMENTATION PLAN 1

DATABASE SYSTEMS DEVELOPMENT & IMPLEMENTATION PLAN 19

Table of Contents 1. Database System Overview 3 1.1 Business Environment 3 1.2 Database system goals and objective 4 2. Entity Relationship Model 7 2.1 Proposed entities 7 2.2 Business rules 8 2.3 Entity–Relationship Model 9 2.3.1 Relationship Types 9 2.3.2 Normalization form 12 2.3.3 Benefit of using database design 14 3. Structured Query Language (SQL) Scripts 15 3.1 Data definition language (DDL) 15 3.2 Data manipulation language (DML) 16 3.3 SQL report 17 3.4 Benefit of using database queries 19 4. Database Administration Plan 20 5. Future Database System Implementation Plan 21 6. References 22

1. Database System Overview

1.1 Business Environment

Office Depot, Inc is an American retail store company founded in 1986 and headquartered in Florida, United States. The company provides office and school supplies with 1400 retail stores and e-commerce sites. The supply includes everything to their customer like latest technology, core school and office supplies, printing and documenting service, furniture and other services like cell phone repair, tech and marketing service etc.

Recently there were too many complaints from existing and new customer that the online site is super glitch and lagging. Another customer posted that the delivery did not come on the scheduled day. And they cannot track down the order because the website does not have tracking information. Also when the website is down, customer service cannot help to see the order details either and therefore, they feel it’s frustrating to order online and therefore want to cancel the order. One other customer posted in the website grievance section that the “label maker” showed available in the stock even though it was out of stock when verified with the customer service representative. With every product not in stock, we lose opportunity of sale which costs the store. This not only affect customer but also affect company. We are so dependent on the data, most of the time staff has to correct accounting report, sales estimates and invoice customer manually which is very time-consuming in an excel sheet.

In order to solve above issues and avoid sales loss, Office Depot must have a database to store and maintain correct count of the products. This database will help inventory management i.e. tracking products, update inventory, find popular or less popular item, loss prevention, track inventory status and perform data mining. The staff can access this database via a computerized database. (Gerald H., Importance of inventory database retail)

1.2 Database system goals and objective

The mission of the company is to become number one retail company by creating inclusive environment and great shopping experience where both customer and employees are respected and valued. To achieve the retail store mission, we are committed to provide secure and robust data base system for our customer. This will monitor and maintain the data; prevent any issues which bring obstruction to our customer; efficiently solve queries or track order. The goals and objective related to the database systems are as follows: (Gerald H., Importance of inventory database retail)

· Order Tracking

· Efficient Inventory Management

· Loss prevention

· Manage customer data and relationship

· Secure and robust database system

· Efficiently respond to customer requests

The proposed database system will help tracking the product which customer always complaint about. Based on the number of product sold, it is easy to find out the most popular or non-popular products. The sales figure can be retrieved from database and then decision can be made which product to include in the stock and which can be discontinued. With this database system, it will be easier to check the number of product available. These will also prevent theft from either customer or employee. Managers can always track the item whenever any discrepancies arise. Once only fewer items are left in the stock, manager can decide to restock them. Not only the number of items it will also shows if any item is available in warehouse or coming to an expiration date and then proper action can be taken. (Gerald H., Importance of inventory database retail)

The database system will have some reporting capabilities, using which we can analyse input data, track productivity, check future trends and customer needs. This is often referred as data mining or pattern finding. These patterns can show the relation between a product sale and the time in a year. For example, during school season some product sell better than other time. This knowledge allows managers to order appropriate product during a time in a year. Not only that it shows the interest of user and while doing online shopping, proper recommendation can be made based on the user interest. Another important feature of database system is redundancy. The same piece of data is stored at different places using master node and slave node concept. This way the data is backup to the salve node always with every write to the database. In case of crash, the backup will help preventing data loss. With addition of user access control the database more secure; redundancy will make database more robust and prevent data loss; updated database will help efficient tracking of data. (5 reasons why your business needs a good database)

The goals of the store are to run a profitable business, provide excellent customer service, retain existing customer, best product along with the business expansion. In order to achieve these goals, certain critical success factor (CSFs) must be identified and set milestone. The CSFs for the business are training to the staff, offer exclusive discount to the customer, business expansion, sales target. Keeping CFs in mind, creating small milestones will ensure that the retail store is providing best shopping experience to the users. This can be achieved:

· If staff is made aware of how the online system works

· In case the website is down using simple query command data can be retrieved using read access of database

· Awareness of mission and goals motives employee to know customer needs

· Auditing the inventory and database builds confidence on the system

· Timely feedback from employee as well customer helps to improve the process

2. Entity Relationship Model

2.1 Proposed entities

Entities in a database design are defined as type of information that is saved in the database. It can be a person, thing, locations or object. Anything that is needed to fit in the database can be accommodated in these categories. (Introduction to Database Design) For our retail, following are the proposed entities:

Customer

A customer entity will have attributes like given game, last name, email address, phone number, and postal address. Since no two customers can have same customer id, therefore customerID is the primary key.

Order

Since customer can order products online and each order will have attributes like order number, ordered product and status. For every order there will be an order ID for transaction.

Products

For the product, product id will be primary key and product name, price and availability are attributes of the product.

Employee

An employee is uniquely identified by its employee id, so we use employee id as primary key. Given names, last name, date of birth, email address, postal address, and phone number are different attribute for the employee.

Store

Store number located to different locations is unique, therefore identified as primary key. Store address and store name are attribute to the store. Based on the postal address of the customer, particular store will send the products to the customer.

Transactions

Every transaction id is unique to the order; therefore transaction id is a unique key while customer name, date and total price are attributes for the transaction.

2.2 Business rules

Documenting business rules are very important in the database design. These rules allow the database designer to develop relationship rules, understand business process, scope of data and constraint to create a data model. They give proper classification of entities, attribute and relationship between different entities. (Business Rules) Following are the business rules for our retail store:

1. A customer can do multiple orders

2. A customer can talk to many employee and an employee can handle to many customer

3. A person who has placed at least 1 order is a customer

4. Every order should have single transaction

5. Single order can have multiple products

6. Every product has a minimum reorder level and based on the availability new order will be placed.

2.3 Entity–Relationship Model

Database design goes through different phases. These phases are requirement analysis; conceptual design; normalization and converting conceptual design e.g. ER model into actual database. (The 4 phases that create database design)

Entity relationship model (ER) is a graphical or conceptual representation of entities and their relationship with each other in database design. And relationship is an association between entities. There are three types of relationship:

2.3.1 Relationship Types

One to One

In this one instance of entity is associated with another instance of entity. E.g. In this database, each employee is associated with only one employee id.

One to Many

An instance can be associated with zero or many instance of related entity. E.g. In this database, one order can have multiple products.

Many to Many

In this relationship, one instance of an entity is associated with zero, one or many instance of another associated entity. E.g. In this database, one employee can handle many customers and one customer can reach out to multiple employees for different orders.

For the ER model of retail store, we will use crow’s foot notation. In crow’s foot notation:

(Dybka P, 2016)

1. Many to many relationship is represented by three-pronged crow’s foot symbol

2. One and mandatory relationship is represented by straight line with perpendicular to the relationship line

3. Optional relationship is represented by empty circle

Fig (1) E-R model

2.3.2 Normalization form

Normalization is way of organizing data in a table in order to reduce the redundancy and dependency of data. It short, it divides larger tables into smaller one and links them using proper relationship. It helps to create database which are cost effective and have better security models.

CustomerName	PostalAddress	OrderNumber	StoreName
Janet Phil	A	X1, X2, X3	store_a
Robert Jones	B	X1, Y2,Y3	store_b
Amy Jackson	C	X1	store_c

Table (1)

1NF (First Normal Form) Rules

In 1NF, each table should contain single value and every record should be unique value. (What is Normalization? 1NF, 2NF, 3NF & BCNF)

CustomerName	PostalAddress	OrderNumber	StoreName
Janet Phil	A	X1	store_a
Janet Phil	A	X2	store_a
Janet Phil	A	X3	store_a
Robert Jones	B	X1	store_b
Robert Jones	B	Y2	store_b
Robert Jones	C	Y3	store_b
Amy Jackson	C	X1	store_c

Table (2)

2NF (Second Normal Form) rules

2NF should satisfy 1NF rule and should have primary key. In this, we divide our table into two different tables; one contains customer information and another contains the product purchased. CustomerID is a primary key in first table and can be identified to associate customer id with the products.

CustomerID	CustomerName	PostalAddress	StoreName
cus_001	Janet Phil	A	store_a
cus_002	Robert Jones	B	store_b
cus_003	Amy Jackson	C	store_c

Table (3)

CustomerName	OrderNumber
cus_001	X1
cus_001	X2
cus_001	X3
cus_002	X1
cus_002	Y2
cus_002	Y3
cus_003	X1

Table (4)

3NF (Third normal form) Rules

This should satisfy the condition of 2NF and no functional dependencies. (Guru99, n.d)

StoreName	StoreID
store_a	001
store_b	002
store_c	003

Table (5)

CustomerID	CustomerName	PostalAddress	StoreID
cus_001	Janet Phil	A	001
cus_002	Robert Jones	B	002
cus_003	Amy Jackson	C	003

Table (6)

CustomerName	OrderNumber
cus_001	X1
cus_001	X2
cus_001	X3
cus_002	X1
cus_002	Y2
cus_002	Y3
cus_003	X1

Table (7)

2.3.3 Benefit of using database design

One of the benefits of having this database design is data integrity and fast retrieval from storage. Using 3NF normalization, we will reduce the amount of redundant data and create smaller database. Since there is less data stored, data retrieval will be faster and user will have great experience using website.

3. Structured Query Language (SQL) Scripts

3.1 Data definition language (DDL)

Data definition language (DDL) allows user to create and modify database objects. The objects can be schemas, tables, views etc. The common DDL querying in SQL are CREATE, ALTER, and DROP. For office Depot Company, we will create a database and then create table for all the entities we have defined under E-R diagram.

-- Create database officeDepot

CREATE DATABASE officeDepot;

-- Switch the database

USE officeDepot;

-- Create Customer Table

CREATE TABLE tblcustomer(

CustomerID int IDENTITY(0001,1) NOT NULL PRIMARY KEY, -- Start customer Id with 0001

GivenName varchar(255) NOT NULL,

LastName varchar(255),

EmailAddress varchar(255) NOT NULL,

PhoneNumber int,

PostalAddress varchar(255) NOT NULL,

);

-- Create employee table

CREATE TABLE tblemployee(

EmployeeID int IDENTITY(1000800,1) NOT NULL PRIMARY KEY,

GivenName varchar(255) NOt NULL,

LastName varchar(255),

DateOfBirth date NOT NULL,

Designation varchar(255) NOT NULL,

EmailAddress varchar(255) NOT NULL,

PhoneNumber int,

PostalAddress varchar(255)

);

-- Create order table

CREATE TABLE tblorder(

OrderNumber int IDENTITY(00001,1) NOT NULL PRIMARY KEY,

ProductName varchar(255) NOT NULL,

OrderStatus varchar(10) NOT NULL,

OrderDate varchar(10) DEFAULT GETDATE() FOREIGN KEY,

CustomerID int IDENTITY(0001,1) NOT NULL FOREIGN KEY

);

-- Create product table

CREATE TABLE tblproduct(

ProductID int IDENTITY(00001,1) NOT NULL PRIMARY KEY,

ProductName varchar(255) NOT NULL,

ProductAvailability int,

Price float NOT NULL

);

-- Create store table

CREATE TABLE tblstore(

StoreID int IDENTITY(00001,1) NOT NULL PRIMARY KEY,

StoreName varchar(255),

StoreAddress varchar(255),

StoreCountry varchar(255)

);

-- Create transaction table

CREATE TABLE tbltransaction(

TransactionID int IDENTITY(00001,1) NOT NULL PRIMARY KEY,

OrderNumber int NOT NULL FOREIGN KEY,

PaymentDeatils varchar(10),

PaymentDate varchar(10) DEFAULT GETDATE(),

TotalPrice float NOT NULL

);

3.2 Data manipulation language (DML)

Data manipulation language (DML) allows user to manipulate data in a database using SQL language. The manipulation includes inserting data, retrieving, modifying existing data or deleting data. The common example of DML is SELECT, INSERT, UPDATE, DELETE.

Use officeDepot

-- Insert 20 records in each table for testing purpose

DECLARE @count INT = 0;

WHILE @count < 20

BEGIN

-- Insert 20 records in customer table

INSERT INTO tblcustomer (GivenName,LastName,EmailAddress,PostalAddress)

VALUES ('Lars', 'Monsen','[email protected]','20 Newyork');

-- Insert 20 records in employee table

INSERT INTO tblemployee (GivenName,DateOfBirth,Designation,EmailAddress)

VALUES ('Lars', '01/01/1990','Customer service executive','20 Texas');

-- Insert 20 records in order table

INSERT INTO tblorder (ProductNumber1,OrderStatus)

VALUES ('officefiles', 'In-PROGRESS');

-- Insert 20 records in product table

INSERT INTO tblproduct (ProductName,ProductAvailability,Price)

VALUES ('officefiles', 5,10.20+@count);

-- Insert 20 records in store table

INSERT INTO tblstore (StoreName)

VALUES ('a');

-- Insert 20 records in transaction table

INSERT INTO tbltransaction (PaymentDeatils, PaymentDate, TotalPrice)

VALUES ('PAID', '01/02/2018',10.50+@count);

SET @count = @count + 1;

END;

--Delete an entire order by using the unique identifier for that order.

BEGIN TRAN

DELETE FROM tblorder

WHERE OrderNumber=00001;

COMMIT TRAN

--Update the price of a product by using the unique identifier for that product

BEGIN TRAN

UPDATE tblproduct

SET Price = 200

WHERE ProductID = 00001;

COMMIT TRAN

-- Disaply the product price in descending order

SELECT * FROM tblproduct

ORDER BY Price DESC;

-- Display the product where the availability count is less than 25

SELECT * FROM tblproduct

WHERE ProductAvailability<25;

-- For winter sale update the price to 10% discount

BEGIN TRAN

UPDATE tblproduct

SET Price = Price - (10/100)*Price

WHERE Price BETWEEN 100 AND 2000;

COMMIT TRAN

3.3 SQL report

Database report is very important for both analysis and decision making. This is not only important for the company to evaluate sales report but also for the customer to see their order data over a period of time. To extract data, a query is run and in this case SQL is used to get the report of the sales or other important information.

Use officeDepot

SELECT *

FROM tblorder INNER JOIN tblproduct ON

tblorder.ProdctName=tblproduct.ProdctName INNER JOIN

tblcustomer ON tblorder.CustomerID=tblcustomer.CustomerID

GROUP BY tblcustomer.CustomerID

-- (1) Total revenue (sales) per month, grouped by customer

SELECT YEAR(OrderDate) [Year], MONTH(OrderDate) [Month],

DATENAME(MONTH,OrderDate) [Month Name], SUM(tblproduct.Price) [Sales Count]

FROM tblproduct

GROUP BY YEAR(OrderDate), MONTH(OrderDate),

DATENAME(MONTH, OrderDate), CustomerID

-- (2) Total revenue (sales) per month, grouped by product

SELECT YEAR(OrderDate) [Year], MONTH(OrderDate) [Month],

DATENAME(MONTH,OrderDate) [Month Name], SUM(tblproduct.Price) [Sales Count]

FROM tblproduct

GROUP BY YEAR(OrderDate), MONTH(OrderDate),

DATENAME(MONTH, OrderDate), ProdctName

-- (3) Get the product1 and product2 sales

SELECT CustomerID, [ProductName2] AS ProductName2, [ProductName1] AS ProductName1

FROM

(SELECT CustomerID, Product, TotalPrice

FROM tblproductsale ) ps

PIVOT

(

SUM (TotalPrice)

FOR Product IN

([ProductName2], [ProductName1])

) AS pvt

-- (4) Get store count by country

SELECT COUNT(StoreID), StoreCountry

FROM tblstore

GROUP BY StoreCountry

ORDER BY COUNT(StoreID) DESC;

-- (5) Check the products count for less availability

SELECT * FROM tblproduct

WHERE ProductAvailability<25;

GROUP BY ProductName

-- (6) Get the payment details (Pending or Paid) by payment date

SELECT COUNT(PaymentDeatils), PaymentDate

FROM tbltransaction

GROUP BY PaymentDate

ORDER BY COUNT(PaymentDeatils) DESC;

3.4 Benefit of using database queries

By creating a database with proper SQL query command for the report, it is possible to do inventory management. This will help to find if any product is low in stock. With the help of order id, product delivery can be tracked well for the customer.

4. Database Administration Plan

5. Future Database System Implementation Plan

6. References

(1) 5 reasons why your business needs a good database. Retrieved from: https://www.nibusinessinfo.co.uk/content/5-reasons-why-your-business-needs-good-database

(2) Business Rules, Retrieved from: http://databasemanagement.wikia.com/wiki/Business_Rules

(3) Data definition language. Retrieved from: https://www.techopedia.com/definition/1175/data-definition-language-ddl

(4) Data manipulation language. Retrieved from: https://www.techopedia.com/definition/1179/data-manipulation-language-dml

(5) Database report. Retrieved from: https://www.techopedia.com/definition/24439/database-report

(6) Dybka P. 2016. Crow’s foot notation. Retrieved from: http://www.vertabelo.com/blog/technical-articles/crow-s-foot-notation

(7) Gerald H., Importance of inventory database retail. Retrieved from: https://yourbusiness.azcentral.com/importance-inventory-databases-retail-21485.html

(8) Introduction to Database Design, Retrieved from: https://www.datanamic.com/support/lt-dez005-introduction-db-modeling.html

(9) The 4 phases that create database design. Retrieved from: https://medium.com/@deeplogica/the-4-phases-that-create-database-design-31571111e683

(10) What is Normalization? 1NF, 2NF, 3NF & BCNF. Retrieved from: https://www.guru99.com/database-normalization.html

CustomerOrderEmployee

Customer Order Employee M1 M2 M3 M4

CustomerProducts

Customer Products M1 M2 M3 M4

OrderOrderTransaction

Order Order Transaction M1 M2 M3 M4

OrderOrderProducts

Order Order Products M1 M2 M3 M4

ProductProductIDintPKProductNameStrPricefloatAvailabilityint

Product ProductID int FK PK ProductName Str FK PK Price float FK PK Availability int FK PK

EmployeeOrderProductCustomerStoreTransactionEmployee IDintPKGivenNameStrPostalAddressStrDateOfBirthintEmailAddressStrPhoneNumberintOrderNumberintPKProductNameStrOrderStatusStrProductIDintPKProductNameStrPricefloatCustomerIDStrPKEmailAddressStrPhoneNumberintPostalAddressStrStoreIDintPKStoreNameStrStoreAddressintTransactionIDintPKPaymentDetailintDateintTotalPricefloatLastNameStrDesignationStrAvailabilityintLastNameStrGivenNameStrOrderDateStrCustomerIDintFKStoreCountryStrOrderNumberintFKContainsorderscommunicateshasmakes

Employee Order Product Customer Store Transaction Employee ID int FK PK GivenName Str FK PK PostalAddress Str FK PK DateOfBirth int FK PK EmailAddress Str FK PK PhoneNumber int FK PK OrderNumber int FK PK ProductName Str FK PK OrderStatus Str FK PK ProductID int FK PK ProductName Str FK PK Price float FK PK CustomerID Str FK PK EmailAddress Str FK PK PhoneNumber int FK PK PostalAddress Str FK PK StoreID int FK PK StoreName Str FK PK StoreAddress int FK PK TransactionID int FK PK PaymentDetail int FK PK Date int FK PK TotalPrice float FK PK LastName Str FK PK Designation Str FK PK Availability int FK PK M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 M1 M2 M3 M4 LastName Str FK PK GivenName Str FK PK OrderDate Str FK PK CustomerID int FK PK StoreCountry Str FK PK OrderNumber int FK PK Contains orders communicates has makes

CustomerOrderProducts

Customer Order Products M1 M2 M3 M4