Object-relational feature analysis

A generalization hierarchy

A generalization hierarchy
Five options presented:
1 One relational table containing all attributes
2 Three relational tables (one for each class) with the same primary key
3 Three relational tables with views to simulate hierarchy
4 Type as table column
5 Hierarchy of typed tables
Option SQL code to create
1 One relational table containing all attributes CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name Personal_name,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_is_employee BOOLEAN,
Memb_is_student BOOLEAN,
Memb_emp_payroll_number INTEGER,
Memb_emp_date_employed DATE,
Memb_emp_room CHAR(4),
Memb_student_number INTEGER,
Memb_student_status VARCHAR(20),
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));
2 Three relational tables (one for each class) with the same primary key CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name Personal_name,
Memb_suspended BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_Employee(
Emp_Memb_ID INTEGER NOT NULL,
Emp_payroll_number INTEGER,
Emp_date_employed DATE,
Emp_room CHAR(4),
CONSTRAINT pk_Member_emp PRIMARY KEY (Emp_Memb_ID),
CONSTRAINT fk_Member_emp FOREIGN KEY (Emp_Memb_ID) REFERENCES tbl_Member ON DELETE CASCADE );

CREATE TABLE tbl_Student(
Student_Memb_ID INTEGER NOT NULL,
Student_number INTEGER,
Student_status VARCHAR(20),
CONSTRAINT pk_Member_student PRIMARY KEY (Student_Memb_ID),
CONSTRAINT fk_Member_student FOREIGN KEY (Student_Memb_ID) REFERENCES tbl_Member ON DELETE CASCADE);
3 Three relational tables with views to simulate a hierarchy CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name Personal_name,
Memb_suspended BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_Employee(
Emp_Memb_ID INTEGER NOT NULL,
Emp_payroll_number INTEGER,
Emp_date_employed DATE,
Emp_room CHAR(4),
CONSTRAINT pk_Member_emp PRIMARY KEY (Emp_Memb_ID),
CONSTRAINT fk_Member_emp FOREIGN KEY (Emp_Memb_ID) REFERENCES tbl_Member ON DELETE CASCADE );

CREATE TABLE tbl_Student(
Student_Memb_ID INTEGER NOT NULL,
Student_number INTEGER,
Student_status VARCHAR(20),
CONSTRAINT pk_Member_student PRIMARY KEY (Student_Memb_ID),
CONSTRAINT fk_Member_student FOREIGN KEY (Student_Memb_ID) REFERENCES tbl_Member ON DELETE CASCADE);

CREATE VIEW Employee AS
(SELECT M.*, E.*
FROM tbl_Member M, tbl_Employee E
WHERE M.Memb_ID = E.Emp_Memb_ID);

CREATE VIEW Student AS
(SELECT M.*, S.*
FROM tbl_Member M, tbl_Student S
WHERE M.Memb_ID = E. Student_Memb_ID);
4 Type as table column CREATE TYPE Member AS (
Memb_ID INTEGER,
Memb_name Personal_name,
Memb_suspended BOOLEAN DEFAULT FALSE)
NOT INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Employee UNDER Member AS (
Emp_payroll_number INTEGER,
Emp_date_employed DATE,
Emp_room CHAR(4) )
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Student UNDER Member AS (
Student_number INTEGER,
Student_status VARCHAR(20))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Member(
Memb_ref INTEGER NOT NULL,
Member_detail Member,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ref));
5 Hierarchy of typed tables CREATE TYPE Member AS (
Memb_ID INTEGER,
Memb_name Personal_name,
Memb_suspended BOOLEAN DEFAULT FALSE)
NOT INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Employee UNDER Member AS (
Emp_payroll_number INTEGER,
Emp_date_employed DATE,
Emp_room CHAR(4) )
INSTANTIABLE NOT FINAL;

CREATE TYPE Student UNDER Member AS (
Student_number INTEGER,
Student_status VARCHAR(20))
INSTANTIABLE NOT FINAL;

