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

SQL .Net Consultation Lesson 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 Lesson Checking Home Assignment 3-1: Designing cyclic time definition storage Creating web-applications with relational database system 4 • Physical build of a database in MS SQL server • Installation, launching server process • 3 ways of creating new database • Fine tuning of table structures • Creating ERD and relations • Importing data, backing up database • Logical design of Graphic User Interface (GUI): • PaulerSoft™ Advanced Database Diagram • Inputs of GUI design • Understanding form sections • Designing data sources of form sections Home Assignment 4-1: Logical design of GUI References

Click Installation, launching server process Click Click Click MS SQL Server 2005 (trial downloadable: http://technet.microsoft.com/hu-hu/bb738031(en-us).aspx) is a database manager featured:  Client-server application enabling more ef-fective resource utilization than standalone MS Access, server shares work with clients • Has moderate resource requirement (250 MByte) compared to Oracle (1.5 GByte) • Has scaleable (Növelhető) performance ma-inly depending on amount of RAM available • Has more hard to use GUI than Access and does not contain built in GUI generator Installation is easy with install wizard: • Select SQL server, Install database, Local computer, Create new on 1st four screens • Give owner Name & Company next screen • Select Server&Client,Default datab., Typi-cal setup, Use same account+Use local account, Win authentic. on next 5 screens SQL Server runs in OS process (Folyamat) in the background. This way, its GUI can be shut down while doing high computation requirement queries to save resources and reopened when computations are ready. • Server process can be launched/ stopped with Start menu| Programs| SQL Server| Config tools| SQL Config manager: • Selecting SQL Server components in the object browser at the left side • Rightclicking SQL Server at right panel • Selecting Start/Stop from popup menu • Or, at Properties, select Service tab: • StartMode=Automatic:at OS boot • StartMode=Manual:user request Click Click Click Click Click Click Click Click Click Click Click Click Click Click Click Click right click Click Click

Click 3 ways of creating new database Click Click Click Once server process is running SQL Server Mana-gement Studio should be launched: • First it tries to connect to server, if it is successful: • There is Object Explorer on the left, showing object hierarchy of all existing databases on the server. There are system-installed databases: • Model: database template of new databases to create. Set formatting here if you have to create custom-format databases frequently • Default: an empty database • The right panel shows content of selected object • A new empty user database can be created by right-clicking Databases, select New database from Popup menu and set on the New panel: • Database name:should be unique on server. A DB can be stored physically in several data files with *.mdf extension, undo transaction log (Rendszernapló) is stored in *.ldb files • Their Initial size (MByte) and auto-growing rate and –limit can be set here. This is more advanced than Access, which stores every-thing in 1 file limited to 2.4GB by Windows. • Defining a database manually is taxig work. So, an alternative way is to create it from *.ddl script created by MS Visio Enterprise (see Session4): • At File|Open, open script(eg.:Invoicing.DDL) • Click New Query for an empty query panel • Copy all the code to query through clipboard • Click Execute to run query,which creates an empty DB with table structures given in ERD • Incured errors are shown at Output,if no one • New database is shown in object explorer • Moreover, MS SQL DB can be created directly from Visio Enterprise if server process is running right click Click Click Click Click Click

Fine tuning of table structures Click Modify Properties right click • Don’t forget to set initial data- and log file sizes and auto-growth even at the auto-created database with rightcli-cking Databases|Invoicing and se-lecting Properties from popup menu • There is often a need to change table structures from ERD with right click-ing TableName and select Modify: • Select FieldName and at Column-Properties tab set: • The most frequent change from auto-generated ERD is allowing Null values to enter in field, beca-use it turned out to be optional in the current application (cannot be set to primary- and referential integrity-enforced foreign keys) • Also Identity (automatic, read-only numbering) is often turned of temporarily at artificial primary keys if we need to enter existing values in the field manually. (Please note that column format changes are only commited if we use View|Refresh for the filed) • Computed column specs can be changed if there is a syntax incompatibility with given in Visio • Data type,Condensed data type can be changed to save space using DB on mobile tool (Please note that type of primary- and foreign keys cannot be changed, unless the relation is removed!) Click Click right click Click Click Open Modify Click Click Click

CountryID Click Creating ERD and relations drag drop shift click MS SQL Databases can also contain their ERD. It can be assembled right-clicking Database diagrams and selecting New database diagram: • At Add tables tab, select all tables and click Add • ERD appears on the panel. It auto-arranges tables, wasting lot of dia-gram space. We can help on that with manual rearranging tables and chan-ge zoom with View|Zoom. However, if a new table is added, it auto-arran-ges the whole ERD again, and tables can cover relation lines, so it is practi-cally useless for more complex ERD designs, like MDH(see Lesson3). So, design should be made in Visio • It is possible to create new relation on ERD dragging foreign key field and dropping it into primary key of the sa-me or other table (it can handle self-relation of tables without using alias) Relations can be edited manually in TableName|Keys|Rightclick|New: • Select relation from list of all 1:many and many:1 relations of table (it is sil-ly to list them all) • Click tables and colums specs • Select primary key table (1side) • Give foreign key field (at many side), click OK Click Click Click Click right click Click Click Click Click

right click Importing data, backing up database 1 Click Click Click Click Click • If database formatting is completed, master/ code tables (which do not change in time) should be filled by developer (transaction tables are normally filled from GUI later) • Importing is done by Import wizard from Invoicing|Rightclick|Tasks|Import data: • Select data source: Excel, Access (eg. Invoicing.mdb).Net, XML, Oracle, Plain files, other databases with ODBC, Web • Give Path+File, Username, Password • Select target server and database • Select source/target tables, click Edit: • Assign source fields(automatic,but can be overriden manually) • Assign destination fields(override) • Change type, size (Byte), precisi-on/scale (Összes/Tört számjegy) if necessary • Select append rows to table • Check enableidentity insert:iden-tity fields typically set at artificial primary keys are read only, which would prevent importing anything into them. With this setting, identity is temporarily relaxed at import. It was badly missing in Access • Execute immediately • As MS SQL database is set of numerous data and log files integrated into server ob-ject hierarchy, it cannot be copied/moved by files, as in Access. To backup DB into 1 file: • Invoicing|Rightclick|Tasks|Backup: • Give *.bak file name(eg.Invoicing.bak) • To restore: Rightclick|Tasks|Restore Click Click Click Click Click Click Click Click Click Click

Importing data, scripting database 2 Click Open Modify right click Besides importing, often it is necessary to manually edit smaller code/master tables. It can be done with TableName|Rightclick|Open, but with limitations: • Foreign keys cannot be presented as dropdown lists backed with lookup tables (Kinéző tábla) like in Ac-cess. This makes table editor useless, because even simple master tables can have foreign keys and if referential integrity of their relation is locked, they refuse values not are in referred primary key • Group of- or individual records cannot be filled copying through clipboard like in Access. Only individual field values can be copied at once • We cannot use subtables (Segédtábla) like in Acess to show under a record 1 side table relating records of many side table • But data changes has more exact logging and undo than in Access • GUI of SQL Manager is optimized for detailed set-ting of object hierarchy of a database, but ease and speed of handling is terrible. Therefore its worth to consider generating DB from Visio into an Access target DB first, using the same logical model, make there code table edits, then import it in- to MS SQL • Later we can see that coding of GUI in .Net is pretty slow compa- -red to Access, so it is worth to make a rapid prototyping of it in Access form designer (which is its best written component), linking tables back from MS SQL Server into Access (as da-tabase handling of Access is inferior) • Structure of an existing MS SQL DB can be written into an SQL script with Invoicing|Rightclick|Tasks| Generate scripts right click Click Click

Sequence of data import Buyer BuyerID FirstName LastName CellPhone E-mail AddressID Seller SellerID SellerName LegalFormat SellerTaxReg CellPhone E-mail URL AddressID Product BarCode Description UnitPrice VATCode ITJCode MeasUnit PersProdSales PersProdSalID SumOfSales SalesPersID BarCode ITJ ITJCode Description VATCode LegalFormat LegalFormat FormatName Zip Zip City Country SalesPers SalesPersID FirstName LastName CellPhone E-mail AddressID Country Country CntName VAT VATCode VATPercent StreetType StreetType TypeName Item ItemID Quantity NetVal GrossVal InvoiceID BarCode Modifier Modified Status Invoice InvoiceID InvoiceNum ItemCount NetTotalVal GrossTotal VATTotal Paid IssueDate IssueTime SellerID BuyerID SalesPersID Modifier Modified Status MeasUnit MeasUnit UnitName Address AddressID Door Floor Building HouseNum Street StreetType LinePhone Fax Zip Modifier Modified Status 9. 1. • As most of the relations in the database enforces referential integrity, foreign keys do not accept values not represen-ted in referenced primary keys • If primary key table is empty, referencing foreign key will not accept any value! • Therefore tables of database can be filled only in a logical sequence depar-ting from most independent tables (eg. code/master tables with no foreign keys) to most dependent tables (eg. transaction tables with many foreign keys) • The other soultion is temporary disabling of enforcing referential integrity of relations. However it is risky: if imported data contain outlier (Kilógó) records, referencing to non-existing primary key, integrity check cannot be set back! Practice 4-2: Handling MS SQL 2. 10. TASK: To practice joint handling of MS Visio and MS SQL: (2pts) • Restore backed up Invoicing database Invoicing.bak onto your MS SQL server • Add a prejoin connecting tables Addresses and Countries • Back up new version of the database • Reverse engineer it into your Visio Professional 3. 6. 13. 11. 8. 15. 5. 7. 4. 12. 14.

MainForm base entity Address AddressID Door Floor HouseNum StreetName Tel Fax StreetTypeID ZipID CountryID Modifier Modified Status Person PersonID NaturalID GivenName1 GivenName2 SurName MaidenName BirthPlace MotherName BirthDate Modifier Modified Status Residence ResidID StartDate EndDate PersonID AddressID Modifier Modified Status • ChkNewCust • FormName • NaturalID • TextBox • OptionButtons • NumberBox • CheckBox • Date • Time • PictureShow • SoundPlayer • MoviePlayer • ReqForgnKey • OptForgnKey • QueryButton • naturalID • GivenName1 • GivenName2 • VSurName • MaidenName • BirthPlace • MothersName • BirthDate • ResStartDate • ResEndDate • Door • Floor • HouseNum • Tel • StreetName • StreetType • Zip • Store data • Decision • Question? Example of form sections: 1Personcan residemanyAddress (in time),and 1Addresscan be resided by manyPersons (Resi-dence is relation entity) • Lets assume we have a check new customer ac-tivity on BPD we want to support with database at CheckNewCust form. • Its content 1:1 connec-ted to Person, there fore it will be the base entity (Bázisegyed) data sour-ce of main form section goes until BirthDate • Person is 1:több related to Residence, so data of the latter cannot go on the same form section, but it will be base entity of subform (Segédűrlap) • Residence is many:1 related to Address, so data of latter can be joi-ned to subform section, being its joined entity (Csatolt egyed) • As data source of 1form section can be 1 table/ query, Address and Re-sidence are queried into 1view(Nézet) datasource Elements of GUI diagram: Form(Űrlap) block: • Form name at top in blue • Followed by various type of data controls(Adatmeg-jelenítő kontroll) with their caption and cotrol type icon. All of them are linked to a specific data type ex-cept the NumberBox, which can show both inte-ger and fraction number • Boldface controls auto-filled, normals are requ-ired, italics are optional • Underlining means form section break(Űrlap szek-ció törés): different form sections have different table or query data source (see example later) • Form may contain buttons launching query/other form Popup Form (Felugró űrlap): • It contains yes/no question and belongs to a conditi-onal block pair on BPD IF:New Customer? Logical design of GUI in PaulerSoft™ Advanced Database Diagram SubForm base entity SubForm joined entity Sql:Select StartDate, EndDate, Door, Floor, … From ( Residence R Inner Join Address A On R.AddressID= A. AddressID); Data Source (Adatforrás) connector: bidirectional data connection of a form section and a table/SQL query: • Cannot Create record:( )/can:( ) • Cannot Read record:( )/can:( ) • Cannot Update record:( )/can:( ) • Cannot Delete record:( )/can:( ) SubForm datasource SQL query

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)) • Invoice • SellerID • SellerName • LegalFormat • SellerTaxReg • SellAddressID • HouseNum • Street • StreetType . • Zip • City . • BuyerID • FirstName • LastName . • BuyAddressID • HouseNum • Street • StreetType . • Zip • City . • InvoiceNum • ItemCount • NetTotalVal • GrossTotVal • VATTotal • IssueDate • IssueTime • SalesPersID • Paid . • BarCode • Description • UnitPrice • MeasUnit • ITJCode . • VATPercent . • Quantity • NetVal • GrossVal • Store data 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 Inputs of GUI design There are 3 inputs of logical design of GUI: • Data flow diagram (DFD) after detailed Business Process Reengineering (BPR) which corrected process errors and weeded data flood trap from Empirical Data Structures (EPS) (see Session3) • ERD as a result of normalization and denormaliza-tion, preferably in MDH structure (see Lesson3) • Layout design of paper-based forms of the old DPS if there is any. Simple users welcome to see the same structure on screen they get used to on paper. Hovewer be careful about there can be serious design errors in paper-based forms: • Missing fields because of lack of space (eg. Sales person’s name, Paid signal) • Inconsequent sequence of fields, because they could not change it on paper once it was typed: fields does not comply logical order of their cross-validation (see Lesson2) and should be regrouped into several panels • Un-normalized fields (eg. Address in one string) which should be broken up • Therefore original form layout should be reconside-red, many important missing fields can be added after comparing EPS and normalized entites at ERD • Rules of new GUI layout design are in References

