SQL vs ORM

Creating a table

In SQL the table can be created using the CREATE TABLE command

CREATE TABLE people(
 name VARCHAR(30),
 NIC VARCHAR(10) PRIMARY KEY,
 phone VARCHAR(20)
);

In ORM we can create types and use them to create a table.

Object Type syntax

CREATE TYPE <object_type_name> AS OBJECT(
 <attribute1> <datatype>,
 <attribute2> <datatype>
);

First, we have to create a type as an object using the CREATE TYPE command

CREATE TYPE person AS OBJECT(
 name VARCHAR(30),
 phone VARCHAR(20)
);

Then we can create the table using the above type

CREATE TABLE person_table OF person(
 NIC PRIMARY KEY
);

Inserting data into a table

We can insert data in two ways

Method 1

Without specifying the object name

INSERT INTO person_table
VALUES(101, 'John', 'Smith', 'jsmith@example.com', '1-650-555-0135');

Method 2

Using the object name

INSERT INTO person_table
VALUES(person(101, 'John', 'Smith', 'jsmith@example.com', '1-650-555-0135'));

Creating a table with Primary keys and Foreign keys

employee (eno, ename, hireDate, salary)
project (projID, projName, budget)
emp_Proj (eno, projID, assignedDate)

Where eno & projID are Primary Keys and are foreign keys from tables emp_table, proj_table respectively in the table emp_Proj.

First, we have to create a type

CREATE TYPE emp_proj AS OBJECT(
 eno VARCHAR(20),
 projID VARCHAR(20),
 assignedDate DATE
);

Then create the table specifying the keys

CREATE TABLE emp_Proj_table OF emp_proj(
 PRIMARY KEY (eno, projID),
 FOREIGN KEY (eno) REFERENCES emp_table,
 FOREIGN KEY (projID) REFERENCES proj_table
);