Database development (MIS 533) MBS in Management Information Systems and Managerial Accounting Systems (2007 / 2008) Fergal Carton Business Information Systems Last week • • • • More examples of databases Using data.
Download ReportTranscript Database development (MIS 533) MBS in Management Information Systems and Managerial Accounting Systems (2007 / 2008) Fergal Carton Business Information Systems Last week • • • • More examples of databases Using data.
Database development (MIS 533) MBS in Management Information Systems and Managerial Accounting Systems (2007 / 2008) Fergal Carton Business Information Systems Last week • • • • More examples of databases Using data entry controls: field types Cuisine de France: requirements analysis Northwind example: Product & Category tables • Homework – How many field types in Access? – Cuisine de France: draw out list(s) of products MBS (MIMAS) / MIS533 / Database development This week • • • • Requirements analysis Modelling techniques Northwind: Supplier table Modelling exercise MBS (MIMAS) / MIS533 / Database development More examples of databases • Glenn: stock ordering for Chemist • Clive: point of sale and suppliers for StatOil • Tara: books and stationery ordering for Eason’s • Ken: Contacts for pledge customers for park • Shane: Telesales system for Cuisine de France MBS (MIMAS) / MIS533 / Database development Cuisine de France • Requirements analysis – Products – Outlets / customers / business units – Mixing customers with categories! • Where data meets the business – Telesales need correct / easy to use information – Must be kept up to date, who maintains it? MBS (MIMAS) / MIS533 / Database development Northwind example from Access • What can we tell about business from tables? – – – – – Product types Quantities: per unit, stock and re-order level Distributor vs. Retailer? Categories and Products will evolve Quantity per unit field – separate handling unit? • Why have separate table for categories? MBS (MIMAS) / MIS533 / Database development Assessment dates • A practical exam (20%) which will be the development of a database system: – Eg. system to support playlist generation – Scenario to be distributed by mid November – Deadline 7th December 2007 • An Oracle and SQL assessment (20%) will take place on Wed 12th March 2008 at 12.00 in Lab B.04 MBS (MIMAS) / MIS533 / Database development Your own examples Student name System experience 1. Horgan, Shane Christopher Telesales system for Cuisine de France 2. Dillon, Damien 3. Creighton, Glenn Stock ordering for Chemist 4. Kenny, Clive EPOS and suppliers for StatOil 5. Crowley, Micheal 6. Hourigan, Brenda 7. Kavanagh, Fiona Marie Claire 8. Kearney, Tara 9. Holland, Tadhg 10. REYHANI, PEYMAN Quality control, Newmarket Stationery ordering for Eason’s 11 FONZI, Kenneth Christopher Pledge customers for park 12 LI, Yan Purchasing for retail business MBS (MIMAS) / MIS533 / Database development Understanding requirements ... • Analysing problems – – – – – Functional decomposition Process specification Flow Charts Use case Activity diagrams • Designing solutions – Data flow diagrams – Entity relationship model MBS (MIMAS) / MIS533 / Database development Requirements discovery • Meetings with users (who, when, what outcome, …) • Checklist of questions that clarify user requirements • Describe existing processes – Document how things are currently done – Review inputs and outputs of current process (screens, forms, reports) – Outline problems with current way of doing things (speed, risk of error, …) – What improvements are expected from system (single point of data entry, faster reports, less manual work, …) • How to design and communicate the proposed solution – Review requirements documentation – Walk-through solution – Get sign-off from users MBS (MIMAS) / MIS533 / Database development Fact finding techniques • • • • • • • • Sampling of existing forms and files Site visits Observation of work environment Research of similar systems Surveys of users and management Interviews of users and management Prototyping Joint Requirements Planning (JRP) MBS (MIMAS) / MIS533 / Database development Requirements Definition Report • Introduction – Purpose – Background – Scope • General Project Description – System Objectives • Requirements and constraints – Functional requirements – Non-functional requirements • Conclusion – Outstanding issues Appendix (eg. Questionnnaires & responses) MBS (MIMAS) / MIS533 / Database development Exercise • • • • • Pick a business idea 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 Building a model • Functional decomposition Sales Finance • Data Flow Diagram – – – – – Context diagram Processes Data Flow Data store External entities MBS (MIMAS) / MIS533 / Database development Shipping Planning Example : www.RyanAir.com MBS (MIMAS) / MIS533 / Database development Functional decomposition Passenger Flight information enquiry Reservation Payment Ticket issued MBS (MIMAS) / MIS533 / Database development Example : www.RyanAir.com • Input : – Flight details (eg. SNN-BVA, 8 Oct, 10.35) – Credit card details (eg. VISA no, Expiry date) • Process : – Confirms price (eg. EUR 79) – Makes reservation & processes payment • Output : – Reservation reference (eg.CGHKCA) MBS (MIMAS) / MIS533 / Database development Data Flow Diagram Book now Flight details Lists options Outward Inward Confirm price Credit card Makes Reservation & payment Stores Flight Reservation details MBS (MIMAS) / MIS533 / Database development Context diagram Flight reservation Passengers Seat availability Flight Operations Payment Reservations Reservation # Flight Schedule On-line reservation system € Accounts Seat prices Timetable MBS (MIMAS) / MIS533 / Database development SupplierID Data : Suppliers CompanyName ContactName ContactTitle Address 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Exotic Liquids New Orleans Cajun Delights Grandma Kelly's Homestead Tokyo Traders Cooperativa de Quesos 'Las Cabras' Mayumi's Pavlova, Ltd. Specialty Biscuits, Ltd. PB Knäckebröd AB Refrescos Americanas LTDA Heli Süßwaren GmbH & Co. KG Plutzer Lebensmittelgroßmärkte AG Nord-Ost-Fisch Handelsgesellschaft mbH Formaggi Fortini s.r.l. Norske Meierier Bigfoot Breweries Charlotte Cooper Shelley Burke Regina Murphy Yoshi Nagase Antonio del Valle Saavedra Mayumi Ohno Ian Devling Peter Wilson Lars Peterson Carlos Diaz Petra Winkler Martin Bein Sven Petersen Elio Rossi Beate Vileid Cheryl Saylor Purchasing Manager Order Administrator Sales Representative Marketing Manager Export Administrator Marketing Representative Marketing Manager Sales Representative Sales Agent Marketing Manager Sales Manager International Marketing Mgr. Coordinator Foreign Markets Sales Representative Marketing Manager Regional Account Rep. 49 Gilbert St. P.O. Box 78934 707 Oxford Rd. 9-8 Sekimai Musashino-shi Calle del Rosal 4 92 Setsuko Chuo-ku 74 Rose St. Moonie Ponds 29 King's Way Kaloadagatan 13 Av. das Americanas 12.890 Tiergartenstraße 5 Bogenallee 51 Frahmredder 112a Viale Dante, 75 Hatlevegen 5 3400 - 8th Avenue Suite 210 MBS (MIMAS) / MIS533 / Database development 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 Chai 1 1 10 boxes x 20 bags Chang 1 1 24 - 12 oz bottles Aniseed Syrup 1 2 12 - 550 ml bottles Chef Anton's Cajun Seasoning 2 2 48 - 6 oz jars Chef Anton's Gumbo Mix 2 2 36 boxes Grandma's Boysenberry Spread 3 2 12 - 8 oz jars Uncle Bob's Organic Dried Pears 3 7 12 - 1 lb pkgs. Northwoods Cranberry Sauce 3 2 12 - 12 oz jars Mishi Kobe Niku 4 6 18 - 500 g pkgs. Ikura 4 8 12 - 200 ml jars Queso Cabrales 5 4 1 kg pkg. Queso Manchego La Pastora 5 4 10 - 500 g pkgs. Konbu 6 8 2 kg box Tofu 6 7 40 - 100 g pkgs. Genen Shouyu 6 2 24 - 250 ml bottles Pavlova 7 3 32 - 500 g boxes MBS (MIMAS) / MIS533 / Database development Alice Mutton 7 6 20 - 1 kg tins ReorderLevel UnitsOnOrder UnitsInStock QuantityPerUnit UnitPrice CategoryID ProductName SupplierID ProductID Data : Products €18.00 39 0 10 €19.00 17 40 25 €10.00 13 70 25 €22.00 53 0 0 €21.35 0 0 0 €25.00 120 0 25 €30.00 15 0 10 €40.00 6 0 0 €97.00 29 0 0 €31.00 31 0 0 €21.00 22 30 30 €38.00 86 0 0 €6.00 24 0 5 €23.25 35 0 0 €15.50 39 0 5 €17.45 29 0 10 €39.00 0 0 0 CategoryID Data : Categories 1 2 3 4 5 6 7 8 CategoryName Description Beverages Condiments Confections Dairy Products Grains/Cereals Meat/Poultry Produce Seafood Soft drinks, coffees, teas, beers, and ales Sweet and savory sauces, relishes, spreads, and seasonings Desserts, candies, and sweet breads Cheeses Breads, crackers, pasta, and cereal Prepared meats Dried fruit and bean curd Seaweed and fish MBS (MIMAS) / MIS533 / Database development 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 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 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 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 Make spending request Budget is checked [Request > Budget] Request rejected [Request < Budget] Create Purchase Order GRN matched with PO Invoice received 3 way match GRN / PO / Invoice [No discrepancy] [Invoice on hold] Invoice payment MBS (MIMAS) / MIS533 / Database development Budget monitoring case Make Spending Request Department Send Managements Response Send Delivery Advice Check Budget Budget Monitoring System Special Request Made Respond To Special Request Provide Spending Summaries Supply Delivery Advice Suppliers MBS (MIMAS) / MIS533 / Database development Management