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.

One of your colleagues has started to design the database of an ordering application:

// Note: an article can be purchased from any supplier for a different price.
Table article_group {
  id integer
  name char(255) [not null]
}

Table article {
  id integer
  description text
  retail_price decimal(8,2) [not null]
  in_stock integer
  __1 datatype
}

Table order {
  id integer
  ordered_at datetime [not null]
  __1 datatype
}

Table order_item { 
  id integer
  order_id integer
  __1 datatype
  __2 datatype
}

Table supplier {
  __1 datatype
  name char(255) [not null, unique]
  address text [not null]
}

Table __ {
  // Hint: this table must have its own internal id
  __1 datatype
  __2 datatype
  __3 datatype
  __4 datatype
}

Complete the schema by adding the relevant primary and foreign keys and missing table(s). Also note that an article can be purchased from any supplier for a different price.

For managing the inventory of his PC shop, the owner uses an Excel sheet. The following screenshot contains the current format:

The owner wants to migrate this Excel sheet to a relational database.

• How must a relational database must look like?
• What primary keys have to be used?
• What is referential integrity?

A hardware development company wants to track their downtimes due to issues in their own network. Instead of using a program like Jira, they want to develop their own issue tracker.

Based upon the following screenshot you have to model an ER diagram:

A carsharing company provides two Excel tables and an invoice. Those artifacts are used for creating invoices for their customers.

Based upon this information we have to model the ER diagram.

Stamdata

Those two tables are containing the stamdata:

Invoice

This is the invoice the carsharing company sends to its customers:

How has the ER diagram to be modelled?

In today's exercise we want to model an internal web application for a company. Management and supervisors want to do employee interviews twice a year. Before each interview, each employee is assessed by himself and his supervisor independently. There are around 20 statements in which employees have to be assesed. Each statement belongs to one of -currently- five topics. For example the topic "Way of working" contains the statements

• You are working independently.
• You are working structured.

Each of these twenty statements can be valued by choosing a numerical value between 1 (does not apply) and 5 (applies fully) and allows a comment.

The company uses Microsoft Azure Cloud.

Additional questions

• How could users be authenticated?
• Where could the web application be deployed?
• What database management system would you use and why?
• How could be the numerical value range for an assessment be forced?
• What would happen if an employee is valued by two supervisors?

The following screenshot is a real-world sample from a catering service

Design a database model based upon the screenshot/PDF above.

• Orders are transferred from the PDF to the database; there is no need for a user authentication
• Each item is available through a defined menu
• Let's assume that the shop only delivers to customers in the United States

A simple blog application has to be developed.

A blog can have multiple blog posts. One blog post belongs to exactly one author. A blog post can have one of the following status: draft, published or revoked.

For better structuring, a blog post can have any number of tags. In addition to that, categories can be also used. One blog post can optionally belong to a category.

Additional questions

• How can the author be authenticated?
• Which additional attributes for a blog post might be of interest?