-
Step 1
Format for displaying data from a table of a relational database.Review the basic ideas behind relational databases. When database technology was first developed, it was common to store information in a single, large file, similar to a spreadsheet. This format made it difficult to do data analysis, and from those difficulties, relational databases were conceived. The term "relational database" refers to a database consisting of many different sets of data which relate to each other. Relational databases organize information into "tables," which contain different "fields" or "columns" that store data. For example, a website database might include a table called "UserData" which stored information about site users in columns like "UserName," "FirstName" and "LastName."
The term "column" might seem confusing looking at the database diagrams that follow. It comes from the way databases frequently display data stored in the tables, where each of the fields or columns is shown at the top, with the actual data in rows below, as shown here. You can read the information stored in databases much like the way you would read a spreadsheet. -
Step 2
Look at some examples of relational databases to better understand how they work. Take a look at this diagram of a database that stores information about users of a website.
In this example, the website using this database allows users to create multiple avatars that are linked to their user account. On the left, you see the database table "UserData" which stores basic information about users. In addition to the user information, the table assigns a number to each user called "UserID," which is called the "primary key."
Primary Keys are always unique, meaning the same number or value can only occur once. Primary keys provide a dependable way of linking one table to another. In the data shown from the UserData table in the image from Step 1, you can see that each UserData record is assigned a different UserID number, which will never be allowed to repeat. -
Step 3
Diagram of the relationship between two tables in a relational database. (Image by Violet Mabe)Apply this knowledge to understanding a diagram of a real database.
In this diagram, we also see the table "UserAvatars," which stores information about the avatars created by each user. In order to connect the avatars back to the proper user record, UserAvatars also contains a column called UserID. Because one UserData record can be linked to many UserAvatars records, this is called a "one to many" relationship. To retrieve the UserAvatars records associated with a specific user, you can ask the database to display or return only UserAvatars records with a specific value in the UserID column. Using the data in the Step 1 diagram, if we wanted only the avatars created by user "coolken," we would ask the database to return all UserAvatars records where UserID is equal to 2, which is the UserID value assigned to "coolken." A data request like this from a database is called a "query." -
Step 4
The relationships between tables UserAvatars and AvatarImages (Image by Violet Mabe)Learn about the different types of relationships that are possible in a relational database. We've already discussed the "one to many" relationship, in which one record in a database table is connected to many records in another. The relationship between UserData and UserAvatars we reviewed in Step 2 is a "one to many" relationship.
When you have one record in a database table that can be connected to only one record in another table, this is called a "one to one" relationship. You might find this in a database that store a user's username and password in one table, and their name and address in another. In this example, each user could only have one username and one address, thus creating a "one to one" relationship.
A "many to one" relationship occurs when many different records in one table are linked to a single record in another. If we extend our example from the previous steps, suppose that every avatar can use one of ten different available images, which are stored in a table called AvatarImages. Each AvatarImages record has a unique ID, AvatarImageID, which is that table's primary key. Each UserAvatar record, then, contains a link to AvatarImages via AvatarImageID. As a result, because many different avatars use the same image, there are many UserAvatar records linked to each AvatarImages record, creating a "many to one" relationship. The image here shows a diagram of this relationship. -
Step 5
Review how data types affect relational databases. In addition to a name, each column in a relational database is assigned a specific "data type" that defines the type of information that column can store. Common data types include text, number and date. Other less obvious data types include: integer, which stores whole numbers (no decimals), float, which stores numbers that can include several decimal places, char, which stores a specific number of characters, filling in any shorter value with spaces, boolean, which stores either "true" or "false" (sometimes represented as "Yes" and "No," or "1" and "0") and binary, which can store file data, like images or document files.
A column with one data type cannot store anything that isn't a valid value for that data type. An integer column will show an error if you try and put a letter or word into it. Likewise, a column of one data type generally cannot have a relationship with a column of a different data type -- i.e. the UserID column of UserData can't be linked to the AvatarName column of UserAvatars, because one is a number and the other is text. Different database software packages have differing tolerances for converting information from one type to another. Some databases can convert number data types to text types automatically, while others won't automatically convert any data types. -
Step 6
Put it all together as you review a complete database. As a hint, you can view a diagram of the database's relationships by selecting Tools and then Relationships.
There are many other example databases available on the web for whatever database software you use. Keep in mind what you learned as you look at the database, and check out the documentation for your database software for more information on how to access information about the database, and how to view the types of diagrams shown here.
