Subtype Mapping

2. Transform the STAFF supertype in the Global Fast Foods model, using the subtype or two-table implementation.

MANAGERS (MAR)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	budget
	*	tgt_rev

ORDER_TAKERS (OTR)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	ovt_rate

COOKS (COK)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary
	*	training

OTHERS (OER)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	dob
	*	salary

3. Identify the database rules for each part of the database implementations below.

Supertype implementations

• •         Table

o   Single table for super type and all subtype entities, no matter how many subtypes I have.

• •         Column

o   Supertype columns accommodated as is, no change in optionality.

o   Subtype columns accommodated as optional

o   One additional column appears in addition to the ones coming from super and sub entities. This column identifies which subtype this row belongs to. Possible values in this additional column may be taken from short names of subtypes. This column may be named as “maintableshortname_type”.

• •         Identifiers

o   Unique identifiers transform to primary and unique keys.

• •         Relationship

o   Relationships at supertype level mapped as usual, but the sub type level relationship is mapped as optional foreign key, even if it was meant to be mandatory in ERD for that subtype.

• •         Integrity constraint rules

o   A check constraint is needed to ensure that for each particular subtype, all columns that come from mandatory attributes are not null. Check constraint is needed to ensure business rules present in ERD.

Subtype implementations

• •         Table

o   One table per first level subtype.

• •         Column

o   All the attributes from supertype and current subtype are accommodated without losing optionality.

• •         Identifiers

o   Primary UID as primary key and secondary UIDs as unique keys come from supertype as is.

o   If sub type has a column which is unique for that subtype instances, this is also taken in as unique key, since every subtype has separate table.

• •         Relationship

o   Foreign key to represent relationship at super type level are accommodated as is without losing optionality. For the relations at sub type level, foreign key is implemented only in the table of that sub class with original optionality.

• •         Integrity constraint rules

o   Check constraint as in Supertype implementation is not needed here.

In case of Subtype implementation, if primary key need to have unique values across all the sub type tables, it requires additional programming.

Arc implementations

•         Table

o   Supertype and subtype entities generate one table for each entity.

•         Column

o   Each column gets all the attributes from entity of which it is mapped from, with original optionality.

•         Identifiers

o   Primary UID of the supertype level creates a primary key for  each of the tables.

o   All other unique identifiers mapped as unique keys in respective tables.

•         Relationship

o   Relationships mentioned at respective level are maintained as foreign keys without losing optionality.

•         Integrity constraint rules

o   “n” count additional columns are created in super type level table where “n” is no of subtypes. These are optional foreign key columns pointing to each subtype level table. These foreign key columns are optional because these 1:1 relationship b/w super type and subtypes is present in arc.

Additional check constraint is required to implement the arc.

  if we are implementing this, then the ‘primary key of subtype’ is already a foreign key to ‘primary key of super table’.

This part is done to implement exclusive or (arc), means no record in super class can skip to be of any subclass.

1.      My stand here, If, I am doing this, then why doing this should be bothered?

2.      Second thing, if I am doing this, then, I should have observed barred relationship in ERD in study material, I didn’ see that anywhere in study material. (Below mentioned)

See Below two diagrams: