The Options for Mapping EER Model Constructs to Relations

There are many ways of mapping the constructs from the EER model into the relational model. There are mapping of specializations or generalizations, shared subclasses, or multiple inheritance and categories.

Several options are available for mapping several subclasses that together form a specialization. There are two main options for map specializations. The first is to map the whole specialization or subclasses to one single table. The second is to map the specialization or subclasses to multiple tables. Each option is a variation that depends on the constraints on the specialization/generalization, as Elmasri (2015) described. There are a few options for mapping specialization or generalization, as Elmasri (2015) points out.

They are Multiple-relation options with superclass and subclasses. The other is by the subclass relations only. This one only works for a specialization whose subclasses are total or every entity the superclass must belong to at least one of the subclasses. Single-relation options are described as a single relation with one type of attribute or one with multiple attributes.

Multiple inheritance is a subclass with more than one superclass, also known as a shared subclass. Another way to describe a shared subclass or multiple inheritance is simply that it inherits characteristics from all of the superclasses associated with it, as explained by Tupper (2011).

Over the years as a database administrator, I have had many experiences with database designs across many different industries. When migrating from a logical model to a physical model, one must understand the business use cases to identify if all subclasses will become tables or if they will be rolled up into one table. Subclasses are broken out sometimes for ease of use by the end user I have experienced.

The other observation I will make about the logical model and database design is that companies I have worked for in recent years do not maintain a logical or physical model within a data modeling tool. In the early 1980s and 1990, data administration was a group alongside the database administration team that maintained the logical models. The database administrator would create the physical data model. Today, in my current role as a database administrator, the database administrator plays the role of data administrator and database administrator. This may not be the case in very large companies but it is something that I have experienced in more recent years.

References

Elmasri, R., Navathe, S. B. (2015-06-01). Fundamentals of Database Systems, 7th Edition

[VitalSource Bookshelf version]. Retrieved from (https://bookshelf.vitalsource.com/books/9781323139899)

Tupper, C. (2011). Data Architecture. : Elsevier Science. Retrieved from http://0-www.ebrary.com.nell.boulderlibrary.org