1 / 42

Entity Relationship Diagram (ERD)

Entity Relationship Diagram (ERD). 22 February 2011. An Introduction to designing a Database. A database is a collection of data that is organized in such a manner that its contents can be readily accessed, managed and updated

Jimmy
Download Presentation

Entity Relationship Diagram (ERD)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Entity Relationship Diagram (ERD) 22 February 2011

  2. An Introduction to designing a Database • A database is a collection of data that is organized in such a manner that its contents can be readily accessed, managed and updated • An Entity Relationship model (ERM) may be used to aid the development of a database • An ER model may be represented pictorially by ERDs

  3. What is a Data Model? • A detailed model that captures overall structure of organizational data while being independent of any implementation considerations

  4. Rationale for using Data Modelling? • Typically data modelling is carried out parallel with other requirements analysis and structuring steps during the systems analysis phase • During this phase a data model is developed for the current system • Following this, the data model supporting the scope and requirements of the proposed system is developed

  5. ERD’s • Entity Relationship Diagram is a form of data modelling • Data modelling involves examining the data objects in a system and identifying the relationship between these objects

  6. What is an Entity Relationship Diagram (ERD)? • An ERD depicts data in terms of the entities and relationships described in an information system • The primary purpose of an ERD is to document the logical structure of a database

  7. Two Approaches to ERD’s • The two most popular approaches to contructing entity relationship diagrams are: • SSADM • Chen ERD • Visio provides stencils for constructing ERD’s using these apporaches

  8. ERD Symbols 2 Department Department Staff No 2 Department Staff No. employs employs

  9. An Entity • An entity is a thing of interest to a system about which information is kept • For example in a Hospital Administration System, some likely entities would include: Patient, Doctor, Operation, Ward • Each of these things are of interest to the system and would have data stored about them

  10. Choosing Entities • Example • A sale always starts with a customer receiving an estimate.The estimate then becomes an order. An order can be for one or more stock items. Each stock item belongs to a certain stock category (e.g. taps, sinks, cupboards etc.)

  11. An Attribute • An attribute is an item of data held about an entity • In computer systems an attribute is a field of information • Example : Sales System • Entity : Customer • Attributes : Customer Name, Customer Address, Customer Phone Number

  12. Attributes Continued.. • A key attribute uniquely identifies a specific occurrence of an entity • This may also be referred to as a primary key • Examples of primary keys include: • ISBN • Student Number • Staff ID

  13. For Example • In a Sales System, customer name and address are attributes of the entity customer. • The primary key is likely to be Customer Code or Customer ID as the unique identifier for each customer in the system.

  14. Designing a System: • Data Flow Diagramming • External entities, process, data stores • Answers question: “How will the data be used?” • Entity-Relationship Diagramming (ERDs) • Depict entities and their relationships amongst each other • Answers question: “What data will be captured in the database, and how will the data relate to each other?”

  15. Example : Babysitter Service The AITP Service Club wants to run a babysitting service. Customers call to request a sitter and the Club Coordinator assigns an employee to sit for the customer from a list of employees available for the particular day requested.

  16. Babysitting System • Data is used to: • Assign employee to job • Determine availability • Data to be captured in database: • Employee data • Job data • Customer data

  17. Entity-Relationship Model • A logical representation of the data of an organization or business area in graphical form

  18. ERD - Babysitter Service Employee Customer assigned to Related to Job

  19. Babysitting System • Employee • Attributes: EmpID, Name, Address, Phone Number, Available Hours, JobNo • Job • Attributes: JobNo, JobDate, JobTime, EmpID • Customer • Attributes: CusNo, Name, Address, Phone Number, JobNo

  20. Entity Instance • An entity instance is a single occurrence of an entity type. • An entity type is described just once in a data model while many instances of that data type may be represented by data stored in the system. • There is one EMPLOYEE entity type in most organisations but there may be hundred or thousands of instances of this entity type in the company.

  21. Choosing Entities • Example • A sale always starts with a customer receiving an estimate.The estimate then becomes an order. An order can be for one or more stock items. Each stock item belongs to a certain stock category (e.g. taps, sinks, cupboards etc.)

  22. From the narrative we can deduce: • An Estimate becomes and Order • An Estimate is issued to a particular Customer • An Estimate refers to a Stock Item(s) • An Order is placed by the Customer • An Order refers to a Stock Item(s) • A Stock Item refers to a particular Stock Category

  23. From the narrative we can deduce: • An Estimate becomes and Order • An Estimate is issued to a particular Customer • An Estimate refers to a Stock Item(s) • An Order is placed by the Customer • An Order refers to a Stock Item(s) • A Stock Item refers to a particular Stock Category

  24. Order System: ERD Order Refers to Places Stock Item Becomes Customer Issued to Refers to Estimate Refers to Stock Category

  25. Entity Instance • An entity instance is a single occurrence of an entity type. • An entity type is described just once in a data model while many instances of that data type may be represented by data stored in the system. • There is one EMPLOYEE entity type in most organisations but there may be hundred or thousands of instances of this entity type in the company.

  26. ERD Relationships • A relationship is an association between two entities that is important to the system. • We must also consider the extent to which each entity is related to another.

  27. The Cardinality of a Relationship • Cardinality defines the minimum and maximum number of occurrences of one entity for a single occurrence of the other • From the previous example we can say that: • A customer can place several orders if they wish • But an order can only be placed by one customer

  28. Cardinality • Cardinality is bi-directional • This means that it must be defined for both directions of the entity relationship.

  29. One-to-One Relationship (1:1) • A one-to-one relationship occurs where a single occurrence of one entity relates to one occurrence of a second entity. Estimate becomes Order An estimate can only become one order and an order is derived from one estimate.

  30. One-to-Many Relationship(1:N) • One-to-Many relationships occur when a single entity instance is related to many occurences of a second entity. Customer is issued with Estimate One customer can be issued with many estimates (the relationship is described as one-to-many)

  31. Many-to-Many Relationship (N:M) • Many-to-many relationships occur where many instances of an entity relate to many instances of a second entity • These relationships usually contain a hidden entity which should be extracted • Usually N:M relationships are broken into one or more pairs of one-to-many relationships

  32. N:M Many estimates refers to many stock items (or many stock items are referred to by many estimates) Estimate Refers to Stock Item

  33. Sales ERD Order Refers to Places Stock Item Becomes Customer Issued to Refers to Estimate Refers to Stock Category

  34. Cardinality • Optional Cardinality • Where the analyst considers whether an entity occurrence at one end of the relationship can ever be present in the system without the presence of the corresonding occurrence of the entity at the other end of the relationship • Mandatory Cardinality • When the occurrence of the entity at either end of the relationship must be present in the system

  35. Mandatory - Mandatory • Some relationships are mandatory and some are optional Is responsible for Doctor Patient Registered with A doctor must be responsible for one or more patients and a patient must be registered with one and only one doctor

  36. Optional – Mandatory Doctor Is responsible for PROJECT Registered with A doctor may be responsible for one or more patients and a patiente must be registered with one and only one doctor

  37. Mandatory – Optional • A doctor must be responisble for one of more patients and a patient may be registered with one and only one doctor Is responsible for Patient Doctor Registered with

  38. Optional – Optional • A doctor may be responsible for one or more patients and a patient may be registered with one and only one doctor Is responsible for Patient Doctor Registered with

  39. Relationship Cardinality

  40. Note • || denotes 1 and only

More Related