IMS 5024 Information Systems Modelling

1. IMS 5024 Information Systems Modelling Data Modelling

2. Content • Second assignment • Pitfalls revisited • Nature of data modelling • Tools/Techniques used in data modelling • Place in ISD • Evaluation of data modelling • Reading list NB. Not all slides in this lecture will be discussed

3. Assignment two – OO modelling • Due Monday 4th of October – week 11 • Undertake in pairs from the same tute group. • Individual submissions accepted • Decide this week!

4. Assignment Pitfalls • Not starting early • Not confirming your understanding of the case and the requirements with me and Clyde • Not starting early • Not integrating the separate elements of the models • Not starting early • Assuming that Clyde and I do not coordinate our marking

5. Data modelling describes: • Structure • Meaning • Relationship of data

6. Data modelling help us to grasp: • static data in the organisation • fundamental building blocks of the system • different view of data from that of process modelling

7. Techniques used in Data Modelling • Normalisation • Data Dictionary • Entity relationship diagrams • What difference? • Use all?

8. Entity – thing of interest to the business Identifying an entity is subjective Entities can be: Real eg product Abstract eg quota Event remembered eg sale Role played eg employee Entity Employee

9. Relationship Between entities Cardinality (eg. One to many, one to one ect.) Degree of relationship (Unary, Binary, Ternary) Employee Department Relationship

10. Notation conventions • There are several - • but ER concepts are consistent • Entity • Cardinality and existential status • Degree

11. Examples of Cardinalities PATIENT EMPLOYEE Is assigned to Has Is married to PERSON PATIENT HISTORY PROJECT Mandatory cardinalities Optional and mandatory cardinalities Optional cardinalities

12. Relationship Cardinality Summary Mandatory 1 cardinality Many cardinality (1,2 …m) Optional (0 or 1) cardinality Optional (0 or many) cardinality

13. Employee Unary Relationship • Also called a recursive relationship Is married to Manages Person One to one One to many

14. Binary Relationship • A binary relationship is a relationship between instances of two entity types EMPLOYEE SUPPLIER CUSTOMER Leads Places Supplies Placed by Supplied by Lead by PROJECT SALES ORDER ITEM One to one One to many Many to many

15. Ternary Relationship • A ternary relationship is a relationship between instances of three entity types. PART supplies VENDOR WAREHOUSE

16. Attributes • Individual components of information that characterise an Entity – its constituent data • Types: • derived, • multi-valued, • composite, • simple

17. Example of attributes Name Address Emp-no Skill EMPLOYEE

18. Normalisation • the process of identifying the “natural” groupings of attributes • remove redundancy and incompleteness • a bottom up process • relies on Set Theory – well researched

19. Determining columns • One fact per column • Hidden data • Derivable data • Determining the key

20. Steps in Basic Normalisation Unnormalised table • Basic Normalisation is most often accomplished in three stages (these are the three basic normal forms) Remove repeating groups First Normal Form Remove partial dependencies Second Normal Form Remove transitive dependencies ThirdNormalForm

21. First normal form Step 1: Remove the repeating group • Why are repeating groups a problem? • Determine the key for the new group. Order-Item (Order#, Customer#, (Item#, Desc, Qty)) Order-Item (Order#, Item#, Desc, Qty) Order (Order#, Customer#)

22. Second and Third normal forms • Eliminate redundancy • Determinates – one or more columns (attributes) which determine other column values

23. Second and Third normal form procedure • Identify any non-key determinates • Establish a separate table for each determinate and the columns it determines • Name the new tables • Remove the determined columns from the original table

24. Third normal form A table is in third normal form if the only determinant(s) of non-key columns is (are) the primary key determinant(s)

25. Advanced normalisation • A set of tables can be in 3NF and still not be fully normalised • Further stages of normalisation are BCNF, 4NF, 5 NF and Domain key NF Refer to Date, C.J. (1990) An Introduction to Database Systems, Volume 1. 5th edn. Addison-Wesley Publishing Company, MA. pp543-557

26. Higher Normal forms • Occur infrequently • Most tables in 3 NF are already in BCNF, 4NF and 5 NF • Data in 3NF but not in 5NF has • Redundancy • Insert/update/delete anomalies • Difficulty in storing facts independently

27. Thinking in Data modelling • Hard Vs Soft ?? • Perspective • Objective vs Subjective • Nature of the organisation

28. Evaluation of Data modelling

29. Advantages of Data modelling • Data model is not computer oriented (agree??) • Model understandable by technologist and users • Does not show bias • UoD can vary (whole organisation or department) • Readily transformable into other models • Different data analysis techniques • Data modelling is rule-based

30. Disadvantages • Does not encourage or support user participation • Your view on the organisation – people or data • The idea that the model is THE model • Subjective view • One-side to data • Others??

31. Advantages of Normalisation • Rid the data of redundancy and other problems • Very well researched • Math basis for normalisation

32. Disadvantages of normalisation • Does not encourage or support user participation • Your view on the organisation –people or data • One-side to data • Can be done mechanistically without thought • Others??

33. Reading for next week • Johnstone, M.N., McDermid, D.C. (1999). Extending and validating the business rules diagram method. Proceedings of the 10 th Australian Conference on Information Systems. • Chapter 2 of Curran, Keller, Ladd (1998). SAP R/3 the business blueprint: Understanding the business process reference model. Prentice Hall.