Creating Databases for Web applications

1. Creating Databases for Web applications Introductions & overview Administration Moodle HW: Sign on & register for course on Moodle. Take survey. Review HTML (esp. forms). Review Flash/ActionScript. MAT 3530-20

2. Introductions • Jeanine Meyer, Math/Computer Science & New Media. • Ph.D. in Computer Science • IBM Research, IBM EduQuest (corporate grants). Pace University. Consulting/k-12 Faculty development • books: Multimedia in the Classroom, Programming Games using Visual Basic, Creating Databases Web Applications with PHP and ASP, Beginning Scripting through Game Creation • Learning assistant: William Killoran • You MAT 3530-20

3. General objectives • Learn how to learn • Practice looking up and using sources • Learn how to build large[r] applications • Make proposals, make and use diagrams • Work in teams • Make presentations • Practice concise writing • Blog entries, abstracts (1-pagers) MAT 3530-20

4. Moodle • Instructional support tool • IT IS A REQUIREMENT that you check this regularly. • You will be required to make postings to specific Discussion Forums. Hopefully, you will make additional postings. • You are required to read postings. MAT 3530-20

5. Books • There will be assignment(s) to find and describe on-line sources • Reference: Creating Web Databases with PHP and ASP • Note: Coding examples in book for old php. • Much is relevant: general background on middleware, HTML, SQL. Design of examples. • MANY other books MAT 3530-20

6. Course structure • Each day will include lecture, demonstration, discussion, exercises • Postings, homework • team presentations (explanation and enhancements of sample projects) • midterm & final quiz, plus • one original project (can be team) • initial presentation, with diagrams • Final presentation, including demonstration, showing of working code • Extra credit: talks on suggested topics (for example, alternatives to php such as ASP.net, Ruby on Rails, etc.) MAT 3530-20

7. Course content Course content: practical and (some) theoretical tools for creating Web applications involving databases. • (Systems design) Describe (logical) function using diagrams • Entity relationship • Data Flow diagrams • Storyboard • (Systems build) Requires middleware=server-side tools. We will use Open Source software (PHP and MySQL) MAT 3530-20

8. What is a data base? • A data base is organized information. A data base management system is a product that supports implementation & use of databases. • HOWEVER, generally, the term database is reserved for something using a standard DBMS product • DBMS is the product/tool: MySQL, Access, (Oracle, DBII, etc.) • The specific database, for example, the [toy] database examples for this course, are implemented using a particular DBMS MAT 3530-20

9. Database as distinct from: • Flat file • For example, will show php code used for the state capital quiz. This is an example of parallel structures: two arrays, one for the names of the states and one for the names of the capitals. • XML file • Linked lists • Hash table • ? MAT 3530-20

10. A Database • …consists of tables • Tables hold records (= rows) • Records hold fields = attributes (= columns) • A relational database supports fields in a record that point to/link/refer to records in the same or other tables. • Database(s) most probably exist on campus • student table: major, gpa, address. • course table: section, instructor, time, location • enrollments: section & student, semester MAT 3530-20

11. All together now • Database • Tables • Records • Fields • Relationships: fields that refer to records in the same or other tables. MAT 3530-20

12. Database terminology • Primary key: field that uniquely defines a record. Often generated automatically by DBMS • foreign key: field in record in table that ‘is’/points to a record in another database • orderlist = table of orders. Each order includes as one of the fields a customer id. This customer made the order. MAT 3530-20

13. Structured Query Language • Unlike much else in computing, databases follow standards. Everything said so far applies to MySQL, Access, etc. • SQL is … [a] structured query language. • SELECT question, answer, points FROM questions WHERE category = chosencategory • INSERT INTO customers VALUES (fname, lname, billing, email, pass) Syntax (format & punctuation) is tricky! MAT 3530-20

14. MySQL • Open source dbms we will use with php. • (some actions) done using phpmyadmin • May do initial definition of tables • May use for debugging • did my php script put something in my database? • We (our php code) create(s) SQL to access / modify the database MAT 3530-20

15. Systems Design • …refers to functional specification of system (what it should do, not especially how it looks or how it is done) • Use diagrams to specify databases, processes, scripts/Web pages. • tools (computer aided systems engineering=CASE tools) & methodologies exist. We will be less formal. Create diagrams using PowerPoint or any drawing tool. MAT 3530-20

16. Diagrams • Are important! • Will use [at least] 3 types in this course • Entity relationship • Show data and relationships • Data flow • Show agents, programs, data stores • Story board • Connections between programs (scripts) MAT 3530-20

17. Players table player_id Player name Score lastplayed date ER diagram Question databank table question_id Question Answer (answer pattern) Value Category 0 history table question_id player_id whenplayed correct 0 MAT 3530-20

