Entity Relationship Diagram – ER Diagram in DBMS.pptx

er model

1. Entity Relationship Diagram – ER Diagram in DBMS An  Entity–relationship model (ER model)  describes the structure of a database with the help of a diagram, which is known as  Entity Relationship Diagram (ER Diagram) . An ER model is a design or blueprint of a database that can later be implemented as a database. The main components of E-R model are: entity set and relationship set. An ER diagram shows the relationship among entity sets. An entity set is a group of similar entities and these entities can have attributes. In terms of DBMS, an entity is a table or attribute of a table in database, so by showing relationship among tables and their attributes, ER diagram shows the complete logical structure of a database

2. The Entity-Relationship Model ER model stands for an Entity-Relationship model. It is a high-level data model. This model is used to define the data elements and relationship for a specified system. It develops a conceptual design for the database. It also develops a very simple and easy to design view of data. In ER modeling , the database structure is portrayed as a diagram called an entity-relationship diagram. For example,  Suppose we design a school database. In this database, the student will be an entity with attributes like address, name, id, age, etc.

3. Components of a ER Diagram Strong Entity

4. As shown in the above diagram, an ER diagram has three main components: 1. Entity 2. Attribute 3. Relationship 1. Entity: An entity may be any object, class, person or place. In the ER diagram, an entity can be represented as rectangles. Consider an organization as an example- manager, product, employee, department etc. can be taken as an entity.

5. Strong Entity: A strong entity is not dependent on any other entity in the schema. A strong entity will always have a primary key. Strong entities are represented by a single rectangle. The relationship of two strong entities is represented by a single diamond. Various strong entities, when combined together, create a strong entity set.   Weak Entity: An entity that depends on another entity called a weak entity. The weak entity doesn't contain any key attribute of its own. The weak entity is represented by a double rectangle.

6. 2. Attribute The attribute is used to describe the property of an entity. Oval is used to represent an attribute. For example,  id, age, contact number, name, etc. can be attributes of a student. Attribute An attribute describes the property of an entity. An attribute is represented as Oval in an ER diagram. There are four types of attributes: 1. Key attribute 2. Composite attribute 3. Multivalued attribute 4. Derived attribute

7. 1. Key Attribute The key attribute is used to represent the main characteristics of an entity. It represents a primary key. The key attribute is represented by an Oval with the text underlined.

8. 2. Composite attribute: An attribute that is a combination of other attributes is known as composite attribute. For example, In student entity, the student address is a composite attribute as an address is composed of other attributes such as pin code, state, country.

9. 3. Multivalued attribute: An attribute that can hold multiple values is known as multivalued attribute. It is represented with  double ovals  in an ER Diagram. For example – A person can have more than one phone numbers so the phone number attribute is multivalued. 4. Derived attribute: A derived attribute is one whose value is dynamic and derived from another attribute. It is represented by  dashed oval  in an ER Diagram. For example – Person age is a derived attribute as it changes over time and can be derived from another attribute (Date of birth). E-R diagram with multivalued and derived attributes :

10. 3. Relationship A relationship is represented by diamond shape in ER diagram, it shows the relationship among entities. There are four types of relationships: 1. One to One 2. One to Many 3. Many to One 4. Many to Many 1. One to One Relationship When a single instance of an entity is associated with a single instance of another entity then it is called one to one relationship. For example, a person has only one passport and a passport is given to one person.

11. 2. One to Many Relationship When a single instance of an entity is associated with more than one instances of another entity then it is called one to many relationship. For example – a customer can place many orders but a order cannot be placed by many customers. 3. Many to One Relationship When more than one instances of an entity is associated with a single instance of another entity then it is called many to one relationship. For example – many students can study in a single college but a student cannot study in many colleges at the same time.

12. 4. Many to Many Relationship When more than one instances of an entity is associated with more than one instances of another entity then it is called many to many relationships. For example, a student can be assigned to many projects and a project can be assigned to many students.

13. A simple ER Diagram: In the following diagram we have two entities Student and College and their relationship. The relationship between Student and College is many to one as a college can have many students however a student cannot study in multiple colleges at the same time. Student entity has attributes such as Stu_Id , Stu_Name & Stu_Addr and College entity has attributes such as Col_ID & Col_Name .

14. Here are the geometric shapes and their meaning in an E-R Diagram. Rectangle : Represents Entity sets. Oval : Attributes Diamonds : Relationship Set Lines : They link attributes to Entity Sets and Entity sets to Relationship Set Double Oval :  Multivalued Attributes Dashed Oval : Derived Attributes Double Rectangles : Weak Entity Sets Double Lines : Total participation of an entity in a relationship set

15. Entity set Entity set relationship We can say that Entity set A is total participation with Relationship R because all entities in entity set A participated in relation R We can say that Entity set B is partial participation with Relationship R because not all entities in entity set B participated in relation R

16. Entity-Set and Keys Key is an attribute or collection of attributes that uniquely identifies an entity among entity set. For example, the roll_number of a student makes him/her identifiable among students. Super Key  − A set of attributes (one or more) that collectively identifies an entity in an entity set.

17. Candidate Key  − A minimal super key is called a candidate key. An entity set may have more than one candidate key. Primary Key  − A primary key is one of the candidate keys chosen by the database designer to uniquely identify the entity set.

18. Relationship The association among entities is called a relationship. For example, an employee  works_at  a department, a student  enrolls  in a course. Here, Works_at and Enrolls are called relationships. Degree of Relationship The number of participating entities in a relationship defines the degree of the relationship. Binary = degree 2 Ternary = degree 3

