Re: O7.3 Updatable join view: key-preserved table

Gerard H. Pille wrote:
>
> Hello,
>
> Any idea how one could determine which table(s) of an updatable join view
> (Oracle 7.3) are key-preserved?
>
> I can find the tables via user_dependencies, and the updatable columns via
> user_updatable_columns, but joining those fails if the tables contain
> columns with the same name.
>
> Kind regards,
>
> Gerard (ghp_at_santens.com)

Hola,

        In a simple database consisting of four relations; E, L, P, EP, and D corresponding to the employees,  programmers languages, projects, employees working on projects, and departments of a fictitious company. The predicate for E is (e#, ename, dept)
Each employee has a unique identifier, e#, has a name, and works in one department. The predicate for D is D(dept, budget). Each department has a name, dept, which is unique to the company and a budget. We will return to the meanings of EP and L later.

        The nontrivial functional dependencies (FD for simplicity) that hold in E and D are

{e#} -> {ename, dept}
{dept} -> {budget}

 These FD’s are declared to Oracle in one of two ways. In the "create" statement for a given table, you can optionally declare the primary and unique key constraints for that table. After the table has been created you can still add the constraints by "altering" the table.

"alter table E add constraint e_primary_key primary key (e#)"

In either case, this tells Oracle that e# is the determinant set of attributes in E, and that unless otherwise modified, whenever I see the values ename="spencer olson" and dept="npd", I will see e# =12345.

        Suppose we want to create a view that joins E with D over dept. In SQL this might look like

create or replace view employees_and_depts as select e.e#, e.ename, e.dept, d.budget
from e, d
where e.dept = d.dept;

Oracle now allows all columns that are dependent upon the keys that hold within the expression to be updated. For instance, e#, ename, and dept are all updatable, since each value is determined by the primary key constraint in E. In fact if the view’s select clause were changed from e.dept to d.dept, we loose the ability to update dept in E because Oracle looses the dependency of dept on e#. Which of course is utter nonsense since the operator should probably be the "natural join" of E and D over dept. The fact that the one table is referenced over the other should not matter. Be careful when determining which tables are used in the join.

        This company also has many projects, and each employee can work on more than one project. The EP predicate is EP(e#, proj, hours), each employee works on a project, and has worked for some number of hours.  The P predicate is P(proj, leader, status), each project has a leader and a status. A useful view might be

create or replace view e_ep_p as
select e.ename, e.e#, e.dept, ep.proj, ep.hours, p.status, p.leader from e, ep, p
where e.e# = ep.p#
and ep.proj = p.proj;

In this view, all attributes that are dependent upon the primary key in EP, which is the set {e#, proj} are updatable. This includes proj, e#, and hours. Oracle does not allow dept, ename, status or leader to be updated in this expression.

        Taking a look at L. The predicate is L(e#, language), which is meant to express which employees might be programmers, and hence which language they use best. From a developers standpoint this relation might include a FD which states that e# is the primary key, forcing each programmer to use only one language.  On the other hand an employee might know many languages, hence the combination of e# and language is the primary key. Notice that in either case the attribute e# is contained within the determinant set of the FD. In the view

create or replace view programmers as
select e.e#, e.ename, e.dept, l.language from e, l
where e.e# = l.e#;

ALL columns are updateable, since the FD’s that hold in PROGRAMMERS are all dependent upon e#. If (e#,language) is the primary key in L, then it will not be possible to update multiple rows to a common language for a single e# since this would violate the primary key constraint. This feature seems especially useful. This allows attributes that are probably not suitable for similar relations, in this case not every employee is a programmer and does not need a language attribute, to be broken out to separate relations. Once in separate relations, those nasty biting things (NULLS) are not needed as placeholders for employees who are not programmers, as the case might be if the language attribute were contained in E. Furthermore, queries like "Find employees that are not programmers?" is a simple set difference between E and L.

        Hope this helps.

Spencer Olson
State Street Bak
spencer_olsen_at_ssb.com Received on Thu May 01 1997 - 00:00:00 CDT