Denormalization is achieved by merging and splitting database tables. In the process, the tables are either merged using a foreign key or become sibling tables with identical identifying key. Splitting tables involves separating rows or columns.
Normalization is used to model a redundancy-free database requiring a minimum of memory. By creating a conceptual model in an entity relationship model, it is simple to arrive at the 3rd normal form if you avoid transitive or direct dependencies of attributes in an entity. The 3rd normal form is the practical standard in real projects.
A normalized ER model is transferred to a structurally equal database model via an initial mapping step; this model thus represents a normalized database. In the process, the mapping links the corresponding elements (entity and table, attribute and table column) to dependencies.
If denormalization is required for any reason, you can implement this in the database model by merging and splitting tables. This can lead to attributes of an entity being mapped in multiple database tables. Similarly, a database table can form the database for multiple entities. The relationship between ER model and DB model is fully documented owing to the preservation of the mapping dependencies.
Denormalization is performed in Innovator for Database Architects in the database diagram.
Select the foreign key edge, click with the right mouse button and choose Merge>To unique key>Quantity from the context menu.
The columns of the foreign key table are brought into the table with the unique key (one set for each quantity of the command).
The remaining table contains an element of type Table Added with Direction property 'to the primary key'.
The moved columns all receive the Null Property 'zero'.
The columns of the foreign key are merged to the respective columns of the referenced key.
The foreign key table is deleted.
Select the foreign key edge, click with the right mouse button and choose Merge>To foreign key from the context menu.
The columns in the table with the referenced key are brought into the table with the foreign key.
The remaining table contains an element of type Table Added with Direction property 'to the foreign key'.
The moved columns retain their Null Property unless it was an optional foreign key. If tables are merged to an optional foreign key, all added columns receive the Null Property 'zero'.
The tables of the referenced key are merged to the respective columns of the foreign key.
The unique key table is deleted.
Select two database tables which each have a foreign key for the same table marked with 'Z'; click with the right mouse button and choose Merge>Sibling tables from the context menu.
The columns of the table selected second are brought into the table selected first. The remaining table contains an element of type Table Added with Direction property 'to the sibling'. All columns in both tables receive the Null Property 'zero'. The primary keys are merged. The table selected second is deleted.
Select a database table, click with the right mouse button and choose Split>Rows from the context menu.
A copy of the table is created with identical columns and foreign key. Foreign keys that previously referenced the table are each changed to two optional foreign keys; one references the old table and one the new.
In the compartment of the table columns of a database table, select one or more table columns; click with the right mouse button and choose Split>Columns from the context menu.
A new table is created, into which the selected columns are moved. The new table also receives a copy of the primary key, the columns of which, at the same time, form a foreign key on the old table.
© 1986-2014 MID GmbH Nuremberg Germany. DIN EN 9001 certified. All rights reserved.