Database development ( MIS 533 )

1. Database development(MIS 533) MBS in Management Information Systems and Managerial Accounting Systems (2007 / 2008) Fergal Carton Business Information Systems

2. 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

3. 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

4. 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

5. Cucina della ItaliaCustomer 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

6. 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

7. 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 • Receipt of goods, record any anomalies • Invoice • Paymemt Physical processes MIS533 / Database development

8. Information flows • Purchase orders • Sales orders • Goods Delivery Note (& anomalies) • Returns • Invoice • Credit note • Payment MIS533 / Database development

9. Cucina della ItaliaSales process Sales order & GRN Sales order Accounts Sales order processing Customer Goods Delivery (GRN) Invoice Payment MIS533 / Database development

10. Cucina della ItaliaPurchase process? MIS533 / Database development

11. Cucina della ItaliaPurchase process Purchase order & GRN Purchase order Accounts Procurement Supplier Goods Delivery (GRN) Invoice Payment MIS533 / Database development

12. Oracle Demo Set - Sales Order Processing CUSTOMER TABLE SALES_ORDER TABLE PRODUCT TABLE ITEM TABLE MIS533 / Database development PRICE TABLE

13. Building a model • Process Flow • Data Flow Diagram • Processes • Data Flow • Data store • Entities MIS533 / Database development

14. 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

15. All data flows begin and/or end at a process A process is needed to exchange data flows between external entities X X A process is needed to update (or use) a data store X X A process is needed to present data from a data store X A process is needed to move data from a data store to another MIS533 / Database development

16. 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

17. Customer Customer Data flow names should be : • Singular (eg. order instead of orders) • Unique • use adjectives / adverbs to describe how processing has changed a data flow Not to imply physical batches Process Order Order X Order Process Order Approved Order  Order MIS533 / Database development

18. Cucina della ItaliaSales / Purchase DFD? MIS533 / Database development

19. 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

20. 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

21. 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