|
transformation rules- object sets and attributes: objects become tables, attributes become columns
- no external keys: add column for internal key
- specialization and generalization: specialization table contains foreign key that points to generalization table
- 1-1 relationships: model as new column if (1,1--1,1) or with foreign key if (0,1--1,1): could be one or two tables
- 1-* relationships: two tables with foreign key in the * side
- *-* relationships: new link table with composite key (and possible aggregation)
- aggregate objects: link table for each aggregation
- recursive relationships: add key as foreign key column within the table itself
transformation examples- fig 5.23: manwaring consulting model
- fig 5.24 enhanced manwaring consulting model
normalization considerations in table design- abbreviations used in this section are: fd=functionally dependent, nka=non-key attribute, dknf=domain key normal form
- each table contains one or more columns whose values together identify a unique instance (row) of the table. these columns are together called the key
- the key contains the minimum number of columns that can be used to identify a unique row
- x is fd on y means that once you know the value of y you know the value of x without ambituity.
- a table is dknf if every nka in the table is fd on the key, the whole key, and nothing but the key so help you codd and no part of the key is fd on another part of the key.
- therefore a table is not dknf if
- one nka is not fd on the key (violates "fd on the key")
- one nka is fd on part of the key (violates "the whole key")
- one nka is fd on another nka (violates "nothing but the key")
- in a two-column composite key, one column is fd on the other (violates "no part of the key is fd on another part of the key")
|
