Skip to content Skip to main navigation Skip to footer

Basic Mapping The Transformation Process

Objectives

This tutorial covers the following objectives:

  • Distinguish between a conceptual model and a physical model
  • Apply terminology mapping between the two models
  • Understand and apply the Oracle naming conventions for tables and columns used in physical models
  • Transform an entity into a table diagram

Purpose

  • When you design a house, you eventually would like to see the house built.
  • Even if you don’t do the actual construction, you will need to understand the terms used by the builders in order to help them take your conceptual design and make it a physical reality.
  • The initial database design can be used for further discussion between designers, database administrators, and application developers.

You may not see a great difference between the conceptual model and the relational design at this point.

Review of Relational Tables

  • A table is a simple structure in which data is organized and stored.
  • In the example below, the EMPLOYEES table is used to store employees’ information.
  • Tables have columns and rows.
  • In the same below example we have, each row describes an occurrence of an employee.
  • Each column is used to store a specific type of value, such as employee number, last name, and first name.
  • The employee_id column is a primary key.
  • Every employee has a unique identification number in this table.
Review of Relational Tables
  • The value in the primary key column distinguishes each individual row.
  • The payroll_id is a unique key.
  • This means that the system does not allow two rows with the same payroll_id.
  • The foreign key column refers to a column in another table.
  • In the same example we have, the department_id refers to a column in the DEPARTMENTS table.
  • We know that Dana Smith works in department 10.
  • If we wanted to know more about Dana Smith’s department, we would look for the row in the DEPARTMENTS table that has department_id = 10.

Transforming Conceptual to Physical

  • The conceptual model (ER diagram) is transformed into a physical model.
  • The physical implementation will be a relational database.

Transform: To change the elements of an ERD (entities, attributes, relationships) into database elements (tables, attributes, foreign keys).

Transforming Conceptual To Physical

The EMPLOYEE entity in the ERD (conceptual model) transforms into the diagram of the EMPLOYEES table, which represents the definition of the table in the relational model (physical implementation).

The notations in the table diagram will be explained later in this tutorial.

Terminology Mapping

  • Changing from analysis (conceptual model) to implementation (physical model) also means changing terminology:
    • An entity becomes a table.
    • An instance becomes a row.
    • An attribute becomes a column.
    • A primary unique identifier becomes a primary key.
    • A secondary unique identifier becomes a unique key.
    • A relationship is transformed into a foreign-key column and a foreign key constraint.

Map: To associate the elements of an ERD (entities, attributes, relationships) with database elements (tables, attributes, foreign keys).

Terminology Mapping

Analysis and design are phases of the system development life cycle (to be discussed more later). When designing a system, analysis precedes design. Data modeling is done in the analysis phase.

When you are satisfied that you have captured the business requirements in the data model, you move on to the design phase, where the ERD is mapped to a physical implementation.

Table Diagram Notations

In these simple examples there is a one-to-one mapping between conceptual and physical terminology (for example one entity becomes one table) but that this will not always be true in more complex models.

  • The first row of the table diagram contains the table name and the short name.
  • The Key Type column should contain values of “pk” for the primary key, “uk” for the unique key, and “fk” for the foreign-key column.
Table Diagram Notations
  • It will be blank if the column is not a part of any key.
  • The Optionality column must contain “*” if the column is mandatory and “o” if it is optional. This is similar to the entity diagram. The third column is for the column name.

Naming Conventions for Tables and Columns

  • The table name is the plural of the entity name.
  • Example: STUDENT becomes STUDENTS
  • Column names are identical to the attribute names except that special characters and spaces are replaced with underscores.
Naming Conventions for Tables and Columns
  • Column names often use more abbreviations than attribute names. Example: first name becomes first_name, or fname

Table Short Names

Short names are NOT mandatory, simply useful. The suggested “rules” are one of several possible conventions for determining short names.

  • A unique short name for every table is useful in the naming of foreign-key columns.
  • One possible way to make these short names is based on the following rules:
  • For entity names of more than one word, take the:
    • First character of the first word
    • First character of the second word
    • Last character of the last word
  • Example: JOB ASSIGNMENT gets a short name of JAT
Table Short Names
  • For entity names of one word but more than one syllable, take the:
    • First character of the first syllable
    • Lirst character of the second syllable
    • Last character of the last syllable
  • Example: EMPLOYEE gets a short name of EPE and CLIENT gets a short name of CET
Table Short Names
  • For entity names of one syllable but more than one character:
    • First character
    • Second character
    • Last character
  • Example: FLIGHT gets a short name of FLT
Table Short Names

Naming Restrictions with Oracle

Table and column names:

  • Must start with a letter
  • Can contain up to 30 alphanumeric characters
  • Cannot contain spaces or special characters such as “!,” but “$,” “#,” and “_” are permitted.
  • Table names must be unique within one user account in the Oracle database.
  • Column names must be unique within a table.
  • Some words have a special meaning in the Oracle database and in the SQL programming language.
  • These are called “reserved” words.
  • It is best to avoid using these as names for your tables and columns.
  • Some common examples of Oracle reserved words are:
    • TABLE
    • NUMBER
    • SEQUENCE
    • ORDER
    • VALUES
    • LEVEL
    • TYPE
  • A complete list can be found on otn.oracle.com. (this site requires you to sign up, but it is free. It’s a valuable source of technical information on all Oracle products).

Note that Oracle table and column names can contain underscores but not hyphens. For example, SALES_ORDERS is a valid table name but SALES-ORDERS is not.

All database systems make recommendations on naming objects (such as tables). If you do not use an Oracle database, you should still decide on a naming convention and make sure it is compatible with the database system that you have chosen.

Terminology

Key terms used in this tutorial included:

  • Map
  • Reserved word
  • Transform

Summary

In this tutorial, you should have learned how to:

  • Distinguish between a conceptual model and a physical model
  • Apply terminology mapping between the two models
  • Understand and apply the Oracle naming conventions for tables and columns used in physical models
  • Transform an entity into a table diagram

Was This Article Helpful?

0
Related Articles
0 Comments

There are no comments yet

Leave a comment

Your email address will not be published. Required fields are marked *