1 / 17

Data Mining&Business Planning of Engineering/Research Projects

Data Mining&Business Planning of Engineering/Research Projects. Practice 5 Dr. Gá bor Pauler , Associate Professor Faculty of Sciences, University of Pécs Tel:30/9015-488 E-mail: pauler@ t-online.hu. Checking Home Assignment 4: Paper-based questionnaire Creating Forms in MS Access

feo
Download Presentation

Data Mining&Business Planning of Engineering/Research Projects

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Data Mining&Business Planning of Engineering/Research Projects Practice 5 Dr. Gábor Pauler, Associate Professor Faculty of Sciences, University of Pécs Tel:30/9015-488 E-mail:pauler@t-online.hu

  2. Checking Home Assignment 4: Paper-based questionnaire Creating Forms in MS Access Create and Format Tables Create Relations on Relations Diagram Create Form Data Source Queries Create Main- and SubForms Create HTML Pages Create SwitchBoard Form, Create Application Test Application Transfer survey data to SPSS: Export Query Database wizard Introduction Data View Variable View Menus Home Assignment 5: Design query database References Content of the Practice

  3. Creating Forms: Create and Format Tables • You can practice with MyPregnacy.mdb containing raw data- base tables of Pregnacy.mdb • STEP1:Create data- base:with File|New|Empty database (Fájl|Új|Üres adatbázis) menu • STEP2:Create master tables: as these will be referenced by transaction tab-les, we create them first with Tables| Create table in design view(Táblák| Tábla létrehozása tervező nézetben) • Set FieldNames(Mezőnév) and DataTypes(Adattípus). Primary keys type should be Autonumber(Számláló) • Click primary key field and assign:( ) • Save table structure with Ctrl+S and click ( ) to write in data content • Data content is written immediately to hard drive, you don’t have to save it! • You can make table layout more com-pact with Format|Column width|Best width(Formátum|Oszlopszélesség| Legjobb illeszkedés) menu • Save table layout with Ctrl+S • STEP3:Create transaction tables: this is the same, except at foreign keys: they can contain values only which are in referenced primary key field, so: • Click field and Appearence(Megjele-nítés)tab: set Control(Vezérlőelem) to Combined list(Kombinált lista) for selecting primary key table values Click Click Click Click Click Click Click Click Click Click Ctrl Ctrl S S Click Click Click Click Click Click Click Click Click • Rowsource (Sorfor-rás) is set to referenced primary key table • Tied column(Kötött oszlop) is set 1, as usu-ally primary key field is the first in tables Click Click Click Click Click Click

  4. Creating Forms: Create Relations • Number of columns(Oszlopszám) is set to 2 or 3, as besides numeric primary key values, combined list should show some fields with their meaning/description/explanation • Set List element only(Csak lista-elem) this way combined list will accept only valid primary key values • If a field has pre-set values, but they do not occour at other fields (Eg. Status={Normal,Preterm}), it is not worth to make separate lookup table for them in the database. In this case set Row source type(Sorforrás tí-pusa) to List(Lista) and in Row so-urce(Sorforrás) list values manually, selected by „,”(„;” in Hungarian) • STEP 4:Create relations: it is good to create ERD in Access also, this way relations are auto-merged in queries • Click ( ) to get an empty ERD • Add tables(Tábla hozzáadás) with Clicking ( ), select all tables with Shift+Click, press Add(Hozzáadás) • Pull foreign key fields into referenced primary key field:Relationship editor (Kapcsolatszerkesztő) comes up: • Set Enforce referential integrity (Hivatkozási integritás megőrzése) this ensures that user cannot: 1-write non-existing primary key value in foreign key 2-delete primary key record referenced by existing foreign key records creating bad relations Click Click Click Click Click Click Click Click Click Click Click Click Click Click Click Click Shift Click Click Pull Pull Pull Click S Ctrl Click Click OK to create Save relations with Ctrl+S

  5. Creating Forms: Create Data Source Query and Main Form • STEP 5: Create data source views/queries: Womans, Pregnacies, Fathers are queried together as data source of the main form • Queries|Create query in design view(Le-kérdezések|Létrehoz tervező nézetben) • Add tables(Táblák hozzáadása)comes up, select the tables above with Shift+Click, press Add(Hozzáadás): tables will be auto-Joined(Csatol) by their relations • Pull down all different fields of them into Fi-eld(Mező)row of edit grid, save query with Ctrl+S named: PregnacyFormBaseQuery • STEP 6: Create PregnacyForm main form: Form|Create form with wizard(Űrlap| Lét-rehozás varázslóval) launches wizard: • Select PregnacyFormBaseQuary as data source,and all columns with( ),press Next • The wizard detects that query uses more tables and asks to select base table of Ap-pear data(Adatmegjelenítés)=Pregnacies and select Simple form(Egyszerű űrlap) • Pressing Next it asks Form structure (Űrlap szerkezet)=Columnar(Oszlopos) • Pressing Next it asks graphic Style(Stílus) =Standard(Szabványos), press Next, set Form name(Űlap címe)=PregnacyForm • After Finish wizard, form will run. Press( ) to shift into design view. At design screen: • Pull down Form footer(Űrlap láb) to make empty space for subform created later • Pull Data controls(Adatmegjelenítő) and their Title textboxes(Cím szövegdoboz) into more compact layout • Save form with Ctrl+S Click Click Shift Click Click Pull S Ctrl Click Click Click Click Click Click S Ctrl Click Click Pull Click Pull

  6. Creating Forms: Create SubForm Click Click • STEP 6: Creating BabySubForm subform: At form designer: View|Toolbars|Controls (Nézet|Eszköztárak|Vezérlők) menu • With Subform(Segédűrlap)( )control, Pull out a rectangle on the empty space left, it will launch Subform wizard(Varázsló): • Set Use exsting table as source(Meglévő adatforrás tábla használata), press Next • Select Babies and all fields with ( ), Next • Select Join field(Csatolómező) between data sources of main- and subform From list (Választás a listából)=PregnacyID (a foreign key field in Babies), press Next • Name the subform BabySubForm, Next • Subform content appears in the control. To make it looking like tabular list-layout: • PullForm header(Űrlapfej) 1 row high • Click on title texboxes and cut them:Ctrl+X • Paste them to form header with Ctrl+V • Pull them after each other in field sequence • Pull data controls on Form body (Űrlap-törzs) after each other in same sequence • Pull upward Form footer(Űrlapláb) to make form body 1 row height • DoubleClick on subform selector ( ) to show Form properties(Tulajdonságok) panel. At Format(Formátum) tab, set De-fault View(Alapértelmezett nézet)=Conti-nous(Folytonos): this forces subbform to show multiple records on multiple 1-row high pages appearing like a table. This trick is necessary because real tabular form in Access cannot handle combined lists for foreign keys, so that is practically useless! Click Click Pull V Ctrl Pull X Ctrl Pull Click Click Pull Pull Click Click Click Click Click Click

  7. Checking Home Assignment 4: Paper-based questionnaire Creating Forms in MS Access Create and Format Tables Create Relations on Relations Diagram Create Form Data Source Queries Create Main- and SubForms Create HTML Pages Create SwitchBoard Form, Create Application Test Application Transfer survey data to SPSS: Export Query Database wizard Introduction Data View Variable View Menus Home Assignment 5: Design query database References Content of the Practice

  8. Creating Forms: Create HTML Page • DoubleClick main form selector( ) to open Properties(Tulajdonságok) panel, select Data(Adat) tab and set user access rights: • Allow delete(Törlés engedélyez)=No • Allow insert(Beszúrás enged)=Yes • Allow record(Adatbevitel enged)=Yes • Save form with Ctrl+S • STEP 7: Create HTML Page: as Access forms work only desktop, you can create HTML page(Adatelérési lap) enabling users to reach database from their web-browser with File|Save as (Fájl|Mentés másként). Give Form name(Űrlapnév)= PregnacyForm and Object type (Objek-tum típus)=HTML Page(Adatelérési lap) • As the HTML page will search for the data-base *.MDB file in absolute path (Abszolút elérési út), make sure that it is in its final dictionary in the web-server • You can use any web-server software, eg. http://httpd.apache.org/ freeware • Respondents can open saved Pregnacy-Form.HTM with their web-browser.  This is the most primitive, but fastest way to cre-ate web GUI  Unfortunately, MS Access webform control works only with Internet Explorer, and usage of subforms are bloc-ked, which kills effective webGUI-handling. This bullshit prevents it competing with .Net • So, the real proficient solutions are: • Open source:MySQL database + PHP • MS: MSSQL + .Net ASPX forms • Oracle: Oracle DB + Oracle Webforms Click Click Click S Ctrl Click Click Click

  9. Creating Forms: Create Switchboard Form • STEP 8: Create SwitchBoard forms: Access forms can contain only 255 controls, while on the questionnaire you can have much more elements, so you have to break up even a simple but lengthy questionnaire into part-forms. This makes necessary to create a SwitchBoard (Kapcsolótábla) Form to control user access and navigation among forms: it can contain only Password entry (Jelszavas beléptetés), PushButton(Nyomógomb) cont-rols and graphics, but no data entry controls: • Tools|Database utilities|SwitchBoard ma-nager(Eszközök|Adatbázis segédeszközök |Kapcsolótábla-kezelő) launches wizard: • Would you like to create new(Szeretne újat létrehozni)=Yes, press Edit(Szerkeszt) • Give its Name(Név)=SwitchBoard, press New(Új) item to show new item panel: • Set Text(Szöveg)=Pregnacies • Set Command(Parancs)=Form in edit mode(Űrlap szerkesztés módban) • Set Form(Űrlap)=PregnacyForm • Don’t forget to add an Exit item from applica-tion, otherwise user cannot exit • Switchboard will auto-create a new form and a new database table in database. You should not change these manually (except form graphics/fonts/layout which you can), otherwise switchboard will not work! Click Click Click Click Click Click Click Click Click Click

  10. Creating Forms: Create Application • STEP 9:Create user-proof desktop applicati-on: even if you use database only for desktop data recording, you should not allow inexperi-enced Nusi data recorder ever directly ac-cess database tables, only through forms: • At database window Tools|Startup (Eszkö-zök|Indítás) menu show settings panel: • Title of Application(Az alkalmazás címe)= Pregnacy database • Show form (Űrlap megjelenítése)= Switch-Board: which form should come up at run • Show database window (Adatbázis ablak): if this is allowed user can reach tables, and all ather database components directly, so in final test version, it should be locked • Allow hotkeys (Speciális billentyűk hasz-nálata): if this is allowed, user can still reach database components if he/ she presses Shift when launching the application • Therefore, in final user version,this shouldn’t be allowed, however keep a separate deve-loper copy, so you still can access database window! Click Click Click Click Click Enter Shift Enter

  11. Creating Forms: Testing with sample data • STEP 10: Test application: you should record data of 5-8test questionnaires you made earlier as a final test before publishing the system on web. You can observe: • How data source queries of forms working as bidirectional data link (↔) are generating new records( ) with automatically numbered primary keys fields (this is because using AutoNumber field type) • How foreign keys connecting new records with relations are also auto-generated • How data field values are writ-ten and retrieved between tables and forms Orsós Dzsenifer Talán a Pista 2010.11.15 1986.04.25 1991.06.06 2011.06.25 Szűcs doki 2005.11.30 Orsós Brian 5 Preterm Click Click

  12. Checking Home Assignment 4: Paper-based questionnaire Creating Forms in MS Access Create and Format Tables Create Relations on Relations Diagram Create Form Data Source Queries Create Main- and SubForms Create HTML Pages Create SwitchBoard Form, Create Application Test Application Transfer survey data to SPSS: Export Query Database wizard Introduction Data View Variable View Menus Home Assignment 5: Design query database References Content of the Practice

  13. Transfer survey data to SPSS: Export Query, Database wizard • STEP 11: Create export query: as cheaper statistical software can handle usually single data table in the analysis, you have to query all data of questionnaire together into single big table: • Queries|Create query in design view(Le-kérdezések|Létrehoz tervező nézetben) • Add tables(Táblák hozzáadása)comes up, select all data tables with Shift+Click,press Add(Hozzáadás): tables will be auto-Joined (Csatol) by their relations • Pull down all different fields of them into Fi-eld(Mező)row of edit grid • Save query as PregnacyExport withCtrl+S • STEP 12: Import data to SPSS statistical software: launch database wizard with File| Open database|New query menu: • Select Data Source = MS Access, Next • Give path and name of *.MDB file, Next • Select PregnacyExport from table/query list and Pull it in Retrive Fields panel, Next • Next screen you can build filter, skip it:Next • Then you see a summary about field names and types. If you have a non-numeric coded data field in database, you can auto-convert it to numeric coded with Recode to Nume-ric (Eg. Status = {1:Normal, 2:Preterm}) • In final screen you see the SQL code of im-port query and can Finish wizard Click Click Shift Click Click Pull S Ctrl Click Click Click Pull Click Click Click

  14. Transfer survey data to SPSS: Introduction • SPSS (Statistical Package for Sociometric Surveys) is a statistical software frequently used in analyzing survey databases. It can be downloaded from http://www.spss.com as 15 days shareware (450MB ). Evaluation: •  It is only the 3rd best statistical software behind Statistica (http://www.statsoft.com) and SAS (http://www.sas.com), •  However this is the most easily reachable as the shareware demo and the full version has the same setupfiles just differing in the setup password, which can be easily broken, and wide range of unlimited cracks are available at torrent sites (It is suspicious that this is an intensional strategy of SPSS to hold the market in competition with an inferior product). Statistica and SAS have much more complicated modul-wise licensisng •  It can handle almost as wide range of analysis tools as competitors •  It is fully programmable with its own script language. Whatever you do manually in SPSS it is written as script in C:/Windows/Temp/SPSS.JNL text file, where you can easily study how to automate your manual work •  Inferior quality is mainly connected with the multi-dimensional graphics/diagrammming and reporting capacity (there are modules where important diagrams appearing as caracter based-graphics written in 1979) •  In every 12-18 months, a new version appears from SPSS, however these have only minor differences, and basic shortocomings are not repaired. This is more looking innovative than really being innovative! • You can save imported data with Ctrl+S in *.SAV format: •  This is a very compact binary file with less relative space consumption than in any database table (Eg. MS Access) •  But it can contain only 1 database table, which has 2 views:

  15. Transfer survey data to SPSS: Data- and Variable Views • Data View(Adattábla nézet): Columns of a table are fields/questions of the questionnaire, called Variables(Változó) here, rows are receords/filled questionnaires called Cases/Observations(Megfigyelés). Cells(Cella) contain values of responses or missing value(Hiányzó érték) denoted with „.” char: • Variable View(Változó nézet): List of all properties of variables: • Name: of variable: 8±3 char, should not start with number, contain Accented (Ékezetes) or special chars (except:”_”), names should be unique, meaningful english abbreviations, (Eg. NOT V1, V2, etc.) Questionnaire sections should start with same name prefix (Eg. attsafet, attdecor) • Type: of variable: • Numeric:numbers • Text:string of charcters • Date:date as DD-MM-YY • Width/Decimal:digits/fraction • Measure:scale type of variable • Nominal:nominal scale, • Ordinal:ordinal scale, • Scale:interval/ratio scale • Label:description of variables in 16-32 chars. It can contain any characters and it will show up in results • Values:value labels if values are codes (Eg. 1:Male, 2:Female) • Missing:values: besides system missing, we can give special missing codes • Columns:graphical width in table(characters), not influencing data content • Align:ment of column: Left/Center/Rigth • Without setting all these correctly, the SPSS file is practically useless!!

  16. Home Assignment 5: Design query database • Design and create a database in MS Access/ MSSQL /MySQL to store your survey data! (1 point) • Design forms (in MS Access, PHP, .Net, Java, Oracle Forms, etc.) looking graphically similar to your paper-based questionnaire designed in Home Assignment 4! Test forms with recording data content of 5-8 test questionnaires! (1 point) • Transfer test data from database to SPSS and format SPSS file correctly (variable names, tpyes, labels, values labels, missing values, scale types)! (1 point) • Sample Solution: CarSculpturersDatabase.mdb, CarSculpturersData.sav

  17. References • Electronic form generators: • PHP: http://www.php.net/ freeware • ASP .Net: http://www.asp.net/ 180 days shareware • Oracle Designer: http://www.oracle.com/technology/products/designer/index.html no demo • Web-server software: • Apache: http://httpd.apache.org/ freeware • Statistical sofware (in order of their quality rank): • 1. Statistica: http://www.statsoft.com/ 30 days limited shareware • 2. SAS: http://www.sas.com/ no demo • 3. SPSS: http://www.spss.com/ 14 days shareware

More Related