Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2015
    Posts
    7

    Question Student needing help with an ER Diagram

    I'm having difficulty trying to draw up an ER diagram based on a particular homework problem in my textbook for a college level database management course. We have just started learning about all of the basics of an ER model such as the degree, cardinality, entities, attributes, and relationships. The problem I'm having difficulty with is as follows:

    A piano manufacturer wants to keep track of all the pianos it makes individually. Each piano has an identifying serial number and a manufacturing completion date. Each instrument represents exactly one piano model, all of which have an identification number and a name. In addition, the company wants to maintain information about the designer of the model. Over time, the company often manufacturers thousands of pianos of a certain model, and the model design is specified before any single piano exists.

    I've come up with entities of piano, model, and designer. Attributes for piano are serial number and completion date. Attributes for model are ID number and name. No attributes specified for designer.

    For this problem, is PIANO a weak entity since it says the model is designed before any PIANO exists implying it has to have model as its identifying owner? Where would DESIGNER be placed? I've figured out a cardinality between PIANO and MODEL of exactly one and a cardinality of MODEL to PIANO of 0, 1, or many. I cant determine any cardinalities between DESIGNER and the other entities based on the problem. Any assistance with this would be greatly appreciated. I would not consider my professor the best, as I know many of the students seemed to be confused about properly drawing the diagrams. The professor mentioned that if a diagram is drawn representing a binary or ternary relationship when it could have been represented with an associaitve entity, points will be subtracted. If this is not even the proper website for a post of this content, I apologize. I was just looking for some guidance on this problem and came across this website where I hoped some experts may be of some assistance.

    Thank you

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    How about you look at it in terms of time. That may lead you to a couple of meaningful relationships among these concepts (model, instrument, and designer).

    So, we know that "the model design is specified before any piano exists". This is actually very good, as you probably don't want the workmen (or worse team of workmen) making parts for a piano blindly, and hoping what comes out the other side is playable. We also know that "Each instrument represents exactly one piano model". With that in mind, what comes first, the model, or the piano? And what is the relationship between them?

    "Designer" and "piano" are not mentioned in the same sentence anywhere, but "model" and "designer" are. What does that imply about the relationship of "model" and "designer"?

    I am not sure what a "weak entity" is in terms of your class, so let's just concentrate on parent-child relationships, and their cardinality for now. Once you have that, the rest should fall fairly smoothly into place.

  3. #3
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    How about you look at it in terms of time. That may lead you to a couple of meaningful relationships among these concepts (model, instrument, and designer).

    So, we know that "the model design is specified before any piano exists". This is actually very good, as you probably don't want the workmen (or worse team of workmen) making parts for a piano blindly, and hoping what comes out the other side is playable. We also know that "Each instrument represents exactly one piano model". With that in mind, what comes first, the model, or the piano? And what is the relationship between them?

    "Designer" and "piano" are not mentioned in the same sentence anywhere, but "model" and "designer" are. What does that imply about the relationship of "model" and "designer"?

    I am not sure what a "weak entity" is in terms of your class, so let's just concentrate on parent-child relationships, and their cardinality for now. Once you have that, the rest should fall fairly smoothly into place.
    I apologize. A weak entity, in terms of our class, is an entity that cannot exist on its own. It has to have an entity as its identifying owner. An example we were given is BOOK-BOOK COPY. Book copy cannot exist on its own without having book as the identifying owner. It wouldnt make any sense in the database by itself without BOOK. I figured piano, or instrument as you state to use, would be weak because the very last sentence says a model design is specified before any single piano can exist. This tells me that if no design is specified, then the piano or instrument entity would be weak.

    In response to "Each instrument represents exactly one piano model," I would assume the model comes first and the relationship cardinality between instrument and model is one and only one.

    In response to model and designer being mentioned in the same sentence, I would assume a model must be chosen before the designer is. I'm not sure there. If that's correct, I'm not sure what the cardinality of it would be.

    I've attached an image of what I had so far. I know you specified using instrument instead of PIANO. I did this table a few days ago. This is the only information I've been able to obtain for it from the question. I understand designer should be related to model. I just don't understand how or what the cardinalities are because the question doesn't appear to list them as it does for "Each instrument represents exactly one piano model" and "manufactures thousands of pianos of a certain model." I also don't have any attributes for designer because, as stated, our professor told us to not create any attributes for an entity if we are not given them. By doing that, we are essentially making up business rules. I didn't see anything about designer other than the company wants to keep information about the designer.

    Click image for larger version. 

