• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Tim Cooke
  • Campbell Ritchie
  • paul wheaton
  • Ron McLeod
  • Devaka Cooray
Sheriffs:
  • Jeanne Boyarsky
  • Liutauras Vilda
  • Paul Clapham
Saloon Keepers:
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:

Need information about databases for career - SQL and NoSQL

 
Ranch Hand
Posts: 257
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Hi ! Its me again ! This time, I have a couple of questions on which I hope you could shed some light.
(If any of you beginners are reading this and are as confused as me, then feel free to add questions to this list. It could serve as a rough guideline for others like us.)

1 - Which one is the most widely used DB and which ones are losing popularity (and why) ?

2 - Which DB should I use for making a JavaScript and PHP based project for myself ?
I intend to use this project on my resume.

3 - Where can I get comprehensive information about the differences between various DBs ?
Are SQL Server, Oracle, MySQL, DB2 almost the same, with minor differences in syntax ?

4 - Is it advisable for a beginner to learn NoSQL initially or do it after SQL ?

Thanks in advance.

Extra - So many of them here !


 
Bartender
Posts: 2407
36
Scala Python Oracle Postgres Database Linux
  • Likes 3
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Uh oh. Looks like it's just you and me again, Andy. Oh well, here goes.

SQL databases

Virtually every organisation larger than your corner store will be using a relational (SQL) database for something. Businesses, government organisations, colleges, hospitals etc etc. Almost every significant business application needs a datastore, and in the vast majority of cases that store will be a relational database. So if you want to work on business applications that use data (pretty much all of them), you need to know something about relational databases.

Every RDBMS (relational DB management system) is ultimately based on the relational model, which is a robust, flexible and logical approach to modelling data based on mathematical logic and set theory. There are variations e.g. you usually need to denormalise your relational data model to improve performance, and most importantly, data warehouses use a different approach to data modelling - the star schema - which is based on a different logic but is often implemented on a conventional RDBMS platform (with some tweaks).

So if you want to learn about DBs, you could buy a college-level textbook on relational databases, or follow a suitable online course like the one on Coursera, and you definitely need to learn SQL properly. All the RDBMSs support SQL, although there are typically various proprietary extras on different commercial DBs - some of these are very useful, others are simply variations on a theme. Whichever route you take, you need to understand at least the basics of the standard relational approach and SQL before you can appreciate how to use it, when not to use it and how to pick and use an appropriate alternative approach - star schemas for DWH, NoSQL etc.

The most widely used proprietary RDBMSs are probably Oracle, IBM's DB2 and Microsoft SQL Server. There are free versions of these available for download, so you can pick one and start learning it using the relevant online tutorials e.g from the vendor.

As an Oracle developer, I've naturally seen a lot of Oracle systems, so I know Oracle is very widely used for all kinds of systems from small departmental applications to massive DWH systems serving multi-national corporations. As for the others, my impression is that IBM DB2 tends to be found in big data centres for large organisations like banks, but I don't know much about it. SQL Server has traditionally been less popular for large scale applications (this may be changing), but very popular for mid-sized applications, possibly because of the easy integration with Microsoft development tools. Personally, I prefer Oracle but obviously I'm not exactly unbiased here!

There are also specialist databases for big data warehousing systems e.g. IBM Netezza, Teradata, Exadata etc. I'm not aware of any free options for downloading these, and to be honest if you want to learn about DWH and star schemas, you'd be better off doing so on a conventional RDBMS first.

The two main open source relational DBs are MySQL (now owned by Oracle) and PostgreSQL. There are also embedded SQL databases such as SQLite which are aimed at lightweight database applications, but I haven't used these.

