OBSERVATION

When designing ER diagrams, we often happen to ask ourselves "Is this an attribute or an entity?"

In this lesson we will learn how to answer this question.

Let us start from the task about the library that we studied in our previous lesson - slightly modified.

Task

Suppose you want to create a small database for a library.
For each book we want to keep track of its title, author, publisher, genre and publication year. For all authors you also want to keep track of the following details: name, surname, sex, whether they are living or not and - if alive - how old they are.

Analysis - 1

The first question to ask is "Is the library an entity?"

The answer is "no". But why?

If the table which points to a specific entity has only one row, then we can say that we are not dealing with an entity, after all.

Analysis - 2

Let us start from our previous ER diagram (without a publisher).

Is the attribute sex of author really an attribute or is it perhaps an entity?
And what about the attribute genre of book? Is it an attribute or an entity?

When - as in this case - the attribute is an attribute domain the answer is easy: if we can easily list all its potential values we are dealing with an attribute; otherwise, we are dealing with an entity.

In our example, the attribute sex of author can reasonably have only the two values "Male" and "Female" which will be added very easily to our database.

As for the attribute genre of book it would not be just as easy to add all possible genres. Therefore, it seems more reasonable to treat genre as a separate entity.

It seems to follow that we could write - for instance - the word "Novel" in our database just once and not as many times as the novels stored in our database, thus avoiding redundancy and inconsistency.

This is no doubt a better solution than the one we suggested in lesson 5.
What about the publisher?

Is publisher an attribute of book? Or rather a distinct entity?

Our previous comment (the possibility of storing a certain publisher only once) points towards the creation of a new entity. Moreover, this solution seems to be the best one because it also allows us to add a number of details to each publisher.

concePTUAL DATA MODEL: er

Logical data model: relational

author (id, name, surname, sex, DateOfBirth, alive)

genre (id, description)

publisher (id, name, group-, website-)

book (id, title, year, idAuthor, idgenre, idPublisher)

Analysis - 3

Can we apply the same reasoning we used for publisher and genre to year as well?

The answer is no for two reasons.
First we need to consider the fact that storing a number value which contains a year takes up the same amount of space as storing a foreign key. Second, inserting a string value may lead to a number of errors, whereas inserting a number is much easier. In other words, it is easier for the user to make a mistake when writing one or more words than when writing a number.

Naturally, if it is appropriate and convenient also a number can be treated as an entity.

Challenge - 1

Suppose you want to keep track of the authors' nationalities.

Design an ER diagram and a relational schema.

Challenge - 2

Suppose you want to store also the following details about the books: ISBN code, price and - if existing - the series the book belongs to.

Design an ER diagram and a relational schema.

Challenge - 3

Consider the task about the items of clothing of lesson 2. Was it correct to treat type of the item of clothing as an attribute?

Answer the question and, in case, design a new ER diagram and relational schema.

Challenge - 4

Consider the task about the address book of lesson 3. Was it correct to treat the title of contact as an attribute? What about the postal code? And the city? And the province?

Answer the question and, in case, design a new ER diagram and relational schema.

Challenge - 5

Consider the task about the hotel guests of lesson 4. We should probably examine again the attributes type and common of the entity document. We should probably also re-examine postal code, city and province of the entity guest.

Make your observations and, if appropriate, design a new ER diagram and relational schema.