Instructor: Dan
Date: June 16, 2004 (Wednesday)
<Dan> Ladies and gentleman (and you too bot), tonight will be a survey seminar.
<Dan> Since you all know the “normal” rules, I won’t repeat them.
* Dan is now known as CrazyTeacher
<CrazyTeacher> So – a little history to get us started.
<CrazyTeacher> Anybody know how long we’ve had these things called “computers” ?
* RainTurtle raises hand
<CrazyTeacher> Rain?
<RainTurtle> Personal computers go back twenty-five or thirty years, at a guess….big computers go back to the early 20th century, and the good old abacus has been around for millenia
<CrazyTeacher> Fairly close.
<CrazyTeacher> The “PC” era officially started with the introduction of the “IBM PC” in the early ’80s
<CrazyTeacher> Of course, “personal” computers predate that by almost a decade (The Altair kit being the first).
<CrazyTeacher> Bigger “mainframe” computers go back a LOT further… Though the ones in the ’50s were much less capable than what we’re using to do IRC chatting tonight.
<Jael>
<CrazyTeacher> Well – with Computers came the need to store “structured” data… And – even fetch it back.
* CrazyTeacher should note that there is also a need to store, search and retrieve unstructured data as well…
<Aphanas> 🙂
<CrazyTeacher> Perhaps a quick definition might be in order…
<CrazyTeacher> Structured Data – the appearance of the data is similar from one item to the next (for example, addresses are relatively similar – From bottom – country, postal code, locality and address)
<CrazyTeacher> Unstructured data – varries significantly from one instance to the next. (for example Objects of art… some are oil on canvas, others are made out of marble, etc… )
<CrazyTeacher> Got the idea?
<RainTurtle> *nods*
<Aphanas> *nods*
<Jael> *nods*
<CrazyTeacher> When folks first started “Storing” data in computers – it was generally stored as LONG “strings”… Something like the following:
<CrazyTeacher> 1600 Pensylvania Avenue Washington DC 12345 USA
<CrazyTeacher> 1234 Reading Railroad Atlantic City NJ 08123 USA
<CrazyTeacher> See how things line up.
<Aphanas> *nods*
<CrazyTeacher> The “Address” was the first x characters, the City the next, etc.
<CrazyTeacher> That’s called zones.
<Jael> *nods*
<CrazyTeacher> If you use that technique to store a bunch of families – with names, addresses, phone numbers and the like, you end up with LOTS of repititions…
<CrazyTeacher> For example –
<CrazyTeacher> Bush George W. 1600 Pennsylvania Ave. …
<CrazyTeacher> Lincoln Abraham 1600 Pennsylvania Ave. …
<CrazyTeacher> Etc… As lists of folks that lived in the White House.
<CrazyTeacher> The “address” repeats.
<CrazyTeacher> Back in the “good old days” disk space cost lotsa $$$$$
<CrazyTeacher> So – they had to come up with techniques to “save space”
<CrazyTeacher> One approach was to create a “Heirarchy”… Say you have “addresses”, and each address has one or more “residents”.
<CrazyTeacher> This was fairly flexible and useful – since it reduced – a LOT the repitions.
<CrazyTeacher> There were problems with this though… Specially today, in the world of mobile phones… What do you associate the phone number with? The Address, one of the residents? What happens if the phone is handed to someone else? How about if someone puts it in his/her pocket and walks next door?
<CrazyTeacher> Do you see the problem?
<Jael> *nodsnods*
<Aphanas> *nods*
<CrazyTeacher> Well – a really smart dude (well several actually – but one dude wrote the book) came up with another way to handle things… He called it “Relational”.
<CrazyTeacher> Here, you would have a pile of “addresses”, a pile of “people” and a pile of “phones”. And, you’d have a bunch of glue things that “related” addresses to people, addresses to phones, phones to people….
<CrazyTeacher> Much more flexible!
<CrazyTeacher> Do you see how?
<Jael> I see how it’s more flexible, but I don’t understand the glue part.
* CrazyTeacher pings his students – to see if they’ve fallen asleep yet.
* Jael raises hand…
<CrazyTeacher> Jael.
<RainTurtle> (hehe)
<Jael> Is Access a relationship database or the other kind? I’ve tried doing things in Access and had the exact problem you describe…
* Jael could be setting it up entirely wrong too…having never taken a class on the program…
<CrazyTeacher> Access IS a database tool…
<CrazyTeacher> Depending on how you set things up – your database may or may not be a “relational” database.
<CrazyTeacher> BTW – Smart Dude’s name “C. J. Date”
<CrazyTeacher> I should also mention that a “heirarchical” database is a specialized case of a “relational” database.
<CrazyTeacher> There ARE other types of database.
<CrazyTeacher> In “general” the things that hold “data” in a databse are called “tables”…
<CrazyTeacher> Are all of you at least vaguely familiar with either Excel or Access?
<RainTurtle> Yes
<Jael> I love Excel
<RainTurtle> It’s good for charting locations of things
<Aphanas> *nods*
<CrazyTeacher> If you set up Excel so that you have “column headings” at the top of rows, and are really good girls and boys, and always put the same kinda stuff in columns, you may have the beginnings of a single table!
<CrazyTeacher> Using our example above, you could have one column for Last Names, one for first, one for middle, etc.
<CrazyTeacher> With this structure, you can sort on any column… Nifty right?
<RainTurtle> yup
<Jael> *nods*
<Aphanas> *nods*
<CrazyTeacher> But, as we identified before – you sometimes have to repeat a LOT of data from one row to the next… (Say you’re doing a phone directory for your local gaming group… You all live in the same country – in all liklihood)…
<CrazyTeacher> So, if you had a “Country” column, you’d see the same one a LOT.
<CrazyTeacher> I should also note – Just cause you and I can tell that “USA” and “U.S.A.” and “United States of America” are the same country… The computer’s usually not smart enough to realize this.
<Aphanas> Heh… yup.
<CrazyTeacher> ( Some are… But, they’ve been carefully taught… )
<RainTurtle> (some even recognise FSA…)
<Jael> heh…
<CrazyTeacher> Ok – if you decide to put Addresses on the 2nd sheet of your workbook, and add a column “AddressID” column where you put a unique identifier (different for each address).
<CrazyTeacher> You can then, remove the address columns from your person listing on the first sheet – and just put in the single “ID” value.
<CrazyTeacher> And – whenever you want to get a persons name/address (like for a mailing list) you would just do the “lookup”.
<CrazyTeacher> In Access, there are “tools” that “help” you do this – they are called “Queries”…
<CrazyTeacher> We’ll spend MOST of the rest of the time (you want to spend) working on these “Relational” database concepts.
<RainTurtle> 🙂
<CrazyTeacher> An important “concept” in Relational database is “Normalization”… (This is not really like nationalization)
<Aphanas> *nods*
<CrazyTeacher> This is the process a “database” designer goes through to eliminate redundant data, and lay out the database.
<CrazyTeacher> There are 5 levels of normalization. ( You really don’t care the info is there, so you’ve heard the term )
<CrazyTeacher> In general, most database designers START their designs with a goal of working in 3rd normal form…. It’s a compromise level.
* RainTurtle raises hand
<CrazyTeacher> If you go all the way to 5th, your db is probalby not useable!
<CrazyTeacher> rAIN?
<RainTurtle> Is that so they don’t have to hack their own programming to find the data?
<CrazyTeacher> To some extent.
<CrazyTeacher> 5th normal form has SOOOO many “IDs” that the space taken up by “IDs” is so great it exceeds the amount of space saved as well.
<Aphanas> Heh… thanks, I was just about to ask what 5th normal form would look like.
<CrazyTeacher> Performance issues also start really showing up in 4th normal form… So, I generally ignore them.
<RainTurtle> makes sense
<Jael> I think I’m following this…
<CrazyTeacher> As a practical point – there ARE times you “denormalize” (unlookup) your data for performance reasons.
<CrazyTeacher> And other reasons.
<CrazyTeacher> For example:
<CrazyTeacher> Postal Codes…
<CrazyTeacher> Most folks believe that 1 Postal code → One town right?
<CrazyTeacher> WRONG!
<Jael> heh…we have five or six here
<RainTurtle> They’re different, this side of the border
<CrazyTeacher> Jael – that’s not the problem 5 or 6 postal codes for one town is fine…
<CrazyTeacher> If you set up a “lookup” table with postal codes and City/State (for US)… You could eliminate that from your address table couldn’t you?
<CrazyTeacher> *nods* So I understand Rain.
<CrazyTeacher> Well – I’ll cut to the quick – the answer is NO.
<Jael> hehe
<CrazyTeacher> Regretfully, some postal codes actually map to more than a single town/post office.
<Jael> *blinks* didn’t realize that…
<CrazyTeacher> So, there’s a case where you MAY have the “lookup table” of postal code & municipality – but the data still needs to have the municipality so you can over-ride when needed.
<CrazyTeacher> Jael – I actually lived in one once – in Wellesley MA… There was also a “Wellesley Hills” that had the same Zip Code.
<CrazyTeacher> OK.
<CrazyTeacher> So What next…
* Aphanas raises hand…
<CrazyTeacher> A little “descriptive” example Database (AKA DB) design might make some of these concepts easier…
<CrazyTeacher> Aphanas.
<Aphanas> Trying to get a coherent question here… so, basically when the data that you have in a lookup table needs additional references for clarity (like the municipality), you can end up with a database that has too many relationships to use a lookup table?
<CrazyTeacher> *nods*
<CrazyTeacher> You have a lookup table that has lookups to other tables, etc…
<CrazyTeacher> Say you have our list of people.
<CrazyTeacher> And, you have a list of Addresses
<CrazyTeacher> And, a list of Post Offices…
<CrazyTeacher> To find out a person’s address (in Canada where it makes sense), you would start with the person – find his address id, look that up in the address table… Then use the postal code id to look up the “town” …
<CrazyTeacher> And – this can go on and on…
<CrazyTeacher> This is considered “not fun”.
<RainTurtle> heh
<Jael>
<Aphanas> Heh… I imagine not.
<CrazyTeacher> Computers can handle SOME of this… But, even if you make them do it, you can run into performance problems.
<CrazyTeacher> As I said – generally, you make lotsa lookup tables initially, and then decide where to store looked up data in regular table… Though there ARE issues if you do this…
<CrazyTeacher> Think back to our example of the “people” listing of your gamers…
<CrazyTeacher> If you have two brothers in your list – they generally have the same address (for a while anyway)
<RainTurtle> hmmm yes
<CrazyTeacher> Well – if the whole family moves across town – and you have address for both of them, you have to remember to fix it twice (Not a good thing)…
<CrazyTeacher> If you have addresses separated from the names – you only have to fix it once!
<CrazyTeacher> Nice huh
<Aphanas> *nods*
<Jael>
<CrazyTeacher> And – if one of them moves off to college – you just create a new “address” for his college address – and change the ID cell.
<CrazyTeacher> This is what this stuff does for you.
<Jael> hmm….nifty….
<RainTurtle> cool
<Aphanas> 🙂
<CrazyTeacher> So, we’ve got a people table, and address table, and several other tables.
<CrazyTeacher> We COULD have the AddressID in the people table right?
* CrazyTeacher notes – this IS a trick question… And is hoping one of his briliant students might see a problem with this…
<RainTurtle> *blinks*
<CrazyTeacher> Rain – have you ever known someone that had more than one address – at the same time?
<RainTurtle> *nods* I have. My brother does….I expect you do
<CrazyTeacher> Take the example children of divorced/separated parents that have join custody…
<CrazyTeacher> Ok…
<CrazyTeacher> If you put the “AddressID” in the table with people – which address do you put in the field?
<CrazyTeacher> ( I should note that in DB speak – those “cells” are called fields… )
<RainTurtle> The one they use most often, according to the context, generally. Office addy if it is stuff concerning mailings to work, street addy for other things….home addy for others still
<Aphanas> 🙂 You would need the ability to have one to many relationships in the DB.
* CrazyTeacher has a home & a work address but in the case of those kids – they have two home address)
<Jael> I think I’m not one of your brilliant students ;-D
<CrazyTeacher> Yes aphanas.
<CrazyTeacher> Jael – are you seeing how there could be a problem?
<Jael> *nods*
<CrazyTeacher> The more common example is with Phone numbers…
<Jael> It’s the fixing it that I don’t quite get
<CrazyTeacher> Do any of you know anybody that has more than one #?
* CrazyTeacher has a work number, a home number and a mobile number…
<Jael> *nods*
<Aphanas> Heh… do we know anyone that only has one number?
<CrazyTeacher> Luckily I don’t also have a pager number…
<CrazyTeacher> Aphanas – my in-laws only have ONE phone number…
<CrazyTeacher> THey’re retired… And don’t have a mobile phone.
<Aphanas> *nods* Still pretty rare these days though…
<CrazyTeacher> Well – if we were good little “developers” of our database, our “people” table has a “PersonID” column as well… We all did this right?
<Aphanas> 🙂
<Jael> of course…
<CrazyTeacher> One work around – of this problem is to create yet another table to “join” the people and their phones…
<CrazyTeacher> If you go to 4th normal form, you’d have a separate “join” table for EACH relationship between “Person” and “Address” or “Person” and “Phone”… You’d have a “join” for HomePhone, WorkPhone, etc…
<CrazyTeacher> Not fun huh…
<Aphanas> *nods*
<CrazyTeacher> The more COMMON approach is to put ONE join table in – and add a column that specifies WHICH…
<CrazyTeacher> In the case of the PHONE – PERSON link – you’d have PersonID, PhoneID, & PhoneType (Which takes values of Home, Work, Cell, etc.).
<CrazyTeacher> So – if you wanted to know which of your players had cell phones (& what the numbers were) – you’d use this table to join to name to the phone number…
<CrazyTeacher> Nifty enough?
<CrazyTeacher> Or – have I just run off and left you all behind?
<Aphanas> *nods* Though now I’m remembering why I didn’t want to become a DB programmer…
<RainTurtle> So far so froody…
<Aphanas> 🙂
<CrazyTeacher> LOL
<CrazyTeacher> Jael?
<Jael> So you’d have a table with: name, phone, and type, then a table that relates all those to each other?
<Jael> oops…question
* Jael notes that this is exactly where she got lost in the access experiment…
<CrazyTeacher> YOu have one table with names. One with phone numbers, and then one that relates them… You probably SHOULD have a “Type” table as well – but many folks don’t do that with just the type…
<CrazyTeacher> Ahhhh.
<Jael> okay…so a total of three are mandatory, but four would make it clearer?
<CrazyTeacher> No – three required – 4th (Type table) reduces “errors”.
* CrazyTeacher asks another leading question… Have any of you ever known someone with more than one “home” phone?
<Jael> ah…okay. So each “variable” needs its own table. I’m following _
<Aphanas> *nods* Which creates a problem for our current structure…
<CrazyTeacher> Jael – initially DESIGN it with one each.
<CrazyTeacher> Aphanas – not necessarily… you could create a “Home1” and “Home2” type ..
<CrazyTeacher> Not “nice” but, it works.
<Aphanas> *nods* Would have to change the variables that could be selected in the join table under “Type”.
<CrazyTeacher> If you went with Jael’s suggestion of PersonID, PhoneID & TypeID you wouldn’t you’d just add another row to the “Type” table.
<Aphanas> *nods*
<CrazyTeacher> See Jael – here’s where having all 4 tables is a good thing!!!!
<Jael> I’m following still…yippee
<CrazyTeacher> Ok…
<CrazyTeacher> The down side of doing the “Home1” “Home2” “Home3” approach – anyone see it?
<CrazyTeacher> (or them) ?
<RainTurtle> Which one would come up on a basic query?
<CrazyTeacher> That’s the right direction rain – but not all the way there…
<CrazyTeacher> You’d specify which one…
<CrazyTeacher> Remember our “question” up there? Where you were looking for gamers with cell phones?
<Aphanas> And how would you associate the home numbers with home locations?
<Jael> hmm…too many potentials – eventually they’d stop, but when?
<CrazyTeacher> *nods* Kinda crazy no…
<CrazyTeacher> One way – is to have a lookup for the “Type” table to simplify again (Home1 is a Home phone for example)
<CrazyTeacher> Another approach is to add a 4th column to our PersonPhoneType table.
<RainTurtle> It still makes sense, but it sounds like quite a headache for the poor guy putting the system together
<Aphanas> *nods*
* CrazyTeacher is glad folks didn’t suggest just putting two rows with the same type but different phoneIDs!
<CrazyTeacher> DB design is as much an art as it is a science…
* CrazyTeacher notes we haven’t even gotten to SQL yet…
<Jael> lol
<Aphanas> Heh…
<CrazyTeacher> So one approach is to do the PhoneTypeType lookup.
<CrazyTeacher> Another is to add in a “Sequence#” field
<CrazyTeacher> PersonID, PhoneID, PhoneTypeID & SequenceNo
<CrazyTeacher> You can then say “SequenceNo” zero is preferred (bad but done a lot).
<CrazyTeacher> Reason it’s bad – is a purist thing… IDs should NOT have “data” in them.
<RainTurtle> Ahhh
<Jael> No numbers or anything?
<Jael> hmmm….
<CrazyTeacher> Numbers are fine – but the number shouldn’t “mean” anything.
<Aphanas> *nods*
<Jael> ah…okay
<CrazyTeacher> A “BETTER” solution (speaking theory now) is to add a 5th column “IsPrimary” – a boolean value.
<CrazyTeacher> Using the Sequence to “rank” the phones is probably OK – most of the time!
<RainTurtle> Kind of like target IDs in Remote Viewing?
<CrazyTeacher> *nods*
<CrazyTeacher> You CAN have an ID like: “040616-1234”
<CrazyTeacher> But – that’s not good – since you can read that and realize it’s the 1234th target on June 16th, 2004.
<CrazyTeacher> Better would be to have that information in a separate table and just have a number like 1234566
<CrazyTeacher> I should note – just because theory says don’t do something – doesn’t mean you shouldn’t sometimes do it.
<CrazyTeacher> You should just THINK first.
<Aphanas> *nods*
<Jael> *nods*
<CrazyTeacher> A more common RV ID that I’ve seen looks something like “0616-0025”
<CrazyTeacher> And that meant June 16 target 25.
<CrazyTeacher> Anyone see a problem with that ID?
<Aphanas> It still has specific info on the target. It’s not totally random.
<RainTurtle> Depends….what does the database think about 0s in front of things?
<CrazyTeacher> Rain – generally since there’s that “-” character the ID’s a string – so leading 0s are fine.
<CrazyTeacher> Aphanas true, but not there yet…
<RainTurtle> For RV purposes, the target ID can be a date, if it is the date is of the tasking or the viewing itself, but not if it refers to what is viewed
* CrazyTeacher points at earlier RV ID…
<Aphanas> *nods*
<Jael> I was thinking there is no clear year?
<CrazyTeacher> I understand Rain – but for DB not the same.
<CrazyTeacher> Correct Jael….
<CrazyTeacher> How would DB differentiate Last years RV IDs from this years.
<RainTurtle> Poor little puter would get confused?
<CrazyTeacher> *nods* So would you – when you just looked at them…
<CrazyTeacher> That’s why I provided the 040616-1234 example earlier…
<CrazyTeacher> That does still have a problem – but, we probably don’t have to worry about it…
* CrazyTeacher wonders how many of us would be doing RV in 2104…
<Aphanas> *nods*
<CrazyTeacher> But – it IS an issue.
<Jael> heh…
<RainTurtle> Y2K all over again, eh?
<CrazyTeacher> Example Utility “right of ways”
<CrazyTeacher> Yep Rain.
<Aphanas> Yup.
<CrazyTeacher> Some utility companies have gotten “right of way” permissions – over 100 years ago…
<RainTurtle> hmm
<CrazyTeacher> The moral of the story… THINK about what you’re trying to do… Try some “sample” data…
<CrazyTeacher> Then, have someone ELSE look at it.
* CrazyTeacher will assume that folks will not go out and spend several thousand on a DB modeling tool…
<Jael> heh….
<RainTurtle> “Measure twice, cut once” as applied to computers…
<Aphanas> *nods*
<CrazyTeacher> Basically Rain.
<Jael>
<CrazyTeacher> Access lets you try several times to work things out…
<CrazyTeacher> OK SQL is a TLA.
<Aphanas> Heheh…
<Jael> heh….
<CrazyTeacher> T.hree L.etter A.ccronym
<CrazyTeacher> S.tructured Q.uery L.anguage
<CrazyTeacher> SQL is also commonly used to describe the DB as well as the language for working with the data.
<CrazyTeacher> SQL is actually two separate languages.
<CrazyTeacher> DDL & DML
<CrazyTeacher> DDL = Data Definition Language
<CrazyTeacher> DML = Data Manipulation Language
<CrazyTeacher> DDL – that’s what you use to create the tables and give them structure! Useful huh.
<Jael>
<CrazyTeacher> These “languages” go back to Mr. Date (preveiousl mentioned)
<Aphanas> *nods*
<CrazyTeacher> The first implementation of a Relational DB on a VAX computer was Ingres. And the language used there was “QUELL” I don’t recall what the accronym stood for.
<CrazyTeacher> This was in the early ’80s.
<CrazyTeacher> SQL is a living language – there are several “standard” version.
<CrazyTeacher> One problem/issue – I don’t know of ANY database that fully implements the latest “standard” in it’s entirety.
<CrazyTeacher> Have any of you heard of “Oracle” or “Sybase” or “DB2” by any chance?
<Jael> *nods*
<Aphanas> *nods*
<Jael> Don’t ask me anything about them…but I’ve heard of them
* CrazyTeacher could also add “Microsoft SQL Server” and “MS Access” to that list as well…
<RainTurtle> I’ve heard of Oracle…but not the other two
<Aphanas> Oracle resellers keep calling me… And we use Sybase for some of our products on campus.
<CrazyTeacher> Well – there ARE similarities to all of them (YEA) they are (more or less) Relational Databases.
<CrazyTeacher> ( mySQL is an Open Source DB )
<CrazyTeacher> I’ll stick to “lowest common denominator” SQL for this discussion – most of what I describe will work on ANY of them.
<CrazyTeacher> ( This IS a good thing )
<Jael> nifty
<RainTurtle> 🙂
<CrazyTeacher> First – DDL – since we need tables so we can store our data.
<CrazyTeacher> The first “command” is “CREATE TABLE <table_name> <attribute_list>”
<CrazyTeacher> Where “<table_name>” is the name of your table.
<CrazyTeacher> “<attribute_list>” is a tad more complicated…
<CrazyTeacher> For example – in simple terms – HERE’s the DDL to create our Person-Phone mapping table…
<CrazyTeacher> CREATE TABLE PersonPhone (
<CrazyTeacher> PersonID int not null
<CrazyTeacher> ,PhoneID int not null
<CrazyTeacher> ,TypeID int not null
<CrazyTeacher> ,SeqNo int not null
<CrazyTeacher> )
* Aphanas raises hand…
<CrazyTeacher> Aphanas?
<Aphanas> int = integer value in the field; “not null” = must have a value in the field?
<CrazyTeacher> You’re jumping ahead…
<Aphanas> Oops… sorry.
<CrazyTeacher> The stuff up there between “(” and “)” That’s the <attribute_list>.
<Jael> ahh….
<CrazyTeacher> This is a coma separated list (you did see those pesky little “,” characters didn’t you?).
<RainTurtle> mmhmm
<Aphanas> *nods*
<CrazyTeacher> The “chunks” were “AttributeName” (aka field name). Data Type and a boolean flag as to whether the field can accept a “null” value.
<CrazyTeacher> DB “Purists” believe that there should NEVER be a “Null” value… You should always put in a “N/A” or “Unknown” or some such value…
<CrazyTeacher> There are other “optional” things that can be there – depending on “dialect” of SQL…
<CrazyTeacher> Examples include “Defaults”, “Lookups”, and the like…
* CrazyTeacher notes there are other parts of DDL that let you modify or delete tables… But, he will leave those for now…
<RainTurtle> (might need a followup seminar for those, hm?)
<CrazyTeacher> 🙂
<CrazyTeacher> But with similar “create” statements we can create our OTHER tables as well…
<CrazyTeacher> Only problem… We’ve got a database full of tables – but no way to put data in there…
<CrazyTeacher> Not very useful huh…
<Jael>
<RainTurtle> hmm
<Aphanas> 🙂
<CrazyTeacher> I told you Mr. Date was a bright dude….
<CrazyTeacher> DML comes to our rescue!
<CrazyTeacher> It has kewords like INSERT, DELETE, UPDATE and such….
<CrazyTeacher> SOund like they might come in handy?
<Jael> *nods*
<Aphanas> Could be…
<CrazyTeacher> INSERT INTO PersonPhone ( PersonID, PhoneID, TypeID, SeqNo ) VALUES ( 1, 1, 1, 0 )
<CrazyTeacher> There’s an example that would put a row into PersonPhone.
<RainTurtle> good stuff
<Aphanas> *nods*
<CrazyTeacher> That’s about as simple as it gets…
<CrazyTeacher> It gets VERY complex too…
<CrazyTeacher> If you want to Add George Smith’s new middle name to his existing record the following would work
<CrazyTeacher> UPDATE Person SET MiddleName = ‘Finnegan’ WHERE PersonID = 222
<CrazyTeacher> This – of course – assumes that George Smith’s Person ID was 222.
<Aphanas> *nods*
<CrazyTeacher> But, this is powerful – look at our addresses… Say one of the street names gets changed… (Never happen you say…. Balony)
<CrazyTeacher> UPDATE Address SET “AddressLine1 = SubString(1, Position(AddressLine1,’OldName’), AddressLine1 ) + ‘NewName’ + (same kinda stuff to get rest of old address) WHERE AddressLine1 LIKE ‘%OldName%’
<CrazyTeacher> Woah – what’s that you say….
<Aphanas> That’s cool… updates the street name in all records?
<CrazyTeacher> Update the street name in all the records that have the OLD street name.
<CrazyTeacher> See WHERE is VERY important!!!!!
<Aphanas> *nods* Sorry, that’s what I meant.
<CrazyTeacher> Jael – Not all SQL DBs have nice snazy User tools…
<CrazyTeacher> Most tend to use text…
<Jael> Okay
<CrazyTeacher> Access does provide you with a fairly nice “GRAPHICAL” tool for designing your queries.
<CrazyTeacher> But, it’s not much help in putting that “substring” thing together…
<Aphanas> *nods*
<CrazyTeacher> MS SQL Server’s query tool – you type “text” in…
<Jael> okay…so pretty much, you have to ‘know’ your db in order to change stuff in it?
<CrazyTeacher> Ahhh no.
<Jael> uh oh…I think I may have just gotten confused….
<CrazyTeacher> If you have a “nice” interface on top of it – you just type – and it “does the INSERT/DELETE/UPDATE” for you…
<CrazyTeacher> In Access (for example) You just open a form (or tale view) and type – you don’t have to do the “INSERT INTO …. ” stuff
<CrazyTeacher> Have you ever used an online shopping website (like LandsEnd.com or BN.com) ?
<Jael> *nods*
<CrazyTeacher> You know when you place your order – you’re presented with a bunch of fields to fill in right?
<RainTurtle> yup
<Jael> *nods*
<CrazyTeacher> Well – YOU don’t need to know the structure of the DB they store the data in to fill in the fields do you?
<Jael> nope…though that was the nifty screen thing I was wondering about.
<CrazyTeacher> Some hack programmer had to write some code that takes what you keyed in and shove it into the DB.
<Jael> ah….
<CrazyTeacher> In MS Access – if you don’t push it too hard – the basic “forms” will do a lot of that for you as well.
<CrazyTeacher> Is that “clearer” ?
<RainTurtle> point, click, type…
<Jael> okay. I’m no longer confused….the programmer types in the long hand stuff like you showed us above, then that ‘script’ gets run from the nifty screen like on the web shopping sites.
* CrazyTeacher idly notes that “DELETE * FROM MyTable” is not a good thing to execute… WHERE is missing…
<CrazyTeacher> Exactly Jael.
<Aphanas> heh…
<CrazyTeacher> Though, in some cases you had a smart programmer type – and he wrote a little “program” that takes your input and “makes up” the correct DML query on the fly…
<CrazyTeacher> Aphanas – you laugh… It happens!
<CrazyTeacher> And – typically the result you get is “Zero rows affected”… Even if it deletes 2 million rows…
<Aphanas> *nods* I believe it… we’ve done similar things with net account DBs, or other records.
<CrazyTeacher> It also doesn’t ask you for confirmation.
<Aphanas> Ooh… that sucks.
<CrazyTeacher> It is VERY easy to shoot oneself in the foot with SQL…
<CrazyTeacher> Most DBAs have scars (& stories) to proove it…
<CrazyTeacher> DBA = DataBase Administrator or Archetect.
<RainTurtle> The word “oops” followed by colourful and improbable expressions describing the computer’s ancestry and personal habits?
<CrazyTeacher> Followed by mad scrambling in hopes of finding a recent backup…
<Jael> heh…
<Aphanas> Heh… pretty much. You can really scare users by saying “Oops” when working on their computers too…
<CrazyTeacher> 🙂
<RainTurtle> hehehe
<Jael> lol
<Dan> In Access, remember the “Query is your friend”