1 / 38

Creating Databases for Web applications

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. Introductions. Jeanine Meyer, Math/Computer Science & New Media. Ph.D. in Computer Science

takoda
Télécharger la présentation

Creating Databases for Web applications

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. 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

More Related