Intro to Database Systems - Part 2 : The Entity-Relationship Model

@dandancrisan

This page will cover the following keywords : E/R diagram, E/R schema, entities, their attributes, entity sets, keys, ISA hierarchies, overlap constraints, covering constraints (specialization, generalization), relationships, relationship sets, key-constraints (many-to-many, one-to-many, many-to-one, one-to-one), participation constraints (partial / total). You should be able to explain them in your own words at the end of this page.







Before we start, let’s clarify what “Entity-Relationship Model” is. In a nutshell, the ERM is a “GUI for DBs” : a graphical representation of the requirements for a database. Within this lecture, we will notice it under different names, but they all mean the same:

A little confusion might occur between Entity-Relationship schema and relational schema. The relational schema refers to the schema of the relation (in red) discussed in the last post. The relational schema describes the columns in a database table.

36w4dnmjjac6yg.png

On the other hand, an Entity-Relationship schema is only the graphical representation of the entities and their relationship. It has to be :

Hence, a relational schema is NOT a synonym of a semantic schema. The first is a table, the second is a diagram.

Following a process to design a database makes it easier to come up with the relational schema. The following are the 3 steps in database design:

The requirements can be summarized to 2 questions :

The requirements are often mentioned in plain text. They contain data and their functionality.

An example of requirements of a database is a db of courses. The different courses offered during the winter semester are the data. The functionality will be the fact that students can register (class is not full yet), courses get canceled, TA is assigned.

Another example of requirements is a database of books. Titles, prices, authors are the data. The fact that a book might be discounted, out of stock or lost is the functionality.







Now that we’ve got an essence of the design process of the DB, let’s dig in into more precise vocabulary. We’ve already defined a few of those:







An example of an ISA (is a) hierarchy is a database where we have employees as full time and also as contractual. We say an employee is a hourly employee. Another example is a database of living things. A living thing is a plant, but we also have living things as animals in the db. An animal is a fish, but we also have birds. A bird is a land-bird, but we also have aquatics birds. An aquatic bird is a great-blue-heron.

If an entity A is also an entity B, the key is only in B, the superclass.

We have 2 types of constraints on ISA hierarchies:

We implement ISA Hierarchies because we want to:

ISA Hierarchies also have 2 important properties:







Weak entities are entities that can only exist when owned by another one. A ROOM can only exist in a BUILDING. A QUESTION is a strong entity and an ANSWER is a weak entity because the answer needs a question to exist.

db2xml.png







A relationship is an association between two or more entities.

A relationship set is a collection of similar relationships: “increase”, “decrease”, “maintain” are all relationships part of the set “progression”.

Relationships are described by :



A relationship type can be classified by the number of entity types involved:




A relationship type also has a multiplicity constraint (key constraint), which is the maximum and the minimum elements allowed in the set.

In the same time, many entities (in our case of type B) are related to one entity (in our case of type A). We call this many-to-one and we depict it with an arrow from the element that’s not unique (in our case of type B) to the element that’s unique (in our case of type A).
related.png



We depict it with an arrow from both elements.

export.png


In the case where we have a relationship between products and invoice, to generally derive a one:many relationship we should ask those 2 questions, both starting with the keyword one:

In the case where we have a relationship between customers and products, to generally derive a many-many relationship, we should ask those 2 questions:

In the case where we have a relationship between a birth certificate and its owner, to generally to derive a one-to-one relationship, we should ask those 2 questions:




Another constraint that relationships have are participation constraints. The participation can be total or partial. An example of total participation is the fact that every department has a manager. This is represented by a thick line. An example of partial participation is the fact that not every employee is a manager.








As design principles, we have to make sure to :

We need to make sure that we have the correct structure in place:

That’s all folks!
Thank you for reading,
@dandancrisan

 
158
Kudos
 
158
Kudos

Now read this

Intro to Database Systems : Basic Perspectives on Disk and Buffer Management

The Database Management System stores information at 3 levels of the memory hierarchy: Primary storage - main memory (and cache) : for currently used data, it is fast and usually volatile. Secondary storage - magnetic (“hard”) disk : for... Continue →