1 / 74

Essential Guide to Entity Relationship Modeling

Learn the main phases and characteristics of ER modeling, including components, definitions, and attribute classifications. Understand entity types, sets, keys, and relationships, with a focus on database design principles. Figures from renowned authors Elmasri, Navathe, and Connolly are adapted for clarity.

demars
Download Presentation

Essential Guide to Entity Relationship Modeling

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 1 Entity Relationship Model

  2. Chapter 3 : Objectives • Main Phases of Database Design • Characteristics of ER Model • Components of ER Model • Definitions • Classification of Attributes • Entity Types • Entity Set • Key attribute Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  3. Chapter 3 : Objectives (Continued.) • Relationship Types • Recursive Relationships: • Structural Constraints on Relationship Types: • Attributes on Relationship Types • Weak Entity Type • Problems with ER Models Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  4. Main Phases of Database Design • Database Design Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  5. Introduction • E-R model facilitates database design by allowing the specification of an “enterprise schema” which represents the overall logical structure of a database. • The E-R model is extremely useful in mapping the meanings and interactions of real-world enterprises onto a conceptual schema. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  6. Characteristics of ER Model • Express the logical properties of an enterprise database • No physical DBMS • Proposed by Dr. Peter Chen Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  7. Components of ER Model • Entity, Entity Type, Entity Set • Attribute • Key (Identifier) • Relationship, Relationship Type, Relationship Set • Structural Constraints on relationships Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  8. Definitions • A Database can be modeled as : • a collection of entities • relationships among entities. • Entity: • An object in the real world with an independent existence • Is distinguishable from other objects • could have a physical existence or a conceptual existence Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  9. Example (Entity) • Example: person, company, event, plant • More Specific example: Consider University of Windsor as a large enterprise. • Entities : • Ritu (Employee) • John Smith (Student) … Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  10. Attribute Attribute: the property that describes an entity or a relationship type. Examples: Student : id, fname, lname, yrAdmin Employees: id, fname, lname, position ER : Domain of Attributes: Set of values that may be assigned to that attribute for each entity (set of permitted values) . Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  11. Entity Types A collection of entities that have the same attributes (describes the intension of a database). ER : • Example : EMPLOYEES, STUDENTS Entity Type Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  12. Entity Set Entity Set : The collection of all entities of a particular entity type in the database at any point in time. (Extension of the database) Attributes are properties possessed by all members of an entity set . Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  13. Classification of Attributes 1.Simple ~ Composite • Simple Attribute : • Attribute composed of a single component with an independent existence. • Composite Attribute • Attribute composed of multiple components, each with an independent existence. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  14. Classification of Attributes 2. Single-Valued ~ Multi-Valued Single-valued Attribute • Attribute that holds a single value for each occurrence of an entity type. Multi-valued Attribute { }, • Attribute that holds multiple values for each occurrence of an entity type. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  15. Classification of Attributes 3. Stored ~ Derived Derived Attribute • Attribute that represents a value that is derivable from value of a related attribute, or set of attributes, not necessarily in the same entity type. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  16. Classification of Attributes • NULL Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  17. Key attribute • Key attribute : An attribute of an entity type whose values are distinct for each individual entity in the collection. • ER : Student_Id Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  18. Example : COMPANY Database: 1. The company is organized into departments. Each department has a unique name, a unique number, and a particular employee who manages the department. We keep track of the start date when that employee began managing the department. A department may have several locations. 2. A department controls a number of projects, each of which has a unique name, a unique number, and a single location. 3. We store each employee’s name, social security number ,address, salary, sex, and birth date. An employee is assigned to one department but may work on several projects, which are not necessarily controlled by the same department. We keep track of the number of hours per week that an employee works on each project. We also keep track of the direct supervisor of each employee. 4. We want to keep track of the dependents of each employee for insurance purposes. We keep each dependent’s first name, sex, birth date, and relationship to the employee. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  19. Preliminary design of entity types for the COMPANY Database • DEPARTMENT • Name, Number, {Locations}, Manager, ManagerStartDate • PROJECT • Name, Number, Location, ControllingDepartment • EMPLOYEE • Name(FName, MInit, LName), SSN, Sex, Address, Salary, BirthDate, Department, Supervisor, {WorkOn (Project, Hours)} • DEPENDENT • Employee, DependentName, Sex, BirthDate, Relationship Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  20. Ritu 315 Course Employee Relationship • A relationship is an association among entities. Teaches Relationship Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  21. Relationship Types A relationship type R among n entity types E1 .. En is a set of relationship instances r i where each r i associates n individual entities (e1, e2, …. en) and each entity ej in ri is a member of entity type Ej, 1<=j<=n. Ej is called the participating entity type. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  22. Example • An employee works for a department Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  23. WORKS_FOR EMPLOYEE DEPARTMENT Example Relationship instances : r1(e1, d1) r2(e2, d2) r3(e3, d1) ……….. Relationship type: WORKS_FOR • ER : Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  24. Example • ER Diagram? Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  25. Example ER Diagram? Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  26. SUPERVISION EMPLOYEE Recursive Relationships: The same entity type participates more than once in a relationship type in different roles. Each role is given a name. Example : Roles are optional – they clarify semantics of a relationship Supervisee(2) Supervisor(1) Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  27. Recursive Relationships Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  28. Attributes of Relationship Types • A relationship type can have attributes. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  29. Degree of a relationship Degree of a relationship: Number of relations taking part in a relationship type. • Binary Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  30. Structural Constraints on Relationship Types: • Limit the possible combination of entities • Represent business rules established by the user or company. • 2 Structural Constraints : • Cardinality Ratio • Participation Constraint Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  31. Cardinality Ratio Cardinality Ratio : number of relationship instances that an entity can participate in. SHOWN BY PLACING APPROPRIATE NUMBER ON THE LINK. Examples : 1:N, 1:1 , M:N Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  32. Participation Constraint Participation Constraint: specifies whether the existence of an entity depends on its being related to another entity via the relationship type. SHOWN BY DOUBLE LINING THE LINK - Total (Existence dependency) ER : = - Partial Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  33. Weak Entity Type Definition: A weak entity type is one that meets 2 conditions : 1. It is existence dependent; that is, it cannot exist without the entity with which it has a relationship. 2. It has a (primary) key that is partially or totally derived from the parent entity in the relationship. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  34. Weak Entity Type -don't have key attributes of their own -Partial key -Identifying Entity Type ER - -Weak entity types always have a total participation constraint with respect to its identifying relationship. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  35. Example :ER for COMPANY database Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  36. Alternate ER Diagram Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  37. Example 1 : Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  38. Example 2 • Draw an ER diagram to show the relationship between CLUB_MEMBER and LOCKER. • Assume that every club member must have a locker, though a locker need not have a club member assigned to it. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  39. Phases of DB Design Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  40. Relational DB Model :Logical View of Data • Logical Model proposed in 1970 by Codd • Designer focuses on logical representation rather than physical • Use of table advantageous • Structural and data independence • Related records stored in independent tables • Logical simplicity • Allows for more effective design strategies Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  41. Definitions • A relation is a table with columns and rows. • Only applies to logical structure of the database, not the physical structure. • Attribute is a named column of a relation. • Attribute Domain is the set of allowable values for one or more attributes. • Tuple is a row of a relation : rows represent a single entity Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  42. Definitions • Row/column intersection represents single value • Degree is the number of attributes in a relation. • Cardinality is the number of tuples in a relation. • Relational Database is a collection of normalized relations with distinct relation names. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  43. Properties of a relation • The relation has a name that is distinct from all other relation names in the database schema. • Theoretically, ordering of tuples may have no significance. • The order of attributes has no significance. • Each cell of a relation contains exactly one atomic value. • Each attribute has a distinct name. • The values of an attribute are all from the same domain. • Each tuple is distinct – there are no duplicate tuples. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  44. Examples of relation Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  45. Examples of Attribute Domains Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  46. Mathematical Definition of Relation - Consider two sets, D1 & D2, where D1 = {2, 4} and D2 = {1, 3, 5}. Cartesian product, D1 X D2, is set of all ordered pairs, where first element is member of D1 and second element is member of D2. => D1 X D2 = {(2, 1), (2, 3), (2, 5), (4, 1), (4, 3), (4, 5)} - Any subset of Cartesian product is a relation e.g: R = {(2,1),(4,1)} - Mathematically, r(R) is a subset of the Cartesian product of the domains that define R. => r(R) subsetOf (dom(A1) × dom(A2) ….× dom(An)) Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  47. Definitions • Relation Schema R denoted by R(A1,A2, …. An) is made up of a relation name R and a list of attributes A1, A2….An and domain name D1,D2,….Dn pairs. ( Each Ai has a Di ) • Relational database schema is a set of relational schemas, each with a distinct name. • Relation instance ( relation) r of a relation R(A1,A2,…..An) also denoted by r(R) is a set of n-tuples {t1….tm}where each n-tuple t is a list of n-values t=<v1,v2….vn> where 1≤vi≤n is an element of dom(Ai) • R: schema of the relation • R is also called the intension of a relation • r is also called the extension of a relation Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  48. Relational keys • Superkey • Candidate Key (K) • Primary Key • Alternate keys • Foreign Keys • Surrogate keys Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  49. Superkey • An attribute or set of attributes that uniquely identifies a tuple in a relation. • Every relation has at least one super key. Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

  50. Candidate Key (K) • Superkey such that no proper subset of K is a superkey within the relation. • K is a minimal superkey. • K is a super key of R with the additional property that removing any attribute A from K leaves a set of attributes K’ that is not a superkey of R. • In each tuple of a relation R, values of K uniquely identify that tuple (Uniqueness). • No proper subset of K has the uniqueness property (irreducibility). Ritu Chaturvedi Some figures adapted from Fundamentals of database systems by Elmasri and Navathe and By T. Connolly

More Related