WebDatabase Integration

1
Web-Database Integration

• Week 7
• LBSC 690
• Information Technology

2
Agenda

• Using MS Access
• SQL
• PHP
• Midterm review

3
RideFinder Exercise

• Design a database to match drivers with
  passengers (e.g., for road trips)
• Drivers post available seats they want to know
  about interested passengers
• Passengers call up looking for rides they want
  to know about available rides
• These things happen in no particular order

4
One Possible Solution Tables

• Ride Ride ID, Driver ID, Origin, Destination,
  Departure Time, Arrival Time, Available Seats
• Passenger Passenger ID, Name, Address, Phone
  Number
• Driver Driver ID, Name, Address, Phone Number
• Booking Ride ID, Passenger ID

5
One Possible Solution Queries

• Passenger calls Can I get a ride?
• Join Ride, Driver
• Project Departure Time, Name, Phone Number
• Restrict Origin, Destination, Available Seats0
• Driver calls Who are my passengers?
• Join Ride, Passenger, Booking
• Project Name, Phone Number
• Restrict (Driver) Name, Origin, Destination,
  Departure Time

6
Database Programming

• Natural language
• Goal is ease of use
• e.g., Show me the last names of students in CLIS
• Ambiguity sometimes results in errors
• Structured Query Language (SQL)
• Consistent, unambiguous interface to any DBMS
• Simple command structure
• e.g., SELECT Last name FROM Students WHERE
  DeptCLIS
• Useful standard for inter-process communications
• Visual programming (e.g., Microsoft Access)
• Unambiguous, and easier to learn than SQL

7
Using Microsoft Access

• Create a database called M\RideFinder.mdb
• File-New-Blank Database
• Specify the fields (columns)
• Create a Table in Design View
• Fill in the records (rows)
• Double-click on the icon for the table

8
Creating Fields

• Enter field name
• Must be unique, but only within the same table
• Select field type from a menu
• Use date/time for times
• Use text for phone numbers
• Designate primary key (right mouse button)
• Save the table
• Thats when you get to assign a table name

9
Entering Data

• Open the table
• Double-click on the icon
• Enter new data in the bottom row
• A new (blank) bottom row will appear
• Close the table
• No need to save data is stored automatically

10
Building Queries

• Copy N\share\notes\MyRideFinder. to M\
• Create Query in Design View
• In Queries
• Choose two tables, Ride and Driver
• Pick each field you need using the menus
• Unclick show to not project
• Enter a criterion to restrict
• Save, exit, and reselect to run the query

11
Fun Facts about Queries

• Joins are automatic if field names are same
• Otherwise, drag a line between the fields
• Sort order is easy to specify
• Use the menu
• Queries form the basis for reports
• Reports give good control over layout
• Use the report wizard - the formats are complex

12
Structured Query Language (SQL)

• Providing user access to relational DBMS
• Defining the data
• Manipulating the data
• Retrieving data

13
A Normalized Relational Database
Student
Department
Course
Enrollment
14
Basic SELECT Statement
Question List the names and emails of all the
students
SELECT StudentID, Department FROM Student
Student
15
Restriction by WHERE
Question List the names and emails of all the
students in the history
department
SELECT StudentID, Department FROM Student WHERE
DepartmentID HIST
Student Table
16
The SELECT Command

• Syntax
• SELECT column FROM table WHERE column operator
  value
• Project chooses columns
• Based on their label
• Restrict chooses rows
• Based on their contents
• These can be specified together

17
Restrict Operators

• Each SELECT contains a single WHERE
• Numeric comparison
• , , ,
• e.g., grade
• Boolean operations
• e.g., Name John AND Dept HIST

18
Implement Join
Question List the names of all the students
whose lbsc690 grade is larger than 90
SELECT Student.LastName, Student.FirstName FROM
Student WHERE (Student.StudentID
Enrollment.StudentID) AND (Enrollment.CourseID
lbsc690) AND (Enrollment.Grades 90)
Student
Enrollment
19
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)

20
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

21
Three Ways to Serve Data
Microsoft Web Server
.mdb
Microsoft Access
Web Browser
Cold Fusion Server
mysql DBMS
mysql database
PHP-enabled Web Server
22
Hypertext Preprocessor (PHP)

• Server-side scripting language for creating
  dynamic Web pages
• Open source and cross-platform
• Embedded in Web pages

23
PHP Examples
Welcome.html
welcome.php
method"POST" Enter your name type"text" name"name" / Enter your age
type"submit" /
Welcome ?.
You are
years old!
Which outputs Welcome the input name You
are the input age years old!
24
The MySQL DBMS

• C\mysql u dbname h host p password
• mysql show databases
• mysql use MyRideFinder
• mysql show tables
• mysql describe Riding
• mysql select from Driver

25
Interact with MySQL using PHP

• A MySQL database table name, age, phone, email
• A web page for interacting with the MySQL
  database

Name

Age type"text" nameage"
Phone type"text" name"phone"
E-mail type"text" name"email"
type"Submit"
26
insert.php
database"your_database"name_POSTname'
age_POSTage'phone_POST'phone'email
_POST'email' mysql_connect(localhost,userna
me,password)_at_mysql_select_db(database) or
die( "Unable to select database")query
"INSERT INTO contacts VALUES ('','name','age','
phone','email')"mysql_query(query)mysql_clo
se()?
27
Cold Fusion

• Show All Students in Our
  School DATASOURCEstudent20
• SELECT FROM Students
• All Students in Our School
• 
  studentID FirstName MiddleName
  LastName
  
• studentID FirstName MiddleName LastName
  

Must be the same name
CF variables
28
SQL in ColdFusion

• Show who borrowed which book
• "
• SELECT Books.Title, Students.FirstName,
  Students.MiddleName, Students.LastName,
  Checkout.DueDate
• FROM Books, Students, Checkout
• WHERE Books.BookIDCheckout.BookID AND
  Students.StudentIDCheckout.StudentID

29
The Grand Plan
Quiz
Midterm
30
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

31
Computer Systems

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

32
Networks

• Types of Networks
• LAN, WAN, Internet, Wireless
• Packet Switching
• Routers and routing
• Layered Architecture and protocols
• TCP, FTP, and Telnet
• IP address/domain name
• Encryption
• DES and PGP

33
Structured Documents

• The Web
• HTTP, HTML, URL
• XML

My Browser
34
Multimedia

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

Media Sever
Buffer
Internet
35
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

36
Databases

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

37
Web-Database Integration

• Web pages from databases
• SQL
• PHP