State of the Art Database Systems Presentation

1. English Info BSc: State Of The Art Database Systems Presentation 1 Dr. Gábor Pauler, Associate Professor, PTE-TTK, Room F104, 6th Ifjusag str. Pecs, Hungary Mobile: 30/9015-488, Skype: gjpauler E-mail:pauler@t-online.hu Facebook and Open FTP sites of the course: http://www.facebook.com/groups/278606362188127/ ftp://gamma.ttk.pte.hu/pub/pauler/StateOfTheArtDataBase/

2. Content of the presentation • Corporate Data Processing Systems • Organizational preconditions • Business Process Reengineering (BPR) • Business Process Diagram (BPD) • Process errors and their resolution on BPD • Final result of BPR • Disadvantages of paper-based solutions • Data Flow Diagram • Spreadsheets • The spreadsheet-trap • When to use Spreadsheets? • The FUBAR-example • Literature

3. Corporate Data Processing Systems: Organizational Preconditions 1 • Intorduction of even the finest data processing system of the world will fail, of the tageted organization is disfunctional. First we will learn about organizational problems: • Any (Business/Institutional Organization) has 2 components: • (Hierarchy): which (Departments), consists of which (Branches), which (Teams), working in which (Shift), by which (Positions). Who are in (Top management), (Mid-management), who are the (Employees) • (Process Flow): Which (Inputs) the process has (customers, suppliers, resources), which position, in which shift does which activity (Activity), what are the (Outputs) of the process (e.g. satisfied customer) These things are recorded in (Standard Operating Plan, SOP)Hint: at job hunting, if they cannot tell the exact SOP for the targetzed position, it signals organizational mess around it. Worth to think about to take the position then… • The (Carlzon Principle) states that at effective organizations, at all positions (Authorities), (Responsibilities), (Information support) should be balanced: • If somebody responsible for something, but she has no authority to decide about it, she will be frustrated • If someone has decision authority, but has no correct info, he will do sillynesses • If someone has authority, information support, but no responsible for doing anything, that is called politician… (Jan Carlzon was a top manager in 1970s at SAS Scandinavian Airlines, creating the most profitable airline from a decrepit, bankrupted company in 8 years.)

4. Corporate Data Processing Systems: Organizational Preconditions 2 God SemiGod SemiGod SubGod SubGod SubGod SubGod Worker3 Worker4 Worker5 Worker6 Worker1 Worker7 Worker2 Sign Gossip Customer Photocopy Passiance Protegee? Check Stamping Bribe? Coffee Typewrite Aláírta? Passiance Print Pregnacy Report Rewrite Drill • In developing countries, social tradition frequently counteracts to make these principles effective working: • In societies with tradition of dictatoric/bureauctratic rule, decision makers are overestimating the importance of organizational hierarchy, creating too much leveles even for simple tasks. (Parkinson’s Law) says that bureaucrat always wants more subordinates instead of competitors, so bureaucratic organizations can grow 3-5% per annum, regardless the real quantity of their job (e.g. government) • In the meantime, process flow is largely neglected, nobody designs that. Therefore, in the lowest level, fresh IT graduates/interns, young secretaries, 55 years old typewriter-age ladies have no other choice than set up ad-hoc prosesses, otherwise they will be fired! Ad-hoc processes will work unreliably, annoying customers, with huge wasting of time and money. • This can be only resolved by (Business Process Reengineering, BPR) Let’s see its methodology and the type of problems it can solve:

5. Corporate Data Processing Systems: BPR: Business Process Diagram Proc:A EndProc:A FOR:A EndFOR:A IF:B ElseIF:B: EndIF:B Sql: Step: Decl: Sql:Query Unitprice Step: Record item Step:Enter Quantity Step: Scan item Proc:Check Barcode • Proc:Restock • BarCode • Quantity • InvoiceID Step:Record Customer data • Decl: • TempName • TempAdress IF:New Customer? IF:Barcode OK? FOREACH Item ElseIF:Barcode? Step:Issue Invoice Step:Enter TempName,Address EndIF:New customer? EndIF:Barcode? IF:UnitPrice OK? EndIF:Ár? • Proc:Invoicing • CustomerID • InvoiceID ElseIF:Price bad? Sql:Query Customers Step:Sum UnitPrice×Quant Step: Show item EndFOR:Item EndProc: Invoicing • Purpose of (Business Process Diagram, BPD) is to describe a process (inputs, outputs, operations, responsibilities and time/resource consumptions) with a (Flowchart) organized in a 2-axis coordinate system. As flowchart by default is non-stuctured tool, there are xtra rules in this course material called (Advanced Database Diagram (ADD) standard: • Axis 1: (Time): it is not physical time, but set of (Breakpoints/Milestones) of the process • Axis 2: (Roles): discrete scaled axis showing working (Positions/ Workstations/ Partners) It does not show actual persons, as they can leave/ be fired. • Inside: (Activities) are described with (Blocks): their time consumtion is proportional with their lenght, other resource consumptions are described with popup text. They can be: • (Pairs of Blocks): they describe process control elements,they can be nested into each other, nested elements are tabuleted inward, just like program code: • Procedure header /footer withwith list of parameters and their types denoted by icons. (Boldface) list itemsdenote parameter by reference, (Normal) denote parameter by value • Cycle condition /close with cycle variable • Condition header/Else branch /close • (Single Bolcks) can be: • Declaration of local variableswith type icons, simple step , SQL query • Four types of (Arrows) can connect blocks: • Unconditional step forward (), True branch of condition (), False branch of condition (), (Feedback) (): stepping backward to earlier process milestone • Advice: If you draw red and blue arrows on the left hand side of the blocks, and other types on right hand side, they nicely show nesting hierarchy of blocks!

6. Corporate Data Processing Systems:BPR:Errors and their solution in BPD 1 Time: Start +1hrs +2hrs … to the infinity Time: Start +1hrs +2hrs Fore-man Fore-man Create tolerance Command: do again Create tolerance Tell tolerance Complies? Wor-ker Wor-ker Manufacture Manufacture Time: Start +1hrs +2hrs … weekly … monthly Time: Start +1week +2week … to the infinity Perfor-manc OK? Drin-king with poli-tician Drin-king with politi-cians Top-mngm Top-mngm Wife Lo-ver Train dele-gate Wife Yacht Hun-ting Hun-ting Deci-sion Perfor-manc OK? Cus-tomer Mid-mngm Mid-mngm Emp-loyee Cus-tomer Emo-loyee Re-port Train dele-gate Command Serve Serve Propo-sal Served Request Ser-ved Req-est • (Follow-up error): bad sequence of activities are formed by tradition FUBAR:„We used to do it this way, why to change, never mind, honey…” (e.g. Worker first manufactures the material, foreman then finds out the tolerance. If material fails, it is disposed as scrap, and manfactured again), leading to almost infinite cycles because of iterative corrections.It dramatically explodes the minimum amount of time necessary for the whole process called (Critical Path Time)Resolution: sequence of steps should be rearranged: • (Push-up game): employees are kept uninformed, and they have no authority to decide even routine things. (Over-control): Boss has an over-controling personality, and does not trust employees to delegate decisions. Hint: Very frequent error in small businesses because lack of managerial training of the owner(Unbalanced flow): one employee has more diligent personality or overwhelming professional skills, so all others push as much work on him as possible, resulting long waiting lines of customers.Resolution: Time should be invested by boss to take out employees from daily routine, train them how to decide professionally, delegate routine deciosions. Invested time will come back soveral times as boss has to check emplyees only periodically, and customers can be served more quickly.

7. Corporate Data Processing Systems:BPR:Errors and their solution in BPD 2 Approve? Time: Start +1week +2week … infinitely Time: Start +1week +2week +Day15 +Day16 Launch Project Prod Top Mngmt Gyártás.Felső-vezet. Decision Build team Pro-cess decision Gyártás.Közép-vez. Prod Mid Mngmt. Re-port Com-mand Com-mand Gyártás.Alkal-maz. Prod Emp-loyee Serve Serve Sub-mit Approve? Launch Project Market Felső- vezet. Market Top Mngmt Deci-sion Build team Pro-cess decision Market Közép-vez. Market Mid Mngmt Re-port Com-mand Com-mand Market Alkal-maz. Market Emp-loyee Sub-mit Sub-mit For-ward Ügyfél Cus-tomer Served Served Request Request • (Snowballing game), (Fingerpointing/Blaming game): different departments of an organization competing for same resources, and they are in conflict of interest, trying to push work and responsibility on each other. There is bad communication between heads of departments, information walks both departmental hierarchy forth and back, slowing things incredibly Hint: This is the trap of „Official workflow”. It generates corruption: if you bribe department heads to communicate directly, it can accelerate process. Resolution: (Key Performance Indicators (KPI) of departments should be transformed so that they can be rewarded ONLY TOGETHER based on serving final customer. Interfunctional (Team) should be set up from max. 5 experts (not bosses!) of respective departments, and a (Project) is launched on a given (Deadline), supplied by sufficient (Resources) with the (Goal) of working out compromised joint process. Then bosses accept the process and supervise it to execute on their behalf. $

8. Corporate Data Processing Systems: BPR: Final Result • In the figure below, there is a real example of BPD for a pharmatical research lab. It was prepared in 2006 in 3 months using 104 working hours of 3 experts (1 IT, 2 researcher), at the expense 1600EUR. Anyone who is familiar with programming will say: „Hey, flowchart of my first program was more difficult! Why it takes so long time to do and why it is so expensive?” • Partly because it ties down experts from IT side (who know system design) and from research side (who know pharmatical research). • Moreover, BPR is not just an algorithm developement! Behind each decision blocks, there are hard political bargains in the organization, about who will command what resources, to reach a minimally acceptable solution for everyone. Otherwise they will sabotage it. • System developer needs the time and full support of top management, otherwise he cannot get through neccessary changes, which hurt interests of influential people: „Dear Alec, we figured out that your work is useless, while you are holding back vital information to preserve your power, so now you can go to lower position or will be fired!” • If top management is too diplomatic, wants to be nice for everybody, and does not give real support for system developer, BPR+system development will fail! Know how to contract!

9. Content of the presentation • Corporate Data Processing Systems • Organizational preconditions • Business Process Reengineering (BPR) • Business Process Diagram (BPD) • Process errors and their resolution on BPD • Final result of BPR • Disadvantages of paper-based solutions • Data Flow Diagram • Spreadsheets • The spreadsheet-trap • When to use Spreadsheets? • The FUBAR-example • Literature

10. Corporate Data Processing Systems: Further traps: The Cult of Paper • Besides the organization, there are further traps at creating effective corporate data processing systems: • Example 1-1: Invoice of a carpet-selling SMB even you do not understand a word of it, you will be able to notice common traps in (Paper-Based Legacy Sytems), from where you usually have to start system development: • Usage of paper-based administration is well integrated in most of cultures: anyone can read/write from age of 6 • Paper is a flexible data storage medium: if a field is forgotten from the form, it is written on the margin • Very slow and expensive processing software, working unreliably: human workforce, no automatic checks! • Data processing requires expensive physical movement of paper, so its basic principle: „Put together some data of all important entities (Products, Buyer, Seller) in one form, even you can store them only partially” • This leads to (Redundancy in space consumption): an invoice can store 12 items, but most of them use only 1 • In the meantime there can be (Data Loss): if the guy bought 13 items, the last you cannot store, therefore… • You have to open a new invoice copying manually the same seller/buyer data on the header leading to (Redundancy in Workload) • Or, if they do not fill that correctly at the new invoice, just attach to the old one, it can lead to (Ambigous References): who was the seller/buyer? To avoid these traps, you need relational database, but that requires completely different way of thinking than paper: „Put in separate table which is not absolutely 1:1 related, otherwise the system will produce the same errors as paper but more expensive way!”

11. Corporate Data Processing Systems: Further traps: The Waterfall-scandal Sql:Query Unitprice Step: Record item Step:Enter Quantity Step: Scan item Proc:Check Barcode • Proc:Restock • BarCode • Quantity • InvoiceID Step:Record Customer data • Decl: • TempName • TempAdress IF:New Customer? IF:Barcode OK? FOREACH Item ElseIF:Barcode? Step:Issue Invoice Step:Enter TempName,Address EndIF:New customer? EndIF:Barcode? IF:UnitPrice OK? EndIF:Ár? • Proc:Invoicing • CustomerID • InvoiceID ElseIF:Price bad? Sql:Query Customers Step:Sum UnitPrice×Quant Step: Show item EndFOR:Item EndProc: Invoicing • Currently, the mainstream system development method is still the (Waterfall-method/Structured System Analysis and Design Method (SSADM), drawing 10 important/61 minor design diagrams in (Unified Modeling Language (UML), managing project with (PRINCE2 Method) • These are originated in military/large corporate environment and totally unfit for SMB developments being too expensive, lengthy, less interactive, requiring big expertise from both system developer- and business side. • Therefore bounch of (Lean Development Methods) are invented (e.g. Scrum Method), but they usually focus on coding and weak on design side. • In this course material we are focusing only on making the most essential design documentation of developing relational database systems: • (Data Flow Diagram, DFD): it is a BPD with (Empirical Data Structures, EDS) added to activities supported by the system. EDS are collected from interviewing proposed end users by developers and described with: • Name of field (8±3 chars without special chars, unique inside 1 EDS) • Field type icons • Optional fields are (Italics) • Auto-filled fields by system (using formula) are (Bold) • Compulsory fields are typed (Normal) • (Access Rights) of the given user at given activity are denoted with capitals: • Create • Read/retrieve • Update • Delete physically • Archive+logical delete TempCustomer CustName CRUD CustAddr CRUD Item ItemDescr R MeasUnit R BarCode CR TaxCode R Quantity CRUD UnitPrice R VAT% R GrossValue= (UnitPrice* Quantity*(1+ VAT%/100)) Invoice SellerName R SellerAddress R SellerTaxID R BuyeName CRU BuyerAddr CRU InvoiceID R SalesPerson CRU GrossTotal= Sum(Item.GrossValue) Paid CRUD Invoicedata InvoiceTime EmpDataStruct Text CRUDA Integer CRUDA Real CRUD Binary CRUDA Date CRUDA Time CRUDA Image CRUDA Sound CRUDA Movie CRUDA

12. Content of the presentation • Corporate Data Processing Systems • Organizational preconditions • Business Process Reengineering (BPR) • Business Process Diagram (BPD) • Process errors and their resolution on BPD • Final result of BPR • Disadvantages of paper-based solutions • Data Flow Diagram • Spreadsheets • The spreadsheet-trap • When to use Spreadsheets? • The FUBAR-example • Literature

13. In a university IT training 95% of the time you will learn very professional systems and 5% of the time the disdained Excel. Then you go to work for a company, where you will use secretary-created Excel („Isle Applications”) 60% of your working time, filling different gaps of a big integrated system (e.g. SAP) The problem is not usage of spreadsheet software itself (they can be great when used correctly), but using without serious IT knowledge: If paper-based data structures are simply mirrored on a worksheet, instead of redesign, they will produce exactly the same problems as paper: redundancy, data loss, ambiguity Metadata, data content and processing formulas can be totally mixed up in a worksheet: If formulas are written in low quality (using formula wizard), without using named ranges, tabulation, documenting comments, then even small modifications take so much work that it is easier to redo the whole thing! Therefore they fight with obsolete Excels manually. Lack of upscaleability: if Excel is misused instead of relational database, it will result in a 120MB workbook taking 5 minutes just to open, because of the 20-50× more by cell-resource consumption of Excel. Corporate Data Processing Systems: Further traps: Secretary-level Excels CZ184789 CZ184790 CZ184791 CZ184788 • Conclusion: Long term it pays off better to completely redesign secretary-created legacy Excels • (deciding whether they can be kept in Excel or migrated into database), • than keep on suffering with them • But that requires system develop-ment knowledge

14. Corporate Data Processing Systems: Further traps: Spreadsheet or Database? 1 • 1. (Data Mass): Keeping in spreadsheet, the application should not contain large mass of (Transaction data): size of data growing proportionally with time/business activity, otherwise large by-cell resource consumption of Excel will hit us. BUT: • 1. From Office 2007, worksheet size went up from 65535×255 to 1024000×1024 cells, which can be enough for most SMBs. • 2. Excel can aggregate data into Pivot Table/Diagram from database table with 9-10M record using as external data source. • 2. (Relations): Keeping in spreadsheet, application should not contain with complex data structures with many 1:many internal relations, which should be decomposed to separate tables connected with relations. BUT: From Office 2017, the primitive MS Jet database engine of Excel is replaced with MS PowerQuery: • It can handle dat model with dozens of tables and hundreeds of relations • Tables can be handled as worksheets with cell functions, but also as database tables with SQL queries, depending on what is more convenient in the given situation • GUI is wizard-based, no manual programming is neccessary (but can be programmed also with scripting)

15. Corporate Data Processing Systems: Further traps: Spreadsheet or Database? 2 • 3. (Standalone Application): Excel has limited capabilities in shared data access comparing to a relational database (shared for read only). • BUT: From Office 2017, shared from network folder-modifications with by-cell modification diary are introduced • Although it still can’t handle alternative versioning, if there are conflicting multi-user inputs for the same cell. • 4. (Statistical Analysis /Optimization Requirement): Excel (with VBA Analysis Toolpack Add-In using user friendly (Graphic User Interface (GUI) has an advantage over databases if the application has a requirement for running OLS regression, ANOVA, Correlations, Factor Analysis, Genetic Optimization, Gradient Optimization, Linear Programming Optimization, Integer Programming Optimization. For databases, these are accessible only in expensive data mining tools (e.g. Oracle + Express Objects or Rapid Miner), or in free R, but that has no GUI, only command line/scripting.

16. Corporate Data Processing Systems: Further traps: Spreadsheet or Database? 3 • 5. (Debug/Testing Cost): As in Excel we build a computing algorithm step-by-step with cell functions, partial results can be immediately seen. This requires less programming skill than 4GL OOP programming or SQL, where you have to imagine internal working of algorithm by head until it can be tested. • 6. (Deadline): Therefore Excel is suitable for (Rapid Prototyping): developing GUI with reduced processing functionality quickly, and show to the final user to get some feedbacks about it DURING development process, instead of just showing it at the end. Using (ActiveX Controls), you can put the usual elements of a GUI on a worksheet: dropdowns, scrollbars, media players, etc. • 7. (Software Cost): Excel is everywhere, at least in an illegal copy • 8. (Tutorial Cost): Even very simple people can use Excel some degree (opening, saving, etc.), therefore tuition of GUI of the system won’t have to start from zero. • 9. (Desktop): If user has a requirement to freely use and build computations with any partial results of the system, a partially locked worksheet can be good solution as GUI

17. Corporate Data Processing Systems: FUBAR Example Component CompID SubPerMain ValidFrom ValidTo MainMatID SubMatID Compulsory Modifier Modified Status MatClass MatClassID Descr ActualValue DataType MeasUnitID MaterialID Modifier Modified Status SupplMaterial SupplMatID DecCounter Descr ValidFrom ValidTo MeasUnitID MainPartID Modifier Modified Status MaterialMaster MaterialID MatGroup Descr ValidFrom ValidTo MeasUnitID MainPartID Modifier Modified Status PurchInfoRec PIRID Descr UnitPrice ValidFrom ValidTo SupplMatID MaterialID CompulsoryPart Modifier Modified Status • In 2006 a branch of a Finnish global company manufacturing mobile phone circuit boards located at Pécs called me with the big question:„how the f**ing Excel can handle more than 65535 lines in a worksheet” • They used a smaller cheaper ERP system instead of SAP (Baan, whose supplier was out of business), and only accounting and finance modules were introduced, not logistics. The plan was that „The guys at Pécs Central IT department will program logistics module” • This was a 2000man×year task for 40 guys, so they ended up handling strategic data in secretary-level Excels. E.g. Bill Of Material (BOM): which describes component requirement of several 1000 (semi-)finished products was in a single giant worksheet. • BOM would require at least 5 big database tables in simplest layout, but during 538 f**ing meetings, nobody ever recognized that they need relational database, they wanted to fix „the stupid Excel” • When EU forbade using components soldered with plumb, component number suddenly doub-led, and their Excel collapsed. They delivered wrong stuff, wrong time, wrong content,to wrong customer resulting 9000EUR/month fines. • We submitted an offer for a database at 30000EUR, but they found it too expensive (3 months return…) and left the system as it is! • The company reached negative gross profit in 2006 in a rapidly expanding market. • In 2008 crisis their most important customer, Nokia dropped them as worst ever supplier. • In 2010 they went bankrupt firing 2000 men!

18. Literature Organization: • Jan Carlzon: www.andrewgibbons.co.uk/documents/Moments.doc • Northcote C. Parkinson: http://en.wikipedia.org/wiki/Parkinson's_law BPR: • http://www.businessballs.com/business-process-modelling.htm • http://www.altova.com/umodel/business-process-modeling.html • http://www.teamtechnology.co.uk/business-process-reengineering.html BPD: • http://www.omg.org/bpmn/Documents/6AD5D16960.BPMN_and_BPM.pdf DFD: • InSmartDraw: http://www.smartdraw.com/resources/tutorials/data-flow-diagrams/ • http://www.thekjs.essex.sch.uk/yates/data_flow_diagram_(dfd).htm Spreadsheets: • Excel tricks: http://www.andrewsexceltips.com/ • Excel tricks: http://www.andypope.info/ • Excel blog: http://www.dicks-blog.com/