Entities Oracle Database Tips by Donald Burleson

Entities

Entities  are objects or classes of objects that can be identified.  They can be real things (an employee) or logical things (a department).  An employee is a class of entities.  Bill, Tom and Sue are employee entities.  The entity class becomes the tables while the entities are the rows.  An entity class has attributes or elements that make up the entity. 

The attributes become the columns in the tables.  An employee has a name, ssn, address, hire date, etc.  It also needs to be uniquely identified so we can tell the difference between Bill and Sue, so we will give each employee a unique employee number.  Figure 1.8 is the employee entity.

In the above example, the employee entity has the attributes defined as emp_num (employee number), ssn, emp_name, hire_date and dept.  The primary key  (unique identifier) is the employee number, identified as the key because it is underlined.  There are some key things to note about the employee entity. 

First, all employees are going to be in a department, so the dept attribute will have a lot of redundant information in it.  Also, the department is not dependent on the emp_num key, so we need to split it out into its own entity.  Secondly, you might want to use the social security number (ssn) as the primary key ; however, this will limit your design to just persons with a ssn.  No persons from other countries, since they will not have a ssn.  Finally, employee name should be broken up into subelements such as first, middle initial and last name.   After incorporating the changes, we now have the diagram in Figure 1.9.

Notice that department is now its own entity with the dept_num as the primary key.  Also notice that we place a dep_num attribute in the employee entity, this is know as a Foreign Key.  It is an attribute that references another entity's primary key (more on that in Chapter 5).  This actually defines a relationship between the employee and the department entity.   

But, I am ahead of myself.  Entities can be a type of another entity.  An employee is also a person.  If I have a person entity, the employee would be a subtype of the person entity.  For now, we will stick with the employee type.

Get the Complete Oracle SQL Tuning Information  The landmark book "Advanced Oracle SQL Tuning  The Definitive Reference"  is filled with valuable information on Oracle SQL Tuning. This book includes scripts and tools to hypercharge Oracle 11g performance and you can buy it for 30% off directly from the publisher.

---

Oracle SQL support:

For Oracle SQL development support just call to get an Oracle Certified professional for all Oracle SQL development projects.