Name:	image.png 
Views:	14 
Size:	101.1 KB 
ID:	16561

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Perhaps a more concrete example is in order. Think about the relationships between cars, makes, and models. Examples of makes are Ford, Chevy, Honda, etc. For models, you might have Civic, Focus, Spark (I drove one once. Probably never again). Cars would be your car, my car, etc. My own car may be a Ford Focus, and your car may be a Ford Focus, but no one would call both cars the same, right?

    Now, a model may have many cars. A make may have many models. But no car represents two or more models, right?

  5. #5
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    Perhaps a more concrete example is in order. Think about the relationships between cars, makes, and models. Examples of makes are Ford, Chevy, Honda, etc. For models, you might have Civic, Focus, Spark (I drove one once. Probably never again). Cars would be your car, my car, etc. My own car may be a Ford Focus, and your car may be a Ford Focus, but no one would call both cars the same, right?

    Now, a model may have many cars. A make may have many models. But no car represents two or more models, right?
    I'm understanding the car analogy you've used. I just don't know that I'm correctly relating it to this piano problem. Are you stating that a piano wouldn't represent two or more models? I guess the way I read the statements, "Each instrument represents exactly one piano model" and "The company often manufactures thousands of pianos of a certain model" is, a piano can only be exactly one type of model (referring to the first quote). For a particular model, let's say model A, thousands of pianos can be produced for that model (referring to the second quote). That is how I interpretted the text and set my table up to relate to it in that manner. Hopefully this is what you were referring to with your car example. If not, then I guess I'm just really confused about the problem. Trying to understand an explanation to a problem without visuals sometimes is difficult.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    You are getting it. Part of the confusion may be stemming from their use of "instrument" and "piano" to mean essentially the same thing. I think they originally wrote "Each piano represents exactly one piano model", but felt that sounded awkward with so many "pianos" in there, so I blame all those english majors.

    Now, where does a "model" come from (in either example)? Who makes a "model"?

  7. #7
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    You are getting it. Part of the confusion may be stemming from their use of "instrument" and "piano" to mean essentially the same thing. I think they originally wrote "Each piano represents exactly one piano model", but felt that sounded awkward with so many "pianos" in there, so I blame all those english majors.

    Now, where does a "model" come from (in either example)? Who makes a "model"?
    The model comes from the designer. The designer produces the model.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    So the relationship between model and designer is...?

  9. #9
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    So the relationship between model and designer is...?
    I would assume the relationship between model and designer is that a model belongs to exactly one designer? If this is correct, what text in the problem tells you that a model is produced by exactly one designer? The trouble I'm having with the problem is pulling the information directly from the text. Real life experience would tell me that a model belongs to exactly one designer, but we were also told not to assume anything with these models. Some of them are clear cut such as a piano belongs to exactly one piano model. I get that one 100%. What part of the text specificaly gives you enough information to tell you that a model belongs to exactly one designer without assuming it?

  10. #10
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    I am making something of an assumption there. But I am basing that assumption on the line:
    Quote Originally Posted by exercise
    maintain information about the designer of the model
    If they had meant it to be a many-to-one, they should have said:
    Quote Originally Posted by exercise
    maintain information about the designers of the model

  11. #11
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    I am making something of an assumption there. But I am basing that assumption on the line:If they had meant it to be a many-to-one, they should have said:
    Okay. I'm glad I'm thinking along the same lines you are. That's what I was thinking too, but was trying to avoid assuming as our professor said. However, I don't know how else you'd infer cardinalities between the two if you weren't assuming to an extent, since it does not state so directly as the other two statements did. Now as far as the other direction of the relationship, do you see anything specifically in the text stating how to determine the cardinality from DESIGNER to MODEL. We've established a MODEL belongs to exactly one DESIGNER as pictured here: Click image for larger version. 

Name:	image.png 
Views:	8 
Size:	83.5 KB 
ID:	16563

    So, where in the text does it state the cardinality going from DESIGNER to MODEL? How many could a DESIGNER produce of one type of MODEL?

  12. #12
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Quote Originally Posted by Pfunstuff
    I don't know how else you'd infer cardinalities between the two if you weren't assuming to an extent,
    Welcome to the business world. There may be a few other surprises waiting for you here.

    If the relationship between designers and models were many-to-many, how would you write their sentence (you can't use many-to-many, since business users have no idea what that means).

  13. #13
    Join Date
    Sep 2015
    Posts
    7
    Quote Originally Posted by MCrowley View Post
    Welcome to the business world. There may be a few other surprises waiting for you here.

    If the relationship between designers and models were many-to-many, how would you write their sentence (you can't use many-to-many, since business users have no idea what that means).
    Thank you for all of your help with this question/problem. I really do appreciate your insight and the way you helped explain things.

Posting Permissions

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