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 [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Stefano [ip] => sbragaglia.nosp [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 5 [attachments] => [allattachments] => ) -->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 > >[/ref] 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. net ================== [allowsmilie] => 1 [showsignature] => 0 [ipaddress] => [iconid] => 0 [visible] => 1 [attach] => 0 [infraction] => 0 [reportthreadid] => 0 [isusenetpost] => 1 [msgid] => [ref] => [htmlstate] => on_nl2br [postusername] => Jerry [ip] => jstucklex@attgl [isdeleted] => 0 [usergroupid] => [membergroupids] => [displaygroupid] => [password] => [passworddate] => [email] => [styleid] => [parentemail] => [homepage] => [icq] => [aim] => [yahoo] => [msn] => [skype] => [showvbcode] => [showbirthday] => [usertitle] => [customtitle] => [joindate] => [daysprune] => [lastvisit] => [lastactivity] => [lastpost] => [lastpostid] => [posts] => [reputation] => [reputationlevelid] => [timezoneoffset] => [pmpopup] => [avatarid] => [avatarrevision] => [profilepicrevision] => [sigpicrevision] => [options] => [akvbghsfs_optionsfield] => [birthday] => [birthday_search] => [maxposts] => [startofweek] => [referrerid] => [languageid] => [emailstamp] => [threadedmode] => [autosubscribe] => [pmtotal] => [pmunread] => [salt] => [ipoints] => [infractions] => [warnings] => [infractiongroupids] => [infractiongroupid] => [adminoptions] => [profilevisits] => [friendcount] => [friendreqcount] => [vmunreadcount] => [vmmoderatedcount] => [socgroupinvitecount] => [socgroupreqcount] => [pcunreadcount] => [pcmoderatedcount] => [gmmoderatedcount] => [assetposthash] => [fbuserid] => [fbjoindate] => [fbname] => [logintype] => [fbaccesstoken] => [newrepcount] => [vbseo_likes_in] => [vbseo_likes_out] => [vbseo_likes_unread] => [temp] => [field1] => [field2] => [field3] => [field4] => [field5] => [subfolders] => [pmfolders] => [buddylist] => [ignorelist] => [signature] => [searchprefs] => [rank] => [icontitle] => [iconpath] => [avatarpath] => [hascustomavatar] => 0 [avatardateline] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 6 [attachments] => [allattachments] => ) -->