ER diagram .pptxER diagram .pptxER diagram .pptx

presentation

1. Entity-Relationship Diagram Presented By : Group E Ayesha Maryam Isha Ishtiaq Maham Maqbool Syeda Ruatb Aziz

2. ERD ERD (Entity-Relationship Diagram) is a diagram that visually represents the structure of a database. It shows the entities (tables), their attributes (columns), and the relationships (connections) between them. ERDs help in designing, understanding, and organizing the data in a database system.

3. What is a Good Data Model? A good data model is a simple, clear, and organized way to represent data in a system. It ensures that: A good data model is simple. It ensures that the data model is easy to understand and manage. A good data model is essentially nonredundant. It mean No duplicate data. Information is stored only once to avoid confusion and save space. A good data model should be flexible and adaptable to future needs. You can easily add new data or make changes without disrupting the entire system.

4. Conceptual ER diagram symbols

5. Key constraints Key constraints are specific types of constraints in a database that ensure the uniqueness and integrity of key values within tables. These constraints help maintain the relationships between tables and prevent data duplication. Primary Key : Unique, not NULL, one per table. Foreign Key : Enforces referential integrity between tables. Unique Key : Ensures uniqueness, but allows NULLs and multiple per table.

6. An ERD design methodology: Step 1: Identifying Entities Step 2: Defining Attributes Step 3: Specifying Relationships Step 4: Drawing Entities Step 5: Adding Attributes Step 6: Connecting Entities Step 7: Specifying Cardinality Step 8: Organizing ER Diagram

7. 1.Identify Entities An entity is a thing that can have data stored about it. It can be a physical object (e.g. car, person), a concept (e.g. address) or an event (e.g. student enrolment in a course). They represent nouns. They are usually represented as rectangles on an ERD with the entity name inside the rectangle.

8. Entity Symbol Name Description Strong entity These shapes are independent from other entities, and are often called parent entities, since they will often have weak entities that depend on them. They will also have a primary key, distinguishing each occurrence of the entity. Weak entity Weak entities depend on some other entity type. They don't have primary keys, and have no meaning in the diagram without their parent entity. Associative entity Associative entities relate the instances of several entity types. They also contain attributes specific to the relationship between those entity instances.

9. Identifying relationship Weak entity Strong entity

10. Entities for Bank are(Example): Bank, Branch, Employee, customer, loan, account

11. Attribute Symbol Name Description Attribute Attributes are characteristics of an entity, a many-to-many relationship, or a one-to-one relationship. Multivalued attribute Multivalued attributes are those that are can take on more than one value. Derived attribute Derived attributes are attributes whose value can be calculated from related attribute values.

12. 2.Adding Attributes: Attributes are the kind of properties that describe the entities. They are represented by  ovals . Attributes for Bank are: For  Bank Entity  the Attributes   are  Bname, code . For  Branch Entity  the Attributes   are  Blocation, Bname . For  Employee Entity  the Attributes   are  Eid, Designation, salary . For  Customer Entity  the Attributes   are  Cid, Cname, Address, DOB . For  Loan Entity  the Attributes   are   Loan_no, amount, rate . For  Account Entity  the Attributes   are   acc_no , type .

13. 3.Establishing Relationships Entities have some relationships with each other. Relationships define  how entities are associated with each other .

14. Relationship Symbol Name Description Relationship Relationships are associations between or among entities. Weak relationship Weak Relationships are connections between a weak entity and its owner.

15. Strong and Weak Relationships Strong Relationship: Entities are independent of each other. Each entity has its own primary key. Represented by a single diamond in the diagram. Example: Student and Course are connected through a "Enrolls" relationship, but both can exist independently. Weak Relationship : One entity (weak entity) depends on another (strong entity) for its existence. Weak entity uses a foreign key from the strong entity to form its primary key. Represented by a double diamond in the diagram. Example: OrderLine depends on Order, as an OrderLine cannot exist without an Order.

16. Example(Continue): Let's Establishing Relationships between them are: The  Bank  has  branches . The  Branch  provides  loan . The  Employee  works in  branch . The  Branch  contains  customers . The  Customers  has  account . The  Branch  maintains  account . The  Customer   avails  loan

17. 4.Specifying Cardinality Cardinality defines the  numerical constraints  on the  relationships  between entities. It is a  notation  that tells the  ERD  reader whether there are  one ,  many  or some combination of those factors between each entity. 1. One to One relationship(1:1) In the entity relationship diagram, the  one to one  relationship means that single entity in  one table is associated with single entity in another table . For example,  one driver have only one license .

18. 2. One to Many relationship(1:N) One to many relationships means that single entity in one table is related to more than one entities in another table. For example,  one bank has many branches . 3. Many to One(N:1) Many-to-one relationship in entity relationship diagram means that multiple entities are related to only single entity in another table. For example, many developers works on single project. 4. Many to Many relationship(M:N) Many to many relationship means that multiple entities in one table is associated with multiple entities in another table. For example, multiple customers have multiple accounts.

19. Notations

20. Example(Continue): Specify cardinality for Bank: Bank  and  branch  has  One to Many  relationship (a bank has multiple branches). Branch  and  loan  has also  One to Many  relationship(a branch can provide many loans). Branch  and  employee  has  One to Many  relationship(one branch has many employees). Branch  and  account  has  One to Many  relationship(one branch has many accounts). Branch  and  customer  has  Many to Many  relationship(multiple branches have multiple customers). Customer  and  account  has  Many to Many  relationship(multiple customers have multiple accounts). Customer  and  loan  has  Many to Many  relationships(multiple customers have multiple loans)

21. 5.Identify Primary Keys Primary keys are the  unique  identifier for each record in database table. It is denoted by an underline under the attribute name. The Primary key of  Bank  is  code . The Primary key of  Branch  is  branch_code. The Primary key of  Employee  is  Eid. The Primary key of  Customer  is  Cid. The Primary key of  Loan  is  loan_no . The Primary key of  Account  is  acc_no .

22. Final ER Diagram

23. Tools for ERD Creation: A few popular tools for designing ERDs are given below EdrawMax Lucidchart Draw.io Microsoft Visio

24. Benefits of ERD 1. Visual Representation: ERDs provide a clear, graphical representation of the database structure, making it easier to understand relationships between entities. 2. Communication Tool: ERDs act as a bridge between database designers, developers, and stakeholders by simplifying complex database designs.

25. 3. Improves Database Design: Helps identify: Key entities. Attributes and relationships. Redundant data and anomalies, leading to a more efficient database.

26. 4. Supports Normalization: Simplifies the process of reducing redundancy and ensuring logical data organization during database design. 5. Detects Errors Early: ERDs allow designers to spot potential design flaws (e.g., missing relationships or weak entities) before implementation.

27. 6. Facilitates Maintenance: A well-documented ERD serves as a reference for database updates and expansions, making maintenance easier. 7. Enhances Scalability: By modeling relationships and dependencies, ERDs make it easier to scale systems by adding new features or data entities.

28. ERD Example

29. ERD Relationship with Attributes A unary relationship with an attribute. This has a many-to-many relationship

30. ERD Relationship with Attributes A ternary relationship with attributes

31. ERD Relationship with Attributes Examples of multiple relationships – entities can be related to one another in more than one way

32. ERD NOTATION STYLES

33. Chen notation is one of the earliest and most widely recognized styles for entity-relationship diagrams (ERDs). Developed by Peter Chen, this notation uses a combination of geometric shapes to represent the different components of a database schema. Chen Notation

34. An entity is represented by a rectangle which contains the entity’s name. Entity : an entity that cannot be uniquely identified by its attributes alone. The existence of a weak entity is dependent upon another entity called the owner entity. The weak entity’s identifier is a combination of the identifier of the owner entity and the partial key of the weak entity. weak entity : – an entity used in a many-to-many relationship (represents an extra table). All relationships for the associative entity should be many associative entity :

35. In the Chen notation, each attribute is represented by an oval containing atributte’s name: Key Attribute : partial key attribute (discriminator) : an attribute that uniquely identifies a particular entity. The name of a key attribute is underscored: multivalued attribute : an attribute that, when combined with the key attribute of the owner entity, provides a unique identification for the weak entity. We underline the discriminator with a dashed line: an attribute that can have many values (there are many distinct values entered for it in the same column of the table). Multivalued attribute is depicted by a dual oval: : Some attributes can be further subdivided into smaller parts. For example, the attribute “address” can be subdivided into street name, street number, apartment number, city, state, zip code, and country. These are called composite attributes : composite attribute :

36. In the Chen notation, a relationship is represented by a diamond (rhombus) containing the relationship’s name. strong relationship : a relationship where entity is existence-independent of other entities, and PK of Child doesn’t contain PK component of Parent Entity. A strong relationship is represented by a single rhombus: weak (identifying) relationship : – a relationship where Child entity is existence-dependent on parent, and PK of Child Entity contains PK component of Parent Entity. This relationship is represented by a double rhombus

37. Optionality of a relationship a mandatory relationship is represented by a solid line: An optional relationship is represented by a dashed line like in Barker’s notation: An entity set may participate in a relation either totally or partially. Total participation means that every entity in the set is involved in the relationship, e.g., each student must be guided by a professor (there are no students who are not guided by any professor). In the Chen notation, this kind of relation is depicted as a double line. Partial participation means that not all entities in the set are involved in the relationship, e.g., not every professor guides a student (there are professors who don’t). In the Chen notation, a partial participation is represented by a single line. Participation constraints

