1 / 22

Database development ( MIS 533 )

Database development ( MIS 533 ). MBS in Management Information System s and Managerial Accounting Systems (2007 / 2008). Fergal Carton Bu siness Information Systems. Last week. Cuisine de France Table st ructure for bread products Normalise by taking out type to another table

silas
Download Presentation

Database development ( MIS 533 )

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

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

  2. Last week • Cuisine de France • Table structure for bread products • Normalise by taking out type to another table • Database should be independent of application • Primary key = foreign key in related tables • Normalisation: 1NF • One single data item at intersection of row and column • Use sample data to fill out tables to understand structure • Requirements analysis: modelling • Goal is to uncover information flows, start at process level • Modelling exercise • context level diagram for Cucina della Italia MBS (MIMAS) / MIS533 / Database development

  3. This week • Data flow diagrams • Cucina della Italia • Northwind? • Normalisation • Process modelling • Sales order process • Purchase order process • Normalisation 2NF MBS (MIMAS) / MIS533 / Database development

  4. Cucina della Italia • Selling fresh bread to service stations • Describe customer requirements • How these requirements can be met? • What processes will the business need? • What information flows are required? MBS (MIMAS) / MIS533 / Database development

  5. Your own examples MBS (MIMAS) / MIS533 / Database development

  6. Call center customers • Customer rings call center (timeshare sales) • System recognises number, displays record • Is there a database at work? MBS (MIMAS) / MIS533 / Database development

  7. Normalisation • Process of simplifying the relationships amongst data items as much as possible (see example provided - handout) • Through an iterative process, structure of data is refined to 1NF, 2NF, 3NF etc. • Reasons for normalisation: • to simplify retrieval (speed of response) • to simplify maintenance (updates, deletion, insertions) • to reduce the need to restructure the data for each new application MBS (MIMAS) / MIS533 / Database development

  8. Normalisation: 2NF • A table already in 1NF • A table with composite primary keys • A table with a single column primary key is already in 2NF • Values in each column can only be worked out from the values in all the columns that make up the primary key MBS (MIMAS) / MIS533 / Database development

  9. Normalisation Suppliers Categories MBS (MIMAS) / MIS533 / Database development

  10. Sales order process • Provide price / quotation • Give commit date for delivery • Take order • Dispatch goods and invoice • Receive payment from customer MBS (MIMAS) / MIS533 / Database development

  11. Sales order process • PCB case study • Draw a process map of the sales order process • Show the information flows between stages in the process MBS (MIMAS) / MIS533 / Database development

  12. Purchase order process • Obtain price / quotation • Create purchase order • Get approval for expenditure • Receive goods and delivery note • Receive invoice • Make payment to supplier MBS (MIMAS) / MIS533 / Database development

  13. Purchase request and payment • Departments make spending requests • Budget is checked in the allocated budget file • If budget exceeded, request is rejected • Approved requests are stored and used to create Purhcase orders (PO’s) for suppliers • Goods received notes (GRN) are matched with PO’s to identify any discrepancies • When supplier invoice is received, 3 way match is made between PO, GRN and invoice prior to payment MBS (MIMAS) / MIS533 / Database development

  14. Purchase request and payment • Draw a context level diagram for the process • Draw a Level 1 Data Flow Diagram • What sort of analysis method would you use? • What sort of output would you provide? • Structure of User Requirements Report? MBS (MIMAS) / MIS533 / Database development

  15. Example : Budget monitoring case • What analysis method would you use and why? • Interviews • Management / Department interviews • Describe the special approval process • What are the “tolerance levels” for overspending of budget • How many requests, how long to approve, backlog issues • Documentation • Copies of current spending requests • List of departments and corresponding budgets • Print-out from allocated budget file • Copy of spending summary report • Process for order supply of parts (PO’s?) • Copy of delivery advice details • Requirements report MBS (MIMAS) / MIS533 / Database development

  16. Example : Budget monitoring case • What sort of output would you provide? • Requirements report • Questionannaires • Functional Decomposition • Data Flow Diagrams • ERD • Etc. • How would you validate requirements? • JRP • Prototype • Approving Requirements report • … MBS (MIMAS) / MIS533 / Database development

  17. MBS (MIMAS) / MIS533 / Database development

  18. Budget monitoring case MBS (MIMAS) / MIS533 / Database development

  19. Oracle Demo Set - Sales Order Processing CUSTOMER TABLE SALES_ORDER TABLE PRODUCT TABLE ITEM TABLE MBS (MIMAS) / MIS533 / Database development PRICE TABLE

  20. 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 MBS (MIMAS) / MIS533 / Database development

  21. 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 MBS (MIMAS) / MIS533 / Database development

  22. 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 MBS (MIMAS) / MIS533 / Database development

More Related