Represent a Superclass/Subclass Relationship

For each superclass/ subclass relationship in ER-diagrams, we identify the superclass entity as parent entity and the subclass entity as the child entity.

There are various options on how to represent those relationship as one or more relations. The selection of the most appropriate option is dependent on a number of factors such as the disjointness and participation constraints on that relationship.

For example: Staff can have one or more position on the same time in the company. The available positions are Staff, Manager, Salesman, Interviewer, and Head of Branch. It’s also possible in the future, company adding new position.

There are many solution for establish that relationship:

1. Create only single relation with one or more discriminators to distinguish the type of each tuple.
2. Create two relations where one relation for superclass and one relation for all subclasses with one or more discriminators to distinguish the type of each tuple.
3. Create many relations where one relation for each combined superclass/ subclass.
4. Create many relations where one relations for superclass and one for each subclass.

Here, I’m want to offering another idea for establish that relationship, “Bitwise Operation Attribute”. In computer programming, a bitwise operation operates on one or two bit patterns or binary numerals at the level of their individual bits.

In this case, we use AND bitwise operation.

Table Position

Table Employee

How to getting the position for employee S102 ?

• Do bitwise operations in T-SQL for getting the list of position;
• Do natural join.

T-SQL:
SELECT * FROM Employee e, Position p
WHERE (e.position & p.posValue) > 0 AND e.empID = ‘S102’
ORDER BY p.posValue;