Object-relational feature analysis

A structured attribute

A structured attribute
Three options presented:
1 Relational table
2 Structured Type
3 Row Type
Option SQL code to create
1 Relational table CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name CHARACTER VARYING (20) NOT NULL,
Memb_last_name CHARACTER VARYING (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
House CHARACTER VARYING (20) NOT NULL,
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10) NOT NULL,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID));
2 Structured Type CREATE TYPE type_UK_Address AS (
House CHARACTER VARYING (20),
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10))
INSTANTIABLE FINAL;

CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name CHARACTER VARYING (20) NOT NULL,
Memb_last_name CHARACTER VARYING (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_address type_UK_address,
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID);
3 Row Type CREATE TABLE tbl_Member(
Memb_ID INTEGER NOT NULL,
Memb_first_name CHARACTER VARYING (20) NOT NULL,
Memb_last_name CHARACTER VARYING (20) NOT NULL,
Memb_suspended BOOLEAN DEFAULT FALSE,
Memb_address ROW(
House CHARACTER VARYING (20),
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10)),
CONSTRAINT pk_Member PRIMARY KEY (Memb_ID);

Option SQL code test CRUD operations
1 Relational table
Insert a new Member INSERT INTO tbl_Member
VALUES (1234, ’Jo’, ’Smith’, False, ‘27’, ‘New Street’, ‘Worcester’, ‘Worcs’, ‘WR1 9GD’);
Remove a specified Member DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Update the data to record a change to member's address UPDATE tbl_Member
SET House = ‘94’
WHERE Memb_ID = 1234;
Find a member by by post code SELECT Memb_first_name, Memb_last_name, Postcode FROM tbl_Member
WHERE Postcode LIKE 'WR%';
2 Structured Type
Insert a new Member INSERT INTO tbl_Member
VALUES (1234, ’Jo’, ’Smith’, False, NEW UK_address(‘27’, ‘New Street’, ‘Worcester’, ‘Worcs’, ‘WR1 9GD’));
Remove a specified Member DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Update the data to record a change to member's address UPDATE tbl_Member
SET Memb_address = NEW UK_address(‘33’, ‘Old Street’, ‘Leominster’, ‘Herefordshire’, ‘HR1 3JT) WHERE Memb_ID = 1234;
Find a member by by post code SELECT M.Memb_first_name, M.Memb_last_name, M.Memb_address.Postcode
FROM tbl_Member M
WHERE M.Memb_address.Postcode LIKE 'WR%';
3 Row Type
Insert a new Member INSERT INTO tbl_Member
VALUES (1234, ’Jo’, ’Smith’, False, ROW (‘27’, ‘New Street’, ‘Worcester’, ‘Worcs’, ‘WR1 9GD’));
Remove a specified Member DELETE FROM tbl_Member
WHERE Memb_ID = 1234;
Update the data to record a change to member's address UPDATE tbl_Member
SET Memb_address = ROW(
‘33’, ‘Old Street’, ‘Leominster’, ‘Herefordshire’, ‘HR1 3JT')
WHERE Memb_ID = 1234;
Find a member by by post code SELECT M.Memb_first_name, M.Memb_last_name, M.Memb_address.Postcode
FROM tbl_Member M
WHERE M.Memb_address.Postcode LIKE 'WR%';

Option SQL code to test Schema changes
1 Relational table
Adding a new attribute to the address ALTER TABLE Member
ADD COLUMN Country CHARACTER VARYING (24);
Removing an attribute from the address ALTER TABLE Member
DROP COLUMN Country;
Change the name of an attribute of the address ALTER TABLE Member
DROP COLUMN Country;
ALTER TABLE Member
ADD COLUMN State CHARACTER VARYING (24);
Adding a default value ALTER TABLE Member
ALTER COLUMN Country
SET DEFAULT ‘England’;
Adding a constraint ALTER TABLE Member
ADD CONSTRAINT country_check (Country)
CHECK (Country IN (‘England’, ‘Wales’, ‘Scotland’, ‘Northern Ireland’));
2 Structured Type
Adding a new attribute to the address ALTER TYPE UK_Address
ADD ATTRIBUTE Country CHARACTER VARYING (24);
Removing an attribute from the address ALTER TYPE UK_Address
DROP ATTRIBUTE Country;
Change the name of an attribute of the address ALTER TYPE UK_Address
DROP ATTRIBUTE Country;

ALTER TYPE UK_Address
ADD ATTRIBUTE State CHARACTER VARYING (24);
Adding a default value ALTER TYPE UK_Address
ALTER COLUMN Country
SET DEFAULT ‘England’;
Adding a constraint Can't add a constraint to the parts of a type
3 Row Type
Adding a new attribute to the address ALTER TABLE Member
DROP COLUMN Memb_Address;

ALTER TABLE Member
ADD COLUMN
Memb_address ROW(
House CHARACTER VARYING (20),
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10),
Country CHARACTER VARYING (20));
Removing an attribute from the address ALTER TABLE Member
DROP COLUMN Memb_Address;

ALTER TABLE Member
ADD COLUMN
Memb_address ROW(
House CHARACTER VARYING (20),
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10));
Change the name of an attribute of the address Need to drop and recreate the address
Adding a default value ALTER TABLE Member
DROP COLUMN Memb_Address;

ALTER TABLE Member
ADD COLUMN
Memb_address ROW(
House CHARACTER VARYING (20),
Street CHARACTER VARYING (30),
Town CHARACTER VARYING (30),
County CHARACTER VARYING (30),
Postcode CHARACTER VARYING (10)
Country CHARACTER VARYING (20) DEFAULT ‘England’);
Adding a constraint Can't add a constraint to the parts of a row