Validity of the following Entity Relationship Model?

4
votes

Before I go ahead and convert my entity relationship diagram into SQL statements, I thought I'd ask if someone could verify if this model doesn't contain any absurdities and anomalies that will appear once I have a SQL database schema.

I am particularly unsure about my cardinality of a relationship between Customer and VIP. Also, Supplier and CD relationship. start_date of the VIP entity - should it be a weak key? Are there any other potential weak keys besides the name attribute of the Song entity?

E/R Model

Legend

  • Entity enter image description here
  • Attribute enter image description here
  • Weak Entity enter image description here
  • Relationship enter image description here
  • Identifying Relationship enter image description here
  • Cardinality Ratio enter image description here

I've used the following websites as references to construct my diagram:

  1. http://en.wikipedia.org/wiki/File:ERD_Representation.svg
  2. http://en.wikipedia.org/wiki/Entity-relationship_model
  3. http://www.cse.ohio-state.edu/~gurari/course/cse670/cse670Ch2.xht

Software used to create the diagram: Dia (Linux)

1
sqldatabase-designentity-relationshiper-diagrams
Randomly - could two CD's contain the same song? Like a "hits" compliation or some such? Would that be another song record? or would you maybe need to break out the relationship with another table (cd's, songs, songstoCD { songid, cdid, track# }) or some such? – Prescott
my UML is weak - so if this diagram already says that, nevermind my comment! – Prescott
@Prescott Each CD can contain only one unique song name. Fairly simplistic model. – Sahat Yalkabov
Ah ok - so no need to know that maybe an artist has song X on two cd's? you'd just have two records that say song X - Cd1, song X - cd2 and assume that song X is the same song X? or you don't care about that particular relationship at all? I think you get it though, just curious – Prescott
The "is" relationship between Customer and VIP is an instance of the "gen-spec" pattern. There are better ways of modeling gen-spec in ER diagrams. When you go to convert to SQL, you'll want to know how to design SQL tables that model gen-spec. – Walter Mitty

1 Answers

1
votes

Sorry this is a late answer, but in case it's useful there are two improvements you can make.

1) The "is-a" relationship between "VIP" and "CUSTOMER" indicates the presence of a superclass (customer) and subclass (vip). You may want to model VIP as a subclass.

2) Since you are tracking dates for the relationship "rents", the cardinality must be taken "over time". Therefore the cardinality on both sides is "N" (i.e., not "1" on the side of customers)

Minor improvement: in "Song" (weak entity class) set the partial identifier as "track" rather than "name"; this will allow for multiple recordings of the same song on a CD (e.g., 2 versions). The track number will always be unique within the CD