macOS Need help with an ER diagram problem

[Pfunstuff]

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.

What I've deduced from this problem is that the entities are piano, model, and possbly designer? The attributes for piano are serial number and completion date. The attributes for model are ID number and name. I don't have any attributes for desginer, and our professor told us not to make any up if they aren't specified.

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? Would DESIGNER be an entity as well? Where would it 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.

This is what I have so far, and it doesn't feel right.



Edit: For the problem, the degree and cardinalities of the relationship must be explained in addition to the a diagram of the relationship.

 

[D.T.]

I'm note sure I follow about not being able to add attributes, because your diagram technically doesn't relate MODEL to PIANO, i.e., PIANO should have ID_NUMBER as an attribute.

Then MODEL should have DESIGNER_ID to relate MODEL to a DESIGNER, and that should be a one-to-one, for each MODEL there exists one and only one DESIGNER.

However, I'd additionally assume that a DESIGNER could be related to other MODELs, which is kind of what you have modeled.

Unless DESIGNER is simply an attribute on MODEL (though the PIANO to MODEL comments above are still applicable)

[edit]

Since it's school vs. actual production modeling, I guess they don't so much care about the things like like IDs/Keys/etc., they just want you to understand the relationships, so that being the case:

MODEL can have 0,1 to many PIANO
DESIGNER can have 1 to many MODEL
MODEL can have 1 and only 1 DESIGNER
PIANO can have 1 and only 1 MODEL

With a pass-through relationship that a PIANO can have 1 and only 1 DESIGNER

[edit #2]

I guess since this is intro-esque, the point of "a design/desinger exists before anything is built" is to deter people from relating DESIGNER to PIANO.

 

[Pfunstuff]

I'm note sure I follow about not being able to add attributes, because your diagram technically doesn't relate MODEL to PIANO, i.e., PIANO should have ID_NUMBER as an attribute.

Then MODEL should have DESIGNER_ID to relate MODEL to a DESIGNER, and that should be a one-to-one, for each MODEL there exists one and only one DESIGNER.

However, I'd additionally assume that a DESIGNER could be related to other MODELs, which is kind of what you have modeled.

Unless DESIGNER is simply an attribute on MODEL (though the PIANO to MODEL comments above are still applicable)

What our professor specifically told us is that if a problem, such as this one, does not list attributes for an entity that we are not to make them up. She claims we are essentially making up business rules when we don't know them. Since the problem does not mention anything about designer ID as you mentioned, we are not supposed to assume that and add it in. We are in the very early beginnings of designing these diagrams.

I don't know that we have been taught anything about having designer ID as part of model to relate them. I only understand, so far, that the line is the relationship that relates the entities. I have serial number and manufacture completion date under piano because we were taught to pull them from the text as it lists them. It states, "Each piano has an identifying serial number and a manufacturing completion date." We were taught, those are the two attributes given for piano. Each instrument represents exactly one piano model. That's where I got the cardinality of one and only one for PIANO to MODEL. Then it says the company wants to maintain information about the designer but does not list any specific attributes as it did for piano (ie. serial number and completion date). So, I just have designer in a box. Next it says, "Over time the company often manufactures thousands of pianos of a certain model." This is where I got the cardinality of 0, 1, or many from model to piano since it says often, which implies it could be not at all, and thousands which is many. The last part stating model design is specified before any single piano can exist is what makes me thing PIANO is a weak entity because that's essentially saying a piano can't exist without a model.

I hope that explanation and my reasoning makes sense.

 

[Pfunstuff]

I'm note sure I follow about not being able to add attributes, because your diagram technically doesn't relate MODEL to PIANO, i.e., PIANO should have ID_NUMBER as an attribute.

Then MODEL should have DESIGNER_ID to relate MODEL to a DESIGNER, and that should be a one-to-one, for each MODEL there exists one and only one DESIGNER.

However, I'd additionally assume that a DESIGNER could be related to other MODELs, which is kind of what you have modeled.

Unless DESIGNER is simply an attribute on MODEL (though the PIANO to MODEL comments above are still applicable)

[edit]

Since it's school vs. actual production modeling, I guess they don't so much care about the things like like IDs/Keys/etc., they just want you to understand the relationships, so that being the case:

MODEL can have 0,1 to many PIANO
DESIGNER can have 1 to many MODEL
MODEL can have 1 and only 1 DESIGNER
PIANO can have 1 and only 1 MODEL

With a pass-through relationship that a PIANO can have 1 and only 1 DESIGNER

[edit #2]

I guess since this is intro-esque, the point of "a design/desinger exists before anything is built" is to deter people from relating DESIGNER to PIANO.

Your first edit makes a lot more sense now in terms of what we are learning. And yes, you are correct. They don't care so much about IDs/Keys/etc. unless they are speicifically listed in the problem. That's why I only have the few attributes that I do for Piano and Model. We were taught to underline the identifier that can uniquely identify an instance of the entity. This is why I had serial number and ID number underlined.

I have a few questions as to how you figured out some of the cardinalities that you did.

MODEL can have 0,1 to many PIANO -- This I understand based on the statement that the company often manufactures thousands of pianos for a certain model. Often inferring 0 and thousands inferring many.

DESIGNER can have 1 to many MODEL -- Where in the question do you pull this from? I'm not doubting you. I'm just trying to understand the reasoning. Where does it state that DESIGNER has to have at least one but possibly more MODELs?

MODEL can have 1 and only 1 DESIGNER -- Where in the question do you pull this from? Same as above.

PIANO can have 1 and only 1 MODEL -- This I understand because it states each instrument, or piano, has exactly one piano model.

"With a pass-through relationship that a PIANO can have 1 and only 1 DESIGNER" -- What do you mean by this statement? Is it pass-through because PIANO is passing through MODEL to get to DESIGNER? I'm also guessing the opposite spectrum of the pass-through relationship would be that a DESIGNER can have 0, 1, or many PIANOs?

Sorry for all of the questions. I'm just really trying to get a solid grasp on this. Thank you for your responses thus far. They are truly helpful.

 

[JustMartin]

I wonder whether 'design' should also be an entity. It's a long time since I've done any of this stuff, but I'm thinking

A designer creates one or more designs
A design may be implemented by 0 or 1 models
One or more pianos are created according to a model