Page

$1$

of $3$

Smallville Company Database Design Mod$3$

Smallville is the code name for a BPR project at a large enterprise where the leadership

team wants to create a data model based on the company$$s new business process models. This is

a privately$-$owned business earning millions of dollars in revenue while being operated by just a

handful of individuals. They want to create a data model for a tracking system for employee

compensation costs, customer sales orders, product revenue monitoring, and inventory ordering.

They have previously been running the whole business with excel spreadsheets going back and

forth between each manager.

The ordering business process has customers who place orders for products that

Smallville sells. Customers are tracked by a customer number, first name, last name, address,

email and phone. Products are tracked by product number, description $($name$),$ quantity on hand,

whether on back order $($yes$/$no$),$ quantity on back order, $$list price$,$ discount rate, and actual

price $($calculated from list price and discount rate$).$ Product orders are tracked by order date,

quantity of each product ordered, the quoted price, and net sale $($calculated from quantity and

price$).$ A customer order will consist of at least one or more products, and products are

associated with their orders for reporting purposes. In other words, not every product is ordered

every time.

Customer service process happens when a customer places an order and the associate

assigned to the customer runs a product report to find out if units are on hand or if the product is

on back order. An associate is assigned to a customer for each event when a customer contacts

the company. Each event is tracked by date and time, and a short description of the event

type$/$purpose$.$ Products are only entered during the ordering process if they are available on hand.Smallville Company Database Design Mod$3$

Smallville is the code name for a BPR project at a large enterprise where the leadership

team wants to create a data model based on the company$$s new business process models. This is

a privately$-$owned business earning millions of dollars in revenue while being operated by just a

handful of individuals. They want to create a data model for a tracking system for employee

compensation costs, customer sales orders, product revenue monitoring, and inventory ordering.

They have previously been running the whole business with excel spreadsheets going back and

forth between each manager.

The ordering business process has customers who place orders for products that

Smallville sells. Customers are tracked by a customer number, first name, last name, address,

email and phone. Products are tracked by product number, description $($name$),$ quantity on hand,

whether on back order $($yes$/$no$),$ quantity on back order, $$list price$,$ discount rate, and actual

price $($calculated from list price and discount rate$).$ Product orders are tracked by order date,

quantity of each product ordered, the quoted price, and net sale $($calculated from quantity and

price$).$ A customer order will consist of at least one or more products, and products are

associated with their orders for reporting purposes. In other words, not every product is ordered

every time.

Customer service process happens when a customer places an order and the associate

assigned to the customer runs a product report to find out if units are on hand or if the product is

on back order. An associate is assigned to a customer for each event when a customer contacts

the company. Each event is tracked by date and time, and a short description of the event

type$/$purpose$.$ Products are only entered during the ordering process if they are available on hand.

The inventory monitoring process involves products on back order, a product line order is

placed with one of Smallville$$s suppliers. The product line order tracks the supplier, the product

ordered, quantity, date of order, and expected arrival $($calculated as $5$ days forward of the order

date$).$

Smallville has four types of employees that handle the day to day operations of the

company: Sales Reps, Hourly Workers, Salary Workers and Interns. Sales Reps are compensated

by a commission rate times the number of units sold for an order credited to the Sales Rep. This

is not the same as being assigned to a customer event.

Hourly Workers fulfill customer orders and are tracked by hours worked per shift. Due to

the nature of the business, workers can work more than one shift in a single day. Their

compensation is calculated based on an hourly rate and the total hours worked for a given time$-$

period. Salary workers receive a fixed annual compensation $($salary$).$ They are the leadership

team and operations managers. Interns have no specific attributes unique to their type. All

employees are listed by first name, last name, address, email and phone.

Managers supervise hourly workers, sales reps, interns and some salaried emp