1 / 33

ENTITY-RELATIONSHIP MODELING

ENTITY-RELATIONSHIP MODELING . Using Entity-Relationship Diagrams (Concepts book: Chapter 6). Instructional Objectives. What is Entity-Relationship Modeling? Introduction to Entity-Relationship Diagrams Components of an ERD Entity Weak, ID-dependent Attributes Multivalued Relationships

herb
Download Presentation

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. ENTITY-RELATIONSHIP MODELING Using Entity-Relationship Diagrams (Concepts book: Chapter 6)

  2. Instructional Objectives • What is Entity-Relationship Modeling? • Introduction to Entity-Relationship Diagrams • Components of an ERD • Entity • Weak, ID-dependent • Attributes • Multivalued • Relationships • Degree • 1:1, 1:N, N:N • IS-A, HAS-A • Subtypes, supertypes • Cardinalities

  3. Database Design Process • Begin by creating a logical design (or abstract model) using ERM/ERD and the Normalization process • Then create physical design  actual implementation of the database • Convert: • User requirements  ERD Normalization  Relational database design

  4. Entity-Relationship Model • used for conceptual data modeling • developed by Peter Chen in 1976 • made up of entities, relationships, and attributes • can be used to model any kinds of entities and relationships

  5. E-R Model • is a detailed, logical representation of: • the data of an organization (entities), • relationships (or associations) among entities • attributes of entities • allows us to identify relationships between entities - at best it provides a broad overview of relationships

  6. E-R Diagram • an entity relationship diagram is a graphical representation of an E-R model • Components • Entities • Attributes (and identifiers) • Relationships

  7. Entities • What is an entity? • person, place, object, event, or concept about which an organization (or users) want to maintain data about (or track) • Examples: • Person: EMPLOYEE, STUDENT, PATIENT • Place: STATE, REGION, COUNTRY • Object: MACHINE, BUILDING, AUTOMOBILE • Event: SALE, REGISTRATION, RENEWAL • Concept: ACCOUNT, COURSE, WORK CENTER

  8. Entities • need to differentiate between entity type and entity instance • entity type or entity class • entity instance

  9. Entities • Rules: • name of entity is name of relation • attributes of entity are attributes of relation • construct a relation that has all of the entity’s attributes and columns • normalize relations

  10. Weak entities • a weak entity depends for its existence on another entity • this is known as existence dependency • a weak entity cannot be created without its proper parent • when the parent entity is deleted, the weak entity should also be deleted

  11. Existence dependency • example of EMPLOYEE and DEPENDENT • primary key for DEPENDENT would partly be that of EMPLOYEE

  12. ID-dependent weak entity • an entity that is ID-dependent depends for its existence on another entity and the identifier (or key) of the parent must be included in the ID-dependent entity

  13. ID-dependent weak entity • an ID-dependent entity is a type of a weak entity • BUILDING (BuildingName) • OFFICE (BuildingName, OfficeNum) • OFFICE is ID-dependent on BUILDING • Office can’t exist without a building, and if we just say room 104, doesn’t tell us in what building

  14. Attributes • are properties or characteristics about an entity type • candidate key • an attribute (or combination of attributes) that uniquely identifies each instance of an entity type (e.g. primary key or primary field, composite key)

  15. Multivalued attributes • an attribute can have more than one value for each entity instance (or repeating groups) • are removed during normalization process (occurs during logical design) - become classes or entities) of their own

  16. Rules for identifying keys • Choose a key that will not change its value over the life of each instance of an entity type. • Key must have valid values and not be null. • Don’t choose a key whose structure indicates classifications, locations, etc.

  17. Relationships • are an association between instances of one or more entity types of interest to the organization • the degree of the relationship is determined by the number of entity types that participate in a relationship • Degree of relationship can be unary, binary, or ternary • relationships can also be HAS-A or IS-A relationships

  18. Unary relationship • is that of degree 1 • also known as a recursive relationship - a relationship between instances of one entity type • recursive relationships can also be 1:1, 1:M, M:N

  19. Binary relationship • is that of degree 2 • is the most common type of relationship • exists between instances of two entity types

  20. Ternary relationship • is that of degree 3 • a simultaneous relationship among instances of three entity types

  21. Binary Relationships • HAS-A Relationships • One-to-one (1:1) • One-to-many (1:M) • Many-to-many (M:N)

  22. One-to-One relationship • described as 1:1 • a single-entity instance of one entity type is related to a single entity instance of another type • primary key of one would be foreign key in the other • key of either table can be placed as a foreign key in the other table • example: Employee, Auto

  23. One-to-Many relationship • described as 1:M • a single instance of one entity related to many instances of another entity • parent-child relationship • parent is the one side (primary table) • child is the many side (related table) • place key of parent table in child table

  24. Many-to-Many relationship • described as M:N • many instances of one entity relate to many instances of another entity • must be separated into additional 1:M relations • intersection relation (key would be made up of parent keys)

  25. Cardinalities • cardinality of a relationship is the number of instances of entity B that can be (or must) be associated with each instance of entity A

  26. Cardinalities continued • cardinality can be maximum or minimum • minimum cardinality - minimum # of instances of entity B that may be associated with instance of entity A • maximum cardinality - maximum number of instances (can also be a fixed number such as 5 for example, doesn’t always have to be M)

  27. Cardinalities • minimum cardinality = 0 means optional participation • minimum cardinality = 1 means mandatory participation

  28. The Entity-Relationship Model (continued) FIGURE 6-43: E-R diagram that represents cardinality Concepts of Database Management

  29. IS-A Relationships • occur among entities that are subtypes of a common logical type (or supertype) • example: Employee can be hourly or salaried • create a relation for each entity • add key of subtype to supertype

  30. SUPERTYPE and SUBTYPE entities • entities share common properties but also have one or more distinct properties • Supertype • Subtype

  31. Supertype and Subtype • EMPLOYEE (EmpNum, EmpName, EmpAddr, DateHired) • HOURLY (EmpNum, HourlyRate) • SALARIED (EmpNum, AnnualSal, StockOption) • Supertype = Employee • Subtype = Hourly, Salaried

  32. Supertype and Subtype • relationship between supertype and subtype is called ISA • cardinality from subtype to supertype is 1 (i.e. mandatory) • cardinality from supertype to subtype is 0 or 1

  33. Supertype and Subtype • exclusive relationship - subtypes are mutually exclusive (such as a person is either hourly or salaried but not both) • Inheritance - property by which all attributes of a supertype become attributes of its subtypes

More Related