When we design an ER model, sometimes use "is-a," i.e. say you have a Employee entity with "ssn," "name," and "sal" attributes, and you use is-a to derivate two subclasses (entities), one is Hourly_Emp, the other is Contract_Emp. Each of both sub-entities has their own distinct attributes, say Hourly_Emp has its own "hourly_wages" and "hours_worked;" Contract_Emp has its own "contractid" attribute. And they both have common attributes of Employee entity. This is the definition of is-a.
However, my problem is: In the real database level (create table), how can I create this kind of inherited relationship?
1) Single table containing all subtypes, with a "discriminator" column to identify the specific subtype for each row. Check constraints can be used to ensure that each subtype uses only the applicable columns.
2) One table per subtype, duplicating the common supertype attributes.
3) A table representing the supertype, with the common attributes as columns and a discriminator as in (1), and a table per subtype with just the subtype-specific attributes. The primary key of each subtype table is also a foreign key referencing the supertype table.
Probably there is another way also! Which option you choose depends on the pros and cons for your particular case. For example, if the subtypes were very similar with just the odd additional attribute then maybe (1) would suffice.
Whichever way you go, views are a useful way of hiding the implementation details: in each case you could construct a view for each subtype that includes all its own and inherited attributes.