LAB MANUAL

SUBJECT: Database Management System

BTCS505-18

5th Semester (B. Tech)

(Branch: CSE)

Prepared by:
Gurleen Kaur

CGC College Of Engineering

Landran, Mohali – 140307

1
 Index
Sl. No. Content Page. No.
1 Course Objective and Course Outcomes 3
2 List of Experiments 4
3 List of Experiments (Beyond the Syllabus) 4
4 Experiment 1: Introduction to SQL and installation of SQL 5-13
Server / Oracle.
5 Experiment 2: Data Types, Creating Tables, Retrieval of Rows 14-21
using Select Statement, Conditional Retrieval of Rows, Alter and
Drop Statements.
6 Experiment 3: Working with Null Values, matching a Pattern from 22-27
a Table, Ordering the Result of a Query, Aggregate Functions,
Grouping the Result of a Query, Update and Delete Statements.
7 Experiment 4: Set Operators, Nested Queries, Joins, Sequences. 28-34
8 Experiment 5: Views, Indexes, Database Security and Privileges: 35-40
Grant and Revoke Commands, Commit and Rollback Commands.
9 Experiment 6: PL/SQL Architecture, Assignments and 41-44
Expressions, Writing PL/SQL Code, Referencing Non-SQL
parameters.
10 Experiment 7: Stored Procedures and Exception Handling. 45-47
11 Experiment 8: Triggers and Cursor Management in PL/SQL. 48-54
Experiments Beyond the Syllabus
12 Expriments-9: Study of Web Databases. 55-59
13 Experiment-10: Installation of Database Server. 60-70

2
COURSE OBJECTIVES
1. Retrieve data from relational databases using SQL.
2. Implement generation of tables using datatypes
3. Design and execute the various data manipulation queries.
4. Learn to execute triggers, cursors, stored procedures etc.
5. Learn to execute recovery tools.
6. Study tools which can help to design database.

COURSE OUTCOME
1. This will help to retrieve data from relational databases using SQL.
2. To implement generation of tables using datatypes
3. To design and execute the various data manipulation queries.
4. Learn to execute triggers, cursors, stored procedures etc.
5. Learn to execute recovery tools.
6. Tools can help to design database.

3
 List of Experiments

Sl. No. Content Relevance to

CO No.
1 Experiment 1: Introduction to SQL and installation of SQL Server / CO6
Oracle.
2 Experiment 2: Data Types, Creating Tables, Retrieval of Rows using CO1, CO2,
Select Statement, Conditional Retrieval of Rows, Alter and Drop CO3
Statements.
3 Experiment 3: Working with Null Values, matching a Pattern from a CO3
Table, Ordering the Result of a Query, Aggregate Functions, Grouping
the Result of a Query, Update and Delete Statements.
4 Experiment 4: Set Operators, Nested Queries, Joins, Sequences. CO3
5 Experiment 5: Views, Indexes, Database Security and Privileges: Grant CO3
and Revoke Commands, Commit and Rollback Commands.
6 Experiment 6: PL/SQL Architecture, Assignments and Expressions, CO3, CO6
Writing PL/SQL Code, Referencing Non-SQL parameters.
7 Experiment 7: Stored Procedures and Exception Handling. CO4, CO5
8 Experiment 8: Triggers and Cursor Management in PL/SQL. CO4
Experiments Beyond the Syllabus
9 Expriments-9: Study of Web Databases. CO6
10 Experiment-10: Installation of Database Server. CO6

4
Experiment 1: Introduction to SQL and installation of SQL Server /
Oracle.

Objective: Students will be able to gain knowledge of SQL and installation of

SQL server.
Description:
DATABASE MANAGEMENT SYSTEM (DBMS)
 A DBMS (Database Management System) is a software program used to manage
a database.Theseprogramsenableuserstoaccessandmodifydatabase
 ADBMSisacomplexsetofsoftwareprogramsthatcontrolstheorganization,storage,
management, and retrieval of data in adatabase.
 A DBMS includes four main components, which are: Modeling Language, Data
Structures,DBQueryLanguageandReportWriter,andTransactionMechanism.

Eachofthesecomponentscanbefurtherbrokendownintosmallerandmorespecificpieces, but
it is the sum of these parts which are combined to create the management systemaround
the particular database to
beutilized.Adatabasemanagementsystem,orDBMS,givestheuseraccesstotheirdataandhelps
them transform the data
intoinformation.SuchdatabasemanagementsystemsincludedBase,Paradox,IMS,andOracle.
Thesesystems allow users to create, update, and extract information from their databases.
Compared to a manual filing system, the biggest advantages to a computerized database
system are speed, accuracy,
andaccessibility.Adatabaseisastructuredcollectionofdata.Datareferstothecharacteristicsofp
eople,things, andevents.
THE MAIN FOUR COMPONENTS OF DBMS:
Modeling language
AmodelinglanguagetodefinetheschemaofeachdatabasehostedintheDBMS,accordingto the
DBMS data model. The four most common types of organizations are the hierarchical,
network, relational and objectmodels.
Data structures
Datastructures(fields,records,filesandobjects)optimizedtodealwithverylargeamountsof
datastoredonapermanentdatastoragedevice(whichimpliesrelativelyslowaccesscompared
to volatile mainmemory).
5
Db query language and report writer
A database query language and report writer to allow users to interactively interrogate
the database, analyze its data and update it according to the users privileges on data.
Transaction mechanism:
Atransactionmechanism,thatideallywouldguaranteetheACIDproperties,inordertoensure
data integrity, despite concurrent user accesses (concurrency control), and faults (fault
tolerance).

ORACLE
Oracle is one of the powerful RDBMS product that provide efficient solutions for
database
applications.OracleistheproductofOracleCorporationwhichwasfoundedbyLawrence
Ellisionin 1977. The first commercial product of oracle was delivered in 1970. The first
versionoforacle2.0waswritteninassemblylanguage.Nowadayscommonlyusedversionsof
oracle are ORACLE 8, 8i & 9i Oracle 8 and onwards provide tremendous increase in
performance, features andfunctionality.
Features Of Oracle:
 Client/ServerArchitecture
 Large database and SpaceManagement
 ConcurrentProcessing
 High transaction processingperformance
 HighAvailability
 Many concurrent databaseusers
 Controlledavailability
 Openness industrystandards
 Manageablesecurity
 Database enforcedintegrity
 Distributedsystems
 Portability
 Compatibility
OracleServerTool
Oracleisacompanythatproducesmostwidelyusedserverbasedmulti-userRDBMS.Oracle
serverisaprograminstalledonserverhard-diskdrive.ThisprogrammustbeloadedinRAM

6
tothatitcanprocesstheuserrequests.Oracleservertakescareoffollowingfunctions.Oracle
servertoolsarealsocalledasbackend.Functionsofservertool:
 Updates thedata
 Retrievesthedatasharing
 Managesthedatasharing
 Accepts the query statements PL/SQL andSQL
 Enforce the transactionconsistency
Installation Oracle Database 10g on Windows
To install the Oracle software, you must use the Oracle Universal installer.
1. ForthisinstallationyouneedeithertheDVDsoradownloadedversionoftheDVDs.Inthis
tutorial,youinstallfromthedownloadedversion.FromthedirectorywheretheDVDfileswere
unzipped, double-clicksetup.exe.

Fig. 1.1 Installation window

2. The Oracle Universal Installerstarts

7
 Figure 1.2 Command Prompt

3. Youwillperformabasicinstallationwithastarterdatabase. Enter oracle

fortheGlobalDatabase
andoraclefortheDatabasePasswordandConfirmPassword.ThenclickNext.

