How do you mulitply in a field?

On 19 Aug, 07:43, "Graham R Seach" wrote:
A SQL view is not a table; it is the mechanism by which a dataset can be
visualised

I think you are confusing the concepts of 'SQL VIEW' and 'resultset'.
As I previously stated, if by 'view' you mean 'resultset' then we are
in broad agreement.

In SQL terms, however, a VIEW is indeed a table. The SQL-92 spec is
explicit about this [quote]:

"4.9 Tables: A table is either a base table, a viewed table, or a
derived table... A viewed table is a named derived table defined by a
view definition. A viewed table is sometimes called a view."

Normalisation is not applied to tables (in the physical implementation)

Yes, I'm being a little informal by comparing a table in the
implementation to the normal forms. So if you want to be strict,
there's no way of saying which normal form a single column 'all key'
SQL table is in because the term simply does not apply. However, I
think most people would choose an informal approach and say, "This SQL
table is in 5NF."

Jamie.

--

Hi Jamie,

....a VIEW is indeed a table. The SQL-92 spec is explicit about this...
(Sigh) Sect. 4 is a section describing the concepts involved. Although your
quote is verbatim, that section simply seeks to describe the concepts. It is
not defining classes of objects.

In any case, the original point you tried to make was that it is wrong to
include calculated fields in a persisted view (capitalisation
nothwithstanding), due to the rules of normalisation. I restate that it is
OK to
include calculated fields in such views because they do not form part of the
logical model, which is where the rules of normalisation apply. That's my
point.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


"Jamie Collins" wrote in message
ups.com...
On 19 Aug, 07:43, "Graham R Seach" wrote:
A SQL view is not a table; it is the mechanism by which a dataset can be
visualised

I think you are confusing the concepts of 'SQL VIEW' and 'resultset'.
As I previously stated, if by 'view' you mean 'resultset' then we are
in broad agreement.

In SQL terms, however, a VIEW is indeed a table. The SQL-92 spec is
explicit about this [quote]:

"4.9 Tables: A table is either a base table, a viewed table, or a
derived table... A viewed table is a named derived table defined by a
view definition. A viewed table is sometimes called a view."

Normalisation is not applied to tables (in the physical implementation)

Yes, I'm being a little informal by comparing a table in the
implementation to the normal forms. So if you want to be strict,
there's no way of saying which normal form a single column 'all key'
SQL table is in because the term simply does not apply. However, I
think most people would choose an informal approach and say, "This SQL
table is in 5NF."

Jamie.

--

"Jamie Collins" wrote in message
oups.com...
On Aug 17, 1:35 pm, "BruceM" wrote:
You have completely lost me when you say the PK
designation is always arbitrary. Are you saying the designation is
arbitrary?

Yes.

Consider my favourite example: a temporal database with a business
rule, "at no time can an employee have two salaries." The logical
model identifies a table EmployeeSalaryHistory comprising
employee_number, salary_amount, start_date and end_date; all columns
required; end_date = maximum date (#9999-12-31 23:59:59# in
implementation) is used to represents a row in the current state
current. The logical model candidate keys a

(employee_number, start_date)
(employee_number, end_date)
(employee_number, start_date, end_date)

Can end_date be part of a candiate key considering that for the current
salary level there is no end date, and the field would be null?


In implementation we can put a SQL UNIQUE constraint on all of the
above but I choose to omit the third one because it's already covered
by the first two; this is an example how something in the logical
model may not directly related to a single corresponding object in the
implementation.

There's something missing of course: the above candidate keys only
ensure each *period* is unique, whereas the business rule is "at no
time". To cut a long (and hopefully familiar) story short, we need a
sequenced key where overlapping periods are prevented (in
implementation a CHECK constraint may be used plus some other
embellishments).

It is this sequenced key that I would consider as being the logical
primary key (lowercase) of the table.

As described we have implemented a table with a primary key but no SQL
(the language) PRIMARY KEY (PK) designation. Just about everyone says
that every SQL table should have a PK. Take a look in the ANSI SQL
spec:

1) "none of the values in the specified column or columns be the null
value" (section 4.10).
2) PK is a unique constraint (section 4.10.2).
3) there can only be one PK per table (section 11.7).
4) For the references specification of a FOREIGN KEY: "If the
referenced table and columns does not specify a reference column
list, then the table descriptor of the referenced table shall include
a unique constraint that specifies PRIMARY KEY" (section 11.8) [i.e.
you don't specify the columns involved, those of the PK will be used.]

