5' ClientServer Databases and Tools

1
5. Client-Server Databases and Tools
2
Client-Server Database (SQL Server)

• A multiuser relational database management system
• Transaction oriented processing
• Security through accounts, permissions and views
• Rules and triggers to enforce integrity
• Examples Oracle, SQLServer, DB2, Informix, Sybase

3
Criteria for Selecting SQL Servers

• Cost
• Openness
• Vendor support and position
• Third party support
• Scalability
• Extra features

4
How Front-end Tools Work
User creates query
ClientSystem
Front-end formats query and sends it to the DBMS
Database server checks users security rights
ServerSystem
Database server processes the query and returns
the results to the front-end
Front-end receives the response and formats it
for the user
ClientSystem
User view and/or manipulate the data
5
Front-end Tools Categories

• Adds on to existing products
• Require more system resources
• Application development tools
• OLTP (on-line transaction processing)
• Programming
• Query/reporting tools
• Nonprogramming
• Data integration and analysis tools
• EIS/DS (executive information/decision support)

6
Few Leading Tools

• Application development tools
• Developer 2000 Oracle
• PowerBuilder Powersoft
• Visual Basic Microsoft
• Access Microsoft
• Data integration and analysis tools
• Forest Trees
• Query/reporting tools

7
Evaluating Front-end Tools

• Easy coding
• Scripting language
• 4GLs
• Object-oriented features
• Call 3GL modules
• Multiprogrammers features
• Local prototyping opportunity
• Off-line development
• Rapid application development

8
... Evaluating Front-end Tools

• Broad back-end support
• Adherence to existing standards
• ODBC
• Accessibility of server-specific features
• Cross-platform development
• Openness
• Integration with existing tools
• Features, not price

9
Database Languages

• Integrated with a programming language
• dBASE Language, Access
• High-Level Database Languages
• SQL
• Declarative
• Set-at-a-time
• Interactive
• Embedded in a host language
• User-friendly Query Languages
• QBE
• 4GL tools

10
SQL

• Structured Query Language
• Originally was called SEQUEL
• The standard language for relational DBMS
• Developed by IBM in 1972
• SQL DDL DML DCL

11
SQL Standards

• Need for standardization
• ANSI/ISO SQL standard
• ANSI 1986
• Level 1
• Level 2
• ANSI X3.135.1 - 1989
• ANSI Integrity Enhancement
• SQL2 (ANSI 1992)
• SQL3 (ANSI 1994)

12
Stored and System Procedures

• create procedure procname paramertes as
  sql_statment
• execute procname

13
Triggers

• A special kind of stored procedure that goes into
  effect when you modify data in a specified table
• Triggers are used to maintain integrity
• create trigger trignameon tablenamefor insert,
  update, deleteas sql_statements
• drop trigger trigname

14
Control-of-Flow Language

• Batches
• go
• IF ... ELSE
• IF Boolean_expression sql_statementELSE
  sql_statement
• BEGIN ... END
• BEGIN statement_blockEND
• WHILE Boolean_expression sql_statement

15
Methods of Accessing a Database

• Ad-hoc access
• Interactive SQL
• 4GL tools
• Programmatic access
• Embedded SQL
• Call Level Interface (CLI)

16
The challenge of Accessing Heterogeneous Databases

• Data resides on different
• DBMS
• Hardware platforms
• Operating systems
• Network systems
• We do not have to wait for fully distributed
  databases, client-server architecture can address
  the heterogeneous database access problem
• Front-end tools can access diverse databases
• Essential to enterprises with heterogeneous
  databases

17
Embedded SQL

• Host language
• Host variables
• Singleton SELECT
• Cursors
• SQL Communication Area (SQLCA)
• Precompiler
• Static SQL
• Dynamic SQL
• EXECUTE IMMEDIATE
• PREPARE, EXECUTE

18
Call Level Interface

• A program communicates with the DBMS through a
  set of function calls called the application
  program interface (API)
• The CLI approach is used in client-server
  architecture
• The code of the API is located on the client
  system
• The SQL API becomes just another library for the
  programmer to learn
• No need for a precompiler

19
Heterogeneous Database Access Issues

• Programming interface
• Embedded SQL or CLI
• Interprocess communication mechanism
• Named pipes, TCP/IP
• Network protocol
• TCP/IP, NetBEUI, SPX/IPX
• System catalog
• SQL syntax and semantics

20
Heterogeneous Database Access Approaches

• Embedded SQL
• The application should be precompiled with each
  back-end server it accesses
• Source Code Compatibility
• The source code must be recompiled for a new
  environment
• Unacceptable solution to software vendors and
  corporates
• CLI
• The ability to connect to various backends on a
  dynamic basis, by loading a new library
• Binary Compatibility
• Dynamic link libraries or database drivers

21
Open Database Connectivity (ODBC)

• Architecture for database connectivity
• Open
• Vendor-neutral
• ODBC interface defines the following
• ODBC API (function calls)
• SQL syntax based on X/Open and SQL CAE specs
• Standard set of error codes
• Standard way to connect to a DBMS
• Standard representation for data types

22
ODBC Goals

• Provide universal database access
• Ease development burden
• Broadens application support for databases
• Built-in scalability for applications
• With ODBC the client application is not required
  to know
• Location of the DBMS
• Communication method
• What vendors DBMS is being used

23
ODBC Architecture
Application
Driver Manager
ODBC Interface
Driver
Driver
Driver
Data Source
Data Source
Data Source
24
Basic Application Steps
CONNECT (db, user, pswd)
SEND (select from ...)
EXECUTE ( )
GETROW ( )
Client Application
DBMS
(101, Dhahran, ...)
GETROW ( )
(101, Khobar, ...)
DISCONNECT ()
25
... Basic Application Steps

• Setting up a data source
• SQLConnect, SQLDriverConnect
• Sending SQL to the data source
• SQLPrepare, SQLExecute, SQLExecuteDirect
• ODBC syntax
• Pass through
• Retrieving results
• SQLFetch
• Cursors
• SQLSetScrollOption, SQLSetPos
• Static, dynamic, Key-set driven

26
6. The System Catalog
27
Catalogs for Relational DBMSs

• The information stored in a catalog includes
• Relation names, attribute names, attribute
  domains, constraints, views, storage
  structure, indexes, security and authorization
  information, optimization statistics
• Catalog information is stored in relations
  (system objects) accessible to authorized users
• Catalog relations can by queried using SQL

28
Data Dictionary (Repository)

• Stand-alone package used for information resource
  management
• Mainly used by the designers, users, and
  administrators of a computer system
• Passive or active