Re: The ER diagram

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)