Entity Relationship Diagram ERD Background Peter Chen developed

Entity Relationship Diagram ERD

Background • Peter Chen developed ERDs in 1976. • Charles Bachman and James Martin have added some slight refinements to the basic ERD principles.

ER Model • An entity-relationship model (ERM) is a conceptual way of showing data relationships in software development. • ERM is a database modeling technique that generates an abstract diagram or visual representation of a system's data that can be helpful in designing a relational database.

Components of ER model • Entity • Relationship • Attribute

Entity • Entities are objects or concepts that represent important data. They are typically nouns, e. g. customer, supervisor, location or promotion. • Strong entities exist independently from other entity types. They always possess one or more attributes that uniquely distinguish each occurrence of the entity. E. g. student • Weak entities depend on some other entity type. They don't possess unique attributes (also known as a primary key) and have no meaning in the diagram without depending on another entity. E. g. course

Representation in ERD • Entities are represented by rectangles.

Attribute • Attributes are characteristics of either an entity, a many-to-many relationship, or a one-to-one relationship. • Multivalued attributes are those that are capable of taking on more than one value. E. g. phone number • Derived attributes are attributes whose value can be calculated from related attribute values. E. g. age • Composite attributes are those that can be further subdivided. E. g. name, address

Representation in ERD • Attributes are represented by means of ellipses.

Relationship and Cardinality • Relationships, which represent the link between different entities. 1. One to one 2. One to many 3. Many to one 4. Many to many • Cardinality is the number of instance of an entity from one relation that can be associated with the other relation.

Representation in ERD • Relationships are represented by diamond-shaped box. • Name of the relationship is written inside the diamond-box. • All the entities (rectangles) participating in a relationship, are connected to it by a line.

One to one • One-to-one − When only one instance of an entity is associated with the relationship, it is marked as '1: 1'. • E. g. an EMPLOYEE is assigned one PARKING_PLACE, and each PARKING_PLACE is assigned to one EMPLOYEE

One to many • When more than one instance of an entity is associated with a relationship, it is marked as '1: N'. • E. g. a PRODUCT_LINE may contain many PRODUCTS, and each PRODUCT belongs to only one PRODUCT_LINE

Many to many • The following image reflects that more than one instance of an entity on the left and more than one instance of an entity on the right can be associated with the relationship. • E. g. a STUDENT may register for more than one COURSE, and each COURSE may have many STUDENTS

Representation I

Representation II

Representation III

Representation IV

How to create an ERD? 1. 2. 3. 4. 5. Identify entities Identify relationships Describe the relationship Add Attributes Complete the diagram

Step 1 • The first step in making an ERD is to identify all of the entities you will use. • An entity is nothing more than a rectangle with a description of something that your system stores information about. • This could be a customer, a manager, an invoice, a schedule, etc. • Draw a rectangle for each entity you can think of on your page.

Step 2 • Look at two entities, are they related? • If so draw a solid line connecting the two entities.

Step 3 • How are the entities related? • Draw an action diamond between the two entities on the line you just added. • In the diamond write a brief description of how they are related.

Step 4 • Any key attributes of entities should be added using oval-shaped symbols.

Step 5 • Continue to connect the entities with lines, and adding diamonds to describe each relationship until all relationships have been described. • Each of your entities may not have any relationships, some may have multiple relationships. That is okay.

Sample ERD

Exercise ssn • What can you say about policy of the bank from the ER diagram? name Cust. Acct Customer ssn name acct# addr balance Account lot Employees • What can you say about the policy of the company? Manages Dept deptid budget

EXERCISE Draw the ERD for the following scenarios: • A player plays for a team. • Each patient has one or more patient histories; each instance of patient history belongs to one patient. • An employee may be recorded as having many jobs; a particular job may be recorded as having been held by many employees. • A person is a citizen of a country.

Case Study • Odeon Cinema and other international cinemas have decided to install a centralized database. This database should keep information regarding cinemas including its name, address and phone number. Each cinema must have one or more theaters and each theater has a specific showing time. During these showing times a movie is shown to the general public

Entities • • CINEMA THEATER SHOW MOVIE

Relationships • A CINEMA must contain one or more THEATERS. A THEATER must belong to one CINEMA only. • A THEATER has one or more SHOW times. A particular SHOW time for a movie must belong to one THEATER only • A MOVIE can have many SHOW times and a particular SHOW time at a THEATER must belong to one MOVIE only.

Attributes CINEMA • Cinema_ID • Cinema_Name • Cinema_Address • Cinema_Phone THEATER • Theater_ID • Theater_Capacity SHOW • Show_Time • Show_Date • Show_Attendence MOVIE • Movie_ID • Movie_Title • Movie_Director • Movie_Rating

ENTITY RELATIONSHIP DIAGRAM

Practice • • • Library Management System School Management System Hospital Management System Bank Management System Transport Management System

ERD Tools • • • RISE Editor ER Studio ERwin Smart Draw Rational Rose Magic Draw