Exericse: SIM card management


One of the previous exercises was about managing smartphones. Today we want to consider how we can implement a modern SIM card management including call history.

In a company, SIM cards are issued that can be used freely by employees. Those SIM cards are uniquely identified globally via the ICCID. The special thing about the SIM cards is that they are Multi-SIMs. Each SIM has several IMSIs. This makes it possible to use different mobile network providers.

All calls are imported into a database at the end of the month for the necessary billing between the company and the mobile phone provider. The import consists of the IMSI of the mobile phone provider, the duration of the call, the destination phone number and the time the call had started.

As soon as an employee leaves the company, they have to return the SIM card. As soon as a new employee is hired, a free SIM card becomes the property of the new employee.

In the user interface of the SIM card management, only their own calls should appear for an employee. Calls from previous owners of the SIM card must not appear and must not be included in any calculations.

The management of employees is not relevant for this exercise: Model a single table containing an internal and public primary key to identify an employee?


  • What is the format of an ICCID?
  • What is the format of an IMSI?
  • What could be a public primary key for an employee?
  • How can be identified if a SIM card is currently in use?