Transcript database - Purchase College Faculty Web Server

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
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
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
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
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
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
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
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
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
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
All together now
• Database
– Tables
• Records
– Fields
• Relationships: fields that refer to records in
the same or other tables.
MAT 3530-20
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
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
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
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
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
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
MAT 3530-20
correct
Data flow diagram (process
diagram) for quiz show
Player
History DB
Play
game
Player scores
Questions DB
Edit questions
Editor
MAT 3530-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
MAT 3530-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
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.
MAT 3530-20
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
MAT 3530-20
Include/require:
displaycartfunction
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
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
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
Three tier logic model
• Presentation
• Business logic
• Information
MAT 3530-20
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
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
Contrast
• This is not like general practice of testing
on your computer and later uploading
complete tested application!
MAT 3530-20
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
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
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
General format of SELECT
SELECT [modifier such as DISTINCT]
field1, field2, expression [COUNT(field)]
FROM tablea, tableb, …
WHERE condition(s)
GROUP BY fieldx
ORDER BY fieldy
HAVING condition
LIMIT n, m
MAT 3530-20
$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);
MAT 3530-20
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 MySQL and gets results back
that php uses to produce HTML
MAT 3530-20
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
Homework
• Visit Moodle course site.
– Take First Day survey
• Find, briefly review and post assessment of online source for PHP
NOTE: will repeat this for other topics
• Review HTML forms and Flash ActionScript
– See my on-line examples
MAT 3530-20
Homework, cont.
• New procedures for obtaining web
publishing space AND MySQL accounts.
• Sign up for this and save all information.
MAT 3530-20