EER Model Trying

[Misionero]

Hi
"Within the EER model, I need to understand the difference to Kknow an identifying relationship. Could you provide me with an example that I can extrapolate to others? Thank you."

 

[GaP42]

An ER or EER model is a visual representation of data and constraints and its relationships to other data. In both an identifying relationship is a relationship between two entities in which an instance of a child entity is identified through its association with a parent entity. The child entity is dependent on the parent entity for its identity and cannot exist without it.
So for example - an Order Line item cannot exist without the existence of an Order, A real human child cannot exist without biological parents (although that may be getting close to changing - think Dolly the sheep) and other clones, or in other species parthogenesis ..), or a (geopolitical) State may only exist within the context of a Country.

Welcome to the forum

 

[Gasman]

Always my first point of call.

er diagrams explained - Google Search

Next

https://www.youtube.com/results?search_query=er+diagrams

 

[theDBguy]

Hi
"Within the EER model, I need to understand the difference to Kknow an identifying relationship. Could you provide me with an example that I can extrapolate to others? Thank you."

Hi. Welcome to AWF!

Is this class assignment?

 

[Misionero]

Hi
"Within the EER model, I need to understand the difference to Kknow an identifying relationship. Could you provide me with an example that I can extrapolate to others? Thank you."

Hi. Welcome to AWF!

Is this class assignment?

I am trying prepare a nice manual for my students. There are several concepts where i have doubts.

 

[Gasman]

So ask about those concepts?

 

[Misionero]

An ER or EER model is a visual representation of data and constraints and its relationships to other data. In both an identifying relationship is a relationship between two entities in which an instance of a child entity is identified through its association with a parent entity. The child entity is dependent on the parent entity for its identity and cannot exist without it.
So for example - an Order Line item cannot exist without the existence of an Order, A real human child cannot exist without biological parents (although that may be getting close to changing - think Dolly the sheep) and other clones, or in other species parthogenesis ..), or a (geopolitical) State may only exist within the context of a Country.

Welcome to the forum

Thanks for your examples. The identifying relationship, can be 1:1 1:N or N:M or are limited?

 

[Misionero]

Hi. Welcome to AWF!

Is this class assignment?

No its not. I am the teacher ... I am preparing the theory about this world. I have my own system to create models, but in this course i want use other standards. My problem is the indentification any concepts, because i dont know if my onw name is the same than other, or they are diferent concepts.

 

[Misionero]

So ask about those concepts?

Thanks for your recomendation of using google and youtube.

 

[GaP42]

Thanks for your examples. The identifying relationship, can be 1:1 1:N or N:M or are limited?

Consider the mandatory/optional (participation) nature of the relationship to help determine if it is an identifying relationship.
If the relationship to an entity is not Mandatory - then the relationship to that entity cannot be an identifying relationship.

A 1:1 relationship would generally suggest a flaw in the model under normalisation rules - the data in both entities can be identified by the key in present in either entity. There may be other reasons to split such data into two entities, such as in the case of a very sparsely populated, large and wide data entity - but these tend to be physical considerations rather than logical or conceptual. Although one could model User and User Profile as 1:1 (if there is no need to keep user profile history) - a user profile record requires a user record to exist - it is mandatory - and therefore an identifying relationship. (represented in ER as USER-|----o-PROFILE) - the USER record must be created first so that a Profile record can then be created - so optional in the USER -- PROFILE direction)

A 1:N relationship is often where an identifying relationship is needed - but if the relationship is described as optional then the entity record may or may not store the foreign key identity from that relationship - so it would not be an identifying relationship.
( relationship represented as >|---o- or >|---|- or >o---o- or >o---|-) - excuse my rough crow's feet notation
Performers may be Booked for Performances say. Within the context of the business, the business may be set up such that either
1. A Performer must have at least one Booking and each Booking must have a performer - mandatory participation in both directions
2. A Performer may have one or more Bookings, however the Booking must involve a performer - mandatory participation is dictated for a Performer in a Booking,
3. A Performer must have at least one Booking, while a Booking may be for one or more Performers - mandatory participation is dictated for Performers however a booking may not involve a performer (eg some other type of booking event - exhibition)
4. A Performer may have one or more Bookings and a Booking may be for one or more Performers - participation is optional in both.

Identifying relationships will only occur where the Booking requires a Performer (Case 1, Case 2). Case 3, 4 do not require a performer to be identified, and the Performer entity does not identify a booking.

For M:N relationships in ER models, the analysis on the basis of participation can be applied to determine in the relationship is identifying - although it appears that if mandatory in both directions you would have the difficulty of there being 2 identifying relationships referring to the other entity - a circular reference and probably not practical. These are ultimately resolved through application of normalization rules. For M:N focus on the relationship in one direction only.

EDIT: Further to the above:
Within EER the concept of Strong and Weak entities are considered:
The relationship must be many-to-one from the weak to the identifying entity. Participation of the weak entity in the relationship must be mandatory. The discriminator (or partial key) of a weak entity distinguishes weak entities that depend on the same specific strong entity. The primary key of a weak entity is the primary key of the identifying entity set + the partial key of the weak entity set.

Eg: Many payments are made on a loan

• Payments don't exist without a loan.
• Multiple loans will each have a first, second payment and so on. So, each payment is only unique in the context of the loan which it is paying off.

The payment is a weak entity; its existence is dependent on the loan entity.
Other cases: SalesOrder- SalesOrderLineItem, Country - State
Generally the logical/conceptual primary key of the weak entity is the composite of the primary key of the strong entity and an identifier for the weak entity within the context of the strong entity. The physical implementation of the weak entity will store the PK of the strong entity, and in many cases, partial key representing the line item (PK of weak entity = PK of strong entity + weak entity partial key). However, in some cases the design may invoke a unique PK for the weak entity, although often there is some means to also represent the order (eg lineorder sequence no) - as a result - these identifying relationships can be somewhat obscured in the physical implementation

 

[Misionero]

Consider the mandatory/optional (participation) nature of the relationship to help determine if it is an identifying relationship.
If the relationship to an entity is not Mandatory - then the relationship to that entity cannot be an identifying relationship.

A 1:1 relationship would generally suggest a flaw in the model under normalisation rules - the data in both entities can be identified by the key in present in either entity. There may be other reasons to split such data into two entities, such as in the case of a very sparsely populated, large and wide data entity - but these tend to be physical considerations rather than logical or conceptual. Although one could model User and User Profile as 1:1 (if there is no need to keep user profile history) - a user profile record requires a user record to exist - it is mandatory - and therefore an identifying relationship. (represented in ER as USER-|----o-PROFILE) - the USER record must be created first so that a Profile record can then be created - so optional in the USER -- PROFILE direction)

A 1:N relationship is often where an identifying relationship is needed - but if the relationship is described as optional then the entity record may or may not store the foreign key identity from that relationship - so it would not be an identifying relationship.
( relationship represented as >|---o- or >|---|- or >o---o- or >o---|-) - excuse my rough crow's feet notation
Performers may be Booked for Performances say. Within the context of the business, the business may be set up such that either
1. A Performer must have at least one Booking and each Booking must have a performer - mandatory participation in both directions
2. A Performer may have one or more Bookings, however the Booking must involve a performer - mandatory participation is dictated for a Performer in a Booking,
3. A Performer must have at least one Booking, while a Booking may be for one or more Performers - mandatory participation is dictated for Performers however a booking may not involve a performer (eg some other type of booking event - exhibition)
4. A Performer may have one or more Bookings and a Booking may be for one or more Performers - participation is optional in both.

Identifying relationships will only occur where the Booking requires a Performer (Case 1, Case 2). Case 3, 4 do not require a performer to be identified, and the Performer entity does not identify a booking.

For M:N relationships in ER models, the analysis on the basis of participation can be applied to determine in the relationship is identifying - although it appears that if mandatory in both directions you would have the difficulty of there being 2 identifying relationships referring to the other entity - a circular reference and probably not practical. These are ultimately resolved through application of normalization rules. For M:N focus on the relationship in one direction only.

