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