WebDatabase Integration

1
Web-Database Integration

• Week 8
• LBSC 690
• Information Technology

2
Agenda

• Questions
• PHP
• SQL
• Midterm review

3
Ways of Generating Web Pages

• Static Written in a markup language
• HTML, XML
• Dynamic Generated using a program
• Common Gateway Interface (.cgi)
• Java servlets
• Dynamic Generated from a database
• Cold Fusion (.cfm)
• PHP (.php)

4
Why Database-Generated Pages?

• Remote access to a database
• Client does not need the database software
• Serve rapidly changing information
• e.g., Airline reservation systems
• Provide multiple access points
• By subject, by date, by author,
• Record user responses in the database

5
Three Ways to Serve Data
Microsoft Web Server
.mdb
Microsoft Access
Web Browser
Cold Fusion Server
mysql DBMS
mysql database
PHP-enabled Web Server
6
Data Access Pages
7
PHP Examples

• Demo 1 a Hello World program
• Demo 2 Reading input from a form
• Demo 3 Printing output from a table

8
Structured Query Language

• DESCRIBE Flight

9
Structured Query Language

• SELECT FROM Flight

10
Structured Query Language

• SELECT Company.CompanyName, Company.CompanyPhone,
  Flight.Origin, Flight.DepartureTime
• FROM Flight,Company
• WHERE Flight.CompanyNameCompany.CompanyName
• AND Flight.AvailableSeatsgt3

11
The mysql DBMS

• mysql u dbname h host p password
• show databases
• use RentAPlane
• show tables
• describe Flight
• select from Flight
• insert into

12
Cold Fusion

• ltHTMLgt ltHEADgt ltTITLEgtShow All Students in Our
  Schoollt/TITLEgt ltCFQUERY NAMEallStudents
  DATASOURCEstudent20gt
• SELECT FROM Students
• lt/CFQUERYgt
• lt/HEADgt
• ltBODY BGCOLOR"LIGHTBLUE"gt
• ltH2gt ltBgtAll Students in Our Schoollt/Bgt lt/H2gt
• ltBRgt lthrgt ltpregt studentID FirstName MiddleName
  LastNameltbrgtlt/pregt
• ltCFOUTPUT QUERY"allStudents"gt
• ltPREgt
• studentID FirstName MiddleName LastName
  
• lt/PREgt
• lt/CFOUTPUTgt
• ltFORM ACTION"mainMenu.cfm" Method"post"gt
• ltinput type"submit" value"Main Menu"gt
• lt/FORMgt
• lt/BODYgt lt/HTMLgt

Must be the same name
CF variables
13
SQL in ColdFusion

• Show who borrowed which book
• ltCFQUERY name"borrowedBook" dataSource"student20
  "gt
• SELECT Books.Title, Students.FirstName,
  Students.MiddleName, Students.LastName,
  Checkout.DueDate
• FROM Books, Students, Checkout
• WHERE Books.BookIDCheckout.BookID AND
  Students.StudentIDCheckout.StudentID
• lt/CFQUERYgt

14
The Grand Plan
Interaction
Quiz
Midterm
15
The Midterm

• Quiz/homework should be good preparation
• A variety of question types
• Some questions will require computer use
• Lots of prior exams are available
• Many have solutions available
• Open book/notes/Internet/mind/
• Just dont get help from another person

16
Computer Systems

• Hardware
• Types of hardware
• Storage hierarchy
• Moores law
• Software
• Types of software
• Types of interfaces

17
Networks

• Types of Networks
• LAN, WAN, Internet, Wireless
• Packet Switching
• Ethernet, routers, routing tables
• Layered Architecture and protocols
• TCP/UDP
• IP address/domain name
• Encryption

18
Structured Documents

• The Web
• HTTP, HTML, URL
• XML

My Browser
19
Multimedia

• Compression, compression, compression
• Image lossy vs loseless
• Video frames are alike
• Speech voice predictable
• Music masking
• Streaming

Media Sever
Buffer
Internet
20
Human-Computer Interaction

• Input and output devices
• Mental models
• Interaction styles
• Direct manipulation, menus, command line

21
Programming

• Programming languages
• Machines require low-level specific instructions
• Humans require high-level abstraction
• Can create any behavior from 3 control structures
• Sequential execution
• Conditional
• Iteration
• Javascript interpreters are in Web browsers

22
Databases

• Structured information
• Field-gtrecord-gttable-gtdatabase
• Primary key
• Normalized tables (relations)
• Remove redundancy, inconsistency, error
• Easy update, search
• Join links tables together
• Through foreign key
• Access provides visual operations

23
Web-Database Integration

• Access Data Access Pages
• PHP
• SQL