1 / 64

Chapter 7 Data Modeling Using the Entity-Relationship (ER) Model

Chapter 7 Data Modeling Using the Entity-Relationship (ER) Model. Entity Relationship Model (ER). ER model was proposed by Peter Chen in 1976 ER model has become the standard tool for conceptual schema design

aschafer
Download Presentation

Chapter 7 Data Modeling Using the Entity-Relationship (ER) Model

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 7Data Modeling Using the Entity-Relationship (ER) Model

  2. Entity Relationship Model (ER) • ER model was proposed by Peter Chen in 1976 • ER model has become the standard tool for conceptual schema design • ER model consists of three basic constructs: entities, attributes and relationships.

  3. What is an entity ? • An entity is a “thing” in the real world with an independent existence. • It may be an object with physical existence (e.g. person, car, house, employee), or it may be an object with conceptual existence (company, job, university, course).

  4. Entity and Entity Set • Two types of entities: • Strong entity: can exist independently (or can uniquely identify itself) • Weak entity: existence depends on the existence of other (strong) entity or entities • Examples: • An employee is a strong entity but the dependents of the employee could be weak entities • An account in a bank is a strong entity but a transaction could be a week entity

  5. Entity and Entity Set • An entity type defines a set of entities that have the same attributes. • STUDENT is an entity type (Schema) • An entity set is a collection of entities of the same entity type • Examples: • Rema, Ali, Amal, Samer, Rana are entity set of an entity type STUDENT

  6. Attributes • An entity has a set of attributes that describes it. • Person(SSN, Name, Address, Job-description, Salary). • An entity will have a value for each of its attributes • (999-010-201, John Smith, ‘20 Alebany Rd, Cardiff, UK’, ‘Manager’, 2500) • The properties of an entity set are called attributes of the entity set. • Students: SSN, Name, Address, GPA, Status, ... • Books: Title, ISBN, Authors, Publisher, Year, ...

  7. Types of Attributes • Simple (or atomic) attribute is a one which cannot be divided into smaller parts. • Examples: • SSN, GPA, Salary. • Composite attribute is an attribute which can be divided into smaller subparts, these subparts represent more basic attributes with independent meanings of their own • Examples: • Name: First_Name, Middle_Name, Last_Name • Address: Street_Address, City, State, Zip code

  8. An Example of a composite attribute Address Street-Address City State Post Code Street Name House No.

  9. Types of Attributes • A single-valued attribute is a one which has one (single) value for a particular entity. • Example: Age, BirthDate • A multi-valued attribute is a one which may have one or more values for the same entity. • College Degrees for Person: 0, 1, 2, 3, … • Color for a Car: 1, 2, ….. • Authors of Books • Phone Number

  10. Types of Attributes • A stored attribute is a one whose value is explicitly stored in the database. • e.g. name, birth-date. • Derived-attributes: whose values are computed from other attributes. • Age from Birthdate • Annual Salary from Monthly Salary • NoOfEmployees ==> Count number of employees in the Employee table.

  11. Relationship Types • A relationship type is represented as diamond-shaped box which is connected by straight lines

  12. Relationship Degree Degreeof a relationship type is the number of participating entity types: binary relationships, ternary relationships, …. Works-for COMPANY EMPLOYEE Binary Relationships

  13. Ternary Relationships supplies PROJECT SUPPLIER PART

  14. Ternary Relationships Sells PRODUCT COMPANY COUNTRY

  15. Binary Recursive Relationships EMPLOYEE Supervises Marry PERSON

  16. Relationships • The role name signifies the role that a participating entity from the entity type plays in each relationship instance • entity e1 plays the role of a supervisor, while entity e2 plays the role of a supervisee supervisee EMPLOYEE e2 Supervises e1 supervisor

  17. Cardinality Ratio • Specifies the number of relationship instances that an entity can participate in • Common cardinality ratios for binary relationship types are • 1:1, • 1:N, and • M:N

  18. 1:N Works_for N 1 COMPANY EMPLOYEE An employee works for one company, and a company has many employees working for it

  19. 1:1 Has 1 1 MANAGER DEPARTMENT A department has one manager and a manager manages one department

  20. M:N Works-on M N PROJECT EMPLOYEE An employee works on many projects, and a project has many employees working on it

  21. Participation Constraints • Specifies whether the existence of an entity depends on its being related to another entity via the relationship type • There is total and partial participation

  22. Total participation Works-for N 1 DEPARTMENT EMPLOYEE Total participation. Every employee must be related to a department via WORKS-FOR relationship. A department must haveat least one employee.

  23. Partial participation N 1 Buys PERSON CAR A person may buy a car and car may be bought by a person

  24. Total & Partial participation 1 1 PROFESSOR DEPARTMENT Manages A professor may manage a department (partial participation), but a department must be managed by a professor (total participation).

  25. Attributes of Relationship Types Works-for N 1 EMPLOYEE DEPARTMENT Start-Date We may keep a start date attribute to record for each employee the date he/she started work for a certain department.

  26. A weak entity type is an entity which does not have any key attributes Works-for DEPARTMENT EMPLOYEE 1 identifying relationship Dependents Fname N Sex DEPENDENT Birthdate Relationship

  27. Weak Entity Types • A weak entity type always has a total participation with its identifying entity type • A Weak entity type has a partial key, i.e. this key is enough to identify its extension within the scope of its identifying entity type • In the previous example, the first name is enough to identify kids within a single family, but is not enough to identify entities as stand alone entities (two families may use identical names for their kids)

  28. ER Notations Entity Type <Name> Attribute <Name> <Name> Key Attribute Multi-valued attribute <Name>

  29. ER Diagram Notations <Name> Weak Entity Type <Name> Relationship Type Identifying Relationship Type <Name>

  30. ER Notations <Name> <Name> Composite Attribute <Name> Derived Attribute <Name> partial key attribute <Name>

  31. Notations • Entity Types • singular name, capital letters • Relationship Types • usually singular verbs, capital letters • Attribute • nouns, capitalized • Role names • are in lowercase letters • ER diagrams are drawn such that they are readable from left to right and top to bottom (Except weak entity types)

  32. Relationships • Several relationships may exist among the same set of entity sets. Works_in EMPLOYEE DEPARTMENT Manages

  33. Degree of a Relationship (1) • Definition: • The degree of a relationship is the number of entity sets participating the relationship. • Recursive relationship Examples: Supervises on Employees is_prerequisite_of on Courses is_classmate_of on Students

  34. Degree of a Relationship (2) • Binary relationship (degree = 2) • Examples: • takes between Students and Courses • owns between Persons and Cars • Ternary relationship (degree = 3) • Examples: • orders among Customers, Parts and Suppliers • skill_used among Engineers, Skills and Projects

  35. Cardinality (1) • One-to-one (1-to-1) relationship between E1 and E2: • for each entity in E1, there is at most one associated entity in E2, and vice versa. • Examples of 1-to-1 relationships: • Binary 1-to-1 relationship • manages between Employees and Departments • recursive 1-to-1 relationship • is_married_to on Persons

  36. Cardinality (2) • One-to-many (1-to-m) relationship from E1 to E2: for eachentity of E1, there are zero or more associated entities of E2, but for each entity of E2, there is at most one associated entity of E1 • Examples of 1-to-m relationships: • binary 1-to-m relationship • advises between Professors and Students • recursive 1-to-m relationship • is_mother_of on Persons • Many-to-one (m-to-1) relationship from E1 to E2: same as 1-to-m relationship from E2 to E1

  37. Cardinality (3) • Many-to-many (m-to-m) relationship between E1 and E2: for each entity in E1, there are zero or more associated entities in E2, and vice versa • Examples of m-to-m relationships: • binary m-to-m relationship • takes between Students and Courses • recursive m-to-m relationship • is_component_of on Parts

  38. ER Diagram (1) Recursive relationship is_married_to 1 1 PERSON SSN Name Age

  39. ER Diagram (2) binary relationship 1 m advises PROFESSOR STUDENT Age SSN Name SSN Name Age

  40. ER Diagram (3) ternary relationship ENGINEER Skill_used SKILL PROJECT

  41. Role of an Entity Set (1) Definition: The role of an entity set in a relationship is the function it performs in the relationship. Case 1: Role can be determined from properly chosen names. m takes n STUDENT COURSE 1 is_TA_of 1

  42. Role of an Entity Set (2) Case 2: Roles need to be explicitly given. is_married_to supervises 1 m 1 1 wife husband supervisor supervisee PERSON EMPLOYEE

  43. Attribute of Relationship (1) Where to keep the grade information? m n takes STUDENT COURSE grade

  44. Attribute of Relationship (2) Another example: SUPPLIER m Quantity orders n r PART PROJECT

  45. Cardinality Constraint min/max (1) • One in ER model means zero or one • Many in ER model means zero or more • Cardinality constraints make them more precise (1, 5) (15, 60) takes STUDENT COURSE

  46. Cardinality Constraint min/max (2) • General format: • 0  min_card  max_card • Interpretation: • Each entity in E may involve between min_card and max_card relationships in R. (min_card, max_card) E R

  47. Cardinality Constraint min/max (3) • Definition: • If every entity in E involves at least one relationship in R (i.e., min_card >= 1), E is said to have totalparticipation in R • If min_card = 0, E is said to have partialparticipation in R

  48. Cardinality Constraint min/max (4) Employees has a partial participation. Departments has a total participation. (0, 1) (1, 1) manages EMPLOYEE DEPARTMENT

  49. Representing 1-to-1, 1-to-m, m-to-mRelationships (0, 1) (0, 1) one-to-one: E R F (0, m) (0, n) many-to-many: E R F (0, m) (0,1) one-to-many: E R F 1 m E R F

  50. An Example Database Application Company Database

More Related