1 / 44

IMS9001 - Systems Analysis and Design

IMS9001 - Systems Analysis and Design. Topic 5 DATA MODELLING: ENTITY RELATIONSHIP MODELLING. Data modelling. Focus on the information aspects of the organisation In a database environment many applications share the same data The database is a common asset and corporate resource

margretd
Download Presentation

IMS9001 - Systems Analysis and Design

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. IMS9001 - Systems Analysis and Design Topic 5 DATA MODELLING: ENTITY RELATIONSHIP MODELLING

  2. Data modelling • Focus on the information aspects of the organisation • In a database environment many applications share the same data • The database is a common asset and corporate resource • Corporate and application level data modelling

  3. Conceptual data modelling • A conceptual data model is a representation of organisational data • Captures the structure, meaning and interrelationships amongst the data • Independent of any data storage and access method • occurs in parallel with systems analysis activities

  4. Conceptual data modelling • Identification of information requirements • Allows integration of data across the organisation and across applications • Helps eliminate problems of data inconsistency and duplication across the organisation

  5. Conceptual data modelling • Techniques; • Entity relationship modelling • Normalisation • Data structure diagrams • Good modelling techniques are supported by rigorous standards and conventions to remove ambiguity and aid understanding

  6. Entity relationship modelling • Used for conceptual data modelling • Diagrammatic technique used to represent: • things of importance in an organisation - entities • the properties of those things - attributes • how they are related to each other - relationships

  7. Entity relationship modelling • Entity relationship (ER) models can be readily transformed into a variety of technical architectures • All information about the system’s data identified during conceptual data modelling must be entered into the data dictionary or repository • This assists in checking the consistency of data and process models

  8. Entity relationship modelling • data “objects” are things about which we wish to store information • ER models show the major data objects and the associations between them • ER models are useful in the initiation, analysis and design phases

  9. Entity • something of interest about which we store information eg. EMPLOYEE SALES ORDER SUPPLIER • often identified from nouns used within the business application • should be LOGICAL (not physical)

  10. Identifying entities • entities are subjective (i.e. they reflect the viewpoint of the system) and can be: Real eg VEHICLE Abstract eg QUOTA Event remembered eg LOAN Role played eg CUSTOMER Organisation eg DEPARTMENT Geographical eg LOCATION

  11. Representing entities • we represent an entity by a named rectangle • use a singular noun, or adjective + noun • refer to one instance in naming CUSTOMER PART-TIME EMPLOYEE

  12. Entity types and instances • an entity type is a classification of entity instances eg. BN Holdings ABC Engineering Acme Corp. Ltd. SUPPLIER

  13. Entity types are logical • E.g. in a sales and inventory system there might be 3 physical forms of data: • a stock file • product brochures sent customers enquiring about products • a product range book used by salespeople when calling on customers to take orders which could be represented by one logical entity PRODUCT

  14. Entity types are logical • E.g. in a Student Records System there might be an entity type STUDENT which represents some of the data used in several physical forms of data: • Student re-enrolment forms • Subject class lists • Student results file The ER model identifies the minimum set of data objects necessary to construct the data used within the system in its various physical forms.

  15. Relationship • is an association between two entities • we may wish to store information about the association • often recognised by a verb or "entity + verb + entity" eg CUSTOMER places ORDER • relationships capture the "business rules" of the system

  16. Representing relationships • we represent a relationship as a line between two entities • the relationship is named by a meaningful verb phrase which should indicate the meaning of the association • relationships are bi directional so naming each end of the relationship conveys more meaning SUPPLIER ITEM supplies Supplied by

  17. Relationship types and instances • a relationship type is a classification of relationship instances Marketing employs Sue Black Finance employs Bill Brown MIS employs John Smith DEPT EMPLOYEE employs

  18. Cardinalities in relationships • The cardinality of a relationship is the number of instances of one entity type that may be associated with each instance of the other entity type.

  19. Examples of cardinalities One to One One to Many Many to Many EMPLOYEE CUSTOMER SUPPLIER placed by supplied by led by places supplies leads PROJECT SALES ORDER ITEM

  20. Nature of relationships We can indicate whether relationships are optional or mandatory: • A customer MAY place many sales orders • Each sales order MUST be placed by one customer CUSTOMER SALES ORDER places placed by

  21. Notations EMPLOYEE EMPLOYEE Is attended by attends attends COURSE COURSE Notation used in Hoffer et al (1999) Notation we are using

  22. Relationship degree • The degree of a relationship is the number of entity types that participate in the relationship. • The most common relationships in ER modelling in practice are: unary (degree one) binary (degree two) ternary (degree three)

  23. Unary relationships • A unary relationship is a relationship between instances of one entity type (also called a recursive relationship) manages Has component ITEM EMPLOYEE Reports to Is a component of

  24. Binary relationships • A binary relationship is a relationship between instances of two entity types and is the most common type of relationship encountered in practice. has copy MOVIE VIDEO TAPE Is a copy of

  25. Ternary relationships • A ternary relationship is a simultaneous relationship between instances of three entity types. • A ternary relationship is NOT the same as three binary relationships between the same three entity types.

  26. Examples PROJECT PROJECT PROGRAMMER PROGRAMMER LANGUAGE LANGUAGE 3 independent sets of pairs e.g. Mary uses COBOL Mary works on HR Project COBOL is used in the HR Project Triplets e.g. Mary uses COBOL on HR Project

  27. Example ER model employs DEPARTMENT EMPLOYEE employed made by makes places CUSTOMER SALES ORDER placed by is on is for ITEM

  28. Associative Entities (Gerunds) • An associative entity (or gerund) is a relationship that a data modeller decides to model as an entity type • As both entities and relationships can have attributes, this is possible CUSTOMER CUSTOMER Is made by makes Is ordered by SALES ORDER orders PRODUCT PRODUCT Is on has

  29. Multiple relationships • It is common to have two or more relationships between the same entities. • They represent different business rules. Has working Is working on EMPLOYEE PROJECT Has eligible Is eligible for

  30. Modelling Time-dependent Data • Some data values vary over time and it may be important to store a history of data values to understand trends and for forecasting. E.g. for accounting purposes we are likely to need a history of costs of material and labour costs and the time period over which each cost was in effect. • Modelling time-dependent data can result in changes to entities, attributes and relationships.

  31. Modelling Time-dependent Data • One technique is to store a series of time stamped data values. These values can either be represented as repeating data or as an additional entity called PRICE HISTORY. PRODUCT has PRODUCT PRICE HISTORY belongs to Price Effective date

  32. Modelling Time-dependent Data • Relationship cardinality can change. DEPT EMPLOYEE Works for Has working DEPT EMPLOYEE Has worked for Had working

  33. Entity subtypes and supertypes • some entities can be generalised (or specialised) to form other entities • an entity subtype is made up from some of the instances of the entity E.g. the entity types motor car truck train Can be grouped together to form the entity supertype transport vehicle

  34. Entity subtypes and supertypes • Example entity subtype: the entity type EMPLOYEE includes the subtype SALESPERSON EMPLOYEE SALESPERSON

  35. Entity subtypes • entity subtypes are included in the ER model only when they are of use - they may participate in relationships and have additional attributes DEPARTMENT employed employs EMPLOYEE CUSTOMER services SALESPERSON served by

  36. Multiple entity subtypes • Entity types may have multiple subtypes • Entity subtypes may be nested PROPERTY EMPLOYEE COMMERCIAL RESIDENTIAL PART-TIME METROPOLITAN FULL-TIME COUNTRY

  37. Entity subtypes Multiple entity subtypes should be • Non-overlapping (disjoint) • Collectively exhaustive This enables easier translation to a relational design EMPLOYEE PROPERTY METROPOLITAN ? ? PART-TIME RESIDENTIAL SALARIED COMMERCIAL

  38. Building a basic ER model • 1. identify and list the major entities in the system • 2. represent the entities by named rectangles • 3. identify, draw, name, and quantify relationships • 4. indicate mandatory/optional nature of relationships • 5. revise for entity subtypes where appropriate

  39. Eliciting information for an ER model • fact-finding and information gathering techniques are used to determine the entities and relationships • identify both existing and new information • existing documents are particularly useful e.g. forms, paper-based and computer files, reports, listings, data manuals, data dictionary • existing and new business rules for information are often difficult to elicit from documents ... it is essential to speak directly to the client

  40. ER modelling difficulties • is a given object an entity or relationship ? • are two similar objects one entity or two ? • is a given object an entity or an attribute of (data item about) an entity? e.g. EMPLOYEE and EMPLOYEE SPOUSE do we need to store data about the object? • what is the 'best' data model ?

  41. Quality dimensions • Correctness • Completeness • Understandability • Simplicity • flexibility

  42. ER models and DFDs • Do not to confuse entities with sources/sinks and relationships with data flows • TREASURER is the person entering data; there is only one person and hence it is not an entity type • ACCOUNT has many account balance instances • EXPENSE has many expense transactions • EXPENSE REPORT contents are already in ACCOUNT and EXPENSE; it is not an entity type TREASURER ACCOUNT EXPENSE EXPENSE REPORT

  43. Integration of ER Models with DFDs • All data elements represented in data flow diagrams for a system (data flows and data stores) MUST correspond to entities and their attributes in the ER model placed by ORDER CUSTOMER made up of for ORDER LINE PRODUCT

  44. References • HOFFER, J.A., GEORGE, J.F. and VALACICH (2005) Modern Systems Analysis and Design, (4th edition), Pearson Education Inc., Upper Saddle River, New Jersey, USA. Chapter 9 • WHITTEN, J.L., BENTLEY, L.D. and DITTMAN, K.C. (2001) 5th ed., Systems Analysis and Design Methods, Irwin/McGraw-HilI, New York, NY. Chapter 7 • BARKER, R. (1989) CASE*METHOD Entity Relationship Modelling, Addison-Wesley, Wokingham Chapters 4,5

More Related