Mapping Entities and Attributes

From InterBase

Go Up to IBConsole - Designing a Database


Based on the requirements that you collect, identify the objects that need to be in the database – the entities and attributes. An entity is a type of person, object, or thing that needs to be described in the database. It may be an object with a physical existence, like a person, a car, or an employee, or it may be an object with a conceptual existence, like a company, a job, or a project. Each entity has properties, called attributes, that describe it. For example, suppose you are designing a database that must contain information about each employee in the company, departmental-level information, information about current projects, and information about customers and sales.

Defining Entities and Attributes

The table below shows entities and attributes that organize the required data.

Entities Attribute

EMPLOYEE

  • Last Name
  • First Name
  • Department
  • Job Code
  • Phone Extension
  • Salary

DEPARTMENT

  • Department Number
  • Department Name
  • Head Name
  • Head Employee Number
  • Phone Number

PROJECT

  • Project ID
  • Project Name
  • Project Description
  • Project Leader

CUSTOMER

  • Customer Number
  • Customer Last Name
  • Customer First Name
  • Customer Phone Number

SALES

  • Order Number
  • Customer Number
  • Sales Rep
  • Order Date
  • Ship Date
  • Order Status

By listing the entities and associated attributes this way, you can begin to eliminate redundant entries. Do the entities in your list work as tables? Should some columns be moved from one group to another? Does the same attribute appear in several entities? Each attribute should appear only once, and you need to determine which entity is the primary owner of the attribute. For example, DEPARTMENT HEAD NAME should be eliminated because employee names (FIRST NAME and LAST NAME) already exist in the EMPLOYEE entity. DEPARTMENT HEAD EMPLOYEE NUM can then be used to access all of the employee-specific information by referencing EMPLOYEE NUMBER in the EMPLOYEE entity.

Designing tables

In a relational database, the database object that represents a single entity is a table, which is a two-dimensional matrix of rows and columns. Each column in a table represents an attribute. Each row in the table represents a specific instance of the entity. After you identify the entities and attributes, create the data model, which serves as a logical design framework for creating your InterBase database. The data model maps entities and attributes to InterBase tables and columns, and is a detailed description of the database – the tables, the columns, the properties of the columns, and the relationships between tables and columns.


The example below shows how the EMPLOYEE entity from the entities/attributes list has been converted to a table.

EMP_NO
  LAST_NAME
  FIRST_NAME
  DEPT
  JOB_CODE
  PHONE_EXT
  SALARY
  

Each row in the EMPLOYEE table represents a single employee. EMP_NO, LAST_NAME, FIRST_NAME, DEPT_NO, JOB_CODE, PHONE_EXT, and SALARY are the columns that represent employee attributes. When the table is populated with data, rows are added to the table, and a value is stored at the intersection of each row and column, called a field. In the EMPLOYEE table, "Smith" is a data value that resides in a single field of an employee record.