19. Mapping Cardinalities Cardinality  defines the number of entities in one entity set, which can be associated with the number of entities of other set via relationship set. One-to-one  − One entity from entity set A can be associated with at most one entity of entity set B and vice versa.

20. One-to-many  − One entity from entity set A can be associated with more than one entities of entity set B however an entity from entity set B, can be associated with at most one entity.

21. Many-to-one  − More than one entities from entity set A can be associated with at most one entity of entity set B, however an entity from entity set B can be associated with more than one entity from entity set A.

22. Many-to-many  − One entity from A can be associated with more than one entity from B and vice versa.

23. EXTENDED E-R FEATURES-SPECIALIZATION & GENERALIZATION Specialization Generalization Aggregation

24. Specialization In specialization, an entity is divided into sub-entities based on their characteristics. It is a top-down approach where higher-level entity is specialized into two or more lower level entities. For Example, EMPLOYEE entity in an Employee management system can be specialized into DEVELOPER, TESTER etc.

25. As another example, the entity set person may be further classified as one of the following • employee . • student . Each of these person types is described by a set of attributes that includes all the attributes of entity set person plus possibly additional attributes. For example, employee entities may be described further by the attribute salary , whereas student entities may be described further by the attribute tot credits .

26. We can apply specialization repeatedly to refine a design. For instance, employees may be further classified as one of the following: • instructor . • secretary . Each of these employee types is described by a set of attributes that includes all the attributes of entity set employee . In terms of an E-R diagram, specialization is represented by a hollow arrow-head pointing from the specialized entity to the other entity .We refer to this relationship as the ISA relationship.

27. top-down approach

28. disjoint specialization two separate arrows are used. For a disjoint specialization (as is the case for student and employee as specializations of person ), Overlapping specialization (as is the case for instructor and secretary as specializations of employee ), a single arrow is used for overlapping specialization.

29. The requirement of this constraint is that an entity should not belong to no more than one lower-level entity set . For example, the entity of student entity satisfy only one condition for student type attribute i.e. Either an entity can be a graduate or an undergraduate student, but cannot be both at the same time. disjoint specialization  In this category of generalizations, within a single generalization, the same entity may belong to more than one lower-level entity set . For example, in the employee work-team assume that certain employees participate in more than one work team. Thus, it offers a given employee that he may appear in more than one of the team entity sets that are lower-level entity sets of employee.  overlapping specialization

30. Generalization Generalization is the process of extracting common properties from a set of entities and create a generalized entity from it. It is a bottom-up approach in which two or more entities can be generalized to a higher-level entity if they have some attributes in common. For Example, STUDENT and FACULTY can be generalized to a higher-level entity called PERSON as shown in Figure 1. In this case, common attributes like P_NAME, P_ADD become part of higher entity (PERSON) and specialized attributes like S_FEE become part of specialized entity (STUDENT). 

31. Multiple entity sets are synthesized into a higher-level entity set on the basis of common features is called generalization . instructor entity set with attributes instructor id, instructor name , instructor address, instructor salary, and rank. secretary entity set with attributes secretary id, secretary name, secretary address, secretary salary , and hours per week.

32. Higher- and lower-level entity sets also may be designated by the terms superclass and subclass, respectively. The person entity set is the superclass of the employee and student subclasses. Generalization is a simple inversion of specialization. Specialization stems from a single entity set; it emphasizes differences among entities within the set by creating distinct lower-level entity sets. Generalization proceeds from the recognition that a number of entity sets share some common On the basis of their commonalities, generalization synthesizes these entity sets into a single, higher-level entity set.

33. Constraints on Specialization and Generalization: Condition-defined and User-defined: condition-defined: lower-level entity sets, membership is evaluated on the basis of whether or not an entity satisfies an explicit condition Eg :- Only those entities that satisfy the condition studenttype = “graduate” are allowed to belong to the lower-level entity set graduate student. This type of generalization is also known as attribute-defined. User-defined: lower-level entity sets are not constrained by a membership condition; rather, the database user assigns entities to a given entity set. For instance, let us assume that, after 3 months of employment, employees are assigned to one of four work teams. A given employee is not assigned to a specific team entity automatically on the basis of an explicit defining condition.

34. 2. Disjoint and Overlapping: A disjoints constraint requires that an entity belong to no more than one lower-level entity set. In our example, student entity can satisfy only one condition for the student type attribute; an entity can be either a graduate student or an undergraduate student, but cannot be both. In overlapping generalizations , the same entity may belong to more than one lower-level entity set within a single generalization. For an illustration, consider the employee work-team example, and assume that certain employees participate in more than one work team. Thus, the generalization is overlapping

35. 3. Completeness Constraint: The completeness constraint on a generalization or specialization, specifies whether or not an entity in the higher-level entity set must belong to at least one of the lower-level entity sets within the generalization/specialization. This constraint may be one of the following: Total generalization or specialization : Each higher-level entity must belong to a lower-level entity set. Partial generalization or specialization: Some higher-level entities may not belong to any lower-level entity set. Partial generalization is the default. The student generalization is total: All student entities must be either graduate or undergraduate.

36. Aggregation Aggregration is a process when relation between two entities is treated as a  single entity . In the diagram above, the relationship between  Center  and  Course  together, is acting as an Entity, which is in relationship with another entity  Visitor . Now in real world, if a Visitor or a Student visits a Coaching Center , he/she will never enquire about the center only or just about the course, rather he/she will ask enquire about both.

37. Aggregation example an Employee working on a project may require some machinery. So, REQUIRE relationship is needed between the relationship WORKS_FOR and entity MACHINERY. Using aggregation, WORKS_FOR relationship with its entities EMPLOYEE and PROJECT is aggregated into a single entity and relationship REQUIRE is created between the aggregated entity and MACHINERY.