Building Logical Data Model in DBMS

Build and validate logical data model

• To translate the conceptual data model into a logical data model and then to validate this model to check that it is structurally correct and able to support the required transactions.

Activities included in building logical data model are :

• Derive relations for logical data model
• Validate relations using normalization
• Validate relations against transactions
• Check integrity constraints
• Review logical data model with user
• Merge logical data models into global model (optional)
• Check for future growth

Derive relations for logical data model

• To create relations for the logical data model to represent the entities, relationships, and attributes that have been identified
• Database Definition Language (DBDL) for relational databases
• Relation name followed by a list of the relation's attributes enclosed in brackets
• Identification of PK, AKs and FKs
• Placement of FKs (parent/child)

Relations are derived for the following structures that may occur in a conceptual data model:

• Strong entity types
• Weak entity types
• One-to-many (1:*) binary relationship types
• One-to-one (1:1) binary relationship types
• One-to-one (1:1) recursive relationship types
• Superclass/subclass relationship types
• Many-to-many (*:*) binary relationship types
• Complex relationship types
• Multi-valued attributes

Strong entity types

• For each strong entity in the data model, create a relation that includes all simple attributes of that entity.
• For composite attributes, include only the constituent simple attributes

               Staff (staffNo, fName, IName, position, sex, DOB)

        Primary Key staffNo 

Weak entity types

• For each weak entity in the data model, create a relation that includes all the simple attributes of that entity.
• The primary key of a weak entity is partially or fully derived from each owner entity and so on the identification of the primary key of a weak entity cannot be made until after all the relationships with the owner entities have been mapped

              Preference (prefType, maxRent)

              Primary Key None (at present) 

One-to-many (1:＊) binary relationship types

• For each 1:＊binary relationship, the entity on the 'one side' of the relationship is designated as the parent entity and the entity on the 'many side' is designated as the child entity
• To represent this relationship, post a copy of the primary key attributes(s) of parent entity into the relation representing the child entity, to act as a foreign key e.g. Staff , Client relationship etc.
• In the case where a 1:＊ relationship has one or more attributes, these attributes should follow the posting of the primary key to the child relation e.g. attribute called dateRegister etc.

One-to-one (1:1) binary relationship types

• Creating relations to represent a 1:1 relationship is more complex as the cardinality cannot be used to identify the parent and child entities in a relationship.
• Instead, the participation constraints are used to decide whether it is best to represent the relationship by combining the entities involved into one relation or by creating two relations and posting a copy of the primary key from one relation to the other, consider the following:

        Mandatory participation on both sides of 1:1 relationship
        Mandatory participation on one sides of 1:1 relationship

        Optional participation on both sides of 1:1 relationship

One-to-one (1:1) recursive relationship types

• For 1:1 recursive relationship, follow the rules for participation as described above for a 1:1 relationship.
• Mandatory participation on both sides, represent the recursive relationship as a single relation with two copies of the primary key.
• As before, the copies of the primary keys act as foreign keys and have to be renamed to indicate the purpose of each in the relation.
• Optional participation on both sides, again create a new relation as described above.

Superclass/subclass relationship types

• Identity superclass entity as parent entity and subclass entity as the child entity.
• There are various options on how to represent such a relationship as one or more relations.

Many-to-many (＊:＊) binary relationship types

• Create a relation to represent the relationship and include any attributes that are part of the relationship
• We post a copy of the primary key attributes(s) of the entities that partitions in the relationship into the new relation, to act as foreign keys.
• These foreign keys will also form the primary key of the new relation, possibly in combination with some of the attributes of the relationship.

Complex relationship types

• Create a relation to represent the relationship and include any attributes that are part of the relationship.
• Post a copy of the primary key attributes(s) of the entities that participate in the complex relationship into the new relation, to act as foreign keys.

Multi-valued attributes

• Create anew relation to represent multi-valued attribute and include primary key of entity in new relation, to act as a foreign key.
• Unless the multi-valued attribute is itself an alternate key of the entity, the primary key of the new relation is the combination of the multi-valued attribute and the primary key of the entity.