CREATE TABLE tbl_Member OF Member(
REF IS Memb_ref SYSTEM GENERATED,
Memb_ID WITH OPTIONS CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_Employee OF Employee
UNDER tbl_Member( );

CREATE TABLE tbl_Student OF Student
UNDER tbl_Member( );

Option SQL code test CRUD operations
1 One relational table containing all attributes
Create a new object belonging to a sub-class Inserting a new Employee

INSERT INTO tbl_Member
VALUES (1234, NEW Personal_name(’Jo’, ’Smith’), False, TRUE, FALSE, 99123, '12-01-2006', 'AG27', NULL, NULL);
Update an object Suspend an employee

UPDATE tbl_Member
SET Memb_Suspended = TRUE
WHERE Memb_ID = 1234;
Delete an object Delete an employee

DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Read a specific object Find a Student by their student_number

SELECT M.Memb_name.First_name, M.Memb_name.Last_name
FROM tbl_Member M
WHERE M.Memb_student_number = 10665243;
Find all instances of a subclass to match a condition Find all employees who started after 1/12/05

SELECT M.Memb_name.First_name, M.Memb_name.Last_name, M.Memb_emp_date_employed
FROM tbl_Member M
WHERE M.Memb_is_employee
AND M.Memb_emp_date_employed > '01-12-2005'';
2 Three relational tables (one for each class) with the same primary key
Create a new object belonging to a sub-class Inserting a new Employee

INSERT INTO tbl_Member
VALUES (1234, NEW Personal_name(’Jo’, ’Smith’), False);
INSERT INTO tbl_Employee
VALUES ( 1234, 99123, '12-01-2006', 'AG27');
Update an object Suspend an employee

UPDATE tbl_Member
SET Memb_Suspended = TRUE
WHERE Memb_ID = (SELECT Emp_Memb_ID FROM tbl_Employee WHERE Emp_Payroll_number = 99123;
Delete an object Delete an employee

DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Read a specific object Find a Student by their student_number

SELECT M.Memb_name.First_name, M.Memb_name.Last_name
FROM tbl_Member M INNER JOIN tbl_Student S ON M.Memb_ID = S.Stud_Memb_ID
WHERE S.student_number = 10665243;
Find all instances of a subclass to match a condition Find all employees who started after 1/12/05

SELECT M.Memb_name.First_name, M.Memb_name.Last_name, E.Emp_date_employed
FROM tbl_Member M INNER JOIN tbl_Employee E ON M.Memb_ID = E.Emp_Memb_ID
WHERE E.Emp_date_employed > '01-12-2005'';
3 Three relational tables with views to simulate a hierarchy
Create a new object belonging to a sub-class Inserting a new Employee

INSERT INTO Employee
VALUES (1234, NEW Personal_name(’Jo’, ’Smith’), False, 1234, 99123, '12-01-2006', 'AG27');
Update an object Suspend an employee

UPDATE Member
SET Memb_Suspended = TRUE
WHERE Memb_Emp_Payroll_number= 99123;
Delete an object Delete an employee

DELETE FROM Employee
WHERE Memb_ID = 1234;
Read a specific object Find a Student by their student_number

SELECT Memb_name.First_name, Memb_name.Last_name
FROM Student
WHERE Student_number = 10665243;
Find all instances of a subclass to match a condition Find all employees who started after 1/12/05

SELECT Memb_name.First_name, Memb_name.Last_name, Emp_date_employed
FROM Employee
WHERE Emp_date_employed > '01-12-2005'';
4 Type as table column
Create a new object belonging to a sub-class Inserting a new Employee

INSERT INTO tbl_Member
VALUES (1111, NEW Employee (1234, NEW Personal_name (’Jo’, ’Smith’), False, 99123, '12-01-2006', 'AG27'));
Update an object Suspend an employee

UPDATE tbl_Member
SET Member_details.Memb_Suspended = TRUE
WHERE M.Member_detail IS OF Employee AND TREAT (VALUE (M.Member_detail) AS Employee).Emp_Payroll_number= 99123;
Delete an object Delete an employee

DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Read a specific object Find a Student by their student_number

SELECT M. Member_detail. Memb_name.First_name, M.Memb_name.Last_name
FROM tbl_Member M
WHERE M.Member_detail IS OF Student AND TREAT (VALUE (M.Member_detail) AS Student).M. Member_detail. Memb_student_number = 10665243;
Find all instances of a subclass to match a condition Find all employees who started after 1/12/05

SELECT M.Member_detail. Memb_name.First_name, M.Member_detail. Memb_name.Last_name, TREAT (VALUE(M.Member_detail) AS Employee).Emp_date_employed AS Date_employed FROM tbl_Member M WHERE M.Member_detail IS OF Employee AND TREAT (VALUE(M.Member_detail) AS Employee).Emp_date_employed > '01-12-2005'';
5 Hierarchy of typed tables
Create a new object belonging to a sub-class Inserting a new Employee

INSERT INTO tbl_Employee
VALUES (1234, NEW Personal_name(’Jo’, ’Smith’), False, 99123, '12-01-2006', 'AG27');
Update an object Suspend an employee

UPDATE tbl_Employee
SET Memb_Suspended = TRUE
WHERE Emp_Payroll_number= 99123;
Delete an object Delete an employee

DELETE FROM tbl_Employee
WHERE Memb_ID = 1234;
Read a specific object Find a Student by their student_number

SELECT S.Memb_name.First_name, S.Memb_name.Last_name
FROM tbl_Student S
WHERE S.Student_number = 10665243;
Find all instances of a subclass to match a condition Find all employees who started after 1/12/05

SELECT E.Memb_name.First_name, E.Memb_name.Last_name, E.Emp_date_employed
FROM tbl_Employee E
WHERE E.Emp_date_employed > '01-12-2005'';

Option SQL code to test Schema changes
1 One relational table containing all attributes
Add a new attribute to the superclass ALTER TABLE tbl_Member
ADD COLUMN Memb_date_joined DATE;
Add a new attribute to the subclass ALTER TABLE tbl_Member
ADD COLUMN Memb_emp_phone VARCHAR(12);
Drop an existing attribute from the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
Drop an existing attribute from the subclass ALTER TABLE tbl_Member
DROP COLUMN Memb_emp_phone;
Change the name of an attribute of the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
ALTER TABLE tbl_Member
ADD COLUMN Member_date_joined DATE;
Change the name of an attribute of the subclass ALTER TABLE tbl_Member
DROP COLUMN Memb_emp_phone; ALTER TABLE tbl_Member
ADD COLUMN Memb_emp_telephone VARCHAR(12);
Change the defaults value of an attribute of the superclass ALTER TABLE tbl_Member
ALTER COLUMN Memb_suspended SET DEFAULT TRUE;
Change the defaults value of an attribute of the subclass ALTER TABLE tbl_Member
ALTER COLUMN Memb_emp_room SET DEFAULT 1114;
Change the constraints of an attribute of the superclass ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_ID_ck CHECK(Memb_ID BETWEEN 1 AND 9999);
Change the constraints of an attributeof the subclass ALTER TABLE tbl_Member
ADD CONSTRAINT Emp_room_ck CHECK(Memb_emp_room BETWEEN 1101 AND 9099);
2 Three relational tables (one for each class) with the same primary key
Add a new attribute to the superclass ALTER TABLE tbl_Member
ADD COLUMN Memb_date_joined DATE;
Add a new attribute to the subclass ALTER TABLE tbl_Employee
ADD COLUMN Emp_phone VARCHAR(12);
Drop an existing attribute from the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
Drop an existing attribute from the subclass ALTER TABLE tbl_Employee
DROP COLUMN Emp_phone;
Change the name of an attribute of the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
ALTER TABLE tbl_Member
ADD COLUMN Member_date_joined DATE;
Change the name of an attribute of the subclass ALTER TABLE tbl_Employee
DROP COLUMN Emp_phone;
ALTER TABLE tbl_Employee
ADD COLUMN Emp_telephone VARCHAR(12);
Change the defaults value of an attribute of the superclass ALTER TABLE tbl_Member
ALTER COLUMN Memb_suspended SET DEFAULT TRUE;
Change the defaults value of an attribute of the subclass ALTER TABLE tbl_Employee
ALTER COLUMN Emp_room SET DEFAULT 1114;
Change the constraints of an attribute of the superclass ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_ID_ck CHECK(Memb_ID BETWEEN 1 AND 9999);
Change the constraints of an attributeof the subclass ALTER TABLE tbl_Employee
ADD CONSTRAINT Emp_room_ck CHECK(Emp_room BETWEEN 1101 AND 9099);
3 Three relational tables with views to simulate a hierarchy
Add a new attribute to the superclass ALTER TABLE tbl_Member
ADD COLUMN Memb_date_joined DATE;
Add a new attribute to the subclass ALTER TABLE tbl_Employee
ADD COLUMN Emp_phone VARCHAR(12);
Drop an existing attribute from the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
Drop an existing attribute from the subclass ALTER TABLE tbl_Employee
DROP COLUMN Emp_phone;
Change the name of an attribute of the superclass ALTER TABLE tbl_Member
DROP COLUMN Memb_date_joined;
ALTER TABLE tbl_Member
ADD COLUMN Member_date_joined DATE;
Change the name of an attribute of the subclass ALTER TABLE tbl_Employee
DROP COLUMN Emp_phone;
ALTER TABLE tbl_Employee
ADD COLUMN Emp_telephone VARCHAR(12);
Change the defaults value of an attribute of the superclass ALTER TABLE tbl_Member
ALTER COLUMN Memb_suspended SET DEFAULT TRUE;
Change the defaults value of an attribute of the subclass ALTER TABLE tbl_Employee
ALTER COLUMN Emp_room SET DEFAULT 1114;
Change the constraints of an attribute of the superclass ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_ID_ck CHECK(Memb_ID BETWEEN 1 AND 9999);
Change the constraints of an attributeof the subclass ALTER TABLE tbl_Employee
ADD CONSTRAINT Emp_room_ck CHECK(Emp_room BETWEEN 1101 AND 9099);
4 Type as table column
Add a new attribute to the superclass ALTER TYPE Member
ADD ATTRIBUTE Memb_date_joined DATE;
Add a new attribute to the subclass ALTER TYPE Employee
ADD ATTRIBUTE Emp_phone VARCHAR(12);
Drop an existing attribute from the superclass ALTER TYPE Member
DROP ATTRIBUTE Emp_Memb_date_joined;
Drop an existing attribute from the subclass ALTER TYPE Employee
DROP ATTRIBUTE Emp_phone;
Change the name of an attribute of the superclass ALTER TYPE Member
DROP ATTRIBUTE Memb_date_joined;
ALTER TYPE Member
ADD ATTRIBUTE Member_date_joined DATE;
Change the name of an attribute of the subclass ALTER TYPE Employee
DROP ATTRIBUTE Emp_phone;
ALTER TYPE Employee
ADD ATTRIBUTE Emp_telephone VARCHAR(12);
Change the defaults value of an attribute of the superclass ALTER TYPE Member
ALTER ATTRIBUTE Memb_suspended SET DEFAULT TRUE;
Change the defaults value of an attribute of the subclass ALTER TYPE Employee
ALTER ATTRIBUTE Emp_room SET DEFAULT 1114;
Change the constraints of an attribute of the superclass Not possible to put constraints on a type within a column of a table
Change the constraints of an attributeof the subclass Not possible to put constraints on a type within a column of a table
5 Hierarchy of typed tables
Add a new attribute to the superclass Can't change the attributes of a type used in a typed table
Add a new attribute to the subclass Can't change the attributes of a type used in a typed table
Drop an existing attribute from the superclass Can't change the attributes of a type used in a typed table
Drop an existing attribute from the subclass Can't change the attributes of a type used in a typed table
Change the name of an attribute of the superclass Can't change the attributes of a type used in a typed table
Change the name of an attribute of the subclass Can't change the attributes of a type used in a typed table
Change the defaults value of an attribute of the superclass ALTER TYPE Member
ALTER ATTRIBUTE Memb_suspended SET DEFAULT TRUE;
Change the defaults value of an attribute of the subclass ALTER TYPE Employee
ALTER ATTRIBUTE Emp_room SET DEFAULT 1114;
Change the constraints of an attribute of the superclass ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_ID_ck CHECK(Memb_ID BETWEEN 1 AND 9999);
Change the constraints of an attributeof the subclass ALTER TABLE tbl_Employee
ADD CONSTRAINT Emp_room_ck CHECK(Emp_room BETWEEN 1101 AND 9099);