Entity Relationship Model

1. Entity Relationship Model Week 4 CMIS570

2. SDLC Project Planning Analysis ** Design Implementation Support

3. The Entity-Relationship Diagram • Picture of the people, places, objects, things, events, or concepts, their characteristics and relationships, for an organization or business. • Visual representation • Communication tool • Independent of technology • Understandable representation of organization data

4. The Components of an E-R Diagram… • Entitites • Person, place, object, event, or concept (nouns) • Things about which we wish to collect data • Relationship • Association between entities (verbs) • Directional Employee Works

5. Notation • Entities • Relationships Residence Hall Student Student Residence Hall Lives in

6. More about relationships • Degree • Number of entities involved • Typical • Unary (e.g. is married to) • Binary (most common) • The relationship “lives in” is of what degree?

7. And another thing… • Cardinality • Number of instances of one entity that are associated with another • Minimum and maximum – lower and upper bounds on the number of instances • Typically: 0, 1, many – deviations can be noted • Mandatory and optional

8. Examples!

9. More examples!

10. And there’s more • Attributes • Properties or characteristics of entities • Actual data items we collect EmpName Employee EmpAddr EmpJob

11. Primary Keys • Attributes that uniquely identify an entity instance. • How to choose: • Should not change over time (like age) • Must have unique, non-null value • Use as few attributes as possible

12. Identifying the primary key • Select the attribute • Underline it EmpName Employee EmpAddr EmpJob EmpID

13. Practice • The Marathoner, a monthly magazine, regularly reports the performance of professional marathon runners. It has asked you to design a database to record the details of all major marathons (e.g., Boston, London, Paris). Professional marathon runners compete in several races each year. A race may have thousands of competitors, but only about 200 or so are professional runners, the ones The Marathoner tracks. For each race, the magazine reports some personal details such as name, gender, and age as well information about the race.

14. Multi-valued attributes • Attributes that can have multiple values • Can change to standard binary relationship.

15. Multi-valued attributes • Attributes that can have multiple values • Need to fix it before the next phase Employee EmpSkill SkillCode Employee Skill has SkillName

16. More Practice • Kisha, against the advice of her friends, was simultaneously studying data management and Shakespearian drama. She thought the two subjects would be an interesting contrast. However, the classes are very demanding and often enter her dreams. Last night, she dreamt that William Shakespeare wanted her to draw a data model. He explained, before she woke up in a cold sweat, that a play had many characters, but the same character never appeared in more than one play. “Methinks,” he said, “the same name may have appeareth more than the once, but twas always a person of a different ilk.” He then, she hazily recollects, went on to spout about the quality of data dropping like gentle rain… Draw a model to keep old Bill quiet and help Kisha get some sleep.

17. Miscellaneous Stuff … • How many relationships can the same entities be involved in? • How to choose entities?

18. Modeling Hints and Tips • Use different names for different things. • If an entity has only one attribute, think about making the entity an attribute of another entity. • Model the data – not the physical artifacts (reports) • Do NOT try to write code or think about the final format for the data. • Don’t worry about Ifs, or how to connect entities except through relationships.

19. Practice • A laboratory has several chemists who work on various projects and who may use certain kinds of equipment on each project. The laboratory manager wishes to create a database to track chemists, their projects, and the equipment that gets used on various projects. For each chemist, you should capture their name and phone number. For each project, include the project ID and the start date. For each piece of equipment, track the equipment ID number and cost. Feel free to add items if they are needed.

20. A little more practice … • In an organization, the COO is responsible for managing production runs. Each production run uses a variety of materials, some of which are specialized to the specific run. Each material is bought from a single “preferred” vendor, at a pre-negotiated price. The materials are later sold to a variety of vendors, and priced depending on the contract with that vendor (and the prices change over time). At the end of each day, a production report is prepared to summarize the production runs of the day. That report is delivered to the COO to review.