Transcript Course Outline

Database development
(MIS 533)
MBS in Management Information Systems and
Managerial Accounting Systems
(2007 / 2008)
Fergal Carton
Business Information Systems
Last week
• Data flow diagrams (Context level)
– Cucina della Italia
• Normalisation (1NF and 2 NF)
• Process modelling
– Sales order process
– Purchase order process
• Customer requirements drive information
requirements
– eg. Logistics of delivery will influence what needs to be
managed in a system (drivers, vans, addresses, …)
MIS533 / Database development
This week
• Practical assignment for 30th Nov.
•
•
•
•
– Create a system to manage sales order
processing for Cucina della Italia
Building a model
DFD’s
Cucina della Italia DFD
Manufacturing concepts and planning
models
MIS533 / Database development
Cucina della Italia
• A variety of fresh, healthy breads for
service stations in Cork city and Munster
• Real Italian ingredients
• Savoury breads can be eaten on the move
• Competing with Cuisine de France on
quality, short lead times and customer care
MIS533 / Database development
Cucina della Italia
Customer requirements
• Fast and efficient ordering process (on-line, by
phone, by text, directly from driver, …)
• Dough delivered fresh twice daily
• Competitive and consistent pricing
• Customer service includes managing returns,
equipment and stock management
• Flexible payment methods (CC/DC/cheque, …)
• 4 delivery vans and one production unit on
Tramore Road in Cork
MIS533 / Database development
Cucina della Italia: the assignment
• Design a system to handle sales order processing for
Cucina della Italia
• Build the tables to manage the information flows
– Including products, prices, customers, sales orders and invoices
• Link the tables
• Design forms for end-user interaction
–
–
–
–
–
–
Adding new products, deleting old ones
Changing prices and discount levels
Adding new customers, changing customer details
Creating sales orders and goods delivery notes
Creating and printing invoices
Running a sales report by customer, by product, by category
MIS533 / Database development
Sales or purchase process
Virtual processes
• A commitment by one party to another for
– Products
– Price, discount
– Delivery date
• The deal confirms the above on an order
• Efficient communication of order to back office for
execution
• Shipment to customer
Physical processes
• Receipt of goods, record any anomalies
• Invoice
• Paymemt
MIS533 / Database development
Information flows
•
•
•
•
•
•
•
Purchase orders
Sales orders
Goods Delivery Note (& anomalies)
Returns
Invoice
Credit note
Payment
MIS533 / Database development
Cucina della Italia
Sales process
Sales order & GRN
Accounts
Sales order
Sales order
processing
Customer
Goods Delivery
(GRN)
Invoice
Payment
MIS533 / Database development
Cucina della Italia
Purchase process?
MIS533 / Database development
Cucina della Italia
Purchase process
Purchase order & GRN
Accounts
Purchase order
Procurement
Goods Delivery
(GRN)
Invoice
Payment
MIS533 / Database development
Supplier
Oracle Demo Set Sales Order
Processing
CUSTOMER TABLE
SALES_ORDER TABLE
PRODUCT TABLE
ITEM TABLE
MIS533 / Database development
PRICE TABLE
Building a model
• Process Flow
• Data Flow Diagram
–
–
–
–
Processes
Data Flow
Data store
Entities
MIS533 / Database development
Types of logical process
• Logical processes can be “functions”:
– A set of related and ongoing activities of the business
– A function has no start and no end
– It continually performs work as needed
• … or an “event”:
– A logical unit of work that must be completed as a whole
– Triggered by a discreet input and is completed when the
process responds with appropriate output
– Sometimes called transactions
MIS533 / Database development
All data flows begin and/or end at a process
X
X
X
X
X
A process is needed to exchange
data flows between external entities
A process is needed to update (or
use) a data store
A process is needed to present data
from a data store
A process is needed to move data
from a data store to another
MIS533 / Database development
DFD’s: logical data flows
• Although data flows can be implemented in many ways :
–
–
–
–
–
–
–
Telephone calls
Business forms
Bar codes
Emails
Reports
Computer screens
Computer to computer communications (EDI, interface, …)
• … in DFD’s, we are only interested that the flow is
needed, not how the flow will be implemented
MIS533 / Database development
Data flow names should be :
• Singular (eg. order instead of orders)
Not to imply
physical batches
• Unique
– use adjectives / adverbs to describe how processing has
changed a data flow
Customer
Order
Process
Order
Customer
Order
Process
Order
Order
X
Approved
Order

MIS533 / Database development
Cucina della Italia
Sales / Purchase DFD?
MIS533 / Database development
Production planning
• Planning approaches (build to plan)
– Top down quarterly plan drives both Production (units)
and Sales (€) targets
– Takes into account sales forecast, historical
performance and market expectations
– Production plan must then be broken down into units,
configurations, geograpahies
– Local production planners then turn this into:
• an MRP plan to drive purchasing
• and a Master production schedule to drive manufacturing
MIS533 / Database development
Keeping tabs on Work-in-Progress
(WIP)
• Inventory consumption
– Something must trigger inventory consumption as
production proceeds (BO, WO, PO, SO, …)
– Visibility of inventory in Work-in-Progress (WIP) is
low
– You can measure output / consumption at intermediate
points but beware of disrupting process
MIS533 / Database development
•
Manufacturing modes and
lead
times
Difference between process and discrete
manufacturing
– Process manufacturing consumes inventory at a steady
rate, but yield of finished goods may vary
– Discrete manufacturing consumes a predefined amount
of inventory per article of finished goods
• Lead times:
– key to replenishment policy: what’s my buffer stock?
– Fiat Idea : re-uses previous model components to
reduce cost & lead time
MIS533 / Database development