Databases and the Web An Introduction NTW 2000

Databases and the Web An Introduction NTW 2000 -T 3 8 A-1

Why is ‘Databases on the Web’ Important? l l l NTW 2000 -T 3 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 l updating l finding information 8 A-2 Credit: Netskills

Examples of Websites Using Databases l l Organizational information services l employee directories Booking & scheduling l airlines, university courses signup Electronic commerce Website automation l l NTW 2000 -T 3 www. yahoo. com www. webmonkey. com 8 A-3

How to Integrate Databases and the Web? l l NTW 2000 -T 3 Databases Integration tools 8 A-4

Databases l Database l an organized collection of data – paper-based l DBMS (database management system) – software to enable user to create and maintain databases l Relational database l l NTW 2000 -T 3 organizes data into tables RDBMS 8 A-5

Examples of RDBMS l MS Access l l My. SQL, m. SQL l l mid-range Oracle, Sybase, MS SQL Server l NTW 2000 -T 3 desktop large enterprise 8 A-6

How to Integrate Databases and the Web? l Databases l l MS Access My. SQL, m. SQL Oracle, Sybase, MS SQL Server Integration tools l l PHP or CGI, Servlets, JSP, ASP etc. “Middleware”: e. g. Cold. Fusion http: //www. allaire. com/ NTW 2000 -T 3 8 A-7

Application Interface to Databases l CGI l l l CGI Perl DBD (DBD: : mysql) ASP l DBI DBD: : mysql DBD: : oracle ODBC (Open Data. Base Connectivity) – A standard for the MS world l ODBC driver comes with database – My. SQL supplies My. ODBC l NTW 2000 -T 3 Servlets/JSP — JDBC 8 A-8

Relational Databases that organize data into tables l NTW 2000 -T 3 Each table has – A name (For identification) – One or more columns (For attributes or fields) – Rows (For entries or records) 8 A-9

Relational Database Design l l NTW 2000 -T 3 Logical database design Physical database design 8 A-10

Logical Database Design (Entity-relationship modeling) l l l NTW 2000 -T 3 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 8 A-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 NTW 2000 -T 3 8 A-12

Physical Database Design l l Entities become tables Attributes become columns choose appropriate data type for each column l l Unique identifiers become primary keys Relationships are modeled as foreign keys Foreign keys can be primary keys from other tables NTW 2000 -T 3 8 A-13

Structured Query Language (SQL) Standard language for working with relational databases l A type of ‘natural’ language l You may not have to write any code l l 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 NTW 2000 -T 3 8 A-15

Two Categories of SQL Statement 1. Data manipulation • 2. SELECT, INSERT, DELETE Data definition • • NTW 2000 -T 3 CREATE DATABASE, DROP DATABASE CREATE TABLE, DROP TABLE 8 A-16

SQL Statement: INSERT INTO table (col 1, col 2, col 3, . . . ) VALUES (‘text 1’, ’text 2’. . . , num 1, . . ); mysql> INSERT INTO employee -> (firstname, lastname, address, em_id) -> VALUES(‘John’, ’Doe’, ’Somewhere’, 1); NTW 2000 -T 3 8 A-17

SQL Statement: DELETE FROM table WHERE condition; mysql> DELETE FROM employee -> WHERE lastname=‘Jones’; NTW 2000 -T 3 8 A-18

SQL Statement: SELECT column_list FROM table WHERE condition; mysql> SELECT * from course; mysql> SELECT description -> FROM course -> WHERE title LIKE ‘Using%’; NTW 2000 -T 3 8 A-19

Use SELECT to join tables SELECT table 1. colx, table 2. coly. . . FROM table 1, table 2 WHERE condition; mysql> -> -> -> NTW 2000 -T 3 SELECT course. title, course. description, teacher. name FROM course, teacher WHERE course. teacher_ID=teacher_ID; 8 A-20

Reference Programming the Perl DBI http: //www. oreilly. com/catalog/perldbi/chapter/ch 04. html NTW 2000 -T 3 8 A-21

NTW 2000 -T 3 8 A-22

The End NTW 2000 -T 3 8 A-23

Aside: Middleware Adapted from Introduction to Distributed Systems: Slides for CSCI 3171 Lectures by E. W. Grundke References: l [Tv. S] 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_1 e/> l [CDK] G. Coulouris, J. Dollimore and T. Kindberg Distributed System: Concepts and Design, Addison-Wesley (2001) <URL: http: //www. cdk 3. net/ig/beida/index. html> NTW 2000 -T 3 8 A-24

Layered Protocols: IP Layers, interfaces, and protocols in the Internet model. NTW 2000 -T 3 8 A-25

Layered Protocols: OSI Layers, interfaces, and protocols in the OSI model. 2 -1 Tv. S 2. 2 NTW 2000 -T 3 8 A-26

Middleware Protocols 2 -5 An adapted reference model for networked communication. Tv. S 2. 6 NTW 2000 -T 3 8 A-27

Middleware A software layer that l masks the heterogeneity of systems l provides a convenient programming abstraction l provides protocols for providing general-purpose services to more specific applications, e. g. l l l authentication protocols authorization protocols distributed commit protocols distributed locking protocols high-level communication protocols – remote procedure calls (RPC) – remote method invocation (RMI) NTW 2000 -T 3 8 A-28

Middleware General structure of a distributed system as middleware. 1 -22 Tv. S 1. 24 NTW 2000 -T 3 8 A-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. Tv. S 1. 25 NTW 2000 -T 3 8 A-30

Middleware programming models l. Remote t t Calls remote Procedure Calls (RPC) distributed objects and Remote Method Invocation (RMI) v e. g. Java RMI l. Common t Object Request Broker Architecture (CORBA) cross-language RMI l. Other t t t programming models remote event notification remote SQL access distributed transaction processing CDK Ch 1 NTW 2000 -T 3 8 A-31 End of Aside