Object-relational feature analysis

Multi-valued attribute

Multi-valued attribute
Three options presented:
1 Relational tables (normalized)
2 Relational tables (un-normalized)
3 Array or mutliset
Option SQL code to create
1 Relational tables (normalized) CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name VARCHAR (20) NOT NULL,
Memb_last_name VARCHAR (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_address VARCHAR (20) NOT NULL,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

CREATE TABLE tbl_Member_phone(
Memb_ID INTEGER NOT NULL,
Memb_phone_number VARCHAR (12) NOT NULL,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID, Memb_phone_number),
CONSTRAINT fk_Member_phone FOREIGN KEY (Memb_ID) REFERENCES tbl_Member);
2 Relational tables (un-normalized) CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name VARCHAR (20) NOT NULL,
Memb_last_name VARCHAR (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_address VARCHAR (20) NOT NULL,
Memb_phone_1 VARCHAR (12),
Memb_phone_2 VARCHAR (12),
Memb_phone_3 VARCHAR (12),
Memb_phone_4 VARCHAR (12),
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));
3 Array or mutliset CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name VARCHAR (20) NOT NULL,
Memb_last_name VARCHAR (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_address VARCHAR (20) NOT NULL,
Memb_phone VARCHAR (12) MULTISET,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));

Option SQL code test CRUD operations
1 Relational tables (normalized)
Insert a Member's new telephone number INSERT INTO tbl_Member_phone
VALUES (1234, '02089837636');
Remove a specified Member's telephone number DELETE FROM tbl_Member_phone
WHERE Memb_ID = 1234
AND Memb_phone_number = '02089837636';
Update the data to record a change to member's phone number UPDATE tbl_Member_phone
SET Memb_phone_number = '015182762525'
WHERE Memb_ID = 1234
AND Memb_phone_number = '02089837636';
Find a member by phone number SELECT Memb_first_name, Memb_last_name, Postcode, Memb_phone_number
FROM tbl_Member INNER JOIN tbl_Member_phone
ON tbl_Member_phone.Memb_ID = tbl_Member.Memb_ID
WHERE Memb_phone = '015182762525';
2 Relational tables (un-normalized)
Insert a Member's new telephone number UPDATE tbl_Member
SET Member_phone_2 = '02089837636';
Remove a specified Member's telephone number UPDATE tbl_Member
SET Memb_phone_2 = NULL
WHERE Memb_ID = 1234;
Update the data to record a change to member's phone number Note: need to know which column the number is in
UPDATE tbl_Member
SET Memb_phone_1 = '015182762525'
WHERE Memb_ID = 1234;
Find a member by phone number SELECT Memb_first_name, Memb_last_name, Memb_address, Memb_phone_1, Memb_phone_2, Memb_phone_3, Memb_phone_4, Memb_phone_5
FROM tbl_Member
WHERE Memb_phone_1 = '015182762525'
OR Memb_phone_2 = '015182762525'
OR Memb_phone_3 = '015182762525'
OR Memb_phone_4 = '015182762525';
3 Array or mutliset
Insert a Member's new telephone number Can add a new element to the multiset, but not replace a specific element.
UPDATE tbl_Member
SET Memb_phone = Memb_phone MULTISET UNION ['015182762525']
WHERE Memb_ID = 1234;
Remove a specified Member's telephone number UPDATE tbl_Member
SET Memb_phone = Member_phone MULTISET EXCEPT ['030393837']
WHERE Memb_ID = 1234
AND '030393837' MEMBER OF Memb_phone;
Update the data to record a change to member's phone number UPDATE tbl_Member
SET Memb_phone = Memb_phone MULTISET UNION ['015182762525']
WHERE Memb_ID = 1234;
Find a member by phone number SELECT Memb_first_name, Memb_last_name, Memb_address, Memb_phone
FROM tbl_Member
WHERE '015182762525' MEMBER OF Memb_phone';

Option SQL code to test Schema changes
1 Relational tables (normalized)
Increase the number of phones No restriction on number
Reduce the number of phones No restriction on number
Change the name of an attribute of a class ALTER TABLE tbl_Member_phone
DROP COLUMN Memb_phone_number;

ALTER TABLE tbl_Member_phone
ADD COLUMN Telephone CHARACTER VARYING (12);
Adding a default value ALTER TABLE tbl_Member_phone
ALTER COLUMN Memb_phone_number
SET DEFAULT ‘999’;

ALTER TABLE tbl_Member_phone
ALTER COLUMN Memb_phone_number
DROP DEFAULT;
Adding a constraint ALTER TABLE tbl_Member_phone
ADD CONSTRAINT phone_check (Memb_phone_number)
CHECK (Memb_phone_number IN (‘02089387625’, '020898373636', '029087476464');

ALTER TABLE tbl_Member_phone
DROP CONSTRAINT phone_check;
2 Relational tables (un-normalized)
Increase the number of phones ALTER TABLE tbl_Member
ADD COLUMN Memb_phone_5 CHARACTER VARYING (12);
Reduce the number of phones ALTER TABLE tbl_Member
DROP COLUMN Memb_phone_5;
Change the name of an attribute of a class ALTER TABLE tbl_Member
DROP COLUMN Memb_phone_1;

ALTER TABLE tbl_Member
ADD COLUMN Memb_telephone_1 CHARACTER VARYING (12);
Adding a default value ALTER TABLE tbl_Member
ALTER COLUMN Memb_phone_1 SET DEFAULT ‘999’;

Repeat for 2 to 5

ALTER TABLE tbl_Member
ALTER COLUMN Memb_phone_1 DROP DEFAULT;

Repeat for 2 to 5
Adding a constraint ALTER TABLE tbl_Member
ADD CONSTRAINT phone_check1 (Memb_phone_1)
CHECK (Memb_phone_1 IN (‘02089387625’, '020898373636', '029087476464');

Repeat for 2 to 5.

ALTER TABLE tbl_Member
DROP CONSTRAINT phone_check1;

Repeat for 2 to 5.
3 Array or mutliset
Increase the number of phones No restriction on number
Reduce the number of phones No restriction on number
Change the name of an attribute of a class ALTER TABLE tbl_Member
DROP COLUMN Memb_phone;

ALTER TABLE tbl_Member
ADD COLUMN Memb_telephones CHARACTER VARYING (12) MULTISET;
Adding a default value ALTER TABLE tbl_Member
ALTER COLUMN Memb_phone SET DEFAULT MULTISET[‘999’, '02089838737'];

ALTER TABLE tbl_Member
ALTER COLUMN Memb_phone DROP DEFAULT;
Adding a constraint ALTER TABLE tbl_Member
ADD CONSTRAINT phone_check (Memb_phone)
CHECK (Memb_phone EXCEPT MULTISET [‘02089387625’, '020898373636', '029087476464'] IS NULL);

ALTER TABLE tbl_Member
DROP CONSTRAINT phone_check;