18. Data flow diagram (process diagram) for quiz show Player Questions DB Play game History DB Edit questions Player scores Editor MAT 3530-20

19. Include/Required file: opendbq Create quiz tables (php only) Input Questions (handles form input) Choose category Show scores Ask question Storyboard Check answer Clear tables MAT 3530-20

20. ER diagram for on-line store Customer list Customer ID first name last name Billing information E-mail Password Product catalog Product id Product name Picture Cost 0 0 Order list Order ID Customer ID Date Status Total Ordered items Order ID Product Quantity MAT 3530-20

21. Data flow (process) diagram for on-line store. Catalog Browse/Order Customer Customer list Billing Current orders Ordered items Shipping Billing system (timed event) Shipping clerk Note that this is the information/data flow, not the flow of goods. The shipping operation produces a physical product: the collection of ordered items, packed and set off for delivery. MAT 3530-20

22. Storyboard of partial implementation: ordering Create tables (php only) Include/require: opendbo Input products Delete current customer cookie Order product makeorder Submit order Shopping cart Include/require: displaycartfunction MAT 3530-20

23. Web terminology: standard • Web files are stored on the server computer. • The browser (IE, Firefox, Opera, etc.) is on the client computer. • Hypertext Markup Language (HTML) files are requested by the browser from the server and interpreted by the browser. This could include display of image files, FLASH, etc. • Stateless system: server does not ‘remember’ anything between requests. MAT 3530-20

24. but, stateless-ness wasn’t good enough • … to support real, practical applications involving • files and databases • state information—information valid across multiple pages • Need for so-called middleware / server-side • Alternatives were/are Common Gateway Interface (cgi) programming and Java applets. MAT 3530-20

25. Three-tier implementation model • Code to be run on the client (by browser) • HTML and JavaScript • Code to be run on the server • php • Code (queries) executed by the DBMS • SQL queries constructed by php code MAT 3530-20

26. Three tier logic model • Presentation • Business logic • Information MAT 3530-20

27. extra credit opportunity Server-side / Middleware • Files (aka scripts) ‘in’ PHP (, ASP, cold fusion, etc.) are requested by browser. However, the server processes the PHP instructions in the files to • produce an HTML file for interpretation by the browser and • access & modify data (files, databases) on the server. NOTE: database(s) are on the server! • store & access so-called cookies on the client computer. Cookie is a special, small file. MAT 3530-20

28. Development / testing for class • Create / register to have MySql database • We/you will create html files, swf files PLUS php files • Upload all files to your site (account) • Some php files (aka scripts) will create tables • Some html and php files will populate (put data into) tables • Some html and php files will implement working applications MAT 3530-20

29. Contrast • This is not like general practice of testing on your computer and later uploading complete tested application! MAT 3530-20

30. Objects • General computer science concept • An object (object instance) contains data (attributes, properties) and programs (methods, operations) • Object oriented programming system = OOPS MAT 3530-20

31. PHP • Personal Home Page  PHP: Hypertext Preprocessor • Language plus a set of built-in procedures and properties • language includes support for user-defined objects. • Open Source MAT 3530-20

32. Warnings • SQL is a very powerful language. • It may take time to produce 1 SQL statement • The equivalent of many lines of code in another programming language. • Writing php code, including code generating SQL statements, can involve complex syntax • For example, single quotes within double quotes • References to variables within quoted strings. MAT 3530-20

33. General format of SELECT SELECT [modifier such as DISTINCT] field1, field2, expression [COUNT(field)]FROM tablea, tableb, …WHERE condition(s) GROUP BY fieldxORDER BY fieldyHAVING conditionLIMIT n, m MAT 3530-20

34. $sel="SELECT question_id, question, ans, value from questions "; $sel= $sel . " WHERE category= '" . $pickedcategory. "'"; $result=mysql_db_query($DBname, $sel, $link); $NoR=mysql_num_rows($result); Single quote within double quotes MAT 3530-20

35. Reprise • When we write php, we write • plain HTML • php that uses functions that use operating system on server • read/write files • send email • php that produces HTML • php that sends SQL to MySQL and gets results back that php uses to produce HTML MAT 3530-20

36. Preview • We may use Microsoft Access (and Open Office Base) to demonstrate stand-alone use of a DBMS • Note: previous classes uses Access and asp on the server MAT 3530-20

37. Homework • Visit Moodle course site. • Take First Day survey • Find, briefly review and post assessment of on-line source for PHP NOTE: will repeat this for other topics • Review HTML forms and Flash ActionScript • See my on-line examples MAT 3530-20

38. Homework, cont. • New procedures for obtaining web publishing space AND MySQL accounts. • Sign up for this and save all information. MAT 3530-20