MySQL is very widely used for small/medium sized web applications (it's the default datastore for most of the free content management systems, for example). It's very popular with people learning about DBs or developing DB applications e.g. prototyping with Java. I'm not sure how scalable it is for larger applications by default, but I seem torecall reading that Google used grids of MySQL servers running in parallel to serve some of their applications. There is also an "enterprise" version, but I'm not sure how widely it's used.

PostgreSQL seems to be less widely used than MySQL, but my own impression is that it's the more robust and mature of the two databases. It's definitely used in large applications, but is equally suitable for smaller apps as well. It includes support for an implementation of Oracle's PL/SQL language for stored procedures, and there is a paid-for variation of PostgreSQL (EnterpriseDB) which is marketed as an alternative to Oracle for commercial applications. PostgreSQL is also particularly popular for spatial data, because it's always had excellent support for specialist spatial functionality via the PostGIS add-on.

Having used both occasionally for small projects, I much prefer PostgreSQL to MySQL, but I seem to be in the minority there. There are plenty of books and online tutorials for both of these.

NoSQL databases

There is no single "NoSQL" model, because each NoSQL database has its own approach to modelling data. Typically these DBs aim to provide better scalability than the traditional RDBMS (among other benefits) but they usually have to sacrifice other RDBMS features in order to do so e.g. they may not guarantee that your transactions are always consistent at any given time, may not support arbitrary queries etc. This combination of different data models and different properties means that different NoSQL DBs may be appropriate for different applications. This is great, as it provides extra options for people who are struggling to achieve their goals with a conventional RDBMS. However, there is also a danger of people reaching for fashionable NoSQL solutions simply because they never bothered to learn how to use a conventional SQL database properly and they think the NoSQL DB will somehow make all their problems go away. Don't be one of those guys!

The best place to start learning about NoSQL is the excellent book Seven Databases In Seven Weeks by Eric Redmond and Jim Wilson. Buy it now.

If you want to get some free hands-on training in a NoSQL DB, 10Gen are starting a fresh online course in MongoDB For Developers in the New Year, using Java or Python (but you don't need much of either). I just finished the first presentation of the Python course and it was excellent.

Your DB project:

  • Definitely start with a SQL database, because this is what most people use, so the tools and tutorials will support this, and you need to understand SQL DBs before you can appreciate the NoSQL DBs properly. Any of the free relational DBs above will be suitable, but you may find that a lot of tutorials etc assume you're using MySQL.
  • As for your choice of implementation language etc, PHP is fine (lots of tutorials available), but if I were you I might look at something a bit more commercial that will get you using a proper OO language from the start.
  • You could leap in and start with Java (using servlets, JSP etc), or try one of the MVC-based "coding by convention" frameworks like Ruby on Rails, or Grails, which would give you a good structural framework for your application as well as being fun to use.
  • Whichever DB and server-side implementation language you choose, you can still use lots of HTML, CSS and JavaScript to make your browser interface as cool as you like.


  • Final words of advice:
    Pick a database, download and install it and start coding your own web/DB application. You'll learn much more that way than reading endless rambling diatribes from superannuated Oracle developers on JavaRanch!
     
    Andy Jack
    Ranch Hand
    Posts: 257
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    chris webster wrote:
    Your DB project:

  • Definitely start with a SQL database, because this is what most people use, so the tools and tutorials will support this, and you need to understand SQL DBs before you can appreciate the NoSQL DBs properly. Any of the free relational DBs above will be suitable, but you may find that a lot of tutorials etc assume you're using MySQL.
  • As for your choice of implementation language etc, PHP is fine (lots of tutorials available), but if I were you I might look at something a bit more commercial that will get you using a proper OO language from the start.
  • You could leap in and start with Java (using servlets, JSP etc), or try one of the MVC-based "coding by convention" frameworks like Ruby on Rails, or Grails, which would give you a good structural framework for your application as well as being fun to use.
  • Whichever DB and server-side implementation language you choose, you can still use lots of HTML, CSS and JavaScript to make your browser interface as cool as you like.


  • Final words of advice:
    Pick a database, download and install it and start coding your own web/DB application. You'll learn much more that way than reading endless rambling diatribes from superannuated Oracle developers on JavaRanch!



    Thanks again ! I chose SQL server, but not because of technical reasons. But, only because I found better books (sql joes 2 pros - its like a head first) for SQL Server, but not for Oracle and MySQL. That for me is a HUGE <fireworks!!!> factor. Learning becomes
    meaningful and easy with books like these. Don't know if it will go well with PHP because all I hear is PHP, MySQL and never PHP <AnotherDB>. Can I swap PHP with Python ?
     
    chris webster
    Bartender
    Posts: 2407
    36
    Scala Python Oracle Postgres Database Linux
    • Likes 1
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Andy Jack wrote: I chose SQL server, but not because of technical reasons. But, only because I found better books (sql joes 2 pros - its like a head first) for SQL Server, but not for Oracle and MySQL. That for me is a HUGE <fireworks!!!> factor. Learning becomes meaningful and easy with books like these. Don't know if it will go well with PHP because all I hear is PHP, MySQL and never PHP <AnotherDB>. Can I swap PHP with Python ?


    I haven't done any development work with SQL Server, but I know lots of people use it e.g. with Java/JEE but also (most often?) with Microsoft tools on the .NET platform. Not sure about how widely it's used with free open source languages like PHP or Python - if people want to pay for a proprietary SQL Server licence they're generally happy to use proprietary tools like C# anyway (which is what Microsoft want them to do of course). But if you're choosing between PHP and Python, then all else being equal I'd go with Python, as it's a much cleaner OO language which will help you to learn OOP properly, and Python has good support for lots of different databases via its DB API.

    There will certainly be a way to use SQL Server with PHP or with Python, but you may need to search around a bit to find out how to get the appropriate drivers set up and working properly. FYI the drivers are the programs that communicate between your program code (in Python, PHP, Java etc) and the database. Java-based systems can all use Java (JDBC) drivers of various kinds, while other languages use different types of drivers to talk to a database e.g. via ODBC.

    Have fun.
     
    Sheriff
    Posts: 3837
    66
    Netbeans IDE Oracle Firefox Browser
    • Likes 2
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator

    Andy Jack wrote:But, only because I found better books (sql joes 2 pros - its like a head first) for SQL Server, but not for Oracle and MySQL. That for me is a HUGE <fireworks!!!> factor. Learning becomes meaningful and easy with books like these.


    Just a note: Oracle has an excellent documentation. You just need to know where to start, because there is about hundred individual books of documentation for the entire database (many of them pertain to specific features and you don't need to read most of them, of course). You just need to know that it is necessary to start with the Concepts Guide. The main documentation page can be useful too.

    Everybody has a different taste probably, but my opinion is that you don't need a book to learn Oracle - the documentation is more than enough for that. (I've actually printed some books of the docs that I was most interested in to be able to read them off-screen, which was perhaps more costly than a regular book, but that's a different story.)
     
    reply
      Bookmark Topic Watch Topic
    • New Topic