Logical Data Structures

1. Logical Data Structures (Entity Relationship Diagrams)

2. Entities “something about which an e.g. organisation keeps data” customer Attributes “the properties of an entity” e.g. address Relationships “the connections e.g. between entities” customer places order

3. Entities An entity may be physical: an emplyoyee, a part, a machine Or conceptual: a project, an order, a course A particular employee is an “entity occurrence” of the employee entity

4. Attributes An EMPLOYEE has: A PROJECT has: an age a project leader a salary a start date an address a cost

5. Identifying Entities One or more attributes must form a unique identifier (or key) for a given occurrence of an entity. EMPLOYEES have: an employee number (or first name + second name) PROJECTS have: a project code

6. Relationships Relationships link entities: An employee works on a project A part is made on a machine and appears on an order An employee attends a course

7. Terms used in LDS LDSData Storagee.g. Entity File customer Entity Record fred smith Occurrence Atribute Field address

8. Describing Entities and Relationships Factory Entity (Master) Relationship (1:many) “makes” Component Entity (Detail)

9. Relationships and Entities Given a factory we can tell which components it makes Factory Given a component we can tell which factory makes it Component

10. One to Many Relationships For any occurrence of A there may be 0,1 or many occurrences of B A B

11. One to One Relationships Usually one entity should be an attribute of the other Factory Manager Sometimes the diagram requires one-to-one Factory

12. Masters can also be details Division Factory Component

13. Many to Many Relationships component order appears on One component Many orders Is for One order Many components

14. M:N Relationships hide missing entities order component A “link” entity order item What is the identifier for an “order item”? – order no + component no What are its attributes? – qty, price, description

15. Optional Masters Some orders come via sales staff, others directly from customers. So the link may be to a sales person or to another entity (the customer) Sales Person Order

16. The complete LDS division sales person factory order item order component

17. Developing an LDS Select initial entities Identify direct relationships Construct initial diagram Identify additional characteristics

18. Select initial entities Use existing data documents and files to identify: Entities Attributes

19. Identify direct relationships Look for links from one entity to another, e.g. references to other entities within an entity’s attributes b c a d A entity-entity cross reference grid may be useful a b c d

20. Draw initial diagram • 2. Determine degree of relationship • 3. Resolve M:N relationships • 4. Check 1:1 relationships and resolve

21. A wines and spirits wholesaler stocks a range of approximately 400 different lines and delivers by van on regular weekly runs to customers who are mainly off-licenses and clubs. The majority of orders are received by telephone and any one customer may place several orders during the week which must be accumulated and delivered in one consignment. When a customer telephones he expects to be advised on the availability of an item requested and, if not in stock, the alternatives that are available. At present, sales order forms are filled in by the telephone salesmen and sent to the warehouse where they are used for the despatch of the items. Hand-written despatch notes are used as the basis of the invoices which are typed and sent by post sometime later. Prepare a logical data structure (LDS) which reflects the above system.

22. Identify entities • Customer • Customer order • Item • Despatch note • Invoice

23. 2. Cross reference table order cust item d/n inv cust order item d/n inv

24. Initial Diagram customer Despatch note order invoice item

25. Final LDS customer Despatch note order order line invoice item