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’.