TABLE OF CONTENTS

S.NO DATE LIST OF EXPERIMENTS PAGE MARKS SIGNATURE

NO

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
 Database Development Life cycle:

Problem definition and Requirement analysisScope and Constraints

To understand Database development Life cycle with its problem definition and
Aim:
requirement analysis along with their scope and constrains for different applications

Database Development Life Cycle:

Database Development Life Cycle

What are the different phases of database development Life cycle (DBMS)?
The different phases of database development life cycle (DDLC) in the Database
Management System (DBMS)
 Requirement analysis.
 Database design.
 Evaluation and selection.
 Logical database design.
 Physical database design.
 Implementation.
 Data loading.
 Testing and performance tuning.
 Operation.
 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.
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.

Database Life Cycle (DBLC)

The Database Life Cycle (DBLC) contains six phases, as shown in the following Figure:
database initial study, database design, implementation and loading, testing and evaluation,
operation, and maintenance and evolution.
1. The Database Initial Study:
In the Database initial study, the designer must examine the current system’s
operation within the company and determine how and why the current system fails. The
overall purpose of the database initial study is to:
• Analyze the company situation.
• Define problems and constraints.
• Define objectives.
• Define scope and boundaries.

a. Analyze the Company Situation:

The company situation describes the general conditions in which a company operates
its organizational structure, and its mission. To analyze the company situation, the database
designer must discover what the company’s operational components are, how they function,
and how they interact.

b. Define Problems and Constraints:

The designer has both formal and informal sources of information. The process of
defining problems might initially appear to be unstructured. Company end users are often
unable to describe precisely the larger scope of company operations or to identify the real
problems encountered during company operations.
c. Define Objectives:

A proposed database system must be designed to help solve at least the major
problems identified during the problem discovery process. In any case, the database designer
must begin to address the following questions:
• What is the proposed system’s initial objective?
• Will the system interface with other existing or future systems in the company?
• Will the system share the data with other systems or users?

d. Define Scope and Boundaries:

The designer must recognize the existence of two sets of limits: scope and boundaries.
The system’s scope defines the extent of the design according to operational requirements.
Will the database design encompass the entire organization, one or more departments within
the organization, or one or more functions of a single department? Knowing the scope helps
in defining the required data structures, the type and number of entities, the physical size of
the database, and so on.

The proposed system is also subject to limits known as boundaries, which are external to the
system. Boundaries are also imposed by existing hardware and software.

2. Database Design:
The second phase focuses on the design of the database model that will support
company operations and objectives. This is arguably the most critical DBLC phase: making
sure that the final product meets user and system requirements. As you examine the
procedures required to complete the design phase in the DBLC, remember these points:
• The process of database design is loosely related to the analysis and design of a larger
system. The data component is only one element of a larger information system.

• The systems analysts or systems programmers are in charge of designing the other system
components. Their activities create the procedures that will help transform the data within the
database into useful information.

3. Implementation and Loading:

The output of the database design phase is a series of instructions detailing the creation of
tables, attributes, domains, views, indexes, security constraints, and storage and performance
guidelines. In this phase, you actually implement all these design specifications.
a. Install the DBMS:
This step is required only when a new dedicated instance of the DBMS is necessary
for the system. The DBMS may be installed on a new server or it may be installed on existing
servers. One current trend is called virtualization. Virtualization is a technique that creates
logical representations of computing resources that are independent of the underlying
physical computing resources.
b. Create the Database(s):

In most modern relational DBMSs a new database implementation requires the creation of
special storage-related constructs to house the end-user tables. The constructs usually include
the storage group (or file groups), the table spaces, and the tables.

c. Load or Convert the Data:

After the database has been created, the data must be loaded into the database tables.
Typically, the data will have to be migrated from the prior version of the system. Often, data
to be included in the system must be aggregated from multiple sources. Data may have to be
imported from other relational databases, non relational databases, flat files, legacy systems,
or even manual paper-and-pencil systems

4. Testing and Evaluation:

In the design phase, decisions were made to ensure integrity, security, performance, and
recoverability of the database. During implementation and loading, these plans were put into
place. In testing and evaluation, the DBA tests and fine-tunes the database to ensure that it
performs as expected. This phase occurs in conjunction with applications programming.
a. Test the Database:
During this step, the DBA tests the database to ensure that it maintains the integrity and
security of the data. Data integrity is enforced by the DBMS through the proper use of
primary and foreign key rules. In database testing you must check Physical security allows,
Password security, Access rights, Data encryption etc.
b. Fine-Tune the Database:
Although database performance can be difficult to evaluate because there are no standards
for database performance measures, it is typically one of the most important factors in
database implementation. Different systems will place different performance requirements on
the database. Many factors can impact the database’s performance on various tasks.
Environmental factors, such as the hardware and software environment in which the database
exists, can have a significant impact on database performance.
c. Evaluate the Database and Its Application Programs:
As the database and application programs are created and tested, the system must also be
evaluated from a more holistic approach. Testing and evaluation of the individual
components should culminate in a variety of broader system tests to ensure that all of the
components interact properly to meet the needs of the users. To ensure that the data contained
in the database are protected against loss, backup and recovery plans are tested.

5. Operation
Once the database has passed the evaluation stage, it is considered to be operational. At that
point, the database, its management, its users, and its application programs constitute a
complete information system. The beginning of the operational phase invariably starts the
process of system evolution.
6. Maintenance and Evolution
The database administrator must be prepared to perform routine maintenance activities within
the database. Some of the required periodic maintenance activities include:
• Preventive maintenance (backup).
• Corrective maintenance (recovery).
• Adaptive maintenance (enhancing performance, adding entities and attributes, and so on).
• Assignment of access permissions and their maintenance for new and old users.

Problem definition and Requirement analysis:

Problem Definition:
The designer has both formal and informal sources of information. The process of
defining problems might initially appear to be unstructured. Company end users are often
unable to describe precisely the larger scope of company operations or to identify the real
problems encountered during company operations.
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.
Scope and Constrains using PostgreSQL:

The following are commonly used constraints available in PostgreSQL.

 NOT NULL Constraint − Ensures that a column cannot have NULL value.
 UNIQUE Constraint − Ensures that all values in a column are different.
 PRIMARY Key − Uniquely identifies each row/record in a database table.
 FOREIGN Key − Constrains data based on columns in other tables.
 CHECK Constraint − The CHECK constraint ensures that all values in a column
satisfy certain conditions.
 EXCLUSION Constraint − The EXCLUDE constraint ensures that if any two rows
are compared on the specified column(s) or expression(s) using the specified
operator(s), not all of these comparisons will return TRUE.

Conclusion:
Thus database development life cycle is analyzed with some constrains.
 Database design using Conceptual modeling (ER-EER) – top-down approach
Mapping conceptual to relational database and validate using Normalization

Aim:
To design using database using conceptual modeling (ER – EER) – top down
approach mapping conceptual to relational database and validate using normalization.

Procedure:

ER-to-Relational Mapping Algorithm:

The steps of an algorithm for ER-to-relational mapping. We use the COMPANY
database example to illustrate the mapping procedure. The COMPANY ER schema is
shown, and the corresponding COMPANY relational database schema is shown below.
ER conceptual schema diagram for the COMPANY database.

Bdate Name Salary

Locations

Name Number

Supervisor Supervisee

Name

Number

Name
 The relational model constraints, which include primary keys, unique keys (if any),
and referential integrity constraints on the relations, will also be specified in the mapping
results.

Steps 1: Mapping of Regular Entity Types.

Step 2: Mapping of Weak Entity Types.
Step 3: Mapping of Binary 1:1 Relationship Types.
Step 4: Mapping of Binary 1: N Relationship Types.
Step 5: Mapping of Binary M: N Relationship Types.
Step 6: Mapping of Multivalued Attributes.
Step 7: Mapping of N-ary Relationship Types.

Mapping EER Model Constructs to Relations:

Mapping of Specialization or Generalization

Step 8: Options for Mapping Specialization or Generalization.

 Multiple relations—superclass and subclasses.
 Multiple relations—subclass relations only
 Single relation with one type attribute
 Single relation with multiple type attributes

Create your Own Database design using EER Model construct.

1. Consider the ER design for the UNIVERSITY database that was modeled using a tool
like ERwin or Rational Rose in Laboratory. Using the SQL schema generation feature of
the modeling tool, generate the SQL schema for an Oracle database.

Conclusion:
Thus database using EER is constructed.
 Implement the database using SQL Data definition with constraints, Views

Aim:
To implement database using SQL data definition with constrains and views.

DDL Commands:
To execute DDL Commands

1. Create Table

2. Alter Table
3. Drop Table

4. Truncate Table

5. Rename Table

DDL
Definition Syntax Example
Commands
To organizes CREATE TABLE table_name ( create table friends
and stores data Column1 data type, (
in a structured Column2 data type, regno int,
format: in rows Column3 data type, name varchar(25),
Create
and columns. ..... pno numeric(10),
Table Column N data type, addrvarchar(100)
PRIMARY KEY (one or more );
columns)
);

To add, modify, ALTER TABLE table_name ALTER Table

or drop/delete ALTER COLUMN column_name friends Alter
columns in a TYPE column_definition; Column name
table TYPE VAR
CHAR (50);

Add column to ALTER TABLE <table name> add alter table friends
Alter Table created table add
<column name><data
type><restrictions / conditions> mailidvarchar(25);

Drop the ALTER TABLE <table name> drop alter table friends
Column column <column name> drop column
regno;

Modify the alter table <table name> alter column alter table friends
 Column Data <column name> type <new alter column addr
Type datatype> type varchar(50);

To remove an DROP TABLE table_name ; drop table fri;

existing table or
Drop Table tables from the
database.
To alter table alter table <table name> rename to alter table friends
Rename name <new name>; rename to fri;
Table

Quickly TRUNCATE [TABLE NAME] Truncate table fri

Truncate removes all
Table rows from a set
of tables

Constrains and views:

Constrains

Constrains Meaning Syntax Example

Ensures that a column CREATE TABLE CREATE TABLE

cannot have NULL value products ( products (product_no
NOT NULL product_no integer integer NULL,name text
Constraint NOT NULL, name NULL, price numeric
text NOT NULL, NULL );
price numeric);

Ensures that all values in CREATE TABLE CREATE TABLE

table_name
a column are different. products (
(Column1datatype
null/not null, product_no integer,
column2datatype
UNIQUE name text,
null/not null, ...
Constraint CONSTRAINT price numeric,
constraint name
UNIQUE
UNIQUE (column1,
column2 ...column_n) (product_no)
);
);

Uniquely identifies each CREATE TABLE CREATE TABLE

PRIMARY table_name
row/record in a database products (
Key (Column1 data type
table product_no integer
null/not null, column2
 data type null/not null PRIMARY KEY,
...
name text,
CONSTRAINT
constraint_name price numeric
PRIMARY KEY );
(column1, column2 ...
column n)
);

Constrains data based on CREATE TABLE CREATE TABLE

columns in other tables. <table_name> orders (
(Column1 data type
order_id integer
null/not null, column2
data type null/not null, PRIMARY KEY,
... CONSTRAINT
FOREIGN product_no integer
<fk_column>
Key FOREIGN KEY REFERENCES
(column1, column2) products (product_no),
REFERENCES
quantity integer
parent_table (column1,
column2) );
);

The CHECK constraint CREATE TABLE CREATE TABLE

ensures that all values in table_name products ( product_no
(Column1data type integer, name text,
a column satisfy certain
null/not null, column2
conditions price numeric
CHECK data type null/not null
... CONSTRAINT CONSTRAINT
Constraint
constraint name positive_price CHECK
CHECK
(price > 0));
(column_name
condition)
);
 The EXCLUDE CREATE TABLE CREATE TABLE
constraint ensures that if circles (c circle, circles ( c circle,
any two rows are EXCLUDE USING gist EXCLUDE USING gist
compared on the (c WITH &&)); (c WITH &&) );
EXCLUSION
specified column(s) or
Constraint
expression(s) using the
specified operator(s), not
all of these comparisons
will return TRUE.

Views:

Views Meaning Syntax Example

To create a view, by CREATE VIEW CREATE VIEW
CREATE VIEW statement. view_name AS
query; comedies AS
Creating
SELECT *
PostgreSQL
FROM films
Views
WHERE kind =
'Comedy';
To change the defining CREATE OR CREATE comedies
REPLACE
Changing query of a view, by using view_name AS AS SELECT * from
PostgreSQL CREATE VIEW statement query films where kind =
Views with OR REPLACE ‘comedy’;
addition

To remove an existing view DROP VIEW [ IF DROP VIEW

Removing EXISTS ]
PostgreSQL in PostgreSQL, by DROP view_name; Comedies;

Views VIEW statement

Result :
Thus SQL Commands executed with Constrains and views.
 Query the database using SQL Manipulation

AIM:
To execute different queries for database using SQL manipulation
SQL MANIPULATION:

DML Query:
1. Insert

2. Update

3. Delete

4. Select

Commands Meaning Syntax Example

Insert into <table Insert into fri (regno,name,pno,addr)
Insert
name> (<cn1,cn2, values(1,'Anand',7894561320,'Coimbatore');
Insert Data
cn3>) values
intothe
(<v1,v2,v3>)
Table

Update<table name> Updatefri set name = 'Anand Kumar';

set <column name> =
<value>

Update<table name>
set <column name> =
Updatefri set name = 'Anand Kumar',addr =
<value>, <column
'Erode';
name> = <value>,
Update
Update <column name> =
the Data
<value>
Value

Update<table name>
set <column name> =
<value>, <column
name> = <value>,
updatefri set name = 'Anand Kumar',addr =
<column name> = 'Erode' where regno = 1;
<value> where <cn>
= <value> and <cn> =
 <value> and <cn> =
<value>

Delete from <table Delete from fri where regno = 1;

Delete Delete name>where <cn> =
the Data <value> and <cn> =
<value>

Select(<cn1,cn2,cn3>) Selectregno,name,pno,addr from fri;

from <table name>

Select
(<cn1,cn2,cn3>) from
Select
<table name> where
Select the Data
<cn> = <value>
from
Table
Select
(<cn1,cn2,cn3>) from
<table name> where
<cn> = <value> and
<cn> = <value>

Result:
Thus the query manipulation is done by SQL queries.
 Querying/Managing the database using SQL Programming
- Stored Procedures/Functions
- Constraints and security using Triggers

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

Procedure:

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

Single Row Functions:

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

⚫ We can having single row functions for the following process

⚫ Numeric Functions
⚫ String Functions
⚫ Date and Time Functions

Group Functions:
⚫ These functions group the rows of data based on the values returned by the
query.

⚫ This is discussed in SQL GROUP Functions.

Programs to demonstrate the Concepts:

1) Calculate the net salary and year salary if da is 30% of basic, hra is 10% of
basic and pf is 7% if basic salary is less than 8000, pf is 10% if basic sal
between 8000 to160000.

Program:
declare
e name
varchar2(15);
basic number;
d a number; h r a
number;pf number;
net salary
number; year
salary number;
begin
e name:='&e
name';
basic:=&basic;
da:=basic *
(30/100);
hra:=basic *
(10/100);if (basic
< 8000) then
pf:=basic *
(8/100);
elsif (basic >= 8000 and basic <=
16000) thenpf:=basic * (10/100);

end if;
netsalary:=basic + da + hra - pf; yearsalary := netsalary*12;

dbms_output.put_line('Employee name : ' || ename); dbms_output.put_line('Providend

Fund : ' || pf); dbms_output.put_line('Net salary : ' || netsalary);
dbms_output.put_line('Year salary : '|| yearsalary);end;
/
2) Create a function to find the factorial of a given number and hence find NCR.
Program:
SQL> create or replace function fact(n number) return number isa number:=n; f
number:=1;i number;
begin
for i in 1..n
loopf:=f*a;
a:=a-1;
end
loop;
return
f; end;
/
SQL> create or replace function ncr(n number ,r number) return number
isn1number:=fact(n);
r1
number:=fact(r);
nr1
number:=fact(n-r);
resultnumber;
begin result:=(n1)/(r1*nr1); return
result;end;
/

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

