PHASE III: Analysis, Design and Implementation of the
Contents
1. 904 London 1908 St 82 Berlin 1936 L 64 Mexico City 196 goul 1988 Barcelon 900 St Louis 190 Angeles 1934 1972 Montreal 1974 ta 1996 Sydney 20 908 Stockholm 19 don 1948 He OLYMPICHRONICLES PHASE TII Analysis Design and Implementation of the OlympiChronicles DB System OLYMPICHRONICLES pBoss COMPANY Craig Shapiro Steffanie Orellana LL TA BEBOP CONTENTS Ee ees deeg sd udse ed ok dee se od do ke se So ee ee ee de 2 2 ENVIRONMENT AND REQUIREMENT ANALYSIS esse ssssssss sees see ses 4 2 1 Purpose of Documenta ee IS Ge Re ee Ge GE ke 4 2 2 Purpose of Project ie eg ee AE de oe ee GE AG ee Ge 4 DES SCOPE EE RE OE OE EE RE OE 5 2 4 Assumptions RE RE N EE EE ed 5 2 5 Technical and Conceptual Problems and Solutions sesse sesse ee see 5 2 5 1 Technical Problems and Solutions eeeeneoooonaooooonese ae 5 2 5 2 Conceptual Problems and Solutions erereeennoooonesooene 6 3 SYSTEM ANALYSIS AND SPECIFICATION eeoseooseoosoososusesusesusenesene0e 7 3 1 Description ot Proced r ede De se Ge N GN De SA Ge Ge saed 7 3 1 1 From the User s Perspective sessies esse ke Ke Sk Se NR N de ds eke 7 3 1 2 From the Developer s Perspective uses sesse sesse ee Gee Ge ena 8 3 ETE Dres LE EE 9 3 14 Web Server Procedures idad 10 3 2 A O ON 11 3 2 1 Top Level Flow Dia grains iniciar 11 3 2 2 Tasks Subtasks and Task Forms oooccccccncnnnononccnnnnonononoos2. i if 2 0 result lt td gt lt div align center gt amp nbsp lt div gt lt td gt lt tr gt j 73 catch Exception ee S MPCData java Continued if 1 2 0 result lt td gt lt div align center gt amp nbsp lt div gt lt td gt lt tr gt catch Exception ee System out println Error populating Non participating countries MPCData ee toString Mm return 74 MPCData java Continued try t Statement s db createStatement ResultSet rset s executeQuery SELECT Sub country Sub5 countryname GoldTotal SilverTotal BronzeTotal MedCount as Total FROM SELECT country count MedalCount MedCount FROM SELECT country count medal MedalCount FROM individualwins IW olympicsite OS WHERE IW year OS year and OS year year 76 CEHData java package SQLUtilities import java sql import java util public class SEHData implements java io Serializable String discipline String event private Connection db null String year String subsport String newevent String gender String medal String country String athlete int currentRow int rowCount List yearList List subsportList List neweventList List genderList List medalList List countryList List athleteList public SEHData discipline win wi gt event dbConnect setYear setSubspor
3. 36 6 1 6 Generate Result Page Task Generate Results Page HTML for webpage layout HTML for OlympiChronicles logo HTML for header name of query general for any query result Generate table get results table display results table If click logo true link to GQSP Query Select Page 37 6 1 7 Create Result Form Task 38 Create Result From Results table create table 2 columns Results tableadd columns Year Site If result Sport Event Result result Sport Event History Result results table add columns Sport Subsport Event Subevent Medals populate with results from OlympicsDB Else if result Country Participation History Result results table add columns Country Abbreviation Country First Year Country Participated Total Number Games populate with results from OlympicsDB Else if result Medal Count Result results table add columns Country Total Number of Medal populate with results from OlympicsDB Else if result Medal Country Result results table add columns Country Gold Medals Silver Medals Bronze Medals populate with results from OlympicsDB Else if result Top Medal Athletes Result results table add columns Team or Athlete Name Medals populate with results from OlympicsDB Else if result Top Medal Country Result results table add columns Co
4. lt div align left gt lt div gt lt div align left gt lt p gt lt br gt lt n gt QUERYMpC JSP Medals per Count Query Page lt jsp useBean id MPCData class SQLUtilities MPCData scopes session gt lt jsp setProperty name MPCData property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt page import SQLUtilities Utilities gt lt page errorPage myError jsp from Query jsp gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt Temp lt title gt automatically generated code lt p gt Please select the options for your query lt p gt lt form action QueryResultMpC jsp method post name form1 gt lt select name year onChange MM_showHideLayers Layer16 show gt lt option value 0 selected gt Select Olympic Year lt option gt lt Utilities getYears gt lt select gt lt div id Layer16 style position absolute left 8px top 116px width 485px height 9 1px z index 2 visibility hidden gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt input type submit names Submit value Submit gt lt input type reset value Reset gt lt div gt lt form gt lt div gt lt div gt l
5. amp nbsp lt p gt lt p gt amp nbsp lt p gt lt 56 if luser isCbValid 0 QUERYFA JSP Flags Anthems Query Page lt jsp useBean id user class SQLUtilities UserData scope session gt lt jsp setProperty name user property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt page import SQLUtilities Utilities gt lt page errorPage myError jsp from QueryFA jsp gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt OlympiChronicles Ouery FLAGS and ANTHEMS lt title gt automatically generated code lt p gt Please select the options for your query lt p gt lt form action QueryResultFA jsp method post name form1 gt lt select name select onChange MM_showHideLayers Layer16 show gt lt option value 0 selected gt Select Country lt option gt lt Utilities getCountries gt lt select gt lt p gt lt input type submit names Submit values Submit gt lt input type reset value Reset gt lt p gt lt div gt lt form gt lt div gt lt div gt lt p gt lt br gt 57 The code presented in this section is half of the code created for the web interface For each of the guery pages presented here there is a corresponding results page that shows the results of the guery
6. style position absolute left 300px top 186px width 500px height 397px b order 1px solid 4999999 z index 1 visibility visible gt lt div align center gt lt p gt amp nbsp lt p gt lt p gt Please select the options for your query lt p gt lt form name myForm action QueryResultSEH jsp method post gt lt p gt Discipline amp nbsp lt select name discipline size 1 onChange updateMenus this MM_showHideLayers Layer16 s how gt lt option gt Par E 49 QUERYCPH JSP Country Participation History Query Page lt jsp useBean id CPHData class SOLUtilities CPHData scopes session gt lt jsp setProperty name CPHData property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt page import SQLUtilities Utilities gt lt page errorPage myError jsp from QueryCPH jsp gt 50 lt html gt lt head gt QUERYMC JSP Medal Count Query Page lt jsp useBean id user class SQLUtilities UserData scope session gt lt jsp setProperty name user property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN 51 http www w3 org TR html4 loose dtd gt lt WD nase imnort SOL Utilities Utilities gt 52 SE SE SE SE SE SE SE SES SESE OG OG ob SESE Se SE SE SE or or oe oF function createMenus for var i 0 1 lt info length i m
7. Critical N A Web queries Index of queried results Web based search engines Researching and bookmarking web sites and or pages with summer Olympic game data None None 3 2 2 2 ETL Task TASK NUMBER ETLT TASK NAME Extract Transform and Load Task PERFORMER Kapow RoboSuite 5 5 PURPOSE To extract data transform or reformat it and load it into the OlympicsDB ENABLING COND The creation of the OlympicsDB and any addition of data or updates to the OlympicsDB DESCRIPTION This tool Kapow RoboSuite 5 5 extracts specific data from a web page and load it into a predefined data relation or table FREQUENCY Once for the creation of the OlympicsDB and during any updates DURATION Varies IMPORTANCE Critical MAXIMUM DELAY N A INPUT A selected web page OUTPUT Data into a relation in the OlympicsDB DOCUMENT USE HTML documents OPS PERFORMED Data extraction data transformation and data loading SUBTASKS Web pages Research ERROR COND None 3 2 2 3 TASK NUMBER TASK NAME PERFORMER PURPOSE ENABLING COND DESCRIPTION FREQUENCY DURATION IMPORTANCE MAXIMUM DELAY INPUT OUTPUT DOCUMENT USE OPS PERFORMED SUBTASKS ERROR COND Generate Welcome Page Task GWPT Generate Welcome Page Apache Tomcat web server To generate the welcome page User accessing the OlympiChronicles web interface The Apache Tomcat web server will generate the OlympiChronicles welcome page home page when a
8. INPUT OlympicsDB data OUTPUT SPR Sport Event Result SEHR Sport Event Historical Result CPHR Country Participation History Result MCR Medal Count Result MCOR Medal Country Result TMAR Top Medal Athletes Result TMCR Top Medal Country Result PMIR Poster Medal Image Result YRBR Year Record Broken Result or FAR Flag Anthem Result DOCUMENT USE None OPS PERFORMED Transform data from the OlympicsDB output format to a web browser compatible format SUBTASKS None ERROR COND If OlympicsDB_output unknown then produce error message and stop 20 3 2 3 Document Forms 21 22 3 2 3 Document Forms Continued 23 3 2 3 Document Forms Continued 24 3 2 3 Document Forms Continued MCOR Medal Country Result Country Year Site SumGM SportGM EventGM SumSM SportGM EventGM SumBM SportBM EventBM TMAR Top Medal Athletes Result Athlete Year Site Sport Event GM or SM or BM SumNumMedals TMCR Top Medal Country Result Sport or Event Country SumNumMedals YRBR Year Record Broken Result Sport Event Year Site OR SOLF SOL Form Select Attributes From Relations Where Conditions PMR Poster Medal Result Year Site Poster Medal FAR Flag Anthem Result Country Flag Anthem UDBR Unformatted Database Results Attribute List Attribute Values 25 3 2 3 Document Forms Continued 26 4 CONCEPTUAL MO
9. TMAG O TMCO O PMIO then create SOL commands else report error SUBTASKS None ERROR COND If A TServer busy then Process TimeOut See OPS PERFORMED 3 2 2 7 Generate Results Page Task TASK NUMBER GRPT TASK NAME Generate Results Page PERFORMER Apache Tomcat web server PURPOSE To generate the results page ENABLING COND Getting the Query Result Form after querying the OlympicsDB DESCRIPTION The Apache Tomcat web server will generate the Results Page to be displayed to the user after getting the Query Result Form FREQUENCY As often as the user submits a query and the query is valid DURATION Short IMPORTANCE Critical MAXIMUM DELAY 10 seconds INPUT Query Result Form OUTPUT Results Page DOCUMENT USE WIRF Web Interface Result Form OPS PERFORMED Generate a Results Page to be displayed to the user from the Result form SUBTASKS None ERROR COND If A TServer busy then Process TimeOut 3 2 2 8 Create Query Result Form Task TASK NUMBER CRFT TASK NAME Create Result Form PERFORMER Server side script PURPOSE Provide a formatted result from the OlympicsDB ENABLING COND Database completing operations DESCRIPTION Formats output of the extracted data from the OlympicsDB to a form that can be interpreted by a web browser FREQUENCY Once per user query submission DURATION Depends on the complexity of the query result IMPORTANCE Critical MAXIMUM DELAY 5 10 seconds
10. boolean recordExists false wi String result Create a Statement Statement stmt db createStatement Select the ENAME column from the EMP table ResultSet rset stmt executeQuery select athlete A abrev record A year from currentWR A where subsport subsport and event trimevent and gender gender wt mn TMCData java Top Medal Countries Ouery package SQLUtilities import java sql import java util public class TMCData implements java io Serializable String subsport String eventt String gender private Connection db null public TMCData subsport AE ae ED n TMCData java Top Medal Countries Ouery Continued public String getSubsport return subsport public String getEventt return eventt public String getResults throws SOLException int index eventt indexOf 1 gender eventt substring index trim String trimevent eventt substring 0 index 1 trim inti 1 hanlean firer trie TMCData java Top Medal Countries Ouery Continued result lt table gt lt br gt lt br gt if recordExists result no data has been captured for your selections please t ry again CPHData java package SQLUtilities import java sql public class CPHData implements java io Serializable String abrev String country private Connection db null Stri
11. created by the user There have been updates that were made to the website and therefore the gueries that had not been implemented are now therefore those pages look more or less similar to the ones presented here what changes is the actual Java code to get the results 7 2 Data Beans SEQuery java Sport Event Query package SQLUtilities import java sql import java util public class SEData implements java io Serializable String subsport String eventt String gender private Connection db null public SEData subsport eventt gender dbConnect private void dbConnect t if db null 58 SEQuery java Sport Event Query Continued public void setEventt String value eventt value public String getSubsport return subsport public String getEventt return eventt public String getCurrentRecord throws SOLException int index eventt indexOf 1 gender eventt substring index trim String trimevent eventt substring 0 index 1 trim boolean recordExists false i String result Create a Statement Statement stmt db createStatement SEQuery java Sport Event Query Continued public String get WorldRecord throws SOLException int index eventt indexOf 1 gender eventt substring index trim String trimevent eventt substring 0 index 1 trim
12. display information and link to go to query if guery info displayed if click to select query true link to GQPT Query Page else no action 6 1 4 Generate Query Page Task 32 Generate Ouery Page HTML for OlympiChronicles logo HTML to display Query Name as a header tide for webpage HTML to display the options users have to select for a specific Query general code for selection for any query If sport related query display choose olympic game display choose_sport display choose_event other info depending on query If country_related query display choose olympic game display choose country If athlete related query display choose olympic game after choosing options If click submit true link to SQL Query Generate SQL Query query OlympicsDB getResults from OlympicsDB link to GRP Results Page Else if click logo true link to GSQP Select Query Page Else no action 33 6 1 5 Generate SQL Query Task 34 Generate SOL If guery Sport Event Ouery SELECT year site sport name subsport name event name subevent name medal FROM Sports OlympicSites Medal Wins Played At WHERE year year chosen and site site chosen and sport name sport name chosen and subsport name subsport name chosen and event name event name chosen and subevent name subevent name chosen and medal medal chosen Else if query Sport Event Historical Query SELECT year site sport name subsport name event name subeven
13. return countryyear public String getPosterMedal return posterMedal public String getSelect2 return country public String getAbrev wi String result try Statement s db createStatement ResultSet rs s executeQuery select abrev from countryAbrev where country like country while rs next result rs getString 1 trim catch Exception ee System out println Error populating UserData ee toString M return abrev result return result public String isCbSelected String s t 68 boolean found false if IposterMedal 0 eguals 1 UserData java Continued public String getGold t String result try Statement s db createStatement ResultSet rs s executeQuery select count count medal from individualwins where country like abrev and year countryyear and medal gold group by sport subsport event gender while rs next result rs getString 1 y catch Exception ee t System out println Error getting goldCount in UserData ee toString return i wi gt return result public String getSilver String result Mm gt try 69 Statement s db createStatement Dira Tia ee Gr SONS jama sl Las e ee EE Li EA SINN ENG UserData java Continued public String getBronze t St
14. user wants to access the information on the OlympicsDB As often as a user accesses the web address Very short Critical 10 seconds None Welcome Page WIFWF Web Interface Welcome Form Generation of welcome page send it to the user and wait for user action None If A TServer busy then Process TimeOut 3 2 2 4 Generate Ouery Select Page Task TASK NUMBER GOSPT TASK NAME Generate Ouery Select PERFORMER Apache Tomcat web server PURPOSE To generate the guery select page ENABLING COND User clicking on the ENTER button in the Welcome Page DESCRIPTION The Apache Tomcat web server will generate the OlympiChronicles guery select page when requested by the user from the Welcome page FREOUENCY As often as the user clicks on the ENTER button on the Welcome Page or the BACK button on a Ouery Page DURATION Very short IMPORTANCE Critical MAXIMUM DELAY 10 seconds INPUT Signal reguest from user to web server OUTPUT Ouery Select Page DOCUMENT USE WISF Web Interface Select Form OPS PERFORMED Generate the Query Select page send it to the user and wait for user input SUBTASKS None ERROR COND If A TServer busy then Process TimeOut 3 2 2 5 Generate Ouery Page Task TASK NUMBER GOPT TASK NAME Generate Ouery Page PERFORMER Apache Tomcat web server PURPOSE To generate the guery page where users will select their options for a specific query ENABLING COND Selecting a query on the Query Se
15. 1 events events substring 0 events lastIndexOf result N Strino valueOf amhennrt oer gt 87 88 Non Bean File 2 Utilities java while rset2 next years rset2 getString 1 im gt years years substring 0 years lastIndexOf result String valueOf country get j years n Mm years result result substring 0 result lastIndexOf conn close return result y public static int get NumDisplines throws SOLException int result 0 Connection conn dbConnect Create a Statement Statement stmt conn createStatement Select the ENAME column from the EMP table ResultSet rset stmt executeOuery select count unigue subsport from individualwins while rset next result rset getInt 1 return result public static String getArcheryEvents throws SQLException String result Connection conn dbConnect wi 3 11 C vaata a Ctatamant 89 Non Bean File 2 Utilities java Select the ENAME column from the EMP table ResultSet rset stmt executeOuery select unigue event from individualwins where subsport Archery while rset next t System out println rset getString 1 result lt option gt rset getString 1 lt option gt conn close return result 7 3 SQL Queries All the SQL queries created are
16. 12 3 2 2 1 Web Pages Research Task iaa 12 3222 ELE EN TA N 13 3 2 2 3 Generate Welcome Page Task sesse sesse sesse ee ee one 14 3 2 2 4 Generate Query Select Page Task esse sesse sesse one 15 3 2 2 5 Generate Query Page Task uses sesse sesse ees see ee mee 16 3 2 2 6 Generate SOL Query Task esse bees os ke oo 17 3 2 2 7 Generate Results Page Task eeenesoooone aoe 18 3 2 2 8 Create Query Result Form Task eeeeee ee 19 3 23 Doc ment Por ss ee ee A 20 4 CONCEPTUAL MODELING ass ENS SG da 25 4 1 Conceptual Schema iss ge Eed Ee ed N ed De ad 25 4 1 1 ER Model Graphical Schema sesse ssse sesse sesse ee ee Ge Gee ae 25 42 Functional Dependencies sissies dese de DNE GE GARAGE KENS Ke Pie Ge eN des kg 26 5 LOGICAL MODELING es sessie nsaisan aaeain a rea as ea saa 26 5 1 Eogical SCHEMA itis cieeascivnsavcushepsnavivexssee cheyossscsapsapwdsetasnteveasi lessees odin saa 26 5 11 Relational Model ius is ses SEE ES N is 26 5 12 Normalization aa 27 6 TASK EMUEATION ii 27 6 1 Task Design Specification uses Ee conoces GR AR RR Ne EKS KS SEN Ne NR ke 27 6 1 1 Extract Transform and Load Task Design eeooeese 27 6 1 1 1 Web Pages Research Task Design esse sesse sesse ees 27 6 1 2 Generate Welcome Page Task Design eneoeoenaseoene 28 6 1 3 Generate Query Select Page Task Design ennneoons 28 6 1 4 Generate Query Page Task Design susse s
17. AData implements java io Serializable Connection db null public TMAData dbConnect private void dbConnect if db null try DriverManager registerDriver new oracle jdbc driver OracleDriver TMAData java Continued while rset next result rset getString 1 catch Exception ee System out println Error populating Non participating countries MPCData ee toString wi return return result 3 Non Bean File gt Utilities java package SQLUtilities import java sql import java 10 import java util ArrayList public class Utilities private static Connection dbConnect 82 Non Bean File 2 Utilities java Create a Statement Statement stmt conn createStatement Select the ENAME column from the EMP table ResultSet rset stmt executeQuery select year from olympicsite Tterate through the result and print the employee names while rset next System out println rset getString 1 result lt option value rset getString 1 gt rset getString 1 lt option gt conn close return result public static String getCountries throws SOLException String result Connection conn dbConnect Mm 5 83 Non Bean File 2 Utilities java Create a Statement Statement stmt conn createStatement Select the ENAME column from the EMP table
18. DELING 27 4 1 Conceptual Schema The conceptual schema is the higher level representation of the OlympiChronicles enterprise as conceived by the designers This includes the different identified entities and relationships based on the document forms specified in the Requirements Document for this enterprise This entities and relationships include the internal processes by which the data will be extracted transformed and loaded ETL into the OlympicsDB as well as the process of user queries 4 1 1 ER Model Graphical Schema 28 42 Functional Dependencies The functional dependencies identified are For Country entity o Country Abbreviation gt Country Name o Country Abbreviation gt Flag o Country Abbreviation gt Anthem o Country Abbreviation gt First Year Participated For OlympicSite entity O Year gt Site O Year gt Poster O Year gt Medal LOGICAL MODELING 5 1 Logical Schema The logical schema is the next level in the representation of the OlympiChronicles enterprise comprised of the relation schemas derived from the ER diagram in the conceptual schema COUNTRY OLYMPIC SITE SPORT ATHLETE TEAM PLAYED AT PARTICIPATED BELONGS WINS 29 5 1 1 Relational Model 5 1 2 Normalization Relations need to be in either Boyce Codd normal form BCNF or in Third normal form 3NF in order to obtain lossless and sometimes dependency preserving relations In order to normalize these relat
19. Database formatted data for Craig to create a couple of tables 3 Web Interface Design 4 Documentation Phase I Phase IT Phase III some diagrams revisions 5 SOL gueries with revisions and changes made by Craig Shapiro 13 WEB SITE RESOURCES Our most extensive source of data was the official Olympics website Olympics org and Olympic it http www factmonster com ipka A0114094 html http www ex ac uk trol databank olympics index htm http www olympic org uk index_uk asp http www e magine education tas gov au pl olympics index htm http www infoplease com ipsa A0114094 html http www98 pair com msmonaco Almanac http www athletics heroes net athletics heroes stats_athletics olympics olympics htm http www runnersweb com running rw news frameset html http www runnersweb co m running olympics html http www recordholders org en links html http www hickoksports com history olympix shtml http www olympic it english game http www flags and anthems com 94 95
20. LIMITATIONS The major limitation in developing this project was the lack of time and knowledge about the different technologies that were needed for the completion of the project Event with this Craig Shapiro managed to finish the website and the results for all the queries Another very important factor was the lack of some data that was needed such as a complete set of results for the summer Olympic Games records broken and a complete list of athletes that participated but did not win medals Part of this information currently resides in the database but it is not complete 11 POSSIBILITIES FOR IMPROVEMENTS Integrating or adding the missing data to the database complete set of results and records Optimizing the SQL queries a couple of them take a few seconds to generate 12 CREDITS Craig Shapiro 1 Research ETL Tools Tomcat Servers in general JSP JavaBeans SQL Web Development Summer Olympic Data etc 2 ETL Kapow RoboSuite 5 5 learn and program to extract and load data into 93 database 3 Database all the tables were created by Craig Shapiro most of the data was obtain through Kapow RoboSuite 5 5 4 Web Interface Design and implementation 5 Documentation Phase I Phase II some diagrams revisions and paper copies 6 Java Code All the files Java and JavaBeans were created by Craig Shapiro Steffanie Orellana 1 Research ETL Tools Tomcat JSP JavaBeans SOL Summer Olympic Data etc 2
21. ResultSet rset stmt executeQuery select country from countryabrev Select the ENAME column from the EMP table ResultSet rset stmt executeQuery select country from countryabrev Iterate through the result and print the employee names while rset next System out println rset getString 1 result lt option value rset getString 1 gt rset getString 1 lt option gt conn close a return result Non Bean File 2 Utilities java public static String outputPosterURL String yearln throws SOLException Mm String result Connection conn dbConnect Create a Statement Statement stmt conn createStatement Select the ENAME column from the EMP table String argue select poster from olympicsite where year yearln ResultSet rset stmt executeOuery argue 85 result lt tr gt lt td gt lt div align center gt lt img src Vrset getString Non Bean File 2 Utilities java ResultSet rset2 stmt executeOuery select unigue event from individualwins where subsport String valueOf subsport get j while rset2 next events rset2 getString 1 events events substring 0 events lastIndexOf result displine String valueOf subsport get j events Jn 86 Non Bean File 2 Utilities java while rset2 next im gt events rset2 getString
22. ase scenario The user will proceed to enter the website A page with a list of ten queries is presented to the user The user will then select one from among the queries to be performed The selection will be sent to the web server where another procedure will generate a page with various options relevant to that query The user will fill out the options desired and submit the query The web server will receive the query request generate the appropriate SQL commands which are then sent to the OlympicsDB The database will then produce a results table and send it to the server from which the query was sent Another process on the server will format the results into a web page and serve it up to the user INTERNET WEB SERVER GWP Generate USER Welcome tE a WP GOSP Welcome Generate USER enters Website Page SOP Select Query Generate USER Selects a Guery Page Query Page OP GSQLQ Query Generate SGLF USER Submits a Query Page SOL SEL Fain Query OlympicsDB RP GRP UDBR Results k Generate Unformatted Page Results Database Page Results 11 3 2 Documentation 3 2 1 Top Level Flow Diagram WP SOP Welcome Page Welcome Page SPO Sport Event Query SPR Sport Event Resut SEHQ GWP GQSP J Spo
23. at event The results display the Olympic year the event you chose the gender whether it was male or female and the athletes names and countries 8 2 3 Country Participation Ouery The Country Participation Ouery allows you to choose a country and the results are the country abbreviation the first year the country participated in the summer Olympics and the total number of years that the specific country participated in the summer Olympics 8 2 4 Medal Count Ouery The Medal Count Ouery allows you to choose a country only countries that have won at least one medal are available to choose from and an Olympic year and the results are the country the total number of gold medals total number of silver medals total number of bronze medals and the total number medals won by that country on that specific Olympic year 8 2 5 Medals per Country Query The Medals per Country Query allows you to choose and Olympic year and the results are the list of countries that participated in the Olympics that year with their total number of gold medals silver medals bronze medals and total number of medals for that year The results also contain a list of countries that participated that year but did not win any medals as well as a list of countries that did not participate in the summer Olympics of that year 8 2 6 Top Medal Athletes Query 91 The Top Medal Athletes Ouery does not ask you to choose from any option the guery displays a list o
24. d submit account j Deploy ETL queries si on with Tomcat RoboSuite 5 5 Upload web scripts Web pages and E Scripts in JSP and HTML Send results of query Develop web to user interfac e and scripts in JSP and HTML Webpages from existing Olympic databas e websites used to populate OlympicsDB via ETL 3 1 3 ETL Procedures The DBoss Company designers will research analyze and select the most relevant summer Olympics websites With the resulting websites bookmarked the Kapow RoboSuite 5 5 ETL tool will be programmed to automatically surf to the various websites guery the relevant data extract it from the resulting tables transform it into the reguired format and load it into the OlympicsDB tables located on the Oracle server to be used by OlympiChronicles to answer the different user gueries through a web interface OlympicsDB Developer s Platform WPR Web Pages Research RSETL RoboSuite ETL 3 1 4 Web Server Procedures 10 The OlympiChronicles internal procedures include the engine that powers the website This involves providing various gueries and results of those gueries available to the user This is accomplished by several scripts and code for pages located on a UNIX web server When a user navigates to the OlympiChronicles website the initial web page is generated and served to the user The remaining scripts and procedures will be described by following a typical use c
25. eb Tiber laces EE ee ed ee ae GE ee TS be 82 9 2 Data BEANS Ge oe de N bb eg GR Ge be ee de dig de 82 93 SOE Mrs aras 82 10 SYSTEM EIMITA TO DSi iii ee ujuk ee 83 11 POSSIBILITIES FOR IMPROVEMENTS sessie sos ese see Ik N See SG eek EE GN Ad be be 83 12 REDES Oe ee ei ee de ee se ee Ge ee ee 83 13 WEBSITE RESOURCES ese ede ese ese ese n bee ede ees ee Ve Su siosio elek sies 83 ENVIRONMENT AND REOUIREMENT ANALYSIS 2 1 Purpose of Document The purpose of this document is to provide detailed reguirements and design specifications as well as to describe the implementation process and result for the enterprise project OlympiChronicles by the DBoss Company In this document there is a description of the ETL Extract Transform Load tool and process a description of the information needs and activities within the project the boundary of the design the assumptions and limitations encountered throughout the course of the design and development phases This document also contains a top level information flow chart as well as diagrams describing the logical flow of the various subsections of the enterprise the different tasks and task forms and the document forms that will be used a description of the conceptual model using an ER diagram and the logical level through the relational model for the OlympicsDB In this document there is also included the relation schema that will represent the data a discussion about the normalization of th
26. embedded in the Java code presented above 8 USER MANUAL 8 1 How to Access OlympiChronicles To access OlympiChronicles you need access to a computer and to the internet The webpage works better if viewed with Internet Explorer 4 0 or greater The URL for OlympiChronicles is http dc umd edu 8081 index2 html 8 2 How to Navigate Through OlympiChronicles Once at the OlympiChronicles website click on the torch in the middle of the introduction page to enter This takes you to the welcome page where you will be redirected to the QuerySelect page where you will be able to select your query There is a navigation bar on the left side of the screen with all the possible queries that you can make To see what each query does mouse over its name and a description will 90 appear particular If you are interested in that guery click on it and you will be redirected to that guery This is where you can choose your options for your guery 8 2 1 Sport Event Ouery The Sport Event Ouery allows you to choose a discipline sport or sub sport and then an event from that discipline The results are the Olympic record and the world record for that event it shows you the name of the athlete who holds that record the country the result and the games in which the record was broken 8 2 2 Sport Event History Ouery The Sport Event History Ouery allows you to choose a discipline sport or sub sport and a particular event and it returns the history of th
27. enul i stringSplit info i menu2 i stringSplit menul i 1 var countries document myForm select2 var years document myForm countryyear countries length menu1 length years length menu2 0 length for vari 0 1 lt menul length i countries options i value menu1 i 0 countries options i text menul 0 j document myForm select2 selected 0 for var x 0 x lt menu2 0 length x years options x text menu2 0 x years options x value menu2 0 x document myForm countryyear selected 0 EE EE EG SE SE BE BE SE SESESESESESESESESE SESE ME SE SE ESE SE SE SE BE BE BE SE SE SE SE SESESESESE SESSE SE SESSE SE SE SE BE BE BE SE SE SE aE aE SE aE aE a a aaa a SE SE ak SE SE BE BE BE SE SES ARA function updateMenus what var sel what selectedIndex if sel gt 0 amp amp sel lt menul length var temp menu sel else var temp new Array what form countryyear length temp length for vari 0 1 lt temp length i what form countryyear options i text templi what form countryyear options i value templi what form countryyear selected 0 QUERYMC JSP Medal Count Query Page Continued LE sh she sh SE SE sh sh SE oh shah sh oh oh oh oh SE ah oh sh oh ah sh sh oh ob sh oh ah oh SE SE ob SE ah oh oh oh ah sh oh oh SESE SE ah ob sh oh sh SE sh oh sh oh oh sh oh oh ob oh oh ah ok AA 53 QUERYMC JSP Medal Co
28. ese relations any functional dependencies derived and a detail discussion of the implementation of the database and web interface This document is intended for staff members of CMSC424 who will be reviewing and approving this analysis The deliverable from this phase is a Project Report describing the specification of the solution and working demo of the implementation of the system 2 2 Purpose of the Project The purpose of the project is to design a reliable and efficient ETL tool or master the use of an existing one which will extract Summer Olympic facts from the web transform the extracted data cleanse it and or format it and load it into the OlympicsDB This is the first goal of the OlympiChronicles project The second goal is to provide users a web accessible database of summer Olympic facts from 1896 to the present The users will select various aspects about the games queries and OlympiChronicles will return a formatted table of the results of the user s query The purpose of this phase of the project is to implement the OlympiChronicles system following the requirements and design specifications of the early stages of the analysis and design process to produce a working demo The implementation includes the population of the database and the creation of a web interface for users to interact and guery the database The second purpose of this phase is to produce a detailed report of the entire development process analy
29. esse sesse ee ae ee ee ee ae 29 6 1 5 Generate SQL Query Task Design esse sesse sesse ee ae ee ee ae 30 6 1 6 Generate Result Page Task Design ereeenneoooenaseeene 32 6 1 7 Create Result Form Task Design sesse esse sesse ee ae ee ae 33 7 SOURCE PROGRAM LISTING isi ds tese ede dese ia 35 7 1 A SEER GR GR Ee GN Ee Ke ee ER Ee ee Re ee eie 35 GE EO EE RE EE separated 51 1 SOL OUEri s as N devel N N ied ee E Ge E 80 USER MANUAL sis sees eke pe ee eN ee eks en Ge OE ee ge ese Ge ee ee aa 80 8 1 How to Access OlsmpiChrondles rd ie Ge Ee ER ge de 80 8 2 How to Navigate Through OlympiChronicles eenneoeneoooonaseee 80 8 2 1 Sport Event Query oe iii ilme ks ida De ae De Ge De Ged De see 80 8 2 2 Sport Event History Query sesde sedes sedes ese be BE ee eke We seke es 81 8 2 3 Country Participation Query reeeeeenneonoooooooeeennnnneoooee 81 8 24 Medal Count Query iese dee ironico arcano 81 8 2 5 Medals per Country Query ni See sees Sk od N ee tava 81 8 2 6 Top Medal Athletes Query sisosicorinicaconocoricaononanio nio Ke Ge Se GR SS su 81 8 2 7 Top Medal Countries Query sides se oe de eg Rd 81 8 2 8 Year Record Broken Query ses ES AR sd ok latina 82 8 2 9 Posters and Medals Query ss esse gese esse sed oe og Ne eed NE 82 8 2 10 Flags and Anthems Query esse se dsesdoee dese kase bes eo dee NG bek 82 9 TESLING ERROR TS ese Mee Gee Gee si ee oi ee ee er dee ee De Ee oes bee ees sea 82 9 1 W
30. etGender return gender public String getMedal return medal public String getCountry return country public String getAthlete return athlete public boolean populate if yearList isEmpty try Statement s db createStatement ResultSet rs s executeQuery select from individualwins where 79 subsport discipline and event event CEHData java Continued countryList clear athleteList clear rowCount 0 while rs next yearList add rs getString year subsportList add rs getString subsport neweventList add rs getString event genderList add rs getString gender medalList add rs getString medal countryList add rs getString country athleteList add rs getString athlete rowCount catch Exception e System out println Error populating SEHData bean e toString return false return true public void setStartRow int _start if start lt rowCount currentRow start j ay 80 public int nextRow CEHData java Continued setMedal String medalList get currentRow setCountry String countryList get currentRow setAthlete String athleteList get currentRow currentRow return currentRow public int getCurrentRow return currentRow TMAData java package SQLUtilities import java sql public class TM
31. f athletes that have won at least three medals during the entire history of the summer Olympic Games 8 2 7 Top Medal Countries Ouery The Top Medal Countries Ouery allows you to choose a discipline sport or sub sport and an event from that discipline and the results are the country and the total number of medals won for that country for that particular event This result represents the medal wins per country through the history of the Olympic games 8 2 8 Year Record Broken Ouery The Year Record Broken Ouery allows you to choose a discipline sport or sub sport and then an event from that discipline The results are the progression of Olympic and world records broken for that discipline event 8 2 9 Posters and Medals Ouery The Poster and Medals Ouery allows you to choose an Olympic year and the choose any of the following three options poster medal front and medal back Depending on what images you want to see the result will be an image of the official poster and medal front and back for that year 8 2 10 Flags and Anthems Ouery The Flags and Anthems Ouery allows you to choose a country and an image of its flag will be displayed along with a link to an audio file with the country s anthem TESTING EFFORTS Web Interface The issue that needed attention in the web interface was input validation For example users cannot proceed to the results page if they do not make choice This is achieved by hiding the Submit
32. ght 290 border 0 gt lt a gt lt img src images torch_1 gif width 50 height 100 gt lt p gt lt hr gt lt p class style6 gt Another internet resource for information on the summer Olympic games lt br gt from 1896 to present lt p gt lt hr gt lt p class style1 gt amp nbsp lt p gt lt p class style2 gt lt span class style3 gt lt blink gt lt span class style8 gt You will be lt span class style9 gt transferred automatically lt span gt to the Welcome Page lt span class style9 gt in 10 secon ds lt span gt lt br gt If your browser does not support automatic forwarding please click on the above Logo lt sp an gt lt blink gt lt span gt lt br gt lt br gt lt p gt lt p gt Developed by lt br gt lt img src images LogoWeb jpg width 214 height 174 gt lt p gt Steffanie lt span class ctn gt Orellana and Craig Shapiro lt span gt lt p class style2 gt lt br gt lt p gt lt div gt lt body gt lt html gt 43 QUERYSELECT HTML Query Select Page lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt OlympiChronicles Query Select gt Please choose from one of the queries lt title gt lt script language JavaScript type
33. head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt OlympiChronicles Query SPORT EVENT HISTORY lt title gt lt script language JavaScript types text JavaScript gt lt hide from old browsers var info new Array lt Utilities getSubSportANDEvent2 gt SE SE Sla sh SS SL SE sh sh SL shh sh SE sh sh sh SE SSE sh sh shh sh SE SE sh sh shh sh SE SE sh SE shh sh SE SE shh sh SE SE sh shhh sh SE SE sh SE shh SL SE SE shh SL SE SE SL SE SS SL SE SE SL SE SS SE a ai R R i RA RARA RA RA RARA RA RARA RA RA RA RARA RA RA RARA RA RARA RA RA RARA RA RARA RA RA RARA RARA RA RARA RA RARA DEE RARAS ae function stringSplit string delimiter if string null string retiirn null QUERYSEH JSP Sport Event Query Page Continued SE SE SE SE EES function createMenus for vari 0 1 lt info length i menul i stringSplit info i menu2 i stringSplit menu1 i 1 var disciplines document myForm discipline var events document myForm eventt disciplines length menu1 length events length menu2 0 length for vari 0 1 lt menu1length i disciplines options i value menu1 1 0 disciplines options i text menul 1 0 dacument mvFarm discinline selected 0 QUERYSEH JSP Sport Event Query Page Continued some more automatically generated code lt div id Layer1
34. ions we need to use the functional dependencies derived in the previous section and check if the relations are BCNF or 3NF and if they are not then the relations need to be decomposed into BCNF or 3NF relations TASK EMULATION 6 1 Task Design Specification 6 1 1 Extract Transform and Load Task 6 1 1 1 Web Pages Research Task Extract Transform and Load Start RoboSuite 5 5 Configure RoboSuite 5 5 for each website bookmarked for each webpage on website query results RoboSuite url webpage url set values to look for extract informationto a predefined table Web Pages Research Google query to find Summer Olympic Games sites For each website found in Google if website has relevant data and if website has complete data to be used by the OlympicsDB Bookmark else skip 6 1 2 Generate Welcome Page Task 6 1 3 Generate Query Select Page Task 31 Generate Welcome Page HTML for webpage layout HTML for OlympiChronicles logo HTML for Company Logo If click to enter true link to GQSP Query Select Page Else no action Generate Query Select HTML for webpage layout HTML for OlympiChronicles logo HTML for header Summer Olympics Facts Intro text Place your mouse over a query to see more information HTML JavaScript for query options vertical navigation bar general code for mouse over any query If click logo true link to this Query Select Page If mouse over query
35. lect Page DESCRIPTION The Apache Tomcat web server will generate the Query page every time a user selects a query from the Query Select Page FREQUENCY As often as the user clicks on a query to select it from the Query Select Page DURATION Very short IMPORTANCE Critical MAXIMUM DELAY 10 seconds INPUT Signal request from user to web server OUTPUT Query Page DOCUMENT USE WIOF Web Interface Query Form OPS PERFORMED Generate the Query From send it to user allow user to make selections wait for user input submit SUBTASKS None ERROR COND If A TServer busy then Process TimeOut 3 2 2 6 Generate SOL Ouery Task TASK NUMBER SOLCT TASK NAME SOL Creation PERFORMER Apache Tomcat web server PURPOSE Create SOL commands ENABLING COND Submitting web guery form DESCRIPTION Generation of SOL commands from a web guery form to task the OlympicsDB FREOUENCY Once per user guery submission DURATION Short IMPORTANCE Critical MAXIMUM DELAY 5 10 seconds INPUT Web guery form OUTPUT SOLF SOL Form DOCUMENT USE SPQ Sport Event Query SEHO Sport Event Historical Ouery CPHO Country Participation History Query MCO Medal Count Query MCoQ Medal Country Query TMAO Top Medal Athletes Query TMCO Top Medal Country Query PMIO Poster Medal Image Query YRBO Year Record Broken Query or FAQ Flag Anthem Query OPS PERFORMED if Q SPQ Q SEHQ O CPHO Q MCQ Q MCoQ O
36. lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt lt a href splashScreen html onMouseOut MM_swapImgRestore onMouseOver MM_swapImage T orch images lighted_torch gif 1 gt lt img src images unlighted torch gif alt CLICK to god to Ouery Select Page name Torch width 87 height 218 border 0 gt lt a gt lt p gt lt p gt amp nbsp lt p gt lt p gt lt em gt lt span class style2 gt Light the lt strong gt TORCH lt strong gt ahawe tn anter OhrmniCheraniclac SPLASHSCREEN HTML Redirection Page lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt meta http equiv refresh content 10 URL OuerySelect html gt lt title gt Untitled Document lt title gt lt style type text css gt 42 da stvle1 font size large SPLASHSCREEN HTML Redirection Page Continued style6 font size x large font family Berlin Sans FB Demi style8 color 000000 style9 color FF0000 gt lt style gt lt head gt lt body gt lt div align center gt lt p gt amp nbsp lt p gt lt p gt lt img src images torch 1 gif width 50 height 100 gt lt a href QuerySelect html gt lt img src images BrandLogoWebNEW jpg width 463 hei
37. mpiChronicles operates via a web browser that allows users to select various search criteria in researching facts about summer Olympic Games from 1896 through the present 3 1 1 From the user s perspective The first step in accessing OlympiChronicles powerful database is to navigate to a predefined website There the user will create a query and submit it to a process running on a remote server where the OlympicsDB is stored This process will create a form containing SQL commands for the specified query and will submit it to the database After the data has been retrieved from the database it will be formatted and presented through the user s web browser 3 1 2 From the developer s perspective The developers will be employing several technologies in order to implement the enterprise in its varying phases The diagram below shows the main components of the system and indicates what responsibility to the system each component has as well as the general flow of information Parts of this diagram will be elaborated upon in subseguent sections DEVELOPER OlympicsDB Database SQL Plus and JDBC Develops and refines code Load data extracted and translated in ETL process Sends queries Sends unfor matted results Developer s to OlympicsDB to web ser ver Sends and Platform receives Infor mation Web Server to from internet Request to access OlympiChronicles Web sites an
38. ng firstyear String totalyears public CPHData country abrev firstyear totalyears wy gt Mm gt dbConnect private void dbConnect if db null try DriverManager registerDriver new oracle dbc driver OracleDriver db DriverManager getConnection jdbc oracle thin dbserv dc umd edu 1521 NR424 nr42418 skidoo23 catch Exception ee System out println Error ee Y CPHData java Continued public String getCountry return country public String getAbrev return abrev public String getFirstyear t return firstyear public String get Totalyears return totalyears public String getCountries wi String result try t Statement s db createStatement ResultSet rset s executeQuery select country from country Abrev while rset next result lt option value rset getString 1 gt 4rset getString 1 lt option gt catch Exception ee System out println Error populating UserData ee toString wi return return result public void setAbrev t String result try S 65 CPHData java Continued while rset next result rset getString 1 catch Exception ee System out println Error populating CPHData ee toString System out println se
39. ngth 1 it 2 if img MM findObj args 1 null if img MM up img MM up img src img src img MM dn argslit 1 argslit 1 img MM up nbArr nbArr length img lt script gt lt script language JavaScript type text JavaScript gt da function MM reloadPage init reloads the window if Nav4 resized if init true with navigator if appName Netscape 8z8z parselnt app Version 4 document MM_pgW innerWidth document MM pgH innerHeight onresize MM reloadPage else if nnerWidth document MM pgW innerHeight document MM pgH location reload MM reloadPage true function MM showHideLayers v6 0 var 1 p v obj args MM showHideLayers arguments for 1 0 i lt args length 2 1 3 if obj MM findObj args 1 null v args i 2 if obj style obj 0bj style v v show visible v hide hidden v obj visibility v gt lt script gt lt style type text css gt lt 45 style3 font size large mt A Than aina VO QUERYSELECT HTML Query Select Page Continued place your mouse pointer on one lt br gt of the ten catagories to the left A description lt br gt of that query will appear in this window lt p gt lt p class style3 gt When you have decided which catagory you would like lt br gt to research click on the title and you will be lt br gt presented with a menu of options for lt br gt
40. option until they have a made a choice When they have to choose from various different options as in the case of medal and poster there is a function that checks what needs to be displayed to the user AIl the gueries were tested with various different inputs to make sure that they work and in the case that no results are found for that guery for the particular options chosen by the user then a 92 message is displayed letting the user know that 9 2 Data Beans The data beans are used to retrieve the information from the database once the user has made a selection or has created a guery The data validation is done in the client side therefore that is not necessary here There are proper try and catch block specially to make sure that the connection to the database has not failed To our knowledge the beans work correctly as long as they are called in the right place from the JSP pages Due to lack of time no further testing was done 9 3 SQL Queries The SQL queries were created using specific examples i e when the input is a year then using a specific year etc and they were tested with different inputs to assure the correctness of the results Most of it was done by hand matching results against the Olympics org database results since that was the greatest source for the OlympicsDB Most of the queries work correctly A couple of queries do not return a complete set of results but the results that generates are correct 10 SYSTEM
41. ring result try Statement s ResultSet rs db createStatement s executeOuery select count count medal from individualwins where country like abrev and year countryyear and medal bronze group by sport subsport event gender while rs next catch Exception ee System out println Error getting goldCount in UserData ee toString mm result rs getString 1 return return result 70 MPCData java package SQLUtilities import java sql public class MPCData implements java io Serializable String year String YRyear private Connection db null public MPCData wi year dbConnect private void dbConnect t if db null 4 71 MPCData java Continued public String getNonPartCountriesCount String result System out println The year to search year and the YR is YRyear try Statement s db createStatement ResultSet rset s executeQuery select count country from countryAbrev CA country Years CY where CY abrev CA abrev and YRyear N wi gt while rset next result rset getString 1 12 catch Exception ee MPCData java Continued else result lt td gt lt div align left gt rset getString 2 rset getString 1 lt div gt lt td gt lt tr gt
42. rt Event Historical Query Generate Generate EMI Welcome Ouery Select CPHQ Page Page SEHR Country Participa tion Sport Event Historical Result History Quer MCO Fe o GOP Country Participa tion Medal Count Query A Generate History Result Query MCoQ Page Medal Country Que 4 Medal Count Resut GSQLQ Generate SQL Query MCoR TMAG Medal Country Resut Top Medal Athle tes Ouery EE SLo 37 EE TMCO TMAR Top Medal Athle tes Result Top Medal Country Query Za TMCR Top Medal Country Result Poster Me tal Image Query YRBO Poster Me tal Image Result l CPHR Year Record Broken Query Olym picsD B YRBR Year Record Broken Result FAQ Flag Anthem Query RSETL RoboS ute ETL UDBR Olympics Websites Unformatted Database Results 92 A Po 35 3 n a gt 3 5 7 E Es Flag Anthem Resut o v 3 2 2 Tasks Subtasks and Task Forms 3 2 2 1 TASK NUMBER TASK NAME PERFORMER PURPOSE ENABLING COND DESCRIPTION FREQUENCY DURATION IMPORTANCE MAXIMUM DELAY INPUT OUTPUT DOCUMENT USE OPS PERFORMED SUBTASKS ERROR COND Web pages Research Task WPRT Web Pages Research OlympiChronicles Designers To research the internet for web sites that contain data for the summer Olympic Games since 1896 until the present To populate the OlympicsDB Research the internet As often as necessary Varies
43. sis design and implementation 2 3 Scope The scope of this project involves multiple tasks The first task involves researching and collecting web pages containing data about the history of the summer Olympic Games from the internet The second includes extracting the relevant data filtering it and populating and maintaining a web accessible database for the summer Olympic Games from 1896 to the present namely the OlympicsDB The third involves creating a web interface from which the user will create a guery to search for summer Olympics information based on year location countries in attendance events participants medals records broken current records and audio visuals from the various years which include images of the medals and posters for each Olympic year and the flags and anthems for participating countries Included in this task will be code to process and interpret the above mentioned gueries and provide results to the user 2 4 Assumptions The assumptions for this enterprise are as follows The user reads and understands English The user does not have to subscribe to the system The user will have internet access The user has basic web browsing skills to access the web interface The data will be accurate reliable and complete It is assumed that the database server is configured appropriately to handle the user demands placed on the project There will be enough space on the Oracle server to s
44. t setNewevent setGender setMedal setCountry setAthlete sranvl iet nar A vvarrl ict A 77 CEHData java Continued genderList new ArrayList medalList new ArrayList countryList new ArrayList athleteList new ArrayList currentRow 0 rowCount 0 private void dbConnect t if db null try t DriverManager registerDriver new oracle jdbc driver OracleDriver db DriverManager getConnection jdbc oracle thin dbserv dc umd edu 1521 NR424 nr42418 skidoo23 catch Exception ee System out println Error ee y P public void setDiscipline String value discipline value public void setEventt String values event values public void setYear String values t 78 year values CEHData java Continued public void setNewevent String values newevent values public void setGender String values gender values public void setMedal String values medal values public void setCountry String values country values public void setAthlete String values athlete values public String getDiscipline return discipline public String getEventt return event public String getYear return year public String getSubsport return subsport public String getNewevent return newevent public String g
45. t div align left gt lt div gt 55 QUERYPM JSP Poster Medal Query Page lt jsp useBean id user class SQLUtilities UserData scope session gt lt jsp setProperty name user property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt page import SQLUtilities Utilities gt lt page errorPage myError jsp from QueryPM jsp gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt OlympiChronicles Query POSTERS and MEDALS lt title gt automatically generated code lt p gt Please select the options for your query lt p gt lt form action QueryResultPM jsp method post name form1 gt lt select name select onChange MM showHideLayers Layer16 show gt lt option value 0 selected gt Select Olympic Year lt option gt lt Utilities getYears gt lt select gt lt div id Layer16 style position absolute left 8px top 116px width 485px height 9 1px z index 2 visibility hidden gt lt p gt Choose from the options below lt p gt lt p gt Poster lt input name posterMedal type checkbox value poster gt Medal front lt input name posterMedal type checkbox value medalFront gt Medal back lt input name posterMedal type checkbox value medalBack gt lt p gt lt p gt
46. t name medal FROM Sports OlympicSites Medal Else if query Country Participation History Query SELECT C year site country abbreviation C country name year first participated count country name FROM Country C OlympicSite O Participated P GROUP BY Pyear Else if guery Medal Count Ouery SELECT year site country name count medal FROM OlympicSite Country Medal Participated Wins Belongs WHERE year year chosen and site site chosen and country name country name chosen GROUP BY medal Else if query Medal Country History Query SELECT year site country_name medal FROM OlympicSite Country Medal Participated Wins Belongs 35 Generate SOL cont Else if query Top Medal Athletes Query SELECT year site first name last name medal FROM OlympicSite Athlete Belongs Participated Medal Sport Wins Played At HAVING count medal gt 3 Else if query Top Medal Country Query SELECT year site country name event name count medal FROM OlympicSite Country Sport Medal Win Participated Played At Else if query Poster Medal Image Query SELECT year site poster front_medal back_medal FROM OlympicSite WHERE year year_ chosen and site site chosen Else if query Year Record Broken Query SELECT FROM WHERE Else if query Flag Anthem Query SELECT year site country name flag anthem FROM OlympicSite Country WHERE year year chosen and sitessite chosen and country name country name chosen
47. text JavaScript gt lt function MM preloadImages v3 0 var d document if d images i d MM p d MM p new Array var ij d MM p length a MM preloadImages arguments for i 0 i lt a length i if a i indexOf 0 d MM p j new Image d MM_p j src a i 4 y function MM findObj n d v4 01 var p 1 x if d d document if p n indexOf gt O amp amp parent frames length d parent frames n substring p 1 document n n substring 0 p if x d n amp amp d all x d all n for 0 x82821 lt d forms length i x d forms i n for 1 0 x8z8Zd layers z8li lt d layers length i x MM findObj n d layers i document f x amp amp d getElementByld x d getElementByld n return x function MM nbGroup event grpName v6 0 var iimg nbArr args MM nbGroup arguments if event init amp amp args length gt 2 if amg MM_findObj args 2 null amp amp limg MM_init img MM_ init true img MM up args 3 img MM dn img src if nbArr document grpName null nbArr document grpName new Array nbArr nbArr length img for 1 4 1 lt args length 1 i 2 if img MM findObj args 1 null if img MM up img MM up img src 44 QUERYSELECT HTML Query Select Page Continued 0 1 lt nbArr length i img nbArr 1 img src img MM up img MM dn 00 document grpName nbArr new Array for 1 2 1 lt args le
48. that query designed to assist you lt br gt in narrowing your results lt p gt lt p class style3 gt amp nbsp lt p gt lt p class style9 gt lt span class style8 gt Have Fun and ENJOY lt span gt lt p gt lt div gt lt div gt lt div id Layer6 style position absolute left 304px top 276px width 491px height 305px z index 11 background color FFFFFF layer background color FFFFFF border Opx none 00000 O visibility hidden gt lt div align center gt lt p gt lt strong gt lt span class style10 gt POSTERS and MEDALS lt br gt lt span class style gt QUERY lt span gt lt span gt lt strong gt lt p gt lt p gt amp nbsp lt p gt lt p gt The result of this query lt p gt lt p gt displays images of the official Olympics lt p gt lt p gt lt strong gt poster lt strong gt and lt strong gt medal lt strong gt front and back lt p gt lt p gt for the selected Olympic year lt p gt lt div gt lt div gt OUERYSEH JSP Sport Event Ouery Page lt jsp useBean id SEHData class SQLUtilities SEHData scope session gt lt jsp setProperty name SEHData property gt lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt page import SOLUtilities Utilities gt lt O page errorPage myError jsp from QuerySEH jsp gt lt html gt lt
49. tore the data 2 5 Technical and Conceptual Problems and Solutions 2 5 1 Technical Problems and Solutions Problem The current limited knowledge of the designers in the required database language and the server client programming Solution Research to acquire the necessary knowledge to carry out these tasks and learn how to design and populate the SQL database and engineer it to be queried in a server client environment Problem Gathering the data Solution Research various websites which currently contain Olympic data Problem Extracting the data Solution To analyze each page containing embedded data create an application which will pattern match and extract that data Problem Transforming the data Solution Reformatting the data extracted to be loaded into the database Problem Consistency of numeric data Solution Convert all measurements to metrics where applicable Problem The Apache web server on the dc cluster account is not compatible with JSP Solution To install and configure Tomcat Problem Lack of knowledge creating web server scripts Solutions Research and learn JSP Problem Configuring RoboSuite 5 5 properly to do the data extraction Solution Research user manuals and contact tech support for additional help Problem Standardizing the data Solution Decide on a format and manually go through extracted data and determine what needs to be standardized Problem Writing accurate and detailed pse
50. tting abrev to result abrev result public void setResult setA brev try Statement s db createStatement ResultSet rset s executeOuery select firstyear totalyears from countrypartici pation where abrev abrev System out println abrev while rset next firstyear rset getString 1 totalyears rset getString 2 catch Exception ee System out println Error getting results CPHData ee toString 66 UserData java package SQLUtilities import java sql public class UserData implements java io Serializable String years String posterMedal String country String abrev private Connection db null String countryyear public UserData Mm gt years countryyear abrev posterMedal new String 1 dbConnect private void dbConnect if db null try DriverManager registerDriver new oracle dbc driver OracleDriver db DriverManager getConnection jdbc oracle thin dbserv dc umd edu 1521 NR424 nr42418 skidoo23 catch Exception ee System out println Error ee 67 public void setCountryyear String value UserData java Continued public void setPosterMedal String values posterMedal values public void setSelect2 String value country value public String getSelect return years public String getCountryyear
51. udocode without having fully researched the technologies and languages that will be used JSP JDBC JAVA etc Solution Further research the technologies and starting the programming phase of this project Problem Checking that data extracted by RoboSuite 5 5 was properly entered into the OlympicsDB Solution Check by hand against the source Problem Learning JavaBeans to interact with JSP Solution Research and follow examples of JavaBeans Problem Understanding JSP enough to access the JavaBean and Java code Solution Research and follow examples Problem Getting the results back to the Client and formatting them Solution Research and follow examples Problem Building the SOL gueries Solution Research and follow examples 2 5 2 Conceptual Problems and Solutions Problem Locating the data Solution Research the web using google and other search engines Problem Identifying a complete set of tasks at this phase of the project Solution Deeper analysis of the enterprise moving to the second phase of the project where the enterprise will be conceptually and logically emulated Problem How to calculate Olympic and world records efficiently Solution Use properties of SOL to use comparisons to derive these attributes Problem Deriving the first year a country participated Solution Use properties of SOL to use comparison to find this information SYSTEM ANALYSIS AND SPECIFICATION 3 1 Description of Procedures Oly
52. unt Query Page Continued lt div id Layer1 style position absolute left 300px top 186px width 500px height 397px border 1px solid 4999999 z index 1 visibility visible gt lt div align center gt lt p gt amp nbsp lt p gt lt p gt Please select the options for your query lt p gt lt form name myForm action OueryResultMC jsp method post gt lt p gt Country amp nbsp lt select name select2 size 1 onChange updateMenus this MM showHideLayers Layer16 show gt lt option gt amp nbsp lt option gt lt option gt lt select gt lt p gt lt div id Layer16 style position absolute left 8px top 116px width 485px height 91px z index 2 visibility hidden gt Year amp nbsp lt select name countryyear size 1 gt lt option gt amp nbsp lt option gt lt option gt lt select gt lt jsp setProperty name user property select2 value document myForm select2 options document myForm select2 selectedIndex text gt lt jsp setProperty name user property countryyear value document myForm countryyear options document myForm countryyear selectedIndex text gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt p gt amp nbsp lt p gt lt input type submit name Submit value Submit gt lt input type reset value Reset gt lt div gt lt form gt lt div gt lt div gt
53. untry Event Total Number of Medals populate with results from OlympicsDB 39 Create Result From cont If result Poster Medal Image Result result table add columns Poster url Front Medal url Back Medal url populate with results from OlympicsDB Else if result Year Record Broken Result result table add columns populate with results from OlympicsDB Else if result Flag Anthem Result result table add column Country Flag url Anthem url populate with results from OlympicsDB Send result table to GRP Results Page 40 7 SOURCE PROGRAM LISTING 7 1 Web Interface INDEX HTML Welcome Page lt DOCTYPE HTML PUBLIC W3C DTD HTML 4 01 Transitional EN http www w3 org TR html4 loose dtd gt lt html gt lt head gt lt meta http equiv Content Type content text html charset iso 8859 1 gt lt title gt OlympiChronicles Welcome Page lt title gt lt script language JavaScript type text JavaScript gt da function MM reloadPage init reloads the window if Nav4 resized if init true with navigator if appName Netscape amp amp parseInt app Version 4 document MM_pgW innerWidth document MM pgH innerHeight onresize MM reloadPage else if inner Width document MM pgW innerHeight document MM pgH location reload MM reloadPageltrue function MM preloadImages
54. v3 0 var d document if d images i d MM p d MM p new Array var 1j d MM p length a MM preloadImages arguments for i 0 i lt a length i if a i indexOf 0 d MM p j new Image d MM p j src a i 4 function MM swaplmgRestore v3 0 var 1 x a document MM_ sr for i 0 a amp amp i lt a length amp amp x ali amp amp x oSre i X Src x oSrc function MM_findObj n d v4 01 var p i x if d d document if p n indexOf gt O amp amp parent frames length d parent frames n substring p 1 document n n substring 0 p if x d n amp amp d all x d all n for 0 x82821 lt d forms length i x d forms il n for 1 0 x8z8Zd layers z8li lt d layers length i x MM findObj n d layers i document f x amp amp d getElementByld x d getElementByld n return x function MM swaplmage v3 0 41 INDEX HTML Welcome Page Continued gt lt script gt lt style type text css gt lt style2 font size 18px style3 font size small gt lt style gt lt head gt lt body onLoads MM preloadImages images lighted_torch gif gt lt div align center gt lt p class style3 gt This site has been optimized for Internet Explorer 4 0 or greater lt br gt lt a href http www microsoft com windows ie downloads critical ie6sp1 default mspx gt Get IN t Here Internet Explorer 6 SP 1 lt a gt lt p gt
Download Pdf Manuals
Related Search