MISRIMAL NAVAJEE MUNOTH JAIN

ENGINEERING COLLEGE
OWNED AND MANAGED BY TAMILNADU EDUCATIONAL AND MEDICAL TRUST
A Jain Minority Institution
Approved by AICTE &Programmes Accredited by NBA, New Delhi, (UG Programmes – MECH, EEE, ECE, CSE & IT) All
Programmes Recognized by the Government of Tamil Nadu and Affiliated to Anna University, Chennai
Guru MarudharKesari Building, Jyothi Nagar, Rajiv Gandhi Salai, OMR Thoraipakkam, Chennai - 600 097.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

AD3381 DATABASE DESIGN AND

MANAGEMENT LABORATORY

NAME :

REGISTER NO :

YEAR/SEMESTER : II / III

DEPARTMENT :
 MISRIMAL NAVAJEE MUNOTH JAIN
ENGINEERING COLLEGE
OWNED AND MANAGED BY TAMILNADU EDUCATIONAL AND MEDICALTRUST
A Jain Minority Institution
Approved by AICTE &Programmes Accredited by NBA, New Delhi, (UG Programmes – MECH, EEE, ECE, CSE & IT) All
Programmes Recognized by the Government of Tamil Nadu and Affiliated to Anna University, Chennai Guru
MarudharKesari Building, Jyothi Nagar, Rajiv Gandhi Salai, OMR Thoraipakkam, Chennai - 600 097.

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

VISION
To produce high quality, creative and ethical engineers and

technologists contributing effectively to the ever-advancing Artificial

Intelligence and Data Science field.

MISSION
To educate future software engineers with strong fundamentals
by continuously improving the teaching-learning methodologies using
contemporary aids.
To produce ethical engineers/researchers by instilling the values of

humility, humaneness, honesty and courage to serve the society.

To create a knowledge hub of Artificial Intelligence and Data

Science with everlasting urge to learn by developing, maintaining and

continuously improving the resources/Data Science.

 MISRIMAL NAVAJEE MUNOTH JAIN ENGINEERING COLLEGE
OWNED AND MANAGED BY TAMILNADU EDUCATIONAL AND MEDICAL TRUST
A Jain Minority Institution
Approved by AICTE &Programmes Accredited by NBA, New Delhi, (UG Programmes – MECH, EEE, ECE, CSE & IT) All Programmes Recognized by the
Government of Tamil Nadu and Affiliated to Anna University, Chennai Guru MarudharKesari Building, Jyothi Nagar, Rajiv Gandhi Salai, OMR Thoraipakkam,
Chennai - 600 097.

Register No:

BONAFIDE CERTIFICATE

This is to certify that this is a bonafide record of the work done

byMr./Ms.___________________________________________of ____________ year

B.Tech- ____________________________________________________ in

AD3381 DATABASE DESIGN AND MANAGEMENT LABORATORY Laboratory during

the Academic year 2022 – 2023.

Faculty-in-charge Head of the Department

Internal Examiner External Examiner

 MISRIMAL NAVAJEE MUNOTH JAIN ENGINEERING
COLLEGE
OWNED AND MANAGED BY TAMILNADU EDUCATIONAL AND MEDICAL TRUST
A Jain Minority Institution
Approved by AICTE &Programmes Accredited by NBA, New Delhi, (UG Programmes – MECH, EEE, ECE, CSE & IT)
All Programmes Recognized by the Government of Tamil Nadu and Affiliated to Anna University, Chennai Guru
MarudharKesari Building, Jyothi Nagar, Rajiv Gandhi Salai, OMR Thoraipakkam, Chennai - 600 097.

AD3381 DATABASE DESIGN AND MANAGEMENT

LABORATORY

COURSE OUTCOMES

CO1 Understand the database development life cycle

CO 2 D
e
s
i
g
nr
e
l
at
i
o
n
al
d
at
a
ba
s
e
u
si
n
g
co
n
ce
p
t
u
al
-
t
o-
r
e
la
t
i
o
na
l
m
ap
p
in
g
,
No
rm
a
l
i
z
at
i
o
n

CO 3 Apply SQL for creation, manipulation and retrieval of data

CO 4 Develop a database applications for real-time problems

CO 5 Design and query object-relational databases

 AD3381 DATABASE DESIGN AND MANAGEMENT LABORTARY
CONTENT

E
x
.
No
. P
a
g
eDateName of the Experiment Signature
N
o
.
1
(
a
)Da
t
a
b
as
e
D
ev
e
l
o
pm
e
nt
L
i
f
ec
y
cl
e
:
Pr
o
bl
e
m
definition and Requirement analysis

1
(
b
)Scope and Constraints

2
(
a
)Da
t
a
b
as
e
d
es
i
g
n
us
i
ng
C
o
n
ce
p
t
u
al
m
od
e
li
n
g
(E
R
EER) top down approach
2
(
b
)Ma
p
p
i
n
gc
o
n
ce
p
t
u
al
t
or
e
l
at
i
o
n
al
d
at
a
ba
s
e
a
nd
validate using Normalization
3Implement the database using SQL Data
definition with constraints, Views
4Q
u
er
y
t
he
d
a
ta
b
a
s
eu
s
i
n
gS
Q
LM
a
n
i
p
u
la
t
i
on
5Q
u
er
y
i
n
g/
M
an
a
g
i
n
gt
h
ed
a
t
a
ba
s
e
u
si
n
g
SQ
L
Programming
Stored Procedures/Functions
6Constraints and security using Triggers
7D
a
ta
b
a
s
ed
e
s
i
g
nu
s
i
ng
N
o
r
ma
l
i
z
at
i
o
n-
b
o
tt
o
m
up approach
8D
a
ta
b
a
s
ed
e
s
i
g
nu
s
i
ng
E
E
Rt
o
O
DB
ma
p
p
i
n
g/
UML class diagrams
9O
b
je
c
t
f
ea
t
u
r
es
o
f
SQ
L
UD
T
s
a
n
ds
u
b
ty
p
e
s
,
Tables using UDTs, Inheritance, Method
definition
1
0
. Q
u
e
r
y
i
ng
t
h
eO
b
j
e
ct
r
el
a
t
io
n
al
d
at
a
ba
s
e
u
si
n
g
Objet Query language
syllabus
AD3381 DATABASE DESIGN AND MANAGEMENT LABORATORY

COURSE OBJECTIVE

To understand the database development life cycle

To learn database design using conceptual modelling, Normalization To

implement database using Data definition, Querying using SQL

manipulation and SQL

programming
To implement database applications using IDE/RAD tools

To learn querying Object-relational databases

LIST OF EXPERIMENTS:
1. Database Development Life cycle: Problem definition and Requirement analysis
Scope and Constraints
2. Database design using Conceptual modeling (ER-EER) – top-down approach
Mapping conceptual to relational database and validate using Normalization
3. Implement the database using SQL Data definition with constraints, Views
4. Query the database using SQL Manipulation
5. Querying/Managing the database using SQL Programming - Stored

Procedures/Functions - Constraints and security using Triggers

6. Database design using Normalization – bottom-up approach

7. Develop database applications using IDE/RAD tools (Eg., NetBeans,VisualStudio)

8. Database design using EER-to-ODB mapping / UML class diagrams

9. Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance,
Method definition
10. Querying the Object-relational database using Objet Query language
 Ex.No: 1(a) Database Development Life cycle: Problem definition

and Requirement analysis Scope and Constraints

Date :

AIM:

To describe the mission statement, mission objectives and to develop the standards for library
database system.

PROCEDURE:

STEP 1: Write the Mission Statement for library database system.

2: Write the Mission Objectives for the database system.

STEP 3: Develop the standards for data collection, data formats, and for naming the data items.

DESCRIPTION:

DATABASE DEVELOPMENT LIFE CYCLE

As a database system is a fundamental component of the larger organization-wide information system, the
database system development lifecycle is inherently associated with the lifecycle of the information system. The
stages of the database system development lifecycle are shown in the below figure.

Database Planning:

The management activities that allow the stages of the database system development lifecycle to be realized
as efficiently and effectively as possible.
Database planning must be integrated with the overall IS strategy of the organization. There are three main
issues involved in formulating an IS strategy, which are:

Identification of enterprise plans and goals with subsequent determination of information system
needs
Evaluation of current information systems to determine existing strengths and

weaknesses Appraisal of IT opportunities that might yield competitive advantage.

An important first step in database planning is to clearly define the mission statement for the database system. The
mission statement defines the major aims of the database system. Those driving the database project within the
organization (such as the Director and/or owner) normally define the mission statement.

A mission statement helps to clarify the purpose of the database system and provide a clearer path towards
the efficient and effective creation of the required database system.
Once the mission statement is defined, the next activity involves identifying the mission objectives. Each mission
objective should identify a particular task that the database system must support. The assumption is that if the
database system supports the mission objectives, then the mission statement should be met.

Database planning should also include the development of standards that govern how data will be collected,
how the format should be specified, what documentation will be needed, and how design and implementation
should proceed.

System Definition:

System Definition describes the scope and boundaries of the database system and the major user views.

User Views: Defines what is required of a database system from the perspective of a particular job role (such as Manager or
Supervisor) or enterprise application area (such as marketing, personnel, or stock control).

Requirements collection and Analysis: The process of collecting and analyzing information about the part
of the organization that is to be supported by the database system, and using this information to identify
the requirements for the new system. Information is gathered for each major user view (that is, job role or
enterprise application area), including:

A description of the data used or generated;

The details of how data is to be used or generated;

Any additional requirements for the new database system.

This information is then analyzed to identify the requirements (or features) to be included in the new
database system. These requirements are described in documents collectively referred to as
requirements specifications for the new database system.
Another important activity associated with this stage is deciding how to deal with the situation in which
there is more than one user view for the database system. There are three main approaches to managing
the requirements of a database system with multiple user views:

The centralized approach;

The view integration approach;

A combination of both approaches.

Centralized Approach: Requirements for each user view are merged into a single set of requirements for the new database
system. A data model representing all user views is created during the database design stage.

View Integration Approach: Requirements for each user view remain as separate lists. Data models representing
each user view are created and then merged later during the database design stage.

Database Design:

The process of creating a design that will support the enterprise’s mission statement and mission objectives for the required
database system.
The two main approaches to the design of a database are:

“bottom-up”

“top-down.”

The bottom-up approach begins at the fundamental level of attributes (that is, properties of entities and relationships),
which through analysis of the associations between attributes are grouped into relations that represent types of entities
and relationships between entities.

A more appropriate strategy for the design of complex databases is to use the top-down approach. This approach starts with
the development of data models that contain a few high-level entities and relationships and then applies successive top-down
refinements to identify lower-level entities, relationships, and the associated attributes.

There are other approaches to database design, such as the inside-out approach and the mixed strategy approach. The
inside-out approach is related to the bottom- up approach, but differs by first identifying a set of major entities and
then spreading out to consider other entities, relationships, and attributes associated with those first identified. The
mixed strategy approach uses both the bottom-up and top-down approach for various parts of the model before finally
combining all parts together.

Sub-Stages in Database Design:

Conceptual Database Design

Logical Database Design

Physical Database Design

Conceptual Database Design: The process of constructing a model of the data used in an enterprise, independent of all
physical considerations.

Logical Database Design: The process of constructing a model of the data used in an enterprise based on a specific data
model, but independent of a particular DBMS and other physical considerations.
Physical Database Design: The process of producing a description of the implementation of the database on secondary
storage; it describes the base relations, file organizations, and indexes used to achieve efficient access to the data, and
any associated integrity constraints and security measures.
DBMS Selection:

The selection of an appropriate DBMS to support the database system.

The steps involved in database selection are:

Define Terms of Reference of study

 Shortlist two or three products

Evaluate products

Recommend selection and produce report

Application Design:

The design of the user interface and the application programs that use and process the database.

Database and application design are parallel activities of the database system development lifecycle. In most cases, it is not
possible to complete the application design until the design of the database itself has taken place.

The steps involved in application design are:

Transaction Design

User interface Design Guidelines

Transaction Design: An action, or series of actions, carried out by a single user or application program, that accesses or
changes the content of the database.

The purpose of transaction design is to define and document the high-level characteristics of the transactions required
on the database, including:

data to be used by the transaction

functional characteristics of the

transactionoutput of the transaction

importance to the users

expected rate of usage

User interface Design Guidelines:

Meaningful title

Comprehensible instructions

Logical grouping and sequencing of fields

Visually appealing layout of the

form/report Familiar field labels

Consistent terminology and abbreviations

Consistent use of color
 Visible space and boundaries for data entry

fields Convenient cursor movement

Error correction for individual characters and entire

fields Error messages for unacceptable values

Optional fields marked clearly

Explanatory messages for fields

Completion signal

Prototyping:

Building a working model of a

database system.

A prototype is a working model that does not normally have all the required features or provide all the functionality of
the final system. The main purpose of developing a prototype database system is to allow users to use the prototype to
identify the features of the system that work well or are inadequate, and if possible to suggest improvements or even
new features to the database system.

There are two prototyping strategies:

requirements prototyping - uses a prototype to determine the requirements of a proposed database system, and
once the requirements are complete, the prototype is discarded
evolutionary prototyping - used for the same purposes, the important difference is that the prototype is not
discarded, but with further development becomes the working database system
Implementation:

The physical realization of the database and application designs.

The database implementation is achieved using the DDL of the selected DBMS or a GUI, which provides the same
functionality while hiding the low-level DDL statements. The application programs are implemented using the preferred
third- or fourth generation language (3GL or 4GL).

Data Conversion and Loading:

Transferring any existing data into the new database and converting any existing applications to run on the new
database. This stage is required only when a new database system is replacing an old system.

Testing:

The process of running the database system with the intent of finding errors.

Before going live, the newly developed database system should be thoroughly tested. This is achieved using carefully planned test
strategies and realistic data, so that the entire testing process is methodically and rigorously carried out.

Operational Maintenance:

The process of monitoring and maintaining the database system following installation.

Monitoring the performance of the system. If the performance falls below an acceptable level, tuning or
reorganization of the database may be required.
 Maintaining and upgrading the database system (when required). New requirements are incorporated into the
database system through the preceding stages of the lifecycle.
CASE Tools:

A computer-aided software engineering (CASE) tool is a software package that provides support for the design and implementation
of information systems. It can document a database design and provide invaluable help in maintaining the consistency of a design.
By integrating many of the techniques used to document a system design including the data dictionary, data flows, and entity
relationships, CASE tool can increase the consistency and accuracy of a database design. It can also ease the task of creating the
diagrams that accompany a system design.

There is no software in the world that can examine a database environment and identify the entities, attributes, and
relationships that should be represented in a database. The model created with CASE tool is therefore only as good as the
analysis of the database environment provided by the people using the tool.

Ex.No: 1(b) SCOPE AND CONSTRAINTS

Date :

AIM:
To define the sub-systems that are within the scope of University Database System.

PROCEDURE:

STEP 1: Identify the boundaries of the system and how it interfaces with other parts of the organization’s information
system

STEP 2: Identify the user views and their dependencies.

STEP 3: Prepare the requirements specifications for the new database system

STEP 4: Design the local data model and global data model

DESCRIPTION:

NOT NULL constraint

create table stud(stuname varchar(20)NOT NULL,age integer); insert into

stud values('',24);insert into stud values('Ram',25);

CHECK constraint

create table stud(stuname varchar(20),age integer CHECK (age>0));

insert into stud values('Ram',-1);

UNIQUE constraint

create table stud(stuname varchar(20)UNIQUE,age integer);

insert into stud values('Ram',24); insert into stud

values('Ram',26);

primary constraint

create table stud(stuname varchar(20)primary key,age integer);

insert into stud values('Ram',24); insert into stud values('Ram',25);

default constraint

create table stud(stuname varchar(20),age integer,country varchar(20)default 'India');

insert into stud values('Ram',24);

insert into stud values('Ram',25);

select * from stud;

OUTPUT
Foreign constraint

create table pat1(pat_id integer primary key,pat_name varchar(20));

insert into pat1 values(1,'Ram');

insert into pat1 values(2,'Arun');

select * from pat1;

create table pat2(pat_accno integer,pat_id integer references pat1(pat_id));

insert into pat2 values(2021,1);

insert into pat2 values(2022,2);

OUTPUT:

RESULT:
Thus, To define the sub-systems that are within the scope of University Database System has executed successfully.
 Ex.no:2(a) DATABASE DESIGN USING CONCEPTUAL

MODELING(ER EER) TOP DOWN APPROACH

date

AIM:
To design the database using ER/EER Diagrams.

PROCEDURE:

STEP 1: Identify the entity types, attributes and the relationship types of the University Database
System.
STEP2: Draw the ER diagram to represent the entity types, attributes and the relationship types
STEP3: Identify the super-classes, sub-classes, aggregation and association.
STEP 4: Draw the EER diagram to represent the super-classes, sub-classes, aggregation and
association.

DESCRIPTION:

ENTITY-RELATIONSHIP MODELING
An entity is a "thing" or "object" in the real world that is distinguishable from all other objects. For example,
each person in an enterprise is an entity. An entity set is a set of entities of the same type that share the
same properties, or attributes. The set of all persons who are customers at a given bank, for example, can
be defined as the entity set customer.

An entity is represented by a set of attributes. Attributes are descriptive properties possessed by

each member of an entity set.
Attribute domain: The set of allowable values (data type) for one or more attributes.

Types of Attributes:
Simple Attribute: An attribute composed of a single component with an independent existence.

Composite Attribute: An attribute composed of multiple components, each with an

independent existence.

Single-Valued Attributes: An attribute that holds a single value for each occurrence of an entity type.

Multi-Valued Attributes: An attribute that holds multiple values for each occurrence of an
entity type.

Derived Attributes: An attribute that represents a value that is derivable from the value of a
related attribute or set of attributes, not necessarily in the same entity type.

A relationship is an association among several entities. Relationship set is a set of relationships of the same
type. The association between entity set is referred to as participation. That is, the entity sets E1, E2, . ..,En
participate in relationship set R.

A uniquely identifiable association that includes one occurrence from each participating entity type. A
relationship occurrence indicates the particular entity occurrences that are related. Relationship type and
Relationship occurrences are one and the same.

Degree of a relationship: The number of participating entity types in a relationship. Binary relationship set
is of degree 2; a tertiary relationship set is of degree 3.

Unary relationship: A unary relationship exists when an association is maintained within a single entity.

Binary relationship: A binary relationship exists when two entities are associated.

Ternary relationship: A ternary relationship exists when there are three entities associated.
Quaternary relationship: A quaternary relationship exists when there are four entities associated.

Entity role: The function that an entity plays in a relationship is called that entity‘s role. A role is one end
of an association. In the below ER model, the publisher entity plays the publishes role.

Recursive Relationship: A relationship type in which the same entity type participates more than once
in different roles.

Keys:
Super Key: Super key is a single attribute or a group of multiple attributes that can uniquely identify
each occurrence of an entity type.
Candidate Key: The minimal set of attributes that uniquely identifies each occurrence of an entity type.
Primary Key: The candidate key that is selected to uniquely identify each occurrence of an entity type.
Composite Key: A candidate key that consists of two or more attributes.
Foreign Key: Foreign key is an attribute which is a Primary key in its parent entity, but is included as an
attribute in another entity. A Foreign key generates a relationship between the parent entity and the
child entity.
Alternate or Secondary Key: Alternate keys are those candidate keys which are not the Primary key.

An entity set may not have sufficient attributes to form a primary key. Such an entity set is termed a
weak entity set. An entity set that has a primary key is termed a strong entity set.
Weak entity set is associated with another entity set called the identifying or owner entity set. i.e.,
weak entity set is said to be existence dependent on the identifying entity set. Identifying entity set is
said to own the weak entity set.
The relationship among the weak and identifying entity set is called the identifying relationship.

STRUCTURAL CONSTRAINTS:
Multiplicity: The number (or range) of possible occurrences of an entity type that may relate to a
single occurrence of an associated entity type through a particular relationship.
one-to-one (1:1)

one-tomany (1:*)

many-to-many (*:*)

One-to-one: An entity in A is associated with at most one entity in B, and an entity in B is associated with
at most one entity in A.

Example: Relationship between Manager and Branch of a Bank.

One-to-many: An entity in A is associated with any number of entities (zero or more) in B. An entity in B,
however, can be associated with at most one entity in A.

Example: Relationship between Department and Employee.

Many-to-many: An entity in A is associated with any number (zero or more) of entities in B, and an entity
in B is associated with any number (zero or more) of entities in A.

Example: Relationship between Supplier and Products.

Cardinality and Participation Constraints: Multiplicity actually consists of two separate constraints
known as cardinality and participation.
Cardinality: Describes the maximum number of possible relationship occurrences for an entity
participating in a given relationship type.

Participation: Determines whether all or only some entity occurrences participate in a relationship The

participation constraint represents whether all entity occurrences are involved in a particular relationship

(referred to as mandatory participation) or only some (referred to as optional participation).

Problems with ER Models:

Fan Trap: Where a model represents a relationship between entity types, but the pathway between
certain entity occurrences is ambiguous.
Chasm Trap: Where a model suggests the existence of a relationship between entity types, but the
pathway does not exist between certain entity occurrences
 MAPPING CONCEPTUAL TO RELATIONAL DATABASE
Ex.No: 2(b)
AND VALIDATE USING NORMALIZATION
Date :

AIM:
To design the database using Normalization.

PROCEDURE:
STEP 1: Identify all the un-normalized data and the functional dependencies between the set of attributes.

STEP 2: Identify the Primary Key for a Relation Using Functional Dependencies
STEP 3: Apply the First Normal Form and the Second Normal Form

STEP 4: Identify the transitive dependencies and apply the Third Normal Form
 STEP 5: Identify the minimal set of functional dependencies and apply Boyce-Codd Normal Form

STEP 6: Identify the multi-valued dependencies and apply the Fourth Normal Form

STEP 7: Identify the lossless-join dependencies and apply the Fifth Normal Form

DESCRIPTION:

DATABASE NORMALIZATION
Database Normalization is a technique of organizing the data in the database. Normalization is a
systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics
like Insertion, Update and Deletion Anomalies.
Normalization is used for mainly two purpose,
Eliminating redundant (useless) data.
Ensuring data dependencies make sense i.e. data is logically stored.

Table Name: Student

S_i
d S_Na meS_Addre
s sSubje
ct_op
t
e
d
401 Adam Noida Bio
402 Alex Panipat Maths
403 Stuart Jammu Maths
404 Adam Noida Physics

Problem Without Normalization :

Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized.
Updation Anamoly : To update address of a student who occurs twice or more than twice in a table, we will have
to update S_Address column in all the rows, else data will become inconsistent.
Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name and address of a
student but if student has not opted for any subjects yet then we have to insert NULL there, leading to
Insertion Anamoly.
Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when we delete that
row, entire student record will be deleted along with it.

Normalization Rule
Normalization rule are divided into following normal form.
1. First Normal Form - Tables Must Not Contain Repeating Groups Of Data
2. Second Normal Form - Eliminations of Redundant data
3. Third Normal Form - Eliminate columns Not Dependant on the key .
4. Fourth Normal Form - Isolate Independent Multiple Relations .
5. BCNF
First Normal Form (1NF)
o As per First Normal Form, no two Rows of data must contain repeating group of information
i.e each set of column must have a unique value, such that multiple columns cannot be used to
fetch the same row.
 o It should hold only atomic(Single) values.
Example: Suppose a company wants to store the names and contact details of its employees. It creates a
table that looks like this:
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
8123450987
Two employees (Jon & Lester) are having two mobile numbers so the company stored them in the
same field as you can see in the table above.
This table is not in 1NF as the rule says “each attribute of a table must have atomic (single) values”,
the emp_mobile values for employees Jon & Lester violates that rule.

To make the table complies with 1NF we should have the data like this:
Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
102 Jon Kanpur 9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
104 Lester Bangalore 8123450987

Second normal form (2NF)

A table is said to be in 2NF if both the following conditions hold:
Table is in 1NF (First normal form)
No non-prime attribute is dependent on the proper subset of any candidate key of table. An
attribute that is not part of any candidate key is known as non-prime attribute.
Example: Suppose a school wants to store the data of teachers and the subjects they teach. They create a
table that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows
for a same teacher.
Te a
ch
e
r
_
i
dSubject Teac
h
e
r
_a
g
e
111 Maths 38
111 Physics 38
222 Biology 38
333 Physics 40
333 Chem istr
y
40
Candidate Keys: {teacher_id, subject}
Non prime attribute: teacher_age
The table is in 1 NF because each attribute has atomic values. However, it is not in 2NF because non prime
attribute teacher_age is dependent on teacher_id alone which is a proper subset of candidate key.
This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper
subset of any candidate key of the table”.
 To make the table complies with 2NF we can break it in two tables like this:
Table Name: teacher_details
Teacher_id Teacher_age
111 38
222 38
333 40
Table Name: teacher_subject table:
T eacher_i
d Subject
111 Maths
111 Physics
222 Biology
333 Physics
333 Chem istr
y
Now the tables comply with Second normal form (2NF).

Third Normal form (3NF)

A table design is said to be in 3NF if both the following conditions hold:
Table must be in 2NF
Transitive functional dependency of non-prime attribute on any super key should be removed. An
attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional
dependency X-> Y at least one of the following conditions hold:
X is a super key of table
Y is a prime attribute of table
An attribute that is a part of one of the candidate keys is known as prime attribute.
Example: Suppose a company wants to store the complete address of each employee, they create a table
named employee_details that looks like this:
Em p_id Em p_name Em p_z
ip E
mp_state Em p _
c
i
t
y Em p_
d i
strict
1001 John 282005 UP Agra Dayal Bagh
1002 Ajeet 222008 TN Chennai M-City
1006 Lora 282007 TN Chennai Urrapakkam
1101 Lilly 292008 UK Pauri Bhagwan
1201 Steve 222999 MP Gwalior Ratan
Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on
Candidate Keys: {emp_id}
Non-prime attributes: all attributes except emp_id are non-prime as they are not part of
any candidate keys.
Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id
that makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key
(emp_id). This violates the rule of 3NF.
To make this table complies with 3NF we have to break the table into two tables to remove the
transitive dependency:
Employee table:
Emp_id Emp_name Emp_zip
 1001 John 282005
1002 Ajeet 222008
006 Lora 282007
1101 Lilly 292008
1201 Steve 222999
Employee_zip table:
Em p_zi
p Em p_sta
te E mp _c
ity Emp_distric
t
282005 UP Agra Dayal Bagh
222008 TN Chennai M-City
282007 TN Chennai Urrapakkam
292008 UK Pauri Bhagwan
222999 MP Gwalior Ratan
Boyce Codd normal form (BCNF)
It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A
table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super
key of the table.
Example: Suppose there is a company wherein employees work in more than one department. They store the
data like this:
Em p_i
d Em p_natio
nali
ty Emp_dept Dept_ t
ype D ept_no_of_ e
m
p
1001 Austrian Production and planning D001 200
1001 Austrian stores D001 250
1002 American design a
ndtechnicalsupport D134 100
1002 American Purchasing department D134 600
Functional dependencies in the table above:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither emp_id nor emp_dept alone are keys.
To make the table comply with BCNF we can break the table in three tables like this:
Emp_Nationality Table:
Emp_
idEmp_nati
o n
a
l
i
t
y
1001 Austrian
1002 American

emp_dept table:
Emp_dept Dep
t_ty
p
eDep
t_n
o
_
o
f
_e
m
p
P r
oduc t
i
o
n a
n
d
pl
a
n
ni
n
gD001 200
stores D001 250
d e
sign a
n
d t
e
c
h
ni
c
a
ls
u
pp
o
r
tD134 100
P u
rcha s
i
ngd
e
pa
r
t
me
n
t D134 600
emp_dept_mapping table:
Emp_
idEmp_dept
1001 Production and planning
1001 stores
 1002 des
ignan dtechnic
a l
suppor
t
1002 Purchasing department
Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
This is now in BCNF as in both the functional dependencies left side part is a key.
Ex.No: 3
DDL COMMANDS WITH CONSTRAINTS AND
Date :
VIEWS

AIM:
To execute and verify the DDL commands with constraints and views.

PROCEDURE

STEP 1: Create the table with its essential attributes and constraints.

STEP 2: Insert attribute values into the table

STEP 3: Create the view by selecting few columns and rows from a table.

STEP 4: Alter the structure of the table and add/modify/drop columns

STEP 5: Alter the structure of the table and add/modify/drop constraints

PROGRAM/SOURCE CODE

Create table

create table stud(stuname varchar(20),age integer);

select * from information_schema.columns where table_name ='stud';

Alter table

i) Add
create table stud(stuname varchar(20),age integer);

alter table stud add regno integer;

 ii) Modify

