Object-relational feature analysis

Two classes with a one-to-one association

Two classes with a one-to-one association
Five options presented:
1 Two relational tables
2 Two typed tables using REF
3 Three relational tables
4 Three typed tables with REFs instead of foreign keys
5 Nested types
Option SQL code to create
1 Two relational tables CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name VARCHAR (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_ID_card(
Barcode INTEGER NOT NULL,
Date_issued DATE NOT NULL,
Memb_ID INTEGER NOT NULL,
CONSTRAINT pk_ID PRIMARY KEY (Barcode),
CONSTRAINT uq_ID UNIQUE(Memb_ID),
CONSTRAINT fk_ID_Memb FOREIGN KEY (Memb_ID) REFERENCES tbl_Member);
2 Two typed tables using REF CREATE TYPE type_Member AS (
Memb_ID INTEGER,
Memb_first_name VARCHAR VARYING (20),
Memb_suspended BOOLEAN DEFAULT FALSE)
INSTANTIABLE FINAL;

CREATE TYPE ID_card(
Barcode INTEGER,
Date_issued DATE,
Member REF(Member) SCOPE tbl_Member )
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);

CREATE TABLE tbl_ID_card OF TYPE ID_card(
Barcode WITH OPTIONS CONSTRAINT Card_pk PRIMARY KEY (Barcode),
Member WITH OPTIONS CONSTRAINT Member_nn NOT NULL,
Member WITH OPTIONS CONSTRAINT Member_unique UNIQUE,
REF is Card_ref SYSTEM GENERATED);
3 Three relational tables CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_name VARCHAR (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_Card(
Card_barcode INTEGER,
Card_date_issued DATE NOT NULL,
CONSTRAINT pk_Card PRIMARY KEY (Card_barcode));

CREATE TABLE tbl_Owns(
Card_barcode INTEGER NOT NULL,
Member_ID INTEGER NOT NULL,
CONSTRAINT pk_Owns PRIMARY KEY (Card_barcode, Member_ID),
CONSTRAINT UQ_Owns_Member UNIQUE (Member_ID),
CONSTRAINT UQ_Owns_Card UNIQUE (Card_barcode),
CONSTRAINT fk_Member_Owns FOREIGN KEY (Member_ID) REFERENCES tbl_Member ON DELETE CASCADE,
CONSTRAINT fk_Card_Owns FOREIGN KEY (Card_barcode) REFERENCES tbl_Card ON DELETE CASCADE);
4 Three typed tables with REFs instead of foreign keys CREATE TYPE Member AS (
Memb_ID INTEGER,
Memb_name VARCHAR VARYING (20),
Memb_suspended BOOLEAN DEFAULT FALSE)
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE ID_card(
Barcode INTEGER,
Date_issued DATE,
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED);

CREATE TYPE Owns AS (
Memb_pointer REF(Member) SCOPE tbl_Member,
Barcode REF(ID_card) SCOPE tbl_Card)
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Member OF Member(
PRIMARY KEY (Memb_ID),
Memb_name WITH OPTIONS NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
REF IS Member_ref SYSTEM GENERATED;

CREATE TABLE tbl_Card OF Card(
PRIMARY KEY (Card_barcode)
Card_date_issued WITH OPTIONS NOT NULL,
REF IS Card_ref SYSTEM GENERATED;

CREATE TABLE tbl_Owns OF Owns(
PRIMARY KEY (Barcode, Member_pointer),
Barcode WITH OPTIONS CONSTRAINT owns_barcode_uq UNIQUE,
Barcode WITH OPTIONS CONSTRAINT owns_barcode_fk FOREIGN KEY REFERENCES tbl_Card (Card_barcode) ON DELETE CASCADE, Memb_pointer WITH OPTIONS CONSTRAINT owns_memb_uq UNIQUE,
Memb_pointer WITH OPTIONS CONSTRAINT owns_memb_fk FOREIGN KEY REFERENCES tbl_Member (Memb_ID) ON DELETE CASCADE, REF IS owns_ref SYSTEM GENERATED);
5 Nested types CREATE TYPE Member_type AS (
Memb_ID INTEGER,
Memb_name VARCHAR VARYING (20),
Memb_suspended BOOLEAN DEFAULT FALSE)
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE ID_card_type AS(
Barcode INTEGER,
Date_issued DATE,
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED);

CREATE TYPE Member_with_card AS(
Member Member_type,
Card ID_card_type)
INSTANTIABLE NOT FINAL;

CREATE TABLE tbl_Member_with_card(
Member WITH OPTIONS CONSTRAINT member_nn NOT NULL,
Card WITH OPTIONS CONSTRAINT card_nn NOT NULL,
REF IS memb_ref SYSTEM GENERATED);

Option SQL code test CRUD operations
1 Two relational tables
Create a new object at one end of an association Inserting a new Member and ID card
INSERT INTO tbl_Member VALUES (1234, ’Jo Smith’, False);
INSERT INTO tbl_ID_card VALUES (998877, '12-APR-2006', 1234):
Update an object at one end of an association Updating the ID card for a new date issued
UPDATE tbl_ID_card SET Date_issued TO '13-APR-2006' WHERE Memb_ID = 1234;
Delete an object at the one end of an association DELETE FROM tbl_ID_card WHERE Memb_ID = 1234;
Read a specific object at one end of an association Find an ID card by barcode number and the owing Member
SELECT M.Memb_ID, M.Memb_name, M.Memb_suspended, I.Barcode, I.Date_issued FROM tbl_Member M INNER JOIN tbl_ID_card I ON M.Memb_ID = I.Memb_ID WHERE I.Barcode = 998877;
Read a specific object at the other end of an association Find a Member by number and its ID card details
SELECT M.Memb_ID, M.Memb_name, M.Memb_suspended, I.Barcode, I.Date_issued FROM tbl_Member M INNER JOIN tbl_ID_card I ON M.Memb_ID = I.Memb_ID WHERE M.Memb_ID = 1234;
Find all instances of one object and any objects from the other end of the association Find all the Members issued cards on a particular date
SELECT M.Memb_ID, M.Memb_name, M.Memb_suspended, I.Barcode, I.Date_issued FROM tbl_Member M INNER JOIN tbl_ID_card I ON M.Memb_ID = I.Memb_ID WHERE I.Date_issued = '12-APR-2006';
2 Two typed tables using REF
Create a new object at one end of an association Inserting a new Member and ID card
INSERT INTO tbl_Member VALUES (1234, ’Jo Smith’, False);
INSERT INTO tbl_ID_card VALUES (998877, '12-APR-2006', (SELECT Member_ref FROM tbl_Member WHERE Memb_id =1234));
Update an object at one end of an association Updating the ID card for a new date issued
UPDATE tbl_ID_card SET Date_issued = '13-APR-2006' WHERE Member -> Memb_id =1234);
Delete an object at the one end of an association DELETE FROM tbl_ID_card WHERE Member -> Memb_id =1234);
Read a specific object at one end of an association Find an ID card by barcode number and the owning Member
SELECT Member -> Memb_ID, Member -> Memb_name, Member -> Memb_suspended, Barcode, Date_issued FROM tbl_ID_card WHERE Barcode = 998877;
Read a specific object at the other end of an association Find a Member by number and its ID card details
SELECT Member -> Memb_ID, Member -> Memb_name, Member -> Memb_suspended, Barcode, Date_issued FROM tbl_ID_card WHERE Member -> Memb_ID = 1234;
Find all instances of one object and any objects from the other end of the association Find all the Members issued cards on a particular date
SELECT Member -> Memb_ID, Member -> Memb_name, Member -> Memb_suspended, Barcode, Date_issued FROM tbl_ID_card WHERE Date_issued = '12-APR-2006';
3 Three relational tables
Create a new object at one end of an association Inserting a new Member and ID card
INSERT INTO tbl_Member VALUES (1234, ’Jo Smith’, False);
INSERT INTO tbl_Card VALUES (1111001, '10-NOV-2006');
INSERT INTO tbl_Owns VALUES (1111001, 1234);
Update an object at one end of an association Updating the ID card for a new date issued
UPDATE tbl_Card SET Card_date_issued = '20-APR-2006' WHERE Card_barcode = 1111001;
Delete an object at the one end of an association DELETE FROM tbl_Card WHERE Card_barcode = 1111001';
Read a specific object at one end of an association Find an ID card by barcode number and the owning Member
SELECT M.Memb_ID, M.Memb_name, M.Memb_suspended, C.Card_barcode, C.Card_date_issued FROM tbl_Member M INNER JOIN (tbl_Owns O INNER JOIN tbl_Card C ON O.Card_barcode = C.Card_barcode) ON M.Memb_ID = O.Member_ID WHERE C.Card_barcode = 1111001;
Read a specific object at the other end of an association Find a Member by number and its ID card details
SELECT M.Memb_ID, M.Memb_first_name, M.Memb_last_name, M.Memb_suspended, C.Card_barcode, C.Card_date_issued FROM tbl_Member M INNER JOIN (tbl_Owns O INNER JOIN tbl_Card C ON O.Card_barcode = C.Card_barcode) ON M.Memb_ID = O.Member_ID WHERE M.Memb_ID = 1234;
Find all instances of one object and any objects from the other end of the association Find all the Members issued cards on a particular date
SELECT M.Memb_ID, M.Memb_name, M.Memb_suspended, C.Card_barcode, C.Card_date_issued FROM tbl_Member M INNER JOIN (tbl_Owns O INNER JOIN tbl_Card C ON O.Card_barcode = C.Card_barcode) ON M.Memb_ID = O.Member_ID WHERE C.Card_date_issued = '10-NOV-2006';
4 Three typed tables with REFs instead of foreign keys <
Create a new object at one end of an association Inserting a new Member and ID card
INSERT INTO tbl_Member VALUES (1234, ’Jo Smith’, False);
INSERT INTO tbl_Card VALUES (1111001, '20-APR-2006');
INSERT INTO tbl_Owns VALUES ( SELECT Member_ref FROM tbl_Member WHERE Memb_ID = 1234, SELECT Card_ref FROM tbl_Card WHERE Card_Barcode = 1111001);
Update an object at one end of an association Updating the date issued for an ID card
UPDATE tbl_Card SET Card_date_issued = '20-APR-2006' WHERE Card_barcode = 1111001;
Delete an object at the one end of an association Removing a specified ID_card
DELETE FROM tbl_Card WHERE Card_barcode = 1111001';
Read a specific object at one end of an association Find an ID card by barcode number and the owning Member
SELECT Memb_pointer -> Memb_ID, Memb_pointer -> Memb_name, Memb_pointer -> Memb_suspended, Barcode -> Card_barcode, Barcode -> Card_date_issued FROM tbl_Owns WHERE Barcode -> Card_barcode = 1111001;
Read a specific object at the other end of an association Find a Member by number and its ID card details
SELECT Memb_pointer -> Memb_ID, Memb_pointer -> Memb_first_name, Memb_pointer -> Memb_last_name, Memb_pointer -> Memb_suspended, Barcode -> Card_barcode, Barcode -> Card_date_issued FROM tbl_Owns WHERE Memb_pointer -> Memb_ID = 1234;
Find all instances of one object and any objects from the other end of the association Find all the Members issued cards on a particular date
SELECT Memb_pointer -> Memb_ID, Memb_pointer -> Memb_name, Memb_pointer -> Memb_suspended, Barcode -> Card_barcode, Barcode -> Card_date_issued FROM tbl_Owns WHERE Barcode -> Card_date_issued = '10-NOV-2006';
5 Nested types
Create a new object at one end of an association Inserting a new Member and card
INSERT INTO tbl_Member_with_card(Member, Card) VALUES (NEW Member_type(1234, 'Jo Smith', false), NEW ID_card_type(998877, '12-APR-2006');
Update an object at one end of an association Updating the ID card for a new date issued
UPDATE tbl_Member_with_card SET Card.Date_issued = '13-APR-2006' WHERE Member .Memb_id =1234;
Delete an object at the one end of an association DELETE FROM tbl_Member_with_card WHERE Member.Memb_id =1234);
Read a specific object at one end of an association Find an ID card by barcode number and the owning Member
SELECT Member.Memb_ID, Member .Memb_name, Member .Memb_suspended, Card.Barcode, Card.Date_issued FROM tbl_Member_with_card WHERE Card.Barcode = 998877;
Read a specific object at the other end of an association Find a Member by number and its ID card details
SELECT Member.Memb_ID, Member .Memb_name, Member .Memb_suspended, Card.Barcode, Card.Date_issued FROM tbl_Member_with_card WHERE Member.Memb_ID = 1234;
Find all instances of one object and any objects from the other end of the association Find all the Members issued cards on a particular date
SELECT Member.Memb_ID, Member .Memb_name, Member .Memb_suspended, Card.Barcode, Card.Date_issued FROM tbl_Member_with_card WHERE Card.Date_issued = '12-APR-2006';

Option SQL code to test Schema changes
1 Two relational tables
Change to the maximum participation of an association Remove the UNIQUE constraint on Member_ID in ID_card
ALTER TABLE tbl_ID_card DROP CONSTRAINT uq_ID;
Change to the minimum participation of an association To enforce a minimum participation of 1 for Member, need to add an ID_card attribute to Member and add a not null constraint
ALTER TABLE tbl_Member ADD COLUMN Memb_ID_card INTEGER NOT NULL;
ALTER TABLE tbl_Member ADD CONSTRAINT fk_Memb_ID FOREIGN KEY (Memb_ID_card) REFERENCES tbl_ID_card;
Add a new attribute to a class at the one end ALTER TABLE tbl_Member ADD COLUMN Memb_Telephone CHARACTER VARYING (24);
Drop an existing attribute from a class at one end ALTER TABLE tbl_Member ADD DROP COLUMN Memb_Telephone;
Change the name of an attribute of a class at the one end ALTER TABLE tbl_Member ADD DROP COLUMN Memb_Telephone;
ALTER TABLE tbl_Member ADD COLUMN Memb_Phone CHARACTER VARYING (24);
Change the defaults value of an attribute Change the default value of an attribute of Member
ALTER Table tbl_Member ALTER COLUMN Memb_suspended SET DEFAULT TRUE;
Change the constraints of an attribute Remove the UNIQUE constraint on Member_ID in ID_card
ALTER TABLE tbl_ID_card ADD DROP CONSTRAINT uq_ID;
2 Two typed tables using REF
Change to the maximum participation of an association Remove the UNIQUE constraint on Member_ID in ID_card
ALTER TABLE tbl_ID_card DROP CONSTRAINT Member_unique;
Change to the minimum participation of an association To enforce a minimum participation of 1 for Member, need to add an ID_card attribute to Member and add a not null constraint
Not possible to change the structure of types used in typed tables
Add a new attribute to a class at the one end Not possible to change the structure of types used in typed tables
Drop an existing attribute from a class at one end Not possible to change the structure of types used in typed tables
Change the name of an attribute of a class at the one end Not possible to change the structure of types used in typed tables
Change the defaults value of an attribute Change the default value of an attribute of Member
ALTER TYPE Member ALTER ATTRIBUTE Memb_suspended SET DEFAULT TRUE;
Change the constraints of an attribute Change the default value of an attribute of Member
ALTER Table tbl_Member ADD DROP CONSTRAINT Member_unique;
3 Three relational tables
Change to the maximum participation of an association Can change maximum participation by removing unique constraints in Owns table
ALTER TABLE tbl_owns DROP CONSTRAINT UQ_Owns_Member ;
Change to the minimum participation of an association Can't enforce minimum participation of one with this model
Add a new attribute to a class at the one end ALTER TABLE tbl_Member ADD COLUMN Memb_Telephone CHARACTER VARYING (24);
Drop an existing attribute from a class at one end ALTER TABLE tbl_Member ADD DROP COLUMN Memb_Telephone;
Change the name of an attribute of a class at the one end ALTER TABLE tbl_Member ADD DROP COLUMN Memb_Telephone;
ALTER TABLE tbl_Member ADD COLUMN Memb_Phone CHARACTER VARYING (24);
Change the defaults value of an attribute Change the default value of an attribute of Member
ALTER Table tbl_Member ALTER COLUMN Memb_suspended SET DEFAULT TRUE;
Change the constraints of an attribute Change the default value of an attribute of Member
ALTER Table tbl_Card DROP CONSTRAINT pk_Card;
4 Three typed tables with REFs instead of foreign keys
Change to the maximum participation of an association Change from one to many ALTER TABLE tbl_Owns DROP CONSTRAINT owns_barcode_uq;
Change to the minimum participation of an association A minimum participation can't be enforced using simple constraints in this model
Add a new attribute to a class at the one end Add a new attribute to an ID_card
ALTER TYPE ID_card ADD ATTRIBUTE Card_status CHARACTER VARYING (10);
Drop an existing attribute from a class at one end Drop an attribute from ID_card
ALTER TYPE ID_card DROP ATTRIBUTE Card_status;
Change the name of an attribute of a class at the one end Change the name of an attribute of ID_card
ALTER TYPE ID_card DROP ATTRIBUTE Card_status; ALTER TYPE ID_card ADD ATTRIBUTE Card_valid DATE;
Change the defaults value of an attribute Change the default value of an attribute
ALTER TYPE ID_card ALTER ATTRIBUTE Card_valid SET DEFAULT CURRENT_DATE;
Change the constraints of an attribute Change the constraints of an attribute
ALTER TABLE tbl_ID_card Card_status WITH OPTIONS CONSTRAINT CHECK (Card_status IN('Active', 'Stopped'));
5 Nested types
Change to the maximum participation of an association Change the single-valued attribute Card to a multiset
ALTER TABLE tbl_Member_with_card DROP ATTRIBUTE Card;
ALTER TABLE tbl_Member_with_card ADD ATTRIBUTE Cards ID_card_type MULTISET;
Change to the minimum participation of an association To remove a minimum participation of 1 for Member, can remove the not null constraint
ALTER TABLE tbl_Member_with_card DROP CONSTRAINT card_nn;
Add a new attribute to a class at the one end ALTER TYPE Member_type ADD ATTRIBUTE Memb_Telephone CHARACTER VARYING (24);
Drop an existing attribute from a class at one end ALTER TYPE Member_type DROP ATTRIBUTE Memb_Telephone;
Change the name of an attribute of a class at the one end ALTER TYPE Member_type DROP ATTRIBUTE Memb_Telephone;
ALTER TYPE Member_type ADD ATTRIBUTE Memb_phone CHARACTER VARYING (24);
Change the defaults value of an attribute Change the default value of an attribute of Member
ALTER TYPE Member_type ALTER ATTRIBUTE Memb_suspended SET DEFAULT TRUE;
Change the constraints of an attribute ALTER TABLE tbl_Member_with_card DROP CONSTRAINT card_nn;