database - Purchase College Faculty Web Server
Transcript database - Purchase College Faculty Web Server
Quick overview on relational
databases and XML.
HW: [quick] research & post on SQL
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 is the product/tool: MySQL, Access, (Oracle,
– The specific database, for example, the [toy] database
examples for this course, are implemented using a
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.
• …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
• 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
• Relationships: fields that refer to records in
the same or other tables.
• Primary key: field that uniquely defines a
record. Often generated automatically by
• 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.
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
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!
• 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
Question databank table
Answer (answer pattern)
ER diagram for on-line
Reprise: Web terminology:
• Web files are stored on the server computer.
• The browser (IE, Firefox, Opera, etc.) is on the
• 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.
but, stateless-ness wasn’t good
• … to support real, practical applications involving
– files and databases
– state information—information valid across multiple
• 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)
• Code to be run on the server
• Code (queries) executed by the DBMS
– SQL queries constructed by php code
Three tier logic model
• Business logic
Server-side / Middleware
• 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
– 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
• Personal Home Page PHP: Hypertext
• Language plus a set of built-in procedures
– language includes support for user-defined
• Open Source
• 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
• 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
SELECT [modifier such as DISTINCT]
field1, field2, expression [COUNT(field)]
FROM tablea, tableb, …
GROUP BY fieldx
ORDER BY fieldy
LIMIT n, m
$sel="SELECT question_id, question, ans, value from questions ";
$sel= $sel . " WHERE category= '" . $pickedcategory. "'";
$result=mysql_db_query($DBname, $sel, $link);
Confirmation on information so far
• What is a database?
• A database is made up of ….
• Describe 3 tier model
– operationally (implementation) in terms of
– functionally (logic) in terms of what function
• What does browser do? What does it
• What is done on/by the server?
• What is done by the DBMS (MySQL)?
• eXtended Markup Language
• General term for anything with properly
• Elements defined by
– opening tag, possibly containing attributes
– contents of element
– closing tag
• 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>
• scalar vector graphics
• Will do more of this later
Example: anything …
… that people agree to
• RSS feeds
• various Web services
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.
So what's the problem?
Big Data (next topic)
• refers to data that is (has)
• Generally is not owned by one
• Not neatly organized and maintained in tables.
• See previous slide: Too much for XML
• For this week's posting, find
unique source on limits on relational databases
comparison of XML and relational databases
any related topic
• READ the information and write summary
in your own words!!!