Data Modelling Paradigms and Concepts
Data modelling is essential for understanding and organizing data structures in various views. It involves conceptual, logical, and physical representations of data entities and relationships. Database organization concepts, data interlinking, primary/foreign keys, and visual representation using modelling languages are key aspects explored in data modelling practices.
Download Presentation
Please find below an Image/Link to download the presentation.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.
You are allowed to download the files provided on this website for personal or commercial use, subject to the condition that they are used lawfully. All files are the property of their respective owners.
The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author.
E N D
Presentation Transcript
Tutorial 3 Tutorial 3 Data Modelling
Why do data modelling? Why do data modelling? Conceptual Logical view of data structures & attributes required in inter-process flows Documented Observations/Meetings/Interviews Other Work Documents of all sorts eg Policies, Procedural Guides Read, comprehend & translate Business Process Modelling Logical Data Modelling using UML Notations Data Modelling Eg using BPMN notations Physical Data Modelling using MS Access Entity Relationship Diagram Notations Selected Database System Implementation view of data structures & attributes required in inter- process flows
Data Modelling Paradigms Data Modelling Paradigms Database organisation concepts: ICT Terms 1. Data files 2. Data fields/attributes 3. Records (groups of fields) 4. Database stored in a server machine Excel or Access Terms Data tables Data table columns Data table rows Groups of Data Files Data File / Table Database Interlinking Data Files/Tables Column 1 (Field 1) .. Column N (Field N) Column 1 (Field 1) Column 1 (Field 1) (Field 1) Column 1 (Field 1) .. Column N (Field N) Column N (Field N) (Field N) Column N (Field N) Column 1 .. .. Column N .. Row 1 (Record 1) Data .. Data Row 1 (Record 1) Row 1 (Record 1) (Record 1) Row 1 (Record 1) Row 1 (Record 1) Data Data .. .. Data Data Column 1 (Field 1) Column 1 (Field 1) .. Column N (Field N) Column N (Field N) Row 1 Data Data .. .. Data Data .. Data .. Data Data Data .. .. Data Data Data .. Data Data Data .. .. Data Data Row 1 (Record 1) Data .. Data Data .. Data Data Data .. .. Data Data Data .. Data Data Data .. .. Data Data Data .. Data Row N (Record N) Data .. Data Row N (Record N) Row N (Record N) (Record N) Row N (Record N) Row N (Record N) Data Data .. .. Data Data Data .. Data Row N Data Data .. .. Data Data Data .. Data Data .. Data Row N (Record N) Data .. Data
Data Modelling Data Modelling Data File / Table Database Interlinking Data Files/Tables Column 1 (Field 1) .. Column N (Field N) Column 1 (Field 1) Column 1 (Field 1) (Field 1) Column 1 (Field 1) .. Column N (Field N) Column N (Field N) (Field N) Column N (Field N) Column 1 .. .. Column N .. Row 1 (Record 1) Data .. Data Row 1 (Record 1) Row 1 (Record 1) (Record 1) Row 1 (Record 1) Row 1 (Record 1) Data Data .. .. Data Data Column 1 (Field 1) Column 1 (Field 1) .. Column N (Field N) Column N (Field N) Row 1 Data Data .. .. Data Data .. Data .. Data Data Data .. .. Data Data Data .. Data Data Data .. .. Data Data Row 1 (Record 1) Data .. Data Data .. Data Data Data .. .. Data Data Data .. Data Data Data .. .. Data Data Data .. Data Row N (Record N) Data .. Data Row N (Record N) Row N (Record N) (Record N) Row N (Record N) Row N (Record N) Data Data .. .. Data Data Data .. Data Row N Data Data .. .. Data Data Data .. Data Data .. Data Row N (Record N) Data .. Data We visually describe their relationships using data modelling languages Some fields are primary or foreign keys of data files/tables Physical Data Modelling using MS Access Entity Relationship Diagram Notations Logical Data Modelling using UML Notations Metaphor example: Cantonese dialect Eg Mandarin dialect
Physical Data Modelling MS Access Entity Relationship Diagram Modelling 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 Review Tutorial Tasks
Tutorial Activity 3 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. 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 Relationship- lines/links 3. Data entities are interlinked by relationship lines, described in terms of an explanation label and multiplicities Relationship List: 1. Employs or Assigns links department & employee Data Entities List: 1. Department 2. Employee 3. Sales Order 4. Product 5. Product Type 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 2. Generates links employee & sales orders 3. Includes links sale orders and products 4. {Classifies} links product & product type
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. classifies 1..1 1..* Product Product Type 1..* Includes 1..1 generates 1..1 0..* Sales Order Employee Relationship List: 1. Employs or Assigns links department & employee 0..* Data Entities List: 1. Department 2. Employee 3. Sales Order 4. Product 5. Product Type Employs/assigns 2. Generates links employee & sales orders 1..1 Department 3. Includes links sale orders and products 4. {Classifies} links product & product type
classifies One PRODUCT TYPE classifies one to many PRODUCT/S One PRODUCT is classified by one PRODUCT TYPE 1..1 1..* Product Product Type 1..* Includes One SALES ORDER includes one to many PRODUCT/S One PRODUCT is included in one SALE ORDER 1..1 generates 1..1 0..* Sales Order Employee 0..* One EMPLOYEE generates zero to many SALE ORDER/S One SALES ORDER is generated by one EMPLOYEE Employs/assigns 1..1 Department One DEPARTMENT employs zero to many EMPLOYEE/S One EMPLOYEE is employed by one DEPARTMENT Tips 1. Active Voice Vs Passive Voice descriptions for relationships Or One DEPARTMENT assigns zero to many EMPLOYEE/S One EMPLOYEE is assigned by one 2. Always start with One
classifies Example for the GIVEN ANSWER Product Types Apple Orange Berry Products Fiji, Gala, Granny Smiths, etc Navel, Valencia, etc Strawberry, blueberry, etc 1..1 1..* Product 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 1..1 generates 1..1 0..* Sales Order Employee 0..* Given Answer Employs/assigns 1..1 (Type) Department
Tutorial Activity 4 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 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 Tutorial Activity 4 Dr Franklin runs a small medical clinic specializing in family practice Patient She has many patients. Patients are established in the database prior to the first patient visit. 1..1 Attends / comes to 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. 0..* Medical Consultation (Patient Visit) 0..* 1. List of key Nouns identified: Patient Patient Visit (medical consultation, which is like ERP sales transaction) Diagnostic Test Involves/Includes 0..* Diagnostic Test 2. List of Relationship descriptors Visits (attends or comes to) - links patients and consultation Takes place (involves or includes) links consultations & tests
Tutorial Activity 4 Tutorial Activity 4 Dr Franklin runs a small medical clinic specializing in family practice Patient She has many patients. Patients are established in the database prior to the first patient visit. 1..1 Attends / comes to 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. 0..* Medical Consultation (Patient Visit) 0..* Involves/Includes One PATIENT attends zero to many CONSULTATION/S One CONSULTATION is attended by one PATIENT 0..* Diagnostic Test One CONSULTATION involves zero to many TEST/S One TEST is involved in zero to many CONSULTATION/S
Tutorial Activity 4 Tutorial Activity 4 Patient 1..1 Attends / comes to 0..* Medical Consultation (Patient Visit) 0..* Involves/Includes 0..* Diagnostic Test Given Answer
Tutorial Activity 5: More ERP events explicit Tutorial Activity 5: More ERP events explicit 1. Which organisation perspective are you data modelling? Joe s Ice Cream Sales Ordering & Fulfilment processes 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. 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) 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. 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 Joe s maintains several banking accounts but deposits all sales receipts into its main checking account {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
Tutorial Activity 5: More ERP events explicit 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. Product Type 1..1 Classifies 1..* serves 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. includes (Sales) Employee Sales (Order) Transaction 1..1 Product 0..* 0..* 1..* 1..1 transacts/buys Results in /leads to 1..1 Customer pays All sales must be paid for at the time the ice cream is served. 1..1 Joe s maintains several banking accounts but deposits all sales receipts into its main checking account 1..1 0..* Cash Cash Receipts (Monies) (Payment Admin) Employee 0..* 1..1 (Bank Account) deposits receives (See relationship descriptions in NOTES sections of this slide)
REA REA Resource, Events & Agents Formatted Data Models 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: REA formatted Data Model EXAMPLE Resource entities on your Left Hand Side Event entities in the middle Agent entities on your Right Hand Side RESOURCES AGENTS EVENTS Of your drawing page Experienced data models don t necessarily use this formatting guideline, especially when modelling more complex data models
