- 1. ET - Entity Named ET
- 2. E-T-AF
- ET1-A-ET2
- ET1-R-ET2-1-N
- ET1-R-ET2-N-M
- 👀ET1-R-A-ET2
- 👀Case 1
- 👀Case 2
- 🚩Case 3
- Case 4
- ET-B-ET1-ET2
Next, you are going to see, how to map EER diagrams to relations.
EER —- >> Relational Mapping
1. ET - Entity Named ET
What I am going to do here and in every single step of the mapping is that to the left I am going to show you the EER (Extended Relationship Model) notation and th the right I am going to show you fragments of the relations, I created by each step of the mapping.
The very first and very simple rule, we have, is, if we have an entity type - give it the name ET - then for that entity type we create a relation. And then the relation gets the same name ET
If an entity type ET has a property type B, the the relation ET will have B as an attribute.
If an entity type ET has a property type A, and that property type is an identifying property, then in the relation ET corresponding to it, there will be an attribute A which is identifying or primary attribute.
So, these steps are very, very simple.
Unfortunately, it gets a little more complicated than that.
We have an entity type ET with a composite property type C. And that composite property type C is composed from property types D and E. Then in the relation corresponding to ET, that adds two attributes D and E.
You might the ask, but happened to property C?
Unfortunately in the translation, property type C gets lost. That means for example if D an E are FirstName and LastName, in the relationship list you will not see the concept of Name composed from FirstName and LastName.
So, one could consider the following alternative way of mapping: It would be nice if in the mapping of ET the concept C would continue to appear with the two components it consists of, D and E.
However, if you remember back to the way we defined a relation, we said that a relation was defined as a subset of tuples that will construct it from domains with atomic values.
Now, this would certainly not be a domain with atomic values. The relational model is inherently flat, and the languages, we are going to look at only work when that’s the case.
So, this is the solution, we have: We have lost C.
2. E-T-AF
The rules for multi-value property types are quite special.
Let us say, we have entity type ET, and it has an identifying property type A. Then you know by a previous rule that in a relationship ET will attribute A, which is then the primary key.
Your multi-value property type F is definded for each F as follows:
A brand new relation is created. The name of that relation is the name of the entity type ET-F (F = the name of the particular multi-value property type we are dealing with, in this case F (Entity Type ProposedDance for example?). So, the name of the additional relation is ET-F. Together with that attribute ET-F we insert the identifying property type A for ET and then the combination of A and F become a composite key in ET-F.
The reason for all of this gymnastics is basically: F is multi-valued. We would not be able to just insert F as a property or attribute in ET. the reason for that is that we do not have multi-values in the relational model. Therefore we put it into a separate relation together with the identifying property type A and make the combination a key. The reason that works is that now for a given instance of ET it will have a unique value of A. A unique value of A can now be represented with multiple different values of F as long as the combination of A and F is unique. That’s the reason for the composite key.
Of course, the As, for which we called F values has to be a subset of As that exists. The A attribute in ET-F will be a foreign key on attribute A in ET.
ET1-A-ET2
Now, we make 1-1 relationship types.
We already know from a previous rule, how to make an ET1 with identifying property type A. It becomes relation ET1 with primary key attribute A.
Similar thing for ET2. In databases we need to be able to start for example with some B values and travel and find the corresponding A values via the relationship type R.
In relation database we need to be able to do the same thing.
There are 2 ways of doing that.
- In ET1 we insert the primary key B attribute of ET2 as sort of a pointer or reference from ET1 to ET2
- Alternatively, I could, of course, take the primary key of ET1 and insert as a reference in ET2 to point back. Again, this would allow to travel both ways.
Both solutions are acceptable.
There is one particular situation with which we would prefer one of these solutions.
Let us say that ET2 (ProposedDance? No, there are days without any exercise, no, there are Users, who may never dance each ProposedDance) has a mandatory participation in relationship type A. What that means is, that every single instance of ET2 in the database must be related via relationship R to ET1. Or in other words, there cannot be an instance of ET2 that is not in a relationship with ET1. In that particular case, it is not advisable to use solution 1.
Since there might be an instance ET1 that are not related, they will have NULL values in B. Solution 2 is much better, because every value in the ET2s will be related to each ET1 and therefore there would never be a NULL value for A.
ET1-R-ET2-1-N
Now, we map 1-to-many relationship types.
Let us consider the 2 different options we had before.
In one option ET1 would be pointing to ET2, and in the other one ET2 would be pointing to ET1. What would that mean?
In a 1-many relationship type the instances of the relationship type look as follows:
If we were to choose the first option, what would that mean? For each instance of ET1, there would obviously be a unique value of A. Associated with that you need unique value. In order to have multiple pointers or references to ET2 - just like shown on the right. But remember agan, you cannot have multi-valued in a field in a relation. That was exactly the reason we had to deal with in such a strange way in multi-valued attributes. So, the solution 1, having multiple pointers a a value of the attribute just does not work.
What about solution2, would that still work?
Yes, that still works, because you can see instances on the ET2 side point to a single value on the ET1 side.
So, it is ok, to insert attribute A as reference into ET1, because you need for every single value of B.
ET1-R-ET2-N-M
Many-to-many relationship types:
As learnt before, we cannot point from the ET2 side to the ET1 side, because that would require a multi-valued reference or pointer from ET1 to ET2.
From the other side, ET2 would have to have multiple pointers pointing to ET1 side, and that is not acceptable either.
Solution:
Instead of establishing only one relation, one for ET1 and one for ET2, aswe have correctly done in the third example, we need to establish a separate relation for R. Basically, the idea is that from A we will point to ET1 and the same for the ET 2 side.
A in R is a foreign key to A in the ET1 AND B in R is a foreign key to B in ET2.
It is important to notice that the attribute A and B that consitute the key of the relation R together. Why is that important?
It is important, because that is exactly what enforces many-to-many relationship type. It means that for a particular value of A identifying ET1 there will be multiple values of B that are possible and the unique combination is going to identify a tuple in R. And vice versa, for each unique value of B, there will be an instance of ET2 that is related to many instances in ET1.
👀ET1-R-A-ET2
Let us now investigate how we treat relationships R with weak entity ET2.
ET1 results in a relation with primary key A.
ET2 creates relation ET2 with attribute B.
How do we now discriminate between the B values?
We know that instances of ET2 needs values of the strong entity identifier A in order to discriminate between them. So, our solution is, we insert in ET2 a reference to ET1, that is the A attribute in ET2, it becomes a foreign key to the A attribute in ET1 (?) achja? 👀
Somewhat similar to what we saw in the many-to-many relationship type, the combination of A and B her constitute the key for ET2.
The reason is as follows.
When you look at an instance of ET1 identifier A, then through the one-to-many relationship type that will identify multiple values of ET2. Each one of those ET2 values has a value B. So, it takes the A and B in combination to distinguish between the instances of ET2.
👀Case 1
Case 4 - presented later - is also an acceptable solution to Case 1
Mapping supersubtype relationship types is a little bit more complicated.
There are four cases, I would like to consider.
In the first case the relationship between ET and the two subtypes is mandatory. That means that every single instance of ET needs to be hooked up to ET1 or ET2.
Aaah, wie ProposedDance to either Teaching Video or YouTube Video (or both).
However, in this particular situation, we are looking at mandatory and disjoint (the d in the circle). The constaint here is, that ET1 and ET2 are disjoint (unzusammenhängend).
The semantics of this case is the following.
Every single ET instance will have an A value and a B value. But we also know that every single instance of ET is hooked up either to ET1 or ET2 or both. Furthermore we know when we look at ET1, it will inherit attribute A and B, and have C, and when you look at ET2, all values will inherit attribute A and B and have D.
👀Case 2
A slight variation in the EER but makes a fairly big difference in the mapping is, when the constraint is overlapping.
Every single instance of ET is either an instance of ET1 or of ET2 or of both.
The first option, which I really do not like, is an option where we create a single relation. We could call that relation ET. It has attribute A and B. It has attribute C, which is representing the attribute C for ET1 and attribute D as an example of the attribute D of ET2.
And then it has an attribute called type. Type will say whether that is an instance that is in ET1 or whether it si an instance that is in ET2 or whether it is an instance that is in both ET1 and ET2.
There are several reasons, I do not like this option. One is that when there are instances of ET that is in either ET1 or in ET2, one of those C and D will automatically be a NULL value.
The type attribute better fit with and correspond to whether there is a vlaue in C or in D or in both places. This solution is prone (anfällig) to consistency problems.
The solution I like better is the following.
There is a relation for ET generated with attributes A and B. There is a relation generated for ET1, it inherits the identifying attribute A and has C represented with. And there is a relation generated for ET2, again inheriting A and with the unique property D.
Since ET is mandatorily related to either ET1 or ET2, every singel tuple in ET relation will either have a matching tuple in ET1 or ET2 or in both, beacuse it is overlapping.
Now, again, this seems to be the two different Videos in LineDance. Why can I not distinct between the Case 1 and 2?
On the other hand, the real issue with this representation here is that each A value is potentially representing a couple of additional times. I do not find this a big negative. Of course, as we have seen before, the reference from ET1 to ET is a foreign key, as is the reference from ET2.
🚩Case 3
In case 3, ET is not mandatorily related to ET1 and/or ET2. And ET1 and ET2 are allowed to overlap
This is an option: you collect all the attributes together. Now, you may have actually tuples in the single relation that has A and B values, but no C or D values, because that is not mandatory anymore.
Second solution: Very similar to the one we used in Case 2 is, that we generate a relation for ET, ET1 and ET2 each. Attribute A is inherited in ET1 and ET2 as identifying property, both will be foreign keys. It is now possible to have instances of ET with A and B values, where the corresponding A value does not exist in ET1 or ET2 simply because it is not mandatory.
It is likewise possible to have instances of ET that are in ET1 or instances of ET that are in ET2.
So, this is a fairly pretty solution.
061_LineDance_EER_V0.3.jpg
Case 4
In Case 4 there is a relationship type that is non-mandatory and disjoint. An elegant solution to that is this one:
You generate the relations for ET, ET1 and ET2, and of course, ET1 and ET2 will have A as an identifying attribute. The reason this is a really beautiful solution is, that since this is not mandatory, you can have instances of ET that have no corresponding tuples in ET1 and ET2.
However, when there are tuples in ET1 or in ET2, because of this disjointness, atuple will only be in one of them. So for an instance in ET1 an instance with a corresponding A value will either be in ET1 or in ET2. So, there is not a replication of an A value in the other ET.
ET-B-ET1-ET2
ET is a union type. ET is a subset of union of ET1 and ET2.
So, we know we are going to get a relation for ET1, and we know, it has identifying attribute C. We know, we will get one for ET2 with identifying attribute D. We know, we have a relation for ET with property type B. The question now is: How do we model this subtype relationship?
One way, which is sort of a “hack”, that works well, is the following: We insert an artificial identifier ET-ID in the relation ET. That identifier will then consist either of what corresponds to C or corresponds to D. Remember, every single element in ET comes either from ET1 or from ET2, so it will be identified by C or D.
So, the ET identifier ET-ID is a foreign key from ET1 and ET2 to ET.