Invoice • SellerID • SellerName • LegalFormat • SellerTaxReg • SellAddressID • HouseNum • Street • StreetType . • Zip • City . • BuyerID • FirstName • LastName . • BuyAddressID • HouseNum • Street • StreetType . • Zip • City . • InvoiceNum • ItemCount • NetTotalVal • GrossTotVal • VATTotal • IssueDate • IssueTime • SalesPersID • Paid . • BarCode • Description • UnitPrice • MeasUnit • ITJCode . • VATPercent . • Quantity • NetVal • GrossVal • Store data Understanding form sections Main Form Main Form • In most form generator systems (eg. Access, Oracle forms), 1 form page in columnar (Rovatos) format (eg. when data controls are laid out in echelon to fill screen space with maximal effi-cience, and show many fields in the same time) can contain data of only 1 re-cord of its base data source table/ query • Therefore, if data sorce table/query of a form section is many:1 related with base data sorce table/query of the form, it cannot go on the main form, as it has more records to be listed at one record of the main form (eg. manyItems should be shown as content of 1Invoice) • Hence, it will be placed on a subform (Segédűrlap) enbedded into main form. It has usually tabular (Tábláza-tos) format, which shows large num-ber of records, but only limited num-ber of fields in the same time • Subforms can be nested into each other in multiple level, if they has data sources connected with chain of 1: many relationships • Data of tables 1:many related with base table of the form can be but on the main form, as they have only 1 record belonging to 1 record on the main form, thats why they are called join tables. Subforms also can have join table data 1:many related to their base table/query Sub Form Main Form Sub Form

