LIST OF EXPERIMENTS

Database Development Life cycle:

1
• Problem definition and Requirement analysis Scope and Constraints
Database design using Conceptual modeling (ER-EER) – top-down approach
2
• 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

Querying/Managing the database using SQL Programming

5 • 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

INDEX

ExNo Title Page Mark Sign

1 Database Development Life cycle

2 ER-EER Mapping

3 SQL Data definition with constraints, Views

4 Query the database using SQL Manipulation

Querying/Managing the database using SQL

5
Programming

6 Database design using Normalization

Develop database applications using IDE/RAD tools

7
(Eg., NetBeans,VisualStudio)

8 EER-to-ODB mapping / UML class diagrams

9 SQL UDT and SubTypes

10 Object Relational Database using Query Language

1
Ex.No:1
Database Development Life cycle
Date:

AIM:
To understand the database development life cycle.

The different phases of database development life cycle (DDLC) in the Database
Management System (DBMS) are explained below −
1. Requirement analysis.
2. Database design.
3. Evaluation and selection.
4. Logical database design.
5. Physical database design.
6. Implementation.
7. Data loading.
8. Testing and performance tuning.
9. Operation.
10. Maintenance.
Requirement Analysis
The most important step in implementing a database system is to find out what is needed i.e what
type of a database is required for the business organization, daily volume of data, how much data
needs to be stored in the master files etc.
In order to collect all this information, a database analyst spends a lot of time within the business
organization talking to people, end users and getting acquainted with the day-to-day process.
Database Design
In this phase the database designers will make a decision on the database model that perfectly
suits the organization’s requirement. The database designers will study the documents prepared
by the analysis in the requirement analysis stage and then start development of a system model
that fulfils the needs.
Evaluation and selection
In this phase, we evaluate the diverse database management systems and choose the one which
perfectly suits the requirements of the organization.
In order to identify the best performing database, end users should be involved.
Logical database design
Once the evaluation and selection phase is completed successfully, the next step is logical
database design.
2

This design is translated into internal model which includes mapping of all objects i.e design of
tables, indexes, views, transaction, access privileges etc.,
Physical Database Design
This phase selects and characterizes the data storage and data access of the database.
The data storage depends on the type of devices supported by the hardware, the data access
methods.
Physical design is very vital because of bad design which results in poor performance.
Implementation
Database implementation needs the formation of special storage related constructs.
These constructs consist of storage groups, table spaces, data files, tables etc.
Data Loading
Once the database has been created, the data must be loaded into the database.
The data required to be converted, if the loaded date is in a different format.
Operations
In this phase, the database is accessed by the end users and application programs.
This stage includes adding of new data, modifying existing data and deletion of absolute data.
This phase provides useful information and helps management to make a business decision.
Maintenance
It is one of the ongoing phases in DDLC.
The major tasks included are database backup and recovery, access management, hardware
maintenance etc.
3

Example:
Database Design Development Lifecycle for Railway Reservation System

RESULT
Thus database development lifecycle stages are studied and designed a DBDL for railway
reservation.
4

Ex.No:2
ER-EER Mapping
Date:
AIM:
To learn database design using conceptual modelling, Normalization

Consider following databases and draw ER diagram and convert entities and relationships
to relation table for a given scenario.
1. COLLEGE DATABASE:
STUDENT (USN, SName, Address, Phone, Gender)
SEMSEC (SSID, Sem, Sec)
CLASS (USN, SSID)
SUBJECT (Subcode, Title, Sem, Credits)
IAMARKS (USN, Subcode, SSID, Test1, Test2, Test3, FinalIA)

Mapping entities and relationships to relation table (Schema Diagram)

5

2. COMPANY DATABASE:
EMPLOYEE (SSN, Name, Address, Sex, Salary, SuperSSN, DNo)
DEPARTMENT (DNo, DName, MgrSSN, MgrStartDate)
DLOCATION (DNo,DLoc)
PROJECT (PNo, PName, PLocation, DNo)
WORKS_ON (SSN, PNo, Hours)
6

RESULT:
Thus the ER-EER mapping was designed and output was verified.
7

Ex.No:3
Implement the database using SQL Data definition
Date: with constraints, Views

AIM
To Implement the database using SQL Data definition with constraints, Views

Creation, Altering and Dropping of Tables and Inserting Rows into a Table (Use Constraints
While
Creating Tables). Examples Using Select Command.
Data Types :- Oracle supports following types of data types.
CHAR (SIZE) :- The CHAR data type stores fixed-length character strings. When you create a
table with a CHAR column, you must specify a string length (in bytes or characters) between 1
and 2000 bytes for the CHAR column width. The default is 1 byte.
VARCHAR2 (SIZE) :- The VARCHAR2 data type stores variable-length character strings.
When you create a table with a VARCHAR2 column, you specify a maximum string length (in
bytes or characters) between 1 and 4000 bytes for the VARCHAR2 column.
VARCHAR (SIZE) :- The VARCHAR data type is synonymous with the VARCHAR2 data
type.
NUMBER (L) :- The NUMBER datatype stores fixed and floating-point numbers. Numbers of
virtually any magnitude can be stored and are guaranteed portable among different systems
operating Oracle Database, up to 38 digits of precision.
NUMBER (L, D) :- Numeric data with total number of digits L and number of digits D after
decimal point.
DATE :- The DATE datatype stores point-in-time values (dates and times) in a table. The
DATE datatype stores the year (including the century), the month, the day, the hours, the
minutes, and the seconds (after midnight).
LONG :- Character data of variable length which stores upto 2 Gigabytes of data. (A bigger
version the VARCHAR2 datatype).
INTEGER :- Integer type of Data. It is actually a synonym for NUMBER(38)
FLOAT :- FLOAT is a 32-bit, single-precision floating-point number datatype. Each FLOAT
8

value requires 5 bytes, including a length byte.

DOUBLE :- DOUBLE is a 64-bit, double-precision floating-point number datatype. Each
DOUBLE value requires 9 bytes, including a length byte.
Data Definition Language (DDL) Commands: -
Create Table
CREATE TABLE DEPT(DEPTNO INTEGER, DNAME VARCHAR(10),LOC
VARCHAR(4), PRIMARY KEY(DEPTNO));
1. Rename the table dept as department
SQL> ALTER TABLE DEPT RENAME TO DEPARTMENT;
Table altered.
2. Add a new column PINCODE with not null constraints to the existing table DEPT
ALTER TABLE DEPARTMENT ADD(PINCODE NUMBER(6) NOT NULL);
Table altered.
DESC DEPARTMENT;
Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DNAME VARCHAR2(10)
LOC VARCHAR2(4)
PINCODE NOT NULL NUMBER(6)
3.All constraints and views that reference the column are dropped automatically, along
with the column.
ALTER TABLE DEPARTMENT DROP column LOC CASCADE
CONSTRAINTS;
Table altered.
descdept
Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DNAME VARCHAR2(10)
9

PINCODE NOT NULL NUMBER(6)

4. Rename the column DNAME to DEPT_NAME in dept table
ALTER TABLE DEPT RENAME COLUMN DNAME TO DEPT_NAME ;
Table altered.
DESC DEPARTMENT;
Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DEPT_NAME VARCHAR2(10)
LOC VARCHAR2(4)
PINCODE NOT NULL NUMBER(6)
5. Change the datatype of colunmloc as CHAR with size 10
ALTER TABLE DEPARTMENT MODIFY LOC CHAR(10) ;
Table altered.
DESC DEPARTMENT;
Name Null? Type

DEPTNO NOT NULL NUMBER(38)

DEPT_NAME VARCHAR2(10)
LOC CHAR(10)
PINCODE NOT NULL NUMBER(6)
6. Delete table
DROP TABLE DEPARTMENT;
Table dropped.

RESULT:
The DDL commands have been executed successfully
10

Ex.No:4
Date: Query the database using SQL Manipulation

AIM
To Query the database using SQL Manipulation

DML commands are the most frequently used SQL commands and is used to
query and manipulate the existing database objects. Some of the commands are
1. INSERT
This is used to add one or more rows to a table. The values are separated
by commas and the data types char and date are enclosed in apostrophes.
The values must be entered in the same order as they are defined.
2. SELECT
It is used to retrieve information from the table.it is generally referred to
as querying the table. We can either display all columns in a table or only
specify column from the table.
3. UPDATE
It is used to alter the column values in a table. A single column may be
updated or more than one column could be updated.
4. DELETE
After inserting row in a table we can also delete them if required. The
delete command consists of a from clause followed by an optional where
clause
PROCEDURE
INSERT COMMAND
(a) Inserting a single row into a table:
Syntax:
insert into <table name> values (<expression1>,<expression2>)
Example:
11

SQL>INSERT INTO EMPLOYEE VALUES(101,'MANU','LECTURER',15000);

(b) Inserting more than one record using a single insert commands:
Syntax:
insert into <table name> values (&col1, &col2, ….)
Example:
SQL> INSERT INTO EMPLOYEE
VALUES(&EMPNO,'&ENAME','&DESIGNATIN','&SALARY');
( c) Skipping the fields while inserting:
Insert into <tablename>(<column name1>,<column name3>)>values
(<expression1>,<expression3>);
Other way is to give null while passing the values.
SELECT COMMAND
(a) view all rows and all columns
Syntax:
Select * from tablename;
Example:
Select * from Employee;
(b)Selected Columns And All Rows
Syntax:
Select <column1>,<column2> from tablename;
Example:
Select empno, empname from Employee;
(c)Selected Columns And selected Rows
Syntax:
SELECt<column1>, <column2> FROM <tablename> WHERE <condition> ;

Example:
Select empno, empname from Employee where designation=’lecturer’;
(c)Eliminating duplicate rows
Syntax:
12

SELECT DISTINCT <column1>, <column2> FROM <tablename>

Example:
Select distinct empname from Employee;
UPDATE COMMAND
(b)updating all rows
Syntax:
update tablename set
columnname1>=<exprssion1>,<columnname2>=<exprssion2>;
Example:
Update Employee set Designation = ‘lecturer’;
(b)updating records conditionally
Syntax:
update tablename set field=values where condition;
Example:
Update Employeeemp set sal = 10000 where empno=135;
DELETE COMMAND
(b)Removal of all rows
Syntax:
Delete from <table name> ;
Example:
Delete from emp;
(b)removal of specific rows
Syntax:
Delete from <table name> where <condition>;
Example:
delete from emp where empno=135;

RESULT:
The DML commands are executed successfully.
13

Ex.No:5
Date: Querying/Managing the database using SQL
Programming

AIM:
To implement Querying/Managing the database using SQL Programming
- Stored Procedures/Functions
- Constraints and security using Triggers
FUNCTION:
A function is a subprogram that computes a value.
syntax
Create or replace function<function_name>[argument]
Return datatype is
(local declaration)
begin
(executable statements)
[Exception]
(exception handlers)
End
PROCEDURE:
create [or replace] procedure procedurename
[parameter[in/out/in/in out] datatype
[:=/default expression]
[(parameter)]
is/as
declaration
begin
pl/sql codes
[exception]
end
PROGRAM
https://gptcthirurangadi.in
14

SQL> create or replace function fact(a number) return number as

i number;
f number;
begin
f:=1;
i:=1;
while (i<=a)
loop
f:=f*i;
i:=i+1;
end loop;
return f;
end fact; /Function created.
SQL>begin
2 dbms_output.put_line('the factorial='||fact(&a));
3* end;
OUTPUT
SQL> /
Enter value for a: 4
old 2: dbms_output.put_line('the factorial='||fact(&a))
new 2: dbms_output.put_line('the factorial='||fact(4));
the factorial=24
PL/SQL procedure successfully completed.

RESULT:
Thus the functions and stored procedures are executed in SQL.
15

ExNo:06

Date: TRIGGER

AIM
Create a Trigger for EMP table it will update another table SALARY while inserting values

OBJECTIVES
To develop and execute a Trigger for Before and After update/Delete/Insert
operations on a table

PROCEDURE
step 1: start
step 2: initialize the trigger with specific table id.
step 3:specify the operations (update, delete, insert) for which the trigger has to
be executed.
step 4: execute the trigger procedure for both before and after sequences
step 5: carryout the operation on the table to check for trigger execution.
step 6: stop

PROGRAM
sql> create table emp(iname varchar2(10),iid number(5),salary number(10));
table created.
sql> create table sal(iname varchar2(10),totalemp number(5),totalsal
number(10));
table created.
sql> create or replace trigger emptrigr after insert on emp
for each row
declare
a varchar2(10);
begin
a:=:new.iname;
update sal set
16

totalsal=totalsal+:new.salary,totalemp=totalemp+1 where
iname=a;
https://gptcthirurangadi.in
DBMS LAB MANUAL Page 44
end;
/
trigger created.
sql> insert into emp values('vec',100,1000);
1 row created.
sql> insert into sal values('vec',0,0);
1 row created.
sql> insert into sal values('srm',0,0);
1 row created.
sql> select * from sal;
inametotalemptotalsal

vec 1 1000
srm 0 0
sql> insert into emp values('srm',200,3000);
1 row created.
sql> select * from sal;
inametotalemptotalsal

Vec 1 1000
srm 1 3000
sql> insert into emp values('vec',100,5000);
1 row created.
sql> select * from sal;
inametotalemptotalsal
17

vec 2 6000
srm 1 3000
sql> insert into emp values('vec',100,2000);
1 row created.
sql> select * from sal;
inametotalemptotalsal

vec 3 8000
srm 1 3000
sql> insert into emp values('srm',200,8000);
https://gptcthirurangadi.in
1 row created.
sql> select * from sal;
inametotalemptotalsal

Vec 3 8000
Srm 2 11000

RESULT:
The trigger procedure has been executed successfully for both before and after sequences.
18

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

AIM:
To Creating a Database Driven Application With NetBeans IDE PHP Editor.

1. Creating a Database Driven Application With PHP - Main page

1. Creating the Database
a. Creating a MySQL Database
b. Creating Oracle Database Tables
c. Designing the Application. Reading from the Database
2. + Creating a New Application User +
3. Optimizing the Code
4. Adding Security. Implementing Application User Logon
5. Adding a New Wish to the Database
6. Updating and Deleting Entries in the Database
7. Making the Application Look Better Using the CSS Technology
8. Deploying the Application on a Remote Web Server

Registering a MySQL Server

If you do not have a MySQL database server registered in the IDE, or you want general
information about using MySQL with NetBeans IDE, see Connecting to a MySQL Database.

Creating the Database User

Before you create a database you need to create its User who will be granted the right to perform
any operations on the database. Creating a database User involves:

• Connecting to the MySQL server as the root user.

• Connecting to the MySQL system database as a root user. This step is necessary to enable
running an SQL command for creating a user because you cannot run an SQL command
without being connected to any database.
• Executing a user creation MySQL statement.
1. Start the IDE, switch to the Services window (Ctrl-5), and expand the Databases
node.
2. To connect to the MySQL database server, navigate to the MySQL Server node
and from the context menu choose Connect.
19

1. The NetBeans IDE connects to the MySQL server, checks for the databases available through
the server, detects the system mysql database, and adds the corresponding new node mysql to
the Databases tree.

2. To execute an SQL command, you need to be connected to a database. Because only the
MySQL system is available, you need to connect to it. To connect to the system database,
navigate to the mysql node and from the context menu choose Connect. If a connection does
not already exist, the New Database Connection dialog box appears. The User Name field is by
default filled in with root. In the Password field, enter the root user’s password.

If you have connected to the mysql database before, this dialog does not appear. Instead, the new
connection node simply appears in the tree.
20

The New Database Connection dialog box shows the message "Connection established." Click
OK. A new node named jdbc:mysql://localhost:3306/mysql is added to the Databases tree.

1. Navigate to the jdbc:mysql://localhost:3306/mysql node and from the context menu

choose Execute Command.

An SQL Command window opens. In the SQL Command window, use syntax similar to the
following statement:

CREATEUSER'phpuser'@'localhost'IDENTIFIEDBY'phpuserpw'
21

From the context menu, choose Run Statement. If the command is executed successfully, the
Status bar shows the message: "SQL Statement(s) executed successfully". If another message is
displayed, check the syntax and follow the message hints.

Creating the Wishlist Database

To create the database:

1. Navigate to the MySQL Server at localhost:3306 node and from the context menu choose
Create Database. The Create MySQL Database dialog box appears. Fill in the fields:
• In the Database Name field, enter wishlist.
• Switch on the Grant full access to user checkbox and from the drop down list
select phpuser@localhost Click OK.

The "Grant full access to user" function does not always work. If it does not work, connect to the
database as the root user and send the SQL query [examplecode]# GRANT ALL ON wishlist.*
TO phpuser@localhost #.

A connection to the database appears in the tree. However the connection is for the root user. You
need a connection for the` phpuser` user.

Establishing Connection to the Wishlist Database

At the end of the previous section, you created the wishlist database with a connection to
the root user. Now you create a new connection for the phpuser user.

1. In the Services window, right-click the Databases node and select New Connection. The
New Connection Wizard opens.

1. In the New Connection Wizard’s Locate Driver panel, select the MySQL (Connector/J
Driver). Click Next. The Customize Connection panel opens.
22

1. In the Database field, type wishlist.

2. In the User Name and Password edit boxes, enter the name and the password specified in
section Creating the Owner (User) of the Database (in our
example phpuser and phpuserpw respectively). Tick Remember Password. Click Test
Connection, and if the connection succeeds, click OK.
23

The corresponding new connection node is displayed in the Databases tree. Now you can delete
the root user’s connection to the wishlist database. Click the jdbc:mysql://localhost:3306/wishlist
[root on Default schema] connection and choose Delete.

Designing the Structure of the Wishlist Database

To arrange and store all the necessary data you need two tables:

• A wishers table for storing names and passwords of registered users

• A wishes table for storing descriptions of wishes

The wishers table contains three fields:

1. id - the unique ID of a wisher. This field is used as the Primary Key

2. name
3. password

The wishes table contains four fields:

1. id - the unique ID of a wish. The field is used as the Primary Key

2. wisher_id - the ID of the wisher to whom the wish belongs. The field is used as the
Foreign Key.
3. description
4. due_date - the date by when the wish is requested

The tables are related through the wisher’s ID. All the fields are mandatory except due_date in
wishes.

Creating the Tables

1. To connect to the database, on the jdbc:mysql://localhost:3306/wishlist connection, click

the right mouse button and choose Connect from the context menu.
If the menu item is disabled, you are already connected. Continue with step 2.
24

1. From the same context menu, choose Execute Command. An empty SQL Command
window opens.
2. To create the wishers table,
3. Type the following SQL query (note that you need to explicitly set character sets to UTF-8
for internationalization):
CREATETABLE wishers(idINTNOTNULL AUTO_INCREMENT PRIMARY KEY,
nameCHAR(50) CHARACTERSET utf8 COLLATE utf8_general_ci
NOTNULLUNIQUE,passwordCHAR(50) CHARACTERSET utf8 COLLATE utf8_general_ci
NOTNULL)

You can get a unique auto generated number from MySQL by specifying the
AUTO_INCREMENT property for a field. MySQL will generate a unique number by
incrementing the last number of the table and will automatically add to the auto incremented
field. In our example the ID field is auto incremented.
1. Click the right mouse button on the query and then choose Run Statement from the
context menu.
The default storage engine for MySQL is MyISAM, which does not support foreign keys. If you
want to use foreign keys, consider using InnoDB as the storage engine.
1. To create the wishes table:
a. Type the following SQL query:
CREATETABLE wishes(idINTNOTNULL AUTO_INCREMENT PRIMARY
KEY,wisher_idINTNOTNULL,descriptionCHAR(255) CHARACTERSET utf8 COLLATE
utf8_general_ci NOTNULL,due_dateDATE,FOREIGNKEY (wisher_id) REFERENCES
wishers(id))

a. Click the right mouse button on the query and then choose Run Statement from the
context menu.
1. To verify that the new tables are added to the database, switch to the Services
window and then navigate to the jdbc:mysql://localhost:3306/wishlist connection
node.
2. Click the right mouse button and choose Refresh. The nodes wishers and wishes
appear in the tree.

Note: You can download a set of SQL commands for creating the MySQL wishlist database here.

Entering the Test Data

To test your application you will need some data in the database. The example below shows how
to add two wishers and four wishes.

1. On the jdbc:mysql://localhost:3306/wishlist connection, click the right mouse button and

choose Execute Command. An empty SQL Command window opens.
2. To add a wisher, use syntax similar to the example below:
INSERTINTO wishers (name, password) VALUES ('Tom', 'tomcat');
25

Click the right mouse button on the query and from the context menu choose Run Statement.

The statement does not contain a value for the id field. The values are entered automatically
because the field type is specified as AUTO_INCREMENT .

Enter another test wisher:

INSERTINTO wishers (name, password) VALUES ('Jerry', 'jerrymouse');

1. To add the wishes, use syntax similar to the example below:

INSERTINTO wishes (wisher_id, description, due_date) VALUES (1, 'Sausage',
080401);INSERTINTO wishes (wisher_id, description) VALUES (1,
'Icecream');INSERTINTO wishes (wisher_id, description, due_date) VALUES (2, 'Cheese',
080501);INSERTINTO wishes (wisher_id, description)VALUES (2, 'Candle');

Select the queries, click the right mouse button on each query and from the context menu choose
Run Selection.

You can also execute the queries one after another as described in item 2.
1. To view the test data, click the right mouse button on the relevant table and from the
context menu choose View Data.

RESULT
Thus database driven application was successfully created using NetBeans IDE PHP
Editor.
26

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

AIM:
To design a database using UML class diagrams.

UML modelling is based on object-oriented programming principals. UML defines a standard set
of modelling diagrams for all stages of developing a software system. This information describes
the entity-relationship model of database design. Another model that you can use is Unified
Modelling Language (UML). The Object Management Group is a consortium that created the
UML standard. This topic provides a brief overview of UML. The basic difference between the
entity-relationship model and the UML model is that, instead of designing entities as this
information illustrates, you model objects. Conceptually, UML diagrams are like the blueprints
for the design of a software development project.

The Class Model

The class is the basic logical entity in the UML. It defines both the data and the behaviour of a
structural unit. A class is a template or model from which instances or objects are created at run
time. When we develop a logical model such as a structural hierarchy in UML we explicitly deal
with classes. When we work with dynamic diagrams, such as sequence diagrams and
collaborations, we work with objects or instances of classes and their inter-actions at run-time.
The principal of data hiding or encapsulation is based on localisation of effect. A class has
internal data elements that it is responsible for. Access to these data elements should be through
the class's exposed behaviour or interface. Adherence to this principal results in more
maintainable code.
27

The Class Model

Behaviour
Behaviour is captured in the class model using the operations that are defined for the class.
Operations may be externally visible (public), visible to children (protected) or hidden (private).
By combining hidden data with a publicly accessible interface and hidden or protected data
manipulation, a class designer can create highly maintainable structural units that support rather
than hinder change.

Relationships and Identity

Association is a relationship between 2 classes indicating that at least one side of the relationship
knows about and somehow uses or manipulates the other side. This relationship may by
functional (do something for me) or structural (be something for me). For this article it is the
structural relationship that is most interesting: for example an Address class may be associated
28

with a Person class. The mapping of this relationship into the relational data space requires some
care.

Class Relationships
Aggregation is a form of association that implies the collection of one class of objects within
another. Composition is a stronger form of aggregation that implies one object is actually
composed of others. Like the association relationship, this implies a complex class attribute that
requires careful consideration in the process of mapping to the relational domain. While a class
represents the template or model from which many object instances may be created, an object at
run time requires some means of identifying itself such that associated objects may act upon the
correct object instance. In a programming language like C++, object pointers may be passed
around and held to allow objects access to a unique object instance. Often though, an object will
be destroyed and require that it be re-created as it was during its last active instance. These objects
require a storage mechanism to save their internal state and associations into and to retrieve that
state as required.

Inheritance provides the class model with a means of factoring out common behaviour into
generalised classes that then act as the ancestors of many variations on a common theme.
Inheritance is a means of managing both re-use and complexity. As we will see, the relational
model has no direct counterpart of inheritance, which creates a dilemma for the data modeller
mapping an object model onto a relational framework. Navigation from one object at run time to
another is based on absolute references. One object has some form of link (a pointer or unique
object ID) with which to locate or re-create the required object.
29

Other Links

The Relational Model

The relational data model has been around for many years and has a proven track record of
providing performance and flexibility. It is essentially set based and has as its fundamental unit
the 'table', which is composed of a set of one or more 'columns', each of which contains a data
element.

Tables and Columns: a relational table is collection of one or more columns each of which has a
unique name within the table construct. Each column is defined to be of a certain basic data type,
such as a number, text or binary data. A table definition is a template from which table rows are
created, each row being an instance of a possible table instance. The relational model only offers a
public data access model. All data is equally exposed and open to any process to update, query or
manipulate it. Information hiding is unknown.

Behaviour
The behaviour associated with a table is usually based on the business or logical rules applied to
that entity. Constraints may be applied to columns in the form of uniqueness requirements,
relational integrity constraints to other tables/rows, allowable values and data types.
Triggers provide some additional behaviour that can be associated with an entity. Typically this is
used to enforce data integrity before or after updates, inserts and deletes. Database stored
procedures provide a means of extending database functionality through proprietary language
extensions used to construct functional units (scripts). These functional procedures do not map
directly to entities, nor have a logical relationship to them. Navigation through relational data sets
is based on row traversal and table joins. SQL is the primary language used to select rows and
locate instances from a table set.

Relationships and Identity

The primary key of a table provides the unique identifying value for a particular row. There are
two kinds of primary key that we are interested in: firstly the meaningful key, made up of data
columns which have a meaning within the business domain, and second the abstract unique
identifier, such as a counter value, which have no business meaning but uniquely identify a row.
We will discuss this and the implications of meaningful keys later. A table may contain columns
that map to the primary key of another table. This relationship between tables defines a foreign
key and implies a structural relationship or association between the two tables.

RESULT:
Thus database design using UML diagrams was successfully created.
30

Ex.No:9
Date: Object features of SQL-UDTs and sub-types, Tables using UDTs,
Inheritance, Methoddefinition

AIM:
To write SQL-UDT, Inheritance and Method definition.

User-defined types

A user-defined data type (UDT) is a data type that derived from an existing data type. You can
use UDTs to extend the built-in types already available and create your own customized data
types.

There are six user-defined types:

• Distinct type
• Structured type
• Reference type
• Array type
• Row type
• Cursor type

Distinct type
A distinct type is a user-defined data type that shares its internal representation with an existing
built-in data type (its "source" type). Distinct types include qualified identifiers. If the schema
name is not used to qualify the distinct type name when used in other than the CREATE TYPE
(Distinct), DROP, or COMMENT statements, the SQL path is searched in sequence for the first
schema with a distinct type that matches. Distinct types that are sourced on LOB types are subject
to the same restrictions as their source type. A distinct type is defined to use either strong typing
or weak typing rules. Strong typing rules are the default.

Strongly typed distinct type

A strongly typed distinct type is considered to be a separate and incompatible type for most
operations. For example, you want to define a picture type, a text type, and an audio type. Each of
these types has different semantics, but each uses the built-in data type BLOB for their internal
representation.

The following example illustrates the creation of a distinct type named AUDIO:
CREATE TYPE AUDIO AS BLOB (1M)

Although AUDIO has the same representation as the built-in data type BLOB, it is considered to
be a separate type; this consideration allows the creation of functions that are written specifically
for AUDIO, and assures that these functions are not applied to values of any other data type (for
example pictures or text).
Strongly typed distinct types support strong typing by ensuring that only those functions and
operators that are explicitly defined on the distinct type can be applied to its instances. For this
reason, a strongly typed distinct type does not automatically acquire the functions and operators
31

of its source type, because these functions and operators might not be meaningful. For example, a
LENGTH function could be defined to support a parameter with the data type AUDIO that returns
length of the object in seconds instead of bytes.

Weakly typed distinct type

A weakly typed distinct type is considered to be the same as its source type for all operations,
except when the weakly typed distinct type applies constraints on values during assignments or
casts. This consideration also applies to function resolution.
The following example illustrates the creation of a distinct type named POSITIVEINTEGER:
CREATE TYPE POSITIVEINTEGER AS INTEGER
WITH WEAK TYPE RULES CHECK (VALUE>=0)

Weak typing means that except for accepting only positive integer values, POSITIVEINTEGER
operates in the same way as its underlying data type of INTEGER.
A weakly typed distinct type can be used as an alternative method of referring to a built-in data
type within application code. The ability to define constraints on the values that are associated
with the distinct type provides a method for checking values during assignments and casts.

Using distinct types can provide benefits in the following categories:

Extensibility
By defining new data types, you increase the set of data types available to you to support your
applications.
Flexibility
You can specify any semantics and behavior for your new data type by using user-defined
functions (UDFs) to augment the diversity of the data types available in the system.
Consistent and inherited behaviour.
Strong typing guarantees that only functions defined on your distinct type can be applied to
instances of the distinct type. Weak typing ensures that the distinct type behaves the same way as
its underlying data type and so can use all the same functions and methods available to that
underlying type.
Encapsulation
Using a weakly typed distinct type makes it possible to define data type constraints in one
location for all usages within application code for that distinct type.
Performance
Distinct types are highly integrated into the database manager. Because distinct types are
internally represented the same way as built-in data types, they share the same efficient code that
is used to implement components such as built-in functions, comparison operators, and indexes
for built-in data types.
Not all built-in data types can be used to define distinct types. The source data type cannot be
XML, array, row, or cursor. For more information, see CREATE TYPE (distinct) statement.

Structured type
A structured type is a user-defined data type that has a structure that is defined in the database. It
contains a sequence of named attributes, each of which has a data type. A structured type also
includes a set of method specifications.
32

A structured type can be used as the type of a table, view, or column. When used as a type for a
table or view, that table or view is known as a typed table or typed view. For typed tables and
typed views, the names and data types of the attributes of the structured type become the names
and data types of the columns of this typed table or typed view. Rows of the typed table or typed
view can be thought of as a representation of instances of the structured type. When used as a data
type for a column, the column contains values of that structured type (or values of any of the
subtypes for that type, as defined later in this section). Methods are used to retrieve or manipulate
attributes of a structured column object.

A supertype is a structured type for which other structured types, called subtypes, are defined. A
subtype inherits all the attributes and methods of its supertype and can have additional attributes
and methods defined. The set of structured types that is related to a common supertype is called a
type hierarchy and the type that does not have any supertype is called the root type of the type
hierarchy.

The term subtype applies to a user-defined structured type and all user-defined structured types
that are below it in the type hierarchy. Therefore, a subtype of a structured type T is T and all
structured types below T in the hierarchy. A proper subtype of a structured type T is a structured
type below T in the type hierarchy.

There are restrictions on having recursive type definitions in a type hierarchy. For this reason, it is
necessary to develop a shorthand way of referring to the specific type of recursive definitions that
are allowed. The following definitions are used:

Directly uses: A type A is said to directly use another type B, if and only if one of the following
statements is true:
Type A has an attribute of type B.
Type B is a subtype of Aor a supertype of A.
Indirectly uses: A type A is said to indirectly use a type B, if one of the following statements is
true:
Type A directly uses type B.
Type A directly uses some type Cand type C indirectly uses type B.

A type cannot be defined so that one of its attribute types directly or indirectly uses itself. If it is
necessary to have such a configuration, consider using a reference as the attribute. For example,
with structured type attributes, there cannot be an instance of "employee" with an attribute of
"manager" when "manager" is of type "employee". There can, however, be an attribute of
"manager" with a type of REF(employee).

A type cannot be dropped if certain other objects use the type, either directly or indirectly. For
example, a type cannot be dropped if a table or view column makes direct or indirect use of the
type.
33

Reference type
A reference type is a companion type to a structured type. Similar to a distinct type, a reference
type is a scalar type that shares a common representation with one of the built-in data types. This
same representation is shared for all types in the type hierarchy. The reference type representation
is defined when the root type of a type hierarchy is created. When using a reference type, a
structured type is specified as a parameter of the type. This parameter is called the target type of
the reference.

The target of a reference is always a row in a typed table or a typed view. When a reference type
is used, it can have a scope defined. The scope identifies a table (called the target table) or view
(called the target view) that contains the target row of a reference value. The target table or view
must have the same type as the target type of the reference type. An instance of a scoped
reference type uniquely identifies a row in a typed table or typed view, called the target row.

Array type
A user-defined array type is a data type that is defined as an array with elements of another data
type. Every ordinary array type has an index with the data type of INTEGER and has a defined
maximum cardinality. Every associative array has an index with the data type of INTEGER or
VARCHAR and does not have a defined maximum cardinality.

Row type
A row type is a data type that is defined as an ordered sequence of named fields, each with an
associated data type, which effectively represents a row. A row type can be used as the data type
for variables and parameters in SQL PL to provide simple manipulation of a row of data.

Cursor type
A user-defined cursor type is a user-defined data type defined with the keyword CURSOR and
optionally with an associated row type. A user-defined cursor type with an associated row type is
a strongly typed cursor type; otherwise, it is a weakly typed cursor type. A value of a user-defined
cursor type represents a reference to an underlying cursor.

Creating Tables with UDTs

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 SQL Server data types. The
following CREATE TABLE Transact-SQL statement creates a table named Points, with a column
named ID, which is defined as an int identity column and the primary key for the table. The
second column is named PointValue, with a data type of Point. The schema name used in this
example is dbo. Note that you must have the necessary permissions to specify a schema name. If
you omit the schema name, the default schema for the database user is used.

CREATE TABLE dbo.Points

(ID int IDENTITY(1,1) PRIMARY KEY, PointValue Point)
Creating Indexes on UDT Columns
There are two options for indexing a UDT column:
34

Index the full value. In this case, if the UDT is binary ordered, you can create an index over the
entire UDT column by using the CREATE INDEX Transact-SQL statement.

Index UDT expressions. You can create indexes on persisted computed columns over UDT
expressions. The UDT expression can be a field, method, or property of a UDT. The expression
must be deterministic and must not perform data access.

UDT Inheritance

Once you have a single data type created, it is possible to set up UDT inheritance where data
types extend to other data types, to add additional members, or override default values. For
example, you can create a new data type and using the inheritance feature it will inherit all Tags
from the parent data type including the parameters. Then you can add additional Tags and/or
override any settings in your new data type. UDT Inheritance is a way to extend to a class of data
types to add more functionality to that class.

For example, you may have a simple motor and a complex motor. The complex motor can inherit
from the simple motor, which means all simple motor values will be in the complex motor and
you can add more.

To Inherit Property Values from an Existing UDT

Let's use our data type Motor from the previous sections to create another data type. We'll set the
parent to Motor so our new data type automatically inherits all the properties of Motor.

In the Tag Browser, click on the UDT Definitions tab and click the Add icon to create a New
Data Type.
The Tag Editor window will open

In the Name field, enter name for your new UDT (i.e. Complex Motor). Under Parent Data Type
property go to the dropdown and select the Motor data type and click Apply. Now your new
Complex Motor UDT is inheriting from all the properties of the parent Motor UDT: Amps, HI SP
and HOA.
35

With the Tag Editor still open, let's add an OPC Tag to the Complex Motor UDT. Click on the
Add Tag and select OPC Tag.

Enter the following properties for your new Tag and click Apply. You will see the new Tag was
added to the Complex Motor UDT.
Name: Temp
Value Source: OPC
Data Type: Integer
OPC Server: Click on the binding () icon and select Ignition OPC UA Server
36

OPC Item Path: Browse the OPC and find the Tag you want to use. This example uses a
Temperature Tag from a Sensor in the Dairy program.

In the image below the Temp Tag is pointing to a specific address in the PLC. Because we're
creating a new Tag in our UDT, we don't want to point to one specific set of 'Temp' Tags. We
want each instance of the Complex Motor UDT to reference a different set of 'Temp' Tags. To do
that, we need to add a parameter to the Complex Motor data type that we will call
'SensorNumber'.

With the Complex Motor UDT Tag Editor open, let's create a new UDT parameter. Right click
the Edit icon next to the Parameters property. The Parameters pane will open.
37

Click the Add icon and add the new parameter, 'SensorNumber'

With the Tag Editor still open, select the "Temp' Tag. In the OPC Item Path field, click the
binding icon, select Edit, and the Temp > OPC Item Path window will open. Place your cursor at
the end of 'Sensor1', delete the '1', add a space, and enter '{SensorNumber}'. Don't forget the
curly braces. Click Apply to save your updates and go back to the previous window.

Starting in version 8.1.17, you may also edit tag paths inline in the Tag Editor rather than opening
the OPC Item Path window. For this example, you would highlight '1' in the OPC Item Path and
replace it with the new parameter, '{SensorNumber}'
38

In the Tag Editor window, click OK to save your UDT. In the Tag Browser, the new data type is
now visible in the , "Complex Motor." You can see all the inherited Tags from the Motor data
type and the newly added Temp Tag.

RESULT

Thus Object features of SQL-UDTs and sub-types, Tables using UDTs, Inheritance,
Method definition successfully implemented.
39

Ex.No:10
Date: Querying the Object-relational database using
Object Query language

AIM:
To write query for object relational database using object query language.

Object Query Language:

Query Object Language (OQL) is a version of the Structured Query Language (SQL) that
has been designed for use in Network Manager. The components create and interact with their
databases using OQL.

Use OQL to create new databases or insert data into existing databases (to configure the
operation of Network Manager components) by amending the component schema files. You can
also issue OQL statements using the OQL Service Provider, for example, to create or modify
databases, insert data into databases and retrieve data.

Database and table creation

You can create databases and tables with the create command.

You can issue the create command to create a database.

create database database_name ;

create table database_name.table_name

column_name [ constraints ] [ default default ] ,

[ column_name [ constraints ] [ default default ] , ]

[ additional_columns ]

[ unique ( column_name ) , ]

[ counter ( column_name ) , ]

[ timestamp ( column_name ) ]

);
40

