The ER diagram

From: Maria <mariabelliti_at_hotmail.com>
Date: 20 Jan 2005 12:21:33 -0800
Message-ID: <44c5795c.0501201221.277a8146_at_posting.google.com>

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 Received on Thu Jan 20 2005 - 21:21:33 CET