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 should 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 versatile 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 as the source of most of our examples. Almost all the sample databases are built in Microsoft Access.

Even though this course was created with examples from Access 2003, you might want to take a look at the more recent version of MS Access, Microsoft Access 2010 tutorials. 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!

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