ER-diagram to Relational - PowerPoint Presentation

Slide1

ER-diagram to Relational

Mapping

An ER-diagram is prepared by the designer to capture a real world situation and to give a pictorial representations of the interaction between the entities. Later the ER-diagram need to be mapped to the relational mode. To explain the process, the example taken is that a library issues books to the members. For simplicity, two entities members and books are taken. Diagrammatically it can be presented as:-

The designer proceeds with the mapping, preserving as many integrity constraints as possible. There are three steps to be taken in the process:-

Conversion of entities,

Conversion of relationships, and

Conversion of multivalued attributes.

Student Mem_Id First_Name Last_Name Type Mem_Address

ENTITY CONVERSION:-

The entity sets are translated into relations(tables). The attributes of entity become the attributes of the relations. Any key attribute is assigned a primary key. In the above example, there will be two tables(Relations).

During this conversion all non-atomic attributes are converted into atomic attributes. For example, if Student Name contains first name and last name then student name should be replaced with atomic attributes.

The relation will be re-written as:-

In case of

weak entities, an entity which cannot be uniquely identified by its attribute alone, the Primary Key of the parent table or owner table must be included as a Foreign Key, in the relation. The other conversions remain similar to the regular entities.

CONVERSION OF RELATIONSHIP:- The relationship sets between the entities are translated into tables(relations). This table assumes the attributes of the relationship set and the key attributes of participating entities. To continue with the example of the library, the relationship between the entities is borrowing of books and the key attributes of participating entities are Book_Id and Mem_Id.

An ‘Issue’ table will be created as given below:- Composite Key [ NOTE- Weak entity set and identifying

relationship sets are translated into a single table.

Issue

Mem_Id

Book_Id

Date_Issue

Date_Return

CONVERSION OF MULTI-VALUED ATTRIBUTES:-

To convert multi-valued attributes into single value attributes, a new relation (table) is created which contains the attribute and the Primary Key of the relation.

For example, the sports team of a college has more than one player (data value). In this case, the table will have the following attributes.

SPORTS_TEAM

Team_members

Team_Id

Team_Name

Multi-valued attribute

(As the number of members is more than one).

The ER-Diagram

Single Value Attributes

Normalization of Relational Database

Normalization is the process of efficiently organizing data. It is the process adopted to structure the data in a manner which removes or controls data redundancy. It contains a set of rules which are concerned with:-

Identifying relationships among the attributes,

Converting those relationships in the form of relations(tables), andCombining these relations to form a database.

It provides the following advantages:-Maintains data integrity,Optimizes query response, andImproves process of updating data.There are different stages of data normalization.

First Normal Form

(1NF) :-

The first normal form sets the very basic rules for organizing database. A relation(table)

said to be in first normal form if all the key attributes are defined attribute values are atomic and the table doesn’t contain any repeating group.There are two steps for a making table in the first normal form:-Eliminate duplicative columns from the table, and

Create separate table for related data of each group and identity each row with the primary key. The following example will explain these steps. Consider the table named ‘Transaction’ produced below to record the transactions of sale of certain products:-

Transaction_Id

Customer_Id

Product

1

Product 2

Product 3

Product 4

T1

C1

P1

P2 P3 P4 T2

C2 P5 P6

- - T3

C2 P2 -

- -

If the column (representing products) are combined to make it a non-atomic, there will be only one column ‘product’ which will be a multi-valued attribute which is not in line with first normal form.

For transaction 1 all the four columns of products are occupied, fine, but it doesn’t allow a transaction to contain more than four(4) products while transaction two and three which contain only two and one product(s) respectively are wasting the precious space of the database, for product 3 and product 4 in case of transaction 2 and the space for product 2- product 4 in case of transaction 3.

This table can be normalized into first form by eliminating the attribute (product) from the table transaction and creating a different table, say, Transaction, Product, for showing products of the transaction.

Transaction_IdCustomer-Id T1 C1

T2

C2

T3

C3

Transaction_Id

Product

T1

P1

T1

P2

T1

P3T1

P4T2P5T2

P6T3P2