CIS 420: Database Management System
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.
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:
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.
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.
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:
What kind of entities are involved in such a database? Possible list may include the following:
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:
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.
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.