Model a Database With UML

Remember the three steps of database modeling:

1. Conceptual model (CDM).

2. Logical model (LDM).

3. Physical model (PDM).

Step 1 is about modeling the data in a human way, whereas steps 2 and 3 move closer to physically implementing the database on a computer.

In this part, we will look at the second step, the logical model. For relational databases, the logical level uses relational modeling. Your relational model will flow out of your UML diagram, and once defined, it will be very easy to create your final database.

Illustrate Your Table and Attributes

In a relational database, data is stored in tabular format, so it’s called a table.

The relational model moves you closer to implementing the database and allows you to model these tables. A relation is the element of the relational model that corresponds to a table.

Throughout the third part, you will be translating your UML diagram into a relational model.

The first step in this translation process is also the simplest. You’ll create a table for each class in your UML diagram. We will leave inheritance aside for the moment (  tv_series  ,  tv_film  , etc.) and deal with this later.

So, here are the current tables:

• production

• location

• shooting

• production_company

• director

Here's the good news. The relational model also has a concept of “attribute,” which means the same thing as it does in UML. Remember that attribute means the same as column. 

So, your five tables have the same attributes as their corresponding classes in UML:

•  location  ,  zipCode for the location table

•  title  for the production table

• etc.

Understand the Diagrams

There are different ways of illustrating a relational model. One describes the model using text, and the other uses a diagram.

Text Description

You could describe your location table with its four attributes as follows:

location (location:String, zipCode:Number)

Diagram

The diagram is very similar to UML because you represent tables using boxes divided into three sections. However, you complete these three sections differently (compared to UML). So, be careful not to confuse them!

By default, attributes are in the third section (rather than the second section in UML). You’ll get more details shortly.

A third type of diagram, which we will call a tabular diagram, is not official. However, we will use it to illustrate some of the examples in this course.

The tabular diagram looks like what you see in your spreadsheet:

• The attributes are the column headings at the top.

• A few (non-exhaustive) examples are then entered into the rows of the table. 

In this diagram, the location table contains two columns and several rows. One of these rows holds data about 1 Front St. and contains the following values:    ("1 Front St," 94111)  .

Let’s Recap!

• The LDM is the second step when modeling a relational database and can be represented using a relational model.

• The main purpose of a relational model is to design the table, which contains rows and columns.

Vocabulary check:

Now that you’ve laid the foundations for your model with your five tables, it’s time to go to the next level of detail and determine the primary keys for your tables. Let’s look at this in the next chapter.