Introduction to Data Modelling

1. Introduction to Data Modelling Revised by Ivor Perry Sept. 2003

2. Data Modeling in IS Development • Information System (IS) • “A system that processes data to produce information” • When analysing the existing system • View of current data in the system (existing data view) • Current processes that operate on the data and/or produce data (process view) • Making sure these tally (event/transaction view) Revised by Ivor Perry Sept. 2003

3. A view of proposed data in the system (proposed data view) • Required processes to operate on data and/or produce data (process view) to satisfy some user requirement • Making sure these tally (event/transaction view) Note No mention of a computer system or computerisation! • When designing a new system Revised by Ivor Perry Sept. 2003

4. Entity-Relationship Modelling • Top-down approach to data modelling • Start by identifying the entities and their associations/relationships • Draw up an ER Diagram (ERD) • using appropriate notation • Applying rules, translate ERD into a set of well-normalised tables The creative bit is the identification of appropriate entities and their relationships The rest is learning how to adhere to and apply appropriate rules of ER modelling correctly Revised by Ivor Perry Sept. 2003

5. ER Modelling - How it works • Entity • thing • independent existence • uniquely identifiable • something you need to keep a list of! • E.g. employee, order, machine, property • Nouns • Defined in terms of their properties Revised by Ivor Perry Sept. 2003

6. Attributes • Attribute • property of an entity • e.g. • an employee’s attributes might be: national insurance number, salary, date of birth • an order’s attributes might be: date of order, order number, customer ID • a machine’s attributes might be: model number, capacity, description • a property’s attributes might be: property address, number of bedrooms, price Revised by Ivor Perry Sept. 2003

7. Relationships • Relationship • association of some kind between entities • Verbs • e.g. employeeandmachine • relationship might be works on An employee works on exactly one machine orderandstock • relationship might be relates to An order relates to many stock items Revised by Ivor Perry Sept. 2003

8. Identifying Entities and Attributes • How to tell what is • an appropriate entity? • an appropriate attribute? • Experience • practice makes better • Reading hints in scenario • if scenario based - especially in exams! • Acting on information from fact finding investigations • Never expect to get it right the first time in the real world • (or the second!) • E-R modelling is an iterative process • It is a CREATIVE process Revised by Ivor Perry Sept. 2003

9. Reading the hints in the scenario or acting on information from fact finding investigations • Boundary of the problem • e.g, client wants • a computerised leisure centre booking system • a computerised video rental system • you to design a booking system for a village hall • Entities actually highlighted explicitly • “record details of bookings, videos and…” • Attributes actually highlighted even if entities aren’t • “the secretary keeps a record of the order number, date of order and what items of stock are held together with their respective quantity” Revised by Ivor Perry Sept. 2003

10. Nouns and verbs • sometimes provide a hint as to entities and their relationships • can see them as Enterprise Rules • e.g. “a customer can make several orders, but an order relates to just one customer” • Look for Enterprise Rules either at attribute level or at entity level • “a part description can be the same for several part numbers, although part numbers are unique” • “A part may be used in several machine repairs, and a machine repair may involve at least one part” Revised by Ivor Perry Sept. 2003

11. Entity Type vs Entity Occurrence • Project vs “P1, Machine Deployment System” • Employee vs “E1, MR JG Browning” • Machine vs “Lathe, #10110” Entity-Relationship Modelling == Entity(Type)-Relationship Modelling But:We are interested in Entity(Occurrence)-Relationship(Occurrence) Diagrams to illustrate concepts (more later) Revised by Ivor Perry Sept. 2003

12. Notations • Many notations are used for Entity-Relationship modelling. • The one I have used here (the Howe notation) is particularly good for beginners. • Seeing examples of more than one notation is useful as so many exist. • The concepts are the same whatever the notation. Revised by Ivor Perry Sept. 2003

13. = Entity = Relationship works on Employee Machine ER Diagram • An ER Diagramis a pictorial representation of Entity (type) level enterprise rules within the boundary of what exists or is required Basic notation of ER Diagram: • NB - the colours are not part of the syntax Always hard to name the relationship Revised by Ivor Perry Sept. 2003

14. Works on 1 1 Employee Machine Degree of a Relationship1:1 Relationship (one to one) • Enterprise Rules Represented: “An employeeworks on at most one machine” “A machineis worked on by at most one employee” Revised by Ivor Perry Sept. 2003

15. Entity Relationship Occurrence Diagram1:1 Employee “Works on” Machine E1 M1 E2 M2 E3 M3 E4 M4 E5 Revised by Ivor Perry Sept. 2003

16. Works on 1 M Employee Machine 1:M Relationship (one to many) • Naming convention from 1 to M here • Often we name relationships from M to 1 • Enterprise Rules Represented: “An employeemay work on one or many machines” “A machineis worked on by at most one employee” Revised by Ivor Perry Sept. 2003

17. Entity Relationship Occurrence Diagram 1:M Employee “Works on” Machine E1 M1 E2 M2 E3 M3 E4 M4 E5 Revised by Ivor Perry Sept. 2003

18. Works on M N Employee Machine M:N Relationship (many to many) • Enterprise Rules Represented: “An employeemay work on one or many machines” “A machineis worked on by one or many employees” • Equally you could use M:M instead of M:N Revised by Ivor Perry Sept. 2003

19. Entity Relationship Occurrence Diagram M:M Employee “Works on” Machine E1 M1 E2 M2 E3 M3 E4 M4 E5 Revised by Ivor Perry Sept. 2003

20. Supplier N M supplies P Customer Part Complex Relationships • Typically M:N:P for 3 entity relationships (M:M:M) • M:N:P:R for 4 entity relationships (very rare) or even more!!!! • Enterprise Rules Represented: • “A supplier may supply one or more parts to one or more customers” • “A customer may be supplied with one on more parts from one or more suppliers” • “A part may be supplied by one or more suppliers to one or more customers” Revised by Ivor Perry Sept. 2003

21. End of Lecture Revised by Ivor Perry Sept. 2003