The ER diagram

[Maria]

Heya,

I am doing some background reading about the database and i am a
little bit confused, i would appreciated any help....
Assume been asked to draw the ER diagram for the following statement:
"Many players play for a team but a player can play just in one team"
The author of the reference that i do have, suggests the following ER
diagram:

/ \
/ \
+-----------+ N / \ 1 +-----------+
|Player |--------/playsfor--------|Team |
+-----------+ \ / +-----------+
\ /
\ /
\ /

I have some concerns about the participation constraints that might be
missing in the above diagram. Personally, i was expecting a
*mandatory* participation for both of the entities. i am just assuming
that if a participation is not specified as in the above diagram, it
has to be considered as optional.
Is that right?

As i have said above i was expecting the participation of both
entities to be mandatory since normally for a team to exist, it must
have a number of players, and a player to (exist), (s)he should be
associated with a team. This seems not the intepretation taken by the
author...
After an informal discusssion with peers, it has been suggested that
NO assumptions should be made unless it is specified clearly and
precisely in the problem description. To support this claim, in the
above example with optional type participation, the database can have
for instance a table for many players and a record for a team. some
players for instance are recorded but are not yet playing for the team
at the present. in parallel, we can have a record of a team with no
associated players yet!

Consequently, i just concluded that when drawing ER diagram no
assumption should be made and i have just to map the statement
requirements accurately with no further personal interpretation. In
other word to make an entity particpation mandatory, the word "must"
should be imperatively included in the statement

Any comment?

however i got confused later while checking the ER diagram suggested
as a solution for the following problem

Problem :
---------
The university database maintains records of its departments,
lecturers, course modules. The requirements are as follows:

The university consists of departments. Each department has a unique
name. A department must also have a number of lecturers.lecturers must
teach one or more modules. A lecturer can only belong to one
department. Modules are offered by departments and taught by
lecturers.
Solution
--------
/ \
Name / \
+-----------+ 1 / \ M +---------+
|Department |--------/ Has \--------|Lecturer |
+-----------+ \ / +---------+
| Mandatory \ / Mandatory |
1| \ / | Mandatory
| \ / |
| /\ 1
/\ / \
/ \ / \
/ \ / \
/Offers\ /teaches\
\ / \ /
\ / \ /
\ / \ /
| \ /
| \/
| | Mandatory
| |
| | M
| |
| N +-----------+
--------------------------------|Module |
+-----------+
Mandatory
In the following, i'll go through the above statement line by line.
my conclusion is preceded by the symbol ::

- The university database maintains records of its departments,
lecturers, course modules.

:: entity types are: departments,lecturers, course modules
- The requirements are as follows: The university consists of
departments. Each department has a unique name.

:: the dept entity has a name attribute

- A department must also have a number of lecturers.

:: one to many relationship between Dept and lecturers. Mandatory
participation for department entity type (must word)

- Lecturers must teach one or more modules.

:: one to many relationship between Lecturer entity and Module entity,
where lecturer participation is mandatory, labelled by + in the above
relationship
- A lecturer can only belong to one department.

:: one to many relationship between lecturer and dept
- Modules are offered by departments and taught by lecturers.

:: should we really have to model the "offers" relationship as shown
above in the diagram?

if we consider only the above statement and the associated ER diagram,
my concerns are:

from where come the 1:N relationship between departement and module?
And why the relationship is optional for the department entity?
As explained in the first part of my message, i don't want to make any
assumption which is not clearly included in the statement. of course
i do expect that a dept offers more than one module, actually i do
expect that each dept MUST offer more than one module, thus the dept
particpation in this relationship is mandatory and not optional as
shown in the above diagram. In parallel, a module can in some
universities offered by more than one dept, i.e joint module, thus the
relationship between dept and module should be N:M relationship
instead of 1:N with mandatory participation for both of the entity
instances.

..
As already explained above, considering solely the above statement, do
we have really to model the relationship (offers) between dept and
modules? for instance, in another variant of the above example, the
university said to have a library which keep track of the papers
published by their members. however, in the modelling, only papers and
lecturers were included in ER diagram and not the library entity.
someone can expect to have a "have" relationship between dept and
library entities

any assistance please?

Thanks

[Hugo Kornelis]

On 20 Jan 2005 12:21:33 -0800, Maria wrote:

(snip)

I have some concerns about the participation constraints that might be
missing in the above diagram. Personally, i was expecting a
*mandatory* participation for both of the entities. i am just assuming
that if a participation is not specified as in the above diagram, it
has to be considered as optional.
Is that right?
Hi Maria,

No, that's wrong.

(Trust me, I'll get more verbose later)
(snip)Consequently, i just concluded that when drawing ER diagram no
assumption should be made and i have just to map the statement
requirements accurately with no further personal interpretation. In
other word to make an entity particpation mandatory, the word "must"
should be imperatively included in the statement

Any comment?
I agree with your conclusion. However, the real world is neither black nor
white. In the real world, if the requirements are not entirely clear, you
ask.

In the real world, you also make use of your knowledge of your customer's
business. Ask a used-car salesman if one customer is allowed to buy more
than one car and you're fired before you can even begin drawing the ER
diagram. But using your knowledge is often a judgement call, as the
dividing line between knowledge and assumptions can be thin.
If you have 30 years experience in modeling banking databases, you are
probably NOT the right person to model the database for a ground breaking
revolutionary new approach to banking - you might be so stuck in the
familiar patterns that your "knowledge" are actually assumptions - and
they might even be contrary to the innovative plans.
Even the safest assumptions can be wrong in certain circumstances. You'd
probably agree that it's always safe to assume that "sex" will always be a
single-valued attribute of the entity "person", right? Wrong! The database
for a gender clinic will include people that are physically male but
living as a female, people that are physically female but legally male, or
even people that are physically somewhere halfway between male and female.
In this (admittedly very rare) case, a person's sex should actually be
modeled as a relationship. Let's hope you verified your "knowlegde" were
not actually "assumptions" before building the database!

Enough of the real world. You are currently reading a text book, and you
can't ask a text book to clarify if the requirements are unclear, nor can
you ask to verify your assumptions. So you'll have to unlearn assuming. Or
to put it another way: learn to always assume the least restrictive
option, unless restrictions are explicitly called for.
(snip)In the following, i'll go through the above statement line by line.
my conclusion is preceded by the symbol ::

- The university database maintains records of its departments,
lecturers, course modules.

:: entity types are: departments,lecturers, course modules
Agree.
- The requirements are as follows: The university consists of
departments. Each department has a unique name.

:: the dept entity has a name attribute
And the name attribute qualifies as candidate key. Important information
that you should not leave out of your conclusions.
It's a shame that the text doesn't include any details on the other
attributes of any of the entities. You're missing lots of essential
information and you could never build a system off these specifications!
- A department must also have a number of lecturers.

:: one to many relationship between Dept and lecturers. Mandatory
participation for department entity type (must word)
Agree with the one to many relationship, but you're concluding it too
soon. At this point in the text, it can still be a many to many
relationship.
Agree with the mandatory participation.
- Lecturers must teach one or more modules.

:: one to many relationship between Lecturer entity and Module entity,
where lecturer participation is mandatory, labelled by + in the above
relationship
This relationship can also still be a many to many relationship. There is
nothing in the line above that warrants the conclusion that a module can't
be taught by more than one teacher.
The "must" and "one or more" are quite specific, so you are right about
the mandatory participation for lecturer.
- A lecturer can only belong to one department.

:: one to many relationship between lecturer and dept
This is indeed the bit of information you need to confirm that the
relationship between lecturer and department is one to many, not many to
many.
The advanced nitpickers can have their field day with this sentence: do
the words "can only belong to" denote a maximum only, or do they denote
both a maximum and a minimum? Correct interpretation of these words
requires a thorough understanding and knowledge of the language. And
though my English is fairly good, it's not my native language, so I don't
think I can make any authorative statements here. However, my gut feeling
says that this is only a maximum and that this sentence does NOT imply
that participation of lecturer in the relationship is mandatory. Your
mileage may vary.

- Modules are offered by departments and taught by lecturers.

:: should we really have to model the "offers" relationship as shown
above in the diagram?
Yes, we should. The modules are apparently not offered by the university
as a whole, but by the specific departments. If somebody calls the
university and has questions about a module, the phone operator should
know which department (or departments) offers that module, so the call can
get transferred to the correct department.

There's very little information about the cardinality of the "offers"
relationship. It might be one to one, one to many, many to one or many to
many - the sentence above is correct in all these cases. It is also not
clear if each department MUST offer any modules. The only thing we can
say, is that each module MUST be offered by a (at least one) department;
otherwise, the words "Modules ARE offered" should have been "Modules MAY
BE offered" (or CAN BE, or whatever).

The same goes for the "teaches" relationship. We already knew that each
lecturer teaches at least one but possibly more modules; from the
information above we can now also conclude that each module is teached by
at least one author. The relationship can still be one to many or many to
many, though.

Instead of the solution in your text book, I have now arrived at the
following solution:

/ \
Name (cand.key) / \
+-----------+ 1 / \ M +---------+
|Department |--------/ Has \--------|Lecturer |
+-----------+ \ / +---------+
| Mandatory \ / |
N | \ / | Mandatory
| \ / |
| /\ N
/\ / \
/ \ / \
/ \ / \
/Offers\ /teaches \
\ / \ /
\ / \ /
\ / \ /
| \ /
| \/
| | Mandatory
| |
| | M
| |
| M +-----------+
--------------------------------|Module |
+-----------+
Mandatory

In my opinion, the author of your book made three assumptions that are not
warranted by the text: the one to many instead of many to many for the
Offers relationship, the one to many instead of many to many for the
teaches relationship and the mandatory participation of Lecturer in the
Has relationship (but see the gotcha above).

if we consider only the above statement and the associated ER diagram,
my concerns are:

from where come the 1:N relationship between departement and module?
Explained above.
And why the relationship is optional for the department entity?
Because there is nothing in the text to indicate that each department MUST
offer any modules.

As explained in the first part of my message, i don't want to make any
assumption which is not clearly included in the statement. of course
i do expect that a dept offers more than one module,
So do I, but the fact that both you and I expect it doesn't change the
clear fact that this is an assumption. Don't use it when you are modeling.

Yes, my model will also allow a department to offer more than one module,
NOT because I assumed they would, but because nothing in the text indicate
that they can't.
actually i do
expect that each dept MUST offer more than one module, thus the dept
particpation in this relationship is mandatory and not optional as
shown in the above diagram.
Here, your assumptions results in a wrong conclusion. The text does not
indicate that a department MUST offer a module, so you should not include
such a constraint.
You may deem it logical that each department should always offer modules,
but others might have different expectations. This particular university
might have departments that are still developing their first modele. Or
departments that will be discontinued, still in the administration until
the last students have finished the last module, but not offering any new
modules. Or the university might have research-only departments.
But even without these examples, the conclusion is simple: the text
doesn't indicate this constraint, so it should not be included.
In parallel, a module can in some
universities offered by more than one dept, i.e joint module, thus the
relationship between dept and module should be N:M relationship
instead of 1:N with mandatory participation for both of the entity
instances.
I agree with the conclusion, but (again) not with the reasoning. That it
can be done in some universities is irrelevant - what counts os that
nothing in the text indicates it isn't done by this particular university.

I've given my students text about relationships between circles, squares
and triangles. I've given them texts with words I made up myself (like
"each grumbah will fnargoosh one or more quirckles"). And I've given them
text about familiar things in the real world, but with some requirements
changed to the unexpected. All with just one purpose: to teach them to
NEVER ASSUME ANYTHING!!!!!

As already explained above, considering solely the above statement, do
we have really to model the relationship (offers) between dept and
modules? for instance, in another variant of the above example, the
university said to have a library which keep track of the papers
published by their members. however, in the modelling, only papers and
lecturers were included in ER diagram and not the library entity.
someone can expect to have a "have" relationship between dept and
library entities

Are you familiar with the term "Universe of discourse" (UoD for short)?
The universe of discourse is the subject of the database or model: the
part of the "world" under discussion. In this UoD, there is only one
university, so there is no need to model the the departments are part of
this university - departments that are not part of this university are not
part of this UoD. The same argument goes for the library: since the UoD
has only one library and all papers are assumed to be in (or registered
by) that library, there's no need to model the library as an entity, nor a
need to model the fact that a paper is in the collection of that library.

However, the UoD has (or rather: might have) more than one department; it
is therefor relevant to model which of the department offers a specific
module.

That's it for now. Did I already mention that I'd get more verbose? :-)

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)