That's it as far as SQL (the language) is concerned. The unwritten
implication is that PRIMARY KEY will have an 'implementation specific'
meaning. For Access/Jet, they a

1) Determines clustering (physical ordering) on disk on compact.
2) PK columns appear as bold text in the Relationships diagram.
3) If you don't specify a PK the Access interface will nag you,
"Although a primary key isn't required, it's highly recommended. A
table must have a primary key for you to define a relationship between
this table and other tables in the database [incorrect!]. Do you want
[Access] to create a primary key now?"

The choice is arbitrary. Whereas it is objectively demonstrable
whether a table is in, say, 3NF, you can't say look at a table and
say, "Hey, you've picked the wrong candidate for PK there," because
the choice of PK subjective, determined by personal preference,
prejudice and ignorance.

Back to the EmployeeSalaryHistory: we have two candidate keys defined
using UNIQUE. Do I really have to choose one to promote to PK. Just
about everyone says every table should have a PRIMAY KEY ...or do they
really mean primary key? FWIW David Portas SQL Server MVP seems to
have similar dilemmas:

Down with Primary Keys?
http://blogs.conchango.com/davidport...eys_3F00_.aspx

I say that yes, every table should have a PRIMARY KEY because
otherwise at the very least you are missing out on the
implementation's meaning; worse, though, you would be at the mercy of
the implementation's designers' default behaviour e.g. in Access/Jet
another NOT NULL (?) UNIQUE will be used for physical ordering
(clustering) but this is undocumented and because I have no idea how
the other candidates are eliminated (first one defined
chronologically? one defined on first columns in left-to-right order?
one defined on first columns in chronologically created order? does
nullable columns affect the choice) I'll make an explicit choice based
on my own criteria.

I've previously mentioned a 'telephone directory' style table where
phone number is the real world unique key and clustering would favour
last name and because in Access/Jet there is no explicit way of
specifying clustering then PRIMARY KEY (last_name, phone_number) would
be best for these aims. Thus, in this scenario the PRIMARY KEY isn't
even a candidate key!

Just to be picky, a relative could end up with the phone number after moving
into the ancestral home, or something like that. I've seen it several
times, once with the son having the same name as the father (meaning that
the suffix "Jr" is needed to distinguish the two). I realize that you are
talking about a phone number *style* table and not an actual phone
directory, but the model raises some questions.

when I refer
to a PK I mean the field or fields that are used for relationships.

Do you not have tables for which more than one key are referenced by
other tables?

A few where more than one field is used as the PK (junction tables,
especially). The only way I know how to do that is by designating the
combination of fields as the PK.

As I have mentioned, we just had the employeeID number change. I have
linked to the Employee table in other databases. Now I have a "real world"
key with no validity. Employee 5487 is now 8279. Maybe I can update if
there is no 8279, but if there is already an 8279 I need to change that
person to 8597, assuming nobody is 8597. This could go on for a while, and
as I've said there are several databases involved. I would rather avoid
this problem, especially considering that it could happen again when
corporate decides that there should be a prefix to identify the facility, or
whatever. If you do not see a problem here, you may make a different
choice.

Again, please understand that I still require that the EmployeeID number be
unique. I just don't use it as the field for relationships.


Jamie.

--

On 20 Aug, 11:28, "Graham R Seach" wrote:
...a VIEW is indeed a table. The SQL-92 spec is explicit about this...
(Sigh) Sect. 4 is a section describing the concepts involved. Although your
quote is verbatim, that section simply seeks to describe the concepts. It is
not defining classes of objects.

If section 4 is too 'conceptual' for you, try 11.19:

11.19 view definition

Function

Define a viewed table.

Format
view definition ::=
CREATE VIEW table name [ left paren view column list
right paren ]
AS query expression
[ WITH [ levels clause ] CHECK OPTION ]

Note the use of the word 'table'. This is what I mean when I use 'SQL
VIEW' (or just use uppercase 'VIEW' to indicate a SQL keyword) i.e.
the one that means a table in the SQL language.

Again, if you want to use 'view' informally to mean 'resultset' then
we are in broad agreeement.

