Chapter 6 Generating from a Conceptual Data Model
Primary identifiers generate primary and foreign keys in the PDM. Identifiers that are not primary identifiers generate alternate keys.
The type of key that is generated in the PDM depends on the cardinality and type of dependency defined for a relationship in the CDM.
A primary key is a column or columns whose values uniquely identify a row in a table.
A foreign key is a column or columns that depend on and migrate from a primary key column in another table.
An alternate key is a column or columns whose values uniquely identify a row in a table, and is not a primary key.
In independent one-to-many relationships, the primary identifier of the entity on the one side of the relationship becomes a:
The CDM below shows an independent relationship. Each division contains one or more employees:
The CDM above generates two tables:
| Table | Primary key | Foreign key |
|---|---|---|
| Division | Division number | — |
| Employee | Employee number | Division number |
The following PDM results from generation:
In dependent relationships, the primary identifier of the nondependent entity becomes a primary/foreign key in the table generated by the dependent entity.
The migrated column is integrated into the primary key if it already exists.
The CDM below shows a dependent relationship. Each task must have a project number.
The CDM above generates two tables:
| Table | Primary key | Foreign key |
|---|---|---|
| Project | Project number | — |
| Task | Project number/Task number | Project number |
The following PDM results from generation.
In independent many-to-many relationships, the primary identifiers of both entities migrate to a join table as primary/foreign keys. The CDM below shows an independent relationship. Each employee can be a member of one or more teams, and each team can have one or more employees as members.
| Table | Primary key | Foreign key |
|---|---|---|
| Team | Team number | — |
| Employee | Employee number | — |
| Member | Team number/Employee number | Team number/Employee number |
The following PDM results from generation.
In independent one-to-one relationships, the primary identifier of one entity migrates to the other generated table as a foreign key.
| Copyright (C) 2005. Sybase Inc. All rights reserved. |
| |