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:

Hierarchies and Recursive Relationships

• ·         A relationship between an entity and itself. –Recursive Relationship
• ·         A series of relationships that reflect entities organized into successive levels. –Hierarchal relationship

1. Develop two ER diagrams to represent the following situation. Develop one using a hierarchical structure and one using a recursive structure.

“Our company sells products throughout the United States. So we’ve divided the U.S. into four major sales regions: the Northern, Eastern, Southern, and Western regions. Each sales region has a unique region code. Each sales region is then divided into sales districts. For example, the Western region is divided into the Rocky Mountain, Northwest, Pacific Coast, and Pacific districts. Each district has a unique district code. Each district is made up of sales territories. The Rocky Mountain district is composed of three territories: Wyoming-Montana, Colorado, and Utah-New Mexico. The North-west district is made up of two territories: the Washington and Oregon-Idaho territories. The Pacific Coast district is composed of two territories: the California and Nevada territories. The Pacific District includes the Hawaii territory and the Alaska territory. Each territory has a unique territory code.

Then each sales territory is broken down into sales areas. For example, Colorado is made up of two sales areas: the Front Range and the Western Slope sales areas. Each sales area has a unique sales-area code.

Each salesperson is responsible for one or more sales areas and has a specific sales quota. We also have sales managers who are responsible for one or more sales districts and sales directors who are responsible for one or more sales regions. Each sales manager is responsible for the territories with his/her districts. We don’t overlap our employees’ responsibilities. Each sales area is always the responsibility of a single salesperson, and our managers' and directors' responsibilities don’t overlap. Some-times our salespersons, managers, and directors will have special assignments and will not be responsible for sales. We identify all our sales personnel by their employee IDs.”

Hierarchical: Assume that every region must have at least one district and so on up to areas.

Recursive:

• ·         To cover top and bottom entities in chain make optional on both end of pigs ear.
• ·         Type attribute may take values e.g. area, territory, district region etc.
• ·         To cover entities of type territory, relation b/w unit and employee on unit side becomes optional. (Additional information to be recorded with this : Each sales manager is responsible for the territories with his/her districts. But this does not mean I will store duplicate information with territories) 
• 
  

Relationship Mapping

• ·         A relationship in a database where the foreign key column in the database table cannot be updated –non-transferable relationship
• ·         A series of relationships implying that the unique identifier of each entity in the chain is carried down to the entity on the next level – cascade barred relationship
• ·         The product of the resolution of a many to many relationship. – Intersection entity

The following entities were mapped to tables in a previous lesson: SHIFT, REGULAR

MENU, PROMOTIONAL MENU, FREQUENT DINER CARD.

1. Transform relationships into foreign-key columns.

Use as many rows as necessary in a table similar to the one in the diagram.

Key Type (pk, uk, fk)	Optionality (“*” or “o”)	Column Name

Refer to the completed Global Fast Foods model and map the following entities:

FOOD ITEM:

FOOD_ITEMS (FIM)
Key Type	Optionality	Column Name
pk	*	number
	*	description
	*	price
fk	○	reg_code
fk	○	promo_code

ORDER:

ORDERS (ODR)
Key Type	Optionality	Column Name
pk	*	number
	*	date
	*	time
fk	*	staff_id
fk	○	din_card_id

ORDER LINE:

ORDER_LINES (OLE)
Key Type	Optionality	Column Name
pk, fk	*	ord_num
pk, fk	*	fd_num
	*	qty

2. Indicate if a check constraint or additional programming is needed to enforce the relationship in the database. Under each table diagram, write the conditions that the check constraint or program has to ensure.

For example:

“To enforce exclusive relationships, a check constraint is needed to make sure that (column A is not null and column B is null) OR (column A is null and column B is not null).”

“To enforce a nontransferable relationship, additional programming is needed to make sure that the <foreign key column> cannot be updated.”

• ·         PROMOTIONAL_MENUS: A check constraint is needed here to ensure ( (end_date is not null and end_date is greater than start_date) or (end_date is null)) [ taking back strikethrough, seems to be out of scope for problem mentioned]
• ·         REGULAR_MENUS: When a food item is ordered, programming is required to not accept order based on hrs_served [ this seems to be out of scope so taking this observation back]
• ·         FOOD_ITEMS:A check constraint for exclusive or relationship

 (reg_code is not null and promo_code is null) or (reg_code is null and promo_code is not  null)

• ·         ORDERS: additional programming to ensure staff_id cannot be changed after the row is created once. This is ‘non-transferability’. [ Also, once served order row cannot be deleted, out of scope of this practice, this seems to be out of scope, so taking strikethrough part back.]
• ·         SHIFT_ASSIGNMENTS: additional programming to ensure, only one day before the date in this table occurs, the assignment can be modified. Once the date started, no change allowed to satisfy ‘conditional non-transferability’.