Tutorial 3 Data Modelling Why do data modelling

Tutorial 3 Data Modelling

Why do data modelling? Conceptual Logical view of data structures & Documented Observations/Meetings/Interviews Other Work Documents of all sorts eg Policies, Procedural Guides Read, comprehend & translate Eg using BPMN notations attributes required in inter-process flows Business Process Modelling Logical Data Modelling using UML Notations Data Modelling Physical Data Modelling using MS Access Entity Relationship Diagram Notations Selected Database System Implementation view of data structures & attributes required in inter-

Data Modelling Paradigms Database organisation concepts: ICT Terms Access Terms 1. Data files 2. Data fields/attributes 3. Records (groups of fields) 4. Database stored in a server machine Data File / Table Row 1 (Record 1) Row N (Record N) Excel or Data tables Data table columns Data table rows Groups of Data Files Database – Interlinking Data Files/Tables Column 1 (Field 1) …. . …… Column N (Field N) Data …… ……. . Data Column 1 …. . …… Column N Column 1 …. . …… Column (Field 1) (Field N) NN Column 1 …. . …… Column (Field 1) (Field. N) N) N Row 1 Data Column …. . …… Data Column (Field 1) 1 …… (Field N) N Row Data Row 111) Data …… ……. . …… Data (Record Column …. . ……. . Column (Field 1) 1…… (Field N) N Row 11)1) Data(Field 1) …… ……. . Data(Field N) (Record Row 11) Data …… …… ……. . Data (Record Data …… …… ……. . Data Row 11) Data …… ……. . (Record Data …… ……. . Data (Record Data 1) …… ……. . Data Data Row N Data …… ……. . Data Row. NNN) Data …… ……. . Data (Record Data …… ……. . Row NN) Data …… ……. . Data (Record N) Row NN) Data …… ……. . Data (Record

Data Modelling Data File / Table Row 1 (Record 1) Row N (Record N) Database – Interlinking Data Files/Tables Column 1 (Field 1) …. . …… Column N (Field N) Data …… ……. . Data Some fields are primary or foreign keys of data files/tables Column 1 …. . …… Column N Column …. . …… Column (Field 1) 11 (Field N) NN Column 1 …. . …… Column (Field 1) (Field. N) N) N Row 1 Data Column …. . …… Data Column (Field 1) 1 …… (Field N) N Row Data Row 111) Data …… ……. . …… Data (Record Column …. . ……. . Column (Field 1) 1…… (Field N) N Row 11)1) Data(Field 1) …… ……. . Data(Field N) (Record Row 11) Data …… …… ……. . Data (Record Data …… …… ……. . Data Row 11) Data …… ……. . (Record Data …… ……. . Data (Record Data 1) …… ……. . Data Data Row N Data …… ……. . Data Row. NNN) Data …… ……. . Data (Record Data …… ……. . Row NN) Data …… ……. . Data (Record N) Row NN) Data …… ……. . Data (Record N) We visually describe their relationships using data modelling languages Logical Data Modelling using UML Notations Metaphor example: Cantonese dialect Physical Data Modelling using MS Access Entity Relationship Diagram Notations Eg Mandarin dialect

Physical Data Modelling MS Access Entity Relationship Diagram Modelling The work you did for Part 2 sets up the data tables (ie data entities), and the keys and relationships and model them in the MS Access Relationship Screen

Review Tutorial Tasks

Tutorial Activity 3 Bunnings has operations in every Australian State. Each department can employ multiple employees (or no employee), but each employee must be assigned to one department only. Every sales order made must be assigned to one employee, and each employee can generate multiple sales orders (but it is possible that an employee will have no sales at all). Each sales order must include at least one product, and each product type can be sold many times. Data Entities List: 1. Department 2. Employee 3. Sales Order 4. Product 5. Product Type Relationship List: 1. Employs or Assigns – links department & employee 2. Generates – links employee & sales orders 3. Includes – links sale orders and products 4. {Classifies} – links product & product type 1. Which org perspective are you modelling? A Bunning’s state operation’s data model 2. Identify NOUNS and linking VERBS List of NOUNS Data Files List of VERBS Data Files’ Relationshiplines/links 3. Data entities are interlinked by relationship lines, described in terms of an explanation label and multiplicities 4. From the 2 lists, draw the data entity boxes and link them by using the: • verbs to label/describe their relationship-lines and • descriptive quantities to label the multiplicities

Bunnings has operations in every Australian State. Each department can employ multiple employees (or no employee), but each employee must be assigned to one department only. Every sales order made must be assigned to one employee, and each employee can generate multiple sales orders (but it is possible that an employee will have no sales at all). Each sales order must include at least one product, and each product type can be sold many times. Data Entities List: 1. Department 2. Employee 3. Sales Order 4. Product 5. Product Type Relationship List: 1. Employs or Assigns – links department & employee 2. Generates – links employee & sales orders 3. Includes – links sale orders and products 4. {Classifies} – links product & product type classifies Product 1. . * 1. . 1 Product Type 1. . * Includes 1. . 1 Sales Order generates 1. . 1 0. . * Employee 0. . * Employs/assigns 1. . 1 Department

One PRODUCT TYPE classifies one to many PRODUCT/S One PRODUCT is classified by one PRODUCT TYPE One SALES ORDER includes one to many PRODUCT/S One PRODUCT is included in one SALE ORDER classifies Product 1. . * 1. . 1 Product Type 1. . * Includes 1. . 1 Sales Order generates 1. . 1 0. . * One EMPLOYEE generates zero to many SALE ORDER/S One SALES ORDER is generated by one EMPLOYEE Employee 0. . * Employs/assigns 1. . 1 Tips 1. Active Voice Vs Passive Voice descriptions for relationships 2. Always start with One DEPARTMENT employs zero to many EMPLOYEE/S One EMPLOYEE is employed by one DEPARTMENT Or One DEPARTMENT assigns zero to many EMPLOYEE/S One EMPLOYEE is assigned by one Department

classifies Example for the GIVEN ANSWER Product Types Products Apple Fiji, Gala, Granny Smiths, etc Orange Navel, Valencia, etc Berry Strawberry, blueberry, etc Product 1. . 1 Product Type 1. . * Includes A sales order includes various apples & oranges only (no berries). Hence PRODUCT TYPE of “Berry” is NOT included in the sales order - that is why One PRODUCT (TYPE) may NOT be included in a SALES ORDER Sales Order generates 1. . 1 0. . * Employee 0. . * e m ea ni ng s 1. . 1 Sa m Given Answer 1. . * Employs/assigns 1. . 1 (Type) Department

Tutorial Activity 4 Dr Franklin runs a small medical clinic specializing in family practice 1. Which org perspective you are data modelling? Dr Franklin’s Medical Clinic’s data requirements She has many patients. Patients are established in the database prior to the first patient visit. 2. List of key Nouns identified: • Patient Visit (medical consultation, which is like ERP sales transaction) • Diagnostic Test When the patients visit the clinic, she may perform several tests to diagnose their conditions. She bills the patient one amount for the visit plus additional amounts for each test. Assume a patient visit could take place without any diagnostic tests and that the tests are established in the database before they are used by Dr Franklin. 3. List of Relationship descriptors • Visits (attends or comes to) - links patients and consultation • Takes place (involves or includes) – links consultations & tests

Tutorial Activity 4 Dr Franklin runs a small medical clinic specializing in family practice She has many patients. Patients are established in the database prior to the first patient visit. When the patients visit the clinic, she may perform several tests to diagnose their conditions. She bills the patient one amount for the visit plus additional amounts for each test. Assume a patient visit could take place without any diagnostic tests and that the tests are established in the database before they are used by Dr Franklin. 1. List of key Nouns identified: • Patient Visit (medical consultation, which is like ERP sales transaction) • Diagnostic Test 2. List of Relationship descriptors • Visits (attends or comes to) - links patients and consultation • Takes place (involves or includes) – links consultations & tests Patient 1. . 1 0. . * Attends / comes to Medical Consultation (Patient Visit) 0. . * Involves/Includes 0. . * Diagnostic Test

Tutorial Activity 4 Dr Franklin runs a small medical clinic specializing in family practice She has many patients. Patients are established in the database prior to the first patient visit. When the patients visit the clinic, she may perform several tests to diagnose their conditions. She bills the patient one amount for the visit plus additional amounts for each test. Assume a patient visit could take place without any diagnostic tests and that the tests are established in the database before they are used by Dr Franklin. One PATIENT attends zero to many CONSULTATION/S One CONSULTATION is attended by one PATIENT One CONSULTATION involves zero to many TEST/S One TEST is involved in zero to many CONSULTATION/S Patient 1. . 1 0. . * Attends / comes to Medical Consultation (Patient Visit) 0. . * Involves/Includes 0. . * Diagnostic Test

Tutorial Activity 4 Patient 1. . 1 0. . * Attends / comes to Medical Consultation (Patient Visit) 0. . * Involves/Includes 0. . * Diagnostic Test Given Answer

Tutorial Activity 5: More ERP events explicit Joe’s is a small ice-cream shop located near the local university’s football ground. Joe’s serves walk-in customers only. The shop carries 26 flavours of ice cream. Customers can buy cones, sundaes or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account 1. Which organisation perspective are you data modelling? Joe’s Ice Cream Sales Ordering & Fulfilment processes 2. List of Nouns: • Customers • Sales Transaction • Sales/Cash Receipts • Products • {Product Type} – Ice-cream cones, sundaes or shakes • {Employee} – one selling the products and another depositing sales receipts (the cash monies) 3. List of Relationships (note many are implicit (ie you have to define yourself using ERP knowledge) • {Serves} - links employees & sales transactions • {Buys} – links customers & sales transactions • Pays – links customers & cash receipts • {Receives} – links employees and sales/cash receipts • • {Leads to/results} – links sales and sale/cash receipts Deposits – links sales/cash receipts & Cash (Bank Account) Includes – links Sales transactions & products {Classified} – links products and implied product types

Joe’s is a small ice-cream shop located near the local university’s football ground. Joe’s serves walk-in customers only. The shop carries 26 flavours of ice cream. Customers can buy cones, sundaes or shakes. When a customer pays for an individual purchase, a sales transaction usually includes just one item. When a customer pays for a family or group purchase, however, a single sales transaction includes many different items. All sales must be paid for at the time the ice cream is served. Joe’s maintains several banking accounts but deposits all sales receipts into its main checking account Tutorial Activity 5: More ERP events explicit Product Type 1. . 1 Classifies 1. . * ↓ Product includes 1. . * 0. . * Sales (Order) Transaction serves 1. . 1 0. . * (Sales) Employee 1. . 1 transacts/buys Results in /leads to ↓ 1. . 1 Customer 1. . 1 (Payment Admin) Employee pays 1. . 1 0. . * Cash (Bank Account) deposits Cash Receipts (Monies) (See relationship descriptions in NOTES sections of this slide) 0. . * receives

REA – Resource, Events & Agents Formatted Data Models See Text books for these definitions REA is a diagram formatting technique for guiding new data modellers to organise and format their data models by grouping: • Resource entities on your Left Hand Side • Event entities in the middle • Agent entities on your Right Hand Side Of your drawing page Experienced data models don’t necessarily use this formatting guideline, especially when modelling more complex data models REA formatted Data Model EXAMPLE RESOURCES EVENTS AGENTS