isl lab manual for teit uop

INFORMATION SYSTEM LAB

PIMPRI CHINCHWAD COLLEGE OF ENGINEERING

DEPARTMENT OF INFORMATION TECHNOLOGY

LAB MANUAL

INFORMATION SYSTEM LAB (TE 2003)310445

SOFTWARES REQUIRED: MS-Office, Microsoft Visual Studio 6.0, Oracle 9iACADEMIC YEAR WITH SEM: TE 2009-10 (SEM-I)

PREPARED BY: HOD IT(Ms. Anagha Chaudhari) (Prof. S. V. Shinde) (Subject In charge)

8 REGISTRATION FORM


INDEX

SR NO.

TITLE OF EXPERIMENT PAGE NO.

1. Design & implement Scientific Calculator with all functionalities.

1-7

2. Design a Registration Form for any application with the help of Active-X Control objects.

8-11

3. Create Text Editor including all functionalities such as Menu bar, Status bar, Progress bar etc.

12-16

4. Develop a Mini-Project on Front end application. 17-18

5. Design & draw an ER diagram using standard notations for given problem statement.

19-24

6. Create database tables & Indices in MS-Access for the same problem statement of the ER diagram.

25-26

7. Write about different Normal Forms & Explain its implementation in the previous database.

27-31

8. Create database tables in Oracle using DDL & DML Commands as Insert, Delete, Update and Modify.

32-38

9. Use Text manipulation functions on the same problem statement as Aggregation func., group by, order by, having clause etc.

39-43

10. Write & execute Triggers, Procedures & Functions using PL/SQL block on the same database.

44-54

11. Develop a Mini-Project on Front end & Back end application. 55-58

8 REGISTRATION FORM


EXPERIMENT NO. 1

TITLE: SCIENTIFIC CALCULATOR

AIM: To design & implement Scientific Calculator with all functionalities.

PRE REQUISITE: ---

THEORY: A brief description of Visual Basic

Introduction:

VISUAL BASIC is a VISUAL and events driven Programming Language. In VISUAL BASIC, programming is done in a graphical environment. Because users may click on a certain object randomly, so each object has to be programmed independently to be able to response to those actions (events). Therefore, a VISUAL BASIC Program is made up of many subprograms, each has its own program codes, and each can be executed independently and at the same time each can be linked together in one way or another.

Visual Basic Integrated Development Environment (IDE).

The Visual Basin IDE has 4 distinct states: Design, Run & Debug.

The IDE is composed of:

a) The Menu barb) The Tool barc) The Project Explorerd) The Properties Windowe) The Form Layout Windowf) The Tool boxg) Form Designersh) Code Window

8 REGISTRATION FORM


Textbox Control

Windows Forms text boxes are used to get input from the user or to display

text. The TextBox control is generally used for editable text, although it can also be

made read-only. Text boxes can display multiple lines, wrap text to the size of the

control, and add basic formatting. The TextBox control allows a single format for

text displayed or entered in the control.

Label Control: Windows Forms Label controls are used to display text or images that

cannot be edited by the user. They are used to identify objects on a form—to provide a description of what a certain control will do if clicked, for example, or to display information in response to a run-time event or process in your application. Because the Label control cannot receive focus, it can also be used to create access keys for other controls.

Button Control:

The Windows Forms Button control allows the user to click it to perform an

action. The Button control can display both text and images. When the button is

clicked, it looks as if it is being pushed in and released.

PictureBox Control:

The Windows Forms PictureBox control is used to display graphics in

bitmap, GIF, JPEG, metafile, or icon format.

NumericUpDown Control:

The Windows Forms NumericUpDown control looks like a combination of a

text box and a pair of arrows that the user can click to adjust a value. The control

displays and sets a single numeric value from a list of choices. The user can increase

and decrease the number by clicking up and down buttons, by pressing the UP and

DOWN ARROW keys, or by typing a number. Clicking the UP ARROW key moves

the value toward its maximum; clicking the DOWN ARROW key moves the

position toward the minimum. An example where this kind of control might be

useful is for a volume control on a music player. Numeric up-down controls are used

in some Windows control panel applications.

8 REGISTRATION FORM


Programming Components in Visual Basic

Variables in Visual Basic:

You often need to store values when performing calculations with Visual

Basic. For example, you might want to calculate several values, compare them, and

perform different operations on them, depending on the result of the comparison.

You need to retain the values if you want to compare them.

Usage:

Visual Basic, like most programming languages, uses variables for storing

values. A variable has a name (the word that you use to refer to the value the

variable contains) and a data type (which determines the kind of data that the

variable can store). A variable can represent an array if it needs to store an indexed

set of closely related data items.

Data Types:

Visual Basic type Common language runtime type structure

Nominal storage allocation

Boolean BooleanDepends on implementing platform

Byte Byte 1 byte

Char (single character) Char 2 bytes

Date DateTime 8 bytes

Decimal Decimal 16 bytes

Double (double-precision floating-point)

Double 8 bytes

Integer Int32 4 bytes

Long (long integer) Int64 8 bytes

Object Object (class)

4 bytes on 32-bit platform 8 bytes on 64-bit platform

8 REGISTRATION FORM


SByte SByte 1 byte

Short (short integer) Int16 2 bytes

Single (single-precision floating-point)

Single 4 bytes

String (variable-length)String (class)

Depends on implementing platform

UInteger UInt32 4 bytes

ULong UInt64 8 bytes

User-Defined (structure)

(inherits from ValueType)

Depends on implementing platform

UShort UInt16 2 bytes

Arrays in Visual Basic:

An array is a set of values that are logically related to each other, such as the

number of students in each grade in a grammar school.An array allows you to refer

to these related values by the same name and to use a number, called an index or

subscript, to tell them apart. The individual values are called the elements of the

array. They are contiguous from index 0 through the highest index value.

Example:

The following example declares an array variable to hold the number of

students in each grade in a grammar school.

Dim students (6) As Integer

The array student in the preceding example contains 7 elements. The indexes

of the elements range from 0 through 6. Having this array is simpler than declaring

7 different variables.

Array Types and Other Types:

Every array has a data type, but it is not the same as the data type of its elements.

For example, the array students in the preceding example is of type

8 REGISTRATION FORM


Integer(), while each of its elements is of type Integer. The notation Integer() means

an array of Integer elements.

Every array inherits from the System. Array class, and you can declare a variable to

be of type Array, but you cannot create an array of type Array. Also, the ReDim

Statement (Visual Basic) cannot operate on a variable declared as type Array. For

these reasons, and for type safety, it is advisable to declare every array as a specific

type, such as Integer in the preceding example.

Array Dimensions:

The array students in the preceding example use one index and is said to be

one-dimensional. An array that uses more than one index or subscript is called

multidimensional.

Another kind of array is one which holds other arrays as elements. This is

known as an array of arrays or a jagged array. A jagged array can be either one-

dimensional or multidimensional, and so can its elements.

Control Flow in Visual Basic:

Left unregulated, a program proceeds through its statements from beginning

to end. Some very simple programs can be written with only this unidirectional

flow. However, much of the power and utility of any programming language comes

from the ability to change execution order with control statements and

loops.Control structures allow you to regulate the flow of your program's execution.

Using control structures, you can write Visual Basic code that makes decisions or

that repeats actions. Other control structures let you guarantee disposal of a

resource or run a series of statements on the same object reference.

8 REGISTRATION FORM


ALGORITHM: ---

INPUT/OUTPUT:

INPUT: 1. To get started with Microsoft Visual Basic Studio 6.0. 2. Drag the required tools from the Toolbox on the design form. 3. Complete the design view with setting properties. 4. Write the Code for the corresponding design in proper events.OUTPUT:

1. After completing the code for the design, try to execute & run the design.2. If any errors, try to debug them & again execute.

OPERATIONAL STEPS REQUIRED:

1. Go to START.2. Go to PROGRAMS.3. Select Microsoft Visual Basic Studio 6.0 tool.4. Select the new Windows application template to start the new VB project.5. View the Menu bar, Toolbox window, Properties window, Form layout

window, Project Explorer, Form designers & Code window.6. Start with the application.

APPLICATION: 1. To design a Simple Calculator & also Scientific Calculator. 2. To design any application using basic VB tools & Control arrays.

CONCLUSION:

By the use of basic VB tools, properties, Events, methods & Control array structures, we can implement the Simple as well as Scientific Calculator.

8 REGISTRATION FORM


FAQ’S:

1. What is Visual Basic tool?2. What are the advantages of using VB?3. Explain the VB IDE?4. Different tools of Visual Basic application.5. Their properties, methods & events.6. What is Control Array Structure?7. Code for different operations in Scientific Calculator.

8 REGISTRATION FORM


EXPERIMENT NO. 2

TITLE: REGISTRATION FORM

AIM: To design a registration form for any application with the help of Active-X control.

PRE REQUISITE: Previous Assignment 1.

THEORY:

Textbox Control

Windows Forms text boxes are used to get input from the user or to display

text. The TextBox control is generally used for editable text, although it can also be

made read-only. Text boxes can display multiple lines, wrap text to the size of the

control, and add basic formatting. The TextBox control allows a single format for

text displayed or entered in the control.

Also include the basic properties of text box as:Multiline, Scrollbars & Maxlength.

RadioButton Control:

Windows Forms RadioButton controls present a set of two or more mutually

exclusive choices to the user. While radio buttons and check boxes may appear to

function similarly, there is an important difference: when a user selects a radio

button, the other radio buttons in the same group cannot be selected as well.

CheckBox Control:

The Windows Forms CheckBox control indicates whether a particular

condition is on or off. It is commonly used to present a Yes/No or True/False

selection to the user. You can use check box controls in groups to display multiple

8 REGISTRATION FORM


choices from which the user can select one or more. It is similar to the RadioButton

control, but any number of grouped CheckBox controls may be selected.

ListBox Control:

The ListBox control enables you to display a list of items to the user that the

user can select by clicking. A ListBox control can provide single or multiple

selections using the SelectionMode property. The ListBox also provides the

MultiColumn property to enable the display of items in columns instead of a

straight vertical list of items. With this, the control can display more visible items

and the user no longer needs to scroll to an item.

Typically, Windows handles the task of drawing the items to display in the

ListBox. You can use the DrawMode property, and handle the MeasureItem and

DrawItem events so you can override the automatic drawing that Windows provides

and draw the items yourself. You can use owner-drawn ListBox controls to display

variable-height items, images, or a different color or font for the text of each item in

the list. The HorizontalExtent property, GetItemHeight, and GetItemRectangle also

help you draw your own items.

In addition to display and selection functionality, the ListBox also provides

features that enable you to efficiently add items to the ListBox and to find text

within the items of the list. The BeginUpdate and EndUpdate methods enable you to

add a large number of items to the ListBox without the control being repainted each

time an item is added to the list. The FindString and FindStringExact methods

enable you to search for an item in the list that contains a specific search string.

The Items, SelectedItems, and SelectedIndices properties provide access to

the three collections that are used by the ListBox. The following table outlines the

three collections used by the ListBox and their use within the control.

Panel Control:

Windows Forms Panel controls are used to provide an identifiable grouping

for other controls. Typically, you use panels to subdivide a form by function. The

Panel control is similar to the GroupBox control; however, only the Panel control

can have scroll bars, and only the GroupBox control displays a caption.

8 REGISTRATION FORM


ProgressBar Control:

The Windows Forms ProgressBar control indicates the progress of an action

by displaying an appropriate number of rectangles arranged in a horizontal bar.

When the action is complete, the bar is filled. Progress bars are commonly used to

give the user an indication of how long to wait for a protracted action to complete—

for instance, when a large file is being loaded.

a) Write about the Active X Control Objects, their need & importance.

b) Write about how to design Active X (Custom) Control.

c) Write about the message box, its syntax & Input box with its syntax.

d) Write about the KeyPress Events which helps us to use ASCII code for

integers, characters with its syntax.

ALGORITHM: ---

INPUT/OUTPUT:

INPUT: 1. To get started with Microsoft Visual Basic Studio 6.0. 2. Drag the required tools from the Toolbox on the design form. 3. Complete the design view with setting properties. 4. Write the Code for the corresponding design in proper events.OUTPUT:



1. Go to START.2. Go to PROGRAMS.3. Select Microsoft Visual Basic Studio 6.0 tool.4. Select the new Windows application template to start the new VB project.

8 REGISTRATION FORM


5. View the Menu bar, Toolbox window, Properties window, Form layout window, Project Explorer, Form designers & Code window.

6. Start with the application.

APPLICATION:

1. To design a Registration Form. 2. To design any Form using basic VB tools & Active X Control tools & objects. 3. We can also use the Input Box, Message Box & Prompt Box.

CONCLUSION:

By the use of basic VB tools, properties, Events, methods & Active X Control objects, we can implement any type of form or application. Also we learnt to design a Message box, Input box, Prompt Box & Key press Events which includes the ASCII code of numbers & characters.

FAQ’S:

1. What are ActiveX Control objects?2. What are Key Press Events?3. What is Msgbox & Inputbox?4. What is the Multiline property?5. What is the Multiselect property?6. What is Custom Control?

8 REGISTRATION FORM


EXPERIMENT NO. 3

TITLE: TEXT EDITOR

AIM: To create a Text Editor including all functionalities.

PRE REQUISITE: Previous Assignments 1 & 2.

THEORY:

RichTextBox Control:

The Windows Forms RichTextBox control is used for displaying, entering,

and manipulating text with formatting. The RichTextBox control does everything

the TextBox control does, but it can also display fonts, colors, and links; load text

and embedded images from a file; undo and redo editing operations; and find

specified characters. The RichTextBox control is typically used to provide text

manipulation and display features similar to word processing applications such as

Microsoft Word. Like the TextBox control, the RichTextBox control can display

scroll bars; but unlike the TextBox control, it displays both horizontal and vertical

scrollbars by default and has additional scrollbar.

TabControl Control:

The Windows Forms TabControl displays multiple tabs, like dividers in a

notebook or labels in a set of folders in a filing cabinet. The tabs can contain

pictures and other controls. Use the TabControl to create property pages.

Decision Structures

Visual Basic allows you to test conditions and perform different operations

depending on the results of that test. You can test for a condition being true or false,

for various values of an expression, or for various exceptions generated when you

execute a series of statements.

8 REGISTRATION FORM


If...Then...Else Construction

If...Then...Else constructions allow you to test for one or more conditions and run

one or more statements depending on each condition. You can test conditions and

take actions in the following ways:

Run one or more statements if a condition is True

Run one or more statements if a condition is False

Run some statements if a condition is True and others if it is False

Test an additional condition if a prior condition is False

The control structure that offers all these possibilities is the If...Then...Else

Statement (Visual Basic). You can use a single-line version if you have just one test

