ERD ENTITY RELATIONSHIP DIAGRAM

Management Information SystemsERDENTITY RELATIONSHIP DIAGRAMM. Rasti-BarzokiWebsite: rasti.iut.ac.ir92-2

Management Information SystemsERDEntity Relationship Diagrams for Data Modelling An Entity-Relationship Diagram (ERD) shows how the data thatflows in the system is organised and used.2rasti.iut.ac.ir92-2

Management Information SystemsERDEntity Relationship Diagrams for Data Modelling The elements of an ERD are the following:– Entity –– Attributes –– Relationships – associations between entities.3rasti.iut.ac.ir92-2

Management Information SystemsERDExamplesRelationship name:writesBookAuthorAn author writes one or more booksA book can be written by one or more authors.4rasti.iut.ac.ir92-2

Management Information SystemsERDRelationship types and instances Example5rasti.iut.ac.ir92-2

Management Information SystemsERDRelationship types and instances Relationship instances6rasti.iut.ac.ir92-2

Management Information SystemsERDRelationship types and instances Example7rasti.iut.ac.ir92-2

Management Information SystemsERDERD Types of entity relationships and example of theirrepresentation:8rasti.iut.ac.ir92-2

Management Information SystemsERDERD Examples9rasti.iut.ac.ir92-2

Management Information SystemsERDERD Cardinalities Cardinality - The number of instances of entity B that can be associatedwith each instance of entity A.– Minimum Cardinality or Modality - The minimum number of instancesof entity B that may be associated with each instance of entity A.– Maximum Cardinality - The maximum number of instances of entity Bthat may be associated with each instance of entity A10rasti.iut.ac.ir92-2

Management Information SystemsERDEntity Relationship Diagrams for Data ModellingRelationships A one-to-one relationship (1:1) exists when the maximumcardinality for each entity in the relationship is 1. A one-to-many (1:N) relationship exists when the maximumcardinality on one side is 1 and the maximum on the other sideis many. A many-to-many (M:N) relationship exists when the maximumon both sides is many.11rasti.iut.ac.ir92-2

Management Information SystemsERDERD ExamplesA patient history isrecorded for one andonly one patient12A patient must haverecorded at least onehistory, and can havemanyrasti.iut.ac.ir92-2

Management Information SystemsERDERD ExamplesA project must beassigned to at leastone employee, andmay be assigned tomany13An employee can beassigned to any number ofprojects, or may not beassigned to any at allrasti.iut.ac.ir92-2

Management Information SystemsERDExamples One-to-one relationship(1:1): One instance in anentity (parent) refers to oneand only one instance in therelated entity (child)14rasti.iut.ac.ir92-2

Management Information SystemsERDExamples One-to-many relationship(1:M): One instance in anentity (parent) refers toone or more instances inthe related entity (child)15rasti.iut.ac.ir92-2

Management Information SystemsERDExamples Many-to-manyrelationship (M:N): existswhen one instance of thefirst entity (parent) canrelate to many instances ofthe second entity (child),and one instance of thesecond entity can relate tomany instances of the firstentity.16rasti.iut.ac.ir92-2

Management Information SystemsERDExamples17rasti.iut.ac.ir92-2

Management Information SystemsERDMultiple c.irEnrollsStudent92-2

Management Information SystemsERDExample19rasti.iut.ac.ir92-2

Management Information SystemsERDSteps to Construct ERD Like constructing DFDs and data dictionaries, the construction of ERDs isan iterative and evolutionary process that requires refinement as abetter understanding of the system to be developed is achieved.211.Identify entities – use DFDs to identify multiple instance entities.2.Add attributes – requires detailed analysis and perhaps use ofrequirements gathering techniques.3.Assign identifiers or Primary keys – Each entity type must have an attributeor set of attributes that distinguishes one instance from other instances ofthe same type.4.Foreign Keys - The attributes of a foreign key in one entity are theattributes of a primary key in another entity.5.Identify Relationships –rasti.iut.ac.ir92-2

