LIS 384K.11 Database-Management Principles and Applications

1. LIS 384K.11Database-Management Principles and Applications Basic Concepts of Entity-Relationship Analysis R. E. Wyllys Last revised 2002 Feb 18 GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

2. What Is Entity-Relationship Analysis? • E-R analysis endeavors to • Identify major ingredients, entities, of a complicated situation • Identify characteristics, attributes, of the major ingredients • Uncover and analyze interconnections, relationships, among the major ingredients • Assess various aspects of the relationships GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

3. E-R Analysis • In short, E-R analysis strives • to take a close look at the ingredients of a puzzle • in order to identify major and minor ingredients • and to figure out how they relate to each other GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

4. E-R Analysis • E-R analysis can be summarized as the process of uncovering, analyzing, and explicating (i.e., stating clearly and fully) the business rules governing a complicated situation GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

5. Business Rules • Rob and Coronel1 say: • Business rules are precise statements, derived from a detailed description of the organization's operations, that define one or more of the following modeling components: • Entities • Relationships • Attributes • Connectivities • Cardinalities • Constraints 1Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

6. Business Rules • Examples of business rules1 • An invoice contains one or more invoice lines, but each invoice line is associated with a single invoice. That is, • An invoice contains one or more invoice lines. • Each invoice line is associated with a single invoice. • A store employs many employees, but each employee is employed by only one store. • A college has many departments, but each department belongs to a single college. (This business rule reflects a university that has multiple colleges such as Business, Liberal Arts, Education, Engineering, etc.) 1From: Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

7. Business Rules • Examples of business rules1 (cont'd) • A driver may be assigned to drive many different vehicles, and each vehicle can be driven by many drivers. (Note: Keep in mind that this business rule reflects the assignment of drivers over a period of time.) • A client may sign many contracts, but each contract is signed by only one client. • A sales representative may write many contracts, but each contract is written by one sales representative 1From: Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

8. Business Rules • Some business rules for a library • Books and other materials exist in various editions and numbers of copies • Books may or may not circulate • Books that circulate may do so for varying periods • Periods may be determined by • Book status alone • Borrower status alone • Combination of book status and borrower status GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

9. Business Rules • More business rules for a library • Users may have various statuses • Users may have varying numbers of items currently charged out to them • Users may or may not owe fines • Fine status may or may not affect eligibility to borrow GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

10. Business Rules • More business rules for a library • Staff members have attributes of • Salary or wage rates • Annual leave • Sick leave • Work schedules • Job titles • Special skills (e.g., languages, puppetry) • Hire dates • Total length of service • Professional organization memberships GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

11. Business Rules • More business rules for a library • Physical facilities include • Branches • Furniture • Machines (e.g., computers, microform readers, automobiles, players for CD-ROMs, audiotapes, videotapes) • Library-management software • Catalog records (e.g., computerized databases, catalog cards) • Records of inventory, acquisitions, circulation, personnel GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

12. Business Rules • More business rules for a library • Related organizations may include • City, university, or corporate administrations • Employee unions • Friends of the Library organizations • Vendors of books, serials, and other products and services used by the library • Networks of which the library is a member (e.g., OCLC, Amigos, Association of Research Libraries, Texas State Library network, Central Texas Library System) GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

13. E-R Diagrams • E-R analysis relies heavily on diagrams to represent • Entities (rectangles) • Attributes (ellipses) • Relationships (diamonds) • Connections (lines, which may have arrowheads or crowsfeet to represent degrees of connectivity or relationship) GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

14. Crucial Use of E-R Analysis • It is especially important to identify and note many-to-many (M:N) relationships: for example1 • Note: The small circle denotes optionality (a person need not be a customer; but to be a customer, he or she must rent at least one tape) 1From: Rob, P.; Coronel, C. Instructor’s Manual to Accompany Database Systems: Design, Implementation, and Management. Fourth Edition. CD-ROM GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

15. Crucial Use of E-R Analysis • The typical resolution of many-to-many (M:N) relationships is by a bridge table (also called a composite entity) GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

16. Crucial Use of E-R Analysis • A further explication of the resolution of the M:N relationship GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications

17. E-R Analysis Is Part Science, Part Art GSLIS - The University of Texas at Austin LIS 384K.11, Database-Management Principles and Applications