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

SQL .Net Consultation Session 4 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 3 • Physical design alternatives of data storage system: • Nested object collections • Relational database system • Comparison of object-based and relational system • Storage system design with MS Visio • Installation, resurce requirement and versions • Business Process Diagramm • Shapes palette • Editing custom properties of elements • Entity Relationship Diagram • Formatting settings of ERD Template • Entity and relation settings • Logical and physical names • Generating database from ERD • Reverse engineering database structure into ERD • Creating ERD of invoicing system with MS Visio • Using template • Create DDL script References

Class Buyer Inherits Address ‘Data fields FirstName As String Lastname As String CellPhone As String E-mail As String ‘Embedded classes fAddress As Address End Class ‘Global list Dim Buyers As New _ List Of (Buyer)() Class Country ‘Data fields CntName As String End Class ‘Global list Dim Countries As New _ List Of (County)() Physical design alternatives of data storage system: Object hierarchy Class StreetType ‘Data fields TypeName As String End Class ‘Global list Dim StreetTypes As New _ List Of (StreetType)() Class Zip Inherits Country ‘Data fields City As String ‘Embedded classes fCountry As Country End Class ‘Global list Dim Zips As New _ List Of (Zip)() As a final step of logical design of data storage,one can think about the possibilities of the targeted physical storage: • In Session2, we already mentioned that typed lists in VB are pretty suitable for storing data of entities, crea-ting a fast, memory-based storage: • First, we define a class from all entities of ERD • Then we declare a shared, public typed list of objects of each class • We can emulate relations if each class on the many side of the relation inherits class of the one side • Then, in the many side class a data field is created typed by the one side class • This field will store an object reference to a specific item of the shared public list typed by class of one side ( ) Moreover, object collections can directly resolve problems of storing non-fixed lenght EDS as they can store diferrent type of objects, which can embed another collections. However, they are slower than lists. Class SalesPers Inherits Address ‘Data fields FirstName As String Lastname As String CellPhone As String E-mail As String ‘Embedded classes fAddress As Address End Class ‘Global list Dim SalesPersons As New _ List Of (SalesPers)() Class Address Inherits StreetType,Zip ‘Data fields Door As String Door As String Floor As String HouseNum As String LinePhone As String Fax As String ‘Embedded classes fStreetType As StreetType fZip As Zip End Class ‘Global list Dim Addresses As New _ List Of (Address)() Class Seller Inherits Address,LegalFormat ‘Data fields FirstName As String Lastname As String CellPhone As String E-mail As String ‘Embedded classes fAddress As Address fLegalFormat As LegalFormat End Class ‘Global list Dim Sellers As New _ List Of (Seller)() Class LegalFormat ‘Data fields FormatName As String End Class ‘Global list Dim LegalFormats As New _ List Of (LegalFormat)()

Physical design alternatives of data storage system: Relational Database System • The second alternative is to create a Relational Database System (RDS) (Relációs Adatbázis) in a Relational Database Manager (RDM) (Relációs Adatbáziskezelő) as physical storage. • We can compare advantages and disadvantages of Object hierarchy-based and RDS physical storages at the following points:

