Transcript Cuisine de France

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)
/ Database
Alice Mutton
7 / MIS533
6 20
- 1 kgdevelopment
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