Stefano Bragaglia wrote:
> Hello Jerry!
>
> Thank you again for replaying!
>
>
>>You are saying the "name" has the option "surname", which is linked to
>>"person". That's OK.
>>
>>You're also saying that "name" has the option "nickname", which is linked
>>to "person". That in itself is OK.
>>
>>But your problem is that, the way you have it, "surname" and "nickname"
>>could be from two different persons. And I don't think you want the
>>surname from "John Doe" and the nickname from "Chi Chi Cordova".
>
>
> If this is what comes out from my work, then it's simply wrong!
> What I wanted to express is the following (sorry if it will take a little
> long to explain things in details):
>
> Everytime I develop a project, I have to compile a glossary. Each
> project is unambiguously determined by its name.
> Each glossary is a list of entries. Each entry is an idea discussed into
> the project and is composed by:
> - a name: a string that univocally stands for the entire entry (note:
> different glossaries can have entries with the same name);
> - a description: a long text detailing everything about the entry;
> - a list of synonims: the strings in the list are other names with whom
> the entry can be known (note: the list can be blank);
> - a list of terms: these terms are other terms of the same project with
> whom current term is binded (note: this list can't be blank because every
> entry must be
> part of the project; in other word if a term is not binded with the
> rest of its project then probably it's not part of the project).
>
> Let's express, for example, the base E/R Diagrams concepts with the above
> system:
> The project's name can be "E/R Diagrams" and the entries can be "entity",
> "relationship", "attribute" and "class".
> Entry "entity" can be expressed by: "entity", "a discrete object, etc.",
> "table", "attribute, class, relationship".
> On the other hand, entry "relationship" can be expressed by: "relationship",
> "express how two entities are associated, etc.", NULL, "attribute, entity".
> Again,for entry "attribute" we have: "attribute", "typical trait of
> relationships or entities, etc.", "field, column", "attribute,
> relationship".
> And finally, for entry "class": "class", "parenthood between entities,
> etc.", NULL, "class".
>
> Then I translated the above description into the following diagram:
>
> has
> +-----------+ + +-----------+
> Name *---| | (1,1) / \ (1,1) | |
> | Projects +-------+ +-------+ Glossary |
> | | \ / | |
> +-----------+ + +-----+-----+
> (0,n) |
> +
> / \
> contains + +
> \ /
> +
> (1,1) |
> +-----+-----+
> | |---* Name
> | Entry |---o Description
> | |---o Synonims (0,n)
> +-+-------+-+
> | + |
> (1,n) | / \ | (1,n)
> +-+ +-+
> \ /
> +
> linked
>
> After a few refining step, I started to translate it into logical schema:
>
> +-----------+
> Name *---| |
> | Projects |
> | |
> +-----------+
>
>
> *
> +-----------+ |
> Description o---| |-+-o Name (Projects.Name)
> | Glossary |-+-o Entry
> | | |
> +-----------+
>
>
> *
> +-----------+ |
> | |-+-o Name (Glossary.Name)
> | Synonims |-+-o Entry (Glossary.Entry)
> | |-+-o Option
> +-----------+ |
>
>
> *
> +-----------+ |
> | |-+-o Name (Glossary.Name)
> | Linking |-+-o Entry (Glossary.Entry)
> | |-+-o Target (Glossary.Target)
> +-----------+ |
>
> The last diagram is best expressed at logical level by the following:
> Projects (NAME)
> Glossary ([NAME], ENTRY, description)
> Synonims ([NAME], [ENTRY], OPTION)
> Linking ([NAME], [ENTRY], [TARGET-->ENTRY])
>
> These table are 1NF, 2NF, 3NF and BCNF, so can be safely translated into
> physical level by the following SQL:
>
> CREATE SCHEMA glossaries;
> USE glossaries;
> CREATE TABLE projects (
> name VARCHAR(50) NOT NULL,
> PRIMARY KEY (name));
> CREATE TABLE glossary (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> decription TEXT NOT NULL,
> PRIMARY KEY (name, entry),
> FOREIGN KEY (name) REFERENCES projects (name));
> CREATE TABLE synonims (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> option VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, option),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry));
> CREATE TABLE linking (
> name VARCHAR(50) NOT NULL,
> entry VARCHAR(50) NOT NULL,
> target VARCHAR(50) NOT NULL,
> PRIMARY KEY (name, entry, target),
> FOREIGN KEY (name, entry) REFERENCES glossary (name, entry),
> FOREIGN KEY (name, target) REFERENCES glossary (name, entry));
>
> If this is not correct, where lies the error?
> I tryed to carefully follow the suggestions of my teacher... I presumed that
> I developed the whole process correctly...
> I probably misunderstood somenthing, but what? and where?
>
> In the meanwhile, I also produced the following SQL query that seems to work
> correctly (as I already posted):
>
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(DISTINCT l.target SEPARATOR ', ') AS linking
> FROM glossary g NATURAL LEFT JOIN synonims s NATURAL JOIN linking l
> GROUP BY g.name, g.entry
> UNION
> SELECT g.entry, g.description,
> GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
> GROUP_CONCAT(DISTINCT l.entry SEPARATOR ', ') AS linking
> FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
> ON l.name = g.name AND l.target = g.entry
> WHERE g.name = 'example'
> GROUP BY g.name, g.target
> ORDER BY entry;
>
> Now I'm asking myself: this work is correct or I'm obtaining the expected
> output by pure chance?
>
> Thank you in advance for reading and sorry again for the long post!
>
> Stefano Bragaglia
>
>


Stefano,

Again, the problem is that you 'name' and 'nickname' could be two
different people. You need to change your logic so that they both
indicate the same person.

The problem is not necessarily with the way the tables are set up. It's
with your data entries.

You do:

INSERT INTO glossary VALUES (
'example', 'name', 'example of a typical attribute');

INSERT INTO synonims VALUES (
'example', 'name', 'surname');

INSERT INTO synonims VALUES (
'example', 'name', 'nickname');

OK, glossary contains a name. And synonims (sic) contains synonyms of
name being surname and nickname.

But no place have you indicated that these two come from the same row.

Actually, this looks very much like an academic exercise, not something
to be used in the real world. Is it?

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================