Enterprise database design for expanding bookstore chain

Database Administrator for Department Store

Scenario

The department store has expanded in the local region by opening five more bookstores, and the bookstore has launched a series of marketing campaign to increase sales and attract new customers. The objective of this technical report is to develop a plan to create and maintain an enterprise-wide database system that will assist the bookstore to hold the inventory and sales data. The database design will assist the organization to support the new marketing strategy, and align with the product initiatives.

Identification of the Potential Departmental and Sales Transaction to be Store within the Database

In the United States, the enterprise-wide relational database is very critical for the retail bookstore envisaging increasing sales within the contemporary business environment. To design a database to support the accuracy of the business information for the departmental store, there is a need to identify the potential sales transaction. This identification will avoid the redundant data within the enterprise relational database. The principal entities involved in the departmental and sales transactions for the bookstore are as follows:

Customers- They are the people who buy the product from the bookstore. The main attributes for customer's identifications are name, identification number, and address.

Customer History- attributes to identify customer's history are date of delivery, history identification number, and quantity delivered.

Publishers -- These companies supply book stocks to the stores. Main attributes are name, publisher identification number, address, and contact number.

Book- These are the product supplied to the bookstore and which are available for purchase. Main attributes are the ISBN, author, title, and price, amount in the inventory, subject area, and year of publication.

Store -- This is the location or actual premises where the transaction takes place.

Employees -- staff who work in the store and deal with the customers.

Transaction: Process of buying and selling of book product. Main attributes for identification are transaction number, number of books bought and sold, and the amount of money involved in the transaction.

All these makes up of the entities in the database design, and each entity has attributes allotted to them. The table below reveals the attributes for each entity.

Entity

Attribute

Customer

Customer ID

First Name

Last Name

Address

APT

City

Zip

Email

Book

Book ID

Book Number

ISBN

Publication Date

Publisher Name

Type

Price

Quantity

Employee

Employee ID

First Name

Last Name

Address

APT

City

Zip

Email

Hire Date

Term Date

Term Note

Book Author

Author ID

First Name

Last Name

Address

APT

City

Zip

Email

Date of Birth

Order Transaction

Price

Quantity

Total Price

Ship Date

Ship Type

Customer ID

Employee ID

Book ID

Sales

Products

Customer

Employee

Store

Store

Store Name

Address

Publisher

Publisher ID

Address

APT

City

Zip

Email

Web Address

All these attributes provide the information for the successful transactions in the departmental bookstore. The next step is to present the business rules associated with the business transactions as well the storage in the database. Bookstore transaction is similar to the business transaction; however, the department store will organize and store data to facilitate easy transaction.

2. Database Solution and Business Rules for the Sales Transaction

A business rule is a statement that imposes a form of constraint on an aspect of database. The business rules assist the organization to conduct business transactions effectively, and businesses rules provide business solutions for the database. The business rules are important for the database because they provide important method of data modification as well as the method data are created and deleted. The rules assist in determining the structure of the database. The proposed database provides the following business rules for the database design:

Rule 1:

When a customer order a book from the bookstore, the following business process must take place:

An order received from a customer.

An employee checks the inventory for product availability,

Employee confirms the customer's order,

The employees contacts warehouse,

The bookstore ships the product and invoice to the customer.

Rule 2

Department store has 5 stores that sell many books, and this is translated into a field constraint within the dataset. The designated code is also assigned to each store location. In the field, the assigned codes are valid.

Rule 3

A customer is allowed to generate many invoices,

Each invoice could be generated by one customer,

An invoice should contain one or more line, and each line associates with one invoice,

Publisher is allowed to supply many books,

Rule 4:

Each vendor is allowed to supply at least one product. This is represented by the relationship constraint within the database.

3. Evaluation of all Relationships of Each Entity within the Database

With the identification of the business rules needed to design the database, the next step is to work out the relationship among the entities:

Customer

Book

Employee

Book Author

Order Transaction

Stores

Publisher

The report defines the relationship between entities because each entity will have a relationship with other entity in the database. The next section arrives at the accurate relationship of the entities revealing 35 relationships where each entity has 5 relationships.

