1 / 37

Information Resources Management

Information Resources Management. January 30, 2001. Agenda. Administrivia Homework #1 Entity-Relationship Modeling Homework #3. Administrivia. Homework #2 Book?. Homework #1 Results. Recommendations Excel - 7; web - 1 Access - 46; web - 37 Oracle, SQL Server - 0; web - 15 Scores.

carline
Download Presentation

Information Resources Management

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. Information Resources Management January 30, 2001

  2. Agenda • Administrivia • Homework #1 • Entity-Relationship Modeling • Homework #3

  3. Administrivia • Homework #2 • Book?

  4. Homework #1 Results • Recommendations • Excel - 7; web - 1 • Access - 46; web - 37 • Oracle, SQL Server - 0; web - 15 • Scores

  5. Entity-Relationship (E-R) Modeling • Why • Entities • Attributes • Relationships • “The Rules” • Examples

  6. Why Do E-R Modeling • Early process • Much cheaper to fix - cost of changes • Understandability • Data is critical – needs to be correct • Complexity • Stability • Conversion to database

  7. Cost of Changes

  8. Entities • person, place, thing, event, concept • have attributes sufficient to distinguish them • NOUNS • Examples: employee, department, division, project, skill, location

  9. Entity Type • A collection of entities with identical attributes not attribute values • An entity instance (or just instance) is a single occurrence of an entity type

  10. Strong vs. Weak Entities • Entities can be weak or strong • default (if not specified) is strong • a weak entity can not exist without the existence of an instance of some other entity type • Examples: bank transaction (account), grade (student and course), dependent (employee)

  11. Entity Entity (Strong) Name Entity (Weak) Name

  12. Entity Examples Department Employee Transaction Account

  13. Attributes • characteristics of entities • their values differentiate entities • two types: • identifiers (keys) – determine an instance of an entity (underlined) • descriptors – specify non-unique characteristics • Example: first name vs. student ID • NOUNS

  14. Attributes • Simple - single value only • Composite - consists of other attributes • Multivalued - one or more of the same type of value • Derived - value can be calculated

  15. Attributes Simple Derived Composite Multivalued Underlined if key

  16. Attributes Years Employed Assignment Employee EmployeeID Address Name Street First City Last State

  17. Relationships • association between the instances of one or more entity types (usually two) • can have any number of occurrences or instances • described in terms of degree, connectivity, cardinality, and existence • VERBS

  18. Relationship Examples • employee has skills • employee works in department • employee manages project

  19. Employee Employee Employee Skills Department Project has works in manages Relationship Examples

  20. Relationship Degree • Indicates number of entity types involved in a relationship • one - unary • two - binary • three - ternary • more - n-ary

  21. Employee manages Relationship Degree • Unary – relationship between two instances of the same entity type • Example: employee manages employees

  22. Employee Department works in Relationship Degree • Binary – relationship between one instance of each of exactly 2 entity types • Binary is the typical relationship degree

  23. Employee Project Skills uses Relationship Degree • Ternary – relationship between one instance of each of exactly 3 entity types • N-ary – relationship between one instance of each of N entity types

  24. Relationship Connectivity • Qualitatively describes how many entities of one type may be related to a single entity of another • Values are: “one” or “many” • Possibilities: one-to-one, one-to-many, many-to-many • Connectivity refers to the possibility, which may not occur for every entity of that entity type

  25. Connectivity Examples • Employee is assigned a parking space (one-to-one) • Professor teaches a course (one-to-many) • Students takes courses (many-to-many)

  26. Employee Professor Student Parking Space Course Course assigned teaches takes Connectivity Examples (1) 1 1 M 1 M N

  27. Employee Professor Student Parking Space Course Course assigned teaches takes Connectivity Examples (2)

  28. Professor Course teaches Relationship Cardinality • The binding upper and lower bounds on the number of participating entities in a relationship • Sometimes referred to as “business rules” • Example: Full-time professor must teach 3 courses 3 1

  29. Relationship Existence • Defines whether the existence of an entity requires (mandatory) or does not require (optional) the existence of a related entity • Examples • Driver has auto insurance (mandatory) • Tenant has renter’s insurance (optional)

  30. Connectivity, cardinality, and existence are often combined and simply described as relationship cardinality.

  31. Employee Professor Student Parking Space Course Course assigned teaches takes Existence Examples (1) 1 1 M 1 M N

  32. Employee Professor Student Parking Space Course Course assigned teaches takes Existence Examples (2)

  33. Example 1 A hospital has a large number of registered physicians. Patients are admitted to the hospital by a physician. Any patient who is admitted must have exactly one admitting physician. A physician may optionally admit any number of patients. Once admitted, a given patient must be treated by at least one physician. A particular physician may treat any number of patients, or may not treat any. Whenever a patient is treated by a physician, the hospital records the details of the treatment.

  34. Example 2 A company has a number of employees. The company also has several projects. Each employee may be assigned to one or more projects, or may not be assigned to a project. A project must have at least one employee assigned, and may have any number of employees assigned. An employee’s billing rate may vary by project, and the company wishes to record the billing rate for each employee when assigned to a particular project.

  35. Example 3 • A real estate firm lists property for sale. • The firm has a number of sales offices in several states • Each sales office is assigned one or more employees. An employee must be assigned to only one sales office. • For each sales office, there is always one employee assigned to manage that office. An employee may manage only the sales office to which she is assigned. • The firm lists property for sale. • Each unit or property must be listed with one (and only one) of the sales offices. A sales office may have any number of properties listed, or may have no properties listed. • Each unit of property has one or more owners. An owner may own more than one unit of property. An attribute of the relationship between property and owner is percent owned.

  36. Example 4 • Construct an E-R diagram for a bank database that shows the basic relationships among customers, checking accounts, savings accounts, loans, loan officers, and the bank branches where various accounts and loans are taken out. You also want to keep track of transactions on accounts and loans, and maintain the current balance in each account and the balance of the loan.

  37. Homework #3 • E-R Modeling • Two weeks • Do #1 now • Review #2 and #3 • Computer generated • Visio, Word, SilverRun

More Related