"Gene Wirchenko" <genew (AT) ocis (DOT) net> wrote

Actually, normalization is a bottom-up approach, and is done after the fact.
After the fact of coming up with an unnormalized model. If you do modeling
*correctly*, your model will be normalized as soon as you build it, and you
don't have to go through the rigmarole of converting the schema into another
equivalent schema that acheives a higher normal form, and repeating that
exercise.

Having said that, I never learned how to model data *correctly*, so I ended
up normalizing during design time, just as you say.

But it's pretty much clear what a UML class diagram is, isn't it.

The entity-relationship method is often used, advocated and taught
for the design of databases. The design starts by creating
an entity-relationship diagram, that is systematically transformed
and provided with implementation detail until an ER diagram results
that specifies a relational database schema.

An ER diagram is a representation of the relations (tables)
and foreign key relationships in a relational database schema.
The relations in the schema are the entities and many-to-many
relationships in the ER diagram; the foreign keys are the
one-to-many relationships and the links between many-to-many
relathipships and entities in the ER diagram. Every ER diagram
can be created out of a relational schema in this way, but not
every relational schema corresponds to an ER diagram; certain
limitations on the structure of the structure of the foreign keys
apply.

Entity-relationship diagrams and UML class diagrams are very closely
related. The entity sets in an ER diagram correspond to the classes
of an ER diagram; the relationship sets to associations.
There are also many differences, but the starting point is the same.

[...]

Think 'class' ~ 'relation' (table), 'reference' ~ 'foreign key',
'object' ~ 'tuple' (row), 'object model' ~ 'database schema'.
But don't equate them: there are important differences.
One is that objects can hold references to objects, while
a database table can only hold attribute values. This is often
resolved by adding a (usually autonumbered) special attribute
to relations that is guaranteed to be unique for each tuple,
and can therefore be used by way of tuple reference; this may
or may not be a good idea, depending on your purpose and
whom you talk to. Tools exist that automatically map a class model
to a relational database using this idea, but some people in this group
are very much against it. They think one should start with
a relational model and when possible only use 'natural' attributes,
i.e. ones with values we humans can observe, otherwise you
run the risk of creating structures with unnecessary levels of
references, duplication of information, and other issues.

The main thing is that the technical differences may appear
small, but the whole approach to handling data is different,
and you can't expect to pick up such differences nmerely by
participating in a newsgroup discussion.

[...]

Well, sometimes you have 'data' classes that do little but
define a bunch of properties with simple values, and you create
and manipulate collections of such objects; such classes and
collections are a quite like database tables. Inheritance
is also a very useful notion in database design, if we apply
it to properties (attributes, columns) only. But nothing
corresponds to the notion of method. Methods invite
class-specific, object-by-object manipulation techniques;
SQL on the other hand is essentially a language that maps
tables to tables with operations such as row selection,
column selection (projection), union, and join. You don't
manipulate or navigate iundividual values and rows, databases
weren't designed for that.

Set-oriented: you apply operations on whole sets at once.
Non-procedural: you don't change variables, but yield results.
It's a bit like doing all your programming with functions that take
collections and iterators and return resulting collections and iterators.

Some people in this newsgroup believe that the relational model
is sacred, its inventor Codd was a holy man, and a guy called Date
who has written a bunch of popular books on the subject is his
replacement on earth. Anything you say to suggest that other people have
something intelligent to contribute on the subject, or have already done
so, perhaps using slightly different ideas or terminology, is met with
the scorn only dogmatists can muster. Pay attention to their ideas,
ignore their attitude.

Not only newbies, anyone with different ideas or different terminology.
This problem is hard to avoid in human communication, but it seems
particularly bad in computer science.

--
Reinier

In message <epAXl.50$P5.2 (AT) nwrddc02 (DOT) gnilink.net>, Walter Mitty
<wamitty (AT) verizon (DOT) net> writes
Some years back I was handling support for a CASE tool that handled
normalisation. The users didn't want a tool that took them through the
various stages, they assumed that the first time they committed a
structure to the system it would already be in TNF - which was as far as
they were interested in.



--
Bernard Peek

<dr.coffee1 (AT) gmail (DOT) com> wrote



If the mapping between OO concepts and RM concepts were simple, someone
would have, by now, written a book that ties the two of them together, and
explains both patterns in terms that makes sense. to someone coming from one
pattern or the other. To my knowledge, there is no such book. I'm going to
echo something Bernard said in another response.
Better minds than mine have attempted to reconcile the OO way of thinking
and the RM way of thinking with each other. By their own reckoning, their
attempts have been unsuccessful.

So you really shouldn't expect to reduce RM to a pattern that you already
know. And you shouldn't expect, based on your success with OO up to this
point, to be able to treat RM as something that can be marginalized as
either a specialized niche or a trivial development. It is neither.
Possibly the best person to speak to this would be Marshall. Marshall is a
regular here who spend at least ten years as an OO programmer before going
over to the RM side. He might be able to relate to what you do and do not
understand at your present stage.

Meanwhile, I need to correct the general thrust of my first response to
you. In that response, I contrasted the OO way of thinking with the SQL way
of thinking. That completely overlooks the fact that your target is to
build an Access application that stores and serves up data for analysis with
Excel. Much of what I said, while true, is beside the point. You can, in
fact, build a simple yet functional MS Access application without learning
much of any relational theory and without gaining much of any proficiency in
SQL. Sadly, I can't help you do that, because I learned SQL before my first
exposure to Access. So everything new that I learned in Access was
something that I related back to what I already knew in SQL. After playing
around with some wizard for a while, I would switch to the "SQL view" in
order to "Find out what's really going on".

