Database Modeling & Design Fourth Edition- P22: Database technology has evolved rapidly in the three decades since the rise and eventual dominance of relational database systems. While many specialized database systems (object-oriented, spatial, multimedia, etc.) have found substantial user communities in the science and engineering fields, relational systems remain the dominant database technology for business enterprises. | 92 CHAPTER 5 Transforming the Conceptual Data Model to SQL The many-to-many relationship is shown as optional Figure and results in a new table it could also be defined as mandatory using the word must instead of may both cases have the foreign keys defined as not null. In many-to-many relationships foreign key constraints on delete and update must always be cascade because each entry in the SQL table depends on the current value or existence of the referenced primary key. Ternary and n-ary Relationships An n-ary relationship has n 1 possible variations of connectivity all n sides with connectivity one n - 1 sides with connectivity one and one side with connectivity many n - 2 sides with connectivity one and two sides with many and so on until all sides are many. The four possible varieties of a ternary relationship are shown in Figure for the ER model and Figure for UML. All variations are transformed by creating an SQL table containing the primary keys of all entities however in each case the meaning of the keys is different. When all three relationships are one Figure the resulting SQL table consists of three possible distinct keys. This arrangement represents the fact that three FDs are needed to describe this relationship. The optionality constraint is not used here because all n entities must participate in every instance of the relationship to satisfy the FD constraints. See Chapter 6 for more discussion of functional dependencies. In general the number of entities with connectivity one determines the lower bound on the number of FDs. Thus in Figure which is one-to-one-to-many there are two FDs in Figure which is one-to-many-to-many there is only one FD. When all relationships are many Figure the relationship table is all one composite key unless the relationship has its own attributes. In that case the key is the composite of all three keys from the three associated entities. Foreign key constraints on delete and .
