Establishing Relationships between Objects

From InterBase
Jump to: navigation, search

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.

PROJECT 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

EMPLOYEE table
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.

Topics

Advance To: