Data Modelling I

1. Data Modelling I

2. Plan • Introduction • Structured Methods • Data Flow Modelling • Data Modelling • Relational Data Analysis • Feasibility • Maintenance

3. Plan • Introduction • Structured Methods • Data Flow Modelling • Data Modelling • Relational Data Analysis • Feasibility • Maintenance

4. Data Model Requirements • Designers, end-users and programmers tend to view data in different ways • We need a communications tool that will enable models to be understood by all • Non-technical • Un-ambiguous (Connolly & Begg, 2000)

5. Entity Relationship Diagrams • Entity Relationship Diagrams are used to represent the data model graphically • They show entities in the system and the relationships between them • They may also show more advanced concepts • Also called the Logical Data Structure • There are (again) different notations

6. What will we know? • How to identify Entities and Attributes • Why we need a Key Attribute • How to describe the Relationships between Entities • How to draw an Entity Relationship Diagram

7. Business Data • We already know a lot about a business’ data from our Data Flow Diagrams • We find this from our clearly documented descriptions of: • Data Flows (the arrows in DFDs) • Data Stores (again from our DFDs)

8. Data Dictionary Entry (Mason and Wilcocks, 1994)

9. Structured Analysis + Design

10. Structured Analysis + Design

11. Data Modelling • There are various things that an organisation needs to store information about • In data modelling these things are called Entities (These are NOT the same as external entities in DFDS)

12. Entity • A thing that occurs more than once in the system about which we need to store information • …occurs more than once… • …about which we need to store information… (These are NOT the same as external entities in DFDS)

13. If an Entity = Student Occurrence = Wesley Snoopes Carmen Dizi Bard Pith If an Entity = Course Occurrence = BSc Info Systems BA Basket Weaving PhD Surfing Studies Instance • Each occurrence of an entity is called an instance or Entity Occurrence

14. Attribute • We need to store information about each entity • Example: What information does a University need to store about a student? • Name • Address • Phone Number • Course

15. Entity - Student Name Address Phone Number Course Occurrence Wesley Snoops 13 Manchester Blvd 0161 111 112 BA Basket Weaving Example

16. So… • Entity • A thing that occurs more than once in the system about which we need to store information • Attribute • Information we need to store about each entity

17. Identifying Entities • One way of identifying possible entities is to find all the nouns in a description of a system • But… • Some of these might be attributes • Some of these might be neither entities or attributes

18. Exercise • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

19. Exercise • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

20. Exercise • Some of the following are Attributes? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

21. Exercise • Some of the following are Neither? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

22. Entities and Attributes • Often an entity in one system will be an attribute in another system and might be neither in another system • This depends on the context of the system we are studying • We need to be clear about the system and its data and processing requirements

23. Exercise • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

24. Exercise • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system • Of the following, which are Entities? • A member of staff in a personnel system • A book in a library system • A customer in a sales system • A customer’s address in a sales system • A store in the UHS system • The shop in a greengrocer’s shop system • The author of a book in a library system

25. Big Jim’s Bikes • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

26. Identify Noun Phrases • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

27. Identify Noun Phrases • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

28. Identify Entities • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

29. Key Attributes • How do we identify an individual occurrence? • E.g. we might be searching for a student’s address • By adding a Key Attribute • E.g. we add a student number to uniquely identify each student

30. Identifying Entities • List some attributes for each entity in Big Jim’s Bikes • Identify the Key Attribute for each entity

31. Entity Relationship Diagrams • Entity Relationship Diagrams are used to represent the data model graphically • They show entities in the system and the relationships between them

32. Student Student Number Name Address Phone Number Course ERD Elements • E.G. In a student enrolment system Entity Name Key Attribute Non-Key Attributes

33. Customer Product Customer Number Name Address Phone Number Product ID Description Colour Weight ERD Elements • Entities - in Big John’s store system

34. Relationships • Entities in a system will be related to one another • For example • A student is related to a course because a student is enrolled on a course • A supplier is related to a product because they may supply that product

35. The Matrix • Sometimes the relationships are marked on a matrix • This is especially useful for large systems

36. Degree/Multiplicity • Relationships are always two way • Example System Description – Mulchester University • Each student may only enrol on one course • Each course typically has 150 students although this could be higher or lower • These describe the same SINGLE relationship

37. Degree/Multiplicity • Example System Description – Dulchester College • A student may enrol on any number of courses • Each course typically has 30 students although this could be higher or lower • Here the relationship between course and student is different

38. Degree/Multiplicity • Mulchester University • Each student may only enrol on one course • Each course has many students enrolled on it • Dulchester College • Each student may enrol on many courses • Each course has many students enrolled on it • They have a different degree/multiplicity

39. Degree/Multiplicity • Three alternatives • One-to-Many (1:M) • Many-to-Many (M:N) • One-to-One (1:1)

40. Student Course Student Number Course Code One-to-Many • Mulchester University Parent/Master Enrols Enrolled on Child/Detail

41. Student Course Student Number Course Code Many-to-Many • Dulchester College • Note the different configuration Enrolled on Enrols

42. Course Leader Course Staff Number Course Code One-to-One • Each course has one course leader and each course leader leads only one course Responsible for Responsibility of

43. Problem (1) • Mulchester University creates a new Course (Origami) during the summer vacation • The course details need to be put it on the system before any students can enrol • This means a course may exist with no students • We must be able to show this situation on our ERD

44. Student Course Student Number Course Code Solution (1) • Optionality • Each course MAY be enrols students(A course enrols 0, 1 or many students) • Each student MUST be enrolled on a course Enrols Enrolled on Dotted line indicates optional relationship

45. Customer Customer Number Name Address Phone Number Room Room Number Problem (2) • CD Hotels may have Customers who have not yet booked a room • They may have Rooms that have not yet been booked by anyone Books Booked by

46. Customer Customer Number Name Address Phone Number Room Room Number Solution (2) • Each Customer MAY book rooms • Each Room MAY be booked by Customers Book Booked by

47. Examples • 1 to many (1 or more) • 1 to 1 (or 0) • 1 to many (0, 1 or more) • Many (1 or more) to Many (1 or more) • Many (0, 1 or more) to 1 (or 0)

48. Exercise 1 • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

49. Identify Entities • Big Jim sells Bikes on the Internet. Customers email Big Jim with their details and those of the bike that they want to buy • Bike details are taken from the bike list posted on Big Jim’s Website, where every bike is listed with a unique stock number • On receipt of an email Big Jim checks the bike details and then requests payment in full • On receipt of payment Big Jim wraps the bike in brown paper and posts it to the customer using Royal Mail • Big Jim stores customer details in case they buy another bike. No details of payments are stored.

50. Bike Stock Number Draw ERD Customer • Each Customer can buy many bikes • Each bike is bought by one customer • Big Jim holds customer details before they buy a bike • Bike details are held before they are bought Customer Number Buys Bought by