The relational model is the basis for any relational database management system (RDBMS).
[1]A relational model has three core components: a collection of objects or relations, operators
that act on the objects or relations, and data integrity methods. In other words, it has a place to
store the data, a way to create and retrieve the data, and a way to make sure that the data is
logically consistent.
A relational database uses relations, or two-dimensional tables, to store the information
needed to support a business. Let's go over the basic components of a traditional relational
database system and look at how a relational database is designed. Once you have a solid
understanding of what rows, columns, tables, and relationships are, you'll be well on your way to
leveraging the power of a relational database.

A table in a relational database, alternatively known as a relation, is a two-dimensional
structure used to hold related information. A database consists of one or more related tables.
NoteDon't confuse a relation with relationships. A relation is essentially a table, and a
relationship is a way to correlate, join, or associate two tables.
A row in a table is a collection or instance of one thing, such as one employee or one line
item on an invoice. [2]A column contains all the information of a single type, and the piece of data
at the intersection of a row and a column, a field, is the smallest piece of information that can be
retrieved with the database's query language. For example, a table with information about
employees might have a column called LAST_NAME that contains all of the employees' last
names. Data is retrieved from a table by filtering on both the row and the column.

The examples throughout this article will focus on the hypothetical work of Scott Smith,
database developer and entrepreneur. He just started a new widget company and wants to
implement a few of the basic business functions using the relational database to manage his

Human Resources (HR) department.
Relation

A two-dimensional structure used to hold related information, also known as a
table.
Note

Most of Scott's employees were hired away from one of his previous employers, some
of whom have over 20 years of experience in the field. As a hiring incentive, Scott has agreed to
keep the new employees' original hire date in the new database.
Row

A group of one or more data elements in a database table that describes a person,
place, or thing.
Column

The component of a database table that contains all of the data of the same name
and type across all rows.
You'll learn about database design in the following sections, but let's assume for the moment
that the majority of the database design is completed and some tables need to be implemented.
Scott creates the EMP table to hold the basic employee information, and it looks something like
this:

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
7369 SMITH CLERK 7902 17-DEC-80 800 20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975 20
7839 KING PRESIDENT 17-NOV-81 5000 10
7902 FORD ANALYST 7566 03-DEC-81 3000 20

Notice that some fields in the Commission (COMM) and Manager (MGR) columns do not
contain a value; they are blank. A relational database can enforce the rule that fields in a column
may or may not be empty. [3]In this case, it makes sense for an employee who is not in the Sales
department to have a blank Commission field. It also makes sense for the president of the
company to have a blank Manager field, since that employee doesn't report to anyone.

Field

The smallest piece of information that can be retrieved by the database query
language. A field is found at the intersection of a row and a column in a database table.
On the other hand, none of the fields in the Employee Number (EMPNO) column are blank.
The company always wants to assign an employee number to an employee, and that number must
be different for each employee. One of the features of a relational database is that it can ensure
that a value is entered into this column and that it is unique. The EMPNO column, in this case, is
the primary key of the table.
Primary Key

A column (or columns) in a table that makes the row in the table
distinguishable from every other row in the same table.
Notice the different datatypes that are stored in the EMP table: numeric values, character or

alphabetic values, and date values.
As you might suspect, the DEPTNO column contains the department number for the
employee. But how do you know what department name is associated with what number?
Scott created the DEPT table to hold the descriptions for the department codes in the EMP
table.
The DEPTNO column in the EMP table contains the same values as the DEPTNO column
in the DEPT table. In this case, the DEPTNO column in the EMP table is considered a foreign
key to the same column in the DEPT table.
A foreign key enforces the concept of referential integrity in a relational database. [4]The
concept of referential integrity not only prevents an invalid department number from being
inserted into the EMP table, but it also prevents a row in the DEPT table from being deleted if
there are employees still assigned to that department.
Foreign Key

A column (or columns) in a table that draws its values from a primary or
unique key column in another table. A foreign key assists in ensuring the data integrity of a table.
Referential Integrity

A method employed by a relational database system that enforces
one-to-many relationships between tables.

Before Scott created the actual tables in the database, he went through a design process known
as data modeling. In this process, the developer conceptualizes and documents all the tables for the
database. One of the common methods for modeling a database is called ERA, which stands for
entities, relationships, and attributes. The database designer uses an application that can maintain
entities, their attributes, and their relationships. In general, an entity corresponds to a table in the
database, and the attributes of the entity correspond to columns of the table.
Data ModelingA process of defining the entities, attributes, and relationships between the
entities in preparation for creating the physical database.
The data-modeling process involves defining the entities, defining the relationships between
those entities, and then defining the attributes for each of the entities. Once a cycle is complete, it
is repeated as many times as necessary to ensure that the designer is capturing what is important
enough to go into the database. Let's take a closer look at each step in the data-modeling process.

