In a nutshell, create an ASSERTION
to ensure that at no time can the business rule be violated e.g. Full Standard SQL-92 syntax:
CREATE TABLE T1
(
a INTEGER NOT NULL,
d INTEGER NOT NULL,
UNIQUE (a, d)
);
CREATE TABLE T2
(
b INTEGER NOT NULL,
d INTEGER NOT NULL,
UNIQUE (b, d)
);
CREATE TABLE T3
(
a INTEGER NOT NULL,
b INTEGER NOT NULL,
c INTEGER NOT NULL,
UNIQUE (a, b, c)
);
CREATE ASSERTION no_a_and_b_should_be_combined_with_a_c_where_a_and_b_have_different_ds
CHECK (
NOT EXISTS (
SELECT *
FROM T3
WHERE NOT EXISTS (
SELECT T1.d
FROM T1
WHERE T1.a = T3.a
INTERSECT
SELECT T2.d
FROM T2
WHERE T3.b = T3.b
)
)
);
The bad news is that no commercial (or otherwise?) SQL product supports CREATE ASSERTION
.
Most industrial-strength SQL products support triggers: one could implement the above in a trigger on each applicable table. MS Access is the only commercial product I know of that supports subqueries in CHECK
constraints but I don't consider it to be industrial-strength. There are further workarounds e.g. forcing users to update tables only via stored procedures that can be shown to never leave the database in an illegal state.
I'd probably start with something like this:
create table Matrix (
id integer primary key auto_increment,
Name varchar(30) not null,
Description varchar(30) not null
);
create table Updates ( -- Note, singular not possible, conflicts with keyword 'update'.
id integer primary key auto_increment,
Name varchar(20) not null
);
create table UpdateStatus (
id integer primary key auto_increment,
Name varchar(20)
);
create table Matrix_Update (
id_Matrix integer not null,
id_Updates integer not null,
id_UPdateStatus integer not null,
--
foreign key (id_Matrix ) references Matrix (id),
foreign key (id_Updates ) references Updates (id),
foreign key (id_UpdateStatus) references UpdateStatus(id)
);
You then fill your Matrix entries with
insert into Matrix (id, Name, Description) values ( 1, 'Server01', 'First Server');
insert into Matrix (id, Name, Description) values ( 2, 'Server02', 'Second Server');
Similarly, the Updates are filled like so
insert into Updates (id, Name) values ( 1, 'Q1 Update');
insert into Updates (id, Name) values ( 2, 'Q2 Update');
insert into Updates (id, Name) values ( 3, 'Q3 Update');
Finally, insert the possible Update Stati
insert into UpdateStatus (id, Name) values (1, 'Done');
insert into UpdateStatus (id, Name) values (2, 'Incomplete');
Now, you have the framework to assemble your "configuration":
insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 1, 1);
insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 2, 2);
insert into Matrix_Update (id_Matrix, id_Updates, id_UpdateStatus) values ( 1, 3, 2);
This "configuration" can then be queried with a pivot query:
-- Pivot Query
select
Matrix.Name MatrixName,
Matrix.Description MatrixDescription,
group_concat(if(Updates.id = 1, UpdateStatus.Name, null )) Status1,
group_concat(if(Updates.id = 2, UpdateStatus.Name, null )) Status2,
group_concat(if(Updates.id = 3, UpdateStatus.Name, null )) Status3
from
Matrix_Update join
Matrix on Matrix_Update.id_Matrix= Matrix.id join
UpdateStatus on Matrix_Update.id_UpdateStatus = UpdateStatus.id join
Updates Updates on Matrix_Update.id_Updates = Updates.id
group by
Matrix.Name,
Matrix.Description;
I want the Name field from Updates to always be an exact replica of what is in Matrix.
With this design, this is no problem, since the Name is not stored redundantly.
Best Answer
The key is actually
AE
. The proof is simple, a key for definition is a minimal set of attributes whose closure contains all the attributes of the table. If you calculate the closure ofA
with respect to the given functional dependencies you will find:that does not contain the attribute
E
. SoA
is not a key, andE
must be present in any key ofR
. And since:then
AE
is a key, and it is minimal (you cannot remove any attribute from it without losing the property of determining all the other attributes of the relation).