Logical Data Structures Entity Relationship Diagrams Entities Attributes

Logical Data Structures (Entity Relationship Diagrams)

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

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

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

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

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

Terms used in LDS Data Storage e. g. Entity File customer Entity Record fred smith Field address Occurrence Atribute

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

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

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

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

Masters can also be details Division Factory Component

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

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

Optional Masters Sales Person Order 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)

The complete LDS division sales person factory component order item order

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

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

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

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

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.

1. Identify entities - Customer order - Item - Despatch note - Invoice

c u s t o r d e r 2. Cross reference table i t e m d / n cust order item d/n inv i n v

Initial Diagram customer Despatch note order invoice item

Final LDS customer order line item Despatch note invoice