Please enable JavaScript.
Coggle requires JavaScript to display documents.
Logical design - Coggle Diagram
Logical design
Normal Forms
Second normal form: remove partial dependencies from first normal form
First normal form: only contain atomic domains
Third normal form: remove transitive dependencies from second normal form
Boyce-Codd Kent normal form: remove remaining anomalies resulting from multiple candidate keys
Use to validate logical design
Normal forms using key will use decomposition algorithm and functional dependency.
Mapping conceptual model to logical model
Each entity is a table, attributes become a column in the table, entities's identifier become table primary key
Mapping with relationships
Binary relationships
1 to Many: primary from one side become foreign key in many side
Many to Many: New relation with composite key: primary keys of two entities
One to One: Primary key on mandatory side become foreign key on optional side
Unary relationships
1 to Many: become foreign key in the same relation
Many to Many: become 2 relations: one is the entity, the other one is associative relation with composite keys taken from primary key of entity
Ternary relationships: Each entities is a relations and the relationship became an associative entity with primary key composed of entities' primary keys
Supertype/Subtype relationships
Option A: Multiple relations - keep both superclass and subclasses
Option B: Keep subclass relations only. Conditions: applied only to t, d
Option C: Single relation with one type attributes
Option D: Single relations with multiple type attributes
Mapping entities
Weak Entities: become separate relation with primary key is a composite key composed of primary key of superior entity and partial identifier of weak entity
Regular entities
Multivalued attributes: A new table with foreign key taken from superior entity
Simple attributes: map directly to relations
Composite attributes: use component attributes only
Associative Entities: if entity has identifier then identifier become its primary key else its primary keys composed of primary keys from two entities.
Validate model
against user requirements
validate integrity constraints
using normal forms
Design goals
Lossless join: can be check using Tableu table. This must be satisfied when normalize a model
Dependancy preservation: Can be conserved using decomposition algorithm
Highest normal form possible