How to create a correct entity-relationship diagram (ERD) about a video game profits scenario

The General Advice:

When you are starting off learning how to model databases, one of the most important rules of thumb is: Every tangible thing that matters to your system is probably an entity type.

This is a really good place to start with any logical database design. If you spend some time up front thinking about what kind of things matter to your system, then you're going to come up with a solid foundation on which to build your system. The things your organization cares about will change much less frequently than the business processes and rules your organization uses to deal with those things. That is why a solid data model is so important.

Another important rule of thumb is: Normalize your data model by default and only denormalize when you have a (really) good reason to. This is especially true for a transactional system. Reporting systems and data warehouses are a different story.

The Specific Answers:

Cardinality: If you think about it, it is easily the case that a car could have never been serviced (by your shop). Therefore a minimum cardinality of zero is very plausible. On the other hand, by the time the vehicle matters to your system it may well be because it has had its first service - so a minimum cardinality of one is also plausible. You need to think about what the business rule is for your organization and model accordingly. I would think, for example, that a car dealership would have lots of cars in its system that haven't been serviced by the dealership yet, whereas a muffler shop wouldn't care about cars it hasn't serviced.

Service Items: You asked:

Also, a service involves parts, labor, and consumable.

How would you model this? As a separate entity? Or in the service entity or part of the relation (intersection entity) between car and service ?

Let's consider an intersection entity between car and service... You could potentially use such an intersection to store details about the service, like how much labour, which parts, and consumables were used.

However, using an intersection implies a many-to-many between cars and services, but you've already stated that each service is for (exactly?) one car. Using an intersection entity to track service item details would mean your model isn't properly normalized.

Consider this model as an alternative:

In this model each service is for one vehicle, but each service can have many instances of labour, parts and consumables. This model follows the first rule of thumb I mentioned and makes an entity type out of each tangible thing the system cares about. This might be a good first stab at a logical model.

One of the issues with the above model is that it doesn't handle one aspect of how your system is likely to want to use the data, at least not very well. One of the most important reasons for tracking all of this data in your system at all is so that you can print off an itemized service invoice. That means that a service line item is itself a thing which is important to your system. If you take that into consideration, you might end up with something more like this:

Notice in this second alternative SERVICE_LINE_ITEM is recognized as an entity type. It is an intersection between SERVICE and the generic line item type: SKU. A SKU is a supertype entity that could be a part, a consumable or some kind of labour. You don't need to have a logical supertype for service line item types, but a lot of systems would be modeled this way because it makes the transactional detail much simpler.

This second model introduces abstract entities over and above the concrete entities of the first model. Introduction of abstractions like this is one of the things that tends to happen as you move from an initial logical model, based mostly on tangible things to a physical model.

As you gain experience with data modeling, you'll get good instincts for moving past the conceptual/logical model stage directly to a well structured physical model.

This is a great question! You have a great start on the ERD because you took notes on how the world of the Tutoring Center works. You are right in that all of these entities are related. The difficult part is in developing a sound model of what those relationships are. Before you get into relationships though, you first want to uncover if any of the entities are really the same thing. In this case, employee, teacher, director, and section manager are really the same thing - a person working for the tutoring center. So I would say you really have a single entity there - Employee. Now that employee may be a contract worker or be a full time employee. The other words - teacher, director - identify roles those employees play. In one of the sentences from your notes you identify the employee, but the role is really say analyst. The employee analyzes the section's data.

The best way to handle these employee roles is to realize that each employee is really holding a position in the tutoring center. Each position would be of a given position type, and in this case those types are Teacher, Director, Analyst, and Manager. Now that you've identified the entity types, you begin to look for the relationships. For example, your question "where students didn't actually contact the teacher but they will be taught by the teacher so do you establish a many to one relationship between these two entities?" Absolutely there is a relationship because the student is related to the teacher through the class. But there a subtle distinction here that will cause you to introduce more entity types to properly model what is going on.

A thing type is distinct from a thing. We know we have a student and we know we have a class. But really, there is the kind of class - say "Into to Math" - and the offering of that class on a given date and time. This is a very common modeling pattern you will find everywhere. In fact you have already seen it with the position type - a kind of job an employee can do, and the position - where an employee is performing that position type at a moment in time. There are two ways to handle this from a naming standpoint. The first is to use the words class type and class. The second is to come up with words whose natural meaning is associated with a type or an instance. In this case I like the word course to represent the kind of class being offered, and the word offering to represent the scheduling of a teaching team to actually deliver that course to students. So while it isn't in the notes directly, I would say that since each section is broken down by academic area, each section likely offers one or more courses, and each offering may be taught by many teachers, and many teachers may teach one offering.

This now leads into your second question: "I ended up with many to many relationships and I don't know what kind of associate entity I should add between them." If your ERD is a pure conceptual model with no attributes being shown, then you don't need to add an associative entity. You can use the Many to Many relationship to represent this. Only when you add the attributes do you need to resolve M to M relationships with associative entities so they may hold the attributes. But, if the relationship itself will then be associated to new entities, you have to introduce the associative entity. That is the case with offering, as we realize that a student must register for an offering that is being taught cooperatively by many teachers. Registration becomes an associative entity to show a student registers for one or more offerings, and an offering can be registered by one or more students. It isn't the case though with the cooperative teaching, as we can simply show a M to M between offering and teacher as there are no further associations. Then, when you add attributes (even if there are none there are still the foreign keys), you would add an associative entity perhaps called instruction team member. We say member as in ER modeling each entity type is always named for a single occurrence - exactly as you have done. I bring this up only because its very easy to think you are naming an occurrence but have really named the set - in this case to call the associative entity instruction team.

To finish up, you note that a class may offer a test at the end. I would say the relationship you have is actually 1 to M from class to test, as I would suspect each course has its own test type that it uses that is only for that course and no other. Now if we needed to show that students in the class actually take the test and make a grade on it, then we again have test type and test, where test is a particular student taking the test on a given date and making a given grade. Along these lines, you note that a student submits an evaluation. This is modeled by showing that for a registration, which is a student taking a given offering, an evaluation is performed.

Here is an example ERD I prepared to answer this using the Oracle Data Modeler which is a great free tool you can download that uses what I consider to be the best notation for conceptual models - Barker-Ellis notation. There is so much more we could discuss about the model and various patterns to accurately depict the tutoring center. One important addition would be prepositional phrases that precisely describe each relationship. A really good book to check out when it comes to doing this kind of conceptual modeling is Enterprise Model Patterns by David Hay. You can also pick up the original text on the Barker-Ellis notation for just the cost of shipping! Good luck with the project and I hope my comments have helped answer your questions!