How to decipher entity relationships in a Physical Data Model given the corresponding Logical Data Model

Question

Can someone use the entity relationships in this Logical Data Model (LDM):

to explain the entity relationships in this Physical Data Model (PDM):

• and how to specify the foreign keys in the PDM?

Answer 1

I'm not sure that I understood your question...

That said, lemme try to help you.

I undestand the model represents the categorization of 'parties' as begin part of 'segments'. These categorizations are mutable and have to be traceable in time.

Let me humbly present what I think is a more complete LDM, where

• PARTY is explicited
• Segment Group Cd is exported as part of the identifying relationship (a solid line)
• the verbs sentences are more verbose

From this LDM, the straightest derived 3NF (3rd Normal Form) PDM should be (PARTY entity not shown):

The PDM you posted:

1. is denormalized - MRKT_SGMNT_GROUP comprises both MRKT_SGMNT and MRKT_SGMNT_GROUP entities

2. is incomplete - it's missing Party_Sgmnt_End_Dttm

3. states redundant relationships - the two relationships are the same

4. or, more formalistically, states an incorrect relationship - the identifying relationship (the one represented as a solid line) yelds both key properties from MRKT_SEGMNT_GROUP as key properties to PARTY_SGMNT_HIST, which aren't - just Sgmnt_Id has been derived as part of the PK

With the denormalization presented, the PDM ought to be, with the missing property marked in bold red:

Did this serve as any help? Hope so...

Regards, Marcus Vinicius.