Scenario 1: Employees, Departments and Projects
In a certain Company, Employees work on Projects within Departments. We could set up a single, relational table to store information on who does what and where; or on the other hand it might be more useful to nest the information about Projects and Departments as an attribute of Employees, or to nest the information about Employees and Departments as an attribute of Projects.
The script file Epd.sql, available to download, contains SQL statements to set up the “EPD” data base in each of these formats. Further statements reconstruct the relational table from the nested-relational ones. Download this file and read it using Notepad, and try to understand what the various statements mean and how they relate to each other.
Epd.sql
SET VERIFY ON
SET ECHO ON
DROP TABLE EPD
/
DROP TABLE EMP_PD
/
DROP TYPE PD
/
DROP TABLE Proj_ED
/
DROP TABLE ED_Table
/
DROP TYPE ED
/
CREATE TABLE EPD
(EMP_NO CHAR(2),
PROJ_NO CHAR(2),
DEPT_NO CHAR(2),
CONSTRAINT PKEY_EPD PRIMARY KEY (EMP_NO, PROJ_NO, DEPT_NO)
)
/
INSERT INTO EPD
VALUES ('E1', 'P1', 'D1')
/
INSERT INTO EPD
VALUES ('E1', 'P2', 'D1')
/
INSERT INTO EPD
VALUES ('E2', 'P1', 'D1')
/
INSERT INTO EPD
VALUES ('E2', 'P5', 'D3')
/
INSERT INTO EPD
VALUES ('E4', 'P4', 'D6')
/
INSERT INTO EPD
VALUES ('E5', 'P4', 'D6')
/
INSERT INTO EPD
VALUES ('E6', 'P4', 'D7')
/
INSERT INTO EPD
VALUES ('E9', 'P4', 'D7')
/
INSERT INTO EPD
VALUES ('E5', 'P3', 'D9')
/
INSERT INTO EPD
VALUES ('E7', 'P3', 'D9')
/
COMMIT;
SELECT * FROM EPD;
PAUSE 'Press Enter to Continue .... '
REM Perhaps some of these values should be nested.
REM Consider the projects and departments per employee.
CREATE TYPE PD AS OBJECT (
PROJ_NO CHAR(2),
DEPT_NO CHAR(2) );
/
CREATE TYPE PD_Table AS TABLE OF PD ;
/
CREATE TABLE Emp_PD (
EMP_NO CHAR(2),
Set_of_PD PD_Table )
NESTED TABLE Set_of_PD STORE AS PDTable
/
INSERT INTO Emp_PD VALUES (
'E1', PD_Table( PD( 'P1', 'D1' ),
PD( 'P2', 'D1' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E2', PD_Table( PD( 'P1', 'D1' ),
PD( 'P5', 'D3' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E4', PD_Table( PD( 'P4', 'D6' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E5', PD_Table( PD( 'P4', 'D6' ),
PD( 'P3', 'D9' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E6', PD_Table( PD( 'P4', 'D7' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E9', PD_Table( PD( 'P4', 'D7' ) ) )
/
INSERT INTO Emp_PD VALUES (
'E7', PD_Table( PD( 'P3', 'D9' ) ) )
/
COMMIT;
COLUMN SET_OF_PD FORMAT A50
SELECT * FROM Emp_PD;
PAUSE 'PRESS ENTER TO CONTINUE ... '
REM Reconstructing the orignal EPD Relation using Unnesting
SELECT E.Emp_no, PD.Proj_no, PD.Dept_no
FROM Emp_PD E, Table (E.Set_of_PD) PD
/
PAUSE 'PRESS ENTER TO CONTINUE ... '
SELECT Set_of_PD FROM Emp_PD
WHERE EMP_NO = 'E2' ;
PAUSE 'PRESS ENTER TO CONTINUE ... '
REM On the other hand !
REM Consider the employees and departments per project.
CREATE TYPE ED AS OBJECT (
EMP_NO CHAR(2),
DEPT_NO CHAR(2) );
/
CREATE TYPE ED_Table AS TABLE OF ED ;
/
CREATE TABLE Proj_ED (
PROJ_NO CHAR(2),
Set_of_ED ED_Table )
NESTED TABLE Set_of_ED STORE AS EDTable
/
INSERT INTO Proj_ED VALUES (
'P1', ED_Table( ED( 'E1', 'D1' ),
ED( 'E2', 'D1' ) ) )
/
INSERT INTO Proj_ED VALUES (
'P2', ED_Table( ED( 'E1', 'D1' ) ) )
/
INSERT INTO Proj_ED VALUES (
'P5', ED_Table( ED( 'E2', 'D3' ) ) )
/
INSERT INTO Proj_ED VALUES (
'P4', ED_Table( ED( 'E4', 'D6' ),
ED( 'E5', 'D6' ),
ED( 'E6', 'D7' ),
ED( 'E9', 'D7' ) ) )
/
INSERT INTO Proj_ED VALUES (
'P3', ED_Table( ED( 'E5', 'D9' ),
ED( 'E7', 'D9' ) ) )
/
COMMIT;
COLUMN SET_OF_ED FORMAT A50
SELECT * FROM Proj_ED;
PAUSE 'PRESS ENTER TO CONTINUE ... '
REM Reconstructing the orignal EPD Relation using Unnesting
SELECT ED.emp_no, P.proj_no, ED.dept_no
FROM Proj_ED P, TABLE(P.Set_of_ED) ED
/
PAUSE 'PRESS ENTER TO CONTINUE ... '
SELECT Set_of_ED FROM Proj_ED
WHERE PROJ_NO = 'P4'
/
Then, run the script. Note that the SQL is interspersed with SQL*Plus PAUSE commands, so that you can examine the output from various SELECT statements before pressing ENTER to continue running the script.
Scenario 2: Points and Lines
In co-ordinate geometry, any point is defined in terms of its distance from a fixed point (the origin) along two lines or axes (denoted as X and Y) which are orthogonal (at right angles) to each other. Thus we could define a data type to represent Points, having two numeric attributes to represent the X and Y values.
Any finite straight line connects the points at its two ends, leading to a further type definition. Moreover, we can apply Pythagoras’ Theorem to calculate the length of such a line from the co-ordinates of its end-points.
The script file NestedLines.sql contains type definitions of PointType and LineType to implement these ideas, with length being defined as a Member Function of LineType. It goes on to create a table of Lines (which has Points nested within it because of the way the data types have been set up) and provides further SQL statements to demonstrate how such a table can be used.
As in Scenario 1, download the script file, then read and run it to try to understand what is going on.
An alternative, object-relational approach involves giving each Point object a unique identifying number (Object ID). Then a Lines2 table can be set up containing, not nested points, but references to points (which may thus be shared between different Line tuples). In order to obtain meaningful information about the points of a line, it is then necessary to dereference the Object IDs. All of this is implemented in the script ObjRelLines.sql. Again, you should download, read and then run this script.
ObjRelLines.sql
SET VERIFY ON
SET ECHO ON
REM REMOVING EXISTING TYPES AND TABLES
DROP TABLE Points CASCADE CONSTRAINTS PURGE
/
DROP TABLE Lines2 CASCADE CONSTRAINTS PURGE
/
DROP TYPE PointTP FORCE
/
REM CREATING NEW TYPES AND TABLES
CREATE TYPE PointTP AS OBJECT (
id integer,
x NUMBER,
y NUMBER );
/
CREATE TABLE Points OF PointTP (Primary Key (id))
/
CREATE TABLE Lines2 (
end1 REF PointTP,
end2 REF PointTP
)
/
REM Creating Points and Lines
Insert Into Points values (100, 0.0, 0.0);
Insert Into Points values (101, 2.3, 1.2);
Insert Into Points values (102, 1.3, 1.4);
Insert Into Points values (103, 4.0, 3.0);
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 100 and p2.id = 101;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 100 and p2.id = 102;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 100 and p2.id = 103;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 101 and p2.id = 100;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 101 and p2.id = 102;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 101 and p2.id = 103;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 102 and p2.id = 100;
INSERT INTO Lines2
SELECT REF(p1), REF(p2)
FROM Points p1, Points P2
WHERE p1.id = 103 and p2.id = 102;
REM QUERIES INVOLVING DEREFRENCING OBJECTS and getting attribute values
SELECT ll.end1.x, ll.end1.y, ll.end2.x, ll.end2.y
FROM Lines2 ll;
PAUSE 'PRESS ENTER TO CONTINUE ... '
REM YOU WILL SEE THAT USING OBJECT REFS, ENABLES OBJECT SHARING
REM NESTED TABLES DOES NOT ALLOW OBJECT SHARING
column deref(end1) format a30
column deref(end2) format a30
REM QUERIES INVOLVING DEREFRENCING OBJECTS using DEREF
select deref(end1), deref(end2)
from lines2;
For your convenience, copies of the output from all of the SELECT queries are contained in the file Example_Queries_Output.txt.
