Friday, September 23, 2016

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]



No comments:

Post a Comment