Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2012
    Posts
    4

    Help with Logical ERD - Third normal form

    I am designing a ERD on the Third Normal Form for a database and I have a problem.

    I can't seem to do is join the People table with the Competitions table as for the Third normal form ERD I can't have any Many-To-Many relationships, only 1-to-Many or 1-To-1 and the task that I'm doing states that:

    "Any band can enter more than one competition. A recording of which band enters which competition(s) should be kept in the database. The band is then given a number which indicates in which order the bands will compete in one particular competition."

    So in that case: A band can be in MANY competitions and a competition can(has to) have MANY bands - Many-To-Many.. Can't figure out how to get that into One-To-Many.

    Oh and yes, I am a student and yes this is my coursework. I am not looking for people to do my work for me as I do want to learn, this is only a really small bit of the draft ERD that I have been stuck on for quite alot of time and can't seem to figure out.


    *EDIT*
    I think I have figured it out and uploaded a pic of my solution. If i figured it out correctly and the solution is good, then I'm really stuck on how to do the number which indicates the order in which the bands play.

    One more question, if I mark an attribute as Something* and SomethingElse* - It makes it a composition primary key with foreign key attributes? Is that the correct way of doing it when you decompose M-to-M relationships?

    ________ = Primary Key
    * = Foreign Key
    Attached Thumbnails Attached Thumbnails Logical ERD Attempt.jpg  
    Last edited by Lynce; 02-26-12 at 10:56. Reason: Possible solution

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    honest students with legitimate requests for assistance are almost always helped on this site

    it's the guys who dump their assignment paper and ask people to solve it, or the guys who pretend it isn't homework, who get the short shrift



    in your case the solution to the many-to-many is to create an association or relationship entity which has a many-to-one relationship to each of the two entities that it associates

    it would be called something like competitors, and it would have a primary key consisting of two foreign keys, one to competitions, and one to persons

    the pair of FKs form the composite PK, thus assuring that any one person cannot compete in the same competition more than once

    this entity would also have a data attribute, namely, the competition sequence number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2012
    Posts
    4
    I have updated my possible solution before I saw your reply and I think I did exactly what you advised? I didn't quite understand the sequence number. If I add an attribute "Playlist" to the "Event" table - How will it work? It's hard for me to imagine.

    Updated from People to Bands - as I gone through almost the whole assignment and it had to be bands.

    Oh, and thank you for your reply, It's nice to know that you can actually get help. Our sessions in University have too many people and not enough time to get proper advice on these things.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Lynce View Post
    One more question, if I mark an attribute as Something* and SomethingElse* - It makes it a composition primary key with foreign key attributes? Is that the correct way of doing it when you decompose M-to-M relationships?
    yes, that's fine

    it may not necessarily conform to entity relationship diagramming conventions, but as long as the intent is clear, and yours is, it's fine
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2012
    Posts
    4
    I have attached my ERD, could someone take a look and advise if I have done it correctly according to these requirements? Are the keys and relationships correct?

    The ERD had to be logical and of the third normal form.

    1. Bands are made up of university students.
    2. Band members must all come from the same university.
    3. A university may enter any number of bands to a competition.
    4. One member of the band must be designated as Band Leader.
    5. Instruments played in the band by each member should be recorded (one member can play more than one instrument).
    6. Competitions are classified according to Genre.
    7. Each competition is based on a single genre.
    8. Popular genres may have more than one competition devoted to them.
    9. Each competition is sponsored by a national or local company, or by an individual.
    10. Sponsors may sponsor more than one competition but each competition has just one sponsor.
    11. Bands may enter more than one competition.
    12. Bands are given playlist numbers.
    13. Results should be stored using standardised codes (1,2,3,HC,U) and each type of result should be awarded points.
    Attached Thumbnails Attached Thumbnails ERD.png  

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    sorry, tl;dr
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Feb 2012
    Posts
    4
    Okey then, maybe you could explain one smaller bit?

    A student might be only in a band from his university.

    Now the problem is, if i have band "Hills" that belongs to university "Sussex Uni"
    I can still add student "Josh" to the band "Hills" even though Josh's university is set to "London Uni".

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    so you add student plus uni to band, and student+uni has to be a foreign key
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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