Databases and the Web

1
Databases and the Web

• An Introduction

2
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
3
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

4
How to Integrate Databasesand the Web?

• Databases
• Integration tools

5
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

6
Examples of RDBMS

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

7
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/

8
Application Interface to Databases

• CGI
• Perl DBI
• Perl DBD (DBDmysql)
• ASP
• ODBC (Open DataBase Connectivity)
• A standard for the MS world
• ODBC driver comes with database
• MySQL supplies MyODBC
• Servlets/JSP JDBC

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

10
Relational Database Design

• Logical database design
• Physical database design

11
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

12
Terminology
Term Definition
Entity A thing (person, place, event, etc.) which exists outside of the database and is represented in it
Attribute Describes the properties of a particular entity
Relationship Describes the associations between two or more entities
Normalization Prevents inefficiency by ensuring no duplicated data in multiple tables
13
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

14
Structured Query Language (SQL)

• Standard language for working with relational
  databases
• a type of natural language
• We are going to use Oracle on Borg for the
  examples we will do in the class/tutorials and
  for assignment-4.
• If you want to use something else, you are free
  to do so. However, note that in that case it will
  be your responsibility to solve any problems that
  may come up

15
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

16
Two Categories of SQL Statement

• Data manipulation
• SELECT, INSERT, DELETE
• Data definition
• CREATE DATABASE, DROP DATABASE
• CREATE TABLE, DROP TABLE

17
SQL Statement INSERT

• INSERT INTO table
• (col1, col2, col3, ...)
• VALUES (text1,text2...,num1,..)
• mysqlgt INSERT INTO employee
• -gt (firstname, lastname, address,em_id)
• -gt VALUES(John,Doe,Somewhere,1)

18
SQL Statement DELETE

• DELETE FROM table
• WHERE condition
• mysqlgt DELETE FROM employee
• -gt WHERE lastnameJones

19
SQL Statement SELECT

• SELECT column_list
• FROM table
• WHERE condition
• mysqlgt SELECT from course
• mysqlgt SELECT description
• -gt FROM course
• -gt WHERE title LIKE Using

20
Use SELECT to join tables

• SELECT table1.colx, table2.coly...
• FROM table1, table2
• WHERE condition
• mysqlgt SELECT course.title, course.description,
• -gt teacher.name
• -gt FROM course, teacher
• -gt WHERE course.teacher_IDteacher.teacher_ID

21
Reference

• Programming the Perl DBI
• http//www.oreilly.com/catalog/perldbi/chapter/ch0
  4.html

22
(No Transcript)
23
The End
24
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)
• ltURLhttp//www.prenhall.com/divisions/esm/app/aut
  hor_tanenbaum/custom/dist_sys_1e/gt
• CDK G. Coulouris, J. Dollimore and T. Kindberg
• Distributed System Concepts and Design,
  Addison-Wesley (2001)
• ltURLhttp//www.cdk3.net/ig/beida/index.htmlgt

25
Layered Protocols IP

• Layers, interfaces, and protocols in the Internet
  model.

26
Layered Protocols OSI

• Layers, interfaces, and protocols in the OSI
  model.

2-1
TvS 2.2
27
Middleware Protocols
2-5

• An adapted reference model for networked
  communication.

TvS 2.6
28
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)

29
Middleware

• General structure of a distributed system as
  middleware.

1-22
TvS 1.24
30
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
31
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