Information Resources Management

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