EDIT: Further to the above:
Within EER the concept of Strong and Weak entities are considered:
The relationship must be many-to-one from the weak to the identifying entity. Participation of the weak entity in the relationship must be mandatory. The discriminator (or partial key) of a weak entity distinguishes weak entities that depend on the same specific strong entity. The primary key of a weak entity is the primary key of the identifying entity set + the partial key of the weak entity set.

Eg: Many payments are made on a loan

• Payments don't exist without a loan.
• Multiple loans will each have a first, second payment and so on. So, each payment is only unique in the context of the loan which it is paying off.

The payment is a weak entity; its existence is dependent on the loan entity.
Other cases: SalesOrder- SalesOrderLineItem, Country - State
Generally the logical/conceptual primary key of the weak entity is the composite of the primary key of the strong entity and an identifier for the weak entity within the context of the strong entity. The physical implementation of the weak entity will store the PK of the strong entity, and in many cases, partial key representing the line item (PK of weak entity = PK of strong entity + weak entity partial key). However, in some cases the design may invoke a unique PK for the weak entity, although often there is some means to also represent the order (eg lineorder sequence no) - as a result - these identifying relationships can be somewhat obscured in the physical implementation

Incredible answer. I am working in it. But i think you are an expert teacher with your explanations. Thanks a lot.

 

[gemma-the-husky]

Thanks for your examples. The identifying relationship, can be 1:1 1:N or N:M or are limited?

If they are many to many (i assume that is what is meant by N:M, then that cannot be represented in a relational database, and has to be replaced with a junction table, to yield 2 x 1:N relationships.

For example, a customer cannot be directly related to ordered items. You have to include a junction table of orders.

So customer 1 -- N orders and
Orders 1- N ordered items.

Rather than customers M -N ordered items.

This is exemplified by repeating groups within the subordinate table data. In the case of ordered items, you would find that there is a repeated group of customer+order number fields, (and most likely order_date, order_number and so on).

If you don't eliminate the repeating groups you often find a query produces an incorrect output because of cross-product results.

Also 1:1 joins should not really occur. There's is no need for 1:1 joins in data modelling. You may want to include a 1:1 join for some specific data issues. Eg, you want some fields to be confidential, and the confidential items could be stored in a separate table with a 1:1 relationship with the main data table.

@GaP42 mentioned this.
Sometimes what you think is a 1:1 is really a 1:N, with only one item on the many side. In such cases it should be considered as 1:N. The difference is with a 1:1 the PK should be the same, so there could never be a second item in the many side.


I hope this helps.

I find entity relationship modelling can help you visualise the data structure of the project. You should then find that every datum you want to store fits into one of the entities without duplication and without causing repeating groups. If it doesn't you need to reconsider your ER model.

 

[Misionero]

If they are many to many (i assume that is what is meant by N:M, then that cannot be represented in a relational database, and has to be replaced with a junction table, to yield 2 x 1:N relationships.

For example, a customer cannot be directly related to ordered items. You have to include a junction table of orders.

So customer 1 -- N orders and
Orders 1- N ordered items.

Rather than customers M -N ordered items.

This is exemplified by repeating groups within the subordinate table data. In the case of ordered items, you would find that there is a repeated group of customer+order number fields, (and most likely order_date, order_number and so on).

If you don't eliminate the repeating groups you often find a query produces an incorrect output because of cross-product results.

Also 1:1 joins should not really occur. There's is no need for 1:1 joins in data modelling. You may want to include a 1:1 join for some specific data issues. Eg, you want some fields to be confidential, and the confidential items could be stored in a separate table with a 1:1 relationship with the main data table.

@GaP42 mentioned this.
Sometimes what you think is a 1:1 is really a 1:N, with only one item on the many side. In such cases it should be considered as 1:N. The difference is with a 1:1 the PK should be the same, so there could never be a second item in the many side.


I hope this helps.

I find entity relationship modelling can help you visualise the data structure of the project. You should then find that every datum you want to store fits into one of the entities without duplication and without causing repeating groups. If it doesn't you need to reconsider your ER model.

Very nice help. Thanks. I am studying your information.