38. Example

39. Crow's Foot notation is a popular method for representing relationships in entity-relationship diagrams (ERDs). Crow's Foot notation provides a clear and structured way to visualize database relationships, making it easier to understand the interactions between entities. Crow's Foot Notation

40. In crow’s foot notation, an entity is represented by a rectangle, with its name on the top. The name is singular (entity) rather than plural (entities). Entity : An entity is a representation of a class of object. It can be a person, place, thing, etc. Entities usually have attributes that describe them. In crow’s foot notation, an entity is represented by a rectangle, with its name on the top. The name is singular (entity) rather than plural (entities). An attribute is a property that describes a particular entity. The attribute(s) that uniquely distinguishes an instance of the entity is the identifier. Usually, this type of attribute is marked with an asterisk. Attribute :

41. Relationships have two indicators The first one (often called multiplicity) refers to the maximum number of times that an instance of one entity can be associated with instances in the related entity. It can be one or many. The second describes the minimum number of times one instance can be related to others. It can be zero or one, and accordingly describes the relationship as optional or mandatory. In crow’s foot notation: A multiplicity of one and a mandatory relationship is represented by a straight line perpendicular to the relationship line. A multiplicity of many is represented by the three-pronged ‘crow-foot’ symbol. An optional relationship is represented by an empty circle . zero or many one or many one and only one zero or one Example: Each department must have many employees, but each employee belongs to one department. Example: Each department must have many employees, but each employee belongs to one department. Example: A customer may place many orders, but an order must belong to one customer. Example: Students can enroll in many courses, and each course can have many students.

42. Example

43. Barker's notation refers to the ERD notation developed by Richard Barker, Ian Palmer, Harry Ellis et al. whilst working at the British consulting firm CACI around 1981. The notation was adopted by Barker when he joined Oracle and is effectively defined in his book Entity Relationship Modelling as part of the CASE Method series of books. Barkers Notation

44. The most important components in the ERD diagram are: Entity : When drawing elements in the Barker notation, some rules should be followed. Entity – is represented by a rounded corner rectangle. The entity name should be in the upper part of rectangle and in singular form. Attributes – describe the characteristics of a particular entity instance. An attribute can be of three types: Unique Identifier – uniquely identifies an entity instance Mandatory – its value cannot be null Optional – its value can be null as presented in the diagram below. Attribute : entities, which can be thought as physical objects or elements that can be uniquely identified, and relationships, which capture how entities are related to one another.

45. UID bar A bar “|” across one end of a relationship line indicates that the relationship is a component of the primary identifier for the entity type at that end. ORDER ITEM links ORDER and ITEM using composite primary key (ORDER id + ITEM id) and stores quantity.

46. Non-Transferability of Relationships In certain situations, once a relationship is set it can never change. For example, BOOK and CHAPTER in the example below. Chapter can’t be moved to a different book. We call this the non-transferability of a relationship and it is represented by a rhombus.

47. UML is popular for its notations. We all know that UML is for visualizing, specifying, and documenting the components of software and non software systems. What’s more, UML has many types of diagrams which are divided into two categories. Some types represent structural information, others general types of behaviors. Among these, there is one that is commonly used for entity relationship diagrams. UML Notation

48. The most important components in the ERD diagram are: Entity : In UML, an entity is represented by a rectangle: Relationships are solid lines with cardinality specified at the ends of the lines: Relationships : entities, which can be thought as physical objects or elements that can be uniquely identified, and relationships, which capture how entities are related to one another.

49. Generalization is represented by an empty arrow: In UML ER diagrams, generalization represents an "is-a" relationship where a child (subclass) inherits properties and behaviors from a parent (superclass). It’s depicted as: Diagram Symbol: A triangle at the top connecting the superclass to its subclasses with lines. Purpose: Models hierarchical relationships for reusability and clarity. . `Example : Car` and `Truck` generalize the `Vehicle` class

50. Aggregation is shown as binary association with a hollow diamond as a symbol of the aggregation at the end of the association line. In UML ER diagrams, aggregation represents a "has-a" relationship, where one class is a part of another but can exist independently. Diagram Symbol: A hollow diamond near the aggregate (whole) class pointing to the part class. Purpose: Models relationships with shared ownership. Example : a Department has Employees, but employees can exist independently of the department (e.g., transferred to another department). s

51. Composition is presented as binary association with a black diamond as a symbol of the composition at the end of the association line. A composition specifies that the lifetime of the part entity is dependent on the lifetime of the whole entity. In UML ER diagrams, composition is a stronger "has-a" relationship where the part class is wholly dependent on the whole class. Diagram Symbol: A filled diamond near the composite (whole) class pointing to the part class. Purpose: Models relationships with exclusive ownership. Example : a Room is part of a House and cannot exist without it.

52. THE END