5

For a school project, we have to create our own database. I decided to create a database to manage my electronic component inventory. As a requirement, we needed to create an ER diagram, then from that diagram derive the database schema. Unfortunately for me, the professor believes that the diagram I created can be simplified and the "Part" entity is unnecessary.

This is the diagram I came up with, and here is the derived schema.

If I remove the Part entity, then in order for a Circuit entity to "use" any number of any part, and have each part associated with possibly any circuit, I would have to have a separate M-to-N relationship from each component type to Circuit. Each of those relationships would generate a new table. This would definitely go over the strict maximum number of tables we are allowed for the project.

If the professor specifically mentioned Part was unnecessary, then there must be some way to remove it that results in a simpler ER diagram and schema - but I can't see what it is.

Maybe you guys can see what it is and give me a hint?

EDIT: Dan W had a great suggestion. I could eliminate the Part by giving each part type (Capacitor, Resistor, etc.) their own keys. Then inside of uses part, include foreign keys to those components. I would have to assume that each entry of the table would only be associated with a single part, the rest being null. Here's the resulting schema. This schema should work well. But now I have to figure out exactly what modifications to the ER diagram would correspond to this schema.

EDIT2: I've come to the conclusion that the relationship I'm looking for is n-ary. According to several sources, to convert from the n-ary to a schema you include the primary key of each participating entity type's relation as foreign key. Then add the simple attributes. This is what I came up with.

Bill the Lizard
  • 398,270
  • 210
  • 566
  • 880
Schmidget
  • 123
  • 1
  • 7
  • 1
    Couldn't you change PartID to ResistorID, CapicatorID, etc. and then add these columns to the Uses_Part table? – Dan W Feb 14 '12 at 21:58
  • Good suggestion. I've thought of this before, but I'm not entirely sure how to represent that in an ER diagram. It might be an n-ary relationship, but I'll have to see. – Schmidget Feb 14 '12 at 22:54
  • 2
    I really like your design. I would change nothing. There are different kind of parts, with different attributes and you have separate entities for each one (Resistor, Capacitor, etc). The Part entity is needed as the supertype entity of these. As you say (correctly) to be used in the M:N relationship. – ypercubeᵀᴹ Feb 14 '12 at 23:20
  • I thought my original design was okay as well, but I guess I have to do it the professor's way. I'm not sure if one design is better than the other. – Schmidget Feb 14 '12 at 23:34
  • 2
    Your EDIT-2 design with the n-ary relationship does not allow you to store various ciruits. For example circuits with Resistors only. The design would be OK if all Circuits were composed of "lines" that had exactly 1 Resistor, 1 Capacitor, 1 IC and 1 Transistor. Which doesn't seem to fit the concept of circuits well. – ypercubeᵀᴹ Feb 14 '12 at 23:40
  • Hmm...you might be right. I was under the impression that the resulting table would include multiple entries for a single circuit, each row containing the key to exactly 1 component, and the rest being null. Do you have any suggestions as to how Dan W's suggestion might be translated to an ER diagram? It's kind of silly that we have to first come up with an ER diagram, then use that to derive the schema using a specific algorithm described in the text. – Schmidget Feb 14 '12 at 23:48
  • 1
    I think that messing with Nulls during the ER modeling phase is taking the wrong path. – ypercubeᵀᴹ Feb 15 '12 at 00:18
  • 2
    The only way I see for removing the Part entity with your restriction (on the number of tables) is by (overly) simplifying the model. If the Circuit-Part relationship is suppossed to be 1:N (and not M:N), then you could skip the Part entity by adding a `CircuitId` column to all the subtype entities (so the Circuit will have a 1:N relationship with every one of the other tables.) – ypercubeᵀᴹ Feb 15 '12 at 00:22
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/7708/discussion-between-schmidget-and-ypercube) – Schmidget Feb 15 '12 at 00:42
  • @Schmidget Did you ever figure out what schema/ER diagram you'll use? – Dan W Feb 15 '12 at 15:09
  • @DanW I turned in my original design. My original design is the cleanest, the most expandable (I could add new part types without having to change much), and I've already begun the implementation part of the assignment and added a bunch of data. If this design is a problem, I'm putting my foot down unless the professor can show me a solution that is simpler, but remains just as easily expanded. – Schmidget Feb 17 '12 at 14:34
  • I consider ER modeling part of requirements analysis. I consider tables and foreign keys part of database design. If your professor doesn't see the division this way, then my advice will be misleading. If your professor doesn't distinguish between analysis and design, then God help you. – Walter Mitty Mar 29 '12 at 13:08

1 Answers1

1

You have a strict maximum number of tables (physical design) but are you restricted in your ER diagram to that number of entities (logical design)? All of your entities for parts - resistors, transistors, capacitors, and General IC - could be stored in one parts table with all the attributes of Part, resistors, transistors, capacitors and General IC as nullable columns. If an attribute is valid for all types then it is not nullable. Include another column in the parts table which identifies the type of part (resistor, transistor, capacitor or IC) although you already have a type column in all the entities which might also serve for this.

The Parts table in your schema is now:

PartID (PK)
Quantity
Drawer
Part Type
Value
Tolerance
Subtype
Power Rating
Voltage
Term_Style
Diam
Height
Lead_Space
Name
Case
Polarity
Use
V_CE
P_D
I_C
H_FE
Package
Pins
Description

and you drop the Resistor, Capacitor, Transistor and General IC tables in your schema. Leave those entities in your ER diagram because that shows which attributes in the Parts table is required (shouldn't be null) for each part type.

Paul Morgan
  • 31,226
  • 3
  • 24
  • 27