English Info BSc: State Of The Art Database Systems

1. English Info BSc: State Of The Art Database Systems Practice 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 practice • Checking Home Assignment 0: Decidingaboutown project • Corporate Data Processing Systems • Business ProcessReengineering, BPR • Usage of MS Visio • Introduction • Drawing Business Process Diagram, BPD • Example: Invoicingprocess of carpetstore BPD • Standard components of Visio BPD • Usage and settings of SampleBPD.vsdtemplate • Practice 1-1: Re-CreatingCarpetStore BPD in Visio • Home Assignment 1-1: Creating BPD of own project • Literature

3. Corporate Data Processing Systems: BPR: MS Visio: Introduction • (Computer Aided Software Enginering, CASE)useselectronic design diagram tools to acceleratecreatingCorporatedataProcessing Systems, minimizingtheneed of manualprogramming. Usuallytheyareexpensivepackages, buttherearesomecost-effectivealternatives: • MS Visio is part of Microsoft Office Fullsetupas a general diagram drawing software, capablefor: • Drawing(Business Process Diagram, BPD) • Drawing(EntityRelationship Diagram, ERD) and inEnterprise version: • (ReverseengineerDatabase): fromexternaldatabase, createits ERD plan diagram • (Auto-generateDatabase) from ERD, generate DDL script, creatingdatabasein MS Access or MS SQL • Drawing (GUI Plan Diagram) • Wewilluseinvoicingas a carpetstore SMB as an examplefor building BPD

4. Corporate Data Processing Systems: BPR: MS Visio: Standard Components Setup of Visio is similar to other Office products, so we skip it now. • After launch, you have to choose diagram (Category) / (Template): • For ERD: Database| • Database Model Diagram(Metric) • For BPD: Business Process| • Crosfunctional Flowchart(Metric) • In the following part we will start with drawing BPD. It is highly recommended usin Visio not starting from empty project as formatting of default components correctly takes long time. • Instead of it, you may use our template BPD developed for the present course material: SampleBPD.vsd • In the starting BPD wizar, you may choose (Horizontal)functions bands • Give (Number of Bands) • Choose (Include Titlebar), Press OK • Right hand side you can see empty diag-ram, left hand side the (Shape) palettes: • Basic flowchart • Process( ) • Decision( ) • Cross Funct.:BPD-specific elements • Functional band( ) • Separator( ): milestone in time • Elements can be Dragged into with mouse • For Doubleclick you can edit their caption • They can be connected with captioned arrows by their connector points(×) • Diagram can be saved withFile|Save asinto*.vsdfile or in*.gif image Click Click Click Click Click Click Click Dbl Click Click Click Drag

5. Corporate Data Processing Systems: BPR: MS Visio: Our template • RightClicking on elements, you can edit their properties. By default they have 3 properties: • Cost(Text):amount of currency • Duration(Text):how long does it take • Resources(Text):consumed resources • As these are not very informative, in our template (see: SampleBPD.vsd) these are left only at process start ( ) and -end( ) elements,at the activities( )and decisions ( ) we defined additional properties with the Define button: • UnitCost(Currency):repeating activity • DurationSec(Number):how long, secs • EntityUsages(VarList):which entities of ERD are used by the given element and at which access rights: • Create, Read, • Update, Delete, • Archive,Nullify:fill with empty value This will be the basis of designing (Graphic User Interface, GUI)!!! • PropertyUsages(VarList):how properties of entites are accessed by the element (C,R,U,D,A,N) • Min/MaxFreqPerDay(Number):daily frequency at cyclic elements • FormUsages(VarList):access rights to (Forms/Screens) of GUI created later • For (Custom Properties) we can set in their panel: • Label: name of property, Type: data type:Text,Number,Date,Currency • (Type Varlist is multi-item text list • Format: format mask, e.g. „00.00” • Value:default value • Prompt:caption • Lang.:language • We have 4 colored arrows in our template: • YesBranch:() • NoBranch:() • Unconditional-Step: () • Feedback:at cycles() Click Rgt Click Rgt Click Click Click Click Click Click Click Click Click Click Click

6. Content of the practice • Checking Home Assignment 0: Decidingaboutown project • Corporate Data Processing Systems • Business ProcessReengineering, BPR • Usage of MS Visio • Introduction • Drawing Business Process Diagram, BPD • Example: Invoicingprocess of carpetstore BPD • Standard components of Visio BPD • Usage and settings of SampleBPD.vsdtemplate • Practice 1-1: Re-CreatingCarpetStore BPD in Visio • Home Assignment 1-1: Creating BPD of own project • Literature

7. Practice 1-1: Re-Creating Carpet Store BPD in Visio 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 • Please be aware of that even simple-looking everyday process (like invoicing in a carpet store) can contain numerous exceptions, making it more difficult, what your system should handle. Moreover, the story won’t be told by IT professionals, but „girl next door” salespersons. YOU HAVE TO TRANSLATE IT INTO YOUR SYSTEM! • „Well, when the guy checks out with his cart, he gives me the product he want to buy, I scan with the barcode reader, but if neccessera, I can type manually the barcode and quantity. It is especially true if barcode is damaged. If the „dear” customer recognizes ther that the product is too expensive for him, then I have to make storno of the item. When all these are set, we tell the gross total of the invoice for the customer, and we ask whether he need any docoment about the Value Added Tax (VAT) paid. If yes, then I have to ask his name and address. I check out whether he is already an existing customer, and if I found him, then I book the shopping into his loylaty points. If I cannot find him, I have to record himas new customer. After the guy paid, we print the receipt, which is a document for warrant and serves as a document of VAT paid” • Please search, what is missing from the process in the BPD below based on the text description and draw extended BPD in Visio using the template SampleBPD.vsd ! Sample solution: 1-1PracticeSolution.ppt

8. Home Assignment 1-1: Creating BPD of own project • Draw BPD of your own project in Visio using template SampleBPD.vsd : • Be aware of that all text on diagram should start with 3-character group code • Save the diagram as image and post it to the FB group of course! (5points) • Make a presentation about your BPD (using www.prezi.com presentation maker is recommended but not compulsory) (5points)

9. Literature • CASE tools: • List and comparison os CASE tools: http://www.cs.queensu.ca/Software-Engineering/tools.html • MS Visio advice: • MS Visio home: http://office.microsoft.com/en-us/visio/default.aspx