1. Customers ( Sales: 1 customer can buy books as many times as he wishes.

2. Sales ( Customers: 1 or more sale is made from customer

3. Customers ( Book: 1 customer can purchase multiple books

4. Books (Customers: 1 book can be purchased by multiple customers

5. Customers ( Stores: 1 customer can purchase a book from multiple shops

6. Stores ( Customers: 1 store can provide services to multiple customers

7. Stores ( Books: 1 store can sell books to multiple customer

8. Books ( Stores: 1 books can be sold in multiple stores

9. Stores ( Sales: 1 store can make multiple sales

10. Sales ( Stores: 1 store can make 1 sale at the same time

11. Books ( Sales: 1 book can be purchased in multiple sales

12. Sales ( Books: 1 sale can be made out of multiple times

13. Customers ( Employees: 1 customer can only be served by 1 customer

14. Employees ( Customers: 1 employee can serve only 1 customer

15. Stores ( Employees: 1 store can occupy multiple employees

16. Employees ( Stores: 1 employee can serve in multiple stores

17. Publisher ( Customers: 1 publisher may be requested by multiple customers

18. Customers ( Publisher: 1 customer can request multiple publishers

19. Books ( Publisher: 1 book may be supplied by 1 publisher

20. Publisher ( Books: 1 publisher can supply multiple books

21. Sales ( Publishers: 1 sale can be made from multiple publishers

22. Publishers ( Sales: 1 publisher can supply multiple sales

23. Stores ( Publishers: 1 store can provide stock to multiple publishers

24. Publisher ( Stores: 1 publisher can supply stocks to multiple stores

25. Employees ( Publisher: 1 employee can serve multiple publishers

26. Publisher ( Employees: 1 publisher can be occupied by multiple employees

27. Employee ( Sales: 1 employee can make 1 sale

28. Employee ( Sales: 1 employee can make multiple sales

29. Books ( Employees: 1 book can be sold by multiple employees

30. Employees ( Books: 1 employee can sell multiple books.

31. Employee --> Book Author: 1 employee can order from 1 book author.

32. Book Author. --> Employees: 1 book author can supply book to many employees

33. Store --> Order Transaction: 1 store can make multiple order transactions.

34. Order Transaction -->Store: 1 order transaction can occur in one store.

35. Employee --> order Transaction: 1 employee can be in charge of 1 order transaction at a time.

Overview of all the relationships reveals that there are 35 relationships based on 7 entities multiply by 5 relationships. The next step is to provide the cardinality of each relationship and the annotated diagram of each relationship. Each cardinality relationship takes the following form:

One-to-One (1:1)

One-to-many (1: M)

Many-to-Many (M:N)

Many-to-One (M: 1)

Customers --> Sales; --> 1: N

Customers --> Books; --> M: N

Customers --> Stores; --> M: N

Sales --> Books; --> M: N

Stores --> Sales; --> 1: N

Stores --> Books; --> M: N

These relationships are shown in the Entity-Relation Diagram in the Fig 1.

Fig 1: Entity-Relation Diagram for the Department Store

4. Best Practices to use Databases for Retaining Customers and Increasing Sales

a. Use of Big Data tools for forecasting sales and inventory in the Department Store.

In the contemporary business environment, data are interwoven in the department store and critical to organizational performances. Similar to human capital and hard assets, data are essential tools that enhance organizational competitive advantages. The Big Data, which refers to the large pool of data brought together has been the useful tool that enhances decision-making. Typically, the big data can assist department store to forecast sales and inventory useful to reduce waste and increase quality of product and services.

The Big Data is very useful to segment and target potential customer. For example, Big Data tool is very useful to identify customers who are nearing purchasing decision, making the Big Data to be useful tool to enhance competitive advantages and growth for the department store. Typically, The Big Data tool is very useful to store an unlimited amount of information about the customer preference, and the Big Data is a predictive model to accurately predict customer's behaviors. Thus, department store could use Big Data to store large pool of data about the past sales in order to forecast the future sales.

Moreover, department store can use the Big Data tool to understand the customer preference based on the past data, the data collected could assist department stores to forecast the particular inventory to store and discard. Using the Big Data tool accurately, the department store could be able to maintain the inventory that customer require at all time in the year.

b. Two (2) SQL Stored Procedures for Predictive Analytics

Couple with the Big Data tool, the SQL procedure assists sales associates in the predictive analytics. A departmental store can retrieve past customer record to assist in making sale's prediction and the SQL code is as follows:

SQL> SELECT * FROM CUSTOMERS

ORDER BY (CASE ADDRESS

WHEN 'ALPA'

THEN 1

WHEN 'BETA'

THEN 2

WHEN 'PINT'

THEN 3

WHEN 'AHMAD' THEN 4

WHEN 'MP'

THEN 5

Moreover, to select the customer first name and address in order to make predictive analytics on the specific group of customer residing in some selected addresses. The SQL code is as follows:

SQL> SELECT * FROM CUSTOMERS

ORDER BY FIRST_NAME, ADDRESS;

c. "Two Methods the SQL Code Provides Greater Value to Expand Business"

One of the best methods the SQL code is used in predictive analytics is that it is useful to forecast sales, which assist in enhancing competitive market advantages. Using the data collected, organizations could identify customer's preference as well as manipulating data to their best advantages. In the business environment, decision-making using requires number of report to answer specific and fixed set of queries, which include monthly sales per store is very essential for organizational performances. Thus, the SQL code assists in providing queries to make predictive decision to assist organizations to enhance market advantages.

d. Copy of Working SQL Code

create table customer

(cust_id

char (10),

first_name

varchar (50),

address varchar (50),

apt varchar (45),

city char (35),

zip char (10),

email varchar (55),

phone_num varchar (20),

create table book

(book_id

varchar (20),

book_num varchar (25),

isbn varchar (20),

title char (15),

publ_date varchar (20),

publ_name char (25),

type varchar (35),

price varchar (20),

quantity varchar (28)

create table employee

(employee _id varchar (25),

first_name varchar (20),

last_name varchar (20),

address varchar (50),

apt char (25),

city varchar (25),

zip char (15),

email varchar (25),

phone_number varchar (15),

hire_date char (15),

term_date varchar (20),

term_note varchar (100)

create table book _author

author_id varchar (25),

first_name varchar (20),

last_name varchar (20),

address varchar (20),

phone_number char (20),

apt char (15),

city varchar (20),

zip varchar (10),

email varchar (50),

date_of _birth char (15)

create table order_transaction

order_id varchar (25),

order_date varchar (15),

price float (15)

quantity smallinter (100)

total_price float (30)

ship_date timestamp (15)

ship_type varchar (30),

customer_id varchar (35),

employee_id varchar (40),

book_id varchar (38),

create table sales products varchar (35),

date varchar (15),

customer_id char (25),

employee_id varchar (15),

store varchar (18)

create table store store_name varchar (15),

address varchar (50),

create table publisher publisher_id varchar (25),

address varchar (55),

apt char (25),

city char (15),

zip varchar (10),

email varchar (50),

phone_number varchar (27),

web _address

varchar (50)

5. Database Vendors that provide Cloud Computing Services

Oracle Company is one of the database vendors that provide the cloud computing services for database system. The pricing systems that Oracle offers for the cloud computing service for the database are as follows:

Oracle allows customer to choose pay-as-you-go and offers $0.16 per hour for customer who wants to test the software.

Moreover, Oracle offers the following pricing for the processor license of the cloud database:

Standard Edition One $5,800.00,

Standard Edition & #8230;$17,500.00,

Enterprise Edition $47,500.00,

Mobile Server $23,000.00,

NoSQL Database Enterprise Edition. $10,000 (Oracle 2013).

b. "Analysis of Security Considerations and Pricing of the Different Cloud

Implementation"

Data security is very critical when implementing the database cloud service. Encryption is one of the effective data security procedures of the cloud database service. The use of back up is also an effective security tool in the cloud database service. However, the Oracle Database Cloud Service has in-built security procedure that protects the data from malicious intervention. Typically, "the Oracle Database Cloud Service has been built from the ground up to prevent willful or accidental schema violations with best practices and limitations. The overall Database Cloud Service and its components are protected in a number of important dimensions." (Oracle 2012 P10).

Oracle cloud infrastructure protects the database from denial-of-service, which includes WebLogic and Apache against Denial of Service attacks. Some security protocols that Oracle offers to protect the database as follows:

The bind variables prevent SQL injection,

The Session State Protection prevents URL tampering,

Password policies system as well as the use of strong password enhances the Oracle's unique Transparent Data.

However, the users could achieve security protection from the software update with the following pricing:

Standard Edition One $1,276.00

Standard Edition & #8230;$3,850.00

Enterprise Edition $10,450.00

Mobile Server $5,060.00

c. Ranking of the cloud services options of SaaS, PaaS and IaaS.

Platform as a service (PaaS) is the type of cloud computing services that provides a computing solution and platform service. Moreover, infrastructure as a service (IaaS) and software as a service (SaaS) are the other cloud computing service models. The PaaS facilitates application deployment without the complexity and cost of buying underlying software and hardware hosting capabilities. The table below compares the three cloud computing services to assist their ranking.

Software as a service (SaaS)

Platform as a service (PaaS)

Infrastructure as a service (IaaS)

Software can be managed from a central location

Software delivered using a "one to many" model,

Software upgrades and patches are not required,

Application Programming Interfaces (APIs) allow integrating between different pieces of software.

Web-based user interface assists in creating, modifying, testing as well as deploying different UI scenarios,

Built in scalability software that include failover and load balancing,

Integrating the database and web services via common standards.

Resources are distributed based on service,

Allows the dynamic scaling,

Integrating variable cost, and utility pricing model,

Generally allowing multiple users within a single piece of hardware.

Based on the comparison of the three computing clouding services, platform as a service (PaaS) is the most preferable for the cloud computing. Typically, 52% of developers prefer PaaS to SaaS and IaaS. The market for PaaS could increase from $1 billion to $27 billion in 2016. Typically, PaaS is effective in increasing organizational agility, boost innovation and offload operational complexity. However, technical provision to enhance data integrity is to use encryption and cryptographic security procedure for the cloud computing services.

9. Two (2) options for Disaster Management Functions within the Retail Environment

Data are the lifeblood of organizations and the data back up is the strategy that organizations employ to recover data in case of disaster such as fire, flood, theft and natural disaster. Data back up is the strategy of storing the copy data offline at a remote location different from the actual work location. Many organizations have found themselves of the issue of data lost due to unforeseen circumstances and if these organizations do not implement data back-up and recovery plan, their business operation will come to a stand still. Typically, data back up must be swift so that organizations can be back to business as quickly as possible.

One of the effective tools to enhance data back-up and recovery process is to use IBM Tripoli. Typically, IBM Tivoli offers the key following advantages:

"The IBM Tivoli is a centrally automated, managed, and non-disruptive backup -- and restores solutions that support data spread over multiple locations and a wide range of devices." "The IBM Tivoli is also fast and reliable recovery of lost data." (IBM, 2012 P. 2).

Typically, IBM Tivoli provided a track proven records for organization to recover their lost data during the disasters such as the Indian Ocean tsunami, 9/11, and Hurricane Katrina. (IBM, 2012).

Moreover, Samsung offers back up and recovery solution that assist organizations to restore data in case of security threats. Samsung offers small storage device of 64GB SSD. These back up devices are provided in the external storage devices such as the HDD, USB-type, SD card, and USB memory device. (Samsung, 2012).

9b. "Assessing the Security Threats in the Department Store Database and suggest

Measure(s) to Minimize the Threats"

Database is prone to many threats if not fully protected. One of the security threats is the data theft. Unauthorized individual could use many devises such eavesdropping, use of worms and Trojan horse to steal sensitive information from the database. The information, which includes credit card information, social security number, bank data and other sensitive information, may be stolen from the database.

Moreover, penetrators could also steal sensitive data from the database by penetrating organization network devises. Denial of Service attacks is another threat to database of the departmental store. Artificial and natural disasters are the other threats facing departmental store database. Method to minimize these threats is the following:

Use of firewall to prevent access to the database through network,

Installation of IDS (intrusion detection system) and IPS (intrusion prevention system),

Installation of encryption and cryptographic systems,

Use of data back-up and recovery system to restore data in case of disaster,

Design strong data protection policies that all employees must follow.

Reference

Alechina, N. (2009).Concurrency Database Systems Lecture 15.

Coronel, C. Morris, S. & Rob, P. (2012). Database Systems: Design, Implementation,

and Management, (Tenth Edition). Cengage Learning. USA.

Cho, W. Whang, K. Lee, S. et al. (2010). Query Optimization Techniques Utilizing Path

Indexes in Object-Oriented Database Systems. Department of Computer Science, KAIST.

IBM (2012). Data Backup and Recovery. International Business Machine White Paper.

Oracle (2013).Oracle Technology Global Price List. Software Investment Guide.

Oracle (2012).Oracle Database Cloud Service. Oracle White Paper.

Samsung (2012). Chapter 4. Backup / Restore. Samsung USA.

Store

Store Name

Address

Publisher

Publisher ID

Address

APT

City

Zip

Email

Web Address

Sales

Products

Customer

Employee

Store

Order

Order_id INT

Order_Date DATE

Final_Price DECIMAL (10,2)

Quantity INT

Total_Price DECIMAL (10,2)

Ship_Date DATE

Ship_Type VARCHAR (25)

Ship_Status VARCHAR (25)

Customer_id INT

Employee_id INT

Book_id INT

Book_id INT

Book_Number INT

ISBN VARCHAR (17)

Title VARCHAR (100)

Publication_Date DATE

Type VARCHAR (25)

Comment VARCHAR (500)

Price DECIMAL (5,2)

Quantity INT

Book

Employee

Author

Customer_id INT

First_Name VARCHAR (25)

Last_Name VARCHAR (25)

Address VARCHAR (125)

APT VARCHAR (10)

City VARCHAR (50)

State VARCHAR (2)

ZIP VARCHAR (5)

Phone VARCHAR (12)

Email VARCHAR (50)

Customer

Author_id INT

First_Name VARCHAR (25)

Last_Name VARCHAR (25)

Address VARCHAR (125)

APT VARCHAR (10)

City VARCHAR (50)

State VARCHAR (2)

ZIP VARCHAR (5)

Phone VARCHAR (12)

Email VARCHAR (50)

DOB DATE

Employee_id INT

First_Name VARCHAR (25)

Last_Name VARCHAR (25)

Address VARCHAR (125)

APT VARCHAR (10)

City VARCHAR (50)

State VARCHAR (2)

ZIP VARCHAR (5)

Phone VARCHAR (12)

Email VARCHAR (50)

Hire_Date DATE

Term_Date DATE

Term_Note VARCHAR (500)