and one statement to run. If you have a more complex set of conditions and actions,

you can use the multiple-line version.

Select...Case Construction

The Select...Case construction allows you to evaluate an expression once and run

different sets of statements based on different possible values.

Try...Catch...Finally Construction

Try...Catch...Finally constructions allow you to run a set of statements under an

environment that retains control if any of your statements causes an exception. You

can take different actions for different exceptions. You can optionally specify a

block of code that is to run before you exit the entire Try...Catch...Finally

construction, no matter what happens.

Loop Structures:

Visual Basic loop structures allow you to run one or more lines of code

repetitively. You can repeat the statements in a loop structure until a condition is

8 REGISTRATION FORM


True, until a condition is False, a specified number of times, or once for each

element in a collection.

While Loops

The While...End While construction runs a set of statements as long as the

condition specified in the While statement is True.

Do Loops

The Do...Loop construction allows you to test a condition at either the

beginning or the end of a loop structure. You can also specify whether to repeat the

loop while the condition remains True or until it becomes True

For Loops

The For...Next construction performs the loop a set number of times. It uses

a loop control variable, also called a counter, to keep track of the repetitions. You

specify the starting and ending values for this counter, and you can optionally

specify the amount by which it increases from one repetition to the next.

For Each Loops

The For Each...Next construction runs a set of statements once for each

element in a collection. You specify the loop control variable, but you do not have to

determine starting or ending values for it.

Other Control Structures

Visual Basic provides control structures that help you dispose of a resource

or reduce the number of times you have to repeat an object reference.

Using...End Using Construction

The Using...End Using construction establishes a statement block within

which you make use of a resource such as a SQL connection. You can optionally

acquire the resource with the Using statement. When you exit the Using block,

8 REGISTRATION FORM


Visual Basic automatically disposes of the resource so that it is available for other

code to use. The resource must be local and disposable.

With...End With Construction

The With...End With construction allows you to specify an object reference

once and then run a series of statements that access its members. This can simplify

your code and improve performance because Visual Basic does not have to re-

establish the reference for each statement that accesses it.

Write about Common Dialog Box & its different types.

Properties, methods & Events of each common dialog box.

Some of the Common properties are;

a) CancelError

b) DialogTitle

c) Flags

d) Min & Max

Also, write about the Multiple Document Interface (MDI) Form.

ALGORITHM: ---

INPUT/OUTPUT:

1. To get started with Microsoft Visual Basic Studio 6.0. 2. Drag the required tools from the Toolbox on the design form. 3. Complete the design view with setting properties. 4. Write the Code for the corresponding design in proper events.OUTPUT:


8 REGISTRATION FORM



1. Go to START.2. Go to PROGRAMS.3. Select Microsoft Visual Basic Studio 6.0 tool.4. Select the new Windows application template to start the new VB project.5. View the Menu bar, Toolbox window, Properties window, Form layout

window, Project Explorer, Form designers & Code window.6. Start with the application.

APPLICATION:

1. To design a Text Editor. 2. We can also use the MDI Form, Color, Font, Print, File Open, File Save, Help common dialog boxes & Rich Text Boxes to design front end.

CONCLUSION:

By the use of basic VB tools, properties, Events, methods & Common Dialog boxes, we can implement any type of text editor. Also we learnt to design a MDI Form, Color, Font, Print, File Open, File Save, Help common dialog boxes & Rich Text Boxes.

FAQ’S:

1. What is a Common Dialog Box?2. Different types of Common dialog boxes?3. What is a MDI form?4. What is a RichText Box?5. What is MaxFileSize property?6. What is CancelError property?

8 REGISTRATION FORM


EXPERIMENT NO. 4

TITLE: MINI PROJECT ON FRONT END

AIM: To develop a Mini Project on Front End as VB.

PRE REQUISITE: Previous Assignments 1, 2 & 3.

THEORY:

1. Write about the tools in brief used in the design view of the project.2. If any Multimedia Or Animation is used, write about the functions used with

their syntax.

ALGORITHM: ---

INPUT/OUTPUT:

1. To get started with Microsoft Visual Basic Studio 6.0. 2. Drag the required tools from the Toolbox on the design form. 3. Complete the design view with setting properties. 4. Write the Code for the corresponding design in proper events.OUTPUT:



1. Go to START.2. Go to PROGRAMS.3. Select Microsoft Visual Basic Studio 6.0 tool.4. Select the new Windows application template to start the new VB project.

8 REGISTRATION FORM


5. View the Menu bar, Toolbox window, Properties window, Form layout window, Project Explorer, Form designers & Code window.

6. Start with the application.

APPLICATION:

1. To design any type of Mini Project with VB as Front end as it is user friendly.

CONCLUSION:

1. After implementing all previous 3 assignments, we have designed a mini project on front end only. Here we have used VB as our front end to make it more user interactive.

FAQ’S:

1. Explain the project in detail.2. How reports are generated in the project?3. How the forms are linked?4. Where ActiveX control tools are used?

8 REGISTRATION FORM


EXPERIMENT NO. 5

TITLE: ER/EER DIAGRAM

AIM: To design & draw an ER/EER Diagram using standard notations for given problem statement definition & map these diagrams into database tables.

PRE REQUISITE: Unit 1.

THEORY:

Data Model: A data model is a collection of conceptual tools for describing the data ,data

relationships and consistency constraints.Entity Relationship Model:

It perceives the real world as consisting of basic objects, called entities and relationships among these objects.Entity:

An Entity is a “thing” or “object” in the real world that is distinguishable from all other objects.For e.g person, book, student etc. Entity Set:

An entity set is a set of entities of the same type that share the same properties of attributes. For e.g the set of all persons who are customers at a given bank can be defined as an entity set customerAn entity is represented by a set of attributes.Attributes:

Attributes are descriptive properties possessed by each member of an entity set.Relationship:

An association among number of entities.Relationship Set:

Set of Relationships of the same type, associating same entity sets.A relationship may also have attributes called descriptive attributes.Types of Attributes:1.Composite attribute:

An attribute that can be divided into sub parts is called composite attribute.2. Multivalued attribute:

An attribute that takes more than one values for the same entity is called multivalued attribute.

8 REGISTRATION FORM


3.Derived attribute: The value for this type of attribute can be derived from the values of other related

attributes or entities.The value of the derived attribute is not stored but it is computed when required.Role:

A function that an entity plays in a relationship is called that entities role.

Constraints- ER schema defines constraints to which DB system must conform:

1) Mapping cardinalities/ Cardinality ratios It expresses no. of entities to which another entity can be associated via a

relationship set. For Binary Relationship set between entity sets namely A, B, mapping cardinalities must be one of following:

One to one – One entity of A is associated with exactly one entity of B and vice versa.

One to many - One entity of A is associated with zero or more entities of B and one entity of B is associated with exactly one entity of A.

Many to one - One entity of A is associated with exactly one entity of B and one entity of B is associated with one or more entities of A.

Many to many - One entity of A is associated with one or more entities of B and vice versa.

2) Participation constraintsAn entity set E participates in a relationship set R either totally or partially.

Participation of Entity set E in Relationship set R is said to be total if all entities of E are present in at least one relationship of R. otherwise it is said to be partial. Extended ER features-These include specialization, generalization, inheritance, and aggregation.

Specialization: This is a top-down approach of design. It is also called as Super class-

Subclass relationship. Subclasses are derived from super class with some special attributes.

Generalization: This is reverse of Specialization. This is a bottom - up approach of design. We

take out common attributes from subclasses & form super class. Inheritance:

All attributes are inherited by all subclasses from the super class. Aggregation:

Treating a relationship set as a high level entity set is called as aggregation. This is useful in establishing relationship among relationships.

8 REGISTRATION FORM


Database system design using ERD has following steps:

1. Data Requirements : Define scope of the system. Write the characteristics of the system (i.e. the

functionalities), which satisfy the user requirements, from all user points of view.2. Entity sets designation :

From the characteristics listed above, identify entity sets & their attributes.

3. Relationship sets designation: Specify Relationship sets which associate different entity sets together. Specify

their mapping cardinalities.4. ER D:

Using 1,2,3 draw complete ERD, which is a representation of a conceptual schema of the DB system.

Notations used to draw ERD:

Entity set – Rectangle -

Weak entity set - Double rectangle

Attribute of an Entity set – Ellipse

Multivalued attribute- Double ellipse

Derived attribute - Dashed ellipse

Relationship set – Diamond

8 REGISTRATION FORM

E

E

A

A

A

R


Identifying relationship set – Double diamond

Primary Key

Discriminating attribute of weak entity set -

1. One – to one relationship set

2. One to many relationship set

3.Many to one relationship set

4. Many to many relationship set

8 REGISTRATION FORM

R

A

Discriminator

E1 E2R

E1

E1 R

R

E2

E2

Discriminator

A


Cardinality limits can also be shown as below:

l..h

Where l & h are lower & higher cardinality limits respectively.i.e. an entity of E1 can be associated with minimum l or maximum h entities of E2.

Specialization & generalization is denoted in ERD as below:

ISA

Aggregation takes following form:

High level ES

8 REGISTRATION FORM

E1 E2R

High level ES

Low level ES Low level ES

E1 E2 R

R

E3

E1 E2R


ALGORITHM: ---

INPUT/OUTPUT:

After writing & studying all ER/EER diagram notations, draw the ER/EER diagram on paper with all the constraints.

OPERATIONAL STEPS REQUIRED: ---

APPLICATION:

1. We can thus draw ER/EER diagram for any database before creating database tables.

CONCLUSION:

1. We have learnt that by drawing ER/EER diagram we get the overview of the database of its entities & their relationships among each other.

2. We have also learnt about the different mapping cardinalities, Specialization, Generalization, Aggregation & Constraints as Primary key, Partial Key, Super Key, Foreign Key. Candidate Key, Disjoint & Overlapping Constraint.

FAQ’S:

1. What is ER/EER diagram, what is its use?2. What is the difference between Strong & Weak entity set.3. What is a Partial Key?4. What is a Primary, Foreign, Super & Candidate Key?5. What is Aggregation with eg?6. What is the difference between Specialization & Generalization?7. What are total & partial Disjoint & overlapping constraints?8. What is the representation of Identifying Relationship?9. What are Mapping Cardinalities?10. What are different types of attributes with eg.?

8 REGISTRATION FORM


EXPERIMENT NO. 6

TITLE: DATABASE IN MS-ACCESS

AIM: To create database tables & Indices in MS-Access for the same problem statement of the ER diagram.

PRE REQUISITE: Assignment no.5

THEORY:

Relation:A relation is a table with columns &rows.

Attribute:An attribute is a named column of a relation.

Domain:A domain is the set of allowable values for one or more attributes.

Tuple :A tuple is a row of relation.

Relation Schema :It is named relation defined by a set of the attributes & domain name pairs.

Let A1, A2,…, An be attributes with domains D1, D2, ………, Dn . Then the set{ A1: D1, A2:D2,……An : Dn} is a relation schema.

Index :An index is an ordered list of the contents of a column or group of columns of

a table.

ALGORITHM: ---

INPUT/OUTPUT:

INPUT:1. By using the previous ER/EER diagram of the database we can implement

the database tables & their Relationships in MS-Access.

8 REGISTRATION FORM


OUTPUT: 1. We get the output in MS-Access as Entities in class diagram notation & relations between them represented by arrows. Also, we get the mapping cardinalities on the relationship arrows.


1. Open the Microsoft Access application.2. Open the new file & save it as a database with .mdb extension.3. Create a new database in design wizard by selecting some of the attributes

with its properties & data types.4. Then after creating & entering data into the database tables set the

relationships with the relationship icon in toolbar.5. Drag any one of the attribute of one entity to another attribute if the other

entity, so that we can have a mapping cardinality among those entities.6. Mapping Cardinalities totally depend on dragging of which attributes to

where.

APPLICATION:

1. To represent any database system in MS-Access.2. To get the Schema Diagram of the database with relationships in MS-Access.

CONCLUSION:

After implementing the assignment, we can easily draw the database of ER diagram in MS-Access.

FAQ’S:

1. What is the need of drawing the database system in MS-Access?2. What are the different ways to create database tables in MS-Access?3. What is the procedure to set mapping cardinalities between entities?4. How we can set relationships among the entities?

8 REGISTRATION FORM


EXPERIMENT NO. 7

TITLE: NORMALIZATION

AIM: Write about different Normal Forms & Explain its implementation in the previous database.

PRE REQUISITE: Unit 3

THEORY: NORMALIZATION

1) Tabular representation of strong entity set:Let E be a strong entity set with attributes A1, A2,…, An. Map this strong entity set to a table E with n columns each corresponding to n attributes.

2) Tabular representation of weak entity set:Let E1 be a weak entity set with attributes A1, A2, …, An. Let E2 be a strong entity set on which E1 depends. Let PK of E2 be an attribute Ai. Represent E1 by a table called E1 with n columns each corresponding to n attributes of E1, and one column for PK Ai of E2.

3) Tabular representation of relationship set R:Let E1,…,En be n entity sets that participate in R. Let A1,A2,…,Am be the set of attributes formed by union of PK’s of each of the entity sets. Let descriptive attributes of R be B1,B2,…,Bn.Relationship set R is represented by a table R with 1 column for each attribute of the set: { A1,A2,…,Am}U{B1,B2,…,Bn} [U- union]

4) Tabular representation of relationship set linking a weak & a strong ES:Such a table is not needed to include in design, since it will contain redundant information, which is already taken care by a table for a weak ES.

5) Composite attributes: instead of creating a column for Composite attribute, create separate columns for each component attribute.

6) Multivalued attribute M: Create a table T with column C that corresponds to M & columns for PK of ES or RS of which M is an attribute. Each value of M is represented as unique row in table.

7) Tabular representation of generalization:

8 REGISTRATION FORM


Create tables for high-level ES & each low level ES. Table for LLES includes all it’s attributes plus PK of HLES.

Alternative: If generalization is disjoint & complete, then do not create table for high-level ES. Create tables for each low level ES with all it’s attributes plus all attributes of HLES.

Thus each entity set & relationship set in ERD is been mapped to a table with the same name. This will give us a relational schema for the system.

Normalization:Normalization of data is a process of analyzing the given relation schemas based

on their functional dependencies (FDs)& primary keys to achieve the desirable properties of 1) minimizing redundancy & 2) minimizing the insertion, deletion, and update anomalies.

Functional Dependencies:Functional Dependency describes the relationship between attributes in a relation.

If A and B are attributes of relation R then B is functionally dependent on A(denoted by AB), if each value of A is associated with exactly one value of B.

Dependency Diagram:

It is a diagrammatic notation to display functional dependencies. Each FD is displayed as a horizontal line. Vertical lines to the line representing the FD connect the LHS attributes of the FD, while arrows pointing toward the attributes connect the RHS attributes.Ex. FD {A1 A2, A3, A4, A5} is denoted as:

A1 A2 A3 A4 A5

Determinant It refers to the attribute or group of attributes on the left hand side of the

functional dependency.

Types of functional dependency

1. Full Functional Dependency An attribute B of a relation R is full functional dependent on attribute A of R if it

is functionally dependent on A and not on any proper subset of A.2. Partial Functional Dependency

An attribute B of a relation R is partially functional dependent on attribute A of R if it is dependent on any proper subset of A.3.Transitive Functional Dependency

8 REGISTRATION FORM


An attribute B of a relation R is transitively functional dependent on attribute A of R if it is functionally dependent on an attribute C which in turn is functionally dependent on A or any proper subset of A.

4 Trivial Dependency

A dependency is trivial if it is impossible for it not to be satisfied.Or A dependency is trivial if and only if, the right hand side of the dependency is the subset of the left hand side.

Normalization involves a series of rules that can be used to test individual relations so that a database can be normalized to any normal form.

Types of normal forms are: 1NF, 2NF, 3NF, BCNF , 4NF,5NF

A relation is said to be in 1NF if all of its attributes are : Single Valued Restricted to assuming atomic value Functionally dependent on simple primary key.

1NF implies Composite attributes are represented only by their component attributes. Attributes can not have multiple values. Attributes can not have complete tuple as a value.

A relation is said to be in 2NF if: It is in 1NF Every non primary key attribute is fully functionally dependent on primary key.

2NF implies Removal of partial Dependencies

But a table in 2NF may contain transitive dependencies i.e. dependency of one non-prime attribute on another non-prime attribute.

A relation is said to be in 3NF if: It is in 2NF No non primary key attribute is transitively dependent on the primary key.

3NF implies Removal of Transitive Dependencies

8 REGISTRATION FORM


A relation is said to be in BCNF if Eery determinant in it is a candidate key.

BCNF implies Removal of Transitive Dependencies for all candidate keys.

A relation is said to be in 4NF if: It is in BCNF It contains no non trivial multi valued dependencies.

4NF implies Removal of trivial multi valued Dependencies

A relation is said to be in 5NF if: It is in 4NF It contains loss-less join dependencies.

Summary of Normal Forms

Normal Forms

Test Remedy (Normalization)

First (1NF) Relation should have no non atomic values

Form new relations for each non atomic attribute or make sure each row defines a single entity

Second (2NF) Relation should have no partial dependency

Decompose & set up a new relation for each partial key with it's dependent attributes. Also keep a relation with the original PK & any attributes that are fully functionally dependent on it.

Third (3NF) Relation should have no transitive dependency

Decompose & set up a relation that includes the transitive dependent attribute(s)with their determinant .

BCNF Relation should have no transitive dependency on any candidate key.

Decompose & set up a relation that includes the transitive dependent attribute(s)with their determinant for each candidate key.

4NF Relation should have no trivial multi valued dependency on any candidate key.

Decompose & set up a relation that includes the trivial multi valued dependent attribute(s)with their determinant for each candidate key.

5NF Relation should have loss-less join dependency.

Decompose the relations in such a way that ensures that no spurious tuples are generated when relations are re united through natural join.

8 REGISTRATION FORM


ALGORITHM: ---

INPUT/OUTPUT:

INPUT:To write about different normal forms like: 1NF, 2NF, 3NF, BCNF, 4NF, 5NF (PJNF), DKNF.OUTPUT:Apply all the normal forms to the same database system in previous assignment.


APPLICATION:1. We can apply all the normal forms to our database system to avoid data

redundancy.

CONCLUSION:By applying all the normal forms to the database system as 1NF, 2NF, 3NF, BCNF, 4NF, 5NF (PJNF), DKNF, we can reduce the data redundancy to some extent by decomposing the original table into number of small tables according to normal forms.

FAQ’S:

1. What is 1NF, with eg?2. What is 2NF, with eg?3. What is 3NF, with eg?4. What is BCNF, with eg?5. What is 4NF, with eg?6. What is 5NF, with eg?7. What is DKNF, with eg?8. What is normalization? What is its use?9. What is PJNF?

8 REGISTRATION FORM


EXPERIMENT NO. 8

TITLE: DDL & DML QUERIES

AIM: To create database tables in Oracle using DDL & DML Commands as Insert, Delete, Update and Modify.

PRE REQUISITE: Unit 2

THEORY:

SQL is a structured query language. It is a standard relational database language, which has 2 parts:

1. DDL – Data Definition Language2. DML – Data Manipulation Language

SQL DDL includes commands to perform following operations: Defining, modifying, deleting relation schemas Specifying integrity constraints Defining views, index Specifying access rights to relations & views etc.

1) Defining relation schema:

We define a SQL relation by using the create table command:

Syntax: create table r (A1 D1, A2 D2, …, An Dn,<integrity constraint 1>,…..,<integrity constraint k>)

where r is name of the relation, each Ai is the name of an attribute in the schema of relation r, & Di is the domain (data) type of values of attribute Ai.

SQL supports following data types: Boolean, char[length], varchar[length], bit[length], numeric(precision, scale),

decimal(precision, scale), integer, smallint, float, real, date, time, timestamp etc

Integrity constraints include:

Primary key (Aj1,Aj2,…,Ajm): Specifies that attributes Aj1, Aj2,…, Ajm form the PK for the relation.

8 REGISTRATION FORM


PK attribute values are required to be non_null & unique.

Check (P): Specifies a predicate P that must be satisfied by every tuple in the relation.

Referential integrity constraint:Foreign keys can be specified as a part of SQL create table statement by using foreign key clause. Foreign key references the primary key attributes of the referenced table.

Example: Consider 2 relations account, branch with schemas as ,Account_schema = (account_no, branch_name, balance)Branch_schema = (branch_name, city, assets).

Ex: Create account relation as given below:

create table account (account_no char(10), branch_name char(15), balance integer, primary key (account_no), Foreign key (branch name) references Branch, Check (balance >= 100)

….)

To view schema of an existing relation use command:Syntax: describe r.

2) Modifying relation schema:

We use alter table command to add attributes or to change attribute characteristic in an existing relation.

Syntax: Alter table r add A D

Where r is existing relation, A is attribute name to be added, D is its domain.All tuples of r are assigned null value for A.e.g: Alter table employee add (phone integer)

To modify domain type of an attribute use:

Syntax: Alter table r modify A DWhere D is new domain type of attribute A.

We can also drop attributes from a relation,

8 REGISTRATION FORM


Syntax: Alter table r drop A

3) Deleting relation schema:

We use drop table command to remove relation from DB.

Syntax: drop table r

4) Defining views:

We use create view command which includes view name & a query that computes view.

Syntax: create view v as <query expression>e.g.: create view acnt as select account_no, balance from account

View name may appear in any place that a relation name may appear.To drop a view use,

Syntax: Drop view v

5) Defining index: Indices are useful for faster retrieval of tuples from relations & efficient

processing of transactions.

Syntax: create index <index_name> on <relation name> (<attribute-list>)

Attribute-list is the list of attributes of the relations that form the search key for the index.e.g.: create index acnt_index on account (account_no)

To drop an index use,

Syntax: Drop index <index_name>

SQL DML Queries

Theory:SQL DML includes commands to perform following operations:1. DB Modification – It includes inserting, updating, deleting information in the

