Transcript Netskills PowerPoint Template (ver 7.0)

Databases and the Web
An Introduction
NTW2000-T3
8A-1
Why is ‘Databases on the
Web’ Important?



NTW2000-T3
Databases are established technology
for managing large amounts of data
The Web is a good way to present
information
Separating data management from
presentation improves efficiency
 updating
 finding information
8A-2
Credit: Netskills
Examples of Websites
Using Databases




Organizational information services
 employee directories
Booking & scheduling
 airlines, university courses signup
Electronic commerce
Website automation


NTW2000-T3
www.yahoo.com
www.webmonkey.com
8A-3
How to Integrate Databases
and the Web?


NTW2000-T3
Databases
Integration tools
8A-4
Databases

Database

an organized collection of data
– paper-based

DBMS (database management system)
– software to enable user to create and maintain databases

Relational database


NTW2000-T3
organizes data into tables
RDBMS
8A-5
Examples of RDBMS

MS Access


MySQL, mSQL


mid-range
Oracle, Sybase, MS SQL Server

NTW2000-T3
desktop
large enterprise
8A-6
How to Integrate Databases
and the Web?

Databases




MS Access
MySQL, mSQL
Oracle, Sybase, MS SQL Server
Integration tools


PHP or CGI, Servlets, JSP, ASP etc.
“Middleware”: e.g. ColdFusion
http://www.allaire.com/
NTW2000-T3
8A-7
Application Interface to
Databases

CGI



CGI
Perl DBI
Perl DBD (DBD::mysql)
DBI
DBD::mysql DBD::oracle
ASP

ODBC (Open DataBase Connectivity)
– A standard for the MS world

ODBC driver comes with database
– MySQL supplies MyODBC

NTW2000-T3
Servlets/JSP — JDBC
8A-8
Relational Databases

Databases that organize data into tables

NTW2000-T3
Each table has
– A name
(For identification)
– One or more columns
(For attributes or fields)
– Rows
(For entries or records)
8A-9
Relational Database Design


NTW2000-T3
Logical database design
Physical database design
8A-10
Logical Database Design
(Entity-relationship modeling)





NTW2000-T3
Identify and model the entities
Identify and model the relationships
between the entities
Identify and model the attributes
Create unique identifier for each entity
Normalize
8A-11
Terminology
Term
Definition
Entity
A thing (person, place, event, etc.) which exists
outside of the database and is represented in it
Describes the properties of a particular entity
Attribute
Relationship
Describes the associations between two or more
entities
Normalization Prevents inefficiency by ensuring no duplicated data
in multiple tables
NTW2000-T3
8A-12
Physical Database Design


Entities become tables
Attributes become columns
choose appropriate data type for each
column


Unique identifiers become primary keys
Relationships are modeled as foreign keys
Foreign keys can be primary keys from other
tables
NTW2000-T3
8A-13
Structured Query Language (SQL)
Standard language for working with relational
databases
 A type of ‘natural’ language
 You may not have to write any code


There are tools for that e.g Access query tool
But necessary to understand basics, as
SQL is common to all nearly all the tools
covered today
NTW2000-T3
8A-15
Two Categories of SQL Statement
1.
Data manipulation
•
2.
SELECT, INSERT, DELETE
Data definition
•
•
NTW2000-T3
CREATE DATABASE, DROP DATABASE
CREATE TABLE, DROP TABLE
8A-16
SQL Statement: INSERT
INSERT INTO table
(col1, col2, col3, ...)
VALUES (‘text1’,’text2’...,num1,..);
mysql> INSERT INTO employee
-> (firstname, lastname, address,em_id)
-> VALUES(‘John’,’Doe’,’Somewhere’,1);
NTW2000-T3
8A-17
SQL Statement: DELETE
DELETE FROM table
WHERE condition;
mysql> DELETE FROM employee
-> WHERE lastname=‘Jones’;
NTW2000-T3
8A-18
SQL Statement: SELECT
SELECT column_list
FROM table
WHERE condition;
mysql> SELECT * from course;
mysql> SELECT description
-> FROM course
-> WHERE title LIKE ‘Using%’;
NTW2000-T3
8A-19
Use SELECT to join tables
SELECT table1.colx, table2.coly...
FROM table1, table2
WHERE condition;
mysql>
->
->
->
NTW2000-T3
SELECT course.title, course.description,
teacher.name
FROM course, teacher
WHERE course.teacher_ID=teacher.teacher_ID;
8A-20
Reference
Programming the Perl DBI
http://www.oreilly.com/catalog/perldbi/chapter/ch04.html
NTW2000-T3
8A-21
NTW2000-T3
8A-22
The End
NTW2000-T3
8A-23
Aside: Middleware
Adapted from Introduction to Distributed Systems: Slides for
CSCI 3171 Lectures by E. W. Grundke
References:
 [TvS] A. Tanenbaum and M. van Steen
Distributed Systems: Principles and Paradigms, Prentice-Hall (2002)
<URL:http://www.prenhall.com/divisions/esm/app/author_tanenbaum/custom/dist_sys_1e/>

[CDK] G. Coulouris, J. Dollimore and T. Kindberg
Distributed System: Concepts and Design, Addison-Wesley (2001)
<URL:http://www.cdk3.net/ig/beida/index.html>
NTW2000-T3
8A-24
Layered Protocols: IP
Layers, interfaces, and protocols in the Internet model.
NTW2000-T3
8A-25
Layered Protocols: OSI
Layers, interfaces, and protocols in the OSI model.
2-1
TvS 2.2
NTW2000-T3
8A-26
Middleware Protocols
2-5
An adapted reference model for networked
communication.
TvS 2.6
NTW2000-T3
8A-27
Middleware
A software layer that
 masks the heterogeneity of systems
 provides a convenient programming abstraction
 provides protocols for providing general-purpose
services to more specific applications, e.g.





authentication protocols
authorization protocols
distributed commit protocols
distributed locking protocols
high-level communication protocols
– remote procedure calls (RPC)
– remote method invocation (RMI)
NTW2000-T3
8A-28
Middleware
General structure of a distributed system as middleware.
1-22
TvS 1.24
NTW2000-T3
8A-29
Middleware and Openness
1.23
In an open middleware-based distributed system, the protocols
used by each middleware layer should be the same, as well as
the interfaces they offer to applications.
TvS 1.25
NTW2000-T3
8A-30
Middleware programming models
Remote


Calls
remote Procedure Calls (RPC)
distributed objects and Remote Method Invocation (RMI)

e.g. Java RMI
Common

Object Request Broker Architecture (CORBA)
cross-language RMI
Other



programming models
remote event notification
remote SQL access
distributed transaction processing
CDK Ch 1
NTW2000-T3
8A-31
End of Aside