SQL .Net Consultation Session 3 Dr. Gábor Pauler, Associate Professor, Private Entrepeneur

SQL .Net Consultation Session 3 Dr. Gábor Pauler, Associate Professor, Private Entrepeneur Tax Reg. No.: 63673852-3-22 Bank account: 50400113-11065546 Location: 1st Széchenyi str. 7666 Pogány, Hungary Tel: +36-309-015-488 E-mail: pauler@t-online.hu

Content of Presentation Creating web-applications with relational database system 1 • Case Study 3-1: Invoicing system of a small business • Analyzig existing system with the help of Advanced Database Diagram (ADD) • Business Process Diagram (BPD) • Data Flow Diagramm (DFD) • Business Process Reengineering (BPR) • Non-professional electronic data storage solution alternatives: • Storing invoice data in a strongly typed list • Storing invoice data in a single database table • Logical design of data storage: the process of Normalization • Normal Format 1 • Decomposition of empirical data structures • Normal Format 2 • Cardinality Analysis (CA) • Defining relations: primary and foreign keys • Entity Relationship Diagram (ERD) in ADD • References

Case Study 3-1: Invoicing system of a small business • To create commercially profitable web applications, we have to store considerable amount of data in difficult structure collected on web forms • Lets assume that a small business selling rugs (Szőnye-gek) and floor tiles (Parketta) in several locations orders a thin client-based invoicing (Számlázó) system from us Until this point, invoicing was made on paper-based invoi-ces. The advantages of the paper-based Data Proces-sing System (DPS) are: • Ease of usage (everone can read and write since age of 6, and above 14 can be taught how to fill an invoice) • Flexibility: if some data is missing from the invoice (eg. name of the salesperson)it can be written on the margin However there are serious disadvantages of paper storage: • The software processing paper-based DPS is human work, which is slow, very expensive, often makes errors • Data storage requires physical movement of material, so it is very slow and expensive to search another paper file. Therefore the main principle of paper-based form design is „put some data about everything important (products, pricing,customer)together into one form” • As a result, there will be redundancy in space consum-ption (Helypazarlás): 1 invoice can store max. 12 items, but most of them have 1-2 items and 11-12 are wasted • Also, there can be data loss (Adatvesztés):if a customer purchased 13 items, the last item cannot be stored, or • A new invoice has to be opened with redundancy in workload (Munkaerőpazarlás): seller’s and customers data part should be filled again just for 1 extra item, • Or if it is not filled, just attached to the original invoice, and we loose the original, there will be ambigous refe-rence (Kétértelmű hivatkozás): identities of seller and customer cannot be figured out later There’sa need for electronic storage to eliminate these stuff

Proc:A EndProc:A FOR:A EndFOR:A IF:B ElseIF:B: EndIF:B Sql: Step: Decl: Sql:Query unit price Step: Add Item Step:Enter quantity Step: Scan Item Proc:Che- ck barcode • Proc:Restock • BarCode • Quantity • InvoiceID Step:Record Customer data • Decl: • TempName • TempAdress IF:New Customer? IF:Barcode OK? FOREACH Item ElseIF:Barcode? Step:Isuue Invoice Step:Ask customer data EndIF:New Cust? EndIF:Barcode? IF:Price is OK? EndIF:Price? • Proc:Invoicing • CustomerID • InvoiceID ElseIF:Price? Sql:Query Customers Step:Com- pute totals Step: Give Item EndFOR:Item EndProc: Invoicing Analyzig existing system with the help of Advanced Database Diagram (ADD) • The most common beginner’s error designing a new electronic system without knowing the old paper-based systems errors, or forgetting to analyze requirements of a customer PaulerSoft™ Advanced Database Diagram (ADD) is a simple MS Office-based diagramming tool for analyzing and designing storage and user interface of data processing systems • The first tool to describe and analyze errors of an existing data processing system or customer needs in a proposed new system is Business Process Diagram (BPD): it descri-bes a process with given input, output, responsibilities, time- and resource consumption. • It is a flowchart laid out in a 2-dimensional coordinate system: • Time: it is not really a physical time, but a nonlinear timescale broken by uniquely named breakpoints (Töréspont) or milestones (Mérföldkő) of the process • Roles (Szerepek): discrete (Diszkrét beosztású) coordinate axis representing uniqu-ely named units of the organization (not actual people, because they can be fired!)or partners (suppliers(Beszállító),customers, etc.)resposible for activities • Activities (Tevékenység)of the process are represented by blocks of flowchart. Length of blocks is proportional to their time requirement, while other resource consumptions (workforce, hardware, etc.) can be described in blocks’ text. They can be: • Pairs of blocks (Blokkpárok) describing process control. They can be nested into each other. Nested pairs are always tabulated (like program code) for clarity: • Procedure header /footer with parameter list and type icons. Boldfaced (Félkövér) parameters are given by referece, others by value • Cycle condition /footer with cycle variable • Conditon header /Else branch /footer with condition • Single blocks (Egyedi blokkok) describing: • Declaration of local vars with type icons,Process step ,SQL query • There are four types of arrows (Nyíl) joining blocks: • Yes branch of condition (), No branch of condition (), they are always drawn before blocks to visualize their nesting hierarchy, Step forward (), Feedback (Visszacsatolás) (),only this one can step backward in time (of course logically)

