Learning Database Concepts & Applications

[EngWiPy]

Hello,

I would like to learn the concepts of databases and practice them. I found it hard to find a good tutorial that teaches you how to start. All seem to be advanced to me!

Is there a book (or a good tutorial) that teaches you both; the concepts and the applicationsof databases at the same time?

Also, I noted that some tutorials use the MySQL workbench (which I have, and I'll be using) and others use the command prompt, does it matter how I MySQL as a beginner?

Thanks

 

[jedishrfu]

In many production environments, you may be limited in what you have. Hence, its good to know how to use the command line tool to do select queries, use describe and explain to get details on tables and how queries will be evaluated. Also how to adjust tables and move data around to archive or restore it.

One of my coworkers was so adept that he could use sql to write sql that did what he wanted. This was especially useful for recursive type queries that could be constructed directly without detailed knowledge of the database scheme.

I've seen a couple of books on sql hacks that have some cool recipes. You might check amazon searching with sql hacks or sql cookbook.

 

[EngWiPy]

Makes sense. Do you have a suggestion on how to get started in this path?

 

[jedishrfu]

The database I usually use is the H2 database that works standalone or as part of a Java program. It's very fast, quite flexible and comes with a command line access and web access.

H2database.org

Khan Academy has an intro to SQL

https://www.khanacademy.org/computing/computer-programming/sql

 

[EngWiPy]

Interesting. They have their own SQL Script. I guess the commands will be the same. By I noticed that many jobs ask skills with MySQL, that's why I wanted to learn SQL using MySQL.

I still need a good tutorial (or book) that explains the concepts and the terminology about databases, and if it includes practical examples, that would be even better.

 

[jack action]

I really like the MySQL documentation.

The problem with other sources is that they show you some very basic stuff. But SQL is full of very useful things that are needed, especially with very large tables. So the documentation is quite heavy, but in the end you'll find that it is necessary to understand it fully to do good table structures and efficient data manipulations.

I would suggest you start with the following chapters (in order), such that you don't get lost or bored too much. For most of the important stuff elsewhere, there is probably a link inside those sections to reach it. I would also focus on the innoDB engine first, as it is probably the one you'll use (although, I'm not bashing other engines).

Chapter 10 language Structure

This chapter explains the basic language structure. Pretty simple stuff if you are used to programming.

Chapter 12 Data Types

This chapter is essential to help you choose the proper data type (i.e. the one that takes the less storage space). Focus mostly on numeric and string types at first.

Chapter 14 SQL Statement Syntax

This is what I consider the tutorial part of the documentation, especially the first two sections:

• https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-definition.html
• https://dev.mysql.com/doc/refman/5.7/en/sql-syntax-data-manipulation.html

The section 14.8.2 is also essential to understand to learn how to build good queries.

Chapter 13 Functions and Operators

For when you'll want to do more complex comparisons or string manipulations.

Finally, I also suggest Rick's Rules of Thumb that helped me a lot understand the art and importance of proper SQL.

 

[EngWiPy]

I stumbled at the documentation before, but I was lost in it. Thanks for structuring what I need to read from it first.

 

[realAI]

You can take a 6 week online sql certification course through ed2go.com. For entry level SQL, it doesn't really matter which one you learn. Once you get to mid-level or advanced SQL, you will likely need to know SQL platforms.

 

[Greg Bernhardt]

As always learn from the ground up. Once you learn SQL and general data structures from top to bottom you can utilize different GUIs and helpers. Don't go the other way around.

 

[EngWiPy]

Right, but I want to practice as I learn the theory. That's how I learn programming languages in general.

 

[jtbell]

Right, but I want to practice as I learn the theory.

You can do that with the MySQL command-line client. You don't need a fancy GUI or IDE.

Jons-Mac-Pro:~ jtbell$ mysql -p music
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.50 MySQL Community Server (GPL)

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from composer where lastname='strauss';
+------+----------+-----------+-------------+-----------+-----------+
| ID   | lastname | firstname | nationality | birthyear | deathyear |
+------+----------+-----------+-------------+-----------+-----------+
| 1432 | Strauss  | Eduard    | Austria     |      1835 |      1916 |
| 1018 | Strauss  | Johann I  | Austria     |      1804 |      1849 |
| 1019 | Strauss  | Johann II | Austria     |      1825 |      1899 |
| 1020 | Strauss  | Josef     | Austria     |      1827 |      1870 |
| 1021 | Strauss  | Richard   | Germany     |      1864 |      1949 |
+------+----------+-----------+-------------+-----------+-----------+
5 rows in set (0.02 sec)

mysql>

Yes, this is an ancient version of MySQL. I plan to upgrade sometime after the end of the semester.

 

[Aufbauwerk 2045]

I found this book very helpful when I was learning database programming. You should be able to buy a used copy on Amazon very cheaply.

Jan. l. Harrington, Relational Database Design Clearly Explained. (Now in Second Edition at least).

What I like about her book is that she combines theory with some interesting practical examples. If you go through her book, including coding all the examples yourself and making sure they work, then you will have an excellent foundation in database programming.

BTW if you are ever looking for a lightweight alternative to mySQL which is easy to install on your PC, consider SQLite.

 

[jedishrfu]

Another one is H2 database. A pure Java database engine packaged as a single jar file with web server access to the database. Can be used as an application specific database or as a standalone database server. It supports most sql statements and is very fast.

Www.h2database.com

 

[jtbell]

For what it's worth, these are the books I used when I learned about MySQL, supplemented by online references and Google searches:

MySQL Tutorial, by Luke Welling and Laura Thomson
MySQL, by Paul DuBois
MySQL Administrator's Guide, by MySQL AB

These are all several years old, but probably have newer editions now, for newer versions of MySQL.

 

[256bits]

I would like to learn the concepts of databases

A database is just a list of information.
Writing down a shopping list is a simple database.
The dictionary is another.
A phonebook another.
You, as a person know, intuitively how to manipulate those databases to find the information you seek, such as the third item on the shopping list, or the page to flip to in the phonebook to find the telephone number of John Henry ( you could flip A's to B's to C's etc, but most people do a somewhat quicker search assume that "H" is somewhere around the third part of the book, flip there, and move back or forth as need be. Another search would be " How many people have "Henry" as the last name.

A computer isn't so smart, so you have to tell it each and every time you want to search for information, how to do it with a query.
You can have a database organized as one of the two major groups - a flat database or as a relational database, such as:
http://ictlounge.com/html/types_of_databases.htm
By the way, flat databases, can be quite large, so the "simple" comment doesn't really come across correctly I think.

You can have database with the query language understanding built right in, so it stands alone, and your program queries and receives the information to and from for the user to see. Or you can build the database into your program, so that the program and database become inseparable.

Things to know might be sequential files, random access files, sorting data, arrays, etc and all that fun stuff for the manipulation of data. B-trees, binary trees, linked lists, hashing, indexing, etc.

With a relational database you have to look at how to organize your tables, so that information isn't duplicated needlessly.

Your comment about wanting to learn databases is what drove me to write a little bit about the "behind" the screen".

 

[jedishrfu]

There's a set of rules that database designers try to follow to properly organize their database without duplication and for database vendors to offer products that are truly relational:

https://en.wikipedia.org/wiki/Codd's_12_rules

For me the rule that affects folks most is probably rule 3 on the meaning of null values:

Rule 3: Systematic treatment of null values:
Null values (distinct from the empty character string or a string of blank characters and distinct from zero or any other number) are supported in fully relational DBMS for representing missing information and inapplicable information in a systematic way, independent of data type.

This rule often affects how SQL statements return your data when trying to match things up during table/view joins.

https://en.wikipedia.org/wiki/Null_(SQL)

and three valued logic:

https://en.wikipedia.org/wiki/Three-valued_logic#Computer_science

As an example, in a book owners table you might ask how many people own zero books so of course you'd count those with a zero book count but what about the folks that have a null value meaning we don't know how many books they have.

Should they be included in the count? The answer is it depends on the situation but you have to be aware and decide how you'll handle nulls to get the answer you want.

If you want a definitive count then you might exclude the null owners. However if you want a count of people you plan to sell books to for the purpose of estimating the number of book catalogs to print then perhaps you want to include them.

 

[jedishrfu]

More on NULL values:

https://www.tutorialspoint.com/sql/sql-null-values.htm