|
|
Posted by SQL Menace on 07/15/07 12:15
On Jul 15, 7:32 am, raylopez99 <raylope...@yahoo.com> wrote:
> On Jul 15, 2:32 am, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
> > raylopez99(raylope...@yahoo.com) writes:
> > > Very bizarre. If anybody has any advice please offer it. I think SQL
> > > is a dinosaur language and should be scraped, and RDBMS are obsolete
> > > in view of cheap memory, faster processors, and managed pointers, but
> > > that's another matter.
>
> > In that case, should I even bother to answer?
>
> To prove me wrong Erland! LOL.
>
> > Memory may be cheap, but 1TB of memory is still quite expensive. And
> > when power is turned off, not much remains of what once was there.
>
> > And if you think SQL should be scrapped, permit me to point out that
> > most SQL queries written in C++ or C# would require a lot more code.
> > And, what worse is, the code would not be able to adapt to changes in
> > data distribution or addition of new indexes. The point with SQL is
> > that you say what result you want - the optimizer finds out the best
> > way to compute that result.
>
> No, just dump your data into a flat file, use a hash table to index
> every word in said file, and use hardware (CAM memory) to do a fast
> search. Simple really. Probably what Google does right now. You
> think they are using SQL "queries" to do fast searches? No way Jose.
>
>
>
> > As for your problem, you appear to be working with some graphical tools
> > that I have little experience of. (And I don't have much faith in.)
>
> Correct. You are "right as rain" on this point. I do think the C#
> feature of VS2005 vis-a-vis the SQL dB aspect of it is broken--maybe
> because I'm using the SQL Server 2005 Express (free) edition (just
> upgraded for a mere $50 to the Developer's edition--I'll repost here
> if it solves this small problem).
>
>
>
>
>
> > But
> > there is an apparent misconception:
>
> > > But somehow, when using C# rather than C++, I get this error when
> > > attempting to create a relationship between tables when using two
> > > identical columns called "AuthorID": "the columns in table Authors do
> > > not match an existing primary key or UNIQUE constraint" Why? The
> > > columns are the same. I even tried (and this made no difference)
> > > copying and pasting columns from one table to the other, but still I
> > > get this error.
> > >...
> > > One thing (that shouldn't matter, since it didn't matter when I used C+
> > > + rather than C#): the column in table "Author" is a primary key,
> > > while it is a "foreign key" in table "Content". But that is
> > > irrelevant when creating a relationship, no?
>
> > No, that is the essence of a relationship. Or rather that is the
> > relationship.
>
> But it should (I would think) be irrelevant to the particular bug
> here, since the columns are identical. I even tried making both
> columns "primary keys" but this did not solve the bug, which proves my
> point. Sorry if I was not clear but talking about computer problems
> in any language is ackward. [UPDATE: see my "revelation" below]
>
>
>
> > In a relational database, all tables should have a primary key which
> > uniquely identifies the data. Ideally, the PK should be drawn from
> > the data itself. For instance, in a table of countries, the PK should
> > be the country code according to ISO 8601. Often, though, it is more
> > convenient to introduce a surrogate key, that is a system-assigned
> > number to each row. Care should still be take to avoid duplicates,
> > that is, Mark Twain should only appear once in a table. An important
> > thing to point out about primary keys is that a PK can consist of more
> > than one column.
>
> Yes, this is theory. I agree.
>
>
>
> > Now, we have table called Authors. Somewhere else we also have a table
> > called Books, in which the PK would typically be the ISBN. Of course,
> > when we have a book, we want to know the author. So in the Books
> > table is there an Authors column? Nah, a book can have several authors
> > so that is not really a good idea. Instead there is a table AuthorBooks
> > which have two columns AuthorID, ISBN. This table states which authors
> > what wrote which books. The primary key of this table is (AuthorID, ISBN).
> > But the columns AuthorID and ISBN are also foreign keys in this table.
> > Not together, but on their own. AuthorID refers to Authors and ISBN
> > refers to Books. The foreign key is constraint: you cannot add an
> > AuthorID in AuthorBooks if this AuthorID does not exist in Authors.
>
> Yes, but it seems you are advocating a THIRD table "instead there is a
> table AuthorBooks". Why, just to link the two tables Authors and
> Books? Why not simply add a foreign key in Books called "AuthorID",
> that is the primary key in Authors? (This is exactly what my Frasier
> book proposes). But I suppose you can have a third "intermediate"
> table, to tie two tables together--no reason why you cannot--but it
> just doesn't sound "simple". No big deal though.
>
>
>
> > The best way to learn SQL and relational databases is not through
> > graphical tools that do things behind the scenes, and far from always
> > have gotten things right. Instead learn the proper commands and get
> > a book which is focused on learning relational databases.
>
> OK. Tell me the book to buy then! Here is what I've already bought:
> Andrew Brust et al "Programming SQL Server 2005" (too specific on T-
> SQL enhancements, on ADO.NET, on XML data, etc); Frasier book
> (excellent) Pro Visual C++ (has a chapter on SQL programing via VS--
> but a mere dozen pages--however his examples whetted my appetite for
> SQL); Alan Beaulieu "Learning SQL" (O'Reilly Press--too focused on
> MySQL, rather than T-SQL which Microsoft uses, so I can't 'compile'
> his examples, but I do read his book and try and replicate his
> examples in VS2005); Morrison "Creating SQL Server 2005 Applications
> with Visual Studio" (book is just like the title says--the authors use
> Visual Basic as the glue language, which I don't like, but you can
> follow along. There's an emphasis on using the VS IDE to create
> queries, etc). I also just ordered these books: Beginning C# 2005
> Databases (Programmer to Programmer)[probably the same as the Morrison
> et al book on VB, but using C#]; Sams Teach Yourself SQL in 10
> Minutes, Third Edition [might be the most useful book?]
>
> [My REVELATION] BTW, as I typed this, I just realised that creating a
> "relationship", as you said above, is simply creating primary and
> foreign keys and having a foreign key in one table that is a primary
> key in another. Since I've done this already using the "Properties"
> tab of tables "Author" and "Content", then I don't really need the GUI
> "Wizard" that seems to be broke in C#/VS2005, do I? So the "bug" I
> describe in this thread is a minor bug, a trivial bug, for those
> people too lazy to designate a "primary" and "foreign" key, manually.
>
> Thanks Erland--your name sounds familiar... ah yes, you helped me with
> a connection problem I had earlier this year (when I was using C++ as
> the language of choice): http://tinyurl.com/384rjt
>
> Cheers,
>
> Ray- Hide quoted text -
>
> - Show quoted text -
Also how will you do one of these queries
return all customers who live in LA and have ordered product x between
June 2006 and July 2007
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx
Navigation:
[Reply to this message]
|