Sql:Query unit price Step: Add Item Step:Enter quantity Step: Scan Item Proc:Che- ck barcode • Proc:Restock • BarCode • Quantity • InvoiceID Step:Record Customer data • Decl: • TempName • TempAdress IF:New Customer? IF:Barcode OK? FOREACH Item ElseIF:Barcode? Step:Isuue Invoice Step:Ask customer data EndIF:New Cust? EndIF:Barcode? IF:Price is OK? EndIF:Price? • Proc:Invoicing • CustomerID • InvoiceID ElseIF:Price? Sql:Query Customers Step:Com- pute totals Step: Give Item EndFOR:Item EndProc: Invoicing Data Flow Diagramm (DFD) and Business Process Reengineering (BPR) • The next step of describing the current system or detecting customer needs is Data Flow Dia- gram (Adatfolyam diagramm) (DFD): this is a BPD, where we join to blocks of activities the description of their data requirement as empirical data structures (Empírikus a- datstruktúrák) (EDS): uniquely named sets of data fields with: • Field name (Unique within 1 EDS, can be repeated in all) • Field type icons (see legenda below) • Optional filled fields are marked with italics (Dőlt betű) • Automatically filled fields are marked with bold (Félkövér) (They can have formula also) • Access rights (Jog) of the workforce in the role responsible for activity where EDS is connected by the CRUDA model: Create, Read/retrieve, Update, Delete, Archive Let us note that BPD and DFD are not just for describing current system or custo-mer needs, but they are tools for analyzing and correcting errors: • Business Process Reengineering (Üzleti folyamatok újraszervezése) (BPR) deals with correcting errors in process flow. By Carlzon-principle (Carlzon-elv), an organization is effective only if Rights, Responsibilities, and info support from EDS are balanced at all Roles along the whole process: • If someone has rights but no info, will make bad decisions • If someone has responsibility but no rights for decision, becomes frustrated • If someone has rights but no responsibility will make irresponsible decisions It is very important that even the most carefully designed DPS becames inope-rable if BPR is not done correctly, because electronic tools are far less flexible than paper-based, this is the price we pay for much higher capacity! • Using BPD, we can correct the following errors: Invoice SellerName R SellerAddress R SellerTaxReg R BuyerName CRU BuyerAddress CRU InvoiceID R SalesPersName CRU TotalValue= Sum(Item.GrossValue) Paid CRUD Date Time Item ItemDescr R MeasUnit R BarCode CR ITJCode R Quantity CRUD UnitPrice R VATPercent R GrossValue= (UnitPrice* Quantity*(+ VATPerc/100)) TempCustomer Buyername CRUD BuyerAddress CRUD EmpDataStruct Text CRUDA Integer CRUDA Fraction CRUD Binary CRUDA Date CRUDA Time CRUDA Picture CRUDA Sound CRUDA Movie CRUDA

