Object-relational feature analysis

A single class

A class
Three options presented:
1 Relational table
2 Typed table
3 Type as table column
Option SQL code to create
1 Relational table CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name CHARACTER VARYING (40) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));
2 Typed table CREATE TYPE type_Member AS (
Memb_ID INTEGER,
Memb_name CHARACTER VARYING (40),
Memb_suspended BOOLEAN DEFAULT FALSE)
INSTANTIABLE FINAL;

CREATE TABLE tbl_Member OF type_Member (PRIMARY KEY (Memb_ID),
Memb_ID WITH OPTIONS NOT NULL,
Memb_name WITH OPTIONS NOT NULL,
REF IS Member_ref SYSTEM GENERATED);
3 Type as table column CREATE TYPE type_Member AS (
Memb_ID INTEGER,
Memb_name CHARACTER VARYING (40),
Memb_suspended BOOLEAN DEFAULT FALSE)
INSTANTIABLE FINAL;

CREATE TABLE tbl_Member(
Member_ref INTEGER GENERATED ALWAYS AS IDENTITY,
Memb_details type_Member,
CONSTRAINT PK_Member PRIMARY KEY (Memb_Ref));

Option SQL code test CRUD operations
1 Relational table
Insert a new Member INSERT INTO tbl_Member
VALUES (1234, ’Jo Smith’, False);
Remove a specified Member DELETE FROM tbl_Member WHERE Memb_ID = 1234;
Update the data to record a change to member's status UPDATE tbl_Member
SET Memb_suspended TO True
WHERE Memb_ID = 1234;
Find a member by number SELECT * FROM tbl_Member
WHERE Memb_ID = 1234;
Find a member by telephone number SELECT * FROM tbl_Member
WHERE Memb_Telephone = '01216339000';
2 Typed table
Insert a new Member INSERT INTO tbl_Member
VALUES (1234, ’Jo Smith’, False);
Remove a specified Member DELETE FROM tbl_Member WHERE Memb_ID = 1234;
Update the data to record a change to member's status UPDATE tbl_Member
SET Memb_suspended TO True
WHERE Memb_ID = 1234;
Find a member by number SELECT * FROM tbl_Member
WHERE Memb_ID = 1234;
Find a member by telephone number SELECT * FROM tbl_Member
WHERE Memb_Telephone = '01216339000';
3 Type as table column
Insert a new Member INSERT INTO tbl_Member (Memb_details)
VALUES (NEW type_Member(1234, ’Jo Smith’, False));
Remove a specified Member DELETE FROM tbl_Member WHERE Memb_details.Memb_ID = 1234;
Update the data to record a change to member's status UPDATE tbl_Member
SET Memb_suspended TO True
WHERE Memb_details.Memb_ID = 1234;
Find a member by number SELECT * FROM tbl_Member
WHERE Memb_details.Memb_ID = 1234;
Find a member by telephone number SELECT * FROM tbl_Member
WHERE Memb_details.Memb_Telephone = '01216339000';

Option SQL code to test Schema changes
1 Relational table
Adding a new attribute ALTER TABLE tbl_Member
ADD COLUMN Memb_Telephone CHARACTER VARYING (24);
Removing an attribute ALTER TABLE tbl_Member DROP COLUMN Memb_Telephone;
Change the name of an attribute of a class ALTER TABLE tbl_Member
DROP COLUMN Memb_Telephone;

ALTER TABLE tbl_Member
ADD COLUMN Member_Telephone CHARACTER VARYING (24);
Adding a default value ALTER TABLE tbl_Member
ALTER COLUMN Memb_Telephone
SET DEFAULT ‘01216339000’;
Adding a constraint ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_Telephone_values
CHECK Memb_telephone IN (‘01216339000’, 0187363636', '017733663');
2 Typed table
Adding a new attribute Not possible to add a new attribute to a type used in a typed table
Removing an attribute Not possible to add a new attribute to a type used in a typed table
Change the name of an attribute of a class Not possible to change the name of an attribute for a type used in a typed table
Adding a default value ALTER TYPE type_Member
ALTER ATTRIBUTE Memb_Telephone
SET DEFAULT ‘01216339000’;
Adding a constraint ALTER TABLE tbl_Member
ADD CONSTRAINT Memb_Telephone_values
CHECK Memb_telephone IN (‘01216339000’, 0187363636', '017733663');
3 Type as table column
Adding a new attribute ALTER TYPE type_Member
ADD ATTRIBUTE Memb_Telephone CHARACTER VARYING (24);
Removing an attribute ALTER TYPE type_Member DROP ATTRIBUTE Memb_Telephone;
Change the name of an attribute of a class ALTER TYPE type_Member DROP ATTRIBUTE Memb_Telephone;

ALTER TYPE type_Member
ADD ATTRIBUTE Memb_Telephone CHARACTER VARYING (24);
Adding a default value ALTER TYPE type_Member
ALTER ATTRIBUTE Memb_Telephone
SET DEFAULT ‘01216339000’;
Adding a constraint Can't add a table constraint to a part of a column