Exericse: Mobile itemization report

2020-11-07| YouTube explanation (German) | IHK final exam Winter 2003/2004, GH1 Exercise 2

A telephone provider wants to persist all of their customer data inside a database. After talking with the telephone provider's CEO you receive the following information:

The company provides different rates, like "Mobile Data only" or "Phone flatrate". Each of the company's customer can have any number of phones. Each of those phones has exactly one rate assigned. For the billing, a customer must provide his banking details. There can be only one bank account per customer. A bank account belongs to a bank. Each customer's call is tracked, so the customer can receive a mobile itemization report. Every call has unique call id.

Your task is to create a database model with all primary and foreign keys. Additional attributes (like firstname of customer) are not relevant.

Solution

Schema

Diagram

Table tariff {
  id integer [primary key, not null, unique]
}

Table bank {
  id integer [primary key, not null, unique]
  bic char(11) [not null, unique]
}

Table customer {
  id integer [primary key, not null, unique]
  // @see https://database-modelling.com/article?tag=MODELLING_BANK_ACCOUNTS
  iban char(34) [not null]
  bank_id integer [not null, ref: > bank.id]
  // we could also use the BIC as primary key
  // bic char(11) [not null, ref: > bank.bic]
}

Table phone {
  id integer [primary key, not null, unique]
  tariff_id integer [not null, ref: > tariff.id]
  customer_id integer [not null, ref: > customer.id]
}

Table call {
  id integer [primary key, not null, unique]
  phone_id integer [not null, ref: > phone.id]
  // a customer must be able to identify on his mobile itemization sheet.
  // maybe it's not a good idea to use the internal id but to provide another public id.
  guid uuid [not null]
}