The first step in developing a database design is to identify the types of data to be stored in database tables. A database includes information about the entities in an organization or business, and their relationships to each other. In a relational database, entities are represented as tables.
An entity is a person, object, or concept about which you want to store information. Some of the entities described in the sample tables are employees, departments, and projects. (For a description of the sample database, refer to the SQL Reference.)
In the sample employee table, the entity "employee" has attributes, or properties, such as employee number, name, work department, and salary amount. Those properties appear as the columns EMPNO, FIRSTNME, LASTNAME, WORKDEPT, and SALARY.
An occurrence of the entity "employee" consists of the values in
all of the columns for one employee. Each employee has a unique
employee number (EMPNO) that can be used to identify an occurrence of the
entity "employee". Each row in a table represents an occurrence of an
entity or relationship. For example, in the following table the values
in the first row describe an employee named Haas.
Table 4. Occurrences of Employee Entities and their Attributes
| EMPNO | FIRSTNME | LASTNAME | WORKDEPT | JOB |
|---|---|---|---|---|
| 000010 | Christine | Haas | A00 | President |
| 000020 | Michael | Thompson | B01 | Manager |
| 000120 | Sean | O'Connell | A00 | Clerk |
| 000130 | Dolores | Quintana | C01 | Analyst |
| 000030 | Sally | Kwan | C01 | Manager |
| 000140 | Heather | Nicholls | C01 | Analyst |
| 000170 | Masatoshi | Yoshimura | D11 | Designer |
There is a growing need to support non-traditional database applications such as multimedia. You may want to consider attributes to support multimedia objects such as documents, video or mixed media, image, and voice.
Within a table, each column of a row is related in some way to all the other columns of that row. Some of the relationships expressed in the sample tables are:
"Employee" and "department" are entities; Sally Kwan is part of an occurrence of "employee," and C01 is part of an occurrence of "department". The same relationship applies to the same columns in every row of a table. For example, one row of a table expresses the relationship that Sally Kwan manages Department C01; another, the relationship that Sean O'Connell is a clerk in Department A00.
The information contained within a table depends on the relationships to be expressed, the amount of flexibility needed, and the data retrieval speed desired.
In addition to identifying the entity relationships within your enterprise, you also need to identify other types of information, such as the business rules that apply to that data.