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] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 6 [islastshown] => [isfirstshown] => [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] => [avwidth] => [avheight] => [edit_userid] => [edit_username] => [edit_dateline] => [edit_reason] => [hashistory] => [pagetext_html] => [hasimages] => [signatureparsed] => [sighasimages] => [sigpic] => [sigpicdateline] => [sigpicwidth] => [sigpicheight] => [postcount] => 7 [islastshown] => [isfirstshown] => [attachments] => [allattachments] => ) --> [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses - MySQL

[Newby] help on a statement with both GROUP_CONCAT and JOIN clauses - MySQL

'morning everyone! I hope not to violate any rule by posing this question... If so, I'm sorry and please forgive me! I searched Internet and books for a solution with no success, this is why I'm asking you an hint... Let me briefly expose my problem: I'm a software engineer student and I'm working on a demonstrative project to discuss it during my exam... I focused on glossary, one of the doents produced during the developing process of a database. It's the transposition in tabular form of concepts expressed in natural language and gathered during the ysis phase of the ...

  1. #1

    Default [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    'morning everyone!

    I hope not to violate any rule by posing this question... If so, I'm sorry
    and please forgive me! I searched Internet and books for a solution with no
    success, this is why I'm asking you an hint...

    Let me briefly expose my problem: I'm a software engineer student and I'm
    working on a demonstrative project to discuss it during my exam...

    I focused on glossary, one of the doents produced during the developing
    process of a database. It's the transposition in tabular form of concepts
    expressed in natural language and gathered during the ysis phase of the
    project. The glossary table contains entries (names of concepts),
    description (description of concepts), synonims' list (can be blank), list
    of entries linked to the current one (can't be blank). I decided to keep
    things simple so I simply added the concept of project's names to permit the
    handling of multiple glossary.

    I passed by each step of the developing process quite easily, but when i
    finally checked out the correctness of my work I discovered that something
    was wrong... I tryed to fix it but with no success... I'm going to report
    the SQL statements I wrote and the resulting table affected by the problem:




    First of all, I created the data structures:
    ============================================

    CREATE SCHEMA archive;

    USE archive;

    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));



    Afterwards I populated the tables with a few sample data:
    ================================================== =======

    INSERT INTO projects VALUES (
    'example');

    INSERT INTO glossary VALUES (
    'example', 'person', 'example of a typical entity');

    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');

    INSERT INTO linking VALUES (
    'example', 'person', 'name');



    Finally I developed the SQL statement to get the desired table:
    ================================================== =============

    SELECT g.entry, g.description,
    GROUP_CONCAT(s.option SEPARATOR ', ') AS options,
    GROUP_CONCAT(c.target SEPARATOR ', ') AS links
    FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
    ON g.name = l.name AND g.entry =
    WHERE g.name = 'example'
    GROUP BY l.name, l.entry
    UNION
    SELECT g.entry, g.description,
    GROUP_CONCAT(s.option SEPARATOR ', '),
    GROUP_CONCAT(c.target SEPARATOR ', ') AS links
    FROM glossary g NATURAL LEFT JOIN synonims s JOIN linking l
    WHERE g.name = 'example'
    GROUP BY l.name, l.target;



    And this is what the above SQL statement produces:
    ==================================================

    +--------+------------------------------+-------------------+----------------+
    | entry | description | options | links
    |
    +--------+------------------------------+-------------------+----------------+
    | person | example of a typical entity | NULL | name
    |
    | name | example of typical attribute | surname, nickname | person, person
    | (*)
    +--------+------------------------------+-------------------+----------------+

    As you can see, the 'links' field of the record whose entry in 'name'
    contains a double reference to 'person' (*) while I expected a single
    reference...

    Does anyone can help? Thank you all in advance, please let me know,

    Stefano Bragaglia


    Stefano Guest

  2. #2

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Stefano Bragaglia wrote: 

    As it should. You have both surname and nickname in your options, and
    each is a link to a person. And you show two links.

    There is nothing in your setup which indicates surname and nickname must
    belong to the same person.

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

  3. #3

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Good morning Jerry and thank you for your reply!

    I'm obviously doing something wrong, but I cannot find out where the error
    is!

    Can you please suggest me where to look for it? Or where my reasoning is
    leaking something?

    I can provide the description of the problem in natural language, the E/R
    diagram and the relational diagram if it can help...

    Thank you in advance,
    Stefano Bragaglia


    Stefano Guest

  4. #4

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Hello again...

    I just put together this SQL statement that produces the expected output...

    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;

    Can I safely retain the problem solved or do I still miss something?

    Thank you again,
    Stefano


    Stefano Guest

  5. #5

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Stefano Bragaglia wrote: 

    You have a basic problem in your logic.

    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".

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

  6. #6

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Hello Jerry!

    Thank you again for replaying!
     

    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 Guest

  7. #7

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Stefano Bragaglia wrote: 
    >
    >
    > 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
    >
    >[/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
    ==================
    Jerry Guest

  8. #8

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses


    Jerry,

    thank you again for your kindness! I hope not to bother you with my
    questions

    Yes, what I proposed here is an academic exercise... and I hope it will grow
    into a working project to pass the exam...
     

    What do you mean? Referring to the following bit of code, I thought that the
    last line is doing the trick... I mean, doesn't the FOREIGN KEY clause
    actually bind each synonim with the same correct row in table "glossary"?
    If I'm still wrong, can you please show me how to indicate that objects
    comes from the same row?

    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));

    I know I'm looking like the guy who looks at the finger when you point out
    something, but I really don't understand where is my error... Are you
    suggesting me to use a simple unique table to do the whole job? If so,
    yes... it could be easier to manage, but I also have to develop a little
    WAMP solution and I'm planning to handle things (synonims, for example) as
    list of strings and not as a single string to edit...

    Still thank you, forgive my dumbness,
    Stefano Bragaglia


    Stefano Guest

  9. #9

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Stefano Bragaglia wrote: 
    >
    >
    > What do you mean? Referring to the following bit of code, I thought that the
    > last line is doing the trick... I mean, doesn't the FOREIGN KEY clause
    > actually bind each synonim with the same correct row in table "glossary"?
    > If I'm still wrong, can you please show me how to indicate that objects
    > comes from the same row?
    >
    > 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));
    >
    > I know I'm looking like the guy who looks at the finger when you point out
    > something, but I really don't understand where is my error... Are you
    > suggesting me to use a simple unique table to do the whole job? If so,
    > yes... it could be easier to manage, but I also have to develop a little
    > WAMP solution and I'm planning to handle things (synonims, for example) as
    > list of strings and not as a single string to edit...
    >
    > Still thank you, forgive my dumbness,
    > Stefano Bragaglia
    >
    >[/ref]

    In your SELECT statement you are. But there is NOTHING in your design
    which does it. That's the problem.

    These two rows:

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

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

    Are independent rows. There is nothing to tie 'surname' and 'nickname'
    to the same row. They can easily point to two different rows.

    It's the difference between database design and the data itself. And
    it's why, in your first try, you got two 'person' entries returned - one
    for surname and one for nickname. The result were correct - you were
    referring to two 'person' entries.

    Your problem is that your 'synonims' table 'options' field contains two
    different types of data - a 'name' and a 'surname'.

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

  10. #10

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Jerry,

    I give up! I understand I did a mistake but I have no clue on how to solve
    it...

    I just checked my notes and my books but I didn't find anything that
    helps... I think I need an example or I will never pass over this little
    accident...

    Can you please point me out a resource where I can learn how to handle this
    thing or can you show me how you would solve this particular problem? I
    would really appreciate if you can...

    Thank you for your patience,
    Stefano


    Stefano Guest

  11. #11

    Default Re: [Newby] help on a statement with both GROUP_CONCAT and JOIN clauses

    Stefano Bragaglia wrote: 

    Stefano,

    Sorry, I don't have any good references for you. However, you need to
    change your design. Surname and nickname should probably be part of the
    same table.

    The trouble is that surname and nickname are two different items, yet
    you're putting them in the same column.

    Again - this looks more like an academic exercise than a real-life
    example. Is it?

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

Similar Threads

  1. Comma Seperation List and Join Statement
    By Flashm@n in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 13th, 04:19 PM
  2. Problems with CFQUERY and MySQL GROUP_CONCAT
    By anthony@webpex.com in forum Macromedia ColdFusion
    Replies: 0
    Last Post: March 2nd, 10:09 PM
  3. SQL Join Statement help
    By Cary in forum ASP Database
    Replies: 3
    Last Post: August 26th, 04:08 PM
  4. Need help with SQL statement for JOIN-type stuff
    By Mike in forum ASP Database
    Replies: 13
    Last Post: August 16th, 10:36 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •