Creating indexes on views/BCNF

#11

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#12

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#13

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#14

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#15

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#16

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

#17

On Jan 14, 5:38*pm, aehc... (AT) ntu (DOT) edu.sg wrote:

Quote:

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/arti...odeling/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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Creating indexes on views/BCNF

comp.databases.theory comp.databases.theory

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM

Re: Creating indexes on views/BCNF - 01-16-2008 , 02:06 PM