1 / 54

Entity-Relationship (E-R) modeling: constructing a conceptual schema (Chapter 5)

Entity-Relationship (E-R) modeling: constructing a conceptual schema (Chapter 5) Due to course constraints I have to defer other modeling issues (normalization in chapters 3 and 4) until later in the semester. Entity a thing you need to model.

josef
Download Presentation

Entity-Relationship (E-R) modeling: constructing a conceptual schema (Chapter 5)

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 (E-R) modeling: constructing a conceptual schema (Chapter 5) • Due to course constraints I have to defer other modeling issues (normalization in chapters 3 and 4) until later in the semester.

  2. Entity • a thing you need to model. • It is analogous to a class in object oriented design. • Fields of an entity are called attributes. • An entity instance is an occurrence of a particular entity. • It is analogous to an object. However, there is no encapsulation in the OO sense.

  3. One or more attributes is usually an identifier, which uniquely identifies an entity. • It is also called a key, though the term identifier is used in the data model and key is used when creating tables. • Semantics.

  4. Design issue: • Many designers like to keep each identifier and primary key dataless • This means that it contains no information about the entity. This way, they never change. • Ex. IDs, account numbers, etc.

  5. Advantageous if there’s an index or hash function that uses the keys since a changing key would change the internal structure. • Also useful since a key value sometimes exists in other records that are related to the entity with the given key. • These would need to change also.

  6. A relationship defines how two or more entities are connected according the rules in the reality you are modeling.

  7. Some guidelines • A relationship should be named and well-defined. • Do not simply state there is a relationship between two entities. • Articulate what the relationship represents.

  8. Example: • there is a relationship between a student entity and a course entity. • So? • Does it represent courses for which the student has registered? • Does it represent courses dropped? • Does it represent courses needed for a major? • Does it represent courses on a transcript? • Spell it out!!

  9. Degree of a relationship is the number of entities involved. • Most are degree 2 (binary relationships) but some are more. • A ternary relationship involves 3 entities. Example: p. 148. • Can you think of more?

  10. Cardinality is the number of entity instances in a relationship. • Maximum cardinality is the maximum number of instances in a relationship. • For example, the relationship between a sports team and its players has a maximum cardinality defined by rules of the sport.

  11. Three types of maximum cardinality • 1-1 relationship between A and B • For each instance of type A there is no more than one instance of type B, and vice-versa. • Notation and example: p. 149

  12. Some possible examples: • Employee fleet vehicle • Project employee (defined by who is project leader and assumes each employee leads no more than one project.) • Employee computer 1:1 1:1 1:1

  13. 1-many relationship between A and B • For each instance of type A there may be many instances of type B; however, for each instance of type B there is no more than one instance of type A. • Notation and example: p. 149.

  14. Other possible examples: • Coursesections • Departmentsemployees • projectemployees (participation) • Employeecomputer 1:N 1:N 1:N 1:N

  15. May actually specify a maximum number. • Example: Team-players. Number depends on the sport. • Sometimes the term parentapplies to the entity on the “one side” and child applies to the entity on the “many side”.

  16. Many-many relationship between A and B • For each instance of type A there may be many instances of type B, and vice-versa. Notation: p. 149 • More examples follow

  17. Studentscourses (could have several meanings) • Authorsbooks • Moviesactors; • advisorsstudents; • artistsongs • Majorcourses N:M N:M N:M N:M N:M N:M

  18. These are sometimes called HAS-A relationships. • A team has players; a student has courses; etc.

  19. Minimum cardinality • May specify a minimum number of instances. • Can specify whether an instance is mandatory or optional. • Examples: p. 150. Can you think of more?

  20. A crow’s foot notation (page 152-153) often used to provide a visual of the relationships. • We’ll use this notation in subsequent diagrams

  21. Weak entity • Cannot exist unless another type of entity exists • Employeedependent (dependent is weak) • building  room (room is weak) • course  section (section is weak) • book has more

  22. ID-dependent entity • special type of weak entity in which the ID contains the ID of another entity • EX: Rooms on campus have an ID such as MAC 122 (Building ID and room number). • Other examples on page 154 • All ID-dependent entities are weak • A weak entity may not be ID-dependent (example p.155)

  23. Shown in diagram using solid lines (ex. P.154) • If the parent entity is removed, so must all child entities. • Dashed lines represent non-identifying relationships

  24. Strong entity • existence does not depend on another entity. • Ex: Students, employees, departments, computer, building, etc.

  25. Difference between strong and weak not always clear. • Subject to variances in interpretation of the mode. • Kroenke lists possible tests on page 156 and 160

  26. Example: • One-to-many relationship between a pharmaceutical company and a drug. • One-to-many relationship between an employee and a dependent. • Dependent does not exist w/o the employee. • Drug does not exist w/o the pharmaceutical company • Are drug and dependent both weak?

  27. If the employee is removed the dependent disappears. • If the pharmaceutical company disappears, the drug may be assigned to another company. • Argues that the drug is strong.

  28. Creating an E-R diagram using Visio: • Open Microsoft Office Visio. • Select Software and Database under template categories. • Select Database Model Diagram as a template. • Press the Create button. • Drag and drop one or more entity images to your worksheet.

  29. Double click on the icon and, through the properties pane below the worksheet, you can give it a name and define its fields. • Drag and drop a relationship icon to the worksheet. • Connect each end to an entity.

  30. To get the “crow’s foot” format • select Display Options in the database tab • Select the Relationship tab • check the Crow’s feet checkbox. • Double click the relationship icon • select miscellaneous under categories • Select the appropriate cardinality.

  31. NOTE: Visio does not allow the specification of a many-to-many relationship. • Does a poor job as a modeling tool. • However, we will see later that ALL many-to-many relationships can be implemented via two one-to-many relationships

  32. Probably best to NOT use visio for true data modeling diagrams. • But OK for defining relationships among tables and for this course.

  33. n:m n:m Ternary relationships. • Doctor – Patients – Drugs • Relationship below does not convey all necessary information patient drug doctor drug prescription prescribes

  34. patient doctor drugs • A specific drug given to a patient must have been prescribed by a doctor. • Would need

  35. Building a data model: • Interview the users of data. Find out how they operate!! • Look at existing forms, reports, files, lists, etc. • Determine entities. Look for key words such as order, appointment, product, customer, etc.

  36. Specify relationships. • Examine all combinations of entities or examine documents obtained from previous steps. • Determine what attribute (identifier) uniquely determines an entity.

  37. Determine attributes. • Ask whether an attribute should be its own entity. • Salesperson has a region: should region be just an attribute or a separate entity? • Data models should reflect reality. • Problem is one person’s reality may be different than another’s.

  38. See book for examples.

  39. In Class example: Design a data model for a university database.

  40. Using Patterns to design relationships. • Asking users what the maximum cardinality is won’t work • they won’t know what you’re talking about. • You can show them a prototype form or report to learn how many entity objects relate to another. • Ex. Show a course form to a user that shows one instructor. • The user will likely let you know if other instructors should be shown.

  41. Figure 5-15a on p. 159 • Suggests a 1-1 relationship between strong entities member and locker • Data model in Figure 5-16

  42. Figure 5-17 on p. 160 • Suggests a one-to-many between company and department • Figure 5-18 shows the model

  43. Figures 5-19a and 5-19b on p. 161 • The form and report suggest a many-to-many relationship between company and part • Data model in Figure 5.20

  44. Association pattern: • Consider a n:m relationship connecting students and courses (transcript). • Where is the grade stored? • It is not part of the student entity • It is not part of the course entity.

  45. The data model should show a 3rd entity (transcript?) containing the grade • This is analogous to the example from figures 5.21 & 5.22 on p. 162-163

  46. Multivalued attribute pattern • when is an attribute not an attribute • Consider a customer entity. • Is the phone number an attribute? • If just one number, store as an attribute. • If multiple numbers, might be a problem since arrays or lists can not be attribute types in a relation.

  47. May create an ID-dependent entity, PHONE, connected to the customer. • If just two numbers max, might create a primary and secondary phone number attribute of the customer.

  48. Archetype/Instance pattern • One entity represents an instance of another. • Prints of a painting • Copies of a book • Sections of a course

  49. Line Item Pattern • Multiple instances of an entity used to describe another entity • Ex: Line items to describe an order

  50. Recursive relationships – may be 1:1, 1:n, or n:m • A course and it’s prerequisites (n:m). • A course may have multiple prerequisites and may be a prerequisite for multiple other courses. • Manufacturing (Bill of Materials). • Products consists of parts, some of which are composed of other parts. • Parts may be included in other parts (p. 173)

More Related