Welcome to the Visual Case Database tutorial, intended as an introduction to relational database design using Visual Case.

Currently, Visual Case allows you to design databases for the following database management systems (dbms). 

• Microsoft SQL Server 6.5, 7 & 2000
• Microsoft Access 97, 2000 & XP
• MySQL
• Oracle 8i and 9i 
• Sybase 12.5 and 7 
• Interbase
• Pervasive SQL
• PostgreSQL

With Visual Case's database design tools you can also:

• Convert between databases
• Reverse engineer most ODBC sources
• Forward engineer to the above database management systems
• Edit SQL text with our comprehensive SQL editor

This tutorial will walk you through the concepts involved in designing relational databases.  Before beginning, you should download the trial version of Visual Case which can be used free of charge for 30 days.

Tables

The core element in any database design is the table.  Essentially a table is a repository for sets of related information.  Below is an example of a simple table called Employee and some data that it stores.

Our Employee contains three records.  Notice that the three fields or columns Name, City and Salary are all important to an employee.  One would not create a table with unrelated information in each row or record.  For example, columns such as Slope, Latitude, or Government would not be added to this table, but columns such as Email, Address and Last_Name might be useful.  The idea when designing tables is to keep the information relevant to what is being stored.

In the above example notice that each column stores a different kind of information.  The kind or type of information stored is called the datatype of the column.  The columns Name and City store text information, while Salary stores currency or money information.

Above is the Employee table defined in Visual Case for Microsoft Access XP.  When you change the database type, the data types (Text, Currency, etc.) will change as different database management systems have different datatypes that they support.  Below is the Employee table as it would look if we switched to Oracle:

Notice that the Salary column is now just a number.  This is because Oracle 8 does not have support for a money or currency field.  This is okay though since the data is really just a number anyway.

Changing Database Types

You can change the type of a database that you are designing in Visual Case.  When you change the type of a database, Visual Case will automatically change the datatypes of the columns within the tables to match the new dbms.  This is not exact however, as sometimes datatypes aren't supported in various database sytems (as seen above with Oracle and the currency field).

Indices

In a large database, tables will often have hundreds, thousands or even more records in them.  A large company may have tens of thousands of employees around the world, each occupying a record in the Employee table.  Indices are important in maintaining data integrity and order within the table.

An index is a set of columns from the table that have certain constraints on them.  In our employee table, we could create an index on the Name field indicating that it must be unique.  Our index would then be called a unique index, and if a new record was added to the table that violated our index, an error would occur.

In most relational database management systems we can create a special kind of index called a primary key.  The set of columns that make up a primary key are special as they can uniquely identify a record in the table.  Take another look at our simple Employee table:

We can create a primary key on the Name column.  This is valid for if we ask for the employee named "Mary" only one record or row is found.  Below, a new record has been added to our table:

Now, if we ask for the employee named "Mary" there are two results, the one in Halifax and the one in Toronto.  To solve the problem, we can add City to the primary key.   The combination of Name and City now uniquely identifies each record in the table.

Creating a Primary Key with an Auto Numbered Column

You may have noticed that we still have a problem.  We would like to have a primary key as we need a way to uniquely id the employees, but it is likely that there is more than one employee with the same name and city.

Sometimes (as in our Employee table) there is no combination of columns that is convenient to uniquely identify a record.  You can create a column (Employee_ID in the example above) and set it to be automatically numbered.  That is, each time a new record is added to the table, the value is automatically set to a unique value.  In the example above, a primary key has been created on the Employee_ID column.  Columns that are in a table's primary key are underlined on the diagram.

Foreign Keys

After tables, foreign keys are arguably the most important thing in a relational database.  Once you have some tables in your database, a way is needed to connect all of the data together.  Foreign keys, are the easiest way to do it.

Above, the Employee table has been changed.  You will recall that previously, the City field was stored as text.  This could create problems and waste time if each time the city has to be typed in.  In the table, you could end up with errors and inconsistencies with employees working in 'Winipeg' and 'Winnipeg' for example.

Instead, we can create another table called city:

Now in the Employee table, only the unique identifier of the City is referenced.  This has the following advantages:

• The city name is only stored once in the database.  Multiple tables and records may reference the same city, but in each case only a number is stored, not the entire name.  This can save a significant amount of space on disk.
• The incidence of errors is reduced in the database.  Further, if a city changed its name, only one change need be made in the database.
• Other information could be stored about a city in the City table.  For example, in Canada you may wish to store the English and French versions of a name to display to different users.  With the design above, the underlying data need not be changed and our multi-lingual display becomes transparent.

Foreign key relations can be used between any tables.  A client may have many purchases, a library may have many books, a school many classes and a class many students, and so on.

Below are some rules to remember when creating foreign keys:

• The foreign key goes from child table to parent table.  In our example, the Employee table is the child and it references the parent table City.  Visual Case shows an arrow pointing to the parent table.
• The child table is said to be the owner of the foreign key.  Above, the foreign key is considered a constraint on the Employee table.
• The child table must include the columns defined in the primary key of the parent table.  That is, each record in the child table, must be able to uniquely identify a record in the parent table by its foreign key.

Multiplicity

In the example of Employee and City, the multiplicity is said to be many to one.  For each employee there is one city, but for each city there are many employees.  In Visual Case you can specify the multiplicity on a foreign key (unless not supported by the selected database type).

Recall the constraint on a foreign key that the child table must uniquely identify a single record in the parent table.  This means that the multiplicity of the parent table is always one.  The child table can have the following multiplicities:

• Many
• One
• Zero or Many
• Zero or One

On the rare occasion in your designs you will run into a situation in which you need a many to many multiplicity on a foreign key.  A course has many students and a student has many courses.

In the case of a many to many relationship, you need to create an intermediary table.  The way it works is that each record in the Student_Course_Connection table connects a single student with a single course.  Each instance of a student taking a course is represented as a row in the intermediary table.

Putting it all Together

You now have all the basics required to design a relational database.  Beginning with tables you can build storage compartments for the various data that need to be maintained in your system.  Indices on the table allow for easier access as well as control and validation over the data.  Finally, foreign keys allow you to relate the data in one table to that in another.

When designing your database try to keep each table focused and straightforward.  Each table should only have the information relating to the data being stored.

Also, look for situations where a related table would better serve the purpose as we did above with the City field.  In general, you want to avoid duplicating data entry and storage.

Don't be afraid to have a greater number of smaller tables.  In our student and course example, the school may specify that a student can take no more than 6 courses.  It may be tempting to put Course_ID1, Course_ID2... Course_ID6 columns in the Student table and avoid the need for the intermediary table to support the many to many relationship.  You need to think ahead though.  What will be required if down the line the rules are changed and a student can take eight courses.  All of a sudden you have to change the database design (and convert the existing data) as well as your code and the user interface for your system.

Finally, download the 30 day free trial of Visual Case and get started.  You will find designing databases a snap with our design tools.

Visual Case^TM  is a trademark or registered trademark of Artiso Corp. Canada.

Microsoft, Windows, SQL Server, VB, Visual Basic and Access are registered trademarks of Microsoft Corporation.
Oracle is a registered trademark of Oracle Corporation.
Unified Modeling Language and UML are trademarks of Object Management Group Inc. in the U.S. and other countries.
Other brands and their products are trademarks or registered trademarks of their respective holders and should be noted as such.