database - Purchase College Faculty Web Server

Download Report

Transcript database - Purchase College Faculty Web Server

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
• 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
• 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
Question databank table
Player name
lastplayed date
Answer (answer pattern)
history table
Fall, 2011
MATNME 3530.45
ER diagram for on-line
Customer list
Customer ID
first name
last name
Billing information
Product catalog
Product id
Product name
Ordered items
Order ID
Fall, 2011
Order list
Order ID
Customer ID
MATNME 3530.45
Reprise: Web terminology:
• 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
• … 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.
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
• 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
Fall, 2011
MATNME 3530.45
• Personal Home Page  PHP: Hypertext
• Language plus a set of built-in procedures
and properties
– language includes support for user-defined
• Open Source
Fall, 2011
MATNME 3530.45
• 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.
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);
Fall, 2011
MATNME 3530.45
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
• What does browser do? What does it
• What is done on/by the server?
• What is done by the DBMS (MySQL)?
Fall, 2011
MATNME 3530.45
• 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:
• 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
• Not neatly organized and maintained in tables.
• See previous slide: Too much for XML
Fall, 2011
MATNME 3530.45
• 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