Database Systems and Business Intelligence - Principles of Information Systems - page 190

Data Table 1: Project Table

Figure 5.5

Project Description Dept. number

A Relational Database Model
In the relational model, all data
elements are placed in two-
dimensional tables, or relations. As
long as they share at least one
common element, these relations
can be linked to output useful
information. Note that some
organizations might use employee
number instead of Social Security
number (SSN) in Data Tables 2
and 3.

155 Payroll 257

498 Widgets 632

226 Sales manual 598

Data Table 2: Department Table

Dept. Dept. name Manager SSN

257 Accounting 005-10-6321

632 549-77-1001

Manufacturing

598 Marketing 098-40-1370

Data Table 3: Manager Table

SSN Last name First name Hire date Dept. number

005-10-6321 Johns Francine 10-07-1997 257

549-77-1001 Buckley Bill 02-17-1979 632

098-40-1370 Fiske Steven 01-05-1985 598

Projecting involves eliminating columns in a table. For example, a department table
might contain the department number, department name, and Social Security number (SSN)
of the manager in charge of the project. A sales manager might want to create a new table
with only the department number and the Social Security number of the manager in charge
of the sales manual project. The sales manager can use projection to eliminate the department
name column and create a new table containing only department number and SSN.
Joining involves combining two or more tables. For example, you can combine the
project table and the department table to create a new table with the project number,
project description, department number, department name, and Social Security number for
the manager in charge of the project.
As long as the tables share at least one common data attribute, the tables in a relational
database can be linked to provide useful information and reports. Being able to link tables
to each other through common data attributes is one of the keys to the flexibility and power
of relational databases. Suppose the president of a company wants to find out the name of
the manager of the sales manual project and the length of time the manager has been with
the company. Assume that the company has the manager, department, and project tables
shown in Figure 5.5. A simplified ER diagram showing the relationship between these tables
is shown in Figure 5.6. Note the crow's-foot by the project table. This indicates that a
department can have many projects. The president would make the inquiry to the database,
perhaps via a personal computer. The DBMS would start with the project description and
search the project table to find out the project's department number. It would then use the
department number to search the department table for the manager's Social Security number.
The department number is also in the department table and is the common element that
links the project table to the department table. The DBMS uses the manager's Social Security
number to search the manager table for the manager's hire date. The manager's Social Security
number is the common element between the department table and the manager table. The
final result is that the manager's name and hire date are presented to the president as a response
to the inquiry (see Figure 5.7).

projecting
Manipulating data to eliminate
columns in a table.

joining
Manipulating data to combine two or
more tables.

linking
Data manipulation that combines
two or more tables using common
data attributes to form a new table
with only the unique data attributes.