Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2012
    Posts
    1

    Exclamation Decomposing Many to Many Relationships

    I'm looking for some help in decomposing a couple of many-to-many relationships for a piece of coursework...

    These are the entities and their attributes I've established from the requirements specification:
    Staff: (StaffNo, Name, D.O.B. ....)
    Course: (CourseCode, StaffNo, Course_Title, Course_Desc, Fee ....)
    Delegate: (DelegateID, CourseCode, Reg_Type, Del_Name, ....)
    Invoice: (Invoice_ID, Date_Paid, Payment_Method)
    and here's the un-decomposed ERD I've made using this info.

    The spec states that each course is delivered by one staff member, but I've assumed that any staff member can deliver more than one course, hence the one-to-many relationship between those two entities.

    However, what's completely stumped me is the relationship between Course and Delegate, as the course is obviously attended by many delegates, and the spec states that a delegate may attend multiple courses. This also applies to Invoice and Delegate, as a Delegate could potentially receive multiple Invoices, and Invoices are sent to multiple Delegates.

    The question states that the M:M relationships should be "replaced with new entity types and relationships", but my (hair-brained) attempts to do so just don't seem right.


    Tell me, am I looking at this all wrong? Any help regarding this would be hugely appreciated.
    Attached Thumbnails Attached Thumbnails undecomposederd.png   decomposederd.png  

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Though I don't know exactly how to read the diagrams,
    if entity A and entity B has a many to many relationship, like...
    A(primary key: PK_A) (many)<--->(many) B(primary ke: PK_B)

    make a new entity(say, A_B) for the M:M relationship, like...
    A <---(foreign key: PK_A)--- A_B(primary key: (PK_A, PK_B) ) ---(foreign key: PK_B)---> B

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Do you understand there need to be an Entity that ESTABLISHES Those Relationships?

    STAFF 1====M Courses

    Such that

    Staff
    Staff ID
    Name
    ect

    Course
    CourseID
    Course Name
    ect

    Staff_Teaching_Course -- Your NEW Entity
    StaffID
    CourseID

    Not that hard
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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