DB.2. Information extraction from DB – It includes selecting the required information

from the DB.

Consider Account_schema = (account_no, branch_name, balance) &

8 REGISTRATION FORM


Branch_schema = (branch_name, city, assets) for example queries given below:

Database Modification

Insertion – To insert data into a relation, we either specify a tuple to be inserted or write a query whose result is a set of tuples to be inserted.Ex. To insert a fact that there is an account A-72 at the Pune branch & it has a balance of Rs. 5000, we write,

Syntax: Insert into r Values (v1, v2,…) Insert into r Values (A1 v1, A2 v2,…)

where Ai – attribute names, vi – value of Ai

e.g.: Insert into account Values (‘A-72’, ‘Pune’, 5000) OR

e.g.: Insert into account (branch_name, account_no, balance)Values (‘Pune’, ‘A-72’, 5000)

To insert set of tuples into relation, use a subquery (i.e. a select query) whose result is a set of tuples to be inserted,

Syntax: Insert into r Select query expression

Updates – To change a value in a tuple without changing other values in the tuple, the update statement can be used.

Syntax: update r set Aj = expression r – relation name, Aj – attribute to be updated, expression computes to new value of Aj]

e.g. To make annual interest payments, all balances are to be increased by 5%, we write,

Update accountSet balance = balance* 1.05

This statement is applied once to each of the tuples in account relation.

To update selected tuples, which satisfy given condition use,Syntax:

Update r set Ai = expression

where condition

8 REGISTRATION FORM


Deletion – We can delete only whole tuples, we can’t delete values of only particular attributes. To delete all tuples from r write,

Syntax: Delete from r

To delete tuples from r which satisfy given predicate P, we write,

Syntax: Delete from r Where P

Delete command operates on only one relation at a time.

Information extraction from DB

This is done with select statement as,

Syntax:Select A1, A2, …, AnFrom r1, r2, …, rmWhere P[Ai – attribute, ri – relation, P – predicate]

e.g. Select branch_name from branch

e.g. Select * from account where balance > 5000

e.g. Select account_no, city From account, branchWhere account.branch_name = branch.branch_name

ALGORITHM: ---

8 REGISTRATION FORM


INPUT/OUTPUT:

INPUT:1. Write all the commands for all the queries & constraints based on the same

database like CREATE, INSERT, DELETE etc.OUTPUT:

1. When we execute the commands we get the output in Oracle 9i.2. When we write ’desc’ we get only the attributes of the table with the data

types. 3. But when we write “Select * from tablename” , we get the entire table with

inserted data.


1. Open the Oracle Orahome 92 application.2. Go to Applicaion design.3. Open SQL Plus application.4. Enter the Username as “scott” & Password as “tiger”.5. Then the SQL file opens where we can write all the queries & commands to

get the desired output.

APPLICATION:

1. By applying SQL DDL & DML commands & queries we can create, insert, update, retrieve, modify & manipulate the data.

2. Also, we can create Views & indexes to have the overview of frequently used attributes to provide security to the database.

3. Also, we can use the date functions.

CONCLUSION:

We have learnt to handle all the queries in SQL where we can immediately view the output. We can modify the inserted data also. We have learnt about all the constraints which help us to put certain conditions on the queries.

8 REGISTRATION FORM


FAQ’S:

1. What is DDL & DML?2. What is SQL?3. What is the difference between primary key & unique key constraint?4. What is the use of Check & Null Constraint?5. What are the date functions?6. What is the use of Alter Command?7. Why update command is used?8. What is the syntax for describing Primary key & Foreign Key?9. What is the difference between drop & truncate?10. What are Views & Indexes?

8 REGISTRATION FORM


EXPERIMENT NO. 9

TITLE: TEXT MANIPULATION FUNCTIONS.

AIM: Use Text manipulation functions on the same problem statement as Aggregation functions, group by, order by, having clause etc.

PRE REQUISITE: Unit 3 & assignment 8.

THEORY:

String operations, group by clause ,date function, nested queries

SQL specifies strings by enclosing them in single quotes.A common operation on strings i.e. pattern matching is done using operator like.Patterns are described by using 2 special characters:

Percent (%): it matches any substring Underscore ( _ ): it matches any character

e.g.: ‘_ _ _’ matches any string of exactly 3 characters ex. ‘one’, ‘two’ etc ‘_ _ _%’ matches any string of at least 3 characters ex. 'india’, ‘one’ etc ‘%sc%’ matches any string containing ‘sc’ as a substring ex. ‘science’, ‘telescope’ etc

e.g. To retrieve branch names which begin with ‘c’ write, Select branch_name From branch Where branch_name like ‘c%’

Ordering the display of tuples

The order by clause causes the tuples in the result of a query to appear in sorted order.

Syntax: select A1, A2, … From r Order by Ai desc, Aj asc

[desc – descending order, asc – ascending order]

8 REGISTRATION FORM


Set operations

Union/ Intersect/ Except operations – These operations operate on relations, which must be compatible i.e. they must have the same no. of attributes with same domain types.

Syntax:(select query1) Union/ Intersect/Except (select query2)

Here set operations are applied to tuples in the results of multiple select queries.All these operations eliminate duplicate tuples from the result.

Aggregate functions

These functions take a collection of values as input & return a single value. SQL offers 5 built in Aggregate functions:

Average: Avg Minimum: Min Maximum: Max Total: Sum Count: Count

Input to sum & avg must be a collection of numbers, but the other operators can operate on collections of nonnumeric data types, such as strings, as well.

Syntax:select aggregate_function_name (Aj)from riwhere condition

[Aj – attribute on which aggregate function is to be applied]

We can apply aggregate function not only to a single set of tuples, but also to a group of sets of tuples, using group by clause as,

Syntax:select Ai, aggregate function name (Aj)from r1, r2, …where condition

8 REGISTRATION FORM


group by Ai

To apply conditions to each group, use having clause as,

Syntax:select Ai, aggregate function name (Aj)from r1, r2, …where conditiongroup by Aihaving condition

SQL applies conditions in the having clause after groups have been formed, so aggregate functions may be used in the having clause.

Nested Sub queries

A subquery is a select-from-where expression that is nested within another query. It has following uses:

Set membership

SQL allows testing tuples for membership in a relation. The in connective tests for set membership, where the set is a collection of values produced by a select clause.The not in connective tests for the absence of set membership.

Syntax:Select AiFrom riWhere Ai in (select Ai from rj)

Set Comparison:

Nested sub query can compare sets using keywords some/any, all & comparison operators.

Syntax:Select AiFrom riWhere Aj > some/any/all (select Aj from ri where condition)

8 REGISTRATION FORM


ALGORITHM: ---

INPUT/OUTPUT:

INPUT:2. Write all the commands for all the queries & constraints based on the same

database like aggregate functions, group by, order by & having clause, date functions, like & between operators.

OUTPUT:4. When we execute the commands we get the output in Oracle 9i.5. When we write ’desc’ we get only the attributes of the table with the data

types. 6. But when we write “Select * from tablename”, we get the entire table with

inserted data.




APPLICATION:

We can use all above mentioned functions to manipulate text data, to have aggregate functions, to apply various clauses etc.

CONCLUSION:

1. We have learnt all aggregate functions as MIN, MAX, SUM, Average, and Count.

2. We have learnt all date functions to get the certain date, month difference, getting dates in characters etc.

3. We have also learnt group by, order by & having clause, like & between operators.

8 REGISTRATION FORM


FAQ’S:

1. What are aggregate functions?2. What are date functions, their use?3. What is pattern matching technique?4. What are the different ways in Like operator?5. What is the difference between group by, order by & having clause?6. What is the difference between Like & between operator?

8 REGISTRATION FORM


EXPERIMENT NO. 10

TITLE: TRIGGERS, FUNCTIONS & PROCEDURES.

AIM: Write & execute Triggers, Procedures & Functions using PL/SQL block on the same database.

PRE REQUISITE: Unit 3, assignment 8 & 9.

THEORY:

Stored Procedures, Functions & Triggers

Stored Procedures:Procedures are named pl/sql blocks that can take parameters, perform an action

and can be invoked. A procedure is generally used to perform an action and to pass values.

Procedure are made up of:1. A declarative part,2. An executable part, and 3. An optional exception-handling part.

Declarative Part:The declarative part may contain declarations of cursors, constants, variables,

exceptions and subprograms. These objects are local to the procedure. The objects become invalid once you exit from it.

Executable part:The executable part contains a pl/sql block consisting of statements that assign

values, control execution and manipulate oracle data. The action to be performed is coded here and data that is to be returned back to the calling environment is also returned from here. Variables declared are put to use in this block.

Exception handling part:This part contains code that performs an action to deal with exceptions raised

during the execution of the Executable part. This block can be used to handle oracle’s own exceptions or the exceptions that are declared in the Declarative part. One cannot

8 REGISTRATION FORM


transfer the flow of execution from the Exception Handling part to the Executable part or vice versa.

Procedures in oracle :Procedures in oracle are called stored procedures. Procedures are stored in the

database and are invoked or called by any anonymous block (the pl/sql block that appears within an application). Before the procedure is created, oracle parses the procedure. Then this parsed procedure is stored in the database.

When a procedure is created, oracle automatically performs the following steps:1. Compiles the procedure.2. Stores the compiled code.3. Stores the procedure in the database.

The pl/sql compiler compiles the code. If an error occurs, then the procedure is created but it is an invalid procedure. Oracle displays a message during the time of creation that the procedure was created with compilation errors.It does not display the errors. These errors can be viewed using the select statement.

Select * from user_errors;

Oracle loads the compiled procedure in the memory area called the System Global Area (SGA). This allows the code to be executed quickly. The other users execute the same procedure residing in the SGA also.

Steps to execute a procedure :Oracle performs the following steps to execute a procedure.

1.Verifies user access.2.Verifies procedure validity.3.Executes the procedure.

Oracle check if the user who called the procedure has the execute privilege for the procedure. If the user is invalid, then access is denied otherwise oracle proceeds to check whether the called procedure is valid or not. The user can view the validity of the procedure by using the select statement as:

Select object_name, object_type, status from user_objects where object_type = ‘PROCEDURE’;

Only if the status is valid, then the procedure can be executed. Once the procedure is found valid, oracle then loads the procedure into memory (i.e. if it is not currently present in memory) and executes the pl/sql code.

Syntax for creating stored procedure :

8 REGISTRATION FORM


Create or replace procedure [schema.] procedurename (Argument {IN,OUT,IN OUT} data type,…){IS,AS} Variable declarations; Constant declarations;

BEGIN Pl/sql subprogram body;

EXCEPTION Exception pl/sql block;

END;

Keywords and Parameters:The keywords and the parameters used for creating database procedures are

explained below.

REPLACE: Recreates the procedure if it is already exists. You can use this option to change the definition of an existing procedure without dropping, recreating and regranting object privileges previously granted on it. If you redefine a procedure oracle recompiles it.

Schema: It is the schema to contain the procedure. Oracle takes the default schema to be the current schema, if it is omitted.

Procedure: It is the name of the procedure to be created.

Argument: is the name of an argument to the procedure. Parentheses can be omitted if not arguments are present.

IN : specifies that you must specify a value for the argument when calling the procedure.

OUT: specifies that the procedure passes a value for this argument back to its calling environment after execution.

IN OUT: specifies that you must specify a value for the argument when calling the procedure and that the procedure passes a value for this argument back to its calling environment after execution. By default it takes IN.

Datatype: is the datatype of an argument. It supports any datatype supported by pl/sql.

Pl/sql sub program body is the definition of procedure consisting of pl/sql statements.

Deleting a stored procedure:Syntax:

Drop procedure (procedurename);

8 REGISTRATION FORM


FUNCTIONS:

Functions are named pl/sql blocks that can take parameters, perform an action and returns a value to the host environment. A function can only return one value.

Functions are made up of:1.A declarative part,2.An executable part,3.And an optional exception-handling part.

When a function is created, oracle automatically performs the following steps:

1.Compiles the function.2.Stores the compiled code.3.Stores the function in the database.

The pl/sql compiler compiles the code. If an error occurs, then the function is created but it is an invalid function. Oracle displays a message during the time of creation that the function was created with compilation errors.It does not display the errors. These errors can be viewed using the select statement.

Select * from user_errors;

Oracle loads the compiled function in the memory area called the System Global Area (SGA). This allows the code to be executed quickly. The other users execute the same function residing in the SGA also.

Executing function:Oracle performs the following steps to execute a function.

1.Verifies user access.2.Verifies function validity.3.Executes the function.

Oracle check if the user who called the function has the execute privilege for the function. If the user is invalid, then access is denied otherwise oracle proceeds to check whether the called function is valid or not. The user can view the validity of the function by using the select statement as:

Select object_name, object_type, status from user_objects where object_type = ‘FUNCTION’;

Syntax for creating a stored function:

8 REGISTRATION FORM


Create or replace function [schema.] functionname(argument IN datatype,…)RETURN datatype {IS, AS}

Variable declarations;Constant declaration;

BEGINPl/sql subprogram body;

EXCEPTIONException pl/sql block;

END;

Keywords and Parameters:The keywords and the parameters used for creating database functions are

explained below.

REPLACE: recreated the function if it is already exists. You can use this option to change the definition of an existing function without dropping, recreating and regranting object privileges previously granted on it. If you redefine a function oracle recompiles it.

Schema: is the schema to contain the function. Oracle takes the default schema to be the current schema, if it is omitted.

Function: is the name of the function to be created.

Argument: is the name of an argument to the function. Parentheses can be omitted if not arguments are present.

IN : specifies that you must specify a value for the argument when calling the function.

Return Datatype: is the datatype of the function’s return value. Because every function must return a value, this clause is required. It supports any datatype supported by pl/sql.

Pl/sql sub program body: is the definition of function consisting of pl/sql statements.

Deleting a stored function Drop function (functionname);

Database Triggers:

8 REGISTRATION FORM


Database triggers are procedures that are stored in the database and are implicitly executed (fired) when the contents of a table are changed.Use of database triggers:

Database triggers support oracle to provide a highly customized database management system. Some of the uses to which the database triggers can be put to customize management information in oracle are as follows:

A trigger can permit DML statements against a table only if they are issued during regular business hours or on predetermined weekdays.

A trigger can also be used to keep an audit trail of a table(i.e. to store the modified and deleted records of the table) along with the operation performed and the time on which the operation was performed.

It can be used to prevent invalid transactions. Enforce complex security authorizations.

Note: When a trigger is fired, a SQL statement inside the trigger can also fire the same

or some other trigger (if exists), called as cascading, which must be considered. Excessive use of triggers for customizing the database can result in complex

interdependencies between the triggers, which may be difficult to maintain in a large application.

