Creating Databases for Web applications Introductions overview Administration

Creating Databases for Web applications Introductions & overview Administration Moodle HW: Sign on, register for course, introduce yourself on Moodle. Post source on php.

Introductions • Jeanine Meyer, Mathematics/Computer Science – Ph. D. in Computer Science – IBM Research, IBM Edu. Quest (corporate grants) Pace University Consulting/K-12 Faculty development – books: Multimedia in the Classroom, Programming Games using Visual Basic, Creating Databases for Web Applications with PHP and ASP, Beginning Scripting through Game Creation, The Essential Guide to HTML 5, HTML 5 and Java. Script projects, Elementary Number Theory with Programming – Hobbies/Interests: piano, flute, politics, community volunteer, origami, grandchildren, programming! • You

NOTE • The HTML 5 and Java. Script Projects book (on reserve in Library) has chapters – HTML, php for sending email – HTML, php, My. SQL example

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)

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 and reply postings. – You are required to read postings.

References • There will be assignments to find and describe on-line sources on specific topics. – Unique source, so it pays to do assignment quickly. – You can reply with your opinion. • Many online and off-line reference – Note: my Creating Web Databases with PHP and ASP out-of-date! • Coding examples in book for old php. • though much is relevant: general background on middleware, HTML, SQL. Design of examples. – HTML 5 and Java. Script Projects book better!

Course structure • Each day will include lecture, demonstration, discussion, exercises • Required postings, homework • Team presentations: explanation and enhancements of sample projects • Midterm plus pop quizzes • one original project (can be team) – Initial presentation, with diagrams, on plans – 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; alternatives to SQL; Big Data; tracking; etc. )

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 • (Systems build) Requires middleware=server-side tools. We will use Open Source software (PHP and My. SQL) – Storyboard

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

Examples of things not a relational database • 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 JSON object Linked lists Hash table Key-value pairs

Big Data • partially a marketing term / buzz word • Attributes are – volume -- big – velocity – changes a lot… – variability – greatly variable in formats • Often with the attribute that the organization using it may not control the data • Example: analyzing uses of a term (say, "fiscal cliff") in twitter posts over a set time frame OR last N postings. • Requires mixture of techniques.

A relational 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

All together now • Database – Tables • Records – Fields • Relationships: fields that refer to records in the same or other tables.

Database terminology • Primary key: field that uniquely defines a record. Often generated automatically by DBMS • foreign key: field in record in one table that ‘is’/points to a record in another table • Or to a record in the same table. The field with the foreign key would not be the primary key field. – orderlist = table of orders. Each order includes as one of the fields a customer id. This customer made the order.

Work up example • Information is: courses, sections, enrollments, teachers, schedule, more? • What are the tables? • What are records in each table? • NOTE: it takes time to design a database.

Structured Query Language • Unlike much else in computing, databases follow standards. Everything said so far applies to My. SQL, Access, Oracle, Postgre. SQL 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!

My. SQL • 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

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 Power. Point or any drawing tool.

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)

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

Data flow diagram (process diagram) for quiz show Player History DB Play game Player scores Questions DB Edit questions Editor

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

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

Data flow (process) diagram for on-line store. Customer Catalog Browse/ Order Customer list Current orders Billing 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.

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

Web terminology: standard • Web files are stored on the server computer. • The browser (Firefox, Chrome, 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, playing of video or audio, etc. • Stateless system: server does not ‘remember’ anything between requests.

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.

Three-tier implementation model • Code to be run on the client (by browser) – HTML and Java. Script • Code to be run on the server – php • Code (queries) executed by the DBMS – SQL queries constructed by php code

Three tier logic model • Presentation • Business logic • Information

Cloud computing • extends / replaces 3 tier model. • NOTE: much use of data NOT in an organization's possession or control. – Note: company can use ("rent") storage & processing in "the cloud" and still control/own the data. OR – Use data from other organizations • Example: access Twitter for latest tweets.

HTML 5 • newer features include form validation: specifying what is expected in input elements of forms and promising that browser will perform those checks • Show – http: //faculty. purchase. edu/jeanine. meyer/html 5 logoscale. html Shows slider (range) – http: //faculty. purchase. edu/jeanine. meyer/html 5 /bouncingballinputsimg. html Checks for numbers in the indicated range – http: //faculty. purchase. edu/jeanine. meyer/html 5 /addmessage. html radio buttons. Opera browser supplies color palette

WHOLE COURSE General process • HTML files requested by browser from server to run on client computer. • Files (aka scripts) ‘in’ PHP are requested by browser on client computer to the server computer – Example: action term in a form element • The server processes the PHP instructions in the files to – access & modify data (files, databases) on the server, by generating SQL commands. NOTE: database(s) are on the server! – store & access so-called cookies on the client computer. Cookie is a special, small file (HTML 5 term: local. Storage) – produce an HTML file back to client computer for interpretation by the browser

Terminology • Server side processing • Middleware • Full stack developer – Currently heavily used in job listings • Front end and back end • ?

Development / testing for class • Request database access – In the past, this involved getting accounts from CTS on distinct (virtual) server called socialsoftware. purchase. edu. This MAY not be necessary now. • We/you will create html/Java. Script files and php files • Upload all files to your site (account) • Some php files (aka scripts) will access (read & write) My. Sql database(s) • Some html and php files will populate (put data into) html tables

Contrast • This is not like general practice of testing on your computer and when it is fully debugged, uploading complete tested application!

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

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.

General format of SELECT [modifier such as DISTINCT] field 1, field 2, expression [COUNT(field)] FROM tablea, tableb, … WHERE condition(s) GROUP BY fieldx ORDER BY fieldy HAVING condition LIMIT n, m

$sel="SELECT question_id, question, ans, value from questions "; $sel= $sel. " WHERE category= '". $pickedcategory. "'"; $result=mysql_db_query($DBname, $sel, $link); $No. R=mysql_num_rows($result); Single quote within double quotes

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 My. SQL and gets results back that php uses to produce HTML – The php sections are marked by delimiters

Confirmation on information so far • What is a database? • A database is made up of …. • Describe 3 tier model – operationally (implementation) in terms of [what] code – functionally (logic) in terms of what function

continued… • What does browser do? What does it interpret? • What is done on/by the server? • What is done by the DBMS (My. SQL)?

Homework • Follow CTS instructions on getting database access. • Visit Moodle course site. – Introduce yourself on Introductions, etc. forum. – Find, briefly review and post your assessment of an online source for PHP on appropriate forum on Research Postings tab. • MAKE SURE YOU INSERT A WORKING HYPERLINK. – Must be unique. If someone has done "yours", find another site. You can make a reply posting. – NOTE: will repeat this for other topics