Business data modeling Car Rental example Alar Krist

Business data modeling Car Rental example Alar Krist Alar. Krist@Swedbank. ee 5118398 1

Simplified description of business 2

Simplified description of a car rental business The essence of the car rental business: customers rent cars We have many offices in many countries We have many cars we group cars by car types (small, medium, big) We assign rental price to each car type Customer can make bookings for car types (not for concrete cars) Customer visits an office and rents a free car or customer makes a rental agreement for a car • Customer returns the car to an office • Clerk creates an invoice to customer • Customer pays the invoice by card or by cash • • • 3

A main real life pattern how concepts are related Party Event Thing Events relates parties and things 4

Main types of business entities – parties, events, things The essence of the car rental business: customers rent cars A main method – mark main business concepts by different colors We have many offices in many countries We have many cars we group cars by car types (small, medium, big) We assign rental price to each car type Customer can make bookings for car types Customer visits an office and rents a free car or customer makes a rental agreement for a car • Customer returns the car to an office • Clerk creates an invoice to customer • Customer pays the invoice by card or by cash • • • Question: do we need store and manage information on all marked concepts? 5

Main candidates of business entities – parties, events, things • • • Office Country Car type Customer Booking Rental agreement Return Invoice Questions: is a rental agreement an event or a logical thing? is an invoice a logical thing or an event? 6

Main information needs – we need register and manage next data • Car: – VIN, registry number, producer, model, color • Customer: – Name, personal registration number, mobile phone numbers, e-mail addresses, – Passport number or ID card number, driver license number • Booking: – Car type, planned rental office, rental start date and time, planned return office, return date and time • Rental agreement: – Car, starting mileage, rental office, rental start date and time, planned return office, return date and time • Return: – Return office, return date and time, return mileage • Invoice: – Invoice date, invoice number, – invoice amount (number of rented days * daily price of the rented car type), VAT 7

Examples of business data definitions 8

Definitions of main business concepts • • • Office Country Car type Customer Booking Rental agreement Return Invoice A quick exercise – define the main concepts! 9

Business concepts for BIM: how concepts are related 10

Descriptions of structural business rules – how concepts are related • Car and Car type – R 1: One car type groups many cars; each car has exactly one car type • Customer and Booking – R 2: One customer makes many bookings; one booking is made exactly by one customer • Customer and Rental agreement – R 3: One customer concludes (zero or) many rental agreements; one rental agreement is concluded by exactly one customer. • Rental agreement and Office – R 4: Each rental agreement has exactly one actual rental office; one office may be an actual rental office for many rental agreements; – R 5: Each rental agreement has exactly one planned return office; one office may be a planned return office for many rental agreements; A quick exercise – define missing structural business rules! 11

BIM fragments and business rules: how concepts are related 12

Examples of business data Models 13

Real life patterns how entities are related Party Event Thing Booking Car type We apply the pattern for Car rental Customer Rental Car Return 14

Draft Business Information model Booking Car type Customer Name Booked car type Code Rental office and time Rental price Return office and time Customer Rental Car Name Costomer VIN Personal ID number Rented car Registry number Phone numbers Rental office and time Producer Mail addresses Return office and time Model Driver license Start mileage Color ID card or passport Return Status Customer Returned car Return office and time End mileage 15

Draft Business Information model Booking Country Car type Name Code Currency VAT % Office Rental Car Name Country City Address Phone numbers Mail addresses Opening times Return 16

Entity level BIM – business decision: on what concepts we need information 17

Attribute level BIM– business decision: what detail information is needed 18

Car rental ontology – business knowledge: how concepts are related 19

BIM context – business knowledge: how concepts are related 20

BIM context – business knowledge: how concepts are related 21

Examples of IT data models 22

Loogiline andmemudel variant 1 – IT arhitekti otsus 23

Loogiline andmemudel variant 2 - IT arhitekti otsus 24

Autorendi firma andmemudeli fragment – audit veerud 25

Autorendi firma andmemudeli fragment – Log tabel 26

Kliendiandmete loogiline andmemudel variant 1 27

Kliendiandmete loogiline andmemudel variant 2 28

Kliendiandmete ajalugu variant 1 29

Kliendiandmete ajalugu variant 2 30

Parameetrite ajalugu, tulevik ja kehtiv väärtus variant 1 31

Arvete arhiveerimine, variandid: a) arhiivitabel toodangu baasis b) arhiivitabel eraldi arhiivibaasis c) arhiivitabelid toodangu ja arhiivibaasis pluss 2 tasemeline arhiveerimine 32

Examples of IT data models – what exactly is behind IT data models 33

Loogiline andmemudel variant 2 – tabelite naide 1: M Klient_ID Nimi Klient_ID Lep nr Vs Aeg Auto_ID 1 Alar 1 123 07 okt 2 Mari 2 124 1 N: 1 Auto_ID Mark 33 33 VW 10 okt 44 44 Audi 125 25 okt 55 55 Ford 2 126 30 okt 44 1 127 07 nov 44 2 128 12 nov 55 34

Organisatiooni Loogiline andmemudel – tabelite naide 1: M Type ID Name N: 1 Type ID Start date Org unit ID Related Org unit ID name Master org unit ID 35

Examples of IT data store architecture – what data is stored in what data base 36

Äriandmed erinevates andmebaasides 1 Rent Auto Org CRM Rent->Arve 37

Autorendi firma peamised andmekogumid ja andmevood 1 ESB Arve Org – CRM – Rent - Auto - Org. struktuur, Kontor, Töötaja Kient, kontakt, Probleem Broneering, Rendileping, Tagastus Auto, rike, Remont Klient Töötaja HR rakendus RP – Arve, Konto, Kanne, Ost, müük Laekumine Bron, Rent, Tagastus Klient Kontor, Töötaja Arve Kontor CRM rakendus Rendi rakendus Auto Ost, müük, kanne, Auto rakendus RP rakendus 38

Äriandmed erinevates andmebaasides 2 Rent<-Auto Org->Rent<-CRM Rent->Arve 39

Autorendi firma peamised andmekogumid ja andmevood 2 ESB ESB Arve Kontor Klient Arve Auto Org – CRM – Rent - Auto - Org. struktuur, Kontor, Töötaja Kient, kontakt, Probleem Bron, Rent Tagastus, Kontor, Auto, rike, Remont RP – Arve, Konto, Kanne, Ost, müük Laekumine Ost, müük, kanne, Bron, Kontor, Töötaja HR rakendus Klient CRM rakendus Rent, Tagastus Rendi rakendus Auto rakendus RP rakendus 40

Autorendi firma peamised andmekogumid ja DW andmevood DW Org CRM Rent Auto RP ETL - Extract, Transform, Load Org – CRM – Rent - Auto - Org. struktuur, Kontor, Töötaja Kient, kontakt, Probleem Broneering, Rendileping, Tagastus Auto, rike, Remont RP – Arve, Konto, Kanne, Ost, müük Laekumine 41

Autorendi firma DW andmemudel – 1 – loading columns 42

Autorendi firma DW andmemudel – 1 – history 43