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.

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

3
General objectives

• Learn how to learn
• Practice looking up and using sources
• Learn how to build larger applications
• Make proposals, make and use diagrams
• Work in teams
• Make presentations
• Practice concise writing
• Blog entries, abstracts (1-pagers)

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.

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

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

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 middlewareserver-side
  tools. We will use Open Source software (PHP and
  MySQL)

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

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
• ?

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

11
All together now

• Database
• Tables
• Records
• Fields
• Relationships fields that refer to records in
  the same or other tables.

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.

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!

14
MySQL

• Open source dbms we will use with php.
• (some actions) done at command prompt
• that is, no Graphical User Interface like ACCESS
• We (our php code) creates SQL to access / modify
  the database

15
MySQL command line interface
Will not use this!

• c\mysql\bin mysqld-opt
• c\mysql\bin mysql h localhost u root
• c\mysql\bin use mysql
• c\mysql\bin create database game
• grant select, insert, delete, update
• on game.
• to jeanine_at_localhost identified by 10549

16
Systems Design

• refers to functional specification of system
  (what it should do, not especially how it looks)
• Use diagrams to specify databases, processes,
  scripts/Web pages.
• tools (computer aided systems engineeringCASE
  tools) methodologies exist. We will be less
  formal. Create diagrams using PowerPoint or any
  drawing tool.

17
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)

18
Players table player_id Player name Score lastplay
ed date
ER diagram
Question databank table question_id Question Answe
r (answer pattern) Value Category
0
history table question_id player_id whenplayed cor
rect
0
19
Data flow diagram (process diagram) for quiz show
Player
Questions DB
Play game
History DB
Edit questions
Player scores
Editor
20
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
21
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
22
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.
23
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
24
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.

25
but, this wasnt good enough

• to support real, practical applications
  involving
• files and databases
• state informationinformation valid across
  multiple pages
• Need for so-called middleware / server-side
• Alternatives were/are Common Gateway Interface
  (cgi) programming and Java applets.

26
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

27
Three tier logic model

• Presentation
• Business logic
• Information

28
Server-side / Middleware
extra credit opportunity

• Files (aka scripts) in PHP (, ASP, cold fusion,
  etc.) are requested by browser. However, the
  server processes the 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.

29
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

30
Contrast

• This is not like general practice of testing on
  your computer and later uploading complete tested
  application!

31
Objects

• General computer science concept
• An object (object instance) contains data
  (attributes, properties) and programs (methods,
  operations)
• Object oriented programming system OOPS

32
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

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

34
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

35
sel"SELECT question_id, question, ans, value
from questions " sel sel . " WHERE category
'" . pickedcategory. "'" resultmysql_db_q
uery(DBname, sel, link) NoRmysql_num_rows(
result)
Single quote within double quotes
36
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

37
Preview

• We will 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

38
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