FULL STACK CODING PROGRAM

JOIN OUR PROGRAM AND BUILD YOUR CAREER IN TECH WITH EDULADDER.


NEW JOINEES WILL RECIVE CRYPTO WORTH OF RS 5000/-.


WHITE PAPER APPLY NOW HERE

Real Problems! Real Experts!

An unparalleled, New approach On creative thinking and problem solving.


The Eduladder is a community of students, teachers, and programmers just interested to make you pass any exams. So we help you to solve your academic and programming questions fast.
In eduladder you can Ask,Answer,Listen,Earn and Download Questions and Question papers.
Watch related videos of your favorite subject.
Connect with students from different parts of the world.
Apply or Post Jobs, Courses ,Internships and Volunteering opportunity. For FREE
See Our team
Wondering how we keep quality?
Got unsolved questions? Ask Questions


You are here:Open notes-->VTU-->Database-Management-System-10CS54-VTU-unit-5

Database Management System 10CS54 VTU unit-5

UNIT 5 SQL The Relational Database Standard

5.1 Update Statements in SQL 

The Insert Command 
INSERT INTO EMPLOYEE 
VALUES (‘Richard’,’K’,’Marini’,653298653’,’30dec52’,98 Oak Forest, Katy, 
TX’,’M’,37000,’987654321’,4) 
More on Insert 
 Use explicit attribute names: 
 INSERT INTO EMPLOYEE (FNAME, LNAME,SSN) 
 VALUES (‘Richard’,’Marini’, ‘653298653’ 
The DELECT Command 
DELETE FROM EMPLOYEE
WHERE LNAME=‘Brown’
The UPDATE Command 
Used to modify values of one or more selected tuples 
Change the location and controlling department number of project number 10 to ‘Bellaire’ and 5 
respectively 
UPDATE PROJECT 
SET PLOCATION = ‘Bellaire’, DNUM=5 
Where PNUMBER=10; 
5.2 Views in SQL 
A view refers to a single table that is derived from other tables 
CREATE VIEW WORKS_ON1
AS SELECT FNAME, LNAME, PNAME, HOURS 
FROM EMPLOYEE, PROJECT, WORKS_ON WHERE SSN=ESSN AND PNO=PNUMBER 
More on View 
CREATE VIEW DEPT_INFO(DEPT_NAME, NO_OF_EMPLS, TOTAL_SAL) 
AS SELECT DNAME, COUNT(*), SUM(SALARY) 
FROM DEPARTMENT, EMPLOYEE 
WHERE DNUMBER=DNO 
GROUP BY DNAME 
More on view 
Treat WORKS_ON1 like a base table as follows 
SELECT FNAME, LNAME 
FROM WORKS_ON1 
WHERE PNMAE=‘PROJECTX’ 
Main advantage of view: 
Simplify the specification of commonly used queries 
More on View 
A View is always up to date; 
A view is realized at the time we specify(or execute) a query on the view 
DROP VIEW WORKS_ON1

Updating of Views 

Updating the views can be complicated and ambiguous 
In general, an update on a view on defined on a single table w/o any aggregate functions can be 
mapped to an update on the base table 
More on Views 
We can make the following observations: 
A view with a single defining table is updatable if we view contain PK or CK of the base table 
View on multiple tables using joins are not updatable 
View defined using grouping/aggregate are not updatable 
Specifying General Constraints 
Users can specify certain constraints such as semantics constraints 
CREATE ASSERTION SALARY_CONSTRAINT 
CHECK ( NOT EXISTS ( SELECT * FROM EMPLOYEE E, EMPLOYEE M, 
DEPARTMENT D
WHERE E.SALARY > M. SALARY AND E.DNO=D.NUMBER AND D.MGRSSN=M.SSN))

5.3 Additional features

Granting and revoking privileges
Embedding SQL statements in a general purpose languages (C, C++, COBOL, PASCAL)
SQL can also be used in conjunction with a general purpose programming language, such as 
PASCAL, COBOL, or PL/I. The programming language is called the host language. The 
embedded SQL statement is distinguished from programming language statements by prefixing 
it with a special character or command so that a preprocessor can extract the SQL statements. In 
PL/I the keywords EXEC SQL precede any SQL statement. In some implementations, SQL 
statements are passed as parameters in procedure calls. We will use PASCAL as the host 
programming language, and a "$" sign to identify SQL statements in the program. Within an 
embedded SQL command, we may refer to program variables, which are prefixed by a "%" sign. 
The programmer should declare program variables to match the data types of the database 
attributes that the program will process.These program variables may or may not have names 
that are identical to their corresponding attributes.
Example: Write a program segment (loop) that reads a social security number and prints out 
some information from the corresponding EMPLOYEE tuple
E1: LOOP:= 'Y';
while LOOP = 'Y' do
begin
writeln('input social security number:');
readln(SOC_SEC_NUM);
$SELECT FNAME, MINIT, LNAME, SSN, BDATE,
ADDRESS, SALARY
 INTO %E.FNAME, %E.MINIT, %E.LNAME, %E.SSN,
 %E.BDATE, %E.ADDRESS, %E.SALARY
 FROM EMPLOYEE
 WHERE SSN=%SOC_SEC_NUM ;
writeln( E.FNAME, E.MINIT, E.LNAME, E.SSN, 
E.BDATE, E.ADDRESS, E.SALARY);
writeln('more social security numbers (Y or N)? ');
readln(LOOP)
end;
In E1, a single tuple is selected by the embedded SQL query; that is why we are able to assign 
its attribute values directly to program variables. In general, an SQL query can retrieve many 
tuples. The concept of a cursor is used to allow tupleatatime processing by the PASCAL 
programCURSORS: We can think of a cursor as a pointer that points to a single tuple (row)
from the result of a query.The cursor is declared when the SQL query command is specified. A 
subsequent OPEN cursor command fetches the query result and sets the cursor to a position 
before the first row in the result of the query; this becomes the current row for the cursor. 
Subsequent FETCH commands in the program advance the cursor to the next row and copy its 
attribute values into PASCAL program variables specified in the FETCH command. An implicit 
variable SQLCODE communicates to the program the status of SQL embedded commands. An 
SQLCODE of 0 (zero) indicates successful execution. Different codes are returned to indicate 
exceptions and errors. A special END_OF_CURSOR code is used to terminate a loop over the 
tuples in a query result. A CLOSE cursor command is issued to indicate that we are done with 
the result of the query
When a cursor is defined for rows that are to be updated the clause FOR UPDATE OF must be 
in the cursor declaration, and a list of the names of any attributes that will be updated 
follows.The condition WHERE CURRENT OF cursor specifies that the current tuple is the one 
to be updated (or deleted)
Example: Write a program segment that reads (inputs) a department name, then lists the names 
of employees who work in that department, one at a time. The program reads a raise amount for 
each employee and updates the employee's salary by that amount.
E2: writeln('enter the department name:'); readln(DNAME);
$SELECT DNUMBER INTO %DNUMBER
 FROM DEPARTMENT
 WHERE DNAME=%DNAME;
$DECLARE EMP CURSOR FOR
SELECT SSN, FNAME, MINIT, LNAME, SALARY
FROM EMPLOYEE
WHERE DNO=%DNUMBER
 FOR UPDATE OF SALARY;
$OPEN EMP;
$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,
 %E.LNAME, %E.SAL;
while SQLCODE = 0 do

begin
writeln('employee name: ', E.FNAME, E.MINIT, E.LNAME);
writeln('enter raise amount: '); readln(RAISE);
$UPDATE EMPLOYEE SET SALARY = SALARY + %RAISE
WHERE CURRENT OF EMP;
$FETCH EMP INTO %E.SSN, %E.FNAME, %E.MINIT,
 %E.LNAME, %E.SAL;
end;
$CLOSE CURSOR EMP;
5.4 Database Programming
 Objective: 
 To access a database from an application program (as opposed to interactive 
interfaces)
 Why?
 An interactive interface is convenient but not sufficient
 A majority of database operations are made thru application programs 
(increasingly thru web applications)
 Embedded commands:
 Database commands are embedded in a general-purpose programming language
 Library of database functions:
 Available to the host language for database calls; known as an API
 API standards for Application Program Interface
 A brand new, full-fledged language
 Minimizes impedance mismatch
Impedance Mismatch
 Incompatibilities between a host programming language and the database model, e.g.,
 type mismatch and incompatibilities; requires a new binding for each language
 set vs. record-at-a-time processing
 need special iterators to loop over query results and manipulate individual 
values
 Client program opens a connection to the database server
 Client program submits queries to and/or updates the database
 When database access is no longer needed, client program closes (terminates) the 
connection
5.5 Embedded SQL
 Most SQL statements can be embedded in a general-purpose host programming language 
such as COBOL, C, Java
 An embedded SQL statement is distinguished from the host language statements by 
enclosing it between EXEC SQL or EXEC SQL BEGIN and a matching END-EXEC or 
EXEC SQL END (or semicolon)

 Syntax may vary with language
 Shared variables (used in both languages) usually prefixed with a colon (:) in SQL
 Variables inside DECLARE are shared and can appear (while prefixed by a colon) in 
SQL statements
 SQLCODE is used to communicate errors/exceptions between the database and the 
program
int loop;
EXEC SQL BEGIN DECLARE SECTION;
varchar dname[16], fname[16], …;
char ssn[10], bdate[11], …;
int dno, dnumber, SQLCODE, …;
EXEC SQL END DECLARE SECTION;
 Connection (multiple connections are possible but only one is active)
CONNECT TO server-name AS connection-name
AUTHORIZATION user-account-info;
 Change from an active connection to another one
SET CONNECTION connection-name;
 Disconnection
DISCONNECT connection-name;
loop = 1;
while (loop) {
prompt (“Enter SSN: “, ssn);
EXEC SQL
select FNAME, LNAME, ADDRESS, SALARY
into :fname, :lname, :address, :salary
from EMPLOYEE where SSN == :ssn;
if (SQLCODE == 0) printf(fname, …);
else printf(“SSN does not exist: “, ssn);
prompt(“More SSN? (1=yes, 0=no): “, loop);
END-EXEC
 A cursor (iterator) is needed to process multiple tuples
 FETCH commands move the cursor to the next tuple
 CLOSE CURSOR indicates that the processing of query results has been completed
 Objective: 
5.6 Dynamic SQL
Composing and executing new (not previously compiled) SQL statements at run-time
 a program accepts SQL statements from the keyboard at run-time
 a point-and-click operation translates to certain SQL query
 Dynamic update is relatively simple; dynamic query can be complex 
 because the type and number of retrieved attributes are unknown at compile time
EXEC SQL BEGIN DECLARE SECTION;
varchar sqlupdatestring[256];
EXEC SQL END DECLARE SECTION;
…prompt (“Enter update command:“, sqlupdatestring);
EXEC SQL PREPARE sqlcommand FROM :sqlupdatestring;
 EXEC SQLSQLJ: a standard for embedding SQL in Java
 An SQLJ translator converts SQL statements into Java 
 These are executed thru the JDBC interface
 Certain classes have to be imported
 E.g., java.sql
EXECUTE sqlcommand;
 Environment record:
 Keeps track of database connections
 Connection record:
 Keep tracks of info needed for a particular connection
 Statement record:
 Keeps track of info needed for one SQL statement
 Description record:
 Keeps track of tuples
 Load SQL/CLI libraries 
 Declare record handle variables for the above components (called: SQLHSTMT, 
SQLHDBC, SQLHENV, SQLHDEC)
 Set up an environment record using SQLAllocHandle
 Set up a connection record using SQLAllocHandle
 Set up a statement record using SQLAllocHandle
 Prepare a statement using SQL/CLI function SQLPrepare
 Bound parameters to program variables
 Execute SQL statement via SQLExecute
 Bound query columns to a C variable via SQLBindCol
 Use SQLFetch to retrieve column values into C variables
5.7 Database stored procedures and SQL/PSM
 Persistent procedures/functions (modules) are stored locally and executed by the 
database server
 As opposed to execution by clients
 Advantages:
 If the procedure is needed by many applications, it can be invoked by any of them 
(thus reduce duplications)
 Execution by the server reduces communication costs
 Enhance the modeling power of views
 Disadvantages:
 Every DBMS has its own syntax and this can make the system less portable
 A stored procedure
CREATE PROCEDURE procedure-name (params)
local-declarations
procedure-body;
 A stored function
CREATE FUNCTION fun-name (params) RETRUNS return-type
local-declarations
function-body;
 Calling a procedure or function
CALL procedure-name/fun-name (arguments);
 SQL/PSM: 
 Part of the SQL standard for writing persistent stored modules
 SQL + stored procedures/functions + additional programming constructs
 E.g., branching and looping statements
 Enhance the power of SQL
CREATE FUNCTION DEPT_SIZE (IN deptno INTEGER)
RETURNS VARCHAR[7]
DECLARE TOT_EMPS INTEGER;
SELECT COUNT (*) INTO TOT_EMPS
FROM SELECT EMPLOYEE WHERE DNO = deptno;
IF TOT_EMPS > 100 THEN RETURN “HUGE”
ELSEIF TOT_EMPS > 50 THEN RETURN “LARGE”
ELSEIF TOT_EMPS > 30 THEN RETURN “MEDIUM”
ELSE RETURN “SMALL”
ENDIF;


Editors




You might like this video:Watch more here

Watch more videos from this user Here

Learn how to upload a video over here