Transcript database - Purchase College Faculty Web Server

Topics
Quick overview on relational
databases and XML.
HW: [quick] research & post on SQL
or XML.
Fall, 2011
MATNME 3530.45
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 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
Fall, 2011
MATNME 3530.45
Relational 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
?
Fall, 2011
MATNME 3530.45
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
Fall, 2011
MATNME 3530.45
All together now
• Database
– Tables
• Records
– Fields
• Relationships: fields that refer to records in
the same or other tables.
Fall, 2011
MATNME 3530.45
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.
Fall, 2011
MATNME 3530.45
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.
Fall, 2011
MATNME 3530.45
Structured Query Language
• Unlike much else in computing, databases follow
standards. Everything said so far applies to
MySQL, Access, Oracle, PostgreSQL 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!
Fall, 2011
MATNME 3530.45
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
Fall, 2011
MATNME 3530.45
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
Fall, 2011
correct
MATNME 3530.45
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
Ordered items
Order ID
Product
Quantity
Fall, 2011
Order list
Order ID
Customer ID
Date
Status
Total
MATNME 3530.45
Reprise: 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.
Fall, 2011
MATNME 3530.45
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.
Fall, 2011
MATNME 3530.45
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
Fall, 2011
MATNME 3530.45
Three tier logic model
• Presentation
• Business logic
• Information
Fall, 2011
MATNME 3530.45
Server-side / Middleware
extra credit
opportunity
• Files (aka scripts) ‘in’ PHP (, ASP, cold fusion,
ruby on rails, perl, 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.
• HTML5 term: localStorage
• Flash term: sharedstorage
Fall, 2011
MATNME 3530.45
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
Fall, 2011
MATNME 3530.45
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.
Fall, 2011
MATNME 3530.45
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
Fall, 2011
MATNME 3530.45
$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);
Fall, 2011
MATNME 3530.45
Single
quote
within
double
quotes
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
Fall, 2011
MATNME 3530.45
continued…
• What does browser do? What does it
interpret?
• What is done on/by the server?
• What is done by the DBMS (MySQL)?
Fall, 2011
MATNME 3530.45
XML
• eXtended Markup Language
• General term for anything with properly
nested tags.
• Elements defined by
– opening tag, possibly containing attributes
– contents of element
– closing tag
Fall, 2011
MATNME 3530.45
Example: XHTML
• refers to proper HTML
• What's not proper?
– browser will accept HTML that is not wellnested, missing quotation marks, etc.
– <p> This next is <b> <i> bold </b> </i>
Fall, 2011
MATNME 3530.45
Example: SVG
• scalar vector graphics
• viewsource:http://faculty.purchase.edu/jeanine.m
eyer/html5/html5logoscalesvg.html
• Will do more of this later
Fall, 2011
MATNME 3530.45
Example: anything …
… that people agree to
• RSS feeds
• various Web services
Fall, 2011
MATNME 3530.45
Note on XML
• XML is all text.
• Good news: pretty much human readable
• Bad news: requires processing to use it. So,
not appropriate for large amounts of data
and/or data requiring speed.
Fall, 2011
MATNME 3530.45
So what's the problem?
Big Data (next topic)
• refers to data that is (has)
– volume
– velocity
– variability
• Generally is not owned by one
company/organization.
• Not neatly organized and maintained in tables.
• See previous slide: Too much for XML
Fall, 2011
MATNME 3530.45
Homework
• For this week's posting, find
–
–
–
–
–
unique source on limits on relational databases
XML example
comparison of XML and relational databases
any related topic
Big Data
• READ the information and write summary
in your own words!!!
Fall, 2011
MATNME 3530.45