# Re: Creating indexes on views/BCNF

Date: Wed, 16 Jan 2008 12:06:02 -0800 (PST)

Message-ID: <f8e2504f-e684-478d-809e-d14071b03214_at_d4g2000prg.googlegroups.com>

On Jan 14, 5:38 pm, aehc..._at_ntu.edu.sg wrote:

> Is there a commercial RDBMS that allows one to create indices on

*> views?
**>
**> I'm asking this partly because of BCNF.
**>
**> A typical BCNF problem is (where -> is a FD)
**>
**> A, B->C
**> C -> B
**>
**> which becomes the following relations
**>
**> R_1 [_A_, _C_]
**> R_2 [_C_, B]
**>
**> where _X_ denotes an attribute in the primary key
**>
**> since
**> R [_A_,_C_,B] is not in 2NF
**>
**> But there's an additional constraint where the equijoin of R_1 and
**> R_2
**> on C would have the candidate key A,B.
**>
**> Is there a way to express this last constraint (i.e., a candidate key
**> on a view) in an implementation system?
**>
**> Cecil Chua
*

Hi Cecil;

Please forgive the self-promotion, but my article here: http://www.sqlservercentral.com/articles/Data+Modeling/61529/ is the 4th in a series of 5 articles on db design. It shows, specifically, how to implementa a multi-relvar constraint using an indexed view on MS SQL Server. I think there exists an analogous facility in Oracle, but I'm not sure about other DBMSs (such as MySQL).

TroyK Received on Wed Jan 16 2008 - 21:06:02 CET