In the database modeling series so far we have covered a lot of theory concerning the entity-relationship model and creating diagrams. Now we are going to give an example of an entity-relationship diagram, which will represent a simplified model of a university center. We will briefly explain what this model contains, and then show an image of our diagram. Everything on the image (symbols, notation) is standardized and only the things we have covered in the previous articles have been used.
Note: You can create your diagrams using a specialized tool like ERwin, or using a multitude of online tools, for example draw.io. Since we are using standard notation, the environment shouldn't play any role in creating or displaying a model. An advantage of using specialized software is the opportunity to automatically generate an SQL schema from the model, but we will come back to that later.
As we said, this is a significantly simplified model of a university center. We will use this example throughout the series because it is easy to understand and diverse enough to illustrate all the concepts we are learning. However, if this kind of a database was to be modeled in a real-world system, it would contain many more entities, more complicated structures, more complex relationships, etc. Taking into consideration all of the details when modeling a relational database is no simple task. For now, we will be using a basic model: a university center can have multiple universities, which are identified by ther names. Some other information we are storing about a university is its address and telephone number. Each university can have multiple faculties, and each faculty can have multiple departments. A department contains classes, and each class is taught by a teacher. In the model, you can see that Teacher is a specialization of the Person entity, and so is a Student (Generalization/Specialization is a concept we have learned about in ... ). A person is identified by an ID, and other stored data is the person's name, address and date of birth. This automatically means that students and teachers are also identified by ther ID, and all of the mentioned attributes are also attributes of their entity types, since they are "inherited" from the Person entity type. A student can be enrolled in multiple classes, and take exams. An important thing to notice in this model is that Exam is a weak entity type. It is uniquely identified not only by the date, but also with the primary key of the Class entity type. This means that the primary key of the weak entity type Exam is a combination of the exam's date and the class's ID. This is logical and intuitive, since exams in different classes can take place the same day.
This was a brief explanation of the diagram, which should be enough to understand all of the concepts used in this model. We recommend trying to create your own simple model and its diagram using a tool of your choice, because in the next couple of articles we are going to move on, and introduce the SQL language. Following is an image of our university center database model: