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.lectu rers 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,lec turers, 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 1 4224
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 "assumption s" 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,lec turers, 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 participatio n 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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Tim Smith |
last post by:
Dear All,
Silly question, but I am having trouble understanding the diagram on the
inside back cover entitled "Design Pattern Relationships." It shows the
relationships between all of the design patterns. Unfortunately, the book
doesn't exactly explain what it all means. Although it looks very pretty -
would anyone give me some background on its usefulness? For example is it
just illustrating how different patterns come together to...
|
by: marane moll |
last post by:
Hello
My Access Diagram wizard is buggy and reinstallation of Access (2000)
doesn't help. I guess it is buggy because of a not registered ActiveX
Control. But I don't know which Control the Diagram Wizard uses.
Does anybody know the name of the Control used by the Diagram Wizard?
Thank you
Marane
|
by: PIEBALD |
last post by:
I'm trying to find a general definition/specification for railroad diagrams.
All I find online are explanations by authors who explain what _their_
particular format uses, but they leave me wondering if they are using only a
subset of the possible structures. And there are a few that seem to use
rather unique structures.
Is there a standard? Does each author create his own? There must be a set of
dos and don'ts somewhere!
|
by: Terry |
last post by:
I have added some tables to the MS Access relationship diagram window
(2003) and added some relationships. But after a while when I reopen
the diagram some of the tables are hidden above the top of thewindow
(the relationship lines appear to go way up beyond the upper scroll
limit.
How can I sort them out with out redoing thediagram?
|
by: sangu_rao |
last post by:
Hi,
I have to prepare an ER diagram for the objects in my SQL Server
database. I have used the option "DIAGRAMS" in EnterPrise Manager of
SQL Server 2000. It is creating the diagram for the selected tables
(but the diagram contains only the table which i have selected. It is
not displaying its depended tables). But i am not able to export it to
any of the flat file like MS WORD or paint. I want this diagram to be
uploaded to one Flat file...
| |
by: Anson.Stuggart |
last post by:
I'm designing a debounce filter using Finite State Machine. The FSM
behavior is it follows the inital input bit and thinks that's real
output until it receives 3 consecutive same bits and it changes output
to that 3 consecutive bit until next 3 consecutive bits are received.
A reset will set the FSM to output 1s until it receives the correct
input and ouput.
This is the test sequence with input and correct output.
1 0 0 1 0 1 0 0 0 1...
|
by: Oskar Bennet |
last post by:
Hi everybody,
I am supposed to draw a simple UML diagram for a very small project that
consists of less than a dozen classes. I have never been working with
UML before, I have read some tutorials but I cannot find the connection
between UML and my C++ code.
What I have found out so far is that inheritance is indicated by an
arrow like this
|\
|
by: Doug |
last post by:
Hi all,
A workmate was recently bitching to me about an RFC.
(Apologies - the RFC number eludes me at present (it's related to the
DIAMETER protocol, that's all I can remember) but I will try to find
out and update the thread.)
He was moaning how the RFC laid out the description of an 8-bit
field. According to him, the diagram was (fixed-width font required,
|
by: viepia |
last post by:
Hi,
My project writes records to a new SQL Server 2005 database with
currently 18 tables. When I change the Database Diagram for my
database I save the copy the list of changed tables to a notepad text
file; then one at a time I delete the changed tables from my DBML
Diagram and drag a replacement from the Server Explorer Table list. Is
there a better way to keep the Database Diagram and DBML Diagram in
sync? I would rather not...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |