Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2013
    Posts
    72

    Exclamation Unanswered: Please help with my MySQL

    Hallo

    I hope that someone can help me out here.
    See the picture, in order to know what it is about

    http://s8.postimg.org/6fx617agl/Table.jpg

    The table in red, this is what gets me confused!!

    Code:
    CREATE TABLE AdPhoto (
      AdvertNum CHAR(10),
      PropertyNum CHAR(5),
      FileName VARCHAR2(100) 
    ) ;
    So the code above creates the table, but does not implement
    the Foreign Key and Primary keys ... as well as, I am not sure what that vertical line with a star mean??

    Does it mean that: AdvertNum CHAR(10) and PropertyNum CHAR(5) make one primary key. Also the PropertyNum CHAR(5) and FileName VARCHAR2(100) make another primary key!?

    If that is the case then, why was I thinking "you cant use 2 Primary Keys in one table".


    Anyone help me on this please ???



    ######################

    I am thinking of something like this:

    CREATE TABLE AdPhoto (
    AdvertNum CHAR(10),
    PropertyNum CHAR(5),
    FileName VARCHAR2(100)
    PRIMARY KEY (AdvertNum, PropertyNum, FileName),
    FOREIGN KEY (AdvertNum, PropertyNum) REFERENCES Advert(AdvertNum, PropertyNum),
    FOREIGN KEY (PropertyNum, FileName) REFERENCES Photo(PropertyNum,FileName)
    );

    ... am I being correct on this one??


    ps: The fieldnames on the picture are wrongly named but yeh the ad_num is AdvertNum !! Surely you can stop that.
    Last edited by Bucki; 04-23-13 at 18:50.

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    The red box and the vertical lines with the stars are things that your instructor added to draw attention to the flaw in the AdPhoto schema object. With the exception of dependent relationships, one attribute should never be used by more than one relationship and in this example the property_num attribute is used by two relationships that are not dependent upon each other.

    The logically correct way to resolve this is to have two different attributes in the AdPhoto that store the respective property_num value from the independent relationships. The only alternative would be to somehow make the relationships dependent, but that would imply changing the definitions within the problem realm and I'd be surprised if that was permitted in this example.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    VARCHAR2 is not a valid data type for MySQL. I guess you are using Oracle.
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Apr 2013
    Posts
    72
    Hey PAT

    Well, yeh the ER DIAGRAM cannot be changed as this is what he created and we was given a SQL script (in which bits bits are missing such PKs, FK etc etc).
    Now we have to use the ER Diagram to implement all the things within the SQL coding.

    So yeh, I am lost in that table ....
    Is the coding I typed above , totally wrong??


    //////


    Shammat

    Yeh using Oracle
    Last edited by Bucki; 04-23-13 at 19:25.

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Unless you either change the attributes or the relationships, it isn't possible to do the schema correctly based on the picture that you posted (which I've attached to this post). You can't leave the attributes and relationships unchanged and still have a relationally correct answer.

    Without understanding your assignment in more detail I can't offer you much help because in my opinion you are being asked to create a physical model for a flawed logical model, which is just plain wrong. This logical flaw will cause some serious problems as more and more data goes into the system and eventually those conflicts in the data will cause unresolvable issues between the two foreign key relationships.

    Oracle will do what you've asked, but you should never be in a position where you have to ask it to do that. The two foreign keys that you've defined are syntactically and physically correct, but logically wrong.

    I'll move this post to the Oracle forum so that you get more Oracle specific comments about your problem.

    -PatP
    Attached Thumbnails Attached Thumbnails Bucki.jpg  
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2013
    Posts
    72
    Hi Pat

    Well to be honest there is nothing more to the assignment brief.
    All we have to do is:

    - Setting constraints (based on the ER Diagram)
    - Importing and checking the data (we got an excel sheet full of data to import)
    - Preparing queries (this can be done once everything works)

    So this is it really ..
    I dont think the rest of the ER diagram would be much of help...
    as the AdPhoto table is related to Photo and Advert table, now only issue is with the AdPhoto of not knowing how to implement it in SQL.

    :/

  7. #7
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Your problem specification is fundamentally broken at the logical level. Nothing can fix that, it is what it is unless you change the problem specification.

    What you've done with SQL faithfully implements the flawed logical design. You've done the best that you can do with the problem that you've been given.

    I can only hope that the instructor plans to use this flaw later in the course to show you how things are broken and how to go about fixing them. This isn't a common problem to find in my environment and it is often a pig to fix.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  8. #8
    Join Date
    Apr 2013
    Posts
    72
    hEY pAT

    Just talked to my other class friends and they said, I can change its attribute within that table provided I explain why I did it and what I did LOL

    issue is ... I am not sure what you suggestion to do, to implement / fix the "flaw" ??

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    If you can add a new attribute, then we can fix the logical problem. Once we fix the logical problem, then we can fix the physical problem that it caused!

    The logical problem is that you are currently using the PropertyNum attribute for two unrelated relationships, one to Photo and the other relationship to Advert. This means that if the parent PropertyNum value of either row in these relationships changes then the relationship "breaks". The simplest solution is to create two different PropertyNum values, which seems impossible since you can't have two attributes with the same name... This isn't really a problem because these attributes don't need to have the same name as the attribute in their parent object.

    If we modify your existing schema to:
    Code:
    CREATE TABLE AdPhoto 
    (  AdvertNum         CHAR(10)
    ,  PhotoPropertyNum  CHAR(5)
    ,  AdvertPropertyNum CHAR(5)
    ,  FileName          VARCHAR2(100) 
    ) ;
    This will fix the logical problem. I'm pretty sure that you can handle the SQL syntax to complete the physical side.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  10. #10
    Join Date
    Apr 2013
    Posts
    72
    Ahhhh yehhhh
    you gave me some clues there ...

    Thanks a lot

Posting Permissions

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