Fig. 1.3 Installation screen

4. The installer now verifies that the system meets all the minimum requirements
forinstalling and configuring the chosen product. Please correct any reported errors
(warnings are OK)
beforecontinuing.Whenthechecksuccessfullycompletes(withorwithoutwarnings),click
Next.

Fig. 1.4 Oracle universal Installer window

8
5. If you received any warnings, you can proceed. ClickYes.

Fig. 1.5 Warning Window

6. ReviewtheSummarywindowtoverifywhatistobeinstalled.Then,clickInstall.

Fig. 1.6 Summary Window

9
7. The progress windowappears.

Fig. 1.7 Install Window

8. The Configuration Assistants windowappears.

Fig. 1.8 Configuration Assistant Window

9. Your database is now beingcreated.

10
 Fig. 1.9 Database Configuration Assistant Window

Whenthedatabasehasbeencreated,youcanunlocktheusersyouwanttouse.Click on Power
Management.

Fig. 1.10 Password Management Window

10. UnlockSH,OEandHRusersbyclickingonthecheckmarkintheLockAccount?column.
Enter the same name as the user in the New Password and Confirm Password fields. For
example,tounlockuser,enterSHintheNewPasswordandConfirmPasswordfields.Then,
clickOK.

11
 Fig. 1.11 Password Window Management Window 2

11. Click OK again

Fig. 1.12 Password Window Management Screen

12. Click Exit.

12
 Fig. 1.13 End of Installation Screen

Outcome:

By this process the students learns about the SQL and the installation process of SQL server.

13
Experiment 2: Data Types, Creating Tables, Retrieval of Rows using Select
Statement, Conditional Retrieval of Rows, Alter and Drop Statements.
Objective: Students will be able to gain knowledge of Data Types, tables,
selection of data in rows, conditional retrieval of rows, alter & drop statements.
Description:
CHAR
Thisdatatypeisusedtostorecharacterstringsvaluesoffixedlength.Thesizeinbrackets
determines the number of characters the cell can hold. The maximum number of
characters(i.e.thesize)thisdatatypecanholdis255characters.SyntaxisCHAR(SIZE)
Example is CHAR (20)
VARCHAR
Thisdatatypeisusedtostorevariablelengthalphanumericdata.Themaximumthisdata type can
hold is 2000 characters. One difference between this data type and the CHAR data type
is ORACLE compares VARCHAR values using non-padded comparison semantics i.e.
the inserted values will not be padded with spaces. Syntax is VARCHAR(SIZE)
Example is VARCHAR (20) OR VARCHAR2 (20)
NUMBER
The NUMBER data type is used to store numbers (fixed or floating point). Numbers of
virtuallyanymagnitudemaybestoredupto38digitsofprecision.Numbersaslargeas9.99* 10 to
the power of 124, i.e. followed by 125 zeros can be stored. The precision, (P),
determinesthemaximumlengthofthedata,whereasthescale,(S),determinesthenumber
ofplacestotherightofthedecimal.Ifscaleisomittedthenthedefaultiszero.Ifprecision
isomittedvaluesarestoredwiththeiroriginalprecisionuptothemaximumof38digits.
Syntax is NUMBER (P, S) Example is NUMBER (10, 2)
LONG
Thisdatatypeisusedtostorevariablelengthcharacterstringscontainingupto2GB.LONG data
can be used to store arrays of binary data in ASCII format. LONG values cannot be
indexed,andthenormalcharacterfunctionssuchasSUBSTRcannotbeappliedtoLONG
values.
Syntax is LONG (SIZE) Example is LONG (20)
DATE

14
Thisdatatypeisusedtorepresentdataandtime.ThestandardformatidDD-MM-YYasin 13-
JUL-85. To enter dates other than the standard format, use the appropriate functions.
DateTimestoresdateinthe24-hourformat.Bydefault,thetimeinadatefieldis12:00:00 am, if
no time portion is specified. The default date for a date field is the first day of the current
month. Syntax isDATE.
LONGRAW
LONG RAW data types are used to store binary data, such as Digitized picture or image.
Data loaded into columns of these data types are stored without any further conversion.
LONG RAW data type can contain up to 2GB. Values stored in columns having LONG
RAW data type cannot be indexed. Syntax is LONGRAW (SIZE).
RAW
It is used to hold strings of byte oriented data. Data type can have a maximum length of
255 bytes. Syntax is RAW(SIZE)

The following is a list of datatypes available in Oracle/PLSQL which includes

Character, Numeric, date/Time, LOB and rowid datatypes:
Character Datatypes
Following are the Character Datatypes in Oracle/PLSQL:

Numeric Datatypes
Following are the Numeric Datatypes in Oracle/PLSQl:
DataType Oracle 11g
Explanation
Syntax

15
 Precision can range Where p is the precision and s is the
from 1 to 38. scale.
Scale can range from -
number(p,s) For example, number(7,2) is a number
84 to 127.
that has 5 digits before the decimal and
2 digits after the decimal.

Where p is the precision and s is the

scale.
Precision can range
numeric(p,s) For example, numeric(7,2) is a number
from 1 to 38.
that has 5 digits before the decimal and
2 digits after the decimal.

Float
Where p is the precision and s is the
scale.
Precision can range
dec(p,s) from 1 to 38. Forexample,dec(3,1)isanumberthathas2
digitsbeforethedecimaland1digitafterthe
decimal.
Where p is the precision and s is the
scale.
Precision can range
decimal(p,s) Forexample,decimal(3,1)isanumberthath
from 1 to 38.
as
2digitsbeforethedecimaland1digitafterth
e decimal.
integer
Int
smallint
Real
double precision

Date/Time Datatypes
Following are the Date/Time Datatypes in Oracle/PLSQl:

16
 Data Type Syntax Oracle 11g Explanation
A date between Jan 1, 4712 BC
Date and Dec 31,
9999 AD.
timestamp (fractional fractional seconds Includes year, month, day, hour, minute, and
seconds precision) precision must be a seconds.
number between 0 and 9.
(defaultis For example: timestamp(6)
6)
Includes year, month, day, hour, minute, and
timestamp (fractional seconds;withatimezonedisplacementvalue. For
seconds precision) fractional seconds example: timestamp(5) with timezone
with timezone precision must be a
number between 0 and 9.
(defaultis
6)
Includes year, month, day, hour, minute, and
timestamp (fractional fractional seconds seconds; with a time zone expressed as the
seconds precision) precision must be a session time zone.
with number between 0 and 9. Forexample:timestamp(4)withlocaltime zone
local time (defaultis
zone 6)
interval year (year year precision is the number of Time period stored in years and months.
precision) to month digits in the year. (default is 2)
For example: interval year(4) to month

day
interval day (day precision must be a number
precision) to between 0 and 9. Time period stored in days, hours, minutes,
(default is 2) and seconds.
fractionalseconds precision
must be a
number between 0 and 9.
(default is
6)

second (fractional For example: interval day(2) to second(6)

seconds precision)

Large Object LOB Datatypes

Following are the Large objects(LOB) Datatypes in Oracle/PLSQl:

17
 Data Type Oracle 11g Explanation
Syntax
File locators that point to a binary
Bfile Maximum file size of 264-1 bytes. file on the server file system
(outside the database).
Store up to (4 gigabytes
Blob -1) * (the value of the CHUNK
Stores unstructured binary large
parameter of LOB storage).
objects.
Store up to (4 gigabytes
-1) * (the value of the CHUNK Stores single-byte and multi-byte
parameter of LOB storage) of character data.
Clob
character data.
Store up to (4 gigabytes
-1) * (the value of the CHUNK
parameter of LOB storage) of
Nclob Stores Unicode data.
character text data.