create table stud(stuname varchar(20),age integer); alter

table stud alter column stuname type varchar(25);

Truncate table

create table stud(stuname varchar(20),age integer);

insert into stud values('Ram',25);

truncate table stud;

Drop table

create table stud(stuname varchar(20),age integer);

drop table stud;

NOT NULL constraint

create table stud(stuname varchar(20)NOT NULL,age integer);

insert into stud values('',24);

insert into stud values('Ram',25);

CHECK constraint

create table stud(stuname varchar(20),age integer CHECK (age>0));

insert into stud values('Ram',-1);

UNIQUE constraint

create table stud(stuname varchar(20)UNIQUE,age integer);

insert into stud values('Ram',24); insert into stud

values('Ram',26);

primary constraint

create table stud(stuname varchar(20)primary key,age integer);

insert into stud values('Ram',24); insert into stud

values('Ram',25);

default constraint

create table stud(stuname varchar(20),age integer,country varchar(20)default 'India');

insert into stud values('Ram',24);

insert into stud values('Ram',25);

select * from stud;

Foreign constraint

create table pat1(pat_id integer primary key,pat_name varchar(20));

insert into pat1 values(1,'Ram');

insert into pat1 values(2,'Arun');

select * from pat1;

create table pat2(pat_accno integer,pat_id integer references pat1(pat_id));

insert into pat2 values(2021,1);

insert into pat2 values(2022,2);

VIEW
create table tab1(stuname varchar(20),reg_no integer,mark integer);

insert into tab1 values('Ram',31162021,98); insert into tab1

values('Shiva',31162022,98);

insert into tab1 values('Arun',31162023,97);

insert into tab1 values('Sai',31162024,99);

create view v1 as select reg_no,mark from tab1;

select * from v1;

CREATE VIEW command can be used with WHERE clause.

create table tab1(stuname varchar(20),reg_no integer,mark integer);

insert into tab1 values('Ram',31162021,98);

insert into tab1 values('Shiva',31162022,98);

insert into tab1 values('Arun',31162023,97);

insert into tab1 values('Sai',31162024,99);

create view v1 as select reg_no,mark from tab1 where

mark>97; select * from v1;

CREATE VIEW command can be used with AND and OR operators.

create table emp(empname varchar(20),emp_age integer,country varchar(30),city varchar(30));

insert into emp values('Ram',25,'India','Surat');

insert into emp values('Shiva',24,'India','Bangalore');

insert into emp values('Arun',25,'US','Washington');

insert into emp values('Sai',26,'UK','Elimi');

create view v1 as select * from emp where (country='India' AND city='Bangalore') OR

(country='US'AND city='Washington');

select * from v1;

CREATE VIEW command can be used with GROUP BY clause.

create table stud(stuname varchar(20),age integer,city varchar(20));

insert into stud values('Ram',20,'Chennai'); insert into stud

values('Shiva',22,'Bangalore');

insert into stud values('Arun',23,'Chennai');

insert into stud values('Aadhavan',20,'Chennai');

insert into stud values('Raghavan',24,'Bangalore');

insert into stud values('Ram',20,'Chennai');

create view v1 as select city,count(*) from stud group by city;

select * from v1;

RESULT:

Thus, to execute and verify the DDL commands with constraints and views has been executed
successfully.
EX.NO:4 Query the Database using SQL Manipulation

Date :

AIM:
To implement and execute procedures and functions in database using Procedural Language concepts.

PROCEDURE:

STEP 1: Initialize the necessary parameters.

STEP 2: Develop the set of statements with the essential operational parameters.

STEP 3: Specify the Individual operation to be carried out.

STEP 4: Execute the procedure.

PROGRAM/SOURCE CODE:

INSERT

INSERT-Direct insert

create table stud(stuname varchar(20),regno integer,age

integer); insert into stud values('Ram',2023,20); select *

from stud;
INSERT-Insert during runtime

INSERT-Insert values for particular attributes

create table stud(stuname varchar(20),regno integer,age integer);

insert into stud(stuname,regno) values('Ram',2023); insert into

stud(stuname,age) values('Ram',20);

select * from stud;

INSERT-Insert using null values

create table stud(stuname varchar(20),regno integer,age integer);

insert into stud values('Ram',2023,null); insert into stud

values('Arun',null,20);

select * from stud;

INSERT-Insert by copying from other table

create table stud1(stuname varchar(20),regno integer,age integer);

insert into stud1 values('Ram',2023,20);

insert into stud1 values('Arun',2024,19);

create table stud2(stuname varchar(20),regno integer,age

integer); insert into stud2(stuname,regno,age) select *

from stud1; select * from stud1;

select * from stud2;

SELECT

SELECT-SELECT SPECIFIC ROWS ONLY :

create table stud1(stuname varchar(20),regno integer,age

integer); insert into stud1 values('Ram',2023,20); insert

into stud1 values('Arun',2024,19);

select stuname,age from stud1 where regno=2024;

select * from stud1 where stuname='Ram';

SELECT-SELECT ALL ROWS AND COLUMNS:

create table stud1(stuname varchar(20),regno integer,age integer);

insert into stud1 values('Ram',2023,20); insert into stud1

values('Arun',2024,19);

select * from stud1;

SELECT-SELECT COLUMN ONLY:

create table stud1(stuname varchar(20),regno integer,age integer);

insert into stud1 values('Ram',2023,20); insert into stud1

values('Arjn',2024,20);

insert into stud1 values('Arun',2025,19);

select stuname,regno from stud1;

UPDATE

UPDATE – Simple update

create table stud1(stuname varchar(20),regno integer,age integer); insert

into stud1 values('Ram',2023,20); insert into stud1 values('Arjn',2024,20);

insert into stud1 values('Arun',2025,19);

select * from stud1;

update stud1 set age=20 where stuname='Arun';

select * from stud1;

UPDATE – Update with calculation

create table stud1(stuname varchar(20),regno integer,mark integer); insert

into stud1 values('Ram',2023,95); insert into stud1 values('Arjn',2024,94);

insert into stud1 values('Arun',2025,80);

select * from stud1;

update stud1 set mark=mark+5 where mark<=85;

select * from stud1;

UPDATE- UPDATE NULL VALUES :

create table

stud1(stuname
varchar(20),regno integer,mark integer); insert into stud1

values('Ram',2023,95); insert into stud1 values('Arjn',2024,94);

insert into stud1 values('Arun',2025,null);

select * from stud1;

update stud1 set mark=85 where mark is null;

select * from stud1;

DELETE

DELETE-DELETE SPECIFIC RECORD

create table stud1(stuname varchar(20),regno integer,mark integer); insert

into stud1 values('Ram',2023,95); insert into stud1 values('Arjn',2024,94);

insert into stud1 values('Arun',2025,80);

select * from stud1;

delete from stud1 where stuname='Arun';

select * from stud1;

DELETE-DELETE ALL RECORDS

create table stud1(stuname varchar(20),regno integer,mark integer); insert

into stud1 values('Ram',2023,95); insert into stud1 values('Arjn',2024,94);

insert into stud1 values('Arun',2025,80);

select * from stud1;

delete from stud1;

select * from stud1;

RESULT:

Thus, to implement and execute procedures and functions in database using Procedural Language concepts
has been executed successfully.
 Ex.No: 5
Date : DATABASE PROGRAMMING: STORED
PROCEDURES/FUNCTIONS

AIM:
To implement and execute procedures and functions in database using Procedural Language concepts.

PROCEDURE:

STEP 1: Initialize the necessary parameters.

STEP 2: Develop the set of statements with the essential operational parameters.

STEP 3: Specify the Individual operation to be carried out.

STEP 4: Execute the procedure.

PROGRAM/SOURCE CODE PL/SQL
PL/SQL stands for Procedural Language/SQL. PL/SQL extends SQL by adding constructs found in
procedural languages,

Genera Structure:
DECLARE
//Declarative section: variables, types, and local subprograms
BEGIN
//Executable section: procedural and SQL statements go here.
//This is the only section of the block that is required.
EXCEPTION
//Exception handling section: error handling statements go here.
END;
In PL/SQL code groups of commands are arranged within a block. A block groups related declarations or
statements. A block can be contained within another block or in a larger programming unit called a subprogram.
A subprogram can be a stored procedure, function, trigger or package.
A stored procedure or simply procedure is essentially an encapsulated named block of code stored
within the database.
A function is distinguished from a procedure in that it is able to return a value. Both functions and
procedures can take parameters and are called interactively.
A procedure cannot be called directly from SQL. A function (in most cases) can be called from a SQL
statement.
A trigger cannot take parameters and is called automatically when a database event occurs.
A trigger - like a stored procedure - is a subprogram that does not return a value.
Examples of triggering events include the execution of DML statements (an insert, update or delete) or DDL
statements (create, alter, drop).
 A trigger cannot take parameters and is called automatically when a database event occurs.
A trigger - like a stored procedure - is a subprogram that does not return a value.
Examples of triggering events include the execution of DML statements (an insert, update or delete) or DDL
statements (create, alter, drop).

A Package serves as a container for functions, procedures and PL/SQL types.

Advantage of Using PL/SQL

1. Better performance, as sql is executed in bulk rather than a single statement
2. High Productivity
3. Tight integration with SQL
4. Full Portability
5. Tight Security
6. Support Object Oriented Programming concepts.

PL/SQL Iterative Control:

The LOOP statements allow you to execute one or more sequence of statements multiple times. There are
three forms of LOOP statements: LOOP, WHILE-LOOP, FOR-LOO.

LOOP Statement:
LOOP
-- sequence of statements
END LOOP;

EXIT Statement:
LOOP
v_count := 10;
IF v_count = 10 THEN
EXIT;
END IF;
END LOOP;

EXIT-WHEN Statement:
LOOP
v_count := v_count + 10;
EXIT WHEN v_count = 11;
END LOOP;

FOR-LOOP Statement:
FOR counter IN [REVERSE] lower_number.. higher_number LOOP
-- sequence of statements
END LOOP;

WHILE-LOOP Statement:
WHILE condition LOOP
-- sequence of statements
END LOOP;
Creating Subprograms:
create procedure test1 is
begin
dbms_output.put_line('Hello from a stored procedure');
end;
/
To Run :

$ exec test1

PL/SQL PROCEDURE:
The PL/SQL stored procedure or simply a procedure is a PL/SQL block which performs one or more
specific tasks.

The procedure contains a header and a body.

o Header: The header contains the name of the procedure and the parameters or variables passed to the
procedure.
o Body: The body contains a declaration section, execution section and exception section similar to a
general PL/SQL block.

How to pass parameters in procedure:

1. There is three ways to pass parameters in procedure: IN parameters: The IN parameter can be
referenced by the procedure or function. The value of the parameter cannot be overwritten by the
procedure or the function.
2. OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the
value of the parameter can be overwritten by the procedure or function.
3. INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the
value of the parameter can be overwritten by the procedure or function.

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name];
Table creation:
SQL > create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.

Procedure Code:
create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin
insert into user1 values(id,name);
end;
/
Sample Output:

Procedure created.
SQL > EXECprocedure_name;
SQL > EXEC insertuser(106,'Ram');
PL/SQL program to call procedure

Let's see the code to call above created procedure.

BEGIN
insertuser(106,'Ram');
dbms_output.put_line('record inserted successfully');
END;
/

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
Table creation:
SQL > create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.

Procedure Code:
create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin
insert into user1 values(id,name);
Syntax for creating procedure:
CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
Table creation:
SQL > create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.

Procedure Code:
create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin
insert into user1 values(id,name);
 1.IN parameters: The IN parameter can be referenced by the procedure or function. The value of the
parameter cannot be overwritten by the procedure or the function.
2.OUT parameters: The OUT parameter cannot be referenced by the procedure or function, but the
value of the parameter can be overwritten by the procedure or function.
3.INOUT parameters: The INOUT parameter can be referenced by the procedure or function and the
value of the parameter can be overwritten by the procedure or function.

Syntax for creating procedure:

CREATE [OR REPLACE] PROCEDURE procedure_name
[ (parameter [,parameter]) ]
IS
[declaration_section]
BEGIN
executable_section
[EXCEPTION
exception_section]
END [procedure_name]
Table creation:
SQL > create table user(id number(10) primary key,name varchar2(100));
Now write the procedure code to insert record in user table.

Procedure Code:
create or replace procedure "INSERTUSER" (id IN NUMBER, name IN VARCHAR2) is begin
insert into user1 values(id,name);
end;
/
Sample Output:

Procedure created.
SQL > EXECprocedure_name;
SQL > EXEC insertuser(106,'Ram');
PL/SQL program to call procedure

Let's see the code to call above created procedure.

BEGIN
insertuser(106,'Ram');
dbms_output.put_line('record inserted successfully');
END;
/
FUNCTIUONS:

Function is a subprogram that computes and returns a single value Functions and procedures are
structured alike, except that functions have a RETURN clause. A function has two parts: the specification and the
body. The function specification begins with thekeyword FUNCTION and ends with the RETURN clause, which
specifies the data type of the result value.Parameter declarations are optional. Functions that take no parameters
are written withoutparentheses. The function body begins with the keyword IS and ends with the keyword END
followedby an optional function name.

Comparing Procedures and Functions

PROCEDURE FUNCTION
Execute as a PL/SQL statement Invoke as part of an expression

No RETURN data type Must contain a RETURN data type

Can return none, one or many values Must return a single value

Can not use in SQL Statement Can Use in SQL Statements

Benefits of Stored Procedures and Functions

In addition to modularizing application development, stored procedures and functions have the
following benefits:
Improved performance
• Avoid reparsing for multiple users by exploiting the shared SQL area
• Avoid PL/SQL parsing at run-time by parsing at compile time
• Reduce the number of calls to the database and decrease network traffic by binding

commands
Improved maintenance.
• Modify routines online without interfering with other users
• Modify one routine to affect multiple applications
• Modify one routine to eliminate duplicate testing
Improved data security and integrity

• Control indirect access to database objects from non privileged users with
security Privileges.
•Ensure that related actions are performed together, or not at all, by funneling
activity for related tables through a single path.
Syntax:
CREATE [OR REPLACE] FUNCTION function_name
 [(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN type
{IS | AS}
BEGIN
function_body
END function_name;

Where

1. OR REPLACE specifies the function that is to replace an existing function if present.

2. Type specifies the PL/SQL type of the parameter.
3. The body of a function must return a value of the PL/SQL type specified in the RETURN clause

EXAMPLE:

1> CREATE OR REPLACE FUNCTION employer_details_func

2> RETURN VARCHAR(20);
3> IS
4> emp_name VARCHAR(20);
5> BEGIN
7> SELECT first_name INTO emp_name
8> FROM emp_tbl WHERE empID = '100';
9> RETURN emp_name;
10> END;
11> /

In the example we are retrieving the ‘first_name’ of employee with empID 100 to variable ‘emp_name’.
The return type of the function is VARCHAR which is declared in line no 2.
The function returns the 'emp_name' which is of type VARCHAR as the return value in line no 9.

How to execute a PL/SQL Function?

A function can be executed in the following ways.

1) Since a function returns a value we can assign it to a variable.

employee_name := employer_details_func;

If ‘employee_name’ is of datatype varchar we can store the name of the employee by assigning

the return type of the function to it.

2) As a part of a SELECT statement

SELECT employer_details_func FROM dual;

3) In a PL/SQL Statements like,

dbms_output.put_line(employer_details_func);
This line displays the value returned by the function.

PL/SQL FUNCTION :
 The PL/SQL Function is very similar to PL/SQL Procedure. The main difference between procedure
and a function is, a function must always return a value, and on the other hand a procedure may or may not
return a value.
Syntax to create a function:

CREATE [OR REPLACE] FUNCTION function_name [parameters]

[(parameter_name [IN | OUT | IN OUT] type [, ...])]
RETURN return_datatype
{IS | AS}
BEGIN
< function_body >
END [function_name];
Ex. :

Create a function :
create or replace function adder(n1 in number, n2 in number)
return number
is
n3 number(8);
begin
n3 :=n1+n2;
return n3;
end;

To call the function :

DECLARE
n3 number(2);
BEGIN
n3 := adder(11,22);
dbms_output.put_line('Addition is: ' || n3);
END;
/

Output:

Addition is: 33
Statement processed.
0.05 seconds

PL/SQL Implicit Cursor :

A PL/SQL cursor is a pointer that points to the result set of an SQL query against database tables.
 Attribute Description
%FOUND Itsre tur
n valueis TRU EifD
ML stat
em ents l
ike I
N SERT,DELETE andUPDA TE a
f
f
ec
t
at least onerow orm orero
ws ora SELEC T INT O st
a t
em entreturne
d oneo r
m
or
e
rows. Otherwise it returns FALSE.
%N OTFOUN D Itsre tur
n valueis TRU EifD
ML stat
em ents l
ike I
N SERT,DELETE andUPDA TE a
f
f
ec
t
no ro w,or aSELE CT INTO s
tate
m entreturn no rows.O t
h er
wise i
t re
tur
ns F A
L
S
E.
It
is a just opposite of %FOUND.
%ISOPEN It a lwa ys returns FALSEfor im pli
cit cursors, becaus
e the SQL c ur
s
o
ris
automatically closed after executing its associated SQL statements.
%R OW COUNT Itret urnsthenu mb erofro
wsa f
fecte
d by D ML stat
em entsli
keIN SERT,
DELE TE
,
a
nd
UPDATE or returned by a SELECT INTO statement
Create customers table and have records:
ID NAME A GE ADDRESS S ALA RY
1 Ra mesh 23 Allahabad 20000
2 Suresh 22 Kanpur 22000
3 Mahesh 24 G haziabad 24000
4 Chanda n 25 Noida 26000
5 Alex 21 Paris 28000
6 Sunita 20 Delhi 30000
Let's execute the following program to update the table and increase salary of each customer by 5000. Here,
SQL%ROWCOUNT attribute is used to determine the number of rows affected:
Create Cursor procedure:

DECLARE
total_rows number(2);
BEGIN
UPDATE emp SET sal = sal + 5000;
IF sql%notfound THEN
dbms_output.put_line('no customers updated');
ELSIF sql%found THEN
total_rows := sql%rowcount;
dbms_output.put_line( total_rows || ' customers updated ');
END IF;
END;
/
Sample Output:
6 customers updated
PL/SQL procedure successfully completed.
PL/SQL Explicit Cursors:

You must follow these steps while working with an explicit cursor.
1. Declare the cursor to initialize in the memory.
2. Open the cursor to allocate memory.
3. Fetch the cursor to retrieve data.
4. Close the cursor to release allocated memory.
Create Cursor procedure:

Execute the following program to retrieve the customer name and address.

DECLARE
c_id customers.id%type;
c_name customers.name%type;
c_addr customers.address%type;
CURSOR c_customers is SELECT id, name, address FROM customers;
BEGIN
OPEN c_customers;
LOOP
FETCH c_customers into c_id, c_name, c_addr;
EXIT WHEN c_customers%notfound;
dbms_output.put_line(c_id || ' ' || c_name || ' ' || c_addr);
END LOOP;
CLOSE c_customers;
END;
/

Sample Output:
1 Ramesh Allahabad
2 Suresh Kanpur
3 Mahesh Ghaziabad
4 Chandan Noida
5 Alex Paris
6 Sunita Delhi
PL/SQL procedure successfully completed.

RESULT:
Thus, to implement and execute procedures and functions in database using Procedural
Language concepts has been executed successfully
EX NO: 6 DATABASE PROGRAMMING CONSTRAINTS AND SECURITY USING
DATE: TRIGGERS

AIM:

To implement the database programming to enforce Constraints and security using Triggers in PL/SQL

PROCEDURE:

STEP 1: Create a database trigger for a table on any one type of operations.

STEP 2: Execute the statement related to the operation on which the trigger is defined.

STEP 3: Check whether the code written in the trigger is automatically executed.

PROGRAM/SOURCE CODE
PL/SQL TRIGGER:
Trigger is invoked by Oracle engine automatically whenever a specified event occurs.Trigger is
stored into database and invoked repeatedly, when specific condition match. Advantages of Triggers
These are the following advantages of Triggers:
o Trigger generates some derived column values automatically
o Enforces referential integrity
o Event logging and storing information on table access
o Auditing
o Synchronous replication of tables
o Imposing security authorizations
o Preventing invalid transactions

General Syntax :

CREATE [OR REPLACE ] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF }
{INSERT [OR] | UPDATE [OR] | DELETE}
[OF col_name]
ON table_name
[REFERENCING OLD AS o NEW AS n]
[FOR EACH ROW]
WHEN (condition)
BEGIN
--- sql statements
END;

Ex. : Create and display trigger for changing the salary on customer table

CREATE OR REPLACE TRIGGER display_salary_changes BEFORE

 DELETE OR INSERT OR UPDATE ON customers
FOR EACH ROW
WHEN (NEW.ID > 0)
DECLARE
sal_diff number;
BEGIN
sal_diff := :NEW.salary - :OLD.salary;
dbms_output.put_line('Old salary: ' || :OLD.salary);
dbms_output.put_line('New salary: ' || :NEW.salary);
dbms_output.put_line('Salary difference: ' || sal_diff);
END;
/

Exception handling

Predefined Exception :
Oracle
Or
acleE
x c
ept
ionNam e Explanation
Error
ORA-Y ou tried toex ecute an I
N S ERT or U PDA T Esta t
em ent that has
DUP_VAL_ON_INDEX
00001 created a duplicate value in a field restricted by a unique index.
TIME
OU T_
O N_
R E
SO
URC
EORA-You were waiting for a resource and you timed out.
00051
ORA-You tried to execute a call to Oracle before logging in.
NOT_LOGGED_ON
01012
ORA-You tried to log into Oracle with an invalid username/password
LOGIN_DENIED
01017 combination.
ORA-You tried one of the following:
01403 1. You execut e
d aSEL ECT I
N TO statement and no ro w
s
we
re
NO_DATA_FOUND returned.
2. You referenced an uninitialized row in a table.
3 .Y ouread p astthe e
n dof fil
e w ith t
he U T L_FILE p
a
c
kag
e.
ORA-Y ou trie
d t
o execu t
e aSELECT IN TO s t
a t
e menta nd more t
h
a
no
ne
TOO_MANY_ROWS
01422 row was returned.
ORA-You tried to divide a number by zero.
ZERO_DIVIDE
01476
ORA-Y ou tried toexecu teaS QL statem ent thattried to c
on v e
r
t
as
tri
ng
INVALID_NUMBER
01722 to a number, but it was unsuccessful.
C
U
R
S
OR
_
A
L
RE
A
D
Y_
O
P
ENORA-You tried to open a cursor that is already open.
06511
 Ex. : PL/SQL Program using Exception handling

CR EAT EOR REPLA CE PR OCE

D UR E
add_new_ order (order_id _
inI
N NU M BER ,sales_in I
N NU M B E
R
)
IS
no _sales EX CEP TI
ON ; // user defined exception
BEGIN
IF sales_in = 0 THEN
RAISE no_sales;
ELSE
INSERT INTO orders (order_id, total_sales ) VALUES ( order_id_in, sales_in );
END IF;
EXCEPTION
WHEN no_sales THEN
raise_application_error (-20001,'You must have sales in order to submit the order.');
WHEN DUP_VAL_ON_INDEX THEN
raise_application_error (-20001,'You have tried to insert a duplicate order_id.');
WHEN OTHERS THEN
raise_application_error (-20002,'An error has occurred inserting an order.');
END;
/

RESULT:
Thus,to implement the database programming to enforce Constraints and security using Triggers
in PL/SQL
Ex.No: 7
Date : DATABASE DESIGN USING NORMALIZATION – BOTTOM-UP
APPROACH

AIM:

To design the structures of the relations in a database system using normalization.

PROCEDURE:

STEP 1: Identify the data elements in the system and group the related data elements into multiple relations.

STEP 2: Apply 1NF to ensure that the database stores only the atomic values.
STEP 3: Apply 2NF to ensure that no non-prime attribute in a relation is dependent on the proper subset of
any candidate key.
STEP 4: Apply 3NF to ensure that no non-prime attribute is transitive functional dependent on any super key.

STEP 5: Apply BCNF to ensure that the non-prime attributes functionally dependent only on the super key.

PROGRAM/SOURCE CODE:

Database Normalization:
Database Normalization is a technique of organizing the data in the database. Normalization is a
systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics
like Insertion, Update and Deletion Anomalies.
Normalization is used for mainly two purpose,
Eliminating redundant (useless) data.
Ensuring data dependencies make sense i.e. data is logically
stored. Table Name: Student

S
_
i
dS
_
Na
m
eS
_
A
dd
r
e
s
sSu
b
j
e
c
t_
o
pt
e
d
401 Adam Noida Bio
402 Alex Panipat Maths
403 Stuart Jammu Maths
404 Adam Noida Physics

Problem Without Normalization :

Insertion, Updation and Deletion Anamolies are very frequent if Database is not Normalized.
 Updation Anamoly : To update address of a student who occurs twice or more than twice in a table, we will
have to update S_Address column in all the rows, else data will become inconsistent.
Insertion Anamoly : Suppose for a new admission, we have a Student id(S_id), name and address of a
student but if student has not opted for any subjects yet then we have to insert NULL there, leading to
Insertion Anamoly.
Deletion Anamoly : If (S_id) 401 has only one subject and temporarily he drops it, when we delete that
row, entire student record will be deleted along with it.

Normalization Rule

Normalization rule are divided into following normal form.

1. First Normal Form - Tables Must Not Contain Repeating Groups Of Data
2. Second Normal Form - Eliminations of Redundant data
3. Third Normal Form - Eliminate columns Not Dependent on the key.
4.Fourth Normal Form - Isolate Independent Multiple Relations.
5.BCNF – Every determinant is a primary key.
First Normal Form (1NF)
o As per First Normal Form, no two Rows of data must contain repeating group of information
i.e each set of column must have a unique value, such that multiple columns cannot be used
to fetch the same row.
o It should hold only atomic(Single) values.
Example: Suppose a company wants to store the names and contact details of its employees. It creates a
table that looks like this:

Emp_id Emp_name Emp_address Emp_mobile

101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
8123450987
Two employees (Jon & Lester) are having two mobile numbers so the company stored them in the
same field as you can see in the table above.

This table is not in 1NF as the rule says “each attribute of a table must have atomic (single)
values”, the emp_mobile values for employees Jon & Lester violates that rule. To make the table complies
with 1NF we should have the data like this:
 Emp_id Emp_name Emp_address Emp_mobile
101 Herschel New Delhi 8912312390
102 Jon Kanpur 8812121212
102 Jon Kanpur 9900012222
103 Ron Chennai 7778881212
104 Lester Bangalore 9990000123
104 Lester Bangalore 8123450987

Second normal form (2NF)

A table is said to be in 2NF if both the following conditions hold:
Table is in 1NF (First normal form)
No non-prime attribute is dependent on the proper subset of any candidate key of table.

An attribute that is not part of any candidate key is known as non-prime attribute.
Example: Suppose a school wants to store the data of teachers and the subjects they teach. They create a
table that looks like this: Since a teacher can teach more than one subjects, the table can have multiple rows
for a same teacher.
T
e
a
c
h
er
_
i
dSubject T
e
a
c
h
er
_
a
g
e
111 Maths 38
111 Physics 38
222 Biology 38
333 Physics 40
333 C
h
e
m
i
st
r
y40

Candidate Keys: {teacher_id, subject}

Non prime attribute: teacher_age
The table is in 1 NF because each attribute has atomic values. However, it is not in 2NF because non prime
attribute teacher_age is dependent on teacher_id alone which is a proper subset of candidate key.

This violates the rule for 2NF as the rule says “no non-prime attribute is dependent on the proper
subset of any candidate key of the table”.
To make the table complies with 2NF we can break it in two tables like this:
teacher_details table:
Teacher_id Teacher_age

111 38
222 38

333 4
teacher_subject table:

T
e
a
c
h
er
_
i
dSubject
111 Maths
111 Physics
222 Biology
333 Physics
333 Chemistry
Now the tables comply with Second normal form (2NF).
Third Normal form (3NF)
A table design is said to be in 3NF if both the following conditions hold:
Table must be in 2NF
Transitive functional dependency of non-prime attribute on any super key should be removed. An
attribute that is not part of any candidate key is known as non-prime attribute.
In other words 3NF can be explained like this: A table is in 3NF if it is in 2NF and for each functional
dependency X-> Y at least one of the following conditions hold:
X is a super key of table
Y is a prime attribute of table
An attribute that is a part of one of the candidate keys is known as prime attribute.

Example: Suppose a company wants to store the complete address of each employee, they create a table
named employee_details that looks like this:

E
m
p
_
id
Em
p
_
n
am
e
E
mp
_
z
i
pE
m
p
_s
t
a
t
eEm
p
_
c
i
t
yEm
p
_
d
i
s
tr
i
c
t
1001 John 282005 UP Agra Dayal Bagh
1002 Ajeet 222008 TN Chennai M-City
1006 Lora 282007 TN Chennai Urrapakkam
1101 Lilly 292008 UK Pauri Bhagwan
1201 Steve 222999 MP Gwalior Ratan
Super keys: {emp_id}, {emp_id, emp_name}, {emp_id, emp_name, emp_zip}…so on

Candidate Keys: {emp_id}

Non-prime attributes: all attributes except emp_id are non-prime as they are not part of
any candidate keys.

Here, emp_state, emp_city & emp_district dependent on emp_zip. And, emp_zip is dependent on emp_id that
makes non-prime attributes (emp_state, emp_city & emp_district) transitively dependent on super key
(emp_id). This violates the rule of 3NF.
 To make this table complies with 3NF we have to break the table into two tables to remove the
transitive dependency:

Employee table:

thrh

The

E
m
p
_
id
Em
p
_
n
am
eE
m
p
_z
i
p
1001 John 282005
1002 Ajeet 222008
1006 Lora 282007
1101 Lilly 292008

1201 steve 222999

Employee_zip table:

E
m
p
_
zi
pE
mp
_
s
t
a
teE
m
p
_
ci
t
yE
mp
_
d
i
s
tr
i
c
t
282005 UP Agra Dayal Bagh
222008 TN Chennai M-City
282007 TN Chennai Urrapakkam
292008 UK Pauri Bhagwan
222999 MP Gwalior Ratan

Boyce Codd normal form (BCNF)

It is an advance version of 3NF that’s why it is also referred as 3.5NF. BCNF is stricter than 3NF. A
table complies with BCNF if it is in 3NF and for every functional dependency X->Y, X should be the super
key of the table.
Example: Suppose there is a company wherein employees work in more than one department. They store the
data like this:

E
m
p
_
id
Em
p
_
n
at
i
o
n
al
i
t
y Emp_dept D
e
p
t
_
t
yp
e
De
p
t
_
no
_
o
f
_
em
p
1001 Austrian Production and planning D001 200
1001 Austrian stores D001 250
1002 American d
e
s
i
g
na
n
dt
e
c
h
ni
c
a
ls
u
pp
o
r
tD134 100
1002 American Purchasing department D134 600
Functional dependencies in the table above:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate key: {emp_id, emp_dept}
The table is not in BCNF as neither emp_id nor emp_dept alone are keys.
To make the table comply with BCNF we can break the table in three tables like this:
Emp_Nationality Table:

E
m
p
_
id
Em
p
_
n
at
i
o
n
al
i
t
y
1001 Austrian
1002 American

emp_dept table:

Emp_dept D
e
p
t
_
t
yp
eD
e
p
t
_
no
_
o
f
_
em
p
Production and planning D001 200
 Stores D001 250
d
e
s
i
g
na
n
dt
e
c
h
ni
c
a
ls
u
pp
o
r
tD134 100
Purchasing department D134 600

emp_dept_mapping table:

E
m
p
_
id
Emp_dept
1001 Production and planning
1001 stores
1002 d
e
s
i
g
na
n
dt
e
c
h
ni
c
a
ls
u
pp
o
r
t
1002 Purchasing department
Functional dependencies:
emp_id -> emp_nationality
emp_dept -> {dept_type, dept_no_of_emp}
Candidate keys:
For first table: emp_id
For second table: emp_dept
For third table: {emp_id, emp_dept}
This is now in BCNF as in both the functional dependencies left side part is a key.

RESULT:

Thus, to design the structures of the relations in a database system using normalization.
Ex:NO:8
DATE:
DATABASE DESIGN USING PEER-TO- MAPPING / UML CLASS DIAGRAMS
AIM:

To map the database design depicted in EER to ODB.

PROCEDURE:
STEP 1: Create an ODL class for each EER entity type or subclass. The type of the ODL class should include
all the attributes of the EER class.
STEP 2: Add relationship properties or reference attributes for each binary relation- ship into the ODL
classes that participate in the relationship.
STEP 3: Include appropriate operations for each class.
STEP 4: An ODL class that corresponds to a subclass in the EER schema inherits (via extends) the type and
methods of its superclass in the ODL schema.
STEP 5: Weak entity types can be mapped in the same way as regular entity types
STEP 6: An n-ary relationship with degree n > 2 can be mapped into a separate class, with appropriate
references to each participating class.
STEP 7: Draw the UML Class Diagram

DESCRIPTION

UML Class diagrams:

Class diagram is a static diagram. It represents the static view of an application. Class diagram is not only
used for visualizing, describing, and documenting different aspects of a system but also for constructing
executable code of the software application.

Class diagram describes the attributes and operations of a class and also the constraints imposed on the system.
The class diagrams are widely used in the modeling of objectoriented systems because they are the only UML
diagrams, which can be mapped directly with object-oriented languages.

Class diagram shows a collection of classes, interfaces, associations, collaborations, and constraints. It is
also known as a structural diagram.

Purpose of Class Diagrams

The purpose of class diagram is to model the static view of an application. Class diagrams are the only
diagrams which can be directly mapped with object-oriented languages and thus widely used at the time of
construction.
UML diagrams like activity diagram, sequence diagram can only give the sequence flow of the application, however

class diagram is a bit different. It is the most popular UML diagram in the coder community.

The purpose of the class diagram can be summarized as

Analysis and design of the static view of an application.

Describe responsibilities of a system.

Base for component and deployment diagrams.

Forward and reverse engineers.

class diagrams have a lot of properties to consider while drawing but here the diagram will be considered
from a top level view.

Class diagram is basically a graphical representation of the static view of the system and represents different

aspects of the application. A collection of class diagrams represent the whole system.

The following points should be remembered while drawing a class diagram

The name of the class diagram should be meaningful to describe the aspect of the system.
Each element and their relationships should be identified in advance.
Responsibility (attributes and methods) of each class should be clearly identified
For each class, minimum number of properties should be specified, as unnecessary properties will
make the diagram complicated.
Use notes whenever required to describe some aspect of the diagram. At the end of the drawing it
should be understandable to the developer/coder.
Finally, before making the final version, the diagram should be drawn on plain paper and reworked as
many times as possible to make it correct.

The following diagram is an example of an Order System of an application. It describes a particular aspect
of the entire application.

first of all, Order and Customer are identified as the two elements of the system. They have a one-to

any relationship because a customer can have multiple orders.

 Order class is an abstract class and it has two concrete classes (inheritance relationship)
SpecialOrder and NormalOrder.
The two inherited classes have all the properties as the Order class. In addition, they have additional
functions like dispatch () and receive ().
Use Class Diagrams:
Class diagram is a static diagram and it is used to model the static view of a system. The static view
describes the vocabulary of the system.

Class diagram is also considered as the foundation for component and deployment diagrams. Class diagrams
are not only used to visualize the static view of the system but they are also used to construct the
executable code for forward and reverse engineering of any system.

Generally, UML diagrams are not directly mapped with any object-oriented programming languages but the
class diagram is an exception.

Class diagram clearly shows the mapping with object-oriented languages such as Java, C++, etc. From
practical experience, class diagram is generally used for construction purpose.

In a nutshell it can be said, class diagrams are used for −

Describing the static view of the system.
Showing the collaboration among the elements of the static view.
Describing the functionalities performed by the system.
Construction of software applications using object oriented languages.
Object diagrams are derived from class diagrams so object diagrams are dependent upon class diagrams.

Object diagrams represent an instance of a class diagram. The basic concepts are similar for class diagrams
and object diagrams. Object diagrams also represent the static view of a system but this static view is a
snapshot of the system at a particular moment.

Object diagrams are used to render a set of objects and their relationships as an instance.

Purpose of Object Diagrams

The purpose of a diagram should be understood clearly to implement it practically. The purposes of object
diagrams are similar to class diagrams.
The difference is that a class diagram represents an abstract model consisting of classes and their
relationships. However, an object diagram represents an instance at a particular moment, which is concrete
in nature.

It means the object diagram is closer to the actual system behavior. The purpose is to capture the static
view of a system at a particular moment.

The purpose of the object diagram can be summarized as −

Forward and reverse engineering.

Object relationships of a system

Static view of an interaction.

Understand object behavior and their relationship from practical perspective

How to draw Object Diagram:

To capture a particular system, numbers of class diagrams are limited. However, if we consider object
diagrams then we can have unlimited number of instances, which are unique in nature. Only those instances
are considered, which have an impact on the system.

From the above discussion, it is clear that a single object diagram cannot capture all the necessary instances

or rather cannot specify all the objects of a system.

Hence, the solution is
First, analyze the system and decide which instances have important data and association.
Second, consider only those instances, which will cover the functionality.
Third, make some optimization as the number of instances are unlimited.

Before drawing an object diagram, the following things should be remembered and understood clearly
Object diagrams consist of objects.
The link in object diagram is used to connect objects.
Objects and links are the two elements used to construct an object diagram.

After this, the following things are to be decided before starting the construction of the diagram
The object diagram should have a meaningful name to indicate its purpose.
The most important elements are to be identified.
The association among objects should be clarified.
Values of different elements need to be captured to include in the object diagram.
 Add proper notes at points where more clarity is required.

The following diagram is an example of an object diagram. It represents the Order management system which we
have discussed in the chapter Class Diagram. The following diagram is an instance of the system at a particular
time of purchase. It has the following objects.
Customer Order

SpecialOrder

NormalOrder

Now the customer object (C) is associated with three order objects (O1, O2, and O3). These order objects are associated
with special order and normal order objects (S1, S2, and N1). The customer has the following three orders with different
numbers (12, 32 and 40) for the particular time considered.

