Database Systems: Architectures, Programming, and Interfaces - EECS 647 Spring 2009 - Prof, Lab Reports of Deductive Database Systems

Download Database Systems: Architectures, Programming, and Interfaces - EECS 647 Spring 2009 - Prof and more Lab Reports Deductive Database Systems in PDF only on Docsity!

EECS 647: Introduction to

Database Systems

Instructor: Luke Huan

Spring 2009

2/25/

Luke Huan Univ. of Kansas

Administrative

z^

Sample codes of embedded SQL programming andAPIs with {C, Perl, JAVA, PHP} are available at classwebpage

z^

Next Monday (March 9

th^ ), we will have class at 1005C

for a lab session

2/25/

Luke Huan Univ. of Kansas

Centralized Architectures

z^

Centralized DBMS: combines everything into singlesystem including- DBMS software, hardware,application programs and user interface processingsoftware.

2/25/

Luke Huan Univ. of Kansas

Two Tier Client-Server Architectures

zServer: provides databasequery and transactionservices to client machines zClient: provideappropriate interfaces toserver.

zRun User Interface (UI)Programs andApplication Programs zConnect to servers vianetwork.

2/25/

Luke Huan Univ. of Kansas

Three (n) Tier Client-Server Architecture

WAN

Clients Webserver ApplicationserversDatabaseservers

z^

The intermediate layer iscalled Application Serveror Web Server, or both: z^

Stores the webconnectivity software andbusiness logic forapplications z^

Acts like a conduit forsending partiallyprocessed data betweenthe database server andthe client. z^

Additional Features z^

Security: encrypt the dataat the server and clientbefore transmission

Intermediate layer

2/25/

Luke Huan Univ. of Kansas

Database Programming: Overview

z^

Pros and cons of SQL z^

Very high-level, possible to optimize z^

Specifically designed for databases and is called

data

sublanguage z^

Not intended for general-purpose computation, which isusually done by a

host language

z^

Solutions z^

Augment SQL with constructs from general-purposeprogramming languages (SQL/PSM) z^

Use SQL together with general-purpose programminglanguages z^

Database APIs, embedded SQL, JDBC, etc.

2/25/

Luke Huan Univ. of Kansas

Clarification of Terms (cont.)

z^

John went to his office. He has a JAVA program, whichconnects to a SqlServer database in his company’sintranet. He use the program to retrieve data and printout reports for his business partner. z^

Client-server model z^

Use APIs provided by SqlServer to access the database z^

Java supports SqlServer API using JDBC

2/25/

Luke Huan Univ. of Kansas

Clarification of Terms (cont.)

z^

After job, John went to youtube.com, searched for avideo of Thomas train for his children, and downloadedone z^

Client-mediate level-sever model z^

“SQL experience a plus” from a job ad linked from youtube’s web site.

WAN

2/25/

Luke Huan Univ. of Kansas

A Typical Flow of Interactions

z^

A client (user interface, web server, application server)opens a connection to a database server

z^

A client interact with the database server to performquery, update, or other operations.

z^

A client terminate the connection

2/25/

Luke Huan Univ. of Kansas

Augmenting SQL: SQL/PSM

z^

PSM = Persistent Stored Modules z^

CREATE PROCEDURE

proc_name

(

parameter_declarations

) local_declarationsprocedure_body

;

z^

CREATE FUNCTION

func_name

(

parameter_declarations

) RETURNS

return_type

local_declarationsprocedure_body

;

z^

CALL

proc_name

(

parameters

);

z^

Inside procedure body: SET

variable

= CALL

func_name

(

parameters

);

2/25/

Luke Huan Univ. of Kansas

SQL/PSM example continued

Fetch the first result row: OPEN studentCursor;FETCH FROM studentCursor INTO thisGPA;--

Loop over all result rows: WHILE noMoreRows <> 1 DO

IF thisGPA > newMaxGPA THEN

Enforce

newMaxGPA

UPDATE Student SET Student.GPA = newMaxGPAWHERE CURRENT OF studentCursor;--

Update count: SET rowsUpdated = rowsUpdated + 1; END IF;--

Fetch the next result row: FETCH FROM studentCursor INTO thisGPA; END WHILE;CLOSE studentCursor;

2/25/

Luke Huan Univ. of Kansas

Other SQL/PSM features

z^

Assignment using scalar query results z^

SELECT INTO

z^

Other loop constructs z^

FOR

,^ REPEAT

UNTIL

,^ LOOP

z^

Flow control z^

GOTO

z^

Exceptions z^

SIGNAL

,^ RESIGNAL

… z^

For more pgSQL-specific information, check out itsmanual athttp://www.postgresql.org/docs/8.2/interactive/plpgsql.html

2/25/

Luke Huan Univ. of Kansas

Example PHP

z^

// Connect to the database $dbconn = pg_connect("host= wozniak.eecs.ku.edu port=5432dbname=jhuan user=jhuan password=zzzzz") ordie('Could not connect: '. pg_last_error()); z^

//create a table $query = "CREATE TABLE regiusers ( name varchar(50),passwd

varchar(50) )";

z^

// Execute the Query $query = pg_query($query);… (see next page) z^

// Closing connection pg_close($dbconn); z^

2/25/

Luke Huan Univ. of Kansas

Example PHP (cont.)

z^

//performing update $ldata['name'] = 'a2';

$ldata['passwd'] = 'b2';

$res = pg_insert($dbconn, "regiusers", $ldata); z^

// Performing SQL query $query = 'SELECT * FROM regiusers';$result = pg_query($query) or die('Query failed: ' .pg_last_error()); z^

// analyzing results while ($line = pg_fetch_array($result, null, PGSQL_ASSOC)){

foreach ($line as $col_value) {

statement; } z^

// Free resultset pg_free_result($result);