Re: Views for denomalizing

"Alfredo Novoa" <alfredo_novoa_at_hotmail.com> wrote in message news:8947011inj9na7jt8r8g4kvj4bkq6c913h_at_4ax.com...
> On Fri, 4 Feb 2005 09:27:56 -0500, "Alan" <alan_at_erols.com> wrote:
>
> >> >Views are a physical construction
> >>
> >> Very very wrong!
> >
> >Not at all. Proof: Show me a view that has no underlying tables.
>
> Views have underlying tables by definition. Views are derived tables.
>
> But it does not have any relationship with whether views are logical
> or physical.

Yes it does. Tables are a physical implementation of a logical design. Views are a further implementation based on the physical tables. I think we really agree here, but we are getting hung up on semantics. Something can be physical _and_ logical. Views are physical in that they are constructed by code, and a representation of them is stored. However, they are logical in that there is no data "owned" by or stored in the view- the view "just" presents a representation of some table'(s') data. They are a definition.

>
> >> Views are as logical as the rest of the relation variables.
> >>
> >> It seems that you are calling physical model to the logical model and
> >> logical model to the conceptual model. This is a very common confusion
> >> in the literature.
> >
> >I see. The literature is wrong and you are right.
>
> Not exactly. An important part of the literature is wrong, but some
> books (the best) are right.
>
> An example:
>
> http://www.aw-bc.com/catalog/academic/product/0,4096,0321197844,00.html
>
> >Almost. A view is made of normalized or non-normalized tables. It can be
> >determined if the resulting view is normalized or not, but you can't
> >normalize a view.
>
> But you can drop a view that is in a low normal form and to create new
> views in 5NF.
>

An instantiated view is a representation of data in one or more tables. The view itself has no normal form. It may present a representation of data in an NF other than that which that data is really stored. It's kind of like lo oking at yourself in a fun house mirror and saying, boy, I sure lost a lot of weight. No, the mirror (view) only makes you look that way (5NF). It is not a representation of reality (3NF). In fact, it is a distortion of reality. The data is in 3NF, the view makes it appear to be in 5NF. But again, I think we basically agree and it's just a matter of semantics.

> >> >attributes
> >> >[apply FDs to get to the next level, revealing PKs in the process]:
> >> >relations (entities and relationships - an ERD is often created here)
> >> >[apply transformation rules* to the ERD to get to the next level]
> >> >-----boundry between logical and physical----------
> >>
> >> An ERD is a conceptual design, and if you apply "transformation rules"
> >> to the ERD what you get is an incomplete logical design.
> >
> >Not in the least bit correct. Assuming the ERD itself is correct (a fair
> >assumption), and you follow the rules, you ALWAYS end up with a database
> >that is in at least 3NF.
>
> But it does not have relationship with what I said.
>
> I meant that the ERD is at the conceptual level and the SQL database
> design is at the logical level.

We agree on this completely. I intentionally did not want to make that distinction in the original answer, as it just serves to confuse the issue at that point. That point can always (and should) be brought out later.

>
>
> Regards
Received on Sat Feb 05 2005 - 04:30:53 CET