How to apply database triggers ?

A trigger has three basics parts:1. A triggering event or statement2. A trigger restriction 3. A trigger action

1. Triggering Event or Statement:It is a SQL statement that causes a trigger to be fired. It can be INSERT,

UPDATE or DELETE statement for a specific table. A triggering statement can also specify multiple DML statements.

2. Trigger Restriction:A trigger restriction specifies a Boolean (logical) expression that must be

TRUE for the trigger to fire. It is an option available for triggers that are fired for each row. Its function is to conditionally control the execution of a trigger. A trigger restriction is specified using a WHEN clause.

3. Trigger Action: A trigger action is the procedure (pl/sql block) that contains the SQL

statements and PL/SQL code to be executed when a triggering statement is issued and the trigger restriction evaluates to TRUE. It can contain SQL and PL/SQL statements, can define PL/SQL language constructs and can call stored

8 REGISTRATION FORM


procedures. Additionally, for row triggers, the statements in a trigger action have access to column values (new and old) of the current row being processed.

Types of Triggers:

1.Row Triggers:A row trigger is fired each time the table is affected by the triggering

statement. For eg. If an update statement updates multiple rows of a table, a row trigger is fired once for each row affected by the updated statement. If the triggering statement affects no rows, the trigger is not executed at all. Row trigger should be used when the trigger action code depends on the data provided by the triggering statement or rows that are affected.

2.Statement Triggers:A row trigger is fired once on behalf of the triggering statement,

independent of the number of rows the triggering statement affects (even if no rows are affected). Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.

Before VS. After Triggers:When defining a trigger you can specify the trigger timing, i.e. you can

specify when the triggering action is to be executed in relation to the triggering statement. BEFORE and AFTER apply to both row and the statement triggers.

3.Before Triggers:Before triggers execute the trigger action before the triggering statement.

These types of triggers are commonly used in the following situation: Before triggers are used when the trigger action should determine whether or not

the triggering statement should be allowed to complete. By using a before trigger, you can eliminate unnecessary processing of the triggering statement.

Before triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

4.After Triggers:After triggers execute the trigger action after the triggering statement is executed. These types of triggers are commonly used in the following situation.

After triggers are used when you want the triggering statement to complete before executing the trigger action.

If a before trigger is already present, an after trigger can perform different actions on the same triggering statement.

Combinations:

8 REGISTRATION FORM


Using the options explained above, four types of triggers can be created.

1. Before statement trigger:Before executing the triggering statement, the trigger action is executed.

2. Before row trigger:Before modifying each row affected by the triggering statement and before

appropriate integrity constraints, the trigger is executed if the trigger restriction either evaluated to TRUE or was not included.

3. After statement Trigger:After executing the triggering statement and applying any deferred

integrity constraints, the trigger action is executed.

4. After row trigger:After modifying each row affected by the triggering statement and

possibly applying appropriate integrity constraints, the trigger action is executed for the current row if the trigger restriction either evaluated to TRUE or was not included. Unlike before row triggers, after row triggers have rows locked.

Syntax for creating trigger

Create or replace trigger[schema.] triggername{before, after}{delete,insert,update[of column,….]}on [schema.] tablename[referencing { old as old, new as new}][for each row[when condition]]

DECLAREVariable declarations;Constant declarations;

BEGINPl/sql subprogram body;

EXCEPTIONException pl/sql block;

END;

Keywords and ParametersThe keywords and the parameters used for creating database triggers are

explained below: OR REPLACE: recreated the trigger if it already exists. You can use this option to change the definition of an existing trigger without first dropping it.

8 REGISTRATION FORM


Schema: is the schema to contain the trigger. If you omit the schema, oracle creates the trigger in your own schema.

Triggername: is the name of the trigger to be created.

BEFORE: indicates that oracle fires the trigger before executing the triggering statement.

AFTER: indicates that the oracle fires the trigger after executing the triggering statement.

DELETE: indicates that oracle fires the trigger whenever a delete statement removes a row from the table.

INSERT: indicates that oracle fires the trigger whenever an insert statement adds a row to table.

UPDATE: indicates that oracle fires the trigger whenever an update statement changes a value in one of the columns specified in the OF clause. If you omit the OF clause, oracle fires the trigger whenever an UPDATE statement changes a value in any column of the table.

ON: specifies the schema and name of the table on which the trigger is to be created. If you omit schema, oracle assumes the table is in your own schema. You cannot create a trigger on a table in the schema SYS.

REFERENCING: specifies correlation names. You can use correlation names in the pl/sql block and when clause of a row trigger to refer specifically to old and new values of the current row. The default correlation names are old and new. If your row trigger is associated with a table named old or new, you can use this clause to specify different correlation names to avoid confusion between table name and the correlation name.

FOR EACH ROW: designates the trigger to be a row trigger. Oracle fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in the when clause. If you omit this clause, the trigger is a statement trigger.

WHEN : specifies the trigger restriction. The trigger restriction contains a sql condition that must be satisfied for oracle to fire the trigger. This condition must contain correlation names and cannot contain a query. You can specify trigger restriction only for the row triggers. Oracle evaluates this condition for each row affected by the triggering statement.

Pl/sql block: is the pl/sql block that oracle executes to fire the trigger.

Deleting a trigger:

8 REGISTRATION FORM


Drop trigger trigger name;

ALGORITHM: ---

INPUT/OUTPUT:

INPUT:1. Write all the triggers, procedures & functions based on the same

database so that we can modify the inserted data in original tables by using PL/SQL block (Embedded SQL).

OUTPUT: 1. When we execute the commands we get the output in Oracle 9i.2. We write “SET SERVEROUTPUT ON” before executing triggers,

procedures & functions.




APPLICATION:

1. We can apply triggers, procedures & functions to our database system to manipulate & update the existing data using PL/SQL concepts like If Else, nested If-Else etc.

2. We can also use this concept in our final project based on Front end & Back end.

CONCLUSION:

1. We have learnt all the triggers, procedures & functions to manipulate & update the existing data using PL/SQL concepts like If Else, nested If-Else etc.

8 REGISTRATION FORM


FAQ’S:

1. What is a trigger?2. What is a procedure?3. What is a function?4. What is the difference between procedure &function?5. What is the use of these parameters?

8 REGISTRATION FORM


EXPERIMENT NO. 11

TITLE: MINI PROJECT ON FRONT END & BACK END

AIM: To develop a Mini-Project in both front end 7 back end.

PRE REQUISITE: All previous 10 assignments.

THEORY:

1. ER/EER diagram of the database selected for the Mini-Project. (Diagram)2. Create all the tables in Oracle 9i. (Data Grid In front end)3. Create the Front end i.e, Design View. (Print out)4. Handle all the transactions with the database from front end. (Print out Of

each Transaction)5. Code (Print out)

ALGORITHM: ---

INPUT/OUTPUT: ---


APPLICATION:To create different types of database oriented projects.

8 REGISTRATION FORM


CONCLUSION:

After implementing the project we have learnt to use all the features of VB & Oracle 9i with some Multimedia effects.

FAQ’S:

1. Explain the role of triggers in the project?2. Explain the primary key concept in the project?3. What is the use of Data Grids?4. Why Data Reports are needed in the project?5. Where validity Checks are included?6. What is the procedure of database connectivity?7. What is DSN?8. What is ODBC?9. How Database Connectivity is done in MS-Access?10. Which other modifications can be involved in the project?

8 REGISTRATION FORM

isl lab manual for teit uop

Documents