10. Database Diagrams

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

Diagrams make a database and its relationships much easier to understand. In this lesson, we’ll introduce the basic conventions of database diagrams, and explain a common database structure.

Lesson Notes

Lesson Goal

The goal of this lesson is to learn about conventions when creating or viewing a diagram of a database and to learn about database structures.

Tables and Relationships

Tables appear in a database diagram as a box. The name of the table and a list of its fields are usually given. A relationship between tables is indicated by a line between them.

One important aspect of a relationship is cardinality. This indicates how many times a particular value can occur in each of the tables. For example, when creating a relationship between products and sales, each product appears only once in the product table, but many times in the sales table. Relationships can be either one-to-one, one-to-many, or many-to-many. One-to-many relationships are the most common in most databases.

Star Schema

In many databases, the tables will form a particular structure. One of the most common structures is the star schema. In this structure, a single fact table forms the center of the database. It provides information on some important business process. It is then related to several surrounding dimension tables, which provide information on various objects and things that are related to the process. There are many other database structures, but we don’t consider them in this course.

 

Transcript

Over the past few lessons, we've learned a lot about databases.

We focused mostly on looking at individual tables or on joining two tables together.

In this final lesson of this course, we'll learn about database diagram conventions and database structures.

Diagrams are a useful way of visually understanding how the tables in a database are linked to each other. The topic of database diagrams is quite an extensive one, but in this lesson we'll demonstrate some of the very basic principles you'll find in any of these diagrams. Here we can see how tables appear in a database diagram. We can see that the name of the table is given, as well as all of the names of all the fields in that table.

Sometimes you may see additional information here, but this is the most common appearance of individual tables. As we saw in the previous lesson, a particular field may be found in more than one table. This is illustrated on a diagram through the use of relationships. Relationships are illustrated by drawing a line between the two relevant tables. Here we have a product details table, and a sales table.

Both of these tables contain a produce name field.

In the product details table, the product name is the primary key that indicates the name of the product. In the sales table, it's a foreign key that indicates the name of the product from a particular transaction. This field allows us to create a relationship between the two tables which is indicated by a line joining the two tables.

One important aspect of relationships is cardinality.

Cardinality tells us how many times the related field appears in the two tables. In this case, each product appears only once in the product details table.

However, each product can have multiple sales and can appear multiple times in the sales table. As a result, this is called a one-to-many relationship.

This is represented by the numbers at either end of the relationship line.

The number one appears at the product end of the line while many is indicated by an asterisk at the sales end of the line.

One-to-many relationships are the most common relationship in most databases.

However, one-to-one relationships and many-to-many relationships are also available.

When you look at a visual representation of database relationships, you'll often notice the tables form particular patterns. Let's consider one common layout known as Star Schema. This will also give us an idea of what database diagrams can look like. In a Star Schema a single fact table forms the center of the database and that table contains information on a business process. In this case, that process is sales. This fact table is then surrounded by several dimension tables which provide further information on the sales process.

Here the surrounding tables provide information on products, dates and customers. 'Though there are plenty of other database structures that you may encounter, however, we won't look at them in this course. This concludes our look at database diagrams. As we mentioned earlier, it's possible to go into a lot more detail than this. However, you should now have enough knowledge to understand and illustrate the basic elements of a database diagram.

We've also reached the end of our introductory course on data and databases. In this course, we've learned about where data comes from, how to get it into a structured format like a database, and how databases work.

If you're looking to advance your analytical skills beyond the typical Excel user, then the principles you've learned in this course will give you a good start in becoming a more advanced data analyst.