Entity Relationship Diagrams (ERDs) Presented By- Sandilian A.
Agenda
1. Why we need ERD? 2. Information about ERD 3. ERD Notations 4. Methodology of ERD 5. Example for ERD
Why we need ERD ? •ERDs are a major data modelling tool. •Help to organize the data in your project into entities and define the relationships between the entities •This process has proved to enable the analyst to produce a good database structure •The data can be stored and retrieved in a most efficient manner
Information about ERD
•
Entity & Entity Sets
•
Relationship & Relationship Sets
•
Attributes
Entity & Entity Sets
Entity An entity is an object that exists and is distinguishable from other objects. An entity may be concrete (a person or a book, for example) Or abstract (like a holiday, a concept, an idea or convenience). Example: John with NO. 001-111-10001 is an entity, as he can be uniquely identified as one particular person in the universe.
Entity & Entity Sets
Entity Sets An entity set is a set of entities of the same type (e.g., all persons having an account at a bank). Example: All employees of a company may constitute an entity set employee All departments may belong to an entity set Dept
Relationship & Relationship Sets Relation Ship A relationship captures how two or more entities are related to one another A data relationship is a natural association that exists between one or more entities Example: One person (P1,P2,P3,P4) can sit on only one chair at any point of time.
Relationship & Relationship Sets Relation Ship Sets A relationship set is a set of relationships of the same type. Example: A person can sit on several chairs at different point of time.
Relationship & Relationship Sets Type of Relationship 1. One to One Relationship 2. One to Many Relationship 3. Many to One Relationship 4. Many to Many Relationship
Type of Relationship 1.One to One Example: One person (P1,P2,P3,P4) can sit on only one chair at any point of time. And also one chair (C1,C2,C3,C4) can accommodate a maximum of one person at any given time.
Type of Relationship 2.One to Many Example: One organization(O1,O2,O3) can have many employees but one employee (E1,E2,E3,E4,E5) can work only for one organization.
Type of Relationship 3.Many to One Example: Many employees (E1,E2,E3…) can work for only one department but one department (D1,D2,D3) can have many employees.
Type of Relationship 4.Many to Many Example: One student (S1,S2,S3,S4) is enrolled for many courses (C1,C2,C3,C4) and one course is enrolled by many students
Attributes
Attributes Each entity instance in an entity set is described by a set of attributes that describe their qualities, characteristics or properties that are relevant and the values of the attributes. Example An employee in an employee entity set is likely to be described by its attributes like employee number, name, date of birth, etc.
ERD Notations An entity is an object or concept about which business user wants to store information. A weak entity is dependent on another entity to exist Example : Bank branch depends upon bank name for its existence. Without bank name it is impossible to identify bank branch uniquely. Attributes are the properties or characteristics of an entity. A key attribute is the unique, distinguishing characteristic of the entity. For example, an employee’s employee number might be the employee’s key attribute.
A derived attribute is based on another attribute. example: An employee’s monthly salary is based on the employee’s basic salary and house rent allowance. A multivalued attribute can have more than one value. Example: An employee entity can have multiple skill values. Relationships illustrate how two entities share information in the database structure.
Methodology of ERD
1. Identify Entities
Identify the roles, events, locations, tangible things or concepts about which the end-users want to store data.
2. Find Relationships
Find the natural associations between pairs of entities using a relationship matrix.
3. Draw Rough ERD
Put entities in rectangles and relationships on line segments connecting the entities.
4. Fill in Cardinality
Determine the number of occurrences of one entity for a single occurrence of the related entity.
Methodology of ERD 5. Define Primary Identify the data attribute(s) that uniquely identify Keys
one and only one occurrence of each entity.
6. Draw KeyBased ERD
Eliminate Many-to-Many relationships and include primary and foreign keys in each entity.
7. Identify
Name the information details (fields) which are essential to the system under development.
Attributes 8. Map Attributes
For each attribute, match it with exactly one entity that it describes.
9. Draw fully attributed ERD
Adjust the ERD from step 6 to account for entities or relationships discovered in step 8.
10. Check Results
Does the final Entity Relationship Diagram accurately depict the system data?
Example for ERD A company has several departments. Each department has a supervisor and at least one employee. Employees must be assigned to at least one, but possibly more departments. At least one employee is assigned to a project, but an employee may be on vacation and not assigned to any projects.
Example for ERD
The important data fields are the names of the departments, projects, supervisors and employees, as well as the supervisor and employee number and a unique project number.
Sample ERD
Thank You