Buyer BuyerID FirstName LastName CellPhone E-mail AddressID Country Country CntName Invoice InvoiceID InvoiceNum ItemCount NetTotalVal GrossTotal VATTotal Paid IssueDate IssueTime SellerID BuyerID SalesPersID Modifier Modified Status Item ItemID Quantity NetVal GrossVal InvoiceID BarCode Modifier Modified Status StreetType StreetType TypeName VAT VATCode VATPercent MeasUnit MeasUnit UnitName Address AddressID Door Floor Building HouseNum Street StreetType LinePhone Fax Zip Modifier Modified Status SalesPers SalesPersID FirstName LastName CellPhone E-mail AddressID LegalFormat LegalFormat FormatName ITJ ITJCode Description VATCode PersProdSales PersProdSalID SumOfSales SalesPersID BarCode Product BarCode Description UnitPrice VATCode ITJCode MeasUnit Seller SellerID SellerName LegalFormat SellerTaxReg CellPhone E-mail URL AddressID Zip Zip City Country • Invoice • SellerID • SellerName • LegalFormat • SellerTaxReg • SellAddressID • HouseNum • Street • StreetType . • Zip • City . • BuyerID • FirstName • LastName . • BuyAddressID • HouseNum • Street • StreetType . • Zip • City . • InvoiceNum • ItemCount • NetTotalVal • GrossTotVal • VATTotal • IssueDate • IssueTime • SalesPersID • Paid . • BarCode • Description • UnitPrice • MeasUnit • ITJCode . • VATPercent . • Quantity • NetVal • GrossVal • Store data Designing data sources of form sections 1 Sql:Select I.InvoiceNum, I.IssueDate, I.IssueTime, i.SalesPersID, I.Paid, S.SellerID, S.SellerName, S.LegalFormat S.SellerTaxReg … From ( Invoices I Inner Join Sellers S On I.SellerID= S.SellerID); SQL queries of connecting base- and joined tables with inner joins (Belső csatolás) and they are not stored permanent-ly in memory • View tables bridge the huge structural difference bet-ween the GUI (how user can overview the data easily) and norma-lized storage de-sign (how RDM can store data effectively) • As their Select part usually con-tains simple refe-rences of fields of participating tables and does not com-pute with them, they can perform bidirectional (Két-irányú) data flow between forms and database tab-les forth and back: Main Form MainForm joined entity MainForm base entity • As main- and subform sections usually collects data from multiple joined tables besides its base table, their data sources are usually view tables (Nézet táblák): they are described by MainForm datasource SQL query SubForm datasource SQL query Sub Form Sql:Select It.BarCode, P.Description, P.UnitPrice, P.MeasUnit, P.ITJCode, It.Quantity … From ( Items It Inner Join Product P On It.BarCode= P.BarCode); SubForm joined entity SubForm base entity

