IT Data Modeling Basics 2016 Alar Krist Swedabnk

IT Data Modeling Basics 2016 Alar Krist Swedabnk Group, Strategy and Architecture, Enterprise Information Architect IT Kolledz, Süsteemianalüüsi õppekava külalisõppejõud 5118398 Alar. Krist@Swedbank. ee

A basic approach of Data Modeling

Main pattern in real world - events relates parties and things Party Thing Event 3

Things in real world • Things: – – • Physical things (you can see and touch) • ATM, Fixed assets, Goods, physical product, Cash, Safe deposit box, , Branch building, • Geographic area (country, county, address), … Logical object: (you can not touch) • Classifiers (types, groups, …), Financial service / Product, Organization unit, Cost centre • Bookkeeping Account, Time units (calendar- year, month, week, day ) Parties: – – – Individual / private persons • Employee, Private customer, … Legal persons (set of individuals) • Legal customer, Company (with registering number in some registry), household, Association of individuals, Association of companies, … Robots (automated processes, created by persons) 4

Events in real world • Events – Agreed time events • new year, public holidays, … – Irregular events (related to things and parties, just happens, you can not plan) • accident, error, invoice is received, customer call to helpdesk … – Actions (at least 1 party participates, related with things and/or relationships, you can plan actions) • Make a payment, Conclude agreement between parties of providing and buying services, Create Invoice, send invoice, Buy goods, Sell product, call to Client, Log in, … 5

Relationship patterns in real world • Relationship patterns, such as Legal relations, structure of things, arrangements, business transactions, lifecycle events… Usually relationships are results of events – Party: Ownership of legal company, Marriage, Parent – child, … – Thing – thing: car – parts – details, building – room, ATM – building, Cash – ATM, … – Party – thing: party – vehicle, party - house, employee – branch, … – Thing – Event: vehicle – accident, vehicle – rental, ATM – ending cash, ATM – error, – Party – thing – event: agreement (customer, service provider, service, signing agreement), party rents a car, – Party – Event: party – birth, party – death, employee – holiday, – Event – event: car accident – fixing the car, ATM ending cash – adding cash, 6

Main patterns in real world - detailed Party type Event type Party Thing type Thing Event Party relationship Event relationship Thing structure 7

Examples of main pattern • Events releates parties – A Child borns to a mother (and to a father) – Woman and man get married, . . . diversed, • Events relates parties and things – Customer books a car, customer rents a car – Student studies a course, teacher leads a cource • Events happends with parties – Person borns, accident happends with person, person dies – Company has created (by persons)? , company has closed (by. . . ) – Design and construct a Car, sell, buy, register a car • Events happends with things – Accident with a car, preparation of a car 8

Simplified relationships - from real world to data stores Real world Our Mind Business glossary & Rule repository BIM (business information requirements) IT logical data model (IT design decisions) IT physical data model (structure of a data store) Things Concepts Terms & definitions Business information entity Entity Table / record Properties of Terms & things concepts definitions Business information attribute Attribute Column / field relationship between things relationship between business entities Logical primary and foreign keys Physical primary and foreign key constraints relationship between concepts Business rules how terms are related 9

Create Logical Data Models 10

What is a Logical Data model • • A Logical data model is a DB platform independent data model which contains objects such as all entities, attributes, logical data types of attributes, logical primary keys, foreign keys, alternative keys (logical indexes). Created by IT data architects. There are 2 main styles: – 1) logical (analyse) data model is close to Business Information Model and describes more requirements to the IT physical data model, not so much IT design decisions – 2) logical (design) data model describes how a physical data base should be realised (and how it is realised) 11

Main method – how to create Logical Data models • Define all entities – – • inputs: Business Information Model (BIM), Business glossary output: all entities placed in logical data model Define logical primary keys and foreign keys for all entities – – inputs: BIM, structural business rules in repository output: keys defined in logical data model • Find and define additional identification attributes for each entity • Find and define all other attributes of each entity (both for global world wide identification and local business identification) – inputs: data quality rules of unique identification and non duplicates – Output: alternative keys defined in a logical data model – – • inputs: Business Information Model (BIM), business glossary output: all attributes placed in logical data model define logical data types, max lengths, not null constraints fro all attributes of each entity – – inputs: business data quality rules, business requirements, company standards output: all attributes described in logical data model 12

Why logical data model • For communication IT design decisions on detail information structure of IT system data stores between IT people • A basis of integration different IT systems • For reducing integration costs between IT systems 13

Which is a good logical data model • should be understandable, clear, exact and complete • all entities and attributes should be correspond with business terms defined in the business glossary • Should be compliant with structural business rules and business data quality rules • Readable layout of diagrams: – Set of A 4 pictures, – 7 +/- 2 visual objects (contsepts) on one A 4 – Text size minimum 12 14

Logical Data Model example 15

Differences between BIM and IT Data Models BIM (Business Information Model) IT Logical DM / Physical DM Definition / essence Description of business information requirements (what data we need) Description of IT information (what data and how we store) Main purpose Describe and communicate business information requirements Define and communicate detail leve information stored in IT systems Method Concept modeling, BIM method Logical Data Modeling method Notation UML or ERD - Class /entity business decisions to see information on business concepts as a separate entity, such as Individual person IT decisions to store information on business concepts as a separate entity - Attribute business decisions to see information on a business concept as an attribute of another entity (such as First name and Birth date of an Individual person) IT decisions to store information on a business concept as an attribute - Association Business rules (multiplicity or sub type) IT decision how entities are related how information on business concepts is related 16

Additional material on Logical Data modeling • See presentations – Data modeling fundamentals – Data modeling ER method guide 17

Physical Data Modeling Basics

What is Physical Data Model • A physical data model is an implementation of a logical data model by adding database management system (DBMS) specific modeling information such as tables, columns, and specific data types. • Physical data model consists of descriptions of tables, columns, primary key constraints of tables, foreign key constraints between tables, data types, additional constraints (such as not null) of columns, indexes and other physical properties of data structures • In many cases the physical data model flows naturally from the logical data model. Each entity is implemented as a table, each attribute as a column, each relationship as a constraint etc. – In this case may be reasonable not to separate physical and logical data model. This is called a logical view of Physical data model. 19

Why we need a Physical Data Model • Describe and communicate physical data (DB) structure between IT people (architects, developers, DB administrators) • Assist developers and DB designers to implement Data structure 20

Physical Data Model example 1 21

Physical Data Model example 2 22

How to build a Physical Data model 1 • Build draft physical DM based on standard patterns and information needs, input artifacts: – – – • Analyze business requirements, business rules, BIM, logical DM input artifacts: – – – • business information model, logical data model Standard patterns of DM (Len Silverstone universal DM patterns, ) Similar DM-s of other business domains (you must know existing models) Clear description of business requirements Structural business rules, Data Quality business rules BIM, logical DM Decide and model tables, input artifacts: – – Logical Data Model, BIM, functional requirements, business rules Entity types: persons, things, events, rules, knowledge, … Relationship patterns: Party-party, Party-event, Thing-event, party-thing, … Company Standard patterns, reusable solution patterns, similar data models 23

Physical Data model 2 • Organise physical DM by packages and visual diagrams, alternatives: – by main business entities / tables – by sub systems • Define columns, primary key constraints, data types, lengths, not null constraints of each table, input artifacts – Logical data model, Business information model, business glossary – data quality rules – Domain list for Physical DM (standard names of columns with standard data type and length) • Define 1: m and 1: 1 foreign key constraints between tables – Logical data model, Business information model – structural business rules 24

Physical Data model 3 • Analyze and improve physical DM from different perspective: – – Is an attribute a pointer to common classifier, Are common classifiers modeled, where is the master Should be a column modeled as separate table Are lifecycle events of main tables modeled, how (dates, status changes, special table for events) – Are Statuses of tables modeled, how (only actual status, separate table for status history) – Is time modeled (Past, Actual, Future) and how (in one table, in separate tables) – Are data Changes modeled, how (change log table behind each table, common change log table, …) • Normalize physical DM to 3 NF (describe motivation of exceptions in comments) 25

Physical Data model 4 • Test information needs and improve DM by using: – Business Information needs / requirements – Draft reports – Query classes • Tune DM for high performance – Analyze Access patterns / class of queries (Select, Insert, Update) – De normalize if needed but describe motivation in comments – Add physical structures and properties (indexes, partition, …) – test and analyze query execution plan • Design archiving data structure – no archive – separate archive DB – archive tables in production DB – other 26

Additional materials on Physical Data model • See presentations – “Data modeling ER method guide” – “Physical data modeling” 27

Summary - Main Simplified Relationships Real world Our Mind Business glossary & Rule repository BIM (business information requirements) IT logical data model (IT design decisions) IT physical data model (structure of a data store) Things Concepts Terms & definitions Business information entity Entity Table / record Properties of Terms & things concepts definitions Business information attribute Attribute Column / field relationship between things relationship between business entities Logical primary and foreign keys Physical primary and foreign key constraints relationship between concepts Business rules how terms are related 28

Summary - Why IT data models • Understand an original – IT data structure • Describe reality (DB strutcture) in common (visual) language • Communicate between different IT stakeholders • Assist people to create new reality (DB) • Create new reality – • forward engineering of DB, other IT system components Mirror existing reality – reverse engineering of DB 29

Thank you!