That was a useful way for me to understand it, but may be the long way
around for you.

Like you, I like to learn the big picture first, and then fill in all the
details. I was fortunate to learn the big picture of relational (or at
least SQL) databases back in the 1980s, when there was less to learn. I
find MS Access tutorials to be infuriating, because they all approach the
subject matter as if there is no big picture. Here's how you chage the font
in a dialogue box. And here's how you declare a primary key. And here's
how you use the forms wizard to create a form. Gee whiz! Isn't that
wonderful! But there's no central unifying theme in the narrative.

Anyway, it turns out you can do almost everything in design mode that you
might be able to do by learning the syntax and semantics of SQL DDL and DML.
And all you have to do is point and click, most of the time. And, because
MS Access integrated application building with database building, you end
up building a functioning application alongside the building of a working
database. That's no small benefit. I actually built some useful stuff for
myself using MS Access without really learning it. I hope somebody who
really has learned it can help you more than I can. In the meantime, I'm
going to focus on some sidelights.

MS Access owes a lot to its heritage. It was built to make desktop
database work available to the same kind of person who was comfortable with
Excel or Word. Desktop database work is very different from enterprise
integration database work. Access was typically for one user at a time. It
wasn't easy to get data into an access database, except by interactive data
entry. If you were dealing with more than 10 million rows, you were
probably maxed out. And so on. Over the years, it has matured to where it
has overcome a lot of the initial limitations. But it still looks like a
strange beast, to those of us who grew up with industrial strength DBMS
products. It would be sort of like Object Oriented COBOL, if you get my
drift.

If you learn MS Access from the ground up, you are going to learn several
things about the RM that just ain't so. That is why some of the regulars
are chiding me for encouraging you. The design patterns that work for you
are design antipatterns at the larger scale. That may not matter to you, at
least for a while.

There is one antipattern that I want to mention in particular. Your problem
description, in the original post, reminds me of the Entity-Atrribute-Value
(EAV) antipattern. Different insrument types are like different entities,
different calibrations are like different attributes, and different
measurements are like different values. The terible thing about EAV is the
hell you have to go through to create meaningful queries out of that mess,
if you're using SQL or any language remotely like it.

But there is a tool that can reduce an EAV data glob to a legible summary
without much difficulty. It's the Pivot Table Report in MS Excel. A pivot
table doesn't care whether the row headers and column headers are data or
metadata. It's all databurger to the pivot table. So, if your plan is to
simply pump data into Excel, and reduce it there, you may be able to get
away with an EAV like design, in spite of the fact that people like me
consider it a monstrosity.

Bob Badour wrote:

That is because they don't address the same problems.

As long as you're happy manipulating typical business data
in a database, the relational model is all you need.

As soon as you try to build working software systems,
some considerations arise that the relational model
has nothing to say about. E.g. how do we design and implement
a GUI to present data and allow their manipulation? How do
we split systems into parts that can be designed and implemented
independently and can be reused? OO was created to address
these issues. Relational theory doesn't. Besides, OO is
neither low-level nor a computational model.

--
Reinier

In message <4a2edac9$0$27727$703f8584 (AT) news (DOT) kpn.nl>, none
<rp (AT) raampje (DOT) ?.invalid> writes
That's the physical data structure which describes the way the database
has been built. That's often derived from the logical (and sometimes a
separate conceptual) data structure. That's where the objects on the
diagrams are entities. Entities and tables are not the same thing.

The ERD is an entity relationship diagram which details the logical or
conceptual structure which is then used as a basis for a physical
design.

Entities loosely map to classes in that there are instances of each
entity which vaguely map to objects as instances of a class.

One thing to be aware of is that different design methodologies use
entities and ERDs in slightly different ways. Some have the concept of a
sub-entity, others don't.





--
Bernard Peek

none Reinier Post wrote:

But that would not only be a blunder but a great blunder.


You are an idiot if that is what you honestly believe.

none Reinier Post wrote:

Au contraire.


You don't have a clue what you are talking about.


Really? Define OO.

In message <47BXl.1634$u86.696 (AT) nwrddc01 (DOT) gnilink.net>, Walter Mitty
<wamitty (AT) verizon (DOT) net> writes

I'm an enthusiastic Access user although I'm far from being an expert.
But I know that it does have some serious weaknesses and in some
situations actively encourages bad practise.

That's where a decision is required. Do you want to learn Access or do
you want to learn relational theory. The two are related but they aren't
the same thing.

That's one of its many disadvantages. It's another one of the ideas that
visitors to this newsgroup occasionally espouse as the next Big Thing.

EAV systems generally require very high skill levels in all of the
users. That's fine for a single-user database but generally won't work
for more than one user, without an extraordinary infrastructure to
maintain consistency.



--
Bernard Peek

Bernard Peek replies:

They are the same thing regarded at a different level of abstraction
and a different level or detail. They do map to each other.
Every ER diagram maps to a relational schema after certain
transformation steps, and many relational schemas map to
ER diagrams of a particular type.

I thought that's what I wrote, in different words.

Yes, for 'entity-like' classes.

Yes, is-a (specialization), if used, is one of the constructs that have
to be transformed away, relational schemas don't support it.

--
Reinier