In any case, the original point you tried to make was that it is wrong to
include calculated fields in a persisted view (capitalisation
nothwithstanding), due to the rules of normalisation.
I restate that it is OK to
include calculated fields in such views because they do not form part of the
logical model, which is where the rules of normalisation apply. That's my
point.

Do you not agree that one can *informally* compare a SQL table to the
normal forms? I certainly see in the groups people say things like,
"Your table is not fully normalized" where only the implemented table
has been posted, not the table from the logical model (I know it's the
implemented table because they frequently mention an autonumber PK
which cannot exist in the logical model!)

Jamie.

--

On 20 Aug, 13:16, "BruceM" wrote:
The logical model candidate keys a

(employee_number, start_date)
(employee_number, end_date)
(employee_number, start_date, end_date)

Can end_date be part of a candiate key considering that for the current
salary level there is no end date, and the field would be null?

Yes it can and good point too because that would be an example of a
logical candidate key that is ineligible for consideration for a SQL
PRIMARY KEY merely due to the fact that the person implementing the
logical model has chosen to use NULL to signify the current state (a
very good approach to modelling current periods, IMO).

Just to be picky, a relative could end up with the phone number after moving
into the ancestral home, or something like that.

Yes, so what? Telephone number alone is logical key. The use of

PRIMARY KEY (last_name, phone_number)

here is merely for clustering purposes, PRIMARY KEY (uppercase) not
actually being a primary key in this case.

Do you not have tables for which more than one key are referenced by
other tables?

A few where more than one field is used as the PK (junction tables,
especially). The only way I know how to do that is by designating the
combination of fields as the PK.

Consider a 'supeclass' table Automobiles with columns VIN (unique) and
vehicle_type ('SUV', 'Sedan', etc) with 'subclass' tables for Sedans
and SUVs respectively. You want to prevent rows where
vehicle_type='Sedan' from appearing in the SUVs table. With a
composite key (vehicle_type, VIN) used to reference the Automobiles
tables I can put a validation rule CHECK (vehicle_type = 'SUV') in the
subclassed table. How do you do this with a sole autonumber PK column?
Other tables may pay no regard to vehicle type, therefore may
reference Automobiles using just VIN. Automobiles would be an example
of a table referenced by two keys, (vehicle_type, VIN) and VIN
respectively.

Jamie.

--

"Jamie Collins" wrote in message
oups.com...
On 20 Aug, 13:16, "BruceM" wrote:
The logical model candidate keys a

(employee_number, start_date)
(employee_number, end_date)
(employee_number, start_date, end_date)

Can end_date be part of a candiate key considering that for the current
salary level there is no end date, and the field would be null?

Yes it can and good point too because that would be an example of a
logical candidate key that is ineligible for consideration for a SQL
PRIMARY KEY merely due to the fact that the person implementing the
logical model has chosen to use NULL to signify the current state (a
very good approach to modelling current periods, IMO).

I think you are saying that the PK designation need not be used. In any
case, I still don't get how a combination of fields that includes values
subject to change (such as End_Date becoming not null) would be used in
relationships. I think I get what you are saying about candidate keys and a
number of other related things, but I still don't get how relationships are
modeled.


Just to be picky, a relative could end up with the phone number after
moving
into the ancestral home, or something like that.

Yes, so what? Telephone number alone is logical key. The use of

PRIMARY KEY (last_name, phone_number)

here is merely for clustering purposes, PRIMARY KEY (uppercase) not
actually being a primary key in this case.

So PRIMARY KEY is not really a primary key, and the use of a field in a
composite PRIMARY KEY that is not really a primary key is about clustering
rather than uniqueness? Do you really wonder that I am having trouble
following your points?


Do you not have tables for which more than one key are referenced by
other tables?

A few where more than one field is used as the PK (junction tables,
especially). The only way I know how to do that is by designating the
combination of fields as the PK.

Consider a 'supeclass' table Automobiles with columns VIN (unique) and
vehicle_type ('SUV', 'Sedan', etc) with 'subclass' tables for Sedans
and SUVs respectively. You want to prevent rows where
vehicle_type='Sedan' from appearing in the SUVs table. With a
composite key (vehicle_type, VIN) used to reference the Automobiles
tables I can put a validation rule CHECK (vehicle_type = 'SUV') in the
subclassed table. How do you do this with a sole autonumber PK column?

I would limit the user choice in the front end to vehicles of the
appropriate category. When "SUV" is selected, a combo box from which to
select the model would show only SUVs.

Other tables may pay no regard to vehicle type, therefore may
reference Automobiles using just VIN. Automobiles would be an example
of a table referenced by two keys, (vehicle_type, VIN) and VIN
respectively.

Jamie.

Well ... I had a HUGE disertation that I intended to send to the
group ... But I think I sent it directly to you Jamie! ... (shows my
lack of familiarity with GoogleGroups and news groups in general!)

Anyway the long an short of it was this:

A VIEW/view is a table by definition in SQL-92 ... however, a 'table'
by SQL-92 definition reads like this: "A table is a multiset of rows."
which seems to be the equivalent to what you have called a
resultset .... which, by the way is not a term in the spec I
referenced, nor is rowset or recordset.

table
A table is a multiset of rows...clip..A table is either a base
table, a viewed table, or a derived table.

base table
A base table is either a persistent base table, a global temporary
table, a created local temporary table, or a declared local temporary
table.
A persistent base table is a named table defined by a table
definition that does not specify TEMPORARY.

derived table
A derived table is a table derived directly or indirectly from one or
more other tables by the evaluation of a query expression.

viewed table
A viewed table is a named derived table defined by a view
definition.

Which leads to ....

table definition (11.3)
A persistent base table, a created local temporary table, or a global
temporary table.

query expression (7.1) {put together with some of my own words and
arrangement}
A row value constructor which will specify an ordered set of values
to be constructed into a row or partial row...

view definition (4.9/11.13) {summarized with my own words ...
hopefully we can agree}
A named query expression. Maybe a persistant query expression ...
Either way ... seems to be derived! (ie: an 'output')

So ... with these definitions a VIEW is a table, but a table is not a
base table, and thus, a VIEW is not required to meet the set
informalized rules for normalization implied by the statement:

"Are your tables normalized?"

Which should be properly stated (can there be proper imformal?) ...

"Are your base tables normalized?"

.... So, as I stated to Jamie in an individual reply, we have spent
all this time pegging the geek meter ... proving that we are truly not-
normal, by discussing something in depth to which we all seem to
broadly agree.

Regards,
Brent Spaulding

See my reply under Jamies August 17 reply to BruceM...

Brent Spaulding

On Aug 20, 9:57 pm, datAdrenaline wrote:
So ... with these definitions a VIEW is a table, but a table is not a
base table, and thus, a VIEW is not required to meet the set
informalized rules for normalization implied by the statement:

"Are your tables normalized?"

Which should be properly stated (can there be proper imformal?) ...

"Are your base tables normalized?"

... So, as I stated to Jamie in an individual reply, we have spent
all this time pegging the geek meter ... proving that we are truly not-
normal, by discussing something in depth to which we all seem to
broadly agree.

Agree? Not really, other than we all seemed to agree that a resultset
(my term) does not need to be normalized.

You seem to be saying that normalization can be applied to SQL objects
but only SQL base tables; I've agreed this can only be done
*informally*.

Graham R Seach seems to be saying that normalization cannot be applied
to SQL objects; while I've agreed this is the correct *formal*
approach and I've tried to clarify whether he would permit it
informally (as the person I was originally replying to had done) but
he has not replied.

I'm saying that if you choose to apply normalization to SQL base
tables, as many people do in the groups, then you should apply it
equally to SQL viewed tables (VIEWs); as a practical approach I've
suggested that if your VIEW would not 'pass muster' as a normalized
table then alter it to become the resultset of a stored procedure and
that will prevent it being used as a table (being queried as a table,
SQL updates as a table) -- and you seem to disagree with all this!

Prehaps it's time to "agree to disagree"

Jamie.

--

On Aug 20, 4:50 pm, "BruceM" wrote:
I think you are saying that the PK designation need not be used. In any
case, I still don't get how a combination of fields that includes values
subject to change (such as End_Date becoming not null) would be used in
relationships. I think I get what you are saying about candidate keys and a
number of other related things, but I still don't get how relationships are
modeled.

We have a minor language issue. You seem to use "relationship" to mean
"Access Relationship with RI enforced", whereas I would use "FOREIGN
KEY" or perhaps "referenced table" and "referencing tables" in
context. FWIW I model a relationship using a table which I call a
"relationship table" which IIRC you call a "junction table".

So, I'm assuming you are referring to a FOREIGN KEY (my term). You are
correct that the NULL value here is a complication and one worth
avoiding by design. In Access/Jet, NULL and non-NULL values in the
referencing table must match exactly in the referencing table by
treating the NULL value as if it were an actual value; while this is
not strictly the correct way to handle the NULL value, it has utility
because the non-matching non-NULL values will cause the FK to bite,
also altering the NULL value to a non-NULL value in the referencing
table will result in the new value being CASCADEd to the referencing
table (assuming the ON UPDATE CASCADE referential action has been
specified for the FK). Contrast this with SQL Server which will not
test any FK rows involving the NULL value (i.e. the non-NULL values
may not match but the still FK will not bite as it would in Access/
Jet) and changing from the NULL value to a non-NULL value does not
CASCADE; arguably more correct but has less utility. I'm finding it
hard to describe long hand so here's an example (sorry its ANSI-92
Query Mode and includes the dreaded DDL):

CREATE TABLE Test1
(
col1 INTEGER NOT NULL,
col2 INTEGER, UNIQUE (col1, col2)
)
;
CREATE TABLE Test2
(
col1 INTEGER NOT NULL,
col2 INTEGER,
FOREIGN KEY (col1, col2)
REFERENCES Test1 (col1, col2)
ON DELETE CASCADE
ON UPDATE CASCADE
)
;
INSERT INTO Test1 (col1, col2)
VALUES (1, NULL)
;
INSERT INTO Test2 (col1, col2)
VALUES (1, NULL)
;

Consider this query:

SELECT *
FROM Test1 AS T1
INNER JOIN Test2 AS T2
ON T1.col1 = T2.col1
AND T1.col2 = T2.col2;

returns no rows because T1.col2=T2.col2, NULL=NULL, causes the row to
be removed from the resultset; put crudely, you can't compare the NULL
value to anything, even the NULL value.

Now consider this:

DELETE FROM Test1;

causes the row in Test2 to be removed; Access/Jet successfully matched
NULL=NULL. Impressive or no? In SQL Server, the row would remain (not
orphaned because it was never considered to be referencing in the
first place).

That's one of the reasons why I used a NOT NULL end_date when I posted
the example i.e. to avoid this complexity ...but you did ask
Regardless, I would expect any designer to use (employee_number,
start_date) as the key for referencing tables.

So PRIMARY KEY is not really a primary key, and the use of a field in a
composite PRIMARY KEY that is not really a primary key is about clustering
rather than uniqueness? Do you really wonder that I am having trouble
following your points?

I've tried to show you tables with a PRIMARY KEY but no logical key,
tables where a PRIMARY KEY cannot prevent duplicate data, and tables
where PRIMARY KEY is used for purposes of physical indexing rather
than logical keys.

When you choose to use PRIMARY KEY, the choice of what you use it for
it arbitrary; feel free to omit a PRIMARY KEY but be ready for someone
to challenge you on it (e.g. document your reasons) because there is
this 'touch stone' in SQL that every table should have a primary key/
PRIMARY KEY -- I really don't know what variety they mean! Personally,
I think every table should have a both a primary key and a PRIMARY
KEY, basing their choice for the latter on good (documented) reasons.
For Access/Jet, clustering is IMO the best reason for using PRIMARY
KEY but I do know that many people value that bold text in the Access
'Relationships' diagram.

Final point on this subject: if someone is unaware of Access/Jet's
clustering behaviour, can they really make an informed decision about
PRIMARY KEY designation?

I would limit the user choice in the front end to vehicles of the
appropriate category. When "SUV" is selected, a combo box from which to
select the model would show only SUVs.

Do I need to tell you that your approach doesn't actually solve the
problem at hand? The aim is to prevent this (aircode):

INSERT INTO SUVs (VIN, etc)
SELECT VIN, etc)
FROM Vehicles
WHERE vehicle_type = 'Sedan';

and similar updates that are contrary to the business rules.

What's your position on PRIMARY KEY? If I showed you this table:

CREATE TABLE Employees
(
employee_number INTEGER
);

and pointed out that all nine front end programs known to use the
database have front end code to trap duplicates and NULLs, would you
think it was a good idea to omit a NOT NULL unique constraint from the
SQL table?

Jamie.

--