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
- Slides: 43