FunctionalModeling 4xgs - Overview • Database Design...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Overview • Database Design Database Joins and Functional Modeling • Database Joins • Functional Modeling (IDEF0) Peter L. Jackson Professor School of O.R. and I.E. 1/23/2012 • Appendix: Case Study in Functional Modeling 1/23/2012 1 Industrial Data and Systems Analysis 2 Industrial Data and Systems Analysis Procedures Tables Tables are Related by Keys are Related by Keys Reports Queries Procedures Change the Design the Design Tables Reports Queries • Suppose each customer can have more each customer can have more than one contact person • Eg. Mary handles meat orders, John handles everything else • How would you change the schema? 1/23/2012 Industrial Data and Systems Analysis 3 1/23/2012 Industrial Data and Systems Analysis 4 A Solution: Category Manager Solution: Category Manager How It Works It Works • Every order detail (line item on order) has a productID • The product for that productID tells you the categoryID of the product • Every order detail maps to an orderID • The order record for that orderID contains the customerID • If you know the customerID and categoryID, then the 1 ∞ ∞ ∞ 1/23/2012 CategoryManager table will tell you the contactID of the CategoryManager table will tell you the contactID of the person in that customer company who is uniquely responsible for that category • So, from the order detail record you can get to the contact from the order detail record you can get to the contact responsible along a unique path of records • Other solutions to the problem are possible • You will not be tested on database design in this course will not be tested on database design in this course ∞ Industrial Data and Systems Analysis 5 1/23/2012 Industrial Data and Systems Analysis Overview Joins • Database Design 6 • Good database design separates related data into tables so that the data are not needlessly repeated needlessly repeated • Database Joins • So, how do we put it back together? how do we put it back together? • Functional Modeling (IDEF0) • Appendix: Case Study in Functional Modeling 1/23/2012 Industrial Data and Systems Analysis 7 1/23/2012 Industrial Data and Systems Analysis 8 The Schema, the Data The Schema, the Data Aside: Not a Good Design Not Good Design • This is not a good database design is not good database design • What is wrong with it and how would you fix it? 1/23/2012 Industrial Data and Systems Analysis 9 Alternative Ways to Pull Data Together 1/23/2012 Industrial Data and Systems Analysis 10 All Possible Combinations Possible Combinations • All possible combinations possible combinations • All possible combinations, filtered using • • • • WHERE clause INNER JOIN (equivalent to WHERE filter) LEFT JOIN (a nice possibility) JOIN (a nice possibility) RIGHT JOIN OUTER JOIN JOIN Note: “BookID is ambiguous without table prefix 1/23/2012 Industrial Data and Systems Analysis 11 1/23/2012 Industrial Data and Systems Analysis 12 An Equivalent Query Using Aliasing Ali We Want Authors to Match with Books Based on ‘BookID’ ‘B What’s Missing? 1/23/2012 Industrial Data and Systems Analysis 13 1/23/2012 The content of these empty fields is “null”. Joins are more powerful than Where clauses… Industrial Data and Systems Analysis 14 Suppose We Want to See All Books, Even If There is No Author? If Th An Equivalent Query Equivalent Query 1/23/2012 Industrial Data and Systems Analysis 15 1/23/2012 Industrial Data and Systems Analysis 16 Suppose We Want to See All Authors, Even If There is No Book? If Th Suppose We Want To See All Authors, All All Books, Matched Where Possible? Wh • Sorry, “Outer Joins” are not supported in MS Access MS The content of these empty fields is “null”. 1/23/2012 Industrial Data and Systems Analysis 17 1/23/2012 Industrial Data and Systems Analysis Overview Small Group Activity Group Activity • Database Design 18 • Break into groups of 3-4 • Consider the following problem: • It is a cold night and you as a group have left a restaurant at closing time. One of you was going to drive everyone home but your car keys are not in your pocket. • Database Joins • Functional Modeling (IDEF0) • Quickly decide as a team how you would organize to solve the problem • What are the steps? th • In what sequence? • Can any steps be performed in parallel? any steps be performed in parallel? • Appendix: Case Study in Functional Modeling 1/23/2012 Industrial Data and Systems Analysis 19 1/23/2012 Industrial Data and Systems Analysis 20 Summary Summary System Modeling Modeling • Need to analyze systems from general to to analyze systems from general to specific • Techniques range from block diagrams to computer simulation • Distinguish between functional modeling and behavioral modeling • Functional modeling: hierarchical view of functional relationships (inputs/outputs) (i • Behavioral modeling: sequence and concurrency concurrency 1/23/2012 Industrial Data and Systems Analysis 21 • IDEF0 (“eye-deaf-zero”) 22 • Developed by Air Force in 1970’s by Air Force in 1970 • Simple rules, wide applicability • Main survivor of a family of models (IDEF0, survivor of family of models (IDEF0 • Useful for abstract view of functionality • Does not express sequencing and concurrency well: not precise • Useful in system design when behavior is still vague vague IDEF1, and IDEF2) • Defined by Federal Information Processing by Federal Information Processing Standard Publication 183 • http://www.idef.com/pdf/idef0.pdf • Event Graphs (later in course) • Useful for detailed models of behavior for detailed models of behavior • Expresses sequencing and concurrency well Industrial Data and Systems Analysis Industrial Data and Systems Analysis Integrated Definition for Function Modeling (IDEF0) (IDEF0) Functional Diagramming Diagramming 1/23/2012 1/23/2012 23 1/23/2012 Industrial Data and Systems Analysis 24 IDEF0 Function Template Function Template A0 Manufacture Products (Harrington Model) Funds Plan Change Requests Status Reports Product Definition Authorization to Manufacture Reports to Enterprise Management MANAGE MANUFACTURING Directives Dir. to Develop A1 Design Change Requests Tool Design Requests Product Requirements Dir. to Support Dir. to Produce DEVELOP PRODUCTS New Product Concepts A2 Manufacturing Capabilities Requests for Resources and Product Support Product Designs & Change Orders Procurable Items PRODUCE PRODUCTS Parts and Products A3 Prototypes and Models Resources for Support Design Information SUPPORT SERVICE OF PRODUCTS Instructions A4 Facilities, tooling, tooling equipment, personnel Resources for Product Support O’Sullivan, 1994. 1/23/2012 Industrial Data and Systems Analysis Components of Template of Template Components (cont’d) (cont • Function box • Function name • Controls / Constraints • Informational input to function that influences activity 26 • A verb phrase expressing capability expressing capability system must possess • Unique identifier • For traceability • “A0” is default 1/23/2012 • Inputs • Material, energy or information • Arrows with noun with noun • E . targets or user Eg phrases (enter left) decisions or regulations • Arrows with noun • Outputs phrases phrases (enter top) • Material, energy, or information • Arrows with noun phrases (exit right) h Industrial Data and Systems Analysis • Resources / Mechanisms • Entity used by the function but not consumed or transformed (resource) (resource) • Eg. maintenance crew • Subsidiary function that will not be analyzed will not be analyzed further (mechanism) • Eg. software algorithm • Arrows with noun phrases (enter bottom) 27 1/23/2012 Industrial Data and Systems Analysis 28 Input, Control, or Constraint? Input, Control, or Constraint? IDEF0 Arrow Conventions Arrow Conventions Customer Orders • How people classify a factor depends on their point of view and mental model Control Arrows Use rectilinear arrows, closed arrowheads, and rounded corners Inventory Status • There is no precise definition that distinguishes unambiguously between an input or control or unambiguously between an input or a control or a constraint Materials • In ORIE 3120, use whichever interpretation Input Arrows makes sense to you unless you have been told explicitly in the problem statement how to classify the factor • Resource/mechanism and outputs are not so difficult to classify correctly Plan & Control Orders & Materials Schedule Materials Product Data Resource Arrows Operate Machine Cell Output Arrows Goods NC Machine, Operator, Tooling O’Sullivan, 1994. 1/23/2012 Industrial Data and Systems Analysis 29 Compare with Other Templates Pages • Hatley, Hruscka, and Pirbhai use Hruscka, and Pirbhai use • IDEF0 model consists of series of pages describing functional detailing of a capability requirement • First page (A-0) consists of single function, A0 page (A consists of single function, A0 (the capability requirement) • Every other page presents detailing of a single function from an earlier page function from an earlier page following template for software • At most 6 functions per page • Arranged in waterfall (staircase) • Each properly formatted page contains a diagram, a title, and is accompanied by a textual description 1/23/2012 Industrial Data and Systems Analysis 31 1/23/2012 Industrial Data and Systems Analysis 32 Tracking Detailing Through Page and Function Numbering Example: Mast Raising System Mast Raising System http://www.geocities.com/venturenewport/ http://www.geocities.com/venturenewport/ • 23 ft. swing keel, trailerable sailboat (Venture 23) • 25 ft mast ft mast • Raising/lowering mast requires • 1 middle-aged professor + • 3 strong graduate students 1/23/2012 Industrial Data and Systems Analysis 33 Example: Mast Raising System Mast Raising System 1/23/2012 Industrial Data and Systems Analysis 34 First Level Detailing Level Detailing Page A-0 Page A0 A0 1/23/2012 Industrial Data and Systems Analysis 35 1/23/2012 Industrial Data and Systems Analysis 36 A3 Connect Foot of Mast to Fixture: Current System Fi Observations Observations • Another solution might be to perform A2 solution might be to perform A2 (Transfer mast laterally) and A4 (Raise mast to vertical position) simultaneously • Waterfall structure is suggestive of sequence but allows other interpretations • Ambiguity in sequence/concurrency is useful in concept generation Page A3 A3 1/23/2012 Industrial Data and Systems Analysis 37 1/23/2012 38 Industrial Data and Systems Analysis http://www.dolphin24.org/sailing_solace.html A4 Raise Mast to Vertical Position: Current System Flow and Off-Page Connectors and Off Connectors • Flow • Input/output relationships can be shown as arrows output from one function and input to another input to another • Arrows must be rectilinear and have rounded corners and rounded corners and closed arrowheads • If arrows cross, one arrow must show a jump • Arrows must be labeled with noun phrases • Off-Page Connectors • If input/output relationship is between functions on different pages, connect arrows to numbered/labeled circle on page to represent link 1 1 Page A4 A4 1/23/2012 Industrial Data and Systems Analysis http://www.uiowa.edu/policult/politicalphotos/iwo_jima.html 39 1/23/2012 Industrial Data and Systems Analysis 40 Context and Perspective Context and Perspective Example: Real Estate Game Real Estate Game • Same system can have multiple functional system can have multiple functional detailings • User has different functional view than service technician • IDEF0 model author must identify point of view of model • Done in text or title of page A-0 • Subsequent pages inherit point of view pages inherit point of view Page A-0 Point of View: Player (System User) 1/23/2012 Industrial Data and Systems Analysis 41 Page A0 of Real Estate Game A0 of Real Estate Game 1/23/2012 Industrial Data and Systems Analysis 42 Page A0 Textual Description A0 Textual Description The process of deciding a winner is function A4 Declare Bankruptcy. The inputs to this function are the current set of players (or the initial set if the game is just beginning) and the state of their cash accounts. If all players are solvent (that is, if their cash assets equal or exceed their obligations) then they remain in the game. If any player is insolvent, then he or she must drop out of the game. The output of function A4 is the set of players remaining in the game. The winner is the last person to remain in the game. The goal of the game for each player, therefore, is to drive all other players into an insolvent position. 1/23/2012 Industrial Data and Systems Analysis 43 1/23/2012 Industrial Data and Systems Analysis 44 Text Continued Text Continued Text Completed Completed Play proceeds by determining whose turn is next and having that person roll the dice. Function A1 Take Turn tracks whose turn it is based on the current set of players and who was the first to start. The output of the dice roll is the input to the function A2 Move Piece. Based on the current location of the players' pieces, the board layout, the dice roll, and whose turn it is, it is possible to determine the new location of all pieces. Special moves occur if the player whose turn it is exercises a "Get out of Jail Free" card or lands on a "Chance" or "Community Chest" square of the board. The player whose turn it is engages in real estate transactions based on his or her current piece location, the availability of unsold properties, and the willingness of other players to trade and the willingness of other players to trade properties that they own. Initially, all properties are unsold and are managed by the bank (f (function A5 Manage the Bank). Gradually, these properties are purchased by the players and traded among themselves. The function A3 tracks the cash accounts and property accounts of each player through these transactions. 1/23/2012 Industrial Data and Systems Analysis 45 Observations 1/23/2012 Industrial Data and Systems Analysis 46 In Your Reading Packet Your Reading Packet • Diagram conveys a concept and conveys concept and functional architecture • Textual accompaniment is important • Game is not precisely defined • Family of games compatible with this functional definition • More levels of detailing can increase specificity of model ifi • Sequence and concurrency will be ambiguous 1/23/2012 Industrial Data and Systems Analysis 47 1/23/2012 Industrial Data and Systems Analysis 48 Advanced Topics Advanced Topics Taking Liberties Liberties • http://www.idef.com/pdf/idef0.pdf • Diagrams in lecture do not always conform to standard • Use more suggestive prefixes (eg. QFD) • Sometimes omit arc labels • Do not obey tunneling rules • Refer to standard for more modeling features and syntax rules. For example: • Arcs can be “tunneled”, “bundled”, and used to “call” other functions • Pages can be numbered in negative direction di (A-1) to show system function in context of larger system 1/23/2012 Industrial Data and Systems Analysis 49 1/23/2012 Industrial Data and Systems Analysis 50 Overview • Database Design Appendix: Case Study in Functional Modeling • Database Joins • Functional Modeling (IDEF0) Hospital and Pre-Hospital Planning and Response to a Mass Casualty Event • Appendix: Case Study in Functional Modeling 1/23/2012 Industrial Data and Systems Analysis 51 1/23/2012 Industrial Data and Systems Analysis 52 Functional Context Context Hospital and Pre-Hospital Planning and Response to a Mass Casualty Event Leon Cruz Jonathan Healey Perry Yang Yi Zhang 1/23/2012 Rachit Bindal Andrew Dailey Nathan Mitchell Chen Kong Joanne Lee Danny Tsung Industrial Data and Systems Analysis 53 1. Prepare for Mass Casualty Event Industrial Data and Systems Analysis Industrial Data and Systems Analysis 54 1. Prepare for Mass Casualty Event Respond to Threat of Mass Casualty Event 1/23/2012 1/23/2012 55 1/23/2012 Industrial Data and Systems Analysis 56 Respond to Threat of Mass Casualty Event 2. Forecast and Manage Demand Surge 2.1 Detect Mass Casualty Event 2. Forecast and Forecast and Manage Demand Surge 1/23/2012 Industrial Data and Systems Analysis 57 1/23/2012 Industrial Data and Systems Analysis 58 2. Forecast and Manage Demand Surge 2.1 Detect Mass Casualty Event 2.4 Limit Spread of Disease 1/23/2012 Industrial Data and Systems Analysis 59 1/23/2012 Industrial Data and Systems Analysis 60 Respond to Threat of Mass Casualty Event 2.4 Limit Spread of Disease 3 Evacuate, Expand, nd Expand, and Reconfigure Hospitals 1/23/2012 Industrial Data and Systems Analysis 61 3. Evacuate, Expand, and Reconfigure Hospital ls 1/23/2012 Industrial Data and Systems Analysis 62 3.2 Evacuate and Reconfigure Hospital Capacit ity 3.2 Expand and Reconfigure Hospital Capacity 1/23/2012 Industrial Data and Systems Analysis 63 1/23/2012 Industrial Data and Systems Analysis 64 4. Triage, Treat and Transport Patients Respond to Threat of Mass Casualty Event 4 Triage, Treat, and Transport and Transport Patients 1/23/2012 Industrial Data and Systems Analysis 65 Respond to Threat of Mass Casualty Event 1/23/2012 Industrial Data and Systems Analysis 66 5. Schedule Staff and Distribute Supply 5 Schedule Staff and Distribute Supply 1/23/2012 Industrial Data and Systems Analysis 67 1/23/2012 Industrial Data and Systems Analysis 68 Respond to Threat of Mass Casualty Event 6. Treat Patients in Hospital 6.1 Track and Locate Patients 6 Treat Patients in Hospital 1/23/2012 Industrial Data and Systems Analysis 69 Industrial Data and Systems Analysis Industrial Data and Systems Analysis 70 Respond to Threat of Mass Casualty Event 6.1 Track and Locate Patients 1/23/2012 1/23/2012 71 1/23/2012 Industrial Data and Systems Analysis 72 ...
View Full Document

Ask a homework question - tutors are online