Mastery 08 – Classes to Tables

You have your UML Class Diagram and want to make tables for a database out of it? No problem, here will be a set of steps to follow in order to achieve exactly that:

Step 1

Identify your strong entities, they have primary keys. For each one of them, create a table that includes all the simple attributes of that entity.

Step 2

Identify your weak entities and create a table for each one of them with their simple attributes. The primary key of a weak entity is partially or fully derived from each owner entity so wait until you have your owner identities mapped out before giving primary keys to weak identities.

Step 3

Identify your one-to-many relationships (1:*), and which side is the parent and which side is the child entity. The parent entity is the one with the (1) and the child entity is the one with the (*). To create tables of these relationships, you have to post a copy of the primary key attribute(s) of the parent entity into the child entities’ table and it will be a foreign key.

Step 4

If you have one-to-one binary relationship types, you should first know what type it is from the following:

  • Mandatory participation on both sides
  • Mandatory participation on one side
  • Optional participation on one side

In a mandatory participation on both sides of a 1:1 relationship you should combine the entities involved into one table and choose one of the primary keys of the original entities to be the primary key of the new table, while the other is used as an alternate key.

In a mandatory participation on one side of a 1:1 relationship you are able to identify the parent and child entities for the 1:1 relationship using the participation constraints. The entity that has optional participation in the relationship is designated as the parent entity, and the entity that has mandatory participation in the relationship is designated as the child entity. A copy of the primary key of the parent entity is placed in the table representing the child entity.

In an optional participation on both sides of a 1:1 relationship the designation of the parent and child entities is arbitrary unless you can find out more about the relationship that can help you make a decision one way or the other.

Step 5

For each *:* binary relationship or many-to-many relationship, create a table and include any attributes that are part of the relationship. You should then post a copy of the primary key attribute(s) of the entities that participate in the relationship into the new table, to act as foreign keys. One or both of these foreign keys will also form the primary key of the new relation, possibly in combination with one or more of the attributes of the relationship.

Step 6

For each multi-valued attribute in an entity, create a new table to represent the multi-valued attribute, and include the primary key of the entity in the new relation to act as a foreign key. Unless the multi-valued attribute, is itself an alternate key of the entity, the primary key of the new relation is the combination of the multivalued attribute and the primary key of the entity.

After you have completed these steps, you should be ready to go with your own set of tables based on the UML Class Diagram that you have.

 

Reference(s)

Connolly, T and Begg, C. (2015). Database Systems A Practical Approach to Design, Implementation, and Management. United States: Pearson Education.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s