Object-relational feature analysis

An aggregation

An aggregation
Nine options presented:
1 Two relational tables with foreign keys
2 Three relational tables with association made by foreign keys
3 Two typed tables with REF in the table at the many end
4 Three typed tables with REFs in the joining table
5 A collection of REFs in the parent table
6 A collection of keys in the parent table
7 A collection of REFs in the parent table and a return fk
8 A collection of keys in the parent table and a return fk
9 A Collection of embedded types in the 'whole' table
Option SQL code to create
1 Two relational tables with foreign keys CREATE TABLE tbl_Course(
Course_code INTEGER NOT NULL,
Course_title VARCHAR (50) NOT NULL,
CONSTRAINT pk_Course PRIMARY KEY (Course_code));

CREATE TABLE tbl_Module(
Module_code INTEGER,
Module_title VARCHAR (30),
Module_Course INTEGER,
CONSTRAINT nn_Course_code NOT NULL (Module_Course),
CONSTRAINT pk_Module_code PRIMARY KEY (Module_code),
CONSTRAINT fk_Course_Module FOREIGN KEY (Module_Course) REFERENCES tbl_Course ON DELETE CASCADE);
2 Three relational tables with association made by foreign keys CREATE TABLE tbl_Course(
Course_code INTEGER,
Course_title VARCHAR (50) NOT NULL,
CONSTRAINT pk_Course PRIMARY KEY (Course_code));

CREATE TABLE tbl_Module(
Module_code INTEGER,
Module_title VARCHAR (30),
CONSTRAINT pk_Module_code PRIMARY KEY (Module_code);

CREATE TABLE tbl_Course_module(
Course_code INTEGER NOT NULL,
Module_code INTEGER NOT NULL,
CONSTRAINT uq_module UNIQUE (Module_code),
CONSTRAINT pk_course_module PRIMARY KEY (Course_code, Module_code),
CONSTRAINT fk_Course FOREIGN KEY (Course_code) REFERENCES tbl_Course ON DELETE CASCADE)
CONSTRAINT fk_Module FOREIGN KEY (Module_code) REFERENCES tbl_Module ON DELETE CASCADE);
3 Two typed tables with REF in the table at the many end CREATE TYPE Course AS (
Course_code INTEGER,
Course_title VARCHAR (50))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Course OF Course (
PRIMARY KEY (Course_code),
Course_title WITH OPTIONS CONSTRAINT course_title_nn NOT NULL,
REF is Course_ref SYSTEM GENERATED);

CREATE TYPE Module AS (
Module_code INTEGER,
Module_title VARCHAR (30),
Course_link REF(Course) SCOPE tbl_Course)
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Module OF Module (
PRIMARY KEY (Module_code),
Course_link WITH OPTIONS CONSTRAINT course_link_nn NOT NULL,
REF is Module_ref SYSTEM GENERATED);
4 Three typed tables with REFs in the joining table CREATE TYPE Course(
Course_code INTEGER,
Course_title VARCHAR (50))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Module(
Module_code INTEGER,
Module_title VARCHAR (30))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Course_module (
Course_link REF(Course) SCOPE tbl_Course,
Module_link REF(Module) SCOPE tbl_Module )
INSTANTIABLE FINAL REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Course OF Course (
PRIMARY KEY (Course_code),
REF is Course_ref SYSTEM GENERATED);

CREATE TABLE tbl_Module OF Module (
PRIMARY KEY (Module_code),
REF is Module_ref SYSTEM GENERATED);

CREATE TABLE tbl_Course_module OF Course_module (
PRIMARY KEY (Course_link, Module_link),
Course_link WITH OPTIONS CONSTRAINT cm_Course_nn NOT NULL,
Course_link WITH OPTIONS CONSTRAINT cm_Course_fk FOREIGN KEY REFERENCES tbl_Course ON DELETE CASCADE,
Module_link WITH OPTIONS CONSTRAINT cm_Module_nn NOT NULL,
Module_link WITH OPTIONS CONSTRAINT cm_Module_fk FOREIGN KEY REFERENCES tbl_Module ON DELETE CASCADE,
REF is Course_module_ref SYSTEM GENERATED);
5 A collection of REFs in the parent table CREATE TYPE Module(
Module_code INTEGER,
Module_title VARCHAR (30))
INSTANTIABLE NOT FINAL
REF IS Module_ref SYSTEM GENERATED;

CREATE TYPE Course(
Course_code INTEGER,
Course_title VARCHAR (50),
Course_modules REF(Module) MULTISET )
INSTANTIABLE NOT FINAL
REF IS Course_ref SYSTEM GENERATED;

CREATE TABLE tbl_Module OF Module (
PRIMARY KEY (Module_code),
REF is Module_ref SYSTEM GENERATED);

CREATE TABLE tbl_Course OF Course (
PRIMARY KEY (Course_code),
REF is Course_ref SYSTEM GENERATED,
Course_modules WITH OPTIONS SCOPE tbl_Modules);
6 A collection of keys in the parent table CREATE TABLE tbl_Module(
Module_code INTEGER,
Module_title VARCHAR (30),
CONSTRAINT pk_Module_code PRIMARY KEY (Module_code);

CREATE TABLE tbl_Course(
Course_code INTEGER NOT NULL,
Course_title VARCHAR (50) NOT NULL,
Course_modules INTEGER MULTISET,
CONSTRAINT pk_Course PRIMARY KEY (Course_code));
7 A collection of REFs in the parent table and a return fk CREATE TYPE Module(
Module_code INTEGER,
Module_title VARCHAR (30),
Module_course INTEGER)
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Course(
Course_code INTEGER,
Course_title VARCHAR (50),
Course_modules REF(Module) MULTISET )
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Module OF Module (
PRIMARY KEY (Module_code),
Module_course WITH OPTIONS CONSTRAINT fk_mod_course FOREIGN KEY (Module_course) REFERENCES tbl_Course ON DELETE CASCADE,
REF is Module_ref SYSTEM GENERATED);

CREATE TABLE tbl_Course OF Course (
PRIMARY KEY (Course_code),
Course_title WITH OPTIONS CONSTRAINT course_title_nn NOT NULL,
REF is Course_ref SYSTEM GENERATED);
8 A collection of keys in the parent table and a return fk CREATE TABLE tbl_Course(
Course_code INTEGER,
Course_title VARCHAR (50) NOT NULL,
Course_modules INTEGER MULTISET,
CONSTRAINT pk_Course PRIMARY KEY (Course_code));

CREATE TABLE tbl_Module(
Module_code INTEGER,
Module_title VARCHAR (30),
Module_course INTEGER,
CONSTRAINT fk_Course FOREIGN KEY (Module_course) REFERENCES tbl_Course ON DELETE CASCADE,
CONSTRAINT nn_course_code NOT NULL (Module_course),
CONSTRAINT pk_Module_code PRIMARY KEY (Module_code);
9 A Collection of embedded types in the 'whole' table CREATE TYPE Module(
Module_code INTEGER,
Module_title VARCHAR (30))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Course(
Course_code INTEGER,
Course_title VARCHAR (50))
INSTANTIABLE NOT FINAL
REF IS SYSTEM GENERATED;

CREATE TYPE Course_with_modules (
Course_details Course,
Modules Module MULTISET )
INSTANTIABLE FINAL REF IS SYSTEM GENERATED;

CREATE TABLE tbl_Course OF Course_with_modules (
REF is Course_ref SYSTEM GENERATED);

Option SQL code test CRUD operations
1 Two relational tables with foreign keys
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course VALUES (1234, 'Database Development');
INSERT INTO tbl_Module VALUES (123401, 'Database Applications 1', 123);
INSERT INTO tbl_Module VALUES (123402, 'Database Applications 2', 123);
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = 'Database Development'
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

DELETE tbl_Module
WHERE Module_code = 123404;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN tbl_Module M
ON C.Course_code = M.Course_code
WHERE M.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN tbl_Module M
ON C.Course_code = M.Course_code
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT M.Module_code, M.Module_title
FROM tbl_Module M
WHERE Module_title LIKE '%Network%';
2 Three relational tables with association made by foreign keys
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course VALUES (1234, 'Database Development');
INSERT INTO tbl_Module VALUES (123401, 'Database Applications 1');
INSERT INTO tbl_Module VALUES (123402, 'Database Applications 2');
INSERT INTO tbl_Course_module VALUES (1234, 123401);
INSERT INTO tbl_Course_module VALUES (1234, 123402);
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = 'Database Development'
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

DELETE tbl_Module
WHERE Module_code = 123404;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN (tbl_Module M INNER JOIN tbl_Course_module CM
ON M.Module_code = CM.Module_code)
ON C.Course_code = CM.Course_code
WHERE M.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN (tbl_Module M INNER JOIN tbl_Course_module CM
ON M.Module_code = CM.Module_code)
ON C.Course_code = CM.Course_code
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT M.Module_code, M.Module_title
FROM tbl_Module M WHERE Module_title LIKE '%Network%';
3 Two typed tables with REF in the table at the many end
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course VALUES (1234, 'Database Development');
INSERT INTO tbl_Module
VALUES (123401, 'Database Applications 1',
(SELECT Course_ref FROM tbl_Course WHERE Course_code = 1234));
INSERT INTO tbl_Module
VALUES (123402, 'Database Development', (SELECT Course_ref FROM tbl_Course WHERE Course_code = 1234));
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = 'Database Development'
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

DELETE tbl_Module
WHERE Module_code = 123404;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT M.Course_link -> Course_code, M.Course_link -> Course_title, M.Module_code, M.Module_title
FROM tbl_Module M
WHERE M.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT M.Course_link -> Course_code, M.Course_link -> Course_title, M.Module_code, M.Module_title
FROM tbl_Module M
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT M.Module_code, M.Module_title FROM tbl_Module M WHERE Module_title LIKE '%Network%';
4 Three typed tables with REFs in the joining table
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course VALUES (1234, 'Database Development');
INSERT INTO tbl_Module VALUES (123401, 'Database Applications 1');
INSERT INTO tbl_Module VALUES (123402, 'Database Development');
INSERT INTO tbl_Course_Module
VALUES (SELECT Module_ref FROM tbl_Module WHERE Module_code = 123401),
(SELECT Course_ref FROM tbl_Course WHERE Course_code = 1234));
INSERT INTO tbl_Course_Module
VALUES (SELECT Module_ref FROM tbl_Module WHERE Module_code = 123402),
(SELECT Course_ref FROM tbl_Course WHERE Course_code = 1234));
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = ‘Database Systems Development’
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

DELETE FROM tbl_Module
WHERE Module_code = 123402;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT CM.Course_link -> Course_code, CM.Course_link -> Course_title, CM.Module_link -> Module_code, CM.Module_link -> Module_title
FROM tbl_Course_Module CM
WHERE CM.Module_link -> Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT CM.Course_link -> Course_code, CM.Course_link -> Course_title, CM.Module_link -> Module_code, CM.Module_link -> Module_title
FROM tbl_Course_Module CM
WHERE CM.Course_link -> Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title SELECT Module_code, Module_title FROM tbl_Module WHERE Module_title LIKE '%Network%';
5 A collection of REFs in the parent table
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Module
VALUES (123401, 'Database Applications 1');
INSERT INTO tbl_Module
VALUES (123402, 'Database Development');
INSERT INTO tbl_Course
VALUES (1234, 'Database Development',
MULTISET [(SELECT Module_ref FROM tbl_Module
WHERE Module_code = 123401),
(SELECT Module_ref FROM tbl_Module WHERE Module_code = 123402) ];
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = ‘Database Systems Development’
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

UPDATE tbl_Course
SET Course_modules = Course_modules
EXCEPT MULTISET [ (SELECT Module_ref FROM tbl_Module
WHERE Module_code = 123402]);
DELETE tbl_Module
WHERE Module_code = 123402;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_ref -> Module_code, M.Module_ref -> Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS M(Module_ref)
WHERE M.Module_ref -> Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_ref -> Module_code, M.Module_ref -> Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS M(Module_ref)
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT Module_code, Module_title
FROM tbl_Module
WHERE Module_title LIKE '%Network%';
6 A collection of keys in the parent table
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Module
VALUES (123401, 'Database Applications 1');
INSERT INTO tbl_Module
VALUES (123402, 'Database Development');
INSERT INTO tbl_Course
VALUES (1234, 'Database Development',
MULTISET [123401, 123402];
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = ‘Database Systems Development’
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

UPDATE tbl_Course
SET Course_modules = Course_modules
EXCEPT MULTISET [123402];
DELETE tbl_Module
WHERE Module_code = 123402;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS MC(Module_code)
INNER JOIN tbl_Module M ON MC.Module_code = M.Module_code
WHERE M.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS MC(Module_code)
INNER JOIN tbl_Module M ON MC.Module_code = M.Module_code
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT Module_code, Module_title
FROM tbl_Module
WHERE Module_title LIKE '%Network%';
7 A collection of REFs in the parent table and a return fk
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course
VALUES (1234, 'Database Development', NULL);
INSERT INTO tbl_Module
VALUES (123401, 'Database Appl
ications 1', 1234);
INSERT INTO tbl_Module
VALUES (123402, 'Database Development', 1234);
UPDATE tbl_Course
SET Course_modules = MULTISET [
(SELECT Module_ref FROM tbl_Module WHERE Module_course = 1234) ]
WHERE Course_code = 1234;
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = ‘Database Systems Development’
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

UPDATE tbl_Course
SET Course_modules = Course_modules
EXCEPT MULTISET [ (SELECT Module_ref FROM tbl_Module WHERE Module_code = 123402)];
DELETE tbl_Module
WHERE Module_code = 123402;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_ref -> Module_code, M.Module_ref -> Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS M(Module_ref)
WHERE M.Module_ref -> Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_ref -> Module_code, M.Module_ref -> Module_title
FROM tbl_Course C UNNEST (C.Course_modules) AS M(Module_ref)
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title SELECT Module_code, Module_title FROM tbl_Module WHERE Module_title LIKE '%Network%';
8 A collection of keys in the parent table and a return fk
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course
VALUES (1234, 'Database Development', NULL);
INSERT INTO tbl_Module
VALUES (123401, 'Database Applications 1', 1234);
INSERT INTO tbl_Module
VALUES (123402, 'Database Development', 1234);
UPDATE tbl_Course
SET Course_modules = MULTISET [
(SELECT Module_code FROM tbl_Module WHERE Module_course = 1234) ]
WHERE Course_code = 1234;
Update an object at the part end of an aggregation Change a module's title

UPDATE tbl_Module
SET Module_title = ‘Database Systems Development’
WHERE Module_code = 123402;
Delete an object at the part end of an aggregation Delete a module from a course

UPDATE tbl_Course
SET Course_modules = Course_modules
EXCEPT MULTISET [123402];
DELETE tbl_Module
WHERE Module_code = 123402;
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN tbl_Module M
ON C.Course_code = M.Course_code
WHERE M.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module_code, M.Module_title
FROM tbl_Course C INNER JOIN tbl_Module M
ON C.Course_code = M.Course_code
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT Module_code, Module_title
FROM tbl_Module
WHERE Module_title LIKE '%Network%';
9 A Collection of embedded types in the 'whole' table
Create a new whole object with its parts in an aggregation Inserting a new Course with its modules

INSERT INTO tbl_Course VALUES (
NEW Course(1234, 'Database Development'), MULTISET[NEW Module (123401, 'Database Applications 1'),
NEW Module (123402, 'Database Development')]);
Update an object at the part end of an aggregation Change a module's title - need to add the module to the set (problem that old module is still in the set - can delete it first)

UPDATE tbl_Course
SET Modules = Modules UNION
MULTISET [NEW Module(123402, ‘Database Systems Development’)];
Delete an object at the part end of an aggregation Delete a module from a course

UPDATE tbl_Course
SET Modules = Modules EXCEPT MULTISET [NEW Module (123402, 'Database Development')];
Read a specific object at the part end of an aggregation Find a module by module code and the course it belongs to

SELECT C.Course_code, C.Course_title,
M.Module.Module_code, M.Module.Module_title FROM tbl_Course C UNNEST (C.Modules) AS M(Module)
WHERE M.Module.Module_code = 123402;
Read a specific object at the whole end of an aggregation and all its parts Find a course by course code and all its modules

SELECT C.Course_code, C.Course_title, M.Module.Module_code, M.Module.Module_title
FROM tbl_Course C UNNEST (C.Modules) AS M(Module)
WHERE C.Course_code = 1234;
Find all instances at the part end of an aggregation Find all the modules with the word 'Network' as part of their title

SELECT M.Module.Module_code, M.Module.Module_title
FROM tbl_Course C UNNEST (C.Modules) AS M(Module)
WHERE M.Module.Module_title LIKE '%Network%';

Option SQL code to test Schema changes
1 Two relational tables with foreign keys
Change to the maximum participation of an aggregation Add a unique constraint on course

ALTER TABLE tbl_Module
ADD CONSTRAINT Course_uq UNIQUE (Module_course);
Change to the minimum participation of an aggregation Drop the not null constraint from course

ALTER TABLE tbl_Module
DROP CONSTRAINT nn_Course_code;
Add a new attribute to a class at the whole end ALTER TABLE tbl_Course
ADD COLUMN Examination_board CHARACTER VARYING (20);
Add a new attribute to a class at the part end ALTER TABLE tbl_Module
ADD COLUMN Module_Level NUMBER(1);
Drop an existing attribute from a class at the whole end ALTER TABLE tbl_Course
DROP COLUMN Course_description;
Drop an existing attribute from a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level NUMBER(1);
Change the name of an attribute of a class at the whole end ALTER TABLE tbl_Course Course
DROP COLUMN Course_description;
ALTER TABLE tbl_Course Course
ADD COLUMN Course_text CHARACTER VARYING (500);
Change the name of an attribute of a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level;
ALTER TABLE tbl_Module
ADD COLUMN Module_difficulty NUMBER(1);
Change the defaults value of an attribute at the whole end ALTER TABLE tbl_Course
ALTER COLUMN Course_credit
SET DEFAULT 20;
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level
SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Module
ADD CONSTRAINT Module_level_check CHECK(Module_level IN(1, 2, 3, 4, 5, 6));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Course
ADD CONSTRAINT Course_credit_check CHECK(Course_credit IN(5, 10, 15, 20));
2 Three relational tables with association made by foreign keys
Change to the maximum participation of an aggregation Add a unique constraint on course

ALTER TABLE tbl_Course_Module
ADD CONSTRAINT uq_course UNIQUE (Course_code),
Change to the minimum participation of an aggregation Can't enforce minmum participation with this model
Add a new attribute to a class at the whole end ALTER TABLE tbl_Course
ADD COLUMN Examination_board VARCHAR (20);
Add a new attribute to a class at the part end ALTER TABLE tbl_Module
ADD COLUMN Module_Level NUMBER(1);
Drop an existing attribute from a class at the whole end ALTER TABLE tbl_Course
DROP COLUMN Course_description;
Drop an existing attribute from a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level NUMBER(1);
Change the name of an attribute of a class at the whole end ALTER TABLE tbl_Course Course
DROP COLUMN Course_description;
ALTER TABLE tbl_Course Course
ADD COLUMN Course_text VARCHAR (500);
Change the name of an attribute of a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level;
ALTER TABLE tbl_Module
ADD COLUMN Module_difficulty NUMBER(1);
Change the defaults value of an attribute at the whole end ALTER TABLE tbl_Course
ALTER COLUMN Course_credit
SET DEFAULT 20;
Change the defaults value of an attribute at the part end ALTER TABLE Module
ALTER COLUMN Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Module ADD CONSTRAINT Module_level_check CHECK(Module_level IN(1, 2, 3, 4, 5, 6));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Course ADD CONSTRAINT Course_credit_check CHECK(Course_credit IN(5, 10, 15, 20));
3 Two typed tables with REF in the table at the many end
Change to the maximum participation of an aggregation Add a unique constraint on course

ALTER TABLE tbl_Module
ADD CONSTRAINT uq_course UNIQUE (Course_link),
Change to the minimum participation of an aggregation Drop not null constraint on course

ALTER TABLE tbl_Module
DROP CONSTRAINT Course_link_nn;
Add a new attribute to a class at the whole end Not possible to change the structure of a type used in a typed table
Add a new attribute to a class at the part end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the whole end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the part end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the whole end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the part end Not possible to change the structure of a type used in a typed table
Change the defaults value of an attribute at the whole end ALTER TYPE tbl_Course
ALTER ATTRIBUTE Course_credit SET DEFAULT 20;
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Module
ADD CONSTRAINT Module_level_check CHECK(Module_level IN(1, 2, 3, 4, 5, 6));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Course
ADD CONSTRAINT Course_credit_check CHECK(Course_credit IN(5, 10, 15, 20));
4 Three typed tables with REFs in the joining table
Change to the maximum participation of an aggregation No limits to maximum participation with this implementation
Change to the minimum participation of an aggregation Minimum participation not enforced with this implementation
Add a new attribute to a class at the whole end Not possible to change the structure of a type used in a typed table
Add a new attribute to a class at the part end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the whole end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the part end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the whole end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the part end Not possible to change the structure of a type used in a typed table
Change the defaults value of an attribute at the whole end ALTER TYPE Course
ALTER ATTRIBUTE Course_level SET DEFAULT 'UNDERGRADUATE';
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Course Course_level WITH OPTIONS CONSTRAINT CHECK (Course_level IN('UNDERGRADUATE', 'POSTGRADUATE'));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Module Module_level WITH OPTIONS CONSTRAINT CHECK (Module_level IN(1, 2, 3, 4));
5 A collection of REFs in the parent table
Change to the maximum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Course_Modules) < 100);
Change to the minimum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) > 0);
Add a new attribute to a class at the whole end Not possible to change the structure of a type used in a typed table
Add a new attribute to a class at the part end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the whole end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the part end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the whole end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the part end Not possible to change the structure of a type used in a typed table
Change the defaults value of an attribute at the whole end ALTER TYPE Course
ALTER ATTRIBUTE Course_level SET DEFAULT 'UNDERGRADUATE';
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Course Course_level WITH OPTIONS CONSTRAINT CHECK (Course_level IN('UNDERGRADUATE', 'POSTGRADUATE'));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Module Module_level WITH OPTIONS CONSTRAINT CHECK (Module_level IN(1, 2, 3, 4));
6 A collection of keys in the parent table
Change to the maximum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Course_Modules) < 100);
Change to the minimum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) > 0);
Add a new attribute to a class at the whole end ALTER TABLE tbl_Course
ADD COLUMN Examination_board CHARACTER VARYING (20);
Add a new attribute to a class at the part end ALTER TABLE tbl_Module
ADD COLUMN Module_Level NUMBER(1);
Drop an existing attribute from a class at the whole end ALTER TABLE tbl_Course
DROP COLUMN Course_description;
Drop an existing attribute from a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level NUMBER(1);
Change the name of an attribute of a class at the whole end ALTER TABLE tbl_Course Course
DROP COLUMN Course_description;
ALTER TABLE tbl_Course Course
ADD COLUMN Course_text CHARACTER VARYING (500);
Change the name of an attribute of a class at the part end ALTER TABLE tbl_Module
DROP COLUMN Module_Level;
ALTER TABLE tbl_Module
ADD COLUMN Module_difficulty NUMBER(1);
Change the defaults value of an attribute at the whole end ALTER TABLE tbl_Course
ALTER COLUMN Course_credit
SET DEFAULT 20;
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Module ADD CONSTRAINT Module_level_check CHECK(Module_level IN(1, 2, 3, 4, 5, 6));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Module Module_level WITH OPTIONS CONSTRAINT CHECK (Module_level IN(1, 2, 3, 4));
7 A collection of REFs in the parent table and a return fk
Change to the maximum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Course_Modules) < 100);
Change to the minimum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) > 0);
Add a new attribute to a class at the whole end Not possible to change the structure of a type used in a typed table
Add a new attribute to a class at the part end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the whole end Not possible to change the structure of a type used in a typed table
Drop an existing attribute from a class at the part end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the whole end Not possible to change the structure of a type used in a typed table
Change the name of an attribute of a class at the part end Not possible to change the structure of a type used in a typed table
Change the defaults value of an attribute at the whole end ALTER TYPE Course
ALTER ATTRIBUTE Course_level SET DEFAULT 'UNDERGRADUATE';
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Course Course_level WITH OPTIONS CONSTRAINT CHECK (Course_level IN('UNDERGRADUATE', 'POSTGRADUATE'));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Module Module_level WITH OPTIONS CONSTRAINT CHECK (Module_level IN(1, 2, 3, 4));
8 A collection of keys in the parent table and a return fk
Change to the maximum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Course_Modules) < 100);
Change to the minimum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) > 0);
Add a new attribute to a class at the whole end ALTER TABLE tbl_Module
ADD COLUMN Module_Level NUMBER(1);
Add a new attribute to a class at the part end ALTER TABLE tbl_Course
DROP COLUMN Course_description;
Drop an existing attribute from a class at the whole end ALTER TABLE tbl_Module
DROP COLUMN Module_Level NUMBER(1);
Drop an existing attribute from a class at the part end ALTER TABLE tbl_Course Course
DROP COLUMN Course_description;
ALTER TABLE tbl_Course Course
ADD COLUMN Course_text CHARACTER VARYING (500);
Change the name of an attribute of a class at the whole end ALTER TABLE tbl_Module
DROP COLUMN Module_Level;
ALTER TABLE tbl_Module
ADD COLUMN Module_difficulty NUMBER(1);
Change the name of an attribute of a class at the part end ALTER TABLE tbl_Course
ALTER COLUMN Course_credit
SET DEFAULT 20;
Change the defaults value of an attribute at the whole end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the defaults value of an attribute at the part end ALTER TABLE tbl_Module
ADD CONSTRAINT Module_level_check CHECK(Module_level IN(1, 2, 3, 4, 5, 6));
Change the constraints of an attribute at the whole end ALTER TABLE tbl_Course ADD CONSTRAINT Course_credit_check CHECK(Course_credit IN(5, 10, 15, 20));
Change the constraints of an attribute at the part end ALTER TABLE tbl_Module Module_level WITH OPTIONS CONSTRAINT CHECK (Module_level IN(1, 2, 3, 4));
9 A Collection of embedded types in the 'whole' table
Change to the maximum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) < 100);
Change to the minimum participation of an aggregation ALTER TABLE tbl_Course
ADD CONSTRAINT check_max_modules CHECK (ELEMENTS(Modules) > 0);
Add a new attribute to a class at the whole end ALTER TYPE Course
ADD ATTRIBUTE Examination_board CHARACTER VARYING (20);
Add a new attribute to a class at the part end ALTER TYPE Module
ADD ATTRIBUTE Module_Level NUMBER(1);
Drop an existing attribute from a class at the whole end ALTER TYPE Course
DROP ATTRIBUTE Course_description;
Drop an existing attribute from a class at the part end ALTER TYPE Module
DROP ATTRIBUTE Module_Level;
Change the name of an attribute of a class at the whole end ALTER TYPE Course
DROP ATTRIBUTE Course_description;
ALTER TYPE Course
ADD ATTRIBUTE Course_text CHARACTER VARYING (500);
Change the name of an attribute of a class at the part end ALTER TYPE Module
DROP ATTRIBUTE Module_Level;
ALTER TYPE Module
ADD ATTRIBUTE Module_difficulty NUMBER(1);
Change the defaults value of an attribute at the whole end ALTER TYPE Course
ALTER ATTRIBUTE Course_level SET DEFAULT 'UNDERGRADUATE';
Change the defaults value of an attribute at the part end ALTER TYPE Module
ALTER ATTRIBUTE Module_level SET DEFAULT 2;
Change the constraints of an attribute at the whole end Can't add table constraints within nested types
Change the constraints of an attribute at the part end Can't add table constraints within nested types