Examples of database and table creation

create database staff; // creates the staff database

The following insert defines the managers table.

create table staff.managers

(
EmployeeIDint NOT NULL PRIMARY KEY,
Name text NOT NULL,
Department text default "Sales",
Gender text,
Age int,
unique ( EmployeeID ) // indicates that the data in the
// EmployeeID column must be unique.
);

For the managers table:

The EmployeeID and Name columns cannot be NULL.

The EmployeeID column is the primary key and must be unique.
If no value is inserted into the Department column for a given record it takes the value "Sales".

Example 2

The following insert creates the staff.employees table.

create table staff.employees

(
EmployeeIDint NOT NULL PRIMARY KEY,
Name text NOT NULL,
Skills list type text,
Gender text,
Age int // There is no comma here because this
// is the last entry.
);

For the staff.employees table:

The EmployeeID and Name columns cannot be NULL.

The Skills column is a list of text strings.
41

Example 3
The following insert creates the staff.contractors table.

create table staff.contractors

(
EmployeeIDint NOT NULL PRIMARY KEY,
Name text NOT NULL,
Gender text,
Age int,
ExtraInfo object type vblist,
volatile
);

For the staff.contractors table:

The ExtraInfo column contains a list of varbinds.

Selecting data from a table

QSyntax

The following syntax shows how to use the select keyword to retrieve data from a table.

