[Tuesday, Feb 1, 13:35]

> I've been working on hw2 w/ ... and both of us
> have a question on the natural language portion of the
> assignment.  We feel that the natural language
> constraints are constraints that cannot be adequately
> identified through the ER model.  Our question deals
> with how the natural language constraint should be
> experessed.  Should they be expressed in english or in
> a mathematical expression (i.e. relational algebra)?

Yes, Homework 2 asks for one constraint which cannot be expressed
via the standard ER-constructs. So this contraint must be something else
than a key constraint or a cardinality constraint.

We didn't have relational algebra yet, so you can write it as normal
English text. E.g.  "The year of birth of a composer must be before the year
when the composer died." If you would like it more formal, you can say
"For every composer C: C.Year_Died > C.Year_Born".
But this is not required, the first sentence also would get full points,
and we didn't had such a logical notation in class.

But please make sure that it is really a constraint, i.e. a condition which
restricts the database states. E.g. "A river can flow through more than one
country" is not a constraint, it only explains a cardinality constraint
by saying that you cannot require a more restrictive constraint.
I.e. it says which database states are possible, whereas a constraint says
which database states are impossible.

One area where constraints may be needed is the representation of the capital
city. I know several possibilities for this, but each needs a constraint.
This means that there is no really good solution (really good solutions use
only the constructs of ER-diagrams, not additional constrints).
But maybe one of the students develops a solution which I didn't know yet.
There are also other possibilities for constraints in this homework,
and it suffices to mention only one constraint.

-------------------------------------------------------------------------------

[Tuesday, Feb 1, 13:45]

> is this an example of a constraint for the homework?
>
> "for every city-state entity, we must ensure that the state is valid for the
> related country.  so, although piitsburgh-pa is unique (as compared to
> pittsburgh-ny), it is not valid if the country to which it is associated,
> does not contain the state - for example, canada"
>
> is this what you're asking for, or have we missed the mark?

I didn't thought that the states should be represented as entities themselves.
Of course, you can do that, it is certainly no error.
Often the text of the exercise allows several interpretations.
If you notice that there are several interpretations, you would go back to
the customer (me) and ask him, but often you don't notice it.

But if you represent states as entities, there would be only a relationship
from cities to states (actually, city would be a weak entity with state as
parent/owner), and a relationship from states to countries.
A direct relationship from cities to countries would then be redundant
("shortcut").

Of course, if you put redundant data into the schema, you need at least a
constraint, so this might be a solution. But it is better not to put redundant
data into the schema in the first place.

My other email gives ideas for possible constraints (capital city).
Also check the population.