N00b wanna learn more about database programming

Discussion in 'Programming & Software Development' started by xhanatos, Jul 9, 2007.

  1. xhanatos

    xhanatos Member

    Joined:
    Mar 5, 2004
    Messages:
    6,040
    As per title, I want to learn 'databases.' I have a minor experience in my one of uni courses (it wasnt a dedicated database course) where I used MS Access to design a relational database complete with a frontend. All the stuff were built up on a prior excel project. I did this about a year ago so i've forgotten most of it.

    I want to pick it up again.. any advise on what book(s) to get and what database programming software I should use this time around? Should I stick to Access or learn mySQL or something else?

    It's not a job requirement, I just want to learn 'new' skills. Any advise would be much appreciated. Thaaanks.
     
  2. pongie

    pongie Member

    Joined:
    Dec 1, 2004
    Messages:
    1,050
    Location:
    Sydney 2166
    If you learn mySQL, you'll pretty much master access (not limited by GUI). If then you want to learn oracle it's not that different. So a good starting point would be mySQL.
     
  3. Ashpool

    Ashpool Member

    Joined:
    Feb 24, 2003
    Messages:
    3,352
    Location:
    Ye Olde Melbourne Town
    mySql is the go. Its free and a good place to start. Oracle is also free for development but setting it up is not recommended for the first timer.

    You can setup mySql on your local machine nice and easy. Once installed think of a few ideas that you could easily implement on mySql. Perhaps design a calender and contacts application. Its a good idea for a first step.

    Also try to design it from the ground up first. Make an entity relationship diagram and get it into the correct normalized form BEFORE you start creating tables and foreign keys. It makes things a lot easier in the long run. Also you can use access to create a pretty front end(forms and the like) and you can write your queries in sql in access. But try try to push all the smarts into the database itself.(Triggers, auto increments etc)

    I never found books to be really helpful especially when there is so much good and free info on teh web. The best way to learn imo is to create some applications for yourself.
     
  4. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    MySQL is a database, you don't really learn MySQL.

    You learn SQL. Structured Query Language.

    There are slight Syntax differences between different databases but it's all pretty much the same princible. (spelling?)
     
  5. OP
    OP
    xhanatos

    xhanatos Member

    Joined:
    Mar 5, 2004
    Messages:
    6,040
    Thank you very much for your suggestions!
     
  6. yihfeng

    yihfeng Member

    Joined:
    Nov 23, 2005
    Messages:
    1,400
    Location:
    Melbourne
    As mentioned above, go with MySQL. Access is more of a Database Wannabe :)
     
  7. mordy

    mordy Member

    Joined:
    Aug 30, 2001
    Messages:
    5,100
    Location:
    melb
    mysql is definately a good idea, but you would more likely want to read a proper db book which explains db concepts, they can then be applied to all dbs, not just the one u read about.
     
  8. ACodingFettish

    ACodingFettish Member

    Joined:
    Feb 7, 2004
    Messages:
    6,652
    Location:
    Brisbane
    sqlcourse.com i think it is has a free intro to SQL - the actual language itself, basic usage etc - but not databasing in general.

    databasing in general is a great thing to learn - I believe Microsoft SQL Server is free now (express version?) I think so could also learn that, really not much different AFAIK (from beginners point of view anyway)
     
  9. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    SqlServer 2005 Express is free, it can't be used on a web server unless your mirroring/culstering. Which means you only pay 1 lisence fee now.

    With 2000 you had to buy a lisence for each server, which is about 10k USD. Now its 1 lisence + the express version on each mirror/cluster.

    You can use it for learning, development etc etc tho.

    It has a LOT of fancy cool stuff which i haven't had a chance to play with yet. Like a XML Datatype, and in ASP.Net you can update the XML inside a table without having to pull it out, converting it to XML editing it and putting it back in as a string.
     
  10. prezident doom

    prezident doom Member

    Joined:
    Nov 24, 2004
    Messages:
    5,285
    Location:
    Brisbane
    as others have sugested give mysql or sql server 2005 a shot. and it really isnt the database provider that you need to get an understanding of (they all do the same things in different ways) its the sql that you need to have a good understanding of
     
  11. Blue_muppet

    Blue_muppet Member

    Joined:
    Feb 9, 2003
    Messages:
    487
    Location:
    Fairfield VIC, 3078
    Its sort of a tricky question, I think mordy has the best answer so far.
    Before you setup a database(s) you should learn DB concepts first.
    Stuff like ER diagrams (as mentioned), and all the primary/sec/foreign key-ness stuff. Once you have a pretty good understanding of the theory then it doesnt really matter what DBMS you pick up - but you need to decide which one you want to learn on.
    For example, Oracle is going to be different under the hood to Microsoft products, but at the end of the day the vast majority of the SQL will be similar. Most Oracle administration is done via Toad in an enterprise environment, while Microsoft apps use their own interfaces, and MySQL can be used either straight at command line (you dont want to do this for DDL/DML stuff) or interfaced with PHP fuctions, or you could do what I do (dont) and that is use mainframe to interface with Teradata which is another (leet) DBMS altogether.
    Once you know what DBMS you want to learn on then you can learn its semantics like how it handles date/time datatypes as well as all your joins.

    Id recommend downloading and installing (its free! yay) XAMP or LAMP for windows or 'nix, and creating a MySQL DB interfaced with PHP (but thats because im bias and have never learnt ASP or anything). You'll learn two (three if you dont know html) useful technologies there, except if you end up using myphpadmin for MySQL DDL changes, you're not really going to learn much DDL there. Ah yeah, and there is an absolute heap of tutorials on the net for php/mysql.
    </2cents>
     
    Last edited: Jul 10, 2007
  12. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,774
    Location:
    Briz Vegas
    1. By a book like "SQL in 24hrs" and learn basic SQL as in SQL99 and keep away from DB specific stuff like Stored Procedures and Views until you master plain SQL.
    http://www.amazon.com/Sams-Teach-Yo...0946062?ie=UTF8&s=books&qid=1184041965&sr=1-1

    2. Pick a front-end, web or whatever? Personally on this one I would choose either MySQL and (PHP or Python) and use ADODB, of course you could also choose .NET and MSSQL the choice is yours.
    http://www.amazon.com/Web-Database-Applications-PHP-MySQL/dp/0596000413

    3. Once you have mastered the basic of connecting a DB to a frontend you can start to explore the DB Specific extras like Stored Procedures (personally I dislike but thats my opinion), Views, Indexes etc etc....
     
  13. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I'm not bagging on you or anything but have you used Stored Procedures for a project before (not just trying them to see what they do).

    Because when i learnt them i thought they were retarded but after finishing 1 project it made life so much easier and now i can't live without them...

    ----

    Also if your using MySQL, MySQL 5 has MySQL Administration which i think has a Query program in it. I can't remember. But it doesn't require PHP so it means you dont have to worry about setting PHP/MySQL up and Apache etc.
     
  14. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,774
    Location:
    Briz Vegas
    1. I use stored procedures everyday and I still don't like them when they are used for Interface interaction, rather than administration which is why SQL/PL was originally created.

    2. Not to bag you or anything but your just a .NET MSSQL programmer what would you know about real programming and performance, with much, much less experience than myself, but your entitled to whatever opinion you wish, mine are based on 22 years working as a commercial programmer.

    I have used (as in been a DBA or Developer for over the past 22 years for) Oracle, DB3, Infomix, MSSQL, MySQL, Prostgress, UniData, Universe, Reality X, Jbase and good ole Pick/D3 in that time, but thats just my opinion.

    3. And your point? What does he use for a frontend once he has created his database? The original question mentioned Access, where he created the DB and the interface, so again what will he use for the interface?

    Yes MySQL has a Query Browser and Administration tool it also has a data modeling tool. http://www.mysql.com/products/tools/
     
  15. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    Fair enough.

    Just a .net mssql programmer. Way to assume things.

    No need to get anal about things dude i was just asking a question.

    That was for the original poster, but he referring to learning about SQL, not SQL+(insert interface).

    If he wants he could learn Ruby or JSP or PHP or ASP3 or ASP.Net or what ever he wants. But if he wants to sit down and learn SQL then a Query Browser is all he needs. Then if he wants to learn another language to interface the two he can.

    I'm sorry i tickled a sore spot :(
     
  16. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,774
    Location:
    Briz Vegas
    Sorry dude, I've been having a running battle with a 3rd Party Vendor, and lets just say the topics, Stored Procedures and .NET have been a very hot topic.

    I don't like stored procedures because they obfuscate the code when a well structured class could just as easily accommodate all DB interaction. And when a DB becomes large and has many fingers in the pie, and these finger don't always do things the same way, especially when embedded triggers are concerned things become messy.

    So I live by the rules,

    If it has to do with user interaction (interface) keep it in the source code.

    If it has to do with DB Administration, use whatever you want, including stored procedures.

    AND THE MOST IMPORTANT RULE

    If you need to use a trigger to maintain the integrity of the DB then fix the code that is cause the integrity you lazy bastards!


    I apologize for my pointed response, just a bit fiesty at the moment.
     
  17. mordy

    mordy Member

    Joined:
    Aug 30, 2001
    Messages:
    5,100
    Location:
    melb
    stored procs can easily be switched out for a well written DB access layer, it just depends wether your team has better programmers or better db admins.

    The theory is that its more secure to have no sql code in ur program, but what does sql code have to do with security, just make sure that theres no risk of sql injection and you should be fine.


    btw, i like your trigger rule, but i have a question, if i have a trigger replace a delete command instead to just mark the tuple deleted, performancewise is it better to use an update command from my program or does it make no difference, cause it really doesnt make much difference either way in terms of program complexity.
     
  18. stoj19.

    stoj19. Member

    Joined:
    May 6, 2007
    Messages:
    451
    Location:
    Melbourne, VIC
    w3schools.com IS BLOODY AWESOME

    http://www.w3schools.com/

    I can't stress how much it has helped in my SQL Projects

    I had to design a databse just recently using Access first and then PHP and mySQL

    :D

    here you go, a link to my database which utlises PHP and SQL:

    http://denstoj.com/Denstoj-Workshop.html

    (note: I designed EVERYTHING on that website)
    (note: It is still under construction)


    Cheers,
    :thumbup:
     
  19. GumbyNoTalent

    GumbyNoTalent Member

    Joined:
    Jan 8, 2003
    Messages:
    9,774
    Location:
    Briz Vegas
    Depends on the tuple, the sequence and the DB.

    I use these rules.

    1. Write data once reference many (relational), never delete, flag instead.
    2. Why delete data, disk is cheap data is priceless.
    3. If you must delete be 100% sure it isn't referenced else where and purge.

    So as a rule of thumb I would flag first delete later.

    But I'm very anal about data because I usually consult Banking/Financial. :)

    Yes I know performance, I'm yet to work on a database big enough to really worry about physical hardware performance of the SAN or Processors, and on DB's ranging from a few 100GB to 10TB+. In most instances the performance of the DB wasn't a bottleneck.
     
    Last edited: Jul 10, 2007
  20. Elyzion

    Elyzion Member

    Joined:
    Oct 27, 2004
    Messages:
    7,449
    Location:
    Singapore
    I almost always do Logical deletes. Especially on a shopping cart for things like an address, if a user deletes their address and add's a new one. I just have an Active field which is true/false. That way if they have made an order before i have the history.

    I think the only time i delete data is when i have a Many to Many relationship between 2 tables.
     

Share This Page

Advertisement: