05 – The Relational Database Model Part II

1.1 Introduction 

In this section we will look at the relational model again and we will learn how to represent a system using an ER diagram.

As we said earlier, we look at everything as entities. The Entity – Relationship model explains how each of these entities relates to each other.

There are different kinds of attributes

Simple vs. Composite    - the customary definition for simple attribute is that it has atomic values, i.e. the one that cannot be divided any further. The composite attributes are made of one or more simple attributes – for example, your age cannot be divided any further whereas an address can be divided into number, street name, city, state, zip code etc. Address is a composite attribute.

Derived – the attributes that can be derived from existing simple and composite attributes.

Single valued vs. Multivalued – In single valued attribute there is only one value in the attribute. In Multivalued attribute you can keep many values for the same attribute. Customer’s date of birth will always have a single value. Now don’t get confused with the fact that DOB has year month and day. Still, it is one date. Whereas if you consider the days you have gone to Wal-Mart it will be many. This understanding is crucial when it comes to relationships (see below) and in understanding normalization. 

Relationship Types: Cardinality

Cardinality is how many of a particular entity are associated with the other entity. For example, one customer has one social security number. Base on the cardinality the following categories exist. There are different types of relationships.

One to one

– the best way to explain this is by way of an example.

One customer will have only one social security number. One social security number belongs to only one customer. So the relationship between customer and social security is one to one. Remember it has to be both ways customer to SCN and SCN to customer.

     

 

One to Many

This is the relationship that you will come across frequently in system design. One customer has many bank accounts. One bank account belongs to only one customer. So the relationship between customer and bank account is a one to many relationship.

       

Many to Many

This is a complex relationship. One customer deals with many banks and one bank deals with many customers.

            

 

Note: in case of many to many relationships you don’t leave them as they are. You always put normalize them and bring them to one to many relationship. More on this later.

Er Diagram

Typically you represent an entity and attribute like below – entities in boxes, attributes in ovals.

      

 

Note: However you must remember that there are other ways of representing. Here is one more modern way

       

When you are representing many entities you represent like the one shown below (already given above):

      

Sometimes you can also specify the cardinality using the following notation

                    

 

In representing a system you must remember that a customer may or may not have a bank account. Well, if you are designing a system for the banks he always has a bank account otherwise he is not a customer in the first place, but if you are designing a system for the an supermarket where some corporate customers may want you to debit their bank accounts rather than sending a check then – there could be customers without bank accounts. Because not all customers would want to do that.

So to depict this – optionality – you use the following notation:

     

 

It is sometimes necessary to put the description of the relationship on top of the relationship line.

 

      

So in the above line One customer ‘has’ many bank accounts.

Here is a complete example from a retail system.

            

In this ER diagram there are 4 entities.

1. One customer can place one or more Orders; and one Order can be placed by one and only one customer.

2. One order has many products; and one product can be in many orders;

3. One product can be in many depots and one depot can have many products.

Not on Naming: It is customary to name the entities as singular nouns – Product NOT Products

Like us on Facebook