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]