Database Design and SQL

Building a database is like building a house - you have to get it right at the design stage (also called the modeling stage). Once all the walls are up and the plumbing is installed, it's a bit late to realize that the bathroom should have been three feet wider. It can be done but, it gonna cost you, baby!



So, in these lessons we're going to spend some time on database modeling. By studying several examples of database applications we'll define what the rules are for building a model of a database that we can then expand to cover all kinds of situations. Modeling is what we do when we are designing a database. It's sort of like building a model car except that it's not 3D and it's not plastic. Our model is on paper, or in a modeling program's data file, but it does have a lot of parts that must be assembled in a logical fashion and must all be glued together to work properly.

Want to try the great Linux OS without risk? There's a small distribution that will run from within Windows - you can explore as much as you want. Visit Linux4Windows for the free tutorials, downloads and articles. Linux is a natural fit with MySQL. You really must give it a try!

About the SQL language Once the modeling part is down pat, we will create the databases and learn how to work with them using the SQL language. SQL is the lingua franca of databases. It allows you to communicate between relational databases from Microsoft, MySQL and Oracle, to name just a few. We will look at all the important SQL commands that need to be mastered, again through the use of many examples and exercises. Throughout this tutorial we will use Microsoft Access 2003 (from Microsoft Office 2003 Pro) as the source of most of our examples. Almost all the sample databases are built in Microsoft Access.

You might want to take a look at the Microsoft Access tutorial elsewhere on this site. It also covers many aspects of database design with some very concrete examples.

Gradually, we are including MySQL into the tutorial. The MySQL database server is a powerful, very versatile server which is part of the whole open-source environment along with other tools such as the Apache Web server, the PHP language and the Open Office suite. All those tools are readily available free on the Internet. They are now a credible alternative to the proprietary software sold by Microsoft and Oracle. And since the recent announcement of a joint venture between Sun and Google, you can bet that we'll be seeing a lot of action in that area in the coming months and years!

In addition to the database server itself, MySQL has a whole series of tools that work with it. Most of the tools are developed by third-party vendors who usually sell them but there are always free versions of these clients that help to make the developer's life a lot easier. Since new tools are coming out all the time you may see different screen shots of the examples in different lessons as we update things. We'll try to make it not too confusing!

To highlight some of the uses of the SQL language we've developed a sample project that uses a Visual Basic 6 client to connect to our MySQL database through SQL commands. Once you've mastered the SQL language you may want to look at the Visual Basic 6 ADO database programming project which is an introduction to the use of a visual client in extracting and manipulating data from a relational database via SQL.

Even if you are still a user of Sybase Powerbuilder (which is the subject of another tutorial, in French, at WebProfesseur database programming projects) you can run all the examples using SQL Anywhere which has all the tools necessary to create and manipulate databases. As an aside, I used to work with Sybase Powerbuilder and I taught courses on it. I loved it for development work. But for some reason it never caught-on much here in Canada and now I don't know of anyone who actually uses it. It's too bad.

And if you're looking for some great information on Visual Basic 6.0, you heve to check-out this site: Visual Basic 6 programming tutorials.

Tutorial Content

Lesson 1

• A short history lesson on databases
• Steps in the database design process
• Data modeling - terminology, graphical representation
• The Entity-Relationship (E-R) Diagram

Lesson 2

• Describing the 'Project Management' database
• Normalization of database relationships
• The one-to-many relationship
• The many-to-many relationship

Lesson 3

• Introducing the SQL language
• SQL syntax
• Table manipulation instructions: CREATE, ALTER, DROP
• Data manipulation: INSERT, UPDATE, DELETE

Lesson 3B     

• Introducing the MySQL database server
• Installing the MySQL server
• Installing the MySQL Administrator
• The MySQL GUI clients:
                 DBManager Pro from DBTools Software
                 SQLyog from WEByog

Lesson 3C     

• Creating the Project Management database
• Using the Query Editor
• Using the Table Editor
• Importing database from Microsoft Access into MySQL

Lesson 4

• Case study - the Bids Management database
• Creating queries - the SELECT statement
• The WHERE clause
• The DISTINCT clause
• Working with dates
• Date and Time functions in MySQL

Lesson 5

• New case study in MySQL - The 'Bids Management' database
• Aggregate functions: sum(), count(), avg(), min(), max()
• Subqueries with aggregates
• Other uses of subqueries

Lesson 6

• Sorting data - the ORDER BY clause
• Grouping data - the GROUP BY clause
• Selecting groups with the HAVING ... clause

Lesson 7

• Refining the 'Project Management' example
• Using multiple tables
• Joining tables
• The JOIN formula

Lesson 8

• Using aliases with duplicate field names
• Another use of aliases
• Outer JOINs

Lesson 9

• Advanced topics
• The UNION query
• Adding a 'Totals' row using UNION
• The power of the Crosstab query

Download

• Sample databases: Microsoft Access
       with MySQL databases