Rowid Datatype
Following are the Rowid Datatypes in Oracle/PLSQl:

1. CREATE: The command is used too create a table. It is used to specify a

newrelation
bygivingitanameandspecifyingattributesandinitialconstraints.Thiscommandisa
partofDDL(DataDefinitionLanguage)ofSQL.Thecolumnnamesmustbespecified along the
data types. Each table must have atleast one column. Tables are the basic
structurewheredataisstoredinthedatabase.Tablesaredividedintorowsandcolumns.
Syntax of CREATE command is:

18
Create Table <Table Name>
<Column Name><datatype>(<size>)
<Column Name><datatype>(<size>)……….
OUTPUT

2. ALTER: The definition of a base table or of other named schema elements can be
changedusingtheALTERcommand.Aftercreatingatablewemayneedtochangethe
tablestructuresbecauseweomitacolumnorourcolumndefinitionneedstobechanged. For this
purpose, we use AlterCommand.
Tables can be altered in one of the three ways:

 By adding a column to existingtable.

 By changing a column’sdefinition.
 By dropping acolumn.

ADDING a new column in a table: We can ALTER the table by adding a new column to
the existingtable.
Syntax
Alter Table <Table Name> Add ( <Column Name><Datatype> (<size>));

OUTPUT

19
MODIFYING a column in a table: we can modify a column definition by using the
ALTER command with the modify clause.
Systax
Alter Table <Table Name> Modify (<Column Name><Datatype>(<size>));

OUTPUT

3. DROP: The DROP command is used to drop tables. This command not only
deletesall therecordsinthetable,butalsoremovesthedefinitionofatable.Whenwedropatable,
thedatabaselosesallthedatainthetableandalltheindexesassociatedwithit. All data is deleted
from the table.
Syntax of DROP command

DROP Table <Table Name>;

OUTPUT

20
4. SELECT: TheSELECTstatementisusedtoretrieveexistingrowsfromatable.

Syntax

SELECT column, column, column, ….. FROM <table_name> WHERE condition;

OUTPUT

Outcome:

By this process the students learns about the Data Types, Creating Tables, Retrieval of Rows
using Select Statement, Conditional Retrieval of Rows, Alter and Drop Statements.

21
Experiment 3: Working with Null Values, Matching a Pattern from a
Table, Ordering the Result of a Query, Aggregate Functions, Grouping the
Result of a Query, Update and Delete Statements.
Objective: Students will be able to gain knowledge of null values, pattern from
table, ordering the result of a query, aggregate functions, grouping the results of
query, update and delete statements.
Description:
NULL

The SQL NULL is the term used to represent a missing value. A NULL value in a table is a
value in a field that appears to be blank.A field with a NULL value is a field with no value. It
is very important to understand that a NULL value is different than a zero value or a field that
contains spaces.

Syntax

The basic syntax of NULL while creating a table.

SQL> CREATE TABLE CUSTOMERS(

ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

Here, NOT NULL signifies that column should always accept an explicit value of the given
data type. There are two columns where we did not use NOT NULL, which means these
columns could be NULL.

A field with a NULL value is the one that has been left blank during the record creation.

Pattern Matching
MySQL provides standard SQL pattern matching as well as a form of pattern matching based
on extended regular expressions similar to those used by Unix utilities such as vi, grep,
and sed.
22
SQL pattern matching enables you to use _ to match any single character and % to match an
arbitrary number of characters (including zero characters). In MySQL, SQL patterns are case-
insensitive by default. Some examples are shown here. Do not use = or <> when you use
SQL patterns. Use the LIKE or NOT LIKE comparison operators instead.

WHERE Clause

The WHERE Clause is used to extract only those records that fulfill a specifiedcriterion.
Syntax for WHERE Clause:

SELECT<column_name,column_name>FROMtable_nam
e> WHERE <column_name operatorvalue>;

OUTPUT

WHERE Clause includes operators as:

LIKE Operator: The LIKE Operator is used to search for a specified pattern in a
column.

Syntax for LIKE Operator:

SELECT<column_name,column_name>FROM<table
_name> WHERE <column_name> LIKEpattern;

OUTPUT

BETWEENOperator:TheBETWEENoperatorselectsvalueswithinarange.Thevaluescan be
numbers, text ordates.

23
Syntax for BETWEEN Operator:
SELECT<column_name,column_name>FROM<table_
name>
WHERE<column_name>BETWEEN<value1>AND<va
lue2>;
OUTPUT

GROUP BY Clause

TheGROUPBYstatementisusedinconjuctionwiththeaggregatefunctionstogrouptheresult
set by one or morecolumns.
Syntax for GROUP BY Clause:

SELECT <column_name, aggregate_function(column_name)> from

<table_name>WHERE <column_name operator value> GROUP
BY<column_name>;

OUTPUT

HAVING Clause

The HAVING Clause enables us to specify conditions that filter which group results
appear in the final results.

Syntax for HAVING Clause:

SELECT<column_name,aggregate_function(column_name)>
from<table_name>
WHERE<column_nameoperatorvalue>GROUPBY<column_name> HAVING <aggregate_function(colu

24
25
OUTPUT

ORDER BY Clause
TheORDERBYclauseisusedtosorttheresultsetbyoneor morecolumns. Syntax for

SELECT <column_name, aggregate_function(column_name)>from

<table_name>WHERE <column_name operatorvalue>
ORDER BY <column_name, column_name, …..> ASC|DESC;

OUTPUT

UPDATE Statement
TheUPDATEStatementisusedtomodifyexistingrows.
Syntax for UPDATEStatement:
UPDATE <table_name> SET column=value, column=value WHERE condition;

OUTPUT

DELETE Statement
The DELETE statement is used to delete existing rows from a table.

Syntax for DELETE Statement:

DELETE FROM <table_name> WHERE condition;

26
OUTPUT:

AggregateFunctions:Anaggregatefunctionisafunctionwherethevaluesofmultiplerowsare
groped together as input on certain criteria to form a single value of more significant
measurement ormeaning.

SUM:TheSUM()functionreturnsthetotalsumofanumericcolumn.Syntax:

SELECT SUM(<column_name>) FROM <table_name>;

OUTPUT:

MAX:Thisfunctionreturnsthemaximumofvaluesforanycolumnofatableforallthe
rowsSyntax:
SELECT MAX(<column_name>) FROM <table_name>;

OUTPUT:

MIN:Thisfunctionreturnstheminimumofvaluesforanycolumnofatableforallthe rows.
Syntax:
SELECT MIN(<column_name>) FROM <table_name>;

27
OUTPUT:

AVG:Thisfunctionreturnstheaverage ofvaluesforanycolumnofatable.Itignores theNULL

values in thecolumn.
Syntax:
SELECT AVG(<column_name>) FROM <table_name>;

OUTPUT:

COUNT:Thisfunctionreturnsthenumberofrowsornon-nullvaluesforagivencolumn in
atable.
Syntax:
SELECT COUNT(<column_name>) FROM <table_name>;

OUTPUT:

Outcome:

By this process the students learns about the Working with Null Values, Matching a Pattern
from a Table, Ordering the Result of a Query, Aggregate Functions, Grouping the Result of a
Query, Update and Delete Statements

28
Experiment 4: Set Operators, Nested Queries, Joins, Sequences.
Objective: Students will be able to gain knowledge of
Description:
Union:TheUNIONoperatorisusedtocombinetheresultsetoftwoormoreSELECT
statements.EachSELECTstatementwithintheUNIONmusthavethesamenumberof
columns, The columns must also have similar datatypes.
Syntax for Union
SELECT column_name(s) FROM
table1 UNION
SELECT column_name(s) FROM table2;
OUTPUT:

Intersection:TheSQLINTERSECTclause/operatorisusedtocombinetwoSELECT
statements,butreturnsrowsonlyfromthefirstSELECTstatementthatareidentifiedto
arowinthesecondSELECTstatement.ThismeansINTERSECTreturnsonlycommon rows
returned by the two SELECTstatements.
Syntax for Intersect:
SELECT column_name(s) FROM
table1 INTERSECT
SELECT column_name(s) FROM table2;

OUTPUT:

29
Set Difference: The SQL MINUS query returns all rows in the first SQL SELECT
statementthatarenotreturnedinthesecondSQLSELECTstatement.
Each SQL SELECT statement within the SQL MINUS query must have the same
number of fields in the result sets with similar data types.
Syntax for MINUS:
SELECT column_name(s) FROM
table1 INTERSECT
SELECT column_name(s) FROM table2;

OUTPUT:

SQLjoinsareusedtorelateinformationindifferenttables.Joinsareusedtocombinecolumns
fromdifferenttables.Theconnectionbetweentablesisestablishedthroughthewhereclause.
There are five types ofJoins:
 Equijoins
 CartesianJoins
 OuterJoins
 Non-EquiJoins
 SelfJoins

Equijoins:Whentwotablesarejoinedtogetherusingequalityofvaluesinoneormore columns,
they make an Equijoin. Table prefixes are utilized to prevent ambiguity and the where
clause specifies the columns being joined. Equijoins are also called Simple joins or
Innerjoins.

Syntax of Equijoin:

SELECT column_name(s) FROM table t1, table t2 WHERE condition;

30
OUTPUT:

CartesianJoin:Thecartesianproductreturnsthenumberofrowsequaltotheproduct of all
rows in all the tables joined. Cartesian product is useful in finding out all the possible
combination of columns from differenttables.

Syntax for Cartesian Join:

SELECT column_name(s) FROM table t1, table t2;

OUTPUT:

OuterJoin:TheSQLjoinconditionreturnsallrowsfrombothtableswhichsatisfythe
joinconditionalongwiththerowswhichdonotsatisfythejoinconditionfromoneof
thetables.Theouterjoinisusedinsuchcaseswhereanyvaluesinonetablethatdonot
havecorrespondingvalueinanothertable.Wehavefurtherthreetypesofouterjoin:
 Left OuterJoin
 Right OuterJoin
 Full Outer
SyntaxforLeftOuter Join:

31
 SELECTcolumn_name(s)FROMtable1LEFTOUTERJOINtable2ONco
ndition;

OUTPUT:

Syntax for Right Outer Join:

SELECTcolumn_name(s)FROMtable1RIGHTOUTERJOINta
ble2ON condition;
OUTPUT:

Syntax for Full Outer

SELECTcolumn_name(s)FROMtable1FULLOUTERJOINtable2ONco
ndition;

OUTPUT:

Non-Equi Join: When two or more tables are joined together using non equality of
values in one or more columns, they make a Non-EquiJoin.
32
Syntax for Non-Equi Join:

SELECT column_name(s) FROM table t1, table t2 WHERE condition;

OUTPUT:

SelfJoin:SelfJoinistojoinatabletoitselfmeansthateachrowofthetableiscombined
withitselfandwitheveryotherrowof thetable.Thistypeofjoin isusedwhenatable
hasaforeignkeythatreferencesitsownprimarykey.Theselfjoincanbeviewedasjoin of two
copies of the sametable.

Syntax for Self Join

SELECT column_name(s) FROM table 1 WHERE condition;

OUTPUT:

ASubqueryisaquerywithinanotherSQLqueryandembeddedwithintheWHEREclause.

Syntax for SubQuery:

SELECTcolumn_name(s)FROMtable_namewherecolumn_nameoperator
(SELECT column_name FROMtable_name);

33
OUTPUT:

There are two types of subqueries:

 Single row subquery
 Multiple row subquery
SingleRowSubQuery:AsinglerowsubqueryreturnszerooronerowtotheouterSQL
statement.YoucanplaceasubqueryinaWHEREclause,aHAVINGclause,oraFROM
clauseofaSELECTstatement.Itusesinglerowcomparisonoperators:
Operator Meaning
= Equal to
> Greater than
>= Greater than or equal to
< Less than
<= Less than or equal to
!= or <> Not equal to
OUTPUT:

Multiple row Sub Query: Multiple-row subqueries are nested queries that can return
morethanonerowofresultstotheparentquery.Multiple-rowsubqueriesareusedmost
commonlyinWHEREandHAVINGclauses.Itusesmultiplerowcomparisonoperators:

Operator Meaning
IN Equal to any member in the list.
ANY Compare value to each value returned by the subquery.
ALL Compare value to every value returned by the subquery.

34
OUTPUT:

Sequences: Sequence is a database object from which multiple users may generate
unique integers. We can use sequences to automatically generate primary key values.

Syntax for sequence:

CREATE SEQUENCE sequence_name INCREMENTED BY 1 condition(s);

OUTPUT:

Outcome:

By this process the students learns about the Set Operators, Nested Queries, Joins, Sequences

35
Experiment 5: Views, Indexes, Database Security and Privileges: Grant
and Revoke Commands, Commit and Rollback Commands.
Objective: Students will be able to gain knowledge ofViews, Indexes, Database
Security and Privileges: Grant and Revoke Commands, Commit and Rollback Commands.
Description:
VIEWS:Toreduce redundantdatatotheminimumpossible,Oracleallowsthecreationofan
object called a view. A View is mapped, to a Select sentence. This technique offers a
simple, effective way of hiding columns of atable.
Syntax for Views:

CREATE VIEW view_name AS SELECT column_list FROM table_name;

OUTPUT:

Insertion in Views: It is used to Insert rows in views. We must specify a list of values
for a particular row.
Syntax for insertion:
INSERT INTO view_name VALUES list_of_values;

OUTPUT:

UpdationinViews:Columnsinaviewcanbeupdated.Itisusedtomodifyattributevaluesof
oneormoreselectedrows.Valuesofasinglecolumnorgroupofcolumnscanbeupdated.

36
Syntax for Updation:
UPDATE view_name SET column_name=value WHERE condition;

OUTPUT:

SelectioninViews:SELECTisusedtoretrieveinformationfromtheview.
Syntax forSelection:
SELECT column_name(s) FROM view_name;

OUTPUT:

Deletion in Views: It is used to remove existing rows from a view. The entire row is
deleted from the view. A set of rows can also be deleted from the table by specifying the
condition.

Syntax for Deletion:

DELETE FROM view_name WHERE condition;

37
OUTPUT:

38
Indexes:AnIndexisadatabaseobjectthatisusedbytheservertofindarowinatablequickly.
Indexing a table is an access strategy, that is , a way to sort and search records in the
table.
Indexesareessentialtoimprovethespeedwithwhichtherecordscanbelocatedandretrieved
from a table. Indexes are of twotypes:

 SimpleIndex
 CompositeIndex

1. SimpleIndex:Anindexcreatedonasinglecolumnofatableiscalledasimpleindex.
Syntax for Simple Index:
CREATE UNIQUE INDEX index_name ON
table_name(column_name);

OUTPUT:

2. Composite Index: An Index created on more than one columns is called a composite
index.

Syntax for Composite Index:

CREATE UNIQUE INDEX index_name ON

table_name(column_name(s));

OUTPUT:

GRANT: SQL GRANT is a command used to provide access or privileges on the

database objects to theusers.

39
Syntax for GRANT:
GRANT SELECT privilege_list ON table_name TO SYSTEM;

OUTPUT:

REVOKE: The REVOKE command removes user access rights or privileges to the
databaseobjects.
Syntax for ROLLBACK:

REVOKE SELECT privilege_list ON table_name FROM SYSTEM;

OUTPUT:

ROLLBACK:TheROLLBACKcommandisthetransactionalcommandusedtoundo
transactions that have not already been saved to the database. The ROLLBACK
commandcanonlybeusedtoundotransactionssincethelastCOMMITorROLLBACK
command wasissued.
Syntaxfor ROLLBACK

Rollback;

40
41
OUTPUT:

COMMIT:TheCOMMITcommandisthetransactionalcommandusedtosavechanges
invokedbyatransactiontothedatabase.TheCOMMITcommandsavesalltransactions
tothedatabasesincethelastCOMMITorROLLBACKcommand.
Syntax for COMMIT:

Commit;

OUTPUT:

Outcome:

By this process the students learns about theViews, Indexes, Database Security and
Privileges: Grant and Revoke Commands, Commit and Rollback Commands.

42
Experiment 6: PL/SQL Architecture, Assignments and Expressions,
Writing PL/SQL Code, Referencing Non-SQL parameters.
Objective: Students will be able to gain knowledge of PL/SQL Architecture,
Assignments and Expressions, Writing PL/SQL Code, Referencing Non-SQL
parameters.
Description:

PL/SQL Architecture
 PL/SQL stands for Procedural Language Extension of
SQL.PL/SQLisacombinationofSQLalongwiththeproceduralfeaturesofprogrammin
glanguages.
 It was developed by oracle Corporation in the early 90’s to enhance the
capabilities of SQL. Execution of PL/SQLBlock:
 The PL/SQL statements written on the client side are passed to the PL/SQL
engine at server sideandalltheSQLstatementsaresendtotheSQLexecuter.
 Aftertheexecutionofwholeblockthe resultissendbacktotheclientside.
 Theexecutionofthewholeblockisdoneinonego.

43
Difference Between SQL & PL/SQL

Assignments
The assignment statement sets the current value of a variable, field, parameter, or element
that has been declared in the current scope.The assignment operator (:=) in the assignment
statement can also appear in a constant or variable declaration. In a variable declaration, it
assigns a default value to the variable. Without a default value, a variable is initialized
to NULL every time a block is entered.If a variable does not have a default value, always use
the assignment statement to assign a value to it before using it in an expression.
Syntaxassignment_statement ::=

44
Expression
An expression is an arbitrarily complex combination of operands (variables, constants, literals,
operators, function calls, and placeholders) and operators. The simplest expression is a single variable.
The PL/SQL compiler determines the data type of an expression from the types of the operands and
operators that comprise the expression. Every time the expression is evaluated, a single value of that
type results.
Syntaxexpression ::=

Program:

Compute Addition, Subtraction, Product and Division of given two numbers.

SET SERVEROUTPUT ON;
declareanumber;bnumber;
r1number;r2 number;
r3number;r4number;
begina:= &amp;
b := &amp1; r1:=a+b;
r2:=a-b; r3:=a*b; r4:=a/b;
dbms_output.put_line('Sum is: ' ||r1);
dbms_output.put_line('Differenceis:'||r2);
dbms_output.put_line('Product is: ' ||r3);
dbms_output.put_line('Division is: ' ||r4);
end;

45
OUTPUT:

Referencing Non-SQL parameters

Variable g_monthly_salary number
Define p_annual_salary=60000
Declare
v_salarynumber(9,2):= &p_annual_salary
Begin
:g_monthly_salary :=v_salary/12;
End;
/
Print g_monthly_salary

Outcome:
By this process the students learns about thePL/SQL Architecture, Assignments and
Expressions, Writing PL/SQL Code, Referencing Non-SQL parameters.

46
Experiment 7: Stored Procedures and Exception Handling.
Objective: Students will be able to gain knowledge of Stored procedures and
how to handle exception.
Description:
Procedures:ProceduresarenamedPL/SQLblocksthatcantakeparameters,performanaction
and can be invoked. A procedure is generally used to perform an action and to pass
values. Procedures are made upof:
1. A declarative part
2. An executable part
3. An optional exception handlingpart
DeclarativePart:Thedeclarativepartmaycontaindeclarationofcursors,constants,variables,
exceptions and subprograms. These objects are local to the procedure. The objects
become invalid once you exit fromit.
ExecutablePart:ThispartcontainsPL/SQLblockconsistingofstatementsthatassignvalues,
control execution and manipulate ORACLEdata.
ExceptionHandlingPart:Thispartcontainscodethatperformsactiontodealwithexceptions
raised during the execution of theprogram.
Syntax

CREATE OR REPLACE
PROCEDURE [schema] procedure_name (argument{IN,OUT,INOUT}datatype)
{IS,AS} Variabledeclarations;
Constant declarations; BEGIN
PL/SQL subprogram body; EXCEPTION
Exception PL/SQL
block; END;

Types of Procedures:
1. Local Procedure
2. Stored Procedure
Local Procedure: These procedures are declared within the PL/SQL block and called
from the begin section of the PL/SQL block.
The following is a simple example of a procedure:

47
Program:
declare
a number; b number; c number; d number; e number; f number;
procedureprocess(ainnumber,binnumber,coutnumber,doutnumber,eoutnumber,fout
number) is
begin c:=a+b; d:=a-b; e:=a*b; f:=a/b; end; begin a:=&firstnumber;
b:=&secondnumber;
process(a, b, c, d, e, f); DBMS_output.put_line(‘addition is’ || c);
DBMS_output.put_line(‘subtraction is’ || d);
DBMS_output.put_line(‘multiplicationis’||e); DBMS_output.put_line(‘divisionis’||
f);

end;

OUTPUT

Exception Handling: An exception is an error condition during a program execution.

PL/SQL supports programmers to catch such conditions using EXCEPTION block in the
program and an appropriate action is taken against the error condition. There are two types
of exceptions −System-defined exceptions, User-defined exceptions

Syntax:
WHEN exception THEN statement;

48
Program:

Outcome:

By this process the students learns about the Stored procedures and how to handle exception.

49
Experiment 8: Triggers and Cursor Management in PL/SQL
Objective: Students will be able to gain knowledge of how to managetriggers
and cursors in PL/SQL.
Description:
Triggers:Atriggerisaspecialkindofstoredprocedurethatisinvokedwheneveranattemptis
made tomodify the data in the table it protects. Triggers are automatically executed or
firedwhensome events occur. Modifications to the table are made using INSERT,
UPDATE,ORDELETEstatements.Triggersareusedtoenforcedataintegrityandbusinessrules
such asautomatically updating summary data. It allows performing cascading delete or
updatingoperations. If constraints exist on the trigger table, they are checked prior to the
triggerexecution.Ifconstraintsareviolatedstatementwillnotbeexecutedandtriggerwillnot
run.Triggers are associated with tables and they are automatic. Triggers are
automaticallyinvoked by SQL SERVER. Triggers prevent incorrect, unauthorized, or
inconsistent changestodata.
Uses of Triggers:
1. A trigger can permit DML statements against a table only if they are issued,
duringregular business hours or on predeterminedweekdays.
2. Atriggercanalsobeusedtokeepanaudit trailofatable.
3. It can be used to prevent invalidtransactions.
4. Enforce complex securityauthorizations.
5. Exceptionhandling.
6. Generation of primary key and foreignkey.
How to apply Triggers:
A trigger has three basic parts:
1. TriggeringEventorStatement:ItisaSQLstatementthatcausesatriggertobefired.It
canbeanINSERT,UPDATEorDELETEstatementforaspecifictable.
2. Trigger Restriction:A trigger restriction specifies a Boolean expression that must
be TRUE for thetrigger to fire. It is an option available for triggers that are fired
for each row. Atrigger restriction is specified using a WHENclause.
3. Trigger Action: A trigger action is the PL/SQL code to be executed when a
triggering statementis encounteredandanytriggerrestrictionevaluatestoTRUE.
Types of Triggers
• Row Triggers: A row trigger is fired each time the table is affected by the triggering
50
 statement,forexample,ifanUPDATEstatementupdatemultiplerowsofatable,arow
triggerisfiredonceforeachrowaffectedbytheUPDATEstatement.
• StatementTriggers:Arowtriggerisfiredonceonbehalfofthetriggeringstatement,
independentofthenumberofrowsthetriggeringstatementaffects.
• Before Triggers: Before triggers execute the trigger action before the triggering
statement.Thesetypesoftriggersarecommonlyusedinthefollowingsituations:
1. BEFOREtriggersareusedwhenthetriggeractionshoulddeterminewhetheror
notthetriggeringstatementshouldbeallowedtocomplete.ByusingBEFOREtrigger,
usercaneliminateunnecessaryprocessingofthetriggeringstatement.
2. BEFOREtriggersareusedtoderivespecificcolumnvaluesbeforecompletinga
triggering INSERT or UPDATEstatement.
• After Triggers: After triggers execute the trigger after the triggering statement is
executed.Thesetypesoftriggersarecommonlyusedinthefollowingsituations:
1. AFTERtriggersareusedwhenthetriggeringstatementshouldcompletebefore
executing the triggeraction.
2. IfaBEFOREtriggerisalreadypresent,anAFTERtriggercanperformdifferent
actions on the same triggeringstatement.
CombinationsTriggers:Usingtheabovetriggers,fourtypesoftriggerscouldbecreated.There
are twelve combinations oftriggers.
Before Statement Trigger: Before executing the triggering statement, the trigger action
is executed.
Before Row Trigger: Before modifying each row affected by the triggering statement
and BEFORE applying appropriate integrity constraints, the trigger is executed.
AfterStatementTrigger:Afterexecutingthetriggeringstatementandapplyinganddeferred
integrity constraints, the trigger action isexecuted.
AfterRowTrigger:Aftermodifyingeachrowaffectedbythetriggeringstatementandapplying
appropriate integrity constraints, the trigger action is executed for the current row. Unlike
BEFORE row triggers, AFTER row triggers have rowslocked.
Creation of Triggers: Triggers are created with the CREATE TRIGGER statement. This
statementspecifiesthattheonwhichtabletriggerisdefinedandonwhicheventstriggerwillbe
invoked.

Syntax:

51
DeletingaTrigger:TodropTriggeronecanuseDROPTRIGGERstatement.
Syntax:
DROP TRIGGER &lt;TriggerName&gt;

Example
Tostartwith,usingtheCUSTOMERStable Select * fromcustomers;

+ + + + + +
| ID | NAME | AGE | ADDRESS | SALARY |
+ + + + + +
| 1 | Ramesh | 32 | Ahmedabad | 2000.00 |
|2|Khilan|25|Delhi|1500.00|
|3|kaushik|23|Kota|2000.00|
| 4 | Chaitali | 25 | Mumbai | 6500.00 |
| 5 | Hardik | 27 | Bhopal | 8500.00 |
| 6 | Komal | 22 | MP | 4500.00 |
+ + + + + +
The following program creates a row-level trigger for the customers table that would fire

for INSERT or UPDATE or DELETE operations performed on the CUSTOMERS table.

Thistriggerwilldisplaythesalarydifferencebetweentheoldvaluesandnewvalues− CREATE

OR REPLACE TRIGGERdisplay_salary_changes

BEFOREDELETEORINSERTORUPDATEONcustomers FOR EACHROW

WHEN (NEW.ID &gt; 0) DECLARE

sal_diff number; BEGIN

52
sal_diff := :NEW.salary - :OLD.salary; dbms_output.put_line(&#39;Old salary: &#39;

|| :OLD.salary); dbms_output.put_line(&#39;New salary: &#39; || :NEW.salary);

dbms_output.put_line(&#39;Salary difference: &#39; || sal_diff); END;

WhentheabovecodeisexecutedattheSQLprompt,itproducesthefollowingresult−Trigger

created.

The following points need to be considered here −

 OLDandNEWreferencesarenotavailablefortable-leveltriggers,ratherusethemfor
record-leveltriggers.
 To query the table in the same trigger, then you should use the AFTER keyword,
becausetriggerscanquerythetableorchangeitagainonlyaftertheinitialchangesare
applied and the table is back in a consistentstate.
 TheabovetriggerhasbeenwritteninsuchawaythatitwillfirebeforeanyDELETE
orINSERTorUPDATEoperationonthetable,butatriggercanbewrittenonasingle or
multiple operations, for example BEFORE DELETE, which will fire whenever a
record will be deleted using the DELETE operation on thetable.

Triggering a Trigger:
INSERT INTO CUSTOMERS (ID,NAME,AGE,ADDRESS,SALARY)
VALUES (7, &#39;,Kriti&#39;, 22, &#39;HP&#39;, 7500.00 ); When a record is
createdintheCUSTOMERStable,theabovecreatetrigger,display_salary_changes
willbefiredanditwilldisplaythefollowingresult –Oldsalary:
New salary: 7500 Salary difference:
Because this is a new record, old salary is not available and the above result comes as
null.
TheUPDATEstatementwillupdateanexistingrecordinthetable–
UPDATEcustomers
SET salary = salary + 500

WHERE id = 2;
When a record is updated in the CUSTOMERS table, the above create trigger,

display_salary_changes will be fired and it will display the following result −

Old salary: 1500

53
New salary: 2000
Salary difference: 500

CURSORS:
Oracle DBA uses a work area for its internal processing. This work area is private to
SQL’s
operationsandiscalledCursor.ThedatathatisstoredinthecursoriscalledtheActiveDataSet.
Thesizeofthecursorinmemoryisthesizerequiredtoholdthenumberofrowsintheactivedata
set.
A PL/SQL block of code includes the Procedural code for looping and branching along
with the
SQLstatement.Ifrecordsfromarecordsetcreatedusingaselectstatementaretobeevaluatedand
processedoneatatime,thentheonlymethodavailableisbyusingExplicitCursors.

TYPES OF CURSORS:
 Implicitcursors.
 Explicitcursors.

ATTRIBUTESOFCURSORS:Oracleprovidescertainattributes/
cursorvariablestocontrolthe
exceptionofacursor.Wheneveranycursorisopenedandused,Oraclecreatesasetoffoursystem
variablesviawhichOraclekeepstrackofthe‘Current’statusofthecursor.Wecanaccessthese
variables.Theimplicitandexplicitcursorshavefourattributesasdescribedbelow:
1. %NOTFOUND: It evaluates to TRUE, if an insert, delete ,orupdateaffected no rows
or a singlerow

select returns no rows. Otherwise, it evaluates to FALSE.

Syntax:
cursor_name%NOTFOUND;

2. %FOUND: It is the logical opposite of %notfound. It evaluates to TRUE, if an

insert,delete,orupdateaffected one or more rows, or a single row select returns one or
more rows. Otherwise, it evaluates to FALSE.
Syntax:

54
 cursor_name%FOUND;

3. %ISOPEN:OracleautomaticallyclosestheSQLcursorafterexecutingitsassociatedSQLst
atement. As a result, sql%isopenalways evaluates to FALSE. If the explicit cursor is
open then it returns true otherwisefalse.

Syntax:
cursor_name%ISOPEN;

4. %ROWCOUNT: Returns the number of rows affected by an insert,

delete ,orupdateorselectinto statement.

Syntax:
cursor_name%ROWCOUNT;

IMPLICIT CURSORS:
Oracle implicitly opens a cursor to process each SQL statement not associated with an
explicitly
declaredcursor.PL/SQLletsusrefertothemostrecentimplicitcursorastheSQLcursor.So,altho
ugh
wecannotusetheopen,fetch,andclosestatementstocontrolanimplicitcursor,wecanstillusecur
sor attributestoaccessinformationaboutthemostrecentlyexecutedSQLstatement.

Use cursor to select the five highest paid employees from the emp table showing tables
also.

55
Input Table:

Declare
cursorc1isselectename,empno,salfromemporderbysaldesc;--
startwithhighestpaidemployee my_enamevarchar2(10);
my_empnonumber(4);
my_salnumber(7,2);
Begin open c1;
fori in 1..5 loop
fetchc1intomy_ename,my_empno,my_sal; exit whenc1%notfound;
insertintotempvalues(my_sal,my_empno,my_ename);
commit;
end loop;
close c1;
end;

Output Table:

Outcome:

By this process the students learns about the Triggers and Cursor Management in PL/SQL.

56
Experiment 9: Study of Web Databases.
Objective: Students will be able to gain knowledge of web databases.
Description:
Web Database: A web database is a wide term for managing data online. A web database
gives you the ability to build your own databases/data storage without you being a database
guru or even a technical person.
Examples: banks, airline and rental car reservations, university course registration and so on
 The Web is a distributed information system base on hypertext.
 Most Web documents are hypertext documents formatted via HTML
 HTML Documents contain
 Text along with font specifications, and other formatting instructions
 Hypertext links to other documents , which can be associated with region of the text.
Data Organization
Web databases enable collected data to be organized and cataloged thoroughly within
hundreds of parameters. The Web database does not require advanced computer skills, and
many database software programs provide an easy “click-and-create” style with no
complicated coding. Fill in the fields and save each record. Organize the data however you
choose, such as chronologically, alphabetically or by a specific set of parameters.
Web Database Software
Web database software programs are found within desktop publishing programs, such as
Microsoft Office Access and OpenOffice Base. Other programs include the Webex
WebOffice database and FormLogix Web database. The most advanced software applications
can set up data collection forms, polls, feedback forms and present data analysis in real time.
Applicable Uses
Businesses both large and small can use Web databases to create website polls, feedback
forms, client or customer and inventory lists. Personal Web database use can range from
storing personal email accounts to a home inventory to personal website analytics. The Web
database is entirely customizable to an individual’s or business’s needs.
MySQL
Often in the world of Web databases, MySQL (structured query language) will be mentioned.
This is a relational database management system that manages different Web databases. It
operates as a server, and is an open source project. MySQL is often included with Web

57
hosting for managing either personal or business website databases. It is a programming
language, so is a more difficult to work with than a straight Web database software program.
Database Applications and the Web :
Most of the services we enjoy on the Web are provided by web database applications. Web-
based email, online shopping, forums and bulletin boards, corporate web sites, and sports and
news portals are all database-driven. To build a modern web site, you need to develop a
database application.The most popular database management system used in these solutions
is MySQL, a very fast and easy-to-use system distributed under an Open-Source license by
its manufacturer, MySQL AB.
With a web server such as Apache (we assume Apache in this book, although the software
discussed here works with other web servers as well) and MySQL, you have most of what
you need to develop a web database application. The key glue you need is a way for the web
server to talk to the database; in other words, a way to incorporate database operations into
web pages. The most popular glue that accomplishes this task is PHP.PHP is an open-source
project of the Apache Software Foundation and it’s the most popular Apache web server add-
on module, with around 53% of the Apache HTTP servers having PHP capabilities. PHP is
particularly suited to web database applications because of its integration tools for the Web
and database environments. In particular, the flexibility of embedding scripts in HTML pages
permits easy integration of HTML presentation and code. The database tier integration
support is also excellent, with more than 15 libraries available to interact with almost all
popular database servers.
Apache, MySQL, and PHP can run on a wide variety of operating systems. In this book, we
show you how to use them on Linux, Mac OS X, and Microsoft Windows.
This is an introductory book, but it gives you the sophisticated knowledge you need to build
applications properly. This includes critical tasks such as checking user input, handling errors
robustly, and locking your database operations to avoid data corruption. Most importantly,
we explain the principles behind good web database applications. You’ll finish the book with
not only the technical skills to create an application, but also an appreciation for the strategies
that make an application secure, reliable, maintainable, and expandable.

The Web
When you browse the Web, you use your web browser to request resources from a web server
and the web server responds with the resources. You make these requests by filling in and
submitting forms, clicking on links, or typing URLs into your browser. Often, resources are

58
static HTML pages that are displayed in the browser. This is the classic two-tier or client-
server architecture used on the Web.

A two-tier architecture where a web browser makes a request and the web server responds

A web server is not sophisticated storage software. Complicated operations on data, done by
commercial sites and anyone else presenting lots of dynamic data, should be handled by a
separate database. This leads to a more complex architecture with three-tiers: the browser is
still the client tier, the web server becomes the middle tier, and the database is the third or
database tier. A web browser requests a resource that’s generated from a database, and how
the database and web server respond to the request.

A three-tier architecture where a web browser requests a resource, and a response is

generated from a database

Three-Tier Architectures
This book shows you how to develop web database applications that are built around
the three-tier architecture model. At the base of an application is the database tier, consisting
of the database management system that manages the data users create, delete, modify, and
query. Built on top of the database tier is the middle tier, which contains most of the
application logic that you develop. It also communicates data between the other tiers. On top
is the client tier, usually web browser software that interacts with the application.

59
 The three-tier architecture model of a web database application
The three-tier architecture is conceptual. In practice, there are different implementations of
web database applications that fit this architecture. The most common implementation has the
web server (which includes the scripting engine that processes the scripts and carries out the
actions they specify) and the database management system installed on one machine: it’s the
simplest to manage and secure, and it’s our focus in this book. With this implementation on
modern hardware, your applications can probably handle tens of thousands of requests every
hour.

For popular web sites, a common implementation is to install the web server and the database
server on different machines, so that resources are dedicated to permit a more scalable and
faster application. For very high-end applications, a cluster of computers can be used, where
the database and web servers are replicated and the load distributed across many machines.
Our focus is on simple implementations; replication and load distribution are beyond the
scope of this book.

Describing web database applications as three-tier architectures makes them sound formally
structured and organized. However, it hides the reality that the applications must bring
together different protocols and software, and that the software needs to be installed,
configured, and secured. The majority of the material in this book discusses the middle tier
and the application logic that allows web browsers to work with databases.

Features of Web database:

1. Save Money
One of the advantages of online database software is that it can save your business money.
When you don’t need to buy a software program for your business, this could result in a
major savings overall. In most cases, businesses pay for a software program and then pay for
a licensing fee for each computer that uses it. Using an online database may prove cheaper,

60
depending on the number of computers you use. You also don’t need to invest in servers to
store the data at your business.
2. Flexible Use
Another benefit of using an online database program is that it allows your business to be
flexible. You only pay for the amount of storage that you use. You need not worry about
purchasing servers as you go or eliminating them when they are no longer needed. If your
business grows or shrinks, you do not need to be concerned about the costs of database
management software or servers.
3. Technical Support
Another advantage of using a Web-based database program is that you can shift the technical
support burden to someone else. Paying a company for access to an online database includes
technical support. If the database has problems, you simply contact the the company and the
staff handles it. You don’t need to pay for an information technology professional for this
purpose. If you already have an IT department, your employees can focus on other things.
4. Access
Having access to the database at all times from multiple locations is another major advantage
of this type of database. With an online database, you could theoretically access the
information in the database from any computer. The information is also available 24 hours a
day, seven days a week. This means that all employees have access to the same information
and can collaborate with one another on projects — regardless of location. This advantage
can increase productivity and improve efficiency.
 It’s based on a file management system (no actual database)
 It is a table with several million entries, each entry being a keyword and a related
keyword, plus metrics that measure the quality of the match (how strong the
relationship between the two keywords is), as well as frequencies attached to these
two keywords, and when they are jointly found. The function to measure the quality
of the match can be customized by the user.
Outcome:

By this process the students learns about the web database.

61
Experiment 10: Installation of Database Server.
Objective: Students will be able to gain knowledge of how to install database
server.
Description:
This explain the step by step installation process of MySQL database server. MySQL is open-
source, cross-platform relational database management server developed by Swedish
company “MySQL AB” and later acquired by Oracle corporation. MySQL is offered as an
open-source MySQL community server edition and enterprise server edition. Hence we will
install the MySQL Community server edition.

Download and install MySQL database server

You can download the MySQL community server from the location given below.
https://dev.mysql.com/get/Downloads/MySQLInstaller/mysql-installer-community-
8.0.19.0.msi
Once the installer has been downloaded, double-click the setup file to start the installation
process. On the Choosing a Setup Type page, you can see four installation options.
1. Developer default: If you want to create a development machine, you can use this
option. It installs the components which are required for application development,
e.g., MySQL Server, MySQL Shell, MySQL connectors, MySQL
2. Server Only: If you want to create a standalone database server with specific
components, you can use this option
3. Full: If you want to install MySQL Server with its all components, then you can use
this option
4. Custom: If your requirements are limited to the few components, you can use this
option
We are going to install MySQL Server with all components; hence, choose “Full” and click
on Next.

62
Before installation begins, the installer checks all the prerequisites that are required to install
all the components of the MySQL database server. If any software prerequisites are
missing, then you can see the details of failing requirements on the “Check
Requirements” screen. It shows the name of the product, required component/software, and
its status. As you can see, to install the MySQL database server for visual studio, we must
install visual studio 2015 or above. Similarly, to install Python connector, we must install
python on the work station. Click on Next.

63
An installer gives us a warning. We can continue our installation without installing the visual
studio and python. Click on Yes.

On the Installation screen, you can see the list of the MySQL products/software that are going
to be installed on my workstation. Review the list and click on Execute.

64
The installer downloads all the products/software. After that, it installs all the products. Wait
for a few mins. Once the installation process completes, we are ready to configure the
MySQL database server and other components. Click on Next.
On the Product configuration screen, you can see the list of the products that need to be
configured. First, let us configure the MySQL Server. Click on Next.

On the High availability screen, we can choose to install the InnoDB cluster or Standalone
MySQL Server. InnoDB cluster is the High availability solution of MySQL. It uses group
replication. I will explain more about it in my future series of articles. We are going to
perform a standalone installation of MySQL Server hence choose “Standalone MySQL
Server / Classic MySQL Replication”.
On Type and Networking screen, we can configure the following:
The type of MySQL configuration.
The type of MySQL configuration is a predefined set of configuration parameter that
determines how much resources should be allocated to the MySQL Services. You have three
configuration options:
1. Development Computer: This configuration uses a minimal amount of the resources
to MySQL Service
2. Server Computer: This configuration uses a minimal number of resources. This
option is suitable when we are installing database servers and web servers on the same
machine. The configuration allocates an average amount of resources to MySQL
Service
3. Dedicated Computer: This option is used when we have created a dedicated MySQL
Server. The configuration allocates a high amount of resources to MySQL Service

65
We would configure the server with minimal resources hence select “Development
computer” from the Config Type drop-down box.

Network Connectivity
In this section, we can control how clients can connect to MySQL databases. We can use
TCP/IP protocol or Named Pipe or Shared Memory. If you want to configure Named Pipe /
Shared Memory, we must provide the Pipe Name and Memory Name. You can also specify
the default port to connect to the database server. You can also choose to allow the port
number specified in Port textbox in the firewall. See the following image:

In MySQL 8.0 version, we can use SHA256 based strong passwords. On the Authentication
Method screen, choose the option to use the Legacy authentication method or Strong
password for authentication. Note: If you are using Strong Password Encryption for
Authentication, then make sure that all the connectors must be updated to the latest version.
We are going to use Strong password Encryption for Authentication.
66
On Accounts and Roles screen, you can specify the MySQL root account password. MySQL
Root account is a default sysadmin account, and it must be disabled.

You can also create other users to do that click on Add user. On MySQL User account dialog
box, provide a username, hostname, Role of the User, type of authentication, and password.
Once the user is created, click on Next. See the following image:

On the Windows Service screen, you can configure the MySQL server to run as a windows
service. You can provide the desired name and configure it to auto-start the service when the
system reboots. Moreover, you can provide the credentials under which the MySQL Service
will run. You can choose the standard system account or provide a specific user. See the
following image:

67
On the Apply Configuration screen, you can see the list of confirmation steps. Once all the
configuration settings are verified, click on Execute.
The MySQL installation process starts. You can view the installation process in the “Log”
tab. Once installation completes successfully, click on “Finish” to close the installer.

Install the sample database

If you have chosen to install all the components of MySQL Server (Full Setup Type),
MySQL installer moves to Sample and Example screen. On this screen, provide username
and password of the user that has root/sysadmin privileges and click on Check. If the
connection establishes successfully, click on next. See the following image:

68
On the Apply Configuration Screen, click on Execute to start the installation of the Sample
database. See the following:

Once the sample database has been installed, click on the Finish button.
The installer continues to the Product Configuration screen. On this screen, you can see that
the installation of the MySQL Server 8.0.19 and Sample and Example 8.0.19 has been
completed successfully. See the following image:

69
Once the installation completes, you can copy the installation logs on the clipboard to review
it later. Moreover, if you want to start exploring MySQL straight away, then you can select
“Start MySQL workbench after Setup” and “Start MySQL Shell after Setup” and click
on Finish. See the following image:

Connect to MySQL Server

Once the installation completes, let us connect to the server and execute the first MySQL
Query. Open MySQL workbench. Just like SQL Server management studio, MySQL
workbench is the development tool which is used to querying the database and create
database objects.
On MySQL workbench welcome screen, you can see the list of MySQL connections. We
have not configured multiple connections; hence you can see “Local instance MySQL80.”
Click on it to open the new query editor window.
When you click on the connection, you must enter the credentials to connect the database
server. Enter the password and click on OK.
70
First, let’s create a simple database on MySQL Server. Write the following query in the query
editor window and click on execute. See the following image:

Once the query executes successfully, you can see the new database in the “SCHEMAS” pan.
See the following image:

71
Outcome:

By this process the students learns about the installation of database server.

72