First, the designer identifies all of the entities within the scope of the database application.
The entities are the persons, places, or things that are important to the organization and need to be
tracked in the database. Entities will most likely translate neatly to database tables. For example,
for the first version of Scott's widget company database, he identifies four entities: employees,
departments, salary grades, and bonuses. These will become the EMP, DEPT, SALGRADE, and
BONUS tables.

Once the entities are defined, the designer can proceed with defining how each of the
entities is related. Often, the designer will pair each entity with every other entity and ask, "Is
there a relationship between these two entities?" Some relationships are obvious; some are not.
In the widget company database, there is most likely a relationship between EMP and DEPT,
but depending on the business rules, it is unlikely that the DEPT and SALGRADE entities are
related. If the business rules were to restrict certain salary grades to certain departments, there
would most likely be a new entity that defines the relationship between salary grades and
departments. This entity would be known as an associative or intersection table and would
contain the valid combinations of salary grades and departments.
Associative Table

A database table that stores the valid combinations of rows from two
other tables and usually enforces a business rule. An associative table resolves a many-to-many
relationship.
In general, there are three types of relationships in a relational database:
One-to-many

The most common type of relationship is one-to-many. This means that
for each occurrence in a given entity, the parent entity, there may be one or more
occurrences in a second entity, the child entity, to which it is related. For example, in the
widget company database, the DEPT entity is a parent entity, and for each department,
there could be one or more employees associated with that department. The relationship
between DEPT and EMP is one-to-many.
One-to-one

In a one-to-one relationship, a row in a table is related to only one or none
of the rows in a second table. This relationship type is often used for subtyping. For
example, an EMPLOYEE table may hold the information common to all employees,
while the FULLTIME, PARTTIME, and CONTRACTOR tables hold information unique
to full-time employees, part-time employees, and contractors, respectively. These entities
would be considered subtypes of an EMPLOYEE and maintain a one-to-one relationship
with the EMPLOYEE table. These relationships are not as common as one-to-many
relationships, because if one entity has an occurrence for a corresponding row in another
entity, in most cases, the attributes from both entities should be in a single entity.
Many-to-many

In a many-to-many relationship, one row of a table may be related to
many rows of another table, and vice versa. Usually, when this relationship is

implemented in the database, a third entity is defined as an intersection table to contain
the associations between the two entities in the relationship. For example, in a database
used for school class enrollment, the STUDENT table has a many-to-many relationship
with the CLASS table—one student may take one or more classes, and a given class may
have one or more students. The intersection table STUDENT_CLASS would contain the
combinations of STUDENT and CLASS to track which students are in which classes.

Once the designer has defined the entity relationships, the next step is to assign the attributes
to each entity. This is physically implemented using columns, as shown here for the SALGRADE
table as derived from the salary grade entity.

After the entities, relationships, and attributes have been defined,
the designer may iterate the data modeling many more times. When
reviewing relationships, new entities may be discovered. For example,
when discussing the widget inventory table and its relationship to a
customer order, the need for a shipping restrictions table may arise.
Once the design process is complete, the physical database
tables may be created. [5]Logical database design sessions should not
involve physical implementation issues, but once the design has gone through an iteration or two,
it's the DBA's job to bring the designers "down to earth." As a result, the design may need to be
revisited to balance the ideal database implementation versus the realities of budgets and schedules.

1, leverage  ['li:vəridʒ, 'le-]
n. 手段，影响力；杠杆作用；杠杆效率

2, correlate  ['kɔ:rə,leit]
vi. 关联
vt. 使有相互关系；互相有关系
n. 相关物；相关联的人
adj. 关联的
3, essentially  [i'senʃəli]
adv. 本质上；本来
4, hypothetical  [,haipəu'θetikəl]
adj. 假设的；爱猜想的
5, entrepreneur  [,ɔntrəprə'nə:]
n. 企业家
6, incentive  [in'sentiv]
a. 刺激的,鼓励的
n. 刺激,鼓励,动机
7, integrity  [in'teɡrəti]
n. 完整；正直；诚实；廉正
8, associative 
a. 联合的(相关的,协会的)