[Friday, Mar 10, 18:25]

>   Our group has a few questions with this next homework and translating to
> the relational model.
>
>   a)  With the specialization entity to either a correct entity or incorrect
> entity -- If we are using method #1 which would allow for the boolean
> attribute is_correct, can we just use a natural language constraint to
> identify that it would cover the is_incorrect if the boolean value is null?
> That way we would eliminate the redundancy of having to put both subclasses
> into the relation of Answers( ....)

In this case the subclasses are total and disjoint (which was also missing in
the distributed diagram, there should be t,d written near the triangle).
Then the variant of Method 1 mentioned on Slide 8-41 can be used, where one
attribute is sufficient for all subclasses (in that case "Job" instead of
is_Pilot, is_Technician, is_Clerk, etc.).

A constraint limits the possible database states by excluding states which do
not fulfill some condition. If you have only one attribute in the schema,
you cannot refer to the other attribute in a constraint.

The right construct to use were a derived attribute or a view.
You state how this attribute can be defined in terms of the stored ones,
i.e. that is_incorrect is the negation of is_correct.

However, for me it is ok if you use the one attribute according to this
variant. Maybe you should mention the two possible values and their meaning
if it is not obvious.

>   b)  For the relation "checked", we need to create it as a  relation since
> it is a Many to Many relationship.   However, since it is to a weak entity
> of a weak entity, must we make not just the keys of student and answer as
> the keys for this "checked" relation, but also include the parent keys of
> Question and also Test?

Yes, the real key of Answer consists not only of the explicitly shown ANo,
but also of the key attributes inherited from the parent entity Question.
The real key of Question consists not only of the explicitly shown QNo
but also of the key attribute inherited from its parent entity Test.

It is not quite correct to refer to ANo as the key of Answer.
It is only a partial key.

>    e.g. checked(SSNo -> students, ANo-> answers, QNo ->question, ID -> test)

This is not correct. Remember that a foreign key must consist of as many
attributes as the referenced primary key.

> (Would QNo and ID also need to be underlined along with SSNo and ANo as
> primary Keys?)

I won't answer this one.
In general, if you translate a many-to-many relationship,
you must underline the keys of both entity types.
You must find out what the key of Answer is here.

>   c)  Do we need to try to include as in your Flight example from class,
>       the multiple keys?
>       we were a bit confused with the reason all are in one parenthesis.

A foreign key can consist of multiple attributes.
It has to consist of that many attributes as the referenced primary key.
The notation should have only one -> per foreign key.
The parentheses are need to make clear which attributes together form a
foreign key.

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

[Tuesday, Mar 14, 12:30]

> I am having trouble understanding how to represent the specialized entity
> for Homework #6. Here is what I have so far, the brackets [] represent a
> key which would normally be underlined :
>
> ...
> Chapter([no],headline,Contains->Test)

Please check the translation of the relationship contains.
(There might be other errors, I did not check it completely).

> Answer([id]->Test,[qno]->Question,[ano],text)

Please remember that foreign keys must match the key attributes of the
referenced relation. E.g. if the key of Question consists of two attributes,
then the foreign key referencing it must also consist of two attributes.
There can be a question number 1 in many tests, but the foreign key is supposed
to refer to a unique row in the Question table.

> And here is what I have for the specialized entities:
>
> Correct([id]->Test,[qno]->Question,[ano],text)
> Incorrect([id]->Test,[qno]->Question,[ano],text)

(Please check again the foreign keys.)

This is not one of the method mentioned in the handouts.
Basically, it mixes Method 2 and 3.
The problem is that you already have the Answer table containing the
attribute "text".
For Method 2, you would not have the Answer table
(i.e. the superclass doesn't have its own table, all information is in the
tables for the subclasses).
You can't use Method 2 here because you have a relationship defined on the
superclass.
For Method 3, you have tables for superclass and subclasses, but the tables
for the subclasses contain only the key of the superclass plus the attributes
defined only on that subclass.
So you would not repeat the attribute "text" in the tables "Correct" and
"Incorrect".

Method 3 would work here, but it is very inefficient.
You need a join to find out which answers are correct and incorrect,
and you waste storage by repeating the key attributes.
Method 1 is the best here, in particular the variant explained on Slide 8-41.