The concepts of entity and table are the core concepts of the databases we are going to study, the so-called relational databases.
1. Entity
An entity is a data model. An entity is thus a representation of reality.
Let’s have an example. Assume we’d like to manage data about employees. Before storing the actual data it must be decided which data are meaningful to be stored. On this purpose a data modeling must first be done. Company A may decide to store data illustrated in Figure 2.1 whereas company B has simpler needs as Figure 2.2 shows. There are actually infinitely many models about data employees.
Figure 2.1 : model A
Figure 2.2 : model B
2. Table
Once data modeling has been done, the database must be created (implemented). Entities are then materialized by tables. The following table corresponds to the Employee entity shown in Figure 2.1. A table row (regardless of the header) is called a record (also entry).
| {{h}} |
|---|
| {{r}} |
Some records of the table above have a NULL value regarding the attribute End. SQL speaking this value is either unknown yet or inexistent. In the present case, some employees are still part of the company and therefore no end date is recorded for them.
The attribute ID (identifier) is a special one. It is the primary key of the table. Its role is to make possible the distinction of records within the table. The Department_id attribute is also a special one. It is a so-called foreign key and plays the role of a connector to another table. In the present case, it links to the department each employee works in.
3. Database schema
A database schema represents all tables of a database as well as the relationships between them. The database schema introduced in the previous chapter depicted in Figure 2.3 contains four tables, namely Employee, Department, Project and Involvement. Each of them has their own attributes (First_name, Last_name, …). Primary keys are underlined. Associations between two tables are depicted by an arrow.
Figure 2.3 : database schema
Recap
Figure 2.4 : Design (source platinumtraining.org)
Figure 2.5 : Implementation (source haskell.com)
In database development two separate phases have to be distinguished.
The first one is about what data are relevant according to the needs and how data are related one from another. This phase is called the design. The modeling of entities, their attributes and relationships between entities is part of it. That phase illustrated in Figure 2.4 is beyond the scope of the present course.
The second one is about actual data. This phase illustrated in Figure 2.5 is called the implementation and then each entity is materialized by a table. Table rows are called records. The primary key makes them distinguishable each other. The present course focuses on that second phase.