Designing data sources of form sections 2 • They stream data from database tables to data controls of the form • At user input, in reversed mode, they return changed data back to database tables. One of the most serious disadvantages of .Net 2.0 is that reverse mode is not working. A .Net webform can present data from view table data source, but cannot put back changes automatically in multiple source tables (it can do it only with single source table) which means taxing manual coding!!! Another question is regulating form owner role’s (eg. Salesperson, Customer, Manager) acces rights (Create, Read, Update, Delete, Archive) to fields of dta source tables. There are 2 strategies: • Database-level regulation: view table fields and form data controls in most systems inherit user acces settings of dtabase tables and fields. This way regualtion is fast but cannot be customized form by form (eg. manager on managerial screen can do more than salesperson on his screen) • Form-level regulation: access is defined at form controls: this is more felxible, but takes longer time to code • The input source of access rights design is CRUDA settings of fields in EDS of DFD (see Session3). The agg- regate design tool is called transaction matrix (Tranzakciós mátrix): • There are 2 embedded level of rows: Entites > Attributes • There are 3 embedded level of colums: The owner role > Its forms > CRUDA rights • Cells of the matrix have binary values: 1:allowed, 0:forbidden, Null: non applicable • Life cycle rule of entites/ attributes (Az egyedek/ tulajdonságok életciklus sza- bálya) states that in all rows of the matrix there should be at list one C, R and D right, therefore we introduce at the right edge of matrix checksum columns

