CIS 420: Database Management System

Notes No. 4
Entity-Relationship Model

Suppose you are thinking of developing a library database using the Relational Database principles. How do you get started? The first step is to use a technique called entity-relationship (ER) model to develop an informal organization of tables.

Terminology

First, let us become familiar with the terminology related to the ER model. An entity refers to a general object that makes up the database. In our personal library database, we might identify such as entities as books, authors, and publishers. If our library is large, we might add rooms and shelves where the books are stored. The database developer needs to decide what kinds of data need to be maintained in the library database. Regarding books, we want to store data regarding title, ISBN, and price. We might also want to know the genre of each book--wester, romance, biography, etc., and whether or not it is a paperback or hardcover. These properties of each book are called attributes of the book entity. An entity class is the description for a particular entity, i.e., the name of the entity and all its attributes. An entity object is an instance of the entity class, with particular attribute values. Thus, the book entity class can be described as:

Books (Title, ISBN, Price)

while a book object entity can be described as:

Title: Gone With the Wind
ISBN: 0-12-345678-9
Price: $20.00

Just as in Object-Oriented programming, an entity class is a generic, but precise description of an entity, while an entity object is a description of a specific instance of the entity class. While working with the ER model, it is quite sufficient to concentrate on the entity class description, without worrying about specific instances of individual books

In our database, we can begin with the description of three (simple) entity classes as follows.

  • Books(title ISBN, price)
  • Authors (auName, auPhone, auID)
  • Publishers(pubName, pubPhone, pubID)

You may be wondering where the auID attribute in Authors entity and pubID attribute in Publishers are coming from. Generally speaking, an attribute of an entity has three main purposes.

  • To include information that we want to include in the database. Thus, title, ISBN, and price give us information about a book. In Authors entity, auName and auPhone give us information about an author. In Publishers entity, pubName and pubPhone give us information abut a publisher.
  • To identify each individual entity uniquely within a given entity class. Thus, in Books entity class, ISBN is an attribute that distinguishes each book uniquely, even though there might be duplicates in title or price. In Authors entity, auID serves the same purpose, as does pubID in Publishers entity.
  • To describe relationships between different entity classes. Such an attribute might not add additional information about the entity, but helps to link one entity to another. This aspect will become clearer when we learn how to establish relationship between entities.

You can readily see that the entity classes with their attributes translate into tables in a relational database, and the entity objects translate into individual records.

Exercise: School Database

Suppose that you are to develop a database for a school involving students and their classes so that your client can obtain answers to queries like the following:

  • Which students are in EN101-01 class?
  • Which students live in Dorm A?
  • List all instructors teaching EN101 classes.
  • Which course does John Smith take, and who are the instructors?
  • What is the office phone number of the instructors teaching the EN101-01 class?

What kind of entities are involved in such a database? Possible list may include the following:

  • Students
  • Dorms
  • Classes
  • Instructors

The next step would be to identify attributes for each entity class, so that the various queries can be ultimate answered from the database. For example, Students might need first and last names and dorm address or other address. Of course, in a more realistic database, there would be a wider variety of possible queries, and such attributes as phone numbers GPA might be appropriate attributes. On the other hand, for this particular database, a student's hair color or eye color probably are not relevant.

Exercise: Bank Account Database

Suppose that you are developing a database dealing with bank accounts and have identified the following entity classes:

  • Customers
  • Accounts
  • Employee
  • Transaction

There is a wide range of queries that can be asked by the bank teller regarding customers, their accounts, and their transactions. What are some useful attributes that can be identified for each of these entity classes?

Exercise: Automobile Dealership

Suppose that you are developing a database for an automobile dealership. Design an ER model that can answer the following queries, among others.

  • List of all cars in stock
  • What cars were sold after 1/1/2002?
  • List of cars sold by salesperson, whose SSN = 123-45-6789
  • Which salesperson sold a cars to customer June Jones whose e-mail is jjune@aol.com?

Keys and Superkeys.

Recall that one of the purposes of an attribute is to identify uniquely each entity object from all others in a given entity class. In the Books entity, ISBN serves this purpose, since each book will have an unique ISBN number value. It may be that the combination of title and price might serve to identify a particular book from all others, but this is let likely. An attribute, or a set of attributes, that uniquely identifies an object from all others is called a superkey. The superkey for the Authors entity class is auID; for the Publishers entity, it is pubID. In the Books entity class, a combination of ISBN, title, and price is also a superkey, although the last two attributes are redundant. A minimum superkey is known as a key. Sometimes, there are several possible candidates for a key. In the Books class, ISBN is one, and a combination of title and author may be another, although the second key is open to argument. The attribute that is chosen as the key in a particular entity class is called the primary key. Thus, in the library database, ISBN, auID, and pubID are primary keys in their respective classes.

Relationship between Entities

To see how entity classes (tables) are related, we can identify their relationships with an ER diagram like the one shown below. A diamond figure represents a relationship between two entity classes, with a description of the relationship noted within the figure. For example, a book is "WrittenBy" and author; A publisher is a "PublishersOf" of a book; a contributor "Contributes" to an author.

The attributes for each entity class are indicated with an oval figure, as shown in the following diagram.

Types of relationships

One other kind of information that an ER diagram conveys is the type of relationship between a pair of entity classes. The type of relationship between Publishers and Books is "1 to many (infinity symbol)." Contributors and Authors also have a "1 to 1" relationship. On the other hand, Books and Authors have a "many to many " relationship" In general, there three types of relationship possible between two entity classes.

  • One to many (or many to one): For example, one publisher can be associated with many instances of books. Viewing it another way, many books can be associated with one publishers. This is the type of relationship you would like to have in your database design.
  • One to one: For example, one contributor is always associated with one author, or vice versa. In such a relationship, it is possible to make the contributor an attribute of Authors class, if we are interested in maintaining a simple data item about the contributor--like its name. On the other hand, if we wish to maintain more elaborate information about contributors--name, phone number, email address, etc.--it would be more appropriate to define a separate Contributors entity class.
  • Many to many: For example, one book can be written by several authors, while one author can write many books. A "many-to-many" relationship should be avoided in a database design, and be substituted with a pair of "one-to-many" relationships. This technique will be discussed in a later section