Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have the following functional dependencies which are in BCNF:

a,b -> c
a -> d
b -> d

With the additional constraint, that no a and b should be combined with a c, where a and b have different ds.

Example:

a | d   b | d   a | b | c
-----   -----   ---------
1 | 3   5 | 3   1 | 5 | 6
2 | 4           2 | 5 | 7

The first row in a,b,c is allowed (1->3,5->3), but the second row is forbidden, since (2->4,5->3) 4 != 3.

This additional constraint can have two effects on my data. For each a,b,c there are two redundant ways of determining the d. There can be data which violates the constraint. How can my schema reflect this additional constraint?

share|improve this question
2  
Which DBMS are you using? –  Daniel Hilgarth Dec 14 '11 at 13:55
1  
In Oracle, there are function based constraints –  Daniel Hilgarth Dec 14 '11 at 13:56
4  
Table 2 in your example should be b | d? –  Dems Dec 14 '11 at 13:57
    
All FDs are constraints, but not all constraints are FDs. Therefore, it is delusory to think that one can express any and all constraints using only FDs. –  user5097 Dec 14 '11 at 16:39
    
For everybody not on Oracle (well, probably), there are insert triggers (before or after). –  Clockwork-Muse Dec 14 '11 at 17:39
add comment

migrated from stackoverflow.com Dec 14 '11 at 14:47

This question came from our site for professional and enthusiast programmers.

3 Answers

up vote 3 down vote accepted

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.

share|improve this answer
    
The check constraint can also call a UDF that execute that query. However, this is not a practical solution. As you noted, most RDBMS platforms either don't support this solution, or support it with many caveats. –  Nick Chammas Jan 10 '12 at 17:57
add comment

In a nutshell, introduce d into the third table to enable vanilla foreign key constraints e.g. Transitional 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, 
 d INTEGER NOT NULL, 
 UNIQUE (a, b, c),
 FOREIGN KEY (a, d) REFERENCES T1 (a, d), 
 FOREIGN KEY (b, d) REFERENCES T2 (b, d)
);
share|improve this answer
    
How is a -> d preserved in this? –  ypercube Dec 14 '11 at 18:36
    
@ypercube - T1 covers that dependency, no? Onedaywhen, your last table should be called T3, not T2. Good solution. –  Nick Chammas Dec 14 '11 at 19:18
    
This gives me unwanted redundency. This schema would not be in BCNF anymore. –  johannes Dec 15 '11 at 0:12
    
@johannes - It's a small price to pay for enforcing your constraints in an efficient and simple manner. In practical terms, the only down-side to this approach is the extra cost of storage for the repeated d column in T3, which is a negligible cost honestly. BCNF is not the end-all and be-all of database design, especially when you are implementing and not just designing in the abstract. –  Nick Chammas Dec 15 '11 at 4:04
    
@johannes: I concur. See my second answer. –  onedaywhen Dec 15 '11 at 8:56
add comment

" So is your answer "it's not possible"? "

Many things are possible. In your very particular case, it looks to me like enforcing your 'additional' constraint can be achieved by keeping the database single-table (4-column). This will guarantee you that any combined a,b will always correspond to the same d (because there can only ever be one single d). The price you pay is that there is no longer a "natural" way (i.e. one that is an immediate consequence of the very logical structure of the database itself) that will enforce your a->d and b->d FDs "automatically".

It is a well-known fact that the classical process of normalization-through-decomposition, sometimes requires that certain FDs be reinstated as a database constraint, because in the decomposed design the rule can no longer be stated as an FD. Your particular case seems to be one such, where you have the choice between a design that "automatically" enforces a->d and b->d, but where you have to do extra effort to enforce your additional constraint, or a design that "automatically" enforces your additional constraint, but where you have to do extra work to enforce [the constraints corresponding to] your a->d and b->d FDs.

Having ALL the constraints you mention enforced merely by database structure, is possible, in your particular case, using onedaywhen's solution. However, (a) that is only a solution for particular cases such as your example, (b) the price you pay is increased redundancy, and therefore additional complexity in updating your database (and certain updates might be impossible to achieve !!!), and (c) it still remains a fact that not all conceivable database constraints are expressible as an FD.

(Sorry for posting a second answer. My Stackoverflow login doesn't allow me to comment here.)

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.