A.V.C.

C OLLEGE OF E NGINEERING
Approved by AICTE, New Delhi | Affiliated to Anna University, Chennai
Accredited by NAAC with ‘A’ Grade | An ISO 9001:2015 Certified Institution
Mannampandal, Mayiladuthurai District,Tamilnadu-609305

ESTD: 1996

DEPARTMENT OF ARTIFICIAL INTELLIGENCE AND DATA SCIENCE

AD3381 – DATABASE DESIGN & MANAGEMENT LABORATORY

III SEMESTER – R 2021

LABORATORY MANUAL

Prepared by:

Mrs.G.ARULKUMAR
Assistant Professor
Department of AI&DS
 SYLLABUS

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

TOTAL : 45 PERIODS
Exp:1 Database Development Lifecycle of Banking Management
Date:

Aim:

To plan the effective development of banking, the efficient way to use it by the
users.

Steps:
System Definition: The system definition is done to manage the scope and the range of
boundaries. A bank collects money, cheques, bills and drafts. It accepts deposits from the public &
lends loan to those who are in need of it.
Requirements Collection And Analysis :

1) The XYZ bank can have many automated teller machines(ATMs), and the new system
shall provide functionally on all ATMs.
2) The bank performs 3 types of functions;
a) Withdrawal of funds
b) Query of account balance
c) Transfer of funds from one bank account to another in the same bank
3) The ATM card must be authorized and issues by the bank.
4) The system shall allow the customer to enter the Correct PIN in no more three
attempts .The failure this will lead to confiscation of the ATM card
5) The banking system also identifies that whether there is sufficient amount in the bank
before transaction
6) The customer records , account records and debit card records will all be maintained
at the server and shall not be the responsibility of the system
7) The system shall be linked with the bank server through communication systems, which
are beyond the scope of the current system. It is assumed that this facility is always
available.
Database Design: The database is designed in such a way that it contains the details of the account
complaints, customer, interest, loan and transactions.
Selection of DBMS: The DBMS must be selected for the database.
Prototyping: We must give a prototyping system of our banking management system.
Implementation: The implementation of our idea must be done.
Data conversion and loading: Converting the existing application to run the new database.
Operational Maintenance: Implementing and monitoring the system.

Class Diagram:
Use Case Diagram:

SCOPE OF BANKING MANAGEMENT:

 It can be used by bank employees and customer depending on the bank policies. It can be
used by several employees at the same time. It can be accessed using any general web
browser with geographical interface

Result:

Thus the Database Development Lifecycle of Banking Management has been successfully designed
and verified.
EXP: 2 ER- and EER-to-Relational Mapping
Date:

Aim:

To design a ER- and EER-to-Relational Mapping using MySQL.

Steps:
 ER-to-Relational mapping algorithm
 step 1: mapping of regular entity types
 step 2: m
 apping of weak entity types
 step 3: mapping of binary 1:1 relation types
 step 4: mapping of binary 1:N relationship types
 step 5: mapping of binary M:N relationship types
 step 6: mapping of multi valued attributes
 step 7: mapping of N-ary relationship types

 mapping EER model constructs to relations

 step 8: options for mapping specialization or generalization
 step 9: mapping of union types (categories)
ER conceptual schema:

Resulting relational database schema:

Together of relational and conceptual schema:

Step 1:
 Mapping of regular entity types
 For each regular entity type E in the ER schema, create a relation R that includes all the
simple attributes of E.
 Include only the simple component attributes of a composite attribute. Choose one of the
key attributes of E as primary key for R.
 If the chosen key of E is composite, the set of simple attributes that form it will together
form the primary key of R .
 e.g., EMPLOYEE, DEPARTMENT, PROJECT

Step 2:
 Mapping of weak entity types.
 For each weak entity type W in the ER schema with owner entity type E, create a relation R
and include all simple attributes of W as attributes of R .
  Include as foreign key attributes of R the primary key attribute(s) of the relation(s) that
correspond to the owner entity type(s). The primary key of R is the combination of the
primary key(s) of the owner(s) and the partial key of the weak entity type W, if any.
 If there is a weak entity type E2 whose owner is also a weak entity type E1, then E1 should
be mapped before E2 to determine its primary key first.
 e.g., DEPENDENT

Step 3:
 Mapping of binary 1:1 relationship types
 For each binary 1:1 relationship type R in the ER schema, identify the relations S and T that
correspond to the entity types participating in R
 Foreign key approach
 choose one of the relations, S, and include as a foreign key in S the primary key of T
 include all the simple attributes of R as attributes of S
 Merged relation option
 merge the two entity types and the relationship into a single relation
 Relationship relation option
 set up a third relation R for the purpose of cross-referencing the primary keys of S and T
 MANAGES -> DEPARTMENT.MGRSSN,DEPARTMENT.MGRSTARTDATE
Step 4:
 Mapping of binary 1:N relationship types
 For each binary 1:N relationship type R, identify the relation S that represents the participating
entity type at the N-side of the relationship type
 Include as foreign key in S the primary key of the relation T that represents the other entity
type participating in R
 Include any simple attributes of the 1:N relationship type as attributes of S
 e.g., WORKS_FOR: S = EMPLOYEE, T = DEPARTMENT, DNO: the primary key of T
Step 5:
 Mapping of binary M:N relationship types
 For each binary M:N relationship type R, create a new relation S to represent R
 Include as foreign key attributes in S the primary keys of the relations that represent
the participating entity types
  Their combination will form the primary key of S
 Include any simple attributes of R as attributes of S
 e.g., WORKS_ON: S = WORKS_ON
Step 6:
 Mapping of multi valued attributes
 For each multi valued attribute A, create a new relation R
 R will include an attribute corresponding to A, plus the primary key attribute K - as a foreign
key in R – of the relation that represents the entity type or relationship type that has A as an
attribute
 The primary key of R is the combination of A and K
 If the multi valued attribute is composite, include its simple components
 e.g., Locations: A = DLOCATION, R = DEPT_LOCATIONS, K = DNUMBER.
Step 7:
 Mapping of N-ary relationship types
 For each n-ary relationship type R, where n > 2, create a new relation S to represent R
 Include as foreign key attributes in S the primary keys of the relations that represent
the participating entity types
 Include any simple attributes of R as attributes of S
 The primary key of S is usually a combination of all the foreign keys that reference the
relations representing the participating entity types e.g., SUPPL
Step 8:
 options for mapping specialization or generalization
 convert each specialization with m subclasses {S1, S2, …, Sm} and superclass C, where the
attributes of C are {k, a1, …, an} and k is the key, into relation schemas using one of the
following options
Option 8A:
 Multiple relations-super class and subclasses
 Create a relation L for C with attributes Attrs(L) = {k, a1, …, an} and PK(L) = k
 Create a relation Li for each subclass Si, with the attributes Attrs(Li) = {k} U
{attributes of Si} and PK(Li) = k
 Works for any specialization (total or partial, disjoint or overlapping)

Option 8B:
 Multiple relations-subclass relations only
 Create a relation Li for each subclass Si, with the attributes Attrs(Li) = {attributes of Si} U{k,
a1, …, an} and PK(Li) = k
 Only works for a specialization whose subclasses are total
Option 8C:
 single relation with one type attribute
 create a single relation L with attributes Attrs(L) = {k, a1, …, an} U {attributes of S1} U…
U{attributes of Sm} U{t} and PK(L) = k
 the attribute t is called a type attribute that indicates the subclass to which each tuple
belongs
 works only for a specialization whose subclasses are disjoint

Option 8D:
 single relation with multiple type attributes
 create a single relation schema L with attributes Attrs(L) = {k, a1, …, an} U
{attributes of S1} U… U{attributes of Sm} U{t1, …, tm} and PK(L) = k
  each ti is a Boolean type attribute indicating whether a tuple belongs to subclass Si
 works for a specialization whose subclasses are overlapping

Mapping of shared subclasses

 shared subclass: a subclass of several superclasses, indicating multiple inheritance
 apply any of the options in step 8 to a shared subclass
Mapping of categories
Category:
 A subclass of the union of two or more super classes that can have different keys because they
can be of different entity types
Step 9:
 mapping of categories
 mapping a category whose defining super classes have different keys
 specify a new key attribute, called a surrogate key
 include the surrogate key attribute as foreign key in each relation
corresponding to a super class of the category
 e.g., OWNER category
 mapping a category whose super classes have the same key
 no need for a surrogate key
 e.g., REGISTERED_VEHICLE
Result:

Thus the ER- and EER-to-Relational Mapping has been successfully designed and verified.
Exp: 3 PRACTICING DDL COMMANDS
Date:
Aim :

To create an employee table using DDL commands.

Procedure :

1) Create a table called employee1 with the following structure.

Name Type

emp_no integer

e_name Varchar(30)

desig Varchar(30)

age integer
salary integer

a. Add a column commission with domain to the Employee table.

b. Insert any five records into the table.
c. Update the column details of design.
d. Rename the column of employee1 table using alter command.
e. Truncate the table using truncate command
f. Drop the table using drop command.
g. Create a view in the name of emp_view and Display
the contents of the table.
Data Definition Language (DDL) Commands :

Table Creation :

Output :

Alter Table :

Output :-
Truncate Table :

Output :

Drop Table :

Output :

Views :

View Creation :
OUTPUT :-

Result :

Thus the employee table was created successfully using DDL commands.
Exp: 4 PRACTICING DML COMMANDS
Date:
Aim :

To manipulate the data in the table using DML commands.

Procedure :-

a. Insert any five records into the table.

b. Add a column to the table named place to the table using alter add command
c. Update the column details of place.
d. Delete a record from the table where the emp_no=1001.

Data Manipulation Language (DML) Commands :

Inserting Data To The Table :
 Output :

Updating Values In The Table :

Output :

Deleting A Record From The Trable :

OUTPUT :

Result :

Thus the data in the table has been manipulated successfully using DML commands.
 Exp:5a TRIGGERS
Date:

AIM
To study and implement the concepts of triggers.
DEFINITION
A trigger is a statement that is executed automatically by the system as a side effect of a
modification to the database. The parts of a trigger are,
Trigger statement: Specifies the DML statements and fires the trigger body. It also specifies
the table to which the trigger is associated.
Trigger body or trigger action: It is a PL/SQL block that is executed when The triggering
statement is used.
Trigger restriction: Restrictions on the trigger can be achieved The different uses of triggers
are as follows,
• To generate data automatically
• To enforce complex integrity constraints
• To customize complex securing authorizations
• To maintain the replicate table
• To audit data modifications
TRIGGERS - SYNTAX
CREATE [OR REPLACE] TRIGGER trigger_ nameBEFORE|AFTER
[INSERT,UPDATE,DELETE[COLUMNNAME..]
ON table_name
Referencing[OLDASOLD|NEWAS NEW]
FOREACHROW|FOREACHSTATEMENT [ WHEN Condition]
DECLARE
[declaration_section
variable declarations;constantdeclarations;
]
BEGIN
[executable_section
PL/SQLexecute/subprogram body
] EXCEPTION
[exception_section
PL/SQLExceptionblock ]
1. mysql> create database tr;
Query OK, 1 row affected (0.02 sec)
2. mysql> use tr;
Database changed
3. mysql> CREATE TABLE test1(a1 INT);
Query OK, 0 rows affected (0.06 sec)

4. mysql> CREATE TABLE test2(a2 INT);

Query OK, 0 rows affected (0.25 sec)

5. mysql> CREATE TABLE test3(a3 INT NOT NULL AUTO_INCREMENT

PRIMARY KEY);
Query OK, 0 rows affected (0.08 sec)

6. mysql> CREATE TABLE test4(

-> a4 INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> b4 INT DEFAULT 0
-> );
Query OK, 0 rows affected (0.06 sec)

7. mysql> delimiter |
8. mysql> CREATE TRIGGER testref BEFORE INSERT ON test1
-> FOR EACH ROW
-> BEGIN
-> INSERT INTO test2 SET a2 = NEW.a1;
-> DELETE FROM test3 WHERE a3 = NEW.a1;
-> UPDATE test4 SET b4 = b4 + 1 WHERE a4 = NEW.a1;
-> END;
-> |
Query OK, 0 rows affected (0.01 sec)

9. mysql> delimiter ;
10. mysql> INSERT INTO test3 (a3) VALUES
-> (NULL), (NULL), (NULL), (NULL), (NULL),
-> (NULL), (NULL), (NULL), (NULL), (NULL);
Query OK, 10 rows affected (0.04 sec)
Records: 10 Duplicates: 0 Warnings: 0
11. mysql> INSERT INTO test4 (a4) VALUES
-> (0), (0), (0), (0), (0), (0), (0), (0), (0), (0);
Query OK, 10 rows affected (0.03 sec)
Records: 10 Duplicates: 0 Warnings: 0

12. mysql> INSERT INTO test1 VALUES

-> (1), (3), (1), (7), (1), (8), (4), (4);
Query OK, 8 rows affected (0.05 sec)
Records: 8 Duplicates: 0 Warnings: 0

13. mysql> SELECT * FROM test1;

14. mysql> SELECT * FROM test2;

15. mysql> SELECT * FROM test3;

16. mysql> SELECT * FROM test4;

RESULT
Thus the Triggers were created, executed and their respective outputs were verified.
Exp:5b STORED PROCEDURES
Date:

AIM:
To write MYSQL programs that executes the concept of procedures.
DEFINITION:
A procedure or function is a logically grouped set of SQL and PL/SQL statements that
perform a specific task. They are essentially sub-programs.
Procedures and functions are made up of,
• Declarative part
• Executable part
• Optional exception handling part
These procedures and functions do not show the errors.
KEYWORDS AND THEIR PURPOSES REPLACE:
It recreates the procedure if it already exists.
PROCEDURE: It is the name of the procedure to be created.
ARGUMENT: It is the name of the argument to the procedure. Paranthesis can be omitted
if no arguments are present.
IN: Specifies that a value for the argument must be specified when calling the procedure
ie. used to pass values to a sub-program. This is the default parameter.
OUT: Specifies that the procedure passes a value for this argument back to it’s calling
environment after execution ie. used to return values to a caller of the sub-program.
INOUT: Specifies that a value for the argument must be specified when calling the
procedure and that procedure passes a value for this argument back to it’s calling
environment after execution. RETURN: It is the datatype of the function’s return value
because every function must return a value, this clause is required.

SYNTAX:
CREATE
[DEFINER = user]
PROCEDURE [IF NOT EXISTS] sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
CREATE
[DEFINER = user]
FUNCTION [IF NOT EXISTS] sp_name ([func_parameter[,...]])
RETURNS type
[characteristic ...] routine_body
proc_parameter:
[ IN | OUT | INOUT ] param_name type
func_parameter:
param_name type
type:
Any valid MySQL data type
characteristic: {
COMMENT 'string'
| LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
}
routine_body:
Valid SQL routine statement
mysql> USE database_name;

Procedure without Parameter

Procedures with IN Parameter

DELIMITER &&
CREATE PROCEDURE get_student (IN var1 INT)
BEGIN
SELECT * FROM student_info LIMIT var1;
SELECT COUNT(stud_code) AS Total_Student FROM student_info;
END &&
DELIMITER ;

Procedures with OUT Parameter

DELIMITER &&
CREATE PROCEDURE display_max_mark (OUT highestmark INT)
BEGIN

SELECT MAX(marks) INTO highestmark FROM student_info;

END &&
DELIMITER ;
Procedures with INOUT Parameter

DELIMITER &&
CREATE PROCEDURE display_marks (INOUT var1 INT)
BEGIN
SELECT marks INTO var1 FROM student_info WHERE stud_id = var1;
END &&
DELIMITER ;
Result:
The MYSQL queries to create procedures and functions were executed and
their respective outputs were verified.
Exp: 6 Database Design Using Normalization Bottom-Up Approach
Date:
Aim:

To design a database using Normalization technique(Bottom-up Approach).

Normalization:
 It is the processes of reducing the redundancy of data in the table and also improving the
data integrity. So why is this required? Without Normalization in SQL, we may face
many issues such as.
 Insertion anomaly: It occurs when we cannot insert data to the table without the presence
of another attribute.
 Update anomaly: It is a data inconsistency that results from data redundancy and a
partial update of data.
 Deletion Anomaly: It occurs when certain attributes are lost because of the deletion of
other attributes.
 Normalization entails organizing the columns and tables of a database to ensure that their
dependencies are properly enforced by database integrity constraints.
 It usually divides a large table into smaller ones, so it is more efficient. In 1970 the First
Normal Form was defined by Edgar F Codd and eventually, other Normal Forms were
defined.
 Normalization in SQL will enhance the distribution of data. Now let’s understand each
and every Normal Form with examples.
1st Normal Form (1NF)
 In this Normal Form, we tackle the problem of atomicity. Here atomicity means values in
the table should not be further divided. In simple terms, a single cell cannot hold multiple
values. If a table contains a composite or multi-valued attribute, it violates the First Normal
Form.

 In the above table, we can clearly see that the Phone Number column has two values. Thus
it violated the 1st NF. Now if we apply the 1st NF to the above table we get the below
table as the result.

 We have achieved atomicity and also each and every column have unique values.
2nd Normal Form (2NF)
 The first condition in the 2nd NF is that the table has to be in 1st NF. The table also should
not contain partial dependency. Here partial dependency means the proper subset of
candidate key determines a non-prime attribute.

EMPLOYE DEPART OFFICE

E ID MENT ID LOCATIO
N
1EDU001 ED-T1 Pune
1EDU002 ED-S2 Bengaluru
1EDU003 ED-M1 Delhi
1EDU004 ED-T3 Mumbai

 This table has a composite primary key Employee ID, Department ID. The non-key
attribute is Office Location. In this case, Office Location only depends on Department ID,
which is only part of the primary key.
  Therefore, this table does not satisfy the second Normal Form. To bring this table to
Second Normal Form, we need to break the table into two parts.

EMPLOYEE ID DEPARTMENT ID
1EDU001 ED-T1
1EDU002 ED-S2
1EDU003 ED-M1
1EDU004 ED-T3

DEPARTMENT OFFICE LOCATION

ID
ED-T1 Pune
ED-S2 Bengaluru
ED-M1 Delhi
ED-T3 Mumbai
 In the table, the column Office Location is fully dependent on the primary key of that table,
which is Department ID.

3rd Normal Form (3NF)

 The table has to be in 2NF before proceeding to 3NF. The other condition is there
should be no transitive dependency for non-prime attributes.
 That means non-prime attributes (which doesn’t form a candidate key) should not be
dependent on other non-prime attributes in a given table.
 So a transitive dependency is a functional dependency in which X → Z (X
determines Z) indirectly, by virtue of X → Y and Y → Z.

STUDENT STUDENT SUBJECT ID SUBJECT ADD

ID NAME RES
S
1DT15ENG0 Alex 15CS11 SQL Goa
1
1DT15ENG0 Barry 15CS13 JAVA Beng
2 alur
u
1DT15ENG0 Clair 15CS12 C++ Delhi
3
1DT15ENG0 David 15CS13 JAVA Koch
4 i
 In the above table, Student ID determines Subject ID, and Subject ID
determines Subject.
 Therefore, Student ID determines Subject via Subject ID. This implies that we have a
transitive functional dependency, and this structure does not satisfy the third normal form.
STUDE STUDENT SUBJECT ADDRESS
NT NAME ID
1DT15E Alex 15CS11 Goa
NG01
1DT15E Barry 15CS13 Bengaluru
NG02
1DT15E Clair 15CS12 Delhi
NG03
1DT15E David 15CS13 Kochi
NG04

SUBJECT SUBJECT
ID
15CS11 SQL
 15CS13 JAVA
15CS12 C++
15CS13 JAVA

 The above tables all the non-key attributes are now fully functional dependent only on the
primary key.
 In the first table, columns Student Name, Subject ID and Address are only dependent on
Student ID. In the second table, Subject is only dependent on Subject ID.

Boyce Codd Normal Form (BCNF)

 This is also known as 3.5 NF. It’s the higher version 3NF and was developed by Raymond
F. Boyce and Edgar F. Codd to address certain types of anomalies which were not dealt
with 3NF.
 The table has to satisfy 3rd Normal Form.
 In BCNF if every functional dependency A → B, then A has to be the Super Key of
that particular table.

STUDENT ID SUBJE PROFESSO

CT R
1DT15ENG01 SQL Prof. Mishra
1DT15ENG02 JAVA Prof. Anand
1DT15ENG02 C++ Prof. Kanthi
1DT15ENG03 JAVA Prof. Anand
1DT15ENG04 DBMS Prof. Lokesh

 One student can enrol for multiple subjects.

 There can be multiple professors teaching one subject .
 And, for each subject, a professor is assigned to the student.
 In the table Student ID, and Subject form the primary key, which means the Subject
column is a prime attribute. But, there is one more dependency,
Professor → Subject.
 And while Subject is a prime attribute, Professor is a non-prime attribute, which is not
allowed by BCNF.
 Dividing the table into two parts. One table will hold Student ID which already exists and
newly created column Professor ID.

STUDENT ID PROFESSO
R ID
1DT15ENG01 1DTPF01
1DT15ENG02 1DTPF02
1DT15ENG02 1DTPF03
 And in the second table, we will have the columns Professor ID, Professor and Subject.

PROFESSOR ID PROFESS SUBJECT

OR
1DTPF01 Prof. SQL
Mishra
 1DTPF01 Prof. JAVA
Anand
1DTPF01 Prof. C++
Kanthi
: : :
 By this we satisfiying the Boyce Codd Normal Form.

Bottom –up approach:

 Normalisation is a bottom-up approach which starts with a collection of attributes and
organises them into well-structured relations which are free from redundant data.
BCNF: Boyce-Codd Normal Form

Result:

Thus the database has been designed using Normalization technique(Bottom-up Approach).
Exp:7: Develop A Database Application Using IDE/RAD Tools
Date:

Aim:

To Develop A Database Application Using IDE/RAD Tools.

Procedure:
 Open you Microsoft Visual Studio 2010, 2012 or higher, or just your Microsoft
Visual Basic .Net.
 Create a new project (select File and New Project).For visual studio user: (select
Visual Basic then Windows FormApplication).
 Here is the sample form layout or design. Feel free to design your form.We need to add
the following controls:
 2 labels
 2 textboxes
 2 buttons
 1 checkbox.
 The program will first validate the input of the user, the user must enter a username and
password or else a message will appear that will notify theuser that username and
password field is required.
 The program will then match or compare the user input to the criteria of the program.
The username must be admin and password must also be admin which means that the
username and password combination must beadmin or else a message will prompt you
that your username and password is incorrect.
 To clear the username and password field, kindly double click the Reset button and
paste the code below.
TextBox1.Clear()
TextBox2.Clear()
 Additional feature of this program is to allow the user to view or to makeits password visible
or in simplest explanation is to view what you are typing in the password field. Kindly
double click the Show password checkbox and paste the line of codes below.
Program:
If TextBox1.Text = "" Then
MessageBox.Show("Please enter username")
TextBox1.Focus()
Exit Sub
ElseIf TextBox2.Text = "" Then
MessageBox.Show("Please enter password")
TextBox2.Focus()
Exit Sub
End If
If TextBox1.Text = "admin" And TextBox2.Text = "admin" Then
MessageBox.Show("welcome admin")
Else
MessageBox.Show("incorrect username or password") End If

If CheckBox1.Checked = True Then

TextBox2.PasswordChar = ""
Else
TextBox2.PasswordChar = "*" End
If

Output:
Result:

Thus a Database Application has been successfully developed Using IDE/RAD Tools.
Exp:8 Database design using EER to- ODB mapping/ UML class diagrams
Date:

Aim:

To design a Database 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 > 2.
 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.
 Multivalued attributes are typically declared by using the set, bag, or list constructors. 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).
Step 2.
 Add relationship properties or reference attributes for each binary relationship 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.
  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
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.
Step 4.
 An ODL class that corresponds to a subclass in the EER schema inherits the type and
methods of its super class in the ODL schema.
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.
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
 By declaring a class to represent the category and defining 1:1 relationships between the
category and each of its super classes.
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.
 The mapping has been applied to a subset of the UNIVERSITY database schema in
the context of the ODMG object database standard. The mapped object
schema using the ODL notation is shown.
EER to ODB mapping diagram:

EER (UML class diagram):

Result:

Thus the Database has been designed successfully using EER to- ODB mapping/
UML class diagrams.
Exp:9 OBJECT FEATURES OF SQL-UDTs

Date:
Aim:

To implement the object features of SQl-UDT’s

Objects of SQL:
 SQL objects are schemas, journals, catalogues, tables, aliases, views, indexes, constraints,
triggers, sequences, stored procedures, user-defined functions, user-defined types, global
variables, and SQL packages, SQL creates and maintains these objects.

UDT in SQL:
 The UDT is similar to an alias data type and it uses the existing data types in SQL server
or Azure SQL database.
 SQL server supports two kinds of user defined types
 User- defined data type.
 User- defined table type
Use of UDT in sql server:
 User defined type can be used in the definition of database objects such as variables in
transact-SQL batches, in functions and stored procedures, and as arguments in functions and
stored procedures.

Sub- types of UDT in SQL:

 Exact numeric.
 Approximate numeric.
 Date and Time.
 Character String.
 Unicode character strings.
  CLR data types.
 Spatial data types

Tables using UDTs:

 There is no special syntax for creating a UDT column in a table. You can use the name of the
UDT in 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 into identity column is named PointgValue, with a
data type of Point.
Inheritance in SQL object types:
 SQL object inheritance is based on a family tree of object types that forms a type hierarchy.
The type hierarchy consists of a parent object type, called a super type, and one or more
levels of child object types, called subtypes, which are derived from the parent.
 A subtype 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).

Method Definition:
 A method is procedure or function that is part of the object type definition, and that can
operate on the attributes of the type. Such methods are also called member
methods, and they take the keyword MEMBER when you specify them as a component of
the object type.
 Method specification
  Method names
 Method name overloading

Implementing Methods

To implement a method, create the PL/SQL code and specify it within a CREATE
TYPE BODY statement.

For example, consider the following definition of an object type named rational type:

CREATE TYPE rational_type AS OBJECT (

numerator INTEGER,
denominator INTEGER,
MAP MEMBER FUNCTION rat_to_real RETURN REAL,
MEMBER PROCEDURE normalize, MEMBER
FUNCTION plus (x rational_type)
RETURN rational_type);

Result:

Thus the object features of SQl-UDT’s has been successfully implemented.

 Exp: 10 Querying the Object-relational database using Object Query Language
Date:
Aim:

Querying the Object-relational database using Object Query Language

Object–relational database
 An object–relational database (ORD), or object–relational database management system
(ORDBMS), is a database management system (DBMS) similar to a rela- tional database,
but with an object-oriented database model: objects, classes and inheritance are directly
supported in database schemas and in the query language. In addition, just as with pure
relational systems, it supports extension of the data model with custom data types and
methods.

 An object–relational database can be said to provide a middle ground between relational databases
and object-oriented databases. In object–relational databases, the approach is essentially that of
relational databases.
 The data resides in the database and is manipulated collectively with queries in a query language.
 At the other extreme are OODBMS in which the database is essentially a persistent object store
for software written in an object-oriented programming language, with a
programming API for storing and retrieving objects, and little or no specific support for querying.

Procedure:
 CREATE.
 INSERT.
 UPDATE.
 DELETE
Program:
CREATE TABLE Employees (FirstName VARCHAR(32) NOT NULL,
Surname VARCHAR(64) NOT NULL,DOB DATE NOT NULL,
Salary DECIMAL(10,2) NOT NULLCHECK ( Salary > 0.0 ),
Address_1 VARCHAR(64) NOT NULL,Address_2 VAR- CHAR(64) NOT NULL,
City VARCHAR(48) NOT NULL,State CHAR(2) NOT NULL,ZipCode INTEGER NOT
NULL,PRIMARY KEY (
Surname, FirstName, DOB ));
INSERT INTO Employees ( Pager_Number, Pass_Code, Mes- sage )
SELECT E.Pager_Number,E.Pass_Code,
Print(E.Name) || ': Call 1-800-TEMPS-R-US for immediate INFORMIX DBA job'
FROM Temporary_Employees E
WHERE Contains (GeoCircle('(-122.514, 37.221)', '60
miles')),E.LivesAt )
AND DocContains ( E.Resume, 'INFORMIX and Database Administrator')
AND NOT IsBooked ( Period(TODAY, TODAY + 7),E.Booked );
SELECT *FROM Employees;
Output:

Result:
Thus the Object Relational database has been implemented using Object Query Language.