Establishing Relationships between Objects
Go Up to Designing Databases
The relationship between tables and columns in the database must be defined in the design. For example, how are employees and departments related? An employee can have only one department (a one-to-one relationship), but a department has many employees (a one-to-many relationship). How are projects and employees related? An employee can be working on more than one project, and a project can include several employees (a many-to-many relationship). Each of these different types of relationships has to be modeled in the database.
The relational model represents one-to-many relationships with primary key/foreign key pairings. Refer to the following two tables. A project can include many employees, so to avoid duplication of employee data, the PROJECT
table can reference employee information with a foreign key. TEAM_LEADER
is a foreign key referencing the primary key, EMP_NO
, in the EMPLOYEE
table.
PROJ_ID
|
TEAM_LEADER
|
PROJ_NAME
|
PROJ_DESC
|
PRODUCT
|
---|---|---|---|---|
DGPII |
44 |
Automap |
blob data |
hardware |
VBASE |
47 |
Video database |
blob data |
software |
HWRII |
24 |
Translator upgrade |
blob data |
software |
EMP_NO
|
LAST_NAME
|
FIRST_NAME
|
DEPT_NO
|
JOB_CODE
|
PHONE_EXT
|
SALARY
|
---|---|---|---|---|---|---|
24 |
Smith |
John |
100 |
Eng |
4968 |
64000 |
48 |
Carter |
Catherine |
900 |
Sales |
4967 |
72500 |
36 |
Smith |
Jane |
600 |
Admin |
4800 |
37500 |
For more information on using PRIMARY KEY
and FOREIGN KEY
constraints, see Working with Tables.