Business Process Reengineering 2 • Process errors tend to occour especially in Hungary, because Hungarian organizational culture is hierarchy-oriented (who will be the king, the barons…the slaves) and usually neglects importance of process design (nobody cares about that the organization should make output/satisfied customer from input on deadline) • As a result, at the lowest level of all organizations, low-paid, low-powered old blondies or unexperienced youngsters are sitting bearing full responsibility. As they have no other chace if they are fired, they try to coordinate with each other ad-hoc creating very slow, very ineffective, and unexact processes with huge wasting of resources. Typical symptoms are: • Follow-up errors (Sorrendi hiba): • Example: the worker cuts a rod, but the manager finds out its correct lenght afterwards, requiring redoing the whole thing again and again • Cure: reverse the sequence of activities in BPD (1. Decide its size, 2. Cut the rod) • Push-up game (Döntési felelősség feljebb tolása): • Example: the workers cannot decide even the simplest things and push all decisions on the manager, who has a crowded schedule and can decide minor things very slow. Most of the time is spent waiting for managers decision • Cure: authorize and educate workers preliminary to be able to make decisions • Over-control (Kézivezérlés): • Example: an agressive and paranoid boss keeps all decision rights to make him powerful. Symptoms and cure are the same as above • Unbalanced flow (Terhelés-kiegyensúlyozatlanság): • Example: lazy employees push all the work on a more gifted and diligent one, who becomes overcrowded. Symptoms and cure are the same as above, in serious cases fire lazy employees • Snowballing customers (Ügyfelekkel labdázgatás): • Example: when the customer has to go through all hierarchic levels of two concurring departments (Osztály) to process even the simplest routine cases. It takes forever, and creates good ground for corruption to bribe (Veszteget) 2 department managers to communicate directly and accelerate the case • Cure: the 2 managers should create interfunctional team from experts of the two departments to decide about responsibilities in process and keep its flow at low level • Fingerpointing game (Felelősség egymásra tologatása): • Example: when 2 concurring departments push responsibility on each other. Symptoms and cure are the same as above

Business Process Reengineering 3 Also there are some very nasty errors can be analyzed with DFD: • Data flood trap (Adattenger csapda): • Example: when an employee in a non-managerial position feels more powerful if he maintains lot of data, and request unrealisticly detailed EDS for his activity to comply • Cure: the system designer should warn him that he is the person who will be responsible for filling with data the electronic DPS. Entering unnecessary data will mean unrealisticly high workload, so compromised solution should be negotiated • Report flood trap (jelentéstenger csapda): • Example: a manager most of the time solves unstructured decision problems (Nem struktúrált döntési problémák) where connection of inputs and outputs and set of inputs are uncertain. So he tends to request all kinds of reports in such a quantities, he will never have time to read them • Cure: try to negotiate him to use On-Line Analitical Processing (On-Line Analitikus Kezelés) (OLAP) reporting user interface, where aggregation and grouping of data shown can be changed dynamically from GUI However, considering all these techniques BPR still remains expensive and lenghty process eating up 30% of all development resources and time: • System designer can figure out with BPR that 20-30% of employees do unneccessary work and could be fired, which creates a huge resistance against successful system design • So it works only with full authorization from top management, and all managers active participation, which is covered from their very expensive time Example: there is a political bargain (alku) behind al the blocks of the BPR of a pharmatical research lab below, therefore it took 1 months (104work hours) and HUF520K to prepare However, even the fines BPR is pure wasting of money if storage system design is messed up:

Practice 3-1: Creating business process diagram • Shortly, you will be asked to create BPD from the processes detailed below. Please create a new empty slide in PowerPoint and use ADD symbols (blocks, pairs of blocks, arrows and roles × milestones grid) to describe BPD. Present your design in 3 minutes (2pts): Student 1: Park a car in a parking house Student 2: Boiling an egg Student 3: Start a car in cold weather Student 4: Open a bank account Student 5: Fill a car (it can be diesel or gasoline engined) in a gas station Student 6: Park a car in downtown Student 7: Order a cab Student 8: Report an accident to police Student 9: Arrange a visit at dentist Student 10: Register somebody on a community website Student 11: Buy ticket on an Intercity train personally Student 12: Reserve an airticket by phone

