Search code examples
mysqlsqldatabase-designentity-relationshipdiagram

How could the following database schema be drawn using E/R diagrams?

How could the following database schema be drawn using E/R diagrams? (A sketch or final image would be helpful). I would also appreciate if you could guide me to a easy-to-understand tutorial on entity-relationships so I could learn how to draw them on paper first.

  1. A CD has a title, a year of production and a CD type. (CD type could be anything: mini-CD, CD-R, CD-RW, DVD-R, DVD-RW...)
  2. A CD usually has multiple songs on different tracks. Each song has a name, an artist and a track number. Entity set Song is considered to be weak and needs support from entity set CD.
  3. A CD is produced by a producer which has a name and an address.
  4. A CD may be supplied by multiple suppliers, each has a name and an address.
  5. A customer may rent multiple CDs. Customer information such as Social Security Number (SSN), name, telephone needs to be recorded. The date and period of renting (in days) should also be recorded.
  6. A customer may be a regular member and a VIP member. A VIP member has additional information such as the starting date of VIP status and percentage of discount.

Is this Entity diagram correct? This is so fracking confusing. I've built this diagram on just intuition rather a systematic approach they teach in a textbook. I still can't wrap my head around the many-to-one relation, weak entities, foreign keys.

enter image description here

Solution

  • There's a fair article on ERDs on Wikipedia.

    When you're starting a new ERD - whether it's hand-drawn or computer-drawn - you should focus first on the entities (entity sets). Add the relationships in and then worry about fleshing out your non-key predicates. When you get some experience with ERDs you'll get to the point where you won't need much more work to achieve normalization. It will start to come naturally to you.

    There are probably quite a few changes that you'll want to make to your diagram. Since this may be homework, I'll give you an alternative diagram to consider:

    enter image description here

    This model takes a more sophisticated view of your rules, for example:

    • Songs can appear many times on the same CD and on different CDs.
    • A song can be performed by multiple artists within a given track.
    • Producers can cooperate on a CD.

    None of these are necessarily right for your model. It depends on your business rules.

    Compare your model with this one and ask yourself what is different and why you might want to take one approach or the other.