Management Information SystemsERDERD Three Types of Relationships– One-to-one relationships (1:1) Each store is managed by a single employee and each store manager(employee) only manages a single store.– EMPLOYEE (1) manages STORE (1)– One-to-many relationships (1:M) A painter paints many different paintings, but each one of them is painted byonly that painter.– PAINTER (1) paints PAINTING (M)– Many-to-many relationships (M:N) An employee might learn many job skills, and each job skill might be learnedby many employees.– EMPLOYEE (M) learns SKILL (N)22rasti.iut.ac.ir92-2

Management Information SystemsERDEntity Relationship Diagrams for Data Modelling Implement One-to-One Relationships– In a relational database, one-to-one relationships between entities can beimplemented by merging the two tables/entities together. Implement One-to-Many Relationships– Place the primary key of the entity that can occur only once as a foreign keyin the entity that can occur many times. Implement Many-to-Many Relationships– In a relational database, many-to-many relationships between entities canbe implemented by creating a new entity that links the other two entitiestogether. In most cases, the primary key of the new entity consists of theprimary keys of the entities that it is relating together.– Spit it into two one-to-many relationships!23rasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M Detailed Steps1. Add intersection table2. Flip relationship lines around so “many” side facesthe intersection table3. Add primary keys from original tables as fields in theintersection table4. Assign a name the intersection table5. Add other relevant fields to intersection table ifnecessary25rasti.iut.ac.ir92-2

Management Information SystemsERDMany-to-Many Relationship (N:M)StudentNEnrollsJoeCourseMGS 351MGA 201MGQ 301MGF 301MGB 301SallyTomJean26Mrasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (Before) M:N - Add intersection table which includes primarykeys from both tablesStudentNEnrollsPerson #27MCourseCourse #rasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (After) Add intersection tableStudentCoursePerson #28Course #rasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (After) Flip relationship lines around so “many” side faces theintersection table1NNStudentCourseCourse #Person #291rasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (After) Add primary keys from original tables as foreign keyfields in the intersection table1NNStudentPerson #301CoursePerson #Course #Course #rasti.iut.ac.ir92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (After) Assign a name the intersection table1NRegistrationStudentPerson #31NPerson #Course #rasti.iut.ac.ir1CourseCourse #92-2

Management Information SystemsERDTransforming ERD to Tables – N:M (After) Add other relevant fields to intersection table n #32Person #Course #rasti.iut.ac.irCourse #92-2

Management Information SystemsERDWhat should be used as Primary Key in the intersectiontable?Composite Primary Key or n #33Person #Course #rasti.iut.ac.irCourse #92-2

Management Information SystemsERDSteps to Construct ERD Important guidelines when creating ERDs:– Use consistent labels and names– Avoid unnecessary attributes– Determine cardinality correctly34rasti.iut.ac.ir92-2

Management Information SystemsERDDFDs and ERDs DFDs and ERDs are both used to model systems, but they show twovery different perspectives on the system.– Entities on an ERD often correspond to data stores on a DFD– Attributes on an ERD usually correspond to data elements (listed in thedata dictionary) that make up the data store and data flows on a DFD– Relationships on an ERD do not correspond to processes on a DFD.35rasti.iut.ac.ir92-2

Management Information SystemsERDReferencesSystem Analysis and Design, Sixth Edition.Authors: Gary B. Shelly, Thomas J. Cashman and Harry J. RosenblattPublisher: SHELLY CASHMAN SEWIES.36rasti.iut.ac.ir92-2

Management Information Systems امام على عليه السالم : ح ب س ي ن وُصولُ المَرءِ اِلى ُكلِّ ما َيب َتغيهِ مِه طيبِ َعيشهِ وَ اَمهِ سِرِبِ وَ َس َعةِ رِزِقِ ُِ هِ َّتهِ وَ َس َعةِ ُخل هِق ّ جس ت ج گ خ س انسان ، با نيت خوب و اخالق خوب ِ ، ب تمام آنچه رد وى آن ا ت ، از زند ى وش من ح م ي م س ي و ا ت ط و روزى زياد ، د ت ى يابد .

A one-to-one relationship (1:1) exists when the maximum cardinality for each entity in the relationship is 1. A one-to-many (1:N) relationship exists when the maximum cardinality on one side is 1 and the maximum on the other side is many. A many-to-many (M:N) relationship exists