Struct tInvoice InvoiceNum As String SellerNameAs String SellerAddressAs String SellerTaxRegAs String BuyerNameAs String BuyerAddressAs String InvoiceDate As Date Item1NameAs String Item1MeasUnitAs String Item1Quantity As Double Item1UnitPrice As Single Item1TaxPerc As Single Item1GrossVal As Double Item2NameAs String Item2MeasUnitAs String Item2Quantity As Double Item2UnitPrice As Single Item2TaxPerc As Single Item2GrossVal As Double … TotalVal As Double End Struct Dim Invoices As New _ List (Of tInvoice)() Non-professional electronic data storage alternatives: Beginners tend to solve data store problem over-simplified: • They may convert paper-based invoices into a single strongly typed list from (see Session2) to store data in memory and sometimes save it in a backup file. Lists give pretty fast storage but their size are limited by available working memory • Or, they may store invoicing data in a single, uniquely named database table (Adatbázis tábla): • A permanent data storage on hard drive containing of fixed-lenght, fixed-structure, sequentially (1..n) indexed records (Rekord): • Set of unique named data fields (Adatmező) describing attributes (Tulajdonság) (eg. name, Age, Gender) of an occourence (Előfordulás) (eg. Kovács János) of an entity (Egyed) (eg. Man, Book, Car, etc.). Fields are described by: • Field name (Mezőnév) • Field type (Mezőtípus):String,Date,Integer • Min/Max/Default Val.s(Min/Max/Alapérték) • Compulsory/Optional Fill (Kötlező kitöltés) A database table can store huge amount of data on hard drive and retrive records very fast, as their starting address can be computed in advance as Index×Record lenght. However it is slower than memory based List But, they do not solve the main problems of paper-systems: • They still waste space(1filled item+11 empty in 1record) • They still loose data (cannot store13th item of invoice) • They still waste workforce(open new record for 13th item) So, electronic tools are useless without data storage design!

Invoice SellerName R SellerAddress R SellerTaxReg R BuyerName CRU BuyerAddress CRU InvoiceID R SalesPersName CRU TotalValue= Sum(Item.GrossValue) Paid CRUD Date Time Item ItemDescr R MeasUnit R BarCode CR ITJCode R Quantity CRUD UnitPrice R VATPercent R GrossValue= (UnitPrice* Quantity*(+ VATPerc/100)) Logical design of data storage: the process of Normalization, Normal Format 1 The simple solutions above result in data loss, wasting storage space and working time because empirical data structures (EDS) they tried to model are full with 1:many( ) or many:many( ) nested relationships (Beá-gyazott kapcsolatok) between their fields, for example: • 1 seller or buyer can have many seller or buyer addresses • Many buyer can have 1 address (eg. members of a family) Moreover, one EDS can nest another one: • 1 invoice can have many items All these nestings will result non-fixed lenght data structures, which cannot be processed by fixed-lenght record structures of a list or a database table To make them workable and enjoy their high capacity storage, EDS should be transformed into a structure fits to data storage needs in a process called normalization (Normalizáció): it is a 5-step process, whose steps are called normal formats 1..5 (1.-5.Normálforma) and it has 3 goals: • Avoid loss of data • Avoid redundancies in space consumption and data entry workload • Ensure unambigous (Egyértelmű) relations between data fields Lets note that normalization itself does not minimize speed of data retrieval! As lists and database tables provide fast retrieval, normalization focuses minimizing storage space consumption. These two requirements usually contradict (Ellentmond) each other, one can be optimized only at the others price. So finding the best compromise between them will require fur- ther design steps called denormalization (Denormalizáció) Normal Format 1:EDS has to be decomposed(Szétbont) into Entities(Egyed): • They are objects with large number occourences (Előfordulás) (eg. Man, Book), which can be described by the same, unique-named attributes (Tulajdonság) (eg. FirstName, LastName, Gender, Age, DateOfBirth, etc.) • Entities are system-independent, logical (Logikai) store designs, denoted by unique logical name (Logikai név) which is non-plural (Egyes számú) (eg. Book, instead of Books) • Entities can be stored physically in lists/object collections in memory or in database tables on hard drive, where occourences are records and their attributes are data fields. Unique names of system-dependent physical storages are in plural (Többes szám) (eg. Table of Books instead of Book)

