Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2006
    Posts
    3

    Unanswered: i am not sure y a third table is needed (was "Little Help!!")

    Hey guys i wasn't sure were to post this but i need help with creating this database, i am not sure y a third table is needed and what artributes should it have and how it is gona link to the other entities in the relational (DB)

    And this is the picture of the question:
    http://img113.imageshack.us/img113/4403/pib9qh.jpg


    This is the other tables.
    http://img229.imageshack.us/img229/8677/tables1xv.jpg

    Thanks in advance
    P.S the data cannot be redundant.

  2. #2
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    It's difficult for me to help because I don't want to give you an answer (since this is your assignment not mine) and also I'm not your tutor so I don't really know what he's trying to assess you on or what your learning has been.

    You've related Artist and Paintings using ArtistID and PaintingID. ArtistID is a unique reference given to an individual artist. So Peta might be AR001 for example. PaintingID will be a unique reference given to each painting. So Green Poles might be 123 for example. So how does the ArtistID relate to the PaintingID - the ID's are entirely different so you can't use them as the relationship. You're on the right lines but just do a quick read up on how relationships work.

    Once you've sorted that out, consider what you would do if these three students decided to create another painting (or maybe just two of the students create another painting). How would you manage that? (I'm assuming this is the essence of the question but I might be wrong).

    hth
    Chris

  3. #3
    Join Date
    Mar 2006
    Posts
    3
    But i dont get how i can make a 3rd table and relate green poles to three students. becuase the question shows that 3 ppl worked on the same painting and i have to show how one painting relates to those 3 students in the table.

  4. #4
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Think about the type of relationship you are trying to model between the artist entity and the painting entity (have you done entity relationship modelling?). Can a painting be produced by more than one artist ? (clearly yes). Is an artist allowed to produce more than one painting ? (i can't tell the answer to this from your data but maybe you can) Once you understand the relationship type then it will give you a big steer about how to implement it. You may have to resolve the relationship first.

    If you're really stuck you should really speak to your tutor.

    hth
    Chris

  5. #5
    Join Date
    Mar 2006
    Posts
    3
    Is it possible to relate Artist ID with 'Title of Painting'?

  6. #6
    Join Date
    Jul 2004
    Location
    Southampton, UK
    Posts
    368
    Quote Originally Posted by kopy
    Is it possible to relate Artist ID with 'Title of Painting'?
    At the moment you have two tables that might look something like this:

    The table of artists:
    ArtistID____Fname____Lname…..
    A1_________John______Smith….
    A2_________Anne______Brown……
    A3_________Mike______Adams…..
    etc

    The table of Paintings:
    PaintingID___Title of Painting____Type______Frame____Price
    P1_________Pink Fountains______Oil________Wood___300.00
    P2_________Royal Gardens_____Water______none____200.00
    P3_________Blue Seas__________Oil_______steel_____150.00
    Etc

    ID’s can be numeric but I’ve deliberately made them alpha-numeric here to demonstrate that there is no relationship between the two tables. To answer your question, can you tell from these two tables who painted Royal gardens ? The answer is no. As the tables are currently designed there is no information about the relationship.

    If we suppose that a painting is only ever painted by one person (different to your assignment I know but it’s an example), we can change the above tables to model this:

    The table of artists:
    ArtistID____Fname____Lname…..
    A1_________John______Smith….
    A2_________Anne______Brown……
    A3_________Mike______Adams
    etc

    The table of Paintings:
    PaintingID___Title of Painting____Type______Frame____Price_____ArtistID
    P1_________Pink Fountains______Oil________Wood___300.00____P1
    P2_________Royal Gardens_____Water______none____200.00____P3
    P3_________Blue Seas__________Oil_______steel_____150.00____P3
    Etc

    Now if I ask who painted Royal Gardens you can apply some logic and work out the artist. What we have done is introduce a foreign key (ArtistID) in the Paintings table. Clearly this isn’t enough for your assignment but it demonstrates a basic principle.

    Using the data in your question you could set up the data the other way round by including the foreign key in the artist table instead of the paintings table (the choice of where to put the foreign key is dependent on which side the “many” sits in a “one-to-many” relationship):
    ArtistID____Fname____Lname……….PaintingID
    A1_________John______Smith…………P1
    A2_________Anne______Brown……….P1
    A3_________Mike______Adams……….P1
    etc

    The table of Paintings:
    PaintingID___Title of Painting____Type______Frame____Price
    P1_________Pink Fountains______Oil________Wood___300.00
    P2_________Royal Gardens_____Water______none____200.00
    P3_________Blue Seas__________Oil_______steel_____150.00
    Etc

    This model would work with the example data you have but is clearly limited because suppose you wanted to add a new record for A1 painting P3. The Artist table will look like:
    ArtistID____Fname____Lname……….PaintingID
    A1_________John______Smith…………P1
    A2_________Anne______Brown……….P1
    A3_________Mike______Adams……….P1
    A1_________John______Smith…………P3

    This has two problems:
    - Since ArtistID is a primary key, it cannot be repeated in the table.
    - The table is no longer normalised as you’ve got repeating data e.g. John & Smith.

    The problems above can be solved by employing a third table!

    There’s a whole lot more I could write but given the question you’ve asked I think you really need to do some reading on relational database design, one-to-many and many-to-many relationships, primary keys, foreign keys. These are pretty fundamental concepts. Might be worth a chat with your tutor too.

    hth
    Chris

Posting Permissions

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