Ex. No.

: 5 Querying/Managing the database using SQL Programming

Date : Stored Procedures/Functions

Constraints and security using Triggers

Aim:

To implement queries and manage the database using SQL Programming with
procedures, function, constraints and security using triggers.

Procedure:

Stored Procedures/Functions:
A PostgreSQL function or a stored procedure is a set of SQL and procedural
commands such as declarations, assignments, loops; flow-of-control etc. stored on the
database server and can be involved using the SQL interfaces. And it is also known
as PostgreSQL stored procedures.

Single Row Functions:

 Single row or Scalar functions return a value for every row that is processed in
a query.

 We can having single row functions for the following process

 Numeric Functions

 String Functions

 Date and Time Functions

Group Functions:

 These functions group the rows of data based on the values returned by the
query.

 This is discussed in SQL GROUP Functions.

NUMERIC FUNCTIONS
Functions Description
ABS Calculates the absolute value of an expression.

Returns the number rounded to the nearest multiple of a second number

ROUND
specify or to the number of decimal places indicated by the second number.
MOD Returns the modulus of a number.

REMAINDER Returns the remainder after one numeric expression is divided by another.

POWER Returns m_value raised to the n_value power

TRUNC Truncates a number to a specified number of decimal places.

Returns the smallest whole number greater than or equal to a specified

CEIL
number.

FLOOR Returns the largest whole number equal to or less than a specified number.

SQRT Computes the square root of an expression.

Returns a value that indicates if a specified number is less than, equal to, or
SIGN
greater than 0 (zero).

LOG Computes the logarithm base 10 of an expression.

Calculates the sine of an angle expression. The result returned by SIN is a

SIN
decimal value with the same dimensions as the specified expression.

COS Calculates the cosine of an angle expression.

TAN Calculates the tangent of an angle expression.

ASIN Calculates the angle value (in radians) of a specified sine.

ACOS Calculates the angle value (in radians) of a specified cosine.

ATAN Calculates the angle value (in radians) of a specified tangent.

BITAND Computes an AND operation on the bits of two integers.

EXP Returns eraised to the nth power, where e equals 2.71828183....

LN Returns the natural logarithm of an expression.

STRING FUNCTIONS:
Functions Description
CHR Return the character value

CONCAT Concatenating two string values.

LENGTH Return the length of a given string.

INITCAP Sets the first letter of each word in uppercase.

LOWER Returns a specified character expression in lowercase letters.

UPPER Returns a specified character expression in UPPERCASE letters.

 Pad the left side of a string with a specific set of characters. The function
LPAD
is useful for formatting the output of a query. (expr1,n,expr2)

Pad the right side of a string with a specific set of characters. This function
RPAD
is useful for formatting the output of a query. (expr1,n,expr2)

Remove all specified characters from the left end side of a string.
LTRIM Optionally you can specify an initial character or characters to trim to, or it
will default to a blank.

Remove all specified characters from the right end side of a string.
RTRIM Optionally you can specify an initial character or characters to trim to, or it
will default to a blank.

The Oracle TRIM function is used to remove all leading or trailing

characters (or both) from a character string. If trim_character or
TRIM
trim_source is a character literal, then it is necessary to enclose it in single
quotation marks.

The REPLACE function is used to return char with every occurrence of

REPLACE
search_string replaced with replacement_string.

The SUBSTR function returns the specified number (substring_length) of

characters from a particular position of a given string.

SUBSTRB uses bytes instead of characters.

SUBSTR
SUBSTRC uses Unicode complete characters.

SUBSTR2 uses UCS2 code points.

SUBSTR4 uses UCS4 code points.

Search string for substring and find the the location of the substring in the
INSTR
string.

Get the characters having the binary equivalent to the number in the
NCHR national character set. This function is equivalent to using the CHR
function with the USING NCHAR_CS clause.

The ASCII() function returns the decimal representation of the first

ASCII
character of a character expression.
Date & Time Functions:
Value Description
ADD_MONTHS ADD_MONTHS returns a date (date plus integer months).

CURRENT_DATE returns the current date in the session time

CURRENT_DATE
zone, in a value in the Gregorian calendar of datatype DATE.

The CURRENT_TIMESTAMP() function returns the current

CURRENT_TIMESTAMP date and time in the session time zone, in a value of datatype
TIMESTAMP WITH TIME ZONE.

DBTIMEZONE DBTIMEZONE returns the value of the database time zone.

EXTRACT(YEAR,MONTH,DAY,HOUR,MINUTE,SECOND
EXTRACT(datetime) from Date) function returns extract value of a specified
datetime field from a datetime or interval expression.

LAST_DAY() function returns the date of the last day of the

LAST_DAY
month that contains a date.

LOCALTIMESTAMP() function returns the current date and

LOCALTIMESTAMP time in the session time zone in a value of datatype
TIMESTAMP.

MONTHS_BETWEEN() function returns the number of

MONTHS_BETWEEN
months between dates (date1, date2).

NEXT_DAY returns the date of the first weekday that is later

NEXT_DAY
than the date.

SYSDATE SYSDATE returns the current date and time.

SYSTIMESTAMP function returns the system date, including

SYSTIMESTAMP
fractional seconds and time zone.

TO_CHAR (datetime) function returns a datetime or interval

TO_CHAR(datetime) value of DATE. TIMESTAMP, TIMESTAMP WITH TIME
ZONE.

User Defined Functions

 User-defined functions are functions that you use to organize your code in the body of
a policy.
  Once you define a function, you can call it in the same way as the built-in action and
parser functions.
 Variables that are passed to a function are passed by reference, rather than by value.
Syntax:
CREATE OR REPLACE FUNCTION
function_name(p1 type, p2 type)
RETURNS type
AS
BEGIN
-- logic
END;
LANGUAGE PLPGSQL;

Example:

CREATE or replace FUNCTION inc(val integer) RETURNS integer AS $$

BEGIN

RETURN val + 5;

END; $$

LANGUAGE PLPGSQL;

Calling a Function:

Syntax:

SELECT function_name(20);

Example:

SELECT inc(20);

Stored Procedure

PostgreSQL categorizes the procedural languages into two main groups:

 Safe languages can be used by any users. SQL and PL/pgSQL are safe languages.

 Sand-boxed languages are only used by superusers because sand-boxed languages

provide the capability to bypass security and allow access to external sources. C is an
example of a sandboxed language.

Syntax:

CREATE OR REPLACE PROCEDURE

 <PROCEDURE NAME>(<VARIABLE NAME <DATA TYPE> …)

LANGUAGE SQL

AS $$

<SQL CODE>

$$;

Example:
create or replace procedure transfer(
senderint,
receiverint,
amountdec
)
languageplpgsql
as $$
declare
acc1int;
acc2int;
begin
select balance INTO acc1 from accounts where id = sender;
update accounts set balance = balance - amount where id = sender;
select balance into acc2 from accounts where id = receiver;
update accounts set balance = balance + amount where id = receiver;
insert into trans(said,sobal,raid,robal) values (sender,acc1,receiver,acc2);
commit;
end;$$;
Trigger
 PostgreSQL Triggers are database callback functions, which are automatically
performed/invoked when a specified database event occurs.
 A trigger that is marked FOR EACH ROW is called once for every row that the
operation modifies.
The following table summarizes which types of triggers may be used on tables, views, and
foreign tables:

Statement-
When Event Row-level
level

Tables and Tables, views,

INSERT/UPDATE/DELETE foreign and foreign
BEFORE tables tables

TRUNCATE — Tables

Tables and Tables, views,

INSERT/UPDATE/DELETE foreign and foreign
AFTER tables tables

TRUNCATE — Tables

INSERT/UPDATE/DELETE Views —
INSTEAD OF
TRUNCATE — —

Trigger Function Syntax:

CREATE OR REPLACE FUNCTION <FUNCTION NAME>()
RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
<BLOCK OF CODE>
RETURN NEW;
END;
$$
Trigger Function Example:
CREATE OR REPLACE FUNCTION log_First_name_changes()
 RETURNS TRIGGER
LANGUAGE PLPGSQL
AS
$$
BEGIN
IF NEW.First_name <> OLD.First_name THEN
INSERT INTO Client_audits(Client_id,First_name,changed_on)
VALUES(OLD.ID,OLD.First_name,now());
END IF;
RETURN NEW;
END;
$$
Trigger Syntax:
CREATE TRIGGER <Trigger Name>
BEFORE UPDATE
ON Clients
FOR EACH ROW
EXECUTE PROCEDURE <Function Name>();
Trigger Example:
CREATE TRIGGER First_name_changes
BEFORE UPDATE
ON Clients
FOR EACH ROW
EXECUTE PROCEDURE log_First_name_changes();

Result:
Thus the query manipulation and querying database using SQL Programming is
executed with Querying/Managing the database using SQL Programming.
Ex. No.: 6 Database design using Normalization – bottom-up approach

Date :

Aim:

To implement database design using Normalization with bottom up approach.

Procedure:

Normalization:
Normalization is a database design technique which organizes tables in a manner that
reduces redundancy and dependency of data.
It divides larger tables to smaller tables and links them using relationships.
The inventor of the relational model Edgar Codd proposed the theory of normalization with
the introduction of First Normal Form, and he continued to extend theory with Second and
Third Normal Form. Later he joined with Raymond F. Boyce to develop the theory of Boyce-
Codd Normal Form.
Theory of Data Normalization in SQL is still being developed further. For example, there are
th
discussions even on 6 Normal Form. However, in most practical applications,
rd
normalizationachieves its best in 3 Normal Form. The evolution of Normalization theories is
illustrated below-

Database Normalization Examples -

Assume a video library maintains a database of movies rented out. Without any normalization,
all information is stored in one table as shown below.
Here you see Movies Rented column has multiple values.

Database Normal Forms

st
Now let's move into 1 Normal Forms

1NF (First Normal Form) Rules

 Each table cell should contain a single value.

 Each record needs to be unique.
The above table in 1NF-
1NF Example

Before we proceed let's understand a few things --

What is a KEY?

A KEY is a value used to identify a record in a table uniquely. A KEY could be a single
column or combination of multiple columns

Note: Columns in a table that are NOT used to identify a record uniquely are called non-key
columns.

What is a Primary Key?

A primary is a single column value used to identify a database

record uniquely.

It has following attributes

 A primary key cannot be NULL

 A primary key value must be unique
 The primary key values cannot be changed
 The primary key must be given a value when a new record is
inserted.
What is Composite Key?

A composite key is a primary key composed of multiple columns used to identify a record
uniquely
In our database, we have two people with the same name Robert Phil, but they live in different
places.

Hence, we require both Full Name and Address to identify a record uniquely. That is a
composite key.

Let's move into second normal form 2NF

2NF (Second Normal Form) Rules

 Rule 1- Be in 1NF
 Rule 2- Single Column Primary Key
nd
It is clear that we can't move forward to make our simple database in 2 Normalization form
unless we partition the table above.

Table 1

Table 2

We have divided our 1NF table into two tables viz. Table 1 and Table2. Table 1 contains
member information. Table 2 contains information on movies rented.
We have introduced a new column called Membership_id which is the primary key for table 1.
Records can be uniquely identified in Table 1 using membership id

Database - Foreign Key

In Table 2, Membership_ID is the Foreign Key

Why do you need a foreign key?

Suppose an idiot inserts a record in Table B such as

You will only be able to insert values into your foreign key that exist in the unique key in the
parent table. This helps in referential integrity.
The above problem can be overcome by declaring membership id from Table2 as foreign key
of membership id from Table1

Now, if somebody tries to insert a value in the membership id field that does not exist in the
parent table, an error will be shown!

What are transitive functional dependencies?

A transitive functional dependency is when changing a non-key column, might cause any of
the other non-key columns to change

Consider the table 1. Changing the non-key column Full Name may change Salutation.

Let's move into 3NF

3NF (Third Normal Form) Rules

 Rule 1- Be in 2NF
 Rule 2- Has no transitive functional dependencies

To move our 2NF table into 3NF, we again need to again divide our table.

3NF Example

TABLE 1

Table 2
Table 3
We have again divided our tables and created a new table which stores Salutations.
There are no transitive functional dependencies, and hence our table is in 3NF. In Table 3
Salutation ID is primary key, and in Table 1 Salutation ID is foreign to primary key in Table 3
Now our little example is at a level that cannot further be decomposed to attain higher
forms of normalization. In fact, it is already in higher normalization forms. Separate efforts for
moving into next levels of normalizing data are normally needed in complex databases.
However, we will be discussing next levels of normalizations in brief in the following.
Boyce-Codd Normal Form (BCNF)
rd
Even when a database is in 3 Normal Form, still there would be anomalies resulted if
it has more than one Candidate Key.
Sometimes is BCNF is also referred as 3.5 Normal Form.

4NF (Fourth Normal Form) Rules

If no database table instance contains two or more, independent and multivalued data
th
describing the relevant entity, then it is in 4 Normal Form.

5NF (Fifth Normal Form) Rules

th
A table is in 5 Normal Form only if it is in 4NF and it cannot be decomposed into any
number of smaller tables without loss of data.

6NF (Sixth Normal Form) Proposed

th
6 Normal Form is not standardized, yet however, it is being discussed by database experts
th
for some time. Hopefully, we would have a clear & standardized definition for 6 Normal
Form in the near future.

Result:
Ex. No.:7 Develop database applications using IDE/RAD tools (Eclipses)

Date :
Thus
we implemented database design using Normalization with bottom up approach.

Aim:

To develop database applications using IDE tools with Eclipses.

Procedure:

Adding postgres SQL Driver

 Download the recent driver from below link

https://jdbc.postgresql.org/download.html

 Add it to the Eclipse Java Project

Open the new Eclipse File

Select File->New->Others->Java Project

Add new class in Java Project

Add the class with class name as “PGConnect” inside the package “DBConnection”
 Project (Right Click) -> Properties -> Java Build Path -> Libraries (Tab) -> Add
External Jar -> (Upload the downloaded jar file for postgressql)

Adding External Driver in Project

Syntax

Declaring the Class:

Class.forName("org.postgresql.Driver");

Establishing the Connection:

Connection c = DriverManager.getConnection

("jdbc:postgresql://<Host>:<Port Id>/<Schema>",

“<User Name>",

“<Password>“

);

Example:

Connection c = DriverManager.getConnection

("jdbc:postgresql://localhost:5432/Test","postgres","admin@321");

//To Create the statement class

Statement s = c.createStatement();

//To create/insert/update the table

s.executeUpdate(“<Query>");

// code to insert the data

s.executeUpdate("insert into student (id,name) values (45,'SriRam')");

 // code to update the data

s.executeUpdate("update student set name = 'K.SriRam' where id = 45");

//Code the select the data

ResultSetrs = s.executeQuery("select id,name from student");

//To display the data from the table :

while(rs.next())

System.out.println(

rs.getInt("id") + " - " +

rs.getString("name"));

Closing the Connection

All the opened connection should be closed in stack process

Statement.close();

Connection.close();

Program:

importjava.sql.Connection;

importjava.sql.DriverManager;

importjava.sql.ResultSet;

importjava.sql.SQLException;

importjava.sql.Statement;

public class DBConnectionEg1 {

public static void main(String[] args)

throwsClassNotFoundException, SQLException {

Class.forName("org.postgresql.Driver");

Connectionc = DriverManager.getConnection(

"jdbc:postgresql://localhost:5432/Test","postgres","admin@321");

Statement s = c.createStatement();

// code to add to insert the data

 s.executeUpdate("insert into student (id,name) values (45,'SriRam')");

// code to update the data

s.executeUpdate("update student set name = 'K.SriRam' where id = 45");

// code to view the data

ResultSetrs = s.executeQuery("select id,name from student");

while(rs.next())

System.out.println(rs.getInt("id") + " - " + rs.getString("name"));

s.close();

c.close();

Conclusion:

Thus a database application was created using IDE – Eclipses Tools.

 Ex. No.:8 Database design using EER-to-ODB mapping / UML class

Date : diagrams

Aim:
To develop database design using EER to ODB mapping / UML Class Diagrams.
Procedure:
UML Class Diagram
 Describes the system’s data, including attributes of and relationships between the
“objects.”
Object databases (ODB)
 Object data management systems (ODMS)
 Meet some of the needs of more complex applications
 Specify:
o Structure of complex objects
o Operations that can be applied to these objects
Introduction to object-oriented concepts and features
 Origins in OO programming languages
 Object has two components:
o State (value) and behavior (operations)
 Instance variables
o Hold values that define internal state of object
 Operation is defined in two parts:
 Signature or interface and implementation
Inheritance
Permits specification of new types or classes that inherit much of their structure
and/or operations from previously defined types or classes
Operator overloading

Operation’s ability to be applied to different types of objects

Operation name may refer to several distinct implementations

Unique identity
 Implemented via a unique, system-generated object identifier (OID)
 Immutable
  Most OO database systems allow for the representation of both objects and literals (or
values)
Structure of arbitrary complexity
 Contain all necessary information that describes object or literal
Nesting type constructors
 Construct complex type from other types
Most basic constructors:
Atom
Struct (or tuple)
Collection

Generalization
 Is a relationship that recognizes that some kinds of entities can be subdivided
into smaller, more specialized groups.
General Hardware Company ERD

 General Hardware company E-R diagram.

  General Hardware is a wholesaler.

General Hardware Company Generalization Diagram

 Each box represents a class and has three sections separated by horizontal lines.
 At the top, in capital letters, is the class name
 In the middle are the class attributes
 At the bottom are the class operations (not shown)
Encapsulation of Operations and Persistence of Objects
Encapsulation
 Related to abstract data types and information hiding in programming languages
 Define behavior of a type of object based on operations that can be externally
applied
 External users only aware of interface of the operations

 Divide structure of object into visible and hidden attributes

 Object constructor

 Used to create a new object

 Destructor operation

 Used to destroy (delete) an object

 Modifier operations

 Modify the states (values) of various attributes of an object

 Retrieve information about the object

  Dot notation used to apply operations to object

Persistence of Objects

 Transient objects

 Exist in executing program

 Disappear once program terminates

 Persistent objects

 Stored in database and persist after program termination

 Naming mechanism

Reachability

Type Hierarchies and Inheritance

 Inheritance

 Definition of new types based on other predefined types

 Leads to type (or class) hierarchy

 Type: type name and list of visible (public) functions

 Format:

 TYPE_NAME: function, function, ..., function

Subtype

 Useful when creating a new type that is similar but not identical to an already
defined type

Example:

EMPLOYEE subtype-of PERSON: Salary, Hire_date, Seniority

STUDENT subtype-of PERSON: Major, Gpa

Extent

 Store collection of persistent objects for each type or subtype

 Extents are subsets of the extent of class OBJECT

Persistent collection

 Stored permanently in the database

Transient collection

 Exists temporarily during the execution of a program

Polymorphism of operations

 Also known as operator overloading

 Allows same operator name or symbol to be bound to two or more different

implementations

 Depending on type of objects to which operator is applied

Multiple inheritance

 Subtype inherits functions (attributes and methods) of more than one

supertype

Selective inheritance

 Subtype inherits only some of the functions of a supertype

Conclusion:

Thus database design using EER-to-ODB mapping / UML class diagrams was
constructed.