Cardinality Analysis, Normal Format 2: Concept of keys and relations EDS are broken up into entites using cardinality analysis (Számosság elemzés) (CA): it compares 2 selected attributes of EDS or 2 entities and determines how much occourences of attribute/entity A are related to how much occourences of attribute/entity B • We denote Entity, Attributes, their relation, its cardinality by corresponding color in text • Cardinalities (1:1,1:many,many:many)are examined in 2 directions: „A to B, and B to A” For example: • 1Invoicecan containmanyItems, but1Itembelongs to1Invoice • 1Buyercan havemanyAddresses, and1Addresscan be resided bymanyBuyers • The 2 directions are separated by „but” at 1:many and „and” at many:many relationship • Conditional (Feltételes módú) relation verb (Kapcsolati ige) means optional cardinality (eg. 1Invoicecan containmanyItems  0 ≤ many), non-conditional means compulsory (eg. 1BikehasmanyWheels  2 ≤ many) • A typical beginners’ error at CA is to underestimate cardinality of a relation: eg. assuming Buyer:Address = many:1 it is true in 1 moment, but DPS should work for several years, when buyers can move, so Buyer:Address = many:many. Always think about possible exceptional cases! The 1:1 rule of entities: an entity should relate its fields 1:1. For example: 1Invoicehas1InvoiceNumber, 1IssueDate, 1PaidStatus, etc. • Whatever attribute is related to entity with different cardinality, will be a member of a different entity. For example: 1Invoicecan containmanyBarCodes and 1BarCodecan be presented onmanyInvoices, so BarCode cannot be attribute of Invoice Normal Format2:To preserve data of EDS,Entities should be connected by relations(Relációk): • A primary key (Elsődleges kulcs) attribute is assigned in each entity: • It uniquely identifies occourences, so it cannot contain repeating or empty (Null) values • For safety, it is always an artificial (Mesterséges), sequentially auto-numbered field • If the entity has natural unique ID (Természetes egyedi azonosító), (eg. InvoiceNum at Invoice) it is stored in a separate, uniquely filled field, but we never base our system on external ID, because it can collapse (eg. in 2008 invoice numbers were adapted to EU) • Primary key is denoted by orange in CA. Its recommended name is EntityNameID • A many:1 relation is a reference to primary key of „1”side-entity by „many”side-entity’s foreign key (Idegen kulcs) field: it has the same name and data type as primary key, but can contain repeating and empty values. In CA, it is denoted by olive.It can be required/optional

EntityName EntityNameID Text Integer Fraction Binary Date Time Image Sound Movie ReqForeignKey OptForeignKey Modifier Modified Status MasterEntity MasterID MasterName Normal Format 2, Entity Relationship Diagram (ERD) with ADD • As Items table can have several million records, and unlimited number of them can reference to a given InvoiceID, one can see that number of items on an invoice became practically unlimited instead of the original 12! This is how relations resolve the problem of storing non-fixed length data structures but with still maintaining high retrive speed from database tables or memory based lists/collections • 1:1 relations does not appear between entities, because attributes related accordingly are stored inside one entity • Many:many relations (eg. 1Invoicecan Itemizemany type of Products and 1Product type can be Itemized on manyInvoices) are stored broken into 2 many:1 relations, where „many” side of both relations is a relational entity (Relációs egyed) denoted with blue color (eg. Item): it contains 2 foreign keys (eg. BarCode, InvoiceID) referencing to primary keys of connected master entities (Törzsegyed) (eg. Invoice and Product) • Relational entity usually has its own primary key (eg. ItemID),but it plays no role in building up the current relation. It is neccessary if later we want to reference relational entity from other entities (eg. Discount) • As relational entity can have several million records, it can describe every ima-ginable connection of products and invoices (1 type of product on thousands of invoices, 1 invoice with thousands of products, 1 product sold only in 1 invoice) One can see that it is hard to overwiev relations of a difficult storage system with dozens of entities from little sample tables. Therefore ADD provides tools to re-present it on entity relationship diagram (Egyedkapcsolati diagram) (ERD): • Entites are rounded corner boxes with EntityName at the top. Blue background denotes codetable/master entities with minimal data change in time, yellow denotes relational/transaction entities: rapid, irrevocable data changes in time • Attributes are listed with their data type icons:( , , , , , , , , ) and names: italic means optional-, normal means required-, bold means auto-filled attribute • Data attributes are purple, primary keys are orange prompted by ( ), foreign keys are olive prompted by( ), auto-filled system logging attributes are black • 1:many relations are denoted by ( ) connecting primary- and foreign keys

References (----------------------------- Practice 3-1 Should be performed here! --------------------------------) Relational analysis websites: • http://www.dcs.bbk.ac.uk/~steve/rdanineteen/ • http://www.redbrick.dcu.ie/~foo/ssadm/exam-stuff/rda.pdf • http://gawain.soc.staffs.ac.uk/modules/level1/CE51500-1/s1w8/RELATIONAL%20DATA%20ANALYSIS.htm

SQL .Net Consultation Session 3 Dr. Gábor Pauler, Associate Professor, Private Entrepeneur