Home Assignment 4-1: Logical design of GUI TASK: Design the following GUIs of the MDH Invoicing database ERD described at Session4/Practice 4-1 using ADD Forms diagram symbols! Be especially careful about: • Correctly determining form sections, • Assign their data source tables/queries, • Assign Create/ Read/ Update/ Delete rights to them, Considering what the owner role can do in at a company realistically! (3pts) STUDENT1: Recording actual residence of a buyer (by a sales person (Eladó) STUDENT2: Managing the organization and its departments (by CEO (Vezérigazgató) STUDENT3: Set pricing of products in certain weeks and regions (by sales representative (Ügynök) STUDENT4: Record what type of shifts will be made on a given day (by foreman (Művezető) STUDENT5:Record geocoding result of and address in given zip area of given region and country (by geodet (Földmérő) STUDENT6:Assign an existing employee to a given position of a epartment of an organization (by HR representative (Személyzeti előadó) STUDENT7: Record the work diary in a given shift working in a given employment (by employee) STUDENT8: Change VAT, ITJ, or measure unit of an existing product (by accountant (Könyvelő) STUDENT9: Get a report of sales of a sales person in a given product category (sales department leader) STUDENT10: set promotion for a given product pricing (marketing manager)

References MS SQL Tutorials: • http://www.functionx.com/sqlserver/ • http://www.intermedia.net/support/SQL/sqltut.asp • http://www.tutorialized.com/tutorials/MsSQL/1 Theory of GUI design: • http://www.iie.org.mx/Monitor/v01n03/ar_ihc2.htm • http://toastytech.com/guis/uirant.html • http://www.sju.edu/~jhodgson/gui/guihome.html

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