- Single-valued Property Types
- Identifying Properties
- Composite Properties
- Multi-valued Properties
- One-to-One Relationships
- One-to-Many Relationship Types
- Mandatory 1-N Relationships
- N-M Relationship Types
- N-ary Relationship Types
- ❗Many Relationship Types
- Identifying Relationships And Weak Entity Types
- Recursive Relationship Types
- Supertypes an Subtypes
- ➡️Union Entity Types
- Thing, Relationship, Property
- Are Relationships Entities?
- ❗Another Example | Objectified Relationship Type
- Messing With Your Brain Part 1
- Messing With Your Brain Part 2
- Relationship Type or Entity Type?
- What Can the EER do?
- Car Graphic
- What Is The Result Type of A Query?
- EER Diagram
- Relational Model Theoretical Foundation V2
- Relational Model
- Data Structures
- Big Deal
- Constraints
In order to do data modeling we need data models. The EER is one data model that is particularly good at helping us fix and represent a perception of reality
Single-valued Property Types
Entity types are represented by squares with rounded edges
Property types are represented by ellipses; these ellipses carry names. For the entity USER, the single line ellipse represents a single value property
The three instances of the entity USER
Property values can take many forms, they can be lexical, they could be a visible element, they could be audibles. They could be things that name other things (like the usermail names the PWD).
Identifying Properties
Some property types are identifying property types. That is shown by underlining the name of the property type. This has the following implication: When you point to a particular value (like leo@test.de), there can be only a single entity instance identified by that Email value. What would not be possible, is that given a value of Email (red), that this would point to two different instances of USER.
It is important to understand that every single entity instance in the database at any given point in time must be uniquely referenceable. Later on we are seeing multiple property types and even in some situations relationship types are needed in order to uniquely reference references of entities.
Composite Properties
Composite Property Type, example:
Instance diagram:
Multi-valued Properties
Multi-valued property types are modeled by the double ellipse. Here we have a regular user, who has a multi-valued property Interest.
Here you have the instances of your regular users, there are four instances of those. This multi-value consists of reading, chess and math. The third user is interested in biking, it is acceptable, although it is just one. The third user has a whole bunch of interests, soccer, beer, cricket, baseball.
I failed with the knowledge test:
One-to-One Relationships
Let us now turn our attention to relationship types: Relationship types are represented by diamonds. Both entities are connected via the relationship CurrentMarriage. The “1” indicates the cardinality of the relationship. It says that this relationship CurrentMarriage is a 1-to-1 relationship type.
Here we have five instances of MaleUser and six instances of FemaleUser.
Note that there MaleUsers that are currently not married and FemaleUsers that are currently not married. Since there are instances of MaleUser and FemaleUser that are not married, this function here or this mapping between these two entity types is a partial function.
It is a function, because for each one of the elements on the right there is at most one element on the left that it is mapped to. It is partial, because there are some that are not mapped to anything.
One-to-Many Relationship Types
It is 1-to-many, because each circle of employer can be hooked to zero, one or more instances of RegularUser.
There is a partial function from this set of circleson the right representing RegularUsers to this set of circles on the left representing Employers. It is partial, because it is not necessary that every single User is mapped to Employers.
It is also the case that some employers do not have any RegularUsers working for them. That is not related to being a partial function.
Mandatory 1-N Relationships
A variation of a 1-to-many relationship type is one that is to constrained to be mandatory.
The bold solid line signifies that RegularUser mandatorily must participate in CurrentJob.
That means, we have a set of Employers and a set of RegularUsers. There are a couple of employers that have no employees, but notice that all the RegularUsers have an Employer.
Thus it is a Total Function.
N-M Relationship Types
This is a many-to-many relationship type between RegularUser and Schools they attended.
The first RegularUser has attnded 4 Schools, the second user 1 School, the third RegularUser two Schools.
See what happens now, when we are dealing with M-N relationship types. Notice now, that the first RegularUser may have attended these 4 Schools, but notice that this School here (x) actually has 3 RegularUsers, who have attended that School. In other words any entity on the left may be mapped to multiple instances on the right side and vice versa.
No longer do we have a function here. This is a relationship in a mathematical sense.
N-ary Relationship Types
So far, all the relationship types, we have looked up, have been binary. We are now going to look at an N-ary relationship type with N > 2.
In this case here the EventTeamMember relationship type is a ternary relationship type. It relates three entity types: RegularUser, Team, and Event.
The semantics of this ternary relationship type is somewhat complicated. Let us try to analyze exactly, what it means.
Let us fix Email to be a single value.
Let us fix EventName to be a single value.
Now we have a pair of 1 RegularUser and 1 Event.
Because it is many on the relation (M) between Team and EventTeamMember, that means that a particular RegularUser in a particular Event may participate on many Teams.
Now, let us fix EventName and TeamName. Since these are the identifiers EventName gives us a single Event, and TeamName Team. This Team on this Event has many (L) RegularUsers.
Finally, when we fix an Email and a TeamName, in order to have a pair of 1 RegularUser in 1 Team, there are many (N) Events that that particular RegularUser being on that Team may be participating in this Event.
So, the meaning of an instance of this relationship type EventTeamMember really is the following. A particular RegularUser on a particular Team participates in one particular Event. The implication of that is that you need Email, EventName, and TeamName to identify precisely a single instance of this relationship type (EventTeamMember).
It is quite rare that you see relationship types that are degree higher than 2. One of the reasons for that, they are difficult to understand, they are difficult to explain. Unfortunately, the problem is, that it is not always possible to take a N-ary relationship type with N3 or greater and to decompose it into a conjuction of binary relationships.
We look at that next.
❗Many Relationship Types
The relationship type we had, was revised to basically three different binary relationship types.
RegularUser is hooked up to Team by the relation UserTeam.
RegularUser is hooked up to Event by the relation UserEvent.
The Team is hooked up to the Event by the relation TeamEvent.
What is the meaning of this?
The relation UserTeam would merely state that a RegularUser is on a particular Team. It would state that RegularUser participates in some Event. It would state that in some Event particular Teams are participating. But those three facts together do not cover that a particular RegularUser on a particular Team participates in a particular Event. That space is much more narrow than what is illustrated by this.
Identifying Relationships And Weak Entity Types
In this example, we have two entity types: RegularUser and StatusUpdate. We have an identifying property Email and another property DateAndTime. The idea is as folllows: RegularUsers can post multiple status updates during the day to their website. StatusUpdates, when they are posted will have a date and time associated with them. However, other users could potentially post at the same DateAndTime. So, what we need to model is, that each StatusUpdate in addition is identified by the of the that posted. And then, of course, needless to say DateAndTime need to have a fine enough granularity.
StatusUpdate cannot
- exist without RegularUser
- be identified without RegularUser
(Email, DateAndTime) identifies
- StatusUpdate
On the other hand, since it is not possible to identify a StatusUpdate just by DateAndTime, every single StatusUpdate, that is an instance of StatusUpdate, must be hooked up to a RegularUser, so that we have the Email available. StatusUpdate cannot exist without being hooked to a RegularUser.
That is, why we call that entity type weak, and this is an identifying relationship type. Therefore DateAndTime is calle a Partial Identifier.
Recursive Relationship Types
It is calles recursive, because it relates an entity type to itself.
The AdminUser with properties Email and SinceDate. SinceDate identifying when the AdminUser became the Supervisor for another AdminUser, who is the Supervisee.
1) There is a direction to the lines of the relationship
2) We added roles to the relationship sides
We do that, because when we have one instance of an AdminUser, we need to know whether that AdminUser plays the role of Superviosr or Supervisee in the Manages-relationship.
Supertypes an Subtypes
➡️Union Entity Types
This relationship type shows a relation between Employer and RegularUser. The Employer may be one of 2 different kinds of entities. It could either be a Company or a GovAgency. In case the Emplyer is a company, then an attribute EIN#, which is a tags identification number is needed to be attached with the Employer. In case the Employer is a GovtAgency, then we need an AgencyID, which may be a property type consisting of the AgencyName and which Municipality it is in. The Employer type in this case is called a Union Entity Type.
There are two important roles associated with Union Entity Types:
1) Employer is a subset of Company UNION GovtAgency
2) The intersection between Company and GovtAgency is EMPTY. An Employer is either GovtAgency OR Company.
Thing, Relationship, Property
How do we really know, whether something is a thing, relationship or property? How much does that depend on the context in which we perceive them?
Is the EER (Extended-Entity Relationship) model supporting all the fundamental types of abstraction that we would expect?
Why have we not seen any queries? What would the type of the query on the EER model be?
Are Relationships Entities?
… or are they just glue that tie together entities?
This relationship type has a property type: CurrentJobSince. If the relationship type has a property type, isn’t it then an entity type?
If we can accept that relationship types have properties, then fortunately in this 1-N case there is an acceptable solution.
You can think about the values of the properties of the relationship type as values that label the connection between Employer and RegularUser. So, d1 would be the date the CurrentJobSinceDate of the RegularUser in the top.
Since these are hierarchies the labeling of the instances between them, we could simply move the date down to the RegularUser. Of course not move this date to the Employer.
- relationships may have attributes
- for 1-N (and 1-1) relationships, attributes may be moved to the the entity on the “many-side” (either side for 1-1)
❗Another Example | Objectified Relationship Type
A SchoolsAttended needs to be an entity type as opposed to a relationship type. We call that an objectified relationship type. So, SchoolsAttended is now a relationship type. The GPA property type is a property type of entity type SchoolsAttended.
Now, we need to mould the two relationship types that are necessary in order to get the same functionality and cardinality of the mapping between RegularUser and School. The way it is done, is to introduce two 1-many relationships, one from RegularUser to SchoolsAttended and one from School to SchoolsAttended.
Aha, maybe in LineDance, I need to mould two relationship types that are necessary in order to get the same functionality and cardinality of the mapping between RegularUser and School. The way it is done, is to introduce two 1-many relationships, one from RegularUser to SchoolsAttended and one from School to SchoolsAttended.
So, here on the right, we have RegularUser instances, in the middle SchoolsAttended instances. As you can see, a RegularUser maps to multiple SchoolsAttended via the 1-N relationship.
Messing With Your Brain Part 1
Let’s talk about Users and LastName. The question to you is “Which of these are entitiy types and which of these are property types?”
Most of you probably guess that User is an entity type and LastName is a property type.
Makes perfect sense.
Let us look at two similar names:
Andersen | Anderson
The endings both mean “son of”. However, there is a geographical point to these: The name Andersen is a Danish LastName, the LastName Anderson is a Swedish one.
Haraldsdottir. The ending means “daugther of”. These names come from a time, where children were named by the first name of the father follwed by either “son of” or “daughter of”. Does that mean that a son could not be named from his mother?
Helguson is the the name of the mother Helga.
Carpenter, Baker, Smith, these names refer to the occupation.
Kim, Lee, Park, Kang are names from South Korea. They are religious names. Irfan, Sabier are moslem names, Peter and Paul are christian names.
Some names mean something like Leo the Lion. There is also flower names like Iris, Heidi, Erika, Rose.
So, what exactly is in a name?
We got 7,8 different meanings that are all properties.
Do you think the LastName is a property or a term dealing with genealogy?
Messing With Your Brain Part 2
What do you think, the entity type and relationship type are? Well, that would have been my guessing.
But did you ever watch teh movie “Teh Wedding Planner” or “Father of the Bride?”
Relationship Type or Entity Type?
From the wedding planners perspective let’s take a look at what is important about a wedding.
- Wedding dress
- Honeymoon location
- Flower supplier
- Caterer
- Music
- Car Provision
From the wedding planners perspective, these are all properties about the all central entity type of this whole database namely the wedding.
What Can the EER do?
There are 3 kinds of abstraction that people normally agree are important when you try to fix a perception of reality. They are
- classification ✔️
- aggregation ❓
- generalization ✔️
Our ability to define entity types in the EER (Extended Entity Relationship) model clearly is in support of representing classification. The super software (?) is supporting generalization. But aggregation?
Car Graphic
A car is composed of many components. The Drive Train is coposed of the engine, clutch, transmission, driveshaft, differential, axle, shock.
If you look at an instance of a Drive Train, then that instance is composed from instances of the component parts. You will need an instance of an engine, an instance of a clutch, an instance of a transmission shaft … in order to actually compose an instance of a Drive Train.
How Do You Model That in the EER Model?
Unfortunately, the answer is: “You cannot.”
The EER does not explicitely support aggregation.
Which means for LineDance app: I cannot aggregate Achievements into Badges.
You can fudge it and use existing relationship types, etc. But that is like programming in C. It might be a good idea, but it is not supported by any kind of tool.
What Is The Result Type of A Query?
Remember, a data model consists of formalisms to express data structures, constraints, and operations.
EER Diagram
Let us say we want to print a list of RegularUsers and the SchoolsAttended. The information, we would like to come out in the result could be Email, FirstName, LastName, and SchoolName.
What is the type of that query?
It is a list of properties, but it is not a entity type, not a relationship type, not a supersubtype, not a property. There is no type to capture this result. The essence: Since the result does not have a type, there is no way that we can take that result and continue to operate on it with a query language.
So the query, I just asked, does not have a type, and therefore what I used was not a Closed Query Language. Query languages have to be closed. It is the only way we can formulate high-level ideas and ask high-level questions. It is actually the reason we do not run around in loin cloth anymore.
Later on, when we look at relation based, you will see powerful examples of Closed Query Languages.
In my opinion, the lack of a generally agreed upon Closed Query Language for the EER is the reason that database management systems are not based on the EER.
Relational Model Theoretical Foundation V2
The EER (Extended Entity Relationship) model is great for fixing and representing a perception of reality. However, there is no commercial database system that implements the model directly. Almost all the commercial systems implement the Relational Database model.
What I want to do, is to show you how to map EER into relationship diagrams. For you to understand that mapping, I first need to define for you, what a relation is.
Relational Model
Whenever we look at a data model, we know, there are three things we need to look at. We need to look at
- data structures
- constraints
- operations, there are two fundamental operations that can be used to express operations in relational databases, relation on
- Algebra and on
- Calculus
There are two Calculus notations, one is
- tuple calculus (SQL is a calculus language), it is tuples of relations that are variables
- domain calculus (QBE), it is selfs of domains that are the variables
Data Structures
As opposed to the EER model, which had all kinds and notations to capture structures from entity types to property types to relationship types, and super-sub types, etc. There is only one structure in Relational Databases, namely relations.
A DOMAIN is a set of atomic values.
Atomic values, that from thepoint of view of the DBMS has no meaning inside of it. In other words, nothing is coded in the value. Data are just values without meaning that we want to represent. You can think about the set of atomic values as a type.
A relation R is a subset of the set of ordered n-tuples defined here.
This set of ordered n-tuples is constructed as follows: Each element in the tuple, an element di is pulled from the corresponding domain Di. It is absolutely essential to note that a relation is a set.
Big Deal
In this table, there is a relation name, RegularUser, having 5 attribute names (Email, BirthDate, CurrentCity, Hometown, Salary). The domains are defined by varchar(50), datetime, varchar(50), varchar(50), integer. The number of attributes - or columns - is called the degree of the relations. This is degree 5. The number of tuples in the relation is the cardinality. In this case it is also 5.
It is a really, really big deal that because of attribute naming the order of attributes here is actually not important for what the value of the current relation is. Likewise the set of tuples of the relation does not have to be in this order, it could be any order.
So, the value of the relation is independent of attribute order and tuple order.
Constraints
There are consequences in defining Primary Keys
1) Entity integrity
2) Referential integrity
Email is the Primary Key of the User Table
Important consequence:
1) No value of Email is allowed to be NULL value.
2) Whenever something which is a Primary Key somewhere else, when that is used in another table like the table above RegularUser, the following must be obeyed: The set of Email adresses that appear in Table RegularUser must be a subset of the User Table.