For the SQL Gurus out there, a question -- sql server (alternate)

bjzamora NO[at]SPAM hotmail.com
2/9/2004 11:40:13 AM

[quoted text, click to view]

If you lack social skills...you have no business getting behind the keyboard.

BZ

[quoted text, click to view]

Daniel Morgan
2/9/2004 4:13:25 PM

[quoted text, click to view]

So because you are unqualified to do the job you take money for you
think it appropriate to disparage others that are qualified and point
out the minimal qualifiecation set: Fascinating.

I presume you are not a hypocrite and when you need heart surgery some
day you will not go to a board certified cardiologist but rather will go
to some guy that has a great personality.

It is your heart ... it is not your database. Do your employer a favor
and either learn what you are doing or do the ethical thing and quit.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Anith Sen
2/10/2004 4:42:04 AM

[quoted text, click to view]

Good. Since you are clear about the topic we are talking about, all I need
to provide some directives for you to either learn or recollect.

ISO/IEC document 2382 International Standard Database Vocabulary details the
three levels of discourses for fundamental data representation.
i) A conceptual level which captures an information model that can be
assimilated by humans.(business model)
ii) A logical level that represents, in an abstract form, some or all of a
conceptual model suitable for machine manipulation without regard to actual
media or physical devices (logical model)
iii) A physical level that maps the logical level to actual storage
structures and locations. This involves the actual
encodings of data on physical media or devices (the physical model)

Here is Pascal, clearing it up, with examples:
http://www.dbdebunk.com/page/page/622537.htm
http://www.inconcept.com/JCM/May2003/Pascal.html

Codd's Book RM V2 book, Chapter 29 (Fundamental Laws in Database Management)
has descriptions about the three levels of concept -- the user level,
logical/semantic level & storage oriented or physical levels. Similar
classification of data representation has given by C.J Date in the 2nd
Chapter of his Intro to DB book.

During the relational database design endeavor, the external predicate
(meaning understood by humans) in the conceptual model is translated to the
internal predicate (meaning understood by the DBMS) in the logical model as
values in relations. The physical model, which is transparent to the user,
encompasses the implementation specific details like the internal data
structure, file system, bytes on disk etc & DBMS-specific details like
access paths, indexes, clusters etc.

So, instead of making judgements, I suggest you rethink your statement, "All
many-to-many relationships must be resolved when moving from a logical
design to a physical design." since data representation in a relational
database is, by definition, logical & database design using relational
approach is by definition, logical design.

[quoted text, click to view]
architecture take it at a local college or university. <<

If this is your general attitude, I suggest you read the following link and
get a clue.
http://www.apa.org/journals/psp/psp7761121.html

[quoted text, click to view]
have no business getting behind the keyboard. <<

May I get on with my business?

--
- Anith
( Please reply to newsgroups only )

bjzamora NO[at]SPAM hotmail.com
2/10/2004 7:43:33 AM

[quoted text, click to view]

I would say that Anith Sen is one of the most qualified people in this
newsgroup. Certainly helps more people out than you do in a polite
manner.

[quoted text, click to view]

Disagreeing with your attitude has little to do with my
qualifications. Maybe you should to the ethical thing and show some
respect to others? Just because you are a professor(?) doesn't mean
everyone (outside of your courses) has to bend over for you.

BZ

[quoted text, click to view]

joe.celko NO[at]SPAM northface.edu
2/10/2004 3:35:01 PM

I really hate you table names which are almost impossible to
understand (blah, blah, sample data -- but let's make life easier for
people anyway). How about weighted scores in various contests as the
human-friendly version?

The lack of DDL is also a pain, since we have to make all kinds of
assumptions about NULLs and keys. But is this what you wanted to say?

CREATE TABLE Scores
(score_type CHAR(2) NOT NULL PRIMARY KEY,
wgt INTEGER NOT NULL CHECK (wgt > 0));

INSERT INTO Scores VALUES ('X1', 2);
INSERT INTO Scores VALUES ('X2', 1);
INSERT INTO Scores VALUES ('X3', 5);
INSERT INTO Scores VALUES ('X4', 1);

CREATE TABLE Contests
(contest_id CHAR(2) NOT NULL PRIMARY KEY,
contest_name CHAR(10) NOT NULL);

INSERT INTO Contests VALUES ('Y1', 'Contest-1');
INSERT INTO Contests VALUES ('Y2', 'Contest-2');
INSERT INTO Contests VALUES ('Y3', 'Contest-3');
INSERT INTO Contests VALUES ('Y4', 'Contest-4');

CREATE TABLE Standings
(score_type CHAR(2) NOT NULL REFERENCES Scores (score_type),
contest_id CHAR(2) NOT NULL REFERENCES Contests (contest_id),
PRIMARY KEY (score_type, contest_id));

INSERT INTO Standings VALUES ('X1', 'Y1');
INSERT INTO Standings VALUES ('X1', 'Y2');
INSERT INTO Standings VALUES ('X2', 'Y1');
INSERT INTO Standings VALUES ('X3', 'Y1');
INSERT INTO Standings VALUES ('X3', 'Y2');
INSERT INTO Standings VALUES ('X3', 'Y4');
INSERT INTO Standings VALUES ('X4', 'Y4');

[quoted text, click to view]
Y1 = 100*3/7 Y2=100*2/7 Y3=100*0/7 Y4=100*2/7 <<

I am feeling pedantic, so let's do this in steps to show the reasoning
behind each part and then assemble the final query from them.