Aim:
To implement database design using Normalization with bottom up approach.

Procedure:
Normalization is a process of converting a relation to be standard form by
decomposition a larger relation into smaller efficient relation that depicts a good database
design.
• 1NF: A Relation scheme is said to be in 1NF if the attribute values in the relation are
atomic.i.e., Mutli –valued attributes are not permitted.

• 2NF: A Relation scheme is said to be in 2NF,iff and every Non-key attribute is fully
functionally dependent on primary Key.

• 3NF: A Relation scheme is said to be in 3NF,iff and does not have transitivity
dependencies. A Relation is said to be 3NF if every determinant is a key for each & every
functional dependency.

• BCNF: A Relation scheme is said to be BCNF if the following statements are true for eacg
FD P->Q in set F of FDs that holds for each FD. P->Q in set F of FD’s that holds over R.
Here P is the subset of attributes of R & Q is a single attribute of R.

The given FD is a trivial

P is a super key.

Normalized tables are:-

CREATE TABLE BUS2 (BUSNO VARCHAR (20) PRIMARY KEY, SOURCE
VARCHAR (20), DESTINATION VARCHAR (20));

CREATE TABLE PASSENGER4 (PPN VARCHAR (15) PRIMARY KEY, NAME

VARCHAR (20), AGE INTEGER, SEX CHAR, ADDRESS VARCHAR (20));

CREATE TABLE PASSENGERTICKET (PPN VARCHAR (15) PRIMARY KEY,

TICKETNO INTEGER);

CREATE TABLE RESERVATION2 (PNRNO INTEGER PRIMARY KEY,

JOURNEYDATE DATETIME, NOOFSEATS INT, ADDRESS VARCHAR (20),
CONTACTNO INTEGER);

CREATE TABLE CANCELLATION2 (PNRNO INTEGER PRIMARY KEY,

JOURNEYDATE DATETIME, NOOFSEATS INTEGER, ADDRESS VARCHAR (20),
CONTACTNO INTEGER, FOREIGN KEY (PNRNO) REFERENCES RESERVATION2
(PNRNO));

CREATE TABLE TICKET2(TICKETNO INTEGER PRIMARY KEY,JOURNEYDATE

DATETIME, AGE INT(4),SEX CHAR(2),SOURCE VARCHAR(20),DESTINATION
VARCHAR(20),DEPTTIME VARCHAR(2));

Result:
Thus we implemented database design using Normalization with bottom up approach.
 Develop database applications using IDE/RAD tools (Eg., NetBeans, VisualStudio)

Aim:
To develop database applications using IDE tools with NetBeans.

Procedure:

Creating the Database Tables in NetBeans IDE:

To create the database tables in Java DB, the database server included with
Application Server, need to create the database connection and execute the SQL commands
in tut-install/examples/common/sql/javadb/tutorial.sql.

Creating the Database Connection

To create the database connections do the following:

1. Click the Services tab.

2. Right-click the Databases node and select New Connection to open the New
Connection dialog.

3. Under Name, select Java DB (Network).

4. Set Database URL to the following:

jdbc:derby://localhost:1527/sun-appserv-samples

5. Set User Name to APP.

6. Set Password to APP.

7. Select the Remember Password during this Session box.

8. Click OK.

Creating the Tables

To create the tutorial tables, do the following:

1. Select File->Open File.

2. Navigate to tut-install/examples/common/sql/javadb/ and open tutorial.sql.

3. In the editor pane, select the connection URL to Java DB:

jdbc:derby://localhost:1527/sun-appserv-samples

4. Click the Run SQL button at the top of the editor pane.
Deleting the Tables
1. To delete the tutorial tables, do the following:

2. Select File->Open File.

3. Navigate to tut-install/examples/common/sql/javadb/ and open delete.sql.

4. In the editor pane, select the connection URL to Java DB:

jdbc:derby://localhost:1527/sun-appserv-samples

5. Click the Run SQL button at the top of the editor pane.

Conclusion:
Thus a database application is created using IDE – Netbean Tools.
 Database design using EER-to-ODB mapping / UML class diagrams

Aim:
To develop database design using EER to ODB mapping / UML Class Diagrams.

Procedure:

Mapping an EER Schema to an ODB Schema

It is relatively straightforward to design the type declarations of object classes for an
ODBMS from an EER schema that contains neither categories nor n-ary relationships with n-
ary. However, the operations of classes are not specified in the EER diagram and must be
added to the class declarations after the structural mapping is completed. The outline of the
mapping from EER to ODL is as follows:

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. 38 Multivalued attributes are typically
declared by using the set, bag, or list constructors. 39 If the values of the multivalued
attribute for an object should be ordered, the list constructor is chosen; if duplicates are
allowed, the bag constructor should be chosen; otherwise, the set constructor is chosen.
Composite attributes are mapped into a tuple constructor (by using a struct declaration in
ODL).Declare an extent for each class, and specify any key attributes as keys of the extent.
(This is possible only if an extent facility and key constraint declarations are avail- able in the
ODBMS.)

Step 2. Add relationship properties or reference attributes for each binary relation- ship into
the ODL classes that participate in the relationship. These may be created in one or both
directions. If a binary relationship is represented by references in both directions, declare
the references to be relationship properties that are inverses of one another, if such a facility
exists. 40 If a binary relationship is represented by a reference in only one direction, declare
the reference to be an attribute in the referencing class whose type is the referenced class
name. Depending on the cardinality ratio of the binary relationship, the relationship
properties or reference attributes may be single-valued or collection types. They will be
single-valued for binary relationships in the 1:1 or N:1 directions; they are collection types
(set-valued or list-valued 41 ) for relationships in the 1:N or M:N direction. An alternative
way to map binary M:N relationships is discussed in step 7.
If relationship attributes exist, a tuple constructor ( struct ) can be used to create a structure of
the form < reference , relationship attributes >, which may be included instead of the
reference attribute. However, this does not allow the use of the inverse constraint.
Additionally, if this choice is represented in both directions, the attribute values will be
represented twice, creating redundancy.

38 This implicitly uses a tuple constructor at the top level of the type declaration, but in
general, the tuple constructor is not explicitly shown in the ODL class declarations. 39
Further analysis of the application domain is needed to decide which constructor to use
because this information is not available from the EER schema. 40 The ODL standard
provides for the explicit definition of inverse relationships. Some ODBMS products may
not provide this support; in such cases, programmers must maintain every relationship
explicitly bycoding the methods that update the objects appropriately. 41 The decision
whether to use set or list is not available from the EER schema and must be determined

Step 3. Include appropriate operations for each class. These are not available from the EER
schema and must be added to the database design by referring to the original requirements.
A constructor method should include program code that checks any constraints that must
hold when a new object is created. A destructor method should check any constraints that
may be violated when an object is deleted. Other methods should include any further
constraint checks that are relevant.

Step 4. An ODL class that corresponds to a subclass in the EER schema inherits (via
extends) the type and methods of its super class in the ODL schema. It’s specific (non
inherited) attributes, relationship references, and operations are specified, as discussed in
steps 1, 2, and 3.

Step 5. Weak entity types can be mapped in the same way as regular entity types. An
alternative mapping is possible for weak entity types that do not participate in any
relationships except their identifying relationship; these can be mapped as though they were
composite multivalued attributes of the owner entity type, by using the set < struct < ... >>
or list < struct < ... >> constructors. The attributes of the weak entity are included in the
struct < ... > construct, which corresponds to a tuple constructor. Attributes are mapped as
discussed in steps 1 and 2.

Step 6. Categories (union types) in an EER schema are difficult to map to ODL. It is
 possible to create a mapping similar to the EER-to-relational mapping (see Section

9.2) by declaring a class to represent the category and defining 1:1 relationships between the
category and each of its superclasses. Another option is to use a union type, if it is available

Step 7. An n-ary relationship with degree n > 2 can be mapped into a separate class, with
appropriate references to each participating class. These references are based on mapping a
1: N relationship from each class that represents a participating entity type to the class that
represents the n-ary relationship. An M:N binary relationship, especially if it contains
relationship attributes, may also use this mapping option, if desired.

Conclusion:
Thus database design using EER-to-ODB mapping / UML class diagrams was
constructed.
 Object features of SQL-UDTs and sub-types, Tables using UDTs,
Inheritance, Method definition

Aim:
To create an object features of SQL – UDTs and sub-types, Tables using UDTs,
Inheritance, Method Definition.

Procedure:

User-Defined Types (UDTs):

To create complex-structured objects, and to separate the declaration of a type from
the creation of a table, SQL. The user will create the UDTs for a particular application as
part of the database schema.

Syntax:

CREATE TYPE TYPE_NAME AS (<component declarations>);

Example:
(a) CREATE TYPE STREET _ ADDR TYPE AS (

NuMBER VARCHAR (5),

STREET-NAME VAR CHAR (25),

APT_NO VAR CHAR (5),

SUITE_NO VAR CHAR (5));

(b) CREATE TYPE PERSON _TYPE AS (

NAME VARCHAR (35),

DOB DATE,

PHONE USA_PHONE_TYPE ARRAY[4],

ADDR USA_ADDR_TYPE

INSTANTIBLE
NOT FINAL
REF IS SYSTEM GENERATED

INSTANT METHOD AGE() RETURN INTEGER;

CREATE INSTANCE METHOD AGE () RETURNS INTEGER

FOR PERSON _TYPE

BEGIN
 RETURN

END);

(C) CREATE TYPE GRADE_TYPE AS (

COURSENO CHAR(8),
SEMESTER VARCHAR(8),
YEAR CHAR(4),
GRADE CHAR );
CREATE TYPE STUDENT_TYPE UNDER PERSON _TYPE AS (
MAJOR-CODE CHAR(4),
STUDENT_ID CHAR(12),
DEGREE VARCHAR(5),
TRANSCRIPT GRADE_TYPE ARRAY[100]
INSTANTIBLE

NOT FINAL

INSTANCE METHOD GPA() RETURNS FLOAT;

CREATE INSTANCE METHOD GPA() RETURNS FLOAT

FOR STUDENT _TYPE

BEGIN

RETURN

END,);

(d) CREATE TABLE PERSON OF PERSON _TYPE

REF IS PERSON _ ID SYSTEM GENERATED,

CREATE TABLE EMPOLOYEE OF EMPOLOYEE _TYPE

UNDER PERSON;

CREATE TABLE MANAGER OF MANAGER_ TYPE

UNDER EMPOLOYEE;

CREATE TABLE STUDENT OF STUDENT_TYPE

UNDER PERSON;

Result:

Thus we created Object features of SQL-UDTs and sub-types, Tables using UDTs,
Inheritance, and Method definition.
 Querying the Object-relational database using Objet Query language

Aim:
To query the Object-relational database using Objet Query language
Procedure:

Object Query Language (OQL)

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.
OQL statements using the OQL Service Provider, for example, to create or modify databases,
insert data into databases and retrieve data.

Create command to create a database.

Syntax:

Create database database_name ;

Example:

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 ) ]

);

Data Types:

Data Types Description

TEXT Holds plain text.
INT Holds integer values.
UINT Holds a 32-bit unsigned integer value
FLOAT Holds decimal values.
LONG64 Holds a 64-bit numerical value.
ULONG64 Holds a 64-bit unsigned numerical value
DATA Holds opaque data, typically of the binary format.
LIST TYPE Holds a list of particular datatypes. The list is enclosed in square
brackets, [].
OBJECT TYPE Holds objects of particular datatypes. The object is enclosed in curly
datatype braces, {}. Objects hold a list of varbinds (name/value pairs).
TIME Holds information pertaining to time.

Objects and varbinds

External data types:

Syntax:

data type datatype is external datatype ;

Example:

data type boolean is external boolean

data type entityTypes is external entityTypes

Result:
Thus we created Object-relational database using Objet Query language.