As we said in the introductory article, correct and detailed modeling of our database is one of the most important segments of developing a database information system. The goal is to develop a detailed specification of the structure of our database, having in mind the DBMS we are going to be working with. The logical schema of the database must depict the natural properties of real-world objects and their relationships. A badly modeled database can be a source of multiple problems later on, and might depand large-scale and expensive modifications. This is particularly true for a complex system with thousands of different data sets to be taken into account. Having said all of this, it is clear that we need a systematic approach to database modeling. One of such approaches is developing a conceptual model first—a model close to the human perception of the real world, utilising a high level of abstraction. This is the approach that we will be using.
One of the most common high-level conceptual models is the so-called Entity-Relationship model. A significant advantage of this model is the fact that it is very easy, using a set of simple rules, to generate a relational schema of the modeled database. This is important because today, the most prominent DBMSs are based on the relational paradigm.
Entity-Relationship Model 1/3
If we analize a real-world system, we can distinguish between certain groups of entities: objects, people, concepts, events etc. Each entity represents something which exists as a whole, and is different from other entities in the system. For example, entities in a university information system could be faculties, professors, students, classes, departments etc. The next thing we can notice is that between these entities, there are certain relationships: a professor can teach multiple classes, a student can attend multiple classes but only be enrolled at one department etc. This kind of reasoning is the basis of the entity-relationship conceptual model. We will be representing this model in the ERwin data modeler, using E-R (entity-relationship) diagrams.
Before we get into creating a diagram, we need to explain the entity-relationship model in more detail. Like we said already, an entity is something which exists in the real world, physically or conceptually. Each entity is characterized by certain properties, called attributes. Each property of an entity is described by a quantitative value of a certain attribute, for example, an entity called Student can have an attribute named FirstName, and the value of that attribute can be John or Sarah. Some attributes can (or should) have unique values, like ID numbers, different identifiers etc. These attributes are called primary keys, and this is a very important concept, but we will return to primary keys (and other kinds of keys) later.
Now, let's say a bit more about attributes. Attributes should always be named in a manner which makes it easy and intuitive to understand the attribute's purpose. Attributes, depending on the type, have a set of allowed values, and this set is called the attribute's domain.
There are different kinds of attributes:
- Atomic/Composit attributes - we say that an attribute is atomic when it has values which are unseparable, while a composit attribute's values consist of smaller parts. For example, an address can be considered a composit attribute because it consists of street name, number, city etc.
- Single-valued/Multivalued attributes - single-valued attributes can only have one value for one entity, while multivalued attributes can have multiple values for one entity. For example, a person can have multiple phone numbers, which would make the PhoneNumber attribute in the Person entity multivalued.
Another important thing to say about attributes is that some of them can have the null
value. This is a special value, it can be a part of any domain, and it means that the attribute's value is either unknown or inapplicable. DBMSs treat null
values differently, depending on the implementation, but MySQL (which we will be using) considers each null
value to be unique. In the following articles we will continue to learn about the entity-relationship model, attributes, relationship constraints, mapping cardinality etc., and we will apply that theoretical knowledge on building a simple model of a university.