SELECT C1.contest_id, (1.0 * COUNT(s1.contest_id)/(SELECT 1.0 *
COUNT(*) FROM Standings)) AS distribution
FROM Contests AS C1
LEFT OUTER JOIN
Standings AS ST1
ON C1.contest_id = ST1.contest_id
GROUP BY C1.contest_id;

The 1.0 is to avoid integer math; it could have been a CAST()

[quoted text, click to view]

Here is where I get confused; you show a constant "wgt * 1" for each
row of the standings table, but if this table is all key, isn't the
one redundant? On that assumption:

SELECT ST1.score_type, ST1.contest_id, S1.wgt
FROM Scores AS S1,
Standings AS ST1
WHERE ST1.score_type = S1.score_type;

[quoted text, click to view]

SELECT SUM(S1.wgt)
FROM Scores AS S1,
Standings AS ST1
WHERE ST1.score_type = S1.score_type;

[quoted text, click to view]
distribution in a single SQL query. <<

SELECT C1.contest_id, C1.contest_name,
SUM(S1.wgt)/
(1.0 * (SELECT SUM(S1.wgt)
FROM Scores AS S1, Standings AS ST1
WHERE ST1.score_type = S1.score_type)) AS wgt-distrib
FROM (Contests AS C1
LEFT OUTER JOIN
Standings AS ST1
ON ST1.contest_id = C1.contest_id)
LEFT OUTER JOIN
Scores AS S1
ON ST1.score_type = S1.score_type
GROUP BY C1.contest_id, C1.contest_name;

The optimizer ought to pick out the scalar subquery and do it once.
The big worry is preserving the Contests table via nested LEFT UTER
JOINs.

Results
contest_id contest_name wgt-distrib
=================================
Y1 Contest-1 .38095238095238
Y2 Contest-2 .33333333333333
Y3 Contest-3 NULL
Y4 Contest-4 .28571428571428

Notice I left a NULL in the answer. This means that **nobody** got a
score in contest 'Y3', while a zero implies that **somebody** got a
score of zero in contest 'Y3' -- big difference.

Wangkhar NO[at]SPAM yahoo.com
2/11/2004 3:13:47 AM

pwned by Anith Sen tbh.

Daniel Morgan
2/11/2004 12:53:38 PM

[quoted text, click to view]

I would suggest you not minimize it. The difference is significant.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Wangkhar NO[at]SPAM yahoo.com
2/12/2004 7:48:33 AM

[quoted text, click to view]

Well in that case I suggest you remember the difference in future, and

dba_EdStone NO[at]SPAM yahoo.com.au
2/12/2004 4:46:38 PM

[quoted text, click to view]

Daniel, just because you teach, you think that you can make these
philosophical comments like above. You are egotistical and you shoot
from the hip. Your first thread in the discussion is the perfect
proof. You make silly mistakes in other Oracle discussions but the
tragedy is that you don't have the humility to realize that you are
not as good as you think you are.

In infrastructure related discussions, you're nothing but a biased
troll (as you have been called by others) for Oracle. I don't think
you have the capacity or the humility to be somebody whose opinions
should be taken seriously. I pity those who pay money and attend your

Daniel Morgan
2/12/2004 11:22:46 PM

[quoted text, click to view]

When I screw up and I do often enough ... I accept my chastisement with
grace and acknowledge my mistakes ... I don't try to claim that they
were insignificant semantic differences and try to make the person that
pointed them out as the victim.

And if that is not true in your opinion I do hereby lift a glass of
single-malt in your honor and eat another crow. It's no big deal.

--
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan@x.washington.edu
(replace 'x' with a 'u' to reply)

Greg D. Moore (Strider)
2/13/2004 4:14:34 AM

[quoted text, click to view]

Au contraire, I think you're the one that needs to review here.

Cobb would say that the whole point is that you don't NEED to worry about
the physical design.

The database could be stored via pieces of paper in a pigeon-hole desk for
all the dba cares about.

So again, what does the logical design have to do with the physical design?

[quoted text, click to view]

Noons
2/13/2004 9:18:15 AM

bottom line: we should create designs with M-M relationships
implemented with less than three tables?
Good. You stay with that one. I'll stay with the
delivered, working systems.

I'd rather not learn from this one...
--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam
[quoted text, click to view]

Noons
2/13/2004 9:21:03 PM

[quoted text, click to view]

It all depends on what one calls logical and physical, isn't it?
Using some nomenclatures, you only talk about tables in the physical design.

This , because it may well be that at this stage you may decide the best way
to implement a given data storage is for example to use a flat file.
As opposed to a r-table. It could even be an object in Oracle, for example.
Which is not exactly a table, although it approaches it. Physical design, all
of it, with that approach.

Logical is where you talk about entities, relationships, attributes. Or object
classes and methods. It doesn't matter if the relationship is 1-1, 1-M or M-M
at all, other than by design requirements. And how it is implemented is far
from being of any concern.

So yes in some methodologies tables *are* part of the physical design, while in
others they are exclusively part of the logical design.
As for how all that applies to the OP, I lost track of the whole thing
as well as losing interest when the slag started.
Forgive me.

--
Cheers
Nuno Souto
wizofoz2k@yahoo.com.au.nospam

jp_1872 NO[at]SPAM yahoo.com
2/15/2004 8:16:07 PM

Thanks much that helped - Sorry for the bad names :)

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server (alternate)

sql server (alternate) : For the SQL Gurus out there, a question