From classes to tables

As we saw in UML, Why do we use it?, there´s something called classes where these are defined as different entities in a project. But what happens if you want to make these imaginary classes into something more real, a database table per say. In this post we´ll find out.

Decomposing the concepts.

A Entity Relationship Diagram most known as ER,  is a graphical representation that depicts relationships among people, objects, places, concepts or events within a software engineer system. We can say that that the product of a UML design is an ER. Recalling we can say that ER includes entity sets and relationship sets.

A relationship is a table with columns and rows.

Attribute is a property that describes an object, it is represented as a column named of the relation.

Domain is the set of values permissible for one or more attributes.

A tuple is a row of the relation.

Grade is the number of attributes in a relation.

Cardinality is the number of tuples in a relation.

The convertion.

Converting each entity and relationship set to a table will involve deciding attributes and a candidate key for the table itself.

Entity set to a table.

Given an entity set, we are going to create a table with the same attributes and a candidate key.

For example:

Relationship set to a table.

Binary relationship sets.

Let´s consider a binary relationship set. In the following example for converting R:

we create a table whose attributes include:

• the candidate keys of both entities A and B.
• the attribute of R (if it exists).

The candidate key of the table, however depends on the cardinality constraints of R.

Many-to-many relationship sets.

The candidate key of R includes all the attributes in the candidate keys of A and B.

One-to-many relationship sets.

The candidate key of R is the same as B.

One-to-one relationship sets.

R has two candidate keys. The first (second) one is the same as A (B).

Summary.

For converting an ER diagram to a table, the entity type becomes a table, all single-valued attribute becomes a column for the table, a key attribute of the entity type is represented by primary key, the multivalued attribute is represented by a separate table, composite attribute is represented bu components and derived attributes are not considered in the table.