Databases and the Web

Databases and the Web An Introduction

Why is ‘Databases on the Web’ Important? • 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 Credit: Netskills

Examples of Websites Using Databases • Organizational information services • employee directories • Booking & scheduling • airlines, university courses signup • Electronic commerce • Website automation • www.yahoo.com • www.webmonkey.com

How to Integrate Databasesand the Web? • Databases • Integration tools

Databases • Database • an organized collection of data • paper-based • DBMS (database management system) • software to enable user to create and maintain databases • Relational database • organizes data into tables • RDBMS

Examples of RDBMS • MS Access • desktop • MySQL, mSQL • mid-range • Oracle, Sybase, MS SQL Server • large enterprise

How to Integrate Databasesand 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/

CGI DBI DBD::oracle DBD::mysql Application Interface to Databases • CGI • Perl DBI • Perl DBD (DBD::mysql) • ASP • ODBC (Open DataBase Connectivity) • A standard for the MS world • ODBC driver comes with database • MySQL supplies MyODBC • Servlets/JSP — JDBC

Relational Databases • Databases that organize data into tables • Each table has • A name (For identification) • One or more columns (For attributes or fields) • Rows (For entries or records)

Relational Database Design • Logical database design • Physical database design

Logical Database Design(Entity-relationship modeling) • 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

Terminology

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

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

Two Categories of SQL Statement • Data manipulation • SELECT, INSERT, DELETE • Data definition • CREATE DATABASE, DROP DATABASE • CREATE TABLE, DROP TABLE

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);

SQL Statement: DELETE • DELETE FROM table • WHERE condition; • mysql> DELETE FROM employee • -> WHERE lastname=‘Jones’;

SQL Statement: SELECT • SELECT column_list • FROM table • WHERE condition; • mysql> SELECT * from course; • mysql> SELECT description • -> FROM course • -> WHERE title LIKE ‘Using%’;

Use SELECT to join tables • SELECT table1.colx, table2.coly... • FROM table1, table2 • WHERE condition; • mysql> SELECT course.title, course.description, • -> teacher.name • -> FROM course, teacher • -> WHERE course.teacher_ID=teacher.teacher_ID;

Reference • Programming the Perl DBI • http://www.oreilly.com/catalog/perldbi/chapter/ch04.html

The End

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>

Layered Protocols: IP • Layers, interfaces, and protocols in the Internet model.

Layered Protocols: OSI • Layers, interfaces, and protocols in the OSI model. 2-1 TvS 2.2

Middleware Protocols • An adapted reference model for networked communication. 2-5 TvS 2.6

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)

Middleware • General structure of a distributed system as middleware. 1-22 TvS 1.24

Middleware and Openness • 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. 1.23 TvS 1.25

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 End of Aside CDK Ch 1

Databases and the Web