Introduction to Entity Relationship Model

Unit: II: Database Design 

Syllabus

Entity-Relationship model - E-R Diagrams - Enhanced-ER Model - ER-to-Relational Mapping - Functional Dependencies Non-loss Decomposition - First, Second, Third Normal Forms, Dependency Preservation - Boyce/Codd Normal Form Multi-valued Dependencies and Fourth Normal Form - Join Dependencies and Fifth Normal Form.

Part I: Entity Relationship Model

Introduction to Entity Relationship Model

Entity Relational model is a model for identifying entities to be represented in the database and representation of how those entities are related.

Let us first understand the design process of database design.

Design Phases

Following are the six steps of database design process. The ER model is most relevant to first three steps

Step 1: Requirement analysis:

• In this step, it is necessary to understand what data need to be stored in the database, what applications must be built, what are all those operations that are frequently used by the system.

• The requirement analysis is an informal process and it requires proper communication with user groups.

• There are several methods for organizing and presenting information gathered inthis step.

• Some automated tools can also be used for this purpose.

Step 2: Conceptual database design:

• This is a steps in which E-R Model i.e. Entity Relationship model is built.

• E-R model is a high level data model used in database design.

• The goal of this design is to create a simple description of data that matches with the requirements of users.

Step 3: Logical database design:

• This is a step in which ER model in converted to relational database schema, sometimes called as the logical schema in the relational data model.

Step 4: Schema refinement:

• In this step, relational database schema is analyzed to identify the potential smise problems and to refine it.

• The schema refinement can be done with the help of normalizing and restructuring the relations.

Step 5: Physical database design:

• In this step, the design of database is refined further.

• The tasks that are performed in this step are building indexes on tables and clustering tables, redesigning some parts of schema obtained from earlier design steps.

Step 6: Application and security design:

• Using design methodologies like UML (Unified Modeling Language) the design of the database can be accomplished.

• The role of each entity in every process must be reflected in the application task.

• For each role, there must be the provision for accessing the some part of database and prohibition of access to some other part of database.

• Thus some access rules must be enforced on the application(which is accessing the database) to protect the security features.

ER Model

The ER data model specifies enterprise schema that represents the overall logical structure of a database.

The E-R model is very useful in mapping the meanings and interactions of real-world entities onto a conceptual schema.

The ER model consists of three basic concepts -

1) Entity Sets

• Entity: An entity is an object that exists and is distinguishable from other objects. For example - Student named "Poonam" is an entity and can be identified by her name. The entity can be concrete or abstract. The concrete entity can be - Person, Book, Bank.The abstract entity can be like - holiday, concept entity is represented as a box.

• Entity set: The entity set is a set of entities of the same types. For example - All students studying in class X of the School. The entity set need not be disjoint. Each entity in entity set have the same set of attributes and the set of attributes will distinguish it from other entity sets. No other entity set will have exactly the same set of attributes.

2) Relationship Sets

Relationship is an association among two or more entities.

The relationship set is a collection of similar relationships. For example - Following Fig. 2.1.2 shows the relationship works for for the two entities Employee and Departments.

The association between entity sets is called as participation. That is, the entity sets E1, E2,..., En participate in relationship set R.

The function that an entity plays in a relationship is called that entity's role.

3) Attributes

Attributes define the properties of a data object of entity. For example if student is an entity, his ID, name, address, date of birth, class are its attributes. The attributes help in determining the unique entity. Refer Fig. 2.1.3 for Student entity set with attributes - ID, name, address. Note that entity is shown by rectangular box and attributes are shown in oval. The primary key is underlined.

Types of Attributes

1) Simple and Composite Attributes:

1) Simple attributes are attributes that are drawn from the atomic value domains

For example - Name = {Parth}; Age = {23}

1) Composite attributes: Attributes that consist of a hierarchy of attributes For example - Address may consists of "Number", "Street" and "Suburb"→ Address = {59+ 'JM Road' + 'Shivaji Nagar'}

2) Single valued and multivalued:

• There are some attributes that can be represented using a single value. For example

- StudentID attribute for a Student is specific only one studentID.

• Multivalued attributes: Attributes that have a set of values for each entity. It is represented by concentric ovals

For example - Degrees of a person: BSc', 'MTech', 'PhD'

3) Derived attribute:

Derived attributes are the attributes that contain values that are calculated from other attributes. To represent derived attribute there is dotted ellipse inside the solid ellipse. For example Age can be derived from attribute DateOfBirth. In this situation, DateOfBirth might be called Stored Attribute.