Storage system design with MS Visio We could see that ADD was a simple and effective MS Office-based diagramming tool for designing relational databases. However it main disadvantage is that it cannot generate working database from ERD automatically, and defining hundreeds of tables and thousands of relations in a big system is a taxing manual coding work. • Therefore, we should get acquinted in Computer Aided Software Engineering (CASE) (Számítógéppel segített szoftvertervezés) tools: these are integrated system development environments supporting the whole design process: • Upper CASE: Preparing logical design of storage and GUI • Middle CASE: Preparing system-dependent physical design of storage and GUI • Lower CASE: Auto-coding modules of relational database and forms of GUI • Let us note that CASE tools in general do not automate normalization process itself, as this is an ill-structured decision problem (Rosszul struktúrált döntési probléma): the algorithm from its inputs (DFD) to its outcome (ERD), is not fully deterministic. Therefore CASE tools provide only diagram techniques for designing and auto-coding systems. MS Visio is the general purpose diagram design tool of MS Office package. We will deal with Business Process Diagram (BPD) (Keresztfunkcionális folyamatábra) and Entity Relationship Diagram (ERD) (Egyedkapcsolati diagramm) more detailed. • MS Visio itself has limited resource requirement (150MB installed), but before Office 2007, it could not be installed without .Net environment (9MB) and Visual Studio (1630MB). In case we need a CASE tool with limited resorce requirement, it is better to choose ERWin (see: http://www.ca.com/us/products/product.aspx?id=260) • There are 3 variants of MS Visio: • Standard: in this, there is only BPD, but no ERD • Professional: it can handle both BPD and ERD and can Reverse Engineering (RE) (Visszafejteni) ERD design from existing databases, but cannot auto-generate database from ERD • Enterprise: it can also generate new empty database from ERD automatically in MSSQL, Access, Oracle, but this is not part of MS Universty Licensing Program

MS Visio GUI 1 click click Insallation of MS Visio is similar to other Office products, therefore we omit it. • After starting MS Visio, we have to choose diagramCategory and Template first: • For ERD: Database • Database Model Diagram(Metric) • For BPD: Business Process • Crosfunctional Flowchart(Metric) • However, in the practice, we never start to build BPD or ERD from an empty diagram, because it takes forewer. Instead of it, we use well-formatted design templates: BPDTemplate.vsd, ERDTemplate.vsd Graphic user interface of BPD designer: • When BPD Wizard starts, choose Horizontal function bands for different roles working on the process • Give Number of Bands • Check Include Titlebar, press OK button • Right hand side on the screen, an empty BPD comes up, on the left hand side different palettes of Shapes: • Basic flowchart: • Process( ) • Decision( ) • Cross Funct.:BPD-specific elements • Functional band( ) • Separator( ): for milestones • Elements are drawn to diagram by mouse • For Doubleclick, we can edit their caption • We can connect them with denoted arrow-elements at their join points (×) • BPD can be saved with File|Save as into *.vsd diagram file, or into *.gif picture click click click click click click- click click click draw

(Varlist type is list of texts) • Format: format mask with wild-card charcters, eg. „00.00” • Value:default value • Prompt:caption • Lang.:language In our template,the-re are 4types of denoted arrows (see:Session3): • Yes():branch of a decision • No():branch • Step(): for-ward in time • Feedback():of cycles,it can go back in time MS Visio GUI 2 click right click • Rightclicking on elements, we can edit their properties. By default, each element has 3 properties: • Cost(Text):money requirement • Duration(Text):time requirement • Resources(Text):other requirements • As these are not very informative, in our template (see: BPDTemplate.vsd) we lea-ve them only at process start( ) and -end ( ) elements. At activities( ) and deci-sions( ) we add new custom properties with Define button: • UnitCost(Currency):of element • DurationSec(Number):time req., sec • EntityUsages(VarList):designing GUI of the system, later we have to assign entities from ERD to elements, based on their info needs and access rights recorded on DFD (see:Session3): • Create, Read, Update, Delete, Archive (CRUDA model) • PropertyUsages(VarList):it records access rights of the fuction band owner role to properties: • Create, Read, Update, Delete, Archive (CRUDA model) • Min/MaxFreqPerDay(Number):daily min/max freqency for cyclic activities • FormUsages(VarList):access to forms of GUI by CRUDA model • In Custom Properties panel we can set • Label: name of custom property, • Type: its type: Text, Number, Date, Currency, Varlist right click click click click click click click click click click click

click MS Visio GUI 3 click click click- click In case of ERD, user interface is more difficult: • At the right hand side, an empty diagram, on the left the element palette Entity Rela-tionship(Metric)) comes up, from where we can pull elements with mouse. • Right/Double clicking on elements, the property editor comes up. Text in elements cannot be edited directly, which is vasting of screen space and very annoying. • ERD can be sved with File|Save as meni into *.vsd diagram file or *.gif picture In ERDTemplate.vsd, we made lot of extra set-tings comparing to default diagram settings: • In Database|Options|Drivers menu, target database is set to MSSQL • In Database|Options|Document menu: • At Relations tab, we set signals shown at relations: • Relationships:show relation lines • Crow’s feet:show on many side • Referential action:show settings • At General tab,we give the ERDformat: • Relational:it shows field types • Conceptual names:logical names • At Table tab we set the table format: • Datatypes=Show physical:show physical data types on ERD The Visio ERD contains both logical model of database (entities, properties, logical property types), and physical model of target database (tables, fields, physical field types). However, it is better to use logical names on ERD (non-plural entity names), but with physical data types. pull click click click click click click click click click click click

MS Visio GUI 4 click- click Doubleclicking on an entities’ box, Database Properties panel comes out: • Definition: Physical (in plural) and Conceptual (logical) names of entity • Columns: property/field description: • Physical Name: field name • DataType:data type:bit,int,varchar(n), float(n,d),Datetime Set lenght of string fields always to 4,8,16,32 characters, and write identity after type of primary key to set its automatic numbering • Req’d: required fillup • PK: primary key, selecting more fields means compound(Összetett) key • Move Up/Down:change field sequence • Edit button:special settings of the field: • Definition|Default: default value, it can be a funcion also • Data Type:it can be User Defined Type(UDT)(Felhasználói adatípus) • Check:complex rule checking field • PrimaryID: formatting primary key • Indexes: indexing 1 or more fields together with Unique(Egyedi) or Non-unique(Ismét-lődő értékű)Index(Index): Pointer(Rekord-mutató) typed field attached to table. It is non-readable/writeable by user, but acce-lerates retrieving the records of a big table. Primary keys are unique indexed by defa-ult, and we usually set indexing on foreign keys. As indices(Indexek) slow down insert/ update/delete operations, we should use them only at frequently queried big tables • Triggers:code to auto-run at updating field click click click click click click click click click click click click click

MS Visio GUI 5 click- click Doubleclicking on crow’s legs, Database Properties panel comes up: • Definition: connect 2 fields from the 2 tab-les pulling the mouse, press Associate • Name:set logical/physical names of relation • Verb phrase: logical name of 1 side • InversePhrase:log.name of many side • There is a serious programming error in Visio here: if 2 tables are paralelly connected with 2 relations (eg.the Nap-TipusTort table describes time period-s, and its foreign keys KezdHetNap, VegHetNap are referencing to the Het-NapNevID primary key of table Het-NapNev), they receive identical logical names, which causes compiling error later. To avoid this, we have to modify their verb phrase manually:hashad • Miscellaneous:other settings: • Cardinality: at many side of relation, how much records should reference on 1 record at 1 side: • Zero or more:optional many side • One or more:required many side • Relationship type=non-identifying: foreign key of many side should not be a part of compound primary key of many side table • Referential action:referential integrity: • No action: locked, referenced record cannot be deleted in independent side, outlier cannot be added at dependent • Cascade:recursive del. of dependents • Do not enforce:checking inactivated click click click pull click click has/have been click click click click click- click

LegalForm LegalFormID FormName Week WeekID WeekNum StartDate EndDate QuarterID Day DayID DayName Date WeekID ShiftType ShiftTypeID ShiftTypeNam WageTariffID Shift ShiftID ShiftName StartTime EndTime DayID ShiftTypeID Modifier Modified Status Country CountryID CountryName Residence ResidenID StartDate EndDate PersonID AddressID ResTypeID Modifier Modified Status Quarter QuarterID StartDate EndDate StreetType StreetTypeID SteetTypeNam Region RegionID RegiNam CountryID Point PointID Latitude Longitude Height AddressID Modifier Modified Status Gender GenderID Gendername Person PersonID NaturalID FirstName MiddleName LastName MaidenNam BirthPlace MothersNam BirthDate GenderID CitizenshipID Modifier Modified Status ResType ResTypeID RsTypNam Address AddressID Door Floor HouseNum Street LinePhone Fax StreetTypeID ZipID CountryID Modifier Modified Status Zip ZipID City RegionID Access AccessID CellPhone E-mail Skype StartDate EndDate PersonID ResTypeID Modifier Modified Status Discount DiscntID Quantity NetVal GrossVal ItemID PromoID Modifier Modified Status VAT VATCode VATPercent Employee EmployeeID FiredCause StartDate EndDate PersonID OrganizatID Modifier Modified Status PartnerType PartnerTypID PartnTypNam Emloyment EmploymID FiredCause BaseSalary StartDate EndDate EmployeeID PositionID Modifier Modified Status WorkDiary WorkDiaryID Activity Fee StartDate EndDate EmploymID ShiftID PartnerID Modifier Modified Status Partner PartnerID FiredCause StartDate EndDate OrganizatID PersonID PartnerTypID Modifier Modified Status Item ItemID Quantity NetVal GrossVal InvoiceID ProductID Modifier Modified Status Position PositionID PosName DepartmID MeasUnit MeasUnit UnitName PosProdSales PosProdSalID SumOfSales PositionID ProductID Product ProductID Description ITJCode MeasUnit Pricing PricingID UnitPrice ProductID WeekID RegionID Modifier Modified Status Promotion PromoID DiscValue DiscType PricingID Modifier Modified Status Invoice InvoiceID InvoiceNum ItemCount NetTotalVal GrossTotal VATTotal Paid IssueDate IssueTime OrganizatID PartnerID PositionID Modifier Modified Status Organization OrganizatID TaxRegNum CompRegNo OrgName EstablDate LegalFormID LocationID MasterOrgID ITJ ITJCode Description VATCode Department DepartmID DeptName LeaderID MainDeptID Practice 4-1: Creating MDH-style ERD of invoicing in MS Visio To practice handling of MS Visio ERD, you have to build up the MDH-style database plan of invoicing system starting from the Visio ERD template (see: ERDTemplate.vsd) (2pts):

click Generating database from Visio ERD click The Enterprise version of Visio can generate a *.DDL(Data Definition Language) SQL-script (see: ERDTemplate.DDL) from ERD (see: ERDTemplate.vsd) in Database|Ge-nerate menu. The script can be run in MS SQL and it creates an empty database automatically, saving lot of coding work: • ERD is compiled, logically/physically valida-ted in the target database set at Database| Options|Drivers menu. Validation errors are listed in Output window: • Unfortunately, error messages usually have noting to do with real causes • With lot of experience, one can find out which error causes which message • If there are only Warnings, it will complete compilation, in case of Errors it will abort the process • Generate DDL/Generate new database: generate only script file or empty database • File name: DDL script file name • Installed Visio Driver=MS SQL: syntax of target database script file • Database:name of the new database • Review tables:list of tables to generate • View DDL script?=Yes: show DDL script in a text editor window Running the script in MS SQL it creates the empty database with tables and relations click click click click click click /* This SQL DDL script was /* Driver Used : Microsoft Document: D:\PTE-PMMFK\AdatBa SET QUOTED_IDENTIFIER ON go /* Create Proba1 database. use master go create database "Proba1" go use "Proba1" go

click Reengineering databases into ERD click Professional and Enterprise can Reverse Engineer(Visszafejt) design of an existing database (see:Stores.mdb) and add to our template (see: ERDTemplate.vsd) in the Database|Reverse Engineer menu: • Installed Visio Drivers=Access:give source database driver • Data Source=Access:source RDM • Filename=Stores.mdb: source RDS • User/Password: if the database has no password/username, leave them empty • Object types:objects of database to reverse engineer (select everything) • Select tables:select tables for reverse engineering • Review selection:summary about selec-ted tables and objects • Add shapes current page=Yes: add new entites to existing ERD • Press Finish button • The extended ERD: ERDReverse.vsd We should be aware of that Visio does not allow entity boxes to over- lap. Therefore, we should have e- nough empty space on ERD page. We can increase its size at File| Page setup menu. Recommended page size for bigger plans is A1 click click click click katt click click click click click click

Inviocing ERD in Visio /* This SQL DDL script was generated by Microsoft Visual Studio (Release Date: LOCAL BUILD). */ /* Driver Used : Microsoft Visual Studio - Microsoft SQL Server Driver. */ /* Document : D:\PTE-PMMFK\SqlDotNetConsultation\Session4\Invoicing.vsd. */ /* Time Created: 2008. October 28. 1:45. */ /* Operation : From Visio Generate Wizard. */ /* Connected data source : No connection. */ /* Connected server : No connection. */ /* Connected database : Not applicable. */ SET QUOTED_IDENTIFIER ON go /* Create Invoicing database. */ use master go create database "Invoicing" go use "Invoicing" go • We created ERD of Case study 3-1: Invoicing (see: Session3) with MS Visio (see: Invoicing.vsd) starting from a simplified, non-MDH ERD template (see: SimpleERDTemplate.vsd) • We generated the script Invoicing.DDL

References (--------------------------------Practice 4-1 should be performed here--------------------------------) CASE tools for relational database design: • Comparison of 240 CASE tools: http://www.cs.queensu.ca/Software-Engineering/tools.html • +1DataElements, a CASE tool which has some kind of automated normalization: http://www.plus-one.com/de_users_guide.html (no shareware) MS Visio manuals: • MS Visio home page: http://office.microsoft.com/en-us/visio/default.aspx

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