Data Modeling using Entity Relationship Diagramming (ERD)

1. Data Modelingusing Entity Relationship Diagramming (ERD)

2. ERD Planning Use Cases. DFDs. ERDs. “Working toward an actual information system”. Analysis Design Implementation

3. Use Cases Users DFDs ERDs Information System

4. ERD Planning Use Cases. DFDs. ERDs. “Working toward an actual information system”. Analysis Design Physical DFDs and ERDs go here. Implementation

5. Items in an ERD Entity Relationship * Attribute (identifier) Attribute

6. Items in an ERD Entity Relationship * Attribute (identifier) Attribute Each occurrence of an Entity is an “instance”.

7. Cardinality refers to the number of times instances in one entity can be related to instances in another entity One instance in an entity refers to one and only one instance in the related entity (1:1) Cardinality

8. I find it helpful to use this sentence: A single <instance> <relationship> in at most <either 1 or many> <entity>. Cardinality

9. 1:1 Dept. Manager Dept. contains Cardinality symbols

10. Cardinality refers to the number of times instances in one entity can be related to instances in another entity One instance in an entity refers to one and only one instance in the related entity (1:1) One instance in an entity refers to one or more instances in the related entity (1:M) Cardinality

11. 1:M Dept. Manager Dept. Employee manages The “Many” Cardinality symbol

12. Cardinality refers to the number of times instances in one entity can be related to instances in another entity One instance in an entity refers to one and only one instance in the related entity (1:1) One instance in an entity refers to one or more instances in the related entity (1:M) One or more instances in an entity refer to one or more instances in the related entity (M:M) Cardinality

13. M:M Dept. Projects Dept. Employee works on

14. Modality refers to the minimum number of times that an instance in one entity can be related to an instance in another entity One means that an instance in the related entity must exist for an instance in another entity to be valid Modality

15. I find it helpful to use this sentence: A single <instance> <relationship> in at least <either 0 or 1> <entity>. Modality

16. 1 Dept. Manager Dept. contains Modality “1” symbols

17. Modality refers to the minimum number of times that an instance in one entity can be related to an instance in another entity One means that an instance in the related entity must exist for an instance in another entity to be valid Zero means that no instance in the related entity is necessary for an instance in another entity to be valid Modality

18. 0 Dept. Manager Dept. Employee manages The Modality “0” symbol

19. Both Dept. Projects Dept. Employee works on

20. Steps in Building ERDs • Identify the entities • Add appropriate attributes for each entity • Draw the relationships that connect associated entities

21. ERD Building Tips • Data stores of the DFD should correspond to entities • Only include entities with more than one instance of information • Don’t include entities associated with implementation of the system, not the system itself

22. All analysis activities are interrelated Process models contain two data components Data flows and data stores The DFD data components need to balance the ERD’s data stores (entities) and data elements (attributes) Many CASE tools provide features to check for imbalance Check that all data stores and elements correspond between models Do not follow thoughtlessly -- check that the models make sense! Balancing ERDs with DFDs

23. Recall: on-line university registration (from Use Case & DFD examples) The system should enable the staff of each academic department to examine the course offered by their department, add and remove course, and change the information about them (e.g., the maximum number of students). It should permit students to examine currently available courses, add and drop courses to and from their schedules, and examine the course for which they are enrolled. Department staff should be able to print a variety of reports about the courses and the students enrolled in them. They system should ensure that no student takes too many course and that students who have any unpaid fees are not permitted to register. (Assume that a fees data store is maintained by the university's financial office that the registration system accesses but does not change.)

24. 1 Maintain department course offerings D3 D2 D1 Fees 2 Course Offerings Maintain student enrollments Enrollments 3 Course Enrollment Reports Level 0 DFD: Registration Course Offering Changes Available course request Available courses Available courses Course Offering List Students Available courses Course enrollment Course Offering Updates Course Offerings Student schedule Course enrollment request Dept Staff Student schedule Course information Fee Payment History Student Enrollment Report Request Enrollment information Student Enrollment Report

25. Enrollment * Student ID Dept # Course # Hours credit Registration ERD Course Offering Dept. # * Course # Course name Hours Credit Max. size Number enrolled includes

26. Recall: real estate (from Use Case & DFD examples) A Real Estate Inc. (AREI) sells houses. People who want to sell their houses sign a contract with AREI and provide information on their house. This information is kept in a database by AREI and a subset of this information is sent to the citywide multiple-listing service used by all real estate agents. AREI works with two types of potential buyers. Some buyers have an interest in one specific house. In this case, AREI prints information from its database, which the real estate agent uses to help show the house to the buyer (a process beyond the scope of the system to be modeled). Other buyers seek AREI’s advice in finding a house that meets their needs. In this case, the buyer completes a buyer information form that is entered into a buyer database, and AREI real estate agents use its information to search AREI’s database and the multiple-listing service for houses that meet their needs. The results of these searches are printed and used to help the real estate agent show houses to the buyer.

27. Maintain house seller information D4 D2 D3 D1 Sales Contract 1 Sales Contract Sellers Multiple Listing Services File House information House information House information House information Offered Houses Buyers Sales Contracts House information Generate requested report 2 House information request Buyers Buyer information form Buyer information House information Level 0 DFD: AREI

28. Sales Contract Offered House Buyer offers shown * Seller name Address Phone Listing date Listing term * Seller name Address Style Size Price * Name Address Phone Preferred style Preferred size Price limit AREI ERD