IST 210 EntityRelationship Model Diagram Todd S Bacastow

IST 210 Entity-Relationship Model & Diagram Todd S. Bacastow IST 210: Organization of data 9/17/2020 1

IST 210 Design Principles n n The client has some vague idea of what he/she wants. YOUR task is to design a DB that represents these thoughts and only these thoughts. 9/17/2020 2

IST 210 Data Modeling n n Data modeling is a formalism Entity-Relationship Model (ER) Entity Relationship model: n n a visual model attempts to capture the structure of data 9/17/2020 3

IST 210 What is Visual Modeling? Order Item Ship via Business Process Visual Modeling is modeling using standard graphical notations Computer System 9/17/2020 4

IST 210 Visual Modeling is a Communication Tool Use visual modeling to capture business objects and logic Use visual modeling to analyze and design your database 9/17/2020 Copyright © 1997 by Rational Software Corporation 5

IST 210 Entity-Relationship (E-R) Modeling n Premises n n The real world consists of entities Entities in one enterprise are related n Database model = relevant entities + relationships among them n Entities and relationships have attributes 9/17/2020 6

IST 210 E-R n Features n n n Relatively easy to model real-world Translates easily to relational data model Basic Concepts n n n Entity sets Relationship sets Attributes 9/17/2020 7

IST 210 Entities n Definition n Entity n an object that is distinguishable from other objects. n n a particular student, a particular book Entity set n a set of entities of the same type that share the same set of properties n 9/17/2020 students, books 8

IST 210 Entities n Examples of these entity types are: n Person: n n 9/17/2020 employee student customer supplier 9

IST 210 Entity Types n Place: n n city state county Object: n n 9/17/2020 vehicle building airplane animal 10

IST 210 Entity Types n Event: n n n purchase sale registration game Concept: n n 9/17/2020 account course work group department 11

IST 210 Weak Entities n n n Weak entities are entities, but with a difference--Weak entities only exist because some other entity exists. For example: n n two entities employee and salary-history is a weak entity the record of an employee’s salary history could only exist if a record of an employee also exists E. g. , Joe Smith’s salary history wouldn’t make much sense if Joe Smith doesn’t exist in the data base. 9/17/2020 12

IST 210 Finding Entities n n Entities are normally located through the various user views identified during analysis Search for entities by: n n n looking for nouns finding objects/things that have many instances (i. e. , things stored in tables) have descriptive/defining properties (attributes) 9/17/2020 13

IST 210 Attributes n Definition: n n Attribute: a descriptive property possessed by all members of an entity set. Attribute set: the set of all attributes possessed by members of an entity set. n n n 9/17/2020 e. g. , book = (author, title, publisher, barcode, etc. ) e. g. , student = (ID, surname, firstname, course, etc. ) e. g. , subject = (code, name, lecturer, timetable, etc. ) 14

IST 210 Attributes n Properties of Attributes n n Domain - the seat of permitted values for each attribute Type: n n 9/17/2020 data type simple vs. composite attributes single-valued vs. multivalued derived vs. stored 15

IST 210 Simple & Composite Attributes n Simple attributes cannot be decomposed without loss of meaning, e. g. , n n Title: Mr. , Ms. , Mrs. , Dr. Composite attributes can be further decomposed without loss of meaning, e. g. , n n n Name: first name + last name Date: day + month + year Address: street + city + state + zip 9/17/2020 16

IST 210 Single-Valued Attributes n Single-valued attributes occur once only for each entity instance, e. g. n n n ID Name Department 9/17/2020 17

IST 210 Multi-Valued Attributes n Multi-valued attributes can occur more than once for the same entity instance; e. g. n n n Sports person plays Hobbies person likes Skills person has achieved Courses person has taken Degrees person completed 9/17/2020 18

IST 210 Stored & Derived Attributes n A derived attribute is one that can be calculated from other stored data n not usually stored as an attribute occasionally stored to speed information/query requests when these occur frequently Maybe shown on ERD as attribute name within a dashed-line ellipse 9/17/2020 19

IST 210 Relationships n Relationship: an association among 2 or more entities n n E 1: student - R: takes - E 2: subject e. g. Sara Smith takes IST 210 9/17/2020 20

IST 210 Relationship sets n Relationship set: a relation among entity sets: n n e. g. (Smith, IST 210) takes Relationship sets n n n have degree may have attributes e. g. final mark attribute for takes relationship 9/17/2020 21

IST 210 E-R Diagram Entity Relationship 9/17/2020 22

IST 210 Entity Relationship Diagram Symbols n The symbols used in an ERD are not standardised so n n diagrams may appear somewhat different depending upon who created them symbols used for our purposes will follow the format 9/17/2020 23

Graphical Elements of the ER Diagram IST 210 n Its components are: n n n rectangles representing entity sets. ellipses representing attributes. diamonds representing relationship sets. Entity • Customers • Accounts 9/17/2020 Attribute • id • name Relationship • Customer Account 24

IST 210 Basic Symbols 9/17/2020 25

Relationship IST 210 STUDENT n n Take COURSE Connect two or more entity sets Represented by diamonds Think of the “value” of a relationship set as a table One row for each list of entities, one from each set, that are connected by the relationship 9/17/2020 26

IST 210 Finding Relationships n n Often represented by a Verb allows answers to questions not available through individual entity types EMPLOYEE Has DEPENDENT Weak Entity 9/17/2020 27

IST 210 Attributes & Relationships n Relationship can have 1 or more attributes n n depend on relationship do not belong to the entities for example the date on which a course was completed belongs to the relationship rather than either of the participating entities shown in same way as entity attributes 9/17/2020 28

IST 210 Multiplicity or Cardinality 9/17/2020 29

IST 210 Cardinality Symbology 9/17/2020 30

IST 210 One-to-One 9/17/2020 31

IST 210 One-to-Many 9/17/2020 32

IST 210 Many-to-Many 9/17/2020 33

IST 210 Recursive 9/17/2020 34

IST 210 Common Constraints n n Keys we will discuss next Single-value constraints must have a certain value n n Null value Referential integrity constraints “broken link to a web page” Domain constraints a negative account balance General constraints limits you set 9/17/2020 35

IST 210 Keys Primary key Foreign key Primary key Student ID Last First Grade Class Name 1 Wood Bob C Ist 357 2 Kent Chuck B Ist 115 3 Smith Jane A Ist 357 4 Boone Dan B Ist 357 9/17/2020 #Stud Instructor Ist 357 48 Jones Ist 115 120 Brower Ist 20 Mennis 120 36

IST 210 Keys n n A key is a set of attributes whose values can belong to at most one entity In E-R model, every E. S. must have a key More than one key: a set of attributes is the “designated” key In E-R diagrams: underline all attributed of the designated key 9/17/2020 37

A Multi-attribute Key IST 210 n n dept+number (e. g. IST 210) form a key for courses. Possibly, hours+room also forms a key. But we have not designated it as such. 9/17/2020 38

IST 210 Weak Entities & Keys n Weak entity sets: n n Does not have a primary key are meaningful only in the context of a relationship with an owning strong entity set 9/17/2020 39

IST 210 We Made it! 9/17/2020 40