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

Basic Mapping: The Transformation Process

Cheatsheet

Common for table and column:

• ·         start with letter,
• ·         up to 30 alphanumeric
• ·         no space or special char except $ # and _
• ·         table name unique in one user acc
• ·         column name unique in table
• ·         don't use reserved words e.g. TABLE, NUMBER, SEQUENCE, ORDER, VALUES, LEVEL, TYPE : check more at docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm

For table:

• ·         to plural from entity name
• ·         for short name of more than one word: First char of first word + First char of sec word + last char of last word
• ·         for short name of one word but more than one syllable:  First char. of first slb. +  First char. of sec slb. +  last char. of last slb. [how to recognize syllable - search it in thefreedictionary.com]
• ·         short name for one word containing one syllable: first char + second char + last char
• ·         In case of duplicate short name append with integer like SDT1

For column name (from attribute name):

• ·         special character and space : replace with underscore e.g. first name to first_name
• ·         often abbreviated e.g. first name to fname

Now lets start

• ·         To change the elements of an ERD into database elements-Transform
• ·         To associate the elements of an ERD with database elements-Map

1. Transform the following entities into table definitions using suitable naming conventions.

Table Name (short name)
Key Type (pk, fk, uk)	Optionality ( "*", "○")	Column Name

STUDENTS (SDT)
Key Type	Optionality	Column Name
pk	*	id
	*	fname
	*	lname
	*	haddress
	○	pnumber

TEACHERS (TER)
Key Type	Optionality		Column Name
pk	*		id
	*		first_name
	*		last_name
	*		ssn
	*		ph_number
	○		email_address
BANK_ACCOUNTS (BAT)
Key Type		Optionality		Column Name
pk		*		bnk_id
pk		*		acc_id
		*		cus_name
		*		cus_bal
		○		cus_h_addr

2. Match the ERD elements to their corresponding database elements.

Analysis	Design
Attribute	Column
Entity	Table
ER Model	Physical design
Instance	Row
Primary UID	Primary key
Relationship	Foreign key
Secondary UID	Unique key

3. Identify the table diagram notations listed below.

         “pk” –primary key
         “fk” –foreign key
         “uk”-unique key
         “*”-mandatory
        “o”-optional

4. Create short names for the terms below based on the naming conventions rules.

•          Academic major - AMR
•          Undergraduate (un·der·grad·u·ate) - UDE
•          Class - CLS

5. Identify what is wrong with each of the table names below.

•          1997classes – start with numeric, letters are  in lower [table name is of great importance]
•          Schedule_of_classes_spring_2003 – 31 alphanumeric, letters are in lower [table name is of great importance]
•          Financial Aid deadlines! – has spaces and “!”, letters are in lower [table name is of great importance]

Introduction to Relational Database Concepts

• ·         A constraint which ensures that the column contains no null values and uniquely identifies each row of the table. –Primary Key
• ·         A column or set of columns that refer to a primary key in the same table or another table. –Foreign key
• ·         An entry in a table, consisting of values for each appropriate column -row
• ·         An implementation of an attribute or relationship in a table -column
• ·         An integrity constraint that requires every value in a column or set of columns be unique. - Unique key
• ·         Collections of objects or relations, set of operators to act on those relations and data integrity for accuracy and consistency. –relational database
• ·         More than one column or combination of columns that could serve as the table’s primary key –candidate key

1. Identify the integrity rules below.

• ·         A column must contain only values consistent with the defined data format of the column –column integrity
• ·         Define the relationally correct state for a database; they ensure that users can perform only those operations that leave the database in a correct, consistent state –data integrity
• ·         A primary key must be unique and no part of the primary key can be null –entity integrity
• ·         A foreign key must match an existing primary key value (or else be null) –referential integrity
• ·         The data stored in a database must comply with the rules of the database –user defined integrity

2. Data Integrity in the DJs Database

Examine the sample data for the tables in the DJs on Demand database (next page). Check for entity, referential, and column integrity. Identify any data-integrity violations.

Assume that all date columns should have a date format and all amount (cost) columns should have a number format.

Below mentioned all are data integrity (entity, referential, column, user-defined) violations.

1.      Problem designer removed 77 from id column of client table. This created Entity Integrity issue in clients table and referential integrity issue in events table.

2.      Same in theme _code of events table for row with id 15 - and referential integrity issue

3.      Problem designer instructs all amount columns to be number format and gives cost in row with id 17 as 2gs (events table) to demonstrate column integrity violation.

4.      Same in date of row with id 25 in events table - column integrity violation

5.      To identify user-defined integrity we some need business rules here which must be followed – insufficient information provided to identify this kind of violation.