select comma_separated_column_list_or_wildcard
from database_name.table_name
[ where conditional_test ]
[ order by field_name[asc|desc] ];

The * symbol can be used as a wildcard in a select statement to return all the columns of the table.
Alternatively a comma-separated list of columns can be specified.

If you specify an order by clause, then results are returned in ascending order by default. NULL
values are returned first when the results are in ascending order. Ordering of results in descending
order is the exact opposite of the ordering of results in ascending order.

Example 1

The following example shows how to use the select statement within the OQL Service Provider to
query the staff.managers table (the following example output is abbreviated).

|phoenix:1.>select * fromstaff.managers;
|phoenix:2.>go
.....
{
EmployeeID=1;
Name='Matt';
Department='Development';
Gender='M';
Age=28;
42

}
{
EmployeeID=2;
....
....
}
( 5 record(s) : Transaction complete )
Example 2

The following example shows a select statement that retrieves only specific fields from
the staff.managers table.

|phoenix:1.>select Name, Gender fromstaff.managers;

|phoenix:2.>go
.....
{
Name='Matt';
Gender='M';
}
{
Name='Irene';
Gender='F';
}
{
Name='Ernie';
....
....
}
( 5 record(s) : Transaction complete )
Example 3

The following example uses a where clause to restrict the results.

|phoenix:1.>selectEmployeeID, Name fromstaff.managers

|phoenix:2.>where Department = "Marketing";
|phoenix:3.>go
.
{
EmployeeID=4;
Name='John';
}
( 1 record(s) : Transaction complete )
Example 4

The following example shows how to use a select DISTINCT keyword to retrieve a single row
for each type of data; for example a single row for each department.

|phoenix:1.>select DISTINCT Department fromstaff.managers

|phoenix:2.>go
.
43

{
Department='Development';
}
{
Department='Marketing';
}
{
Department='Sales';
}
( 3 record(s) : Transaction complete )
query the data in a table using the select keyword. Use these examples to help you use the select
keyword.

RESULT:
Thus the query for object relational database using object query language was created and
executed.