The customer can increase the number of orders in future and in that scenario the object diagram will reflect that.
If order, special order, and normal order objects are observed then you will find that they have some values.

For orders, the values are 12, 32, and 40 which implies that the objects have these values for a particular moment
(here the particular time when the purchase is made is considered as the moment) when the instance is captured

The same is true for special order and normal order objects which have number of orders as 20, 30, and 60. If a different
time of purchase is considered, then these values will change accordingly. The following object diagram has been drawn
considering all the points mentioned above.

Where to Use Object Diagrams?

Object diagrams can be imagined as the snapshot of a running system at a particular moment. Let us
consider an example of a running train Now, if you take a snap of the running train then you will find a
static picture of it having the following −
A particular state which is running.
A particular number of passengers. which will change if the snap is taken in a different time

Here, we can imagine the snap of the running train is an object having the above values. And this is true for
any real-life simple or complex system.

In a nutshell, it can be said that object diagrams are used for

Making the prototype of a system.
Reverse engineering.
Modeling complex data structures.
Understanding the system from practical perspective.
Ex.No: 9 OBJECT FEATURES OF SQL UDTS AND SUB
TYPES TABLES USING UDTS, INHERITANCE, METHOD
Date : DEFINITION
 Ex.No: 9
DATE: OBJECT FEATURES OF SQL UDTS AND SUB
TYPES TABLES USING UDTS, INHERITANCE, METHOD
DEFINITION

AIM:
To define User Defined Types and to use them in the tables.

To demonstrate inheritance and method definition.

PROCEDURE:

STEP 1: Identify the requirement for user defined types

STEP 2: Define the user defined types
STEP 3: Create tables with columns that are of above user defined types
STEP 4: Insert values into the tables
STEP 5: Query the tables to fetch the data stored in the columns of user defined types
STEP 6: Create an object type and a subtype
STEP 7: Call the object type methods using subtype variables.

DESCRIPTION:
User Defined Types are “complex” types written in SQL and are whatever you create them to be. UDTs
can be used in the definition of database objects, as variables in Transact-SQL batches, in functions and
stored procedures, and as arguments in functions and stored procedures.

How to Create User-Defined Type (UDT)

We can create a user-defined type using CREATE DOMAIN and CREATE TYPE statements.

-- Define a type using CREATE DOMAIN

CREATE DOMAIN addr VARCHAR(90) NOT NULL DEFAULT 'N/A';

-- Define a type using CREATE TYPE

CREATE TYPE address AS (city VARCHAR(90), street VARCHAR(90));

There is no special syntax for creating a UDT column in a table. You can use the name of the UDT in a
column definition as though it were one of the intrinsic data types.

The following CREATE TABLE creates a table named location, with two columns named ship_address
and full_address which are defined as addr and address UDTs.

-- Use them in a table

CREATE TABLE location (ship_address addr, full_address address);

Create Scalar Type - Alias for Built-in Data Type:

CREATE DOMAIN allows you to create an alias for a built-in data type and specify the range, optional
DEFAULT, NOT NULL and CHECK constraint:
CREATE DOMAIN addr VARCHAR(90) NOT NULL DEFAULT 'N/A';

CREATE DOMAIN idx INT CHECK (VALUE > 100 AND VALUE < 999);

Create an Enumeration Type (Enum or Set of Values)

You can use both CREATE DOMAIN and CREATE TYPE to create an enumeration type that can only
accept a value from the specified list:

CREATE DOMAIN color VARCHAR(10) CHECK (VALUE IN ('red', 'green', 'blue'));

CREATE TYPE color2 AS ENUM ('red', 'green', 'blue');

Create a Composite Type

CREATE TYPE allows you to create a composite type containing multiple fields:

CREATE TYPE full_address AS

(
 city VARCHAR(90),

street VARCHAR(90)

);

CREATE TABLE shipping

name VARCHAR(50),

address full_address

);

INSERT INTO shipping VALUES ('John', ('Northampton', 'Tower St'));

-- or

INSERT INTO shipping VALUES ('Tom', ROW('Bracknell', 'Market St'));

-- Select full composite type

SELECT address FROM shipping;

-- Select each field separately

SELECT (address).city, (address).street FROM shipping;

Inheritance is the mechanism that connects subtypes in a hierarchy to their super-types.

Subtypes automatically inherit the attributes and methods of their parent type. Also, the inheritance link
remains alive. Subtypes automatically acquire any changes made to these attributes or methods in the
parent: any attributes or methods updated in a super-type are updated in subtypes as well.Asubtype can be
derived from a super-type either directly or indirectly through intervening levels of other subtypes. A
super-type can have multiple sibling subtypes, but a subtype can have at most one direct parent super-
type (single inheritance).
To derive a subtype from a super-type, define a specialized variant of the super-type that adds new
attributes and methods to the set inherited from the parent or redefine (override) the inherited methods.
For example, from a person_typ object type you might derive the specialized types student_typ and
employee_typ. Each of these subtypes is still a person_typ, but a special kind of person. What
distinguishes a subtype from its parent super-type is some change made to the attributes or methods
that the subtype received from its parent.

Unless a subtype redefines an inherited method, it always contains the same core set of attributes and
methods that are in the parent type, plus any attributes and methods that it adds. If a person_typ
object type has the three attributes idno, name, and phone and the method get_idno(), then any object
type that is derived from person_typ will have these same three attributes and a method get_idno(). If
the definition of person_typ changes, so do the definitions of any subtypes.

Subtypes are created using the keyword UNDER as follows:

CREATE TYPE student_typ UNDER person_typ

You can specialize the attributes or methods of a subtype in these ways:

Add new attributes that its parent supertype does not have. For example, you might specialize
student_typ as a special kind of person_typ by adding an attribute for major. A subtype cannot drop
or change the type of an attribute it inherited from its parent; it can only add new attributes.

Add entirely new methods that the parent does not have.

Change the implementation of some of the methods that a subtype inherits so that the subtype's
version executes different code from the parent's.

For example, a ellipse object might define a method calculate(). Two subtypes of ellipse_typ,
circle_typ and sphere_typ, might each implement this method in a different way.

RESULT:

Thus, to define User Defined Types and to use them in the tables.to demonstrate inheritance and
method definition has been executed successfully.
Ex.No: 10
Date
QUERYING THE OBJECT RELATIONAL DATABASE USING OBJECT QUERY
LANGUGE
AIM:
To query the data from the object relational database using OQL.

PROCEDURE:
STEP 1: Identify the objects to be queried to fetch the data.
STEP 2: Write the SELECT statement with the object name and the predicate
STEP 3: Identify the object collections required in the system
STEP 4: Create the object views or referenceable views
STEP 5: Create the persistent stored modules
STEP 6: Call these modules from OQL prompt
STEP 7: Create large objects and access them using OQL.

DESCRIPTION:
OQL is the way to access data in an object relational database. OQL is a powerful and easy-to-use SQL-like
query language with special features dealing with complex objects, values and methods. An OQL query is a
function that delivers an object whose type may be inferred from the operator contributing to the query
expression.

SELECT, FROM, WHERE

SELECT <list of values>

FROM <list of collections and variable assignments>

WHERE <condition>

The SELECT clause extracts those elements of a collection meeting a specific condition. By using the
keyword DISTINCT duplicated elements in the resulting collection get eliminated. Collections in FROM can
be either extents (persistent names - sets) or expressions that evaluate to a collection (a set). Strings are
enclosed in double-quotes in OQL. We can rename a field by if we prefix the path with the desired name and
a colon.

Example Query 1
Give the names of people who are older than 26 years old:

SELECT SName: p.name FROM p in People WHERE p.age > 26

Dot Notation & Path Expressions

We use the dot notation and path expressions to access components of complex values.

Let variables t and ta range over objects in extents (persistent names) of Tutors and TAs (i.e., range over
objects in sets Tutors and TAs).

ta.salary -> real

t.students -> set of tuples of type tuple(name: string, fee: real) representing students

t.salary -> real

Cascade of dots can be used if all names represent objects and not a collection.

Example of Illegal Use of Dot

t.students.name, where ta is a TA object.

This is illegal, because ta.students is a set of objects, not a single object.

Example Query 2

Find the names of the students of all tutors:

SELECT s.name FROM Tutors t, t.students s

Here we notice that the variable t that binds to the first collection of FROM is used to help us define the
second collection s. Because students is a collection, we use it in the FROM list, like t.students above, if we
want to access attributes of students.
Sub queries in FROM Clause
Example Query 3

Give the names of the Tutors which have a salary greater than $300 and have a student paying more than
$30:

SELECT t.name FROM ( SELECT t FROM Tutors t WHERE t.salary > 300 ) r, r.students s

WHERE s.fee > 30

Sub queries in WHERE Clause

Example Query 4

Give the names of people who aren't TAs:

SELECT p.name FROM p in People WHERE not ( p.name in SELECT t.name FROM t in TAs )

Set Operations and Aggregation

The standard O2C operators for sets are + (union), * (intersection), and - (difference). In OQL, the operators
are written as UNION, INTERSECT and EXCEPT , respectively.

Example Query 5

Give the names of TAs with the highest salary:

SELECT t.name FROM t in TAs WHERE t.salary = max ( select ta.salary from ta in TAs )

GROUP BY

The GROUP BY operator creates a set of tuples with two fields. The first has the type of the specified
GROUP BY attribute. The second field is the set of tuples that match that attribute. By default, the second
field is called PARTITION.

Example Query 6
Give the names of the students and the average fee they pay their Tuto

SELECT sname, avgFee: AVG(SELECT p.s.fee FROM partition p) FROM t in Tutors, t.students s GROUP BY

sname: s.name rs:

RESULT:
Thus, to query the data from the object relational database using OQL.