Introduction to databases — Tutorial 1

Data modeling

In this tutorial you’ll learn:

  • How to create a conceptual schema of a database.
  • How to draw an entity-relationship (ER) diagram.
  • How to translate a conceptual model into a logical model.

Prerequisites:

1 Database of a social network platform

A social network platform wants to design a relational database to store information on its users. For each user, the platform keeps its nickname, that uniquely identifies the user in the platform, first and family name, geographic location (city and country) and email address; the user can register as many email addresses as s/he wishes. Any user can share content on the platform; each post is characterized by its content, date, time and, when available, the geolocation (latitude, longitude). Optionally, users can tag one or more friends in their posts.

Two users are linked by a friendship relationship if both agree on befriending each other; a user can also follow another user without necessarily befriending her. For any type of relationship (friendship or follower), the platform registers the date when the relationship is established.

1.1 Exercises

Exercise

Exercise 1.1 Give the conceptual schema of the database with an ER diagram.

Solution

Social network solution

Exercise

Exercise 1.2 Translate the conceptual schema into a logical schema. For each table, underline the primary key and specify the foreign keys.

Solution

The collection of tables is the following:

UserAccount  (nickname, first_name, last_name, city, country)

Post         (post_id, content, date, time, lat, long, nickname)

EmailAddress (email_address, nickname)

Relationship (nickname_src, nickname_dst, type, date)

Tag          (post_id, nickname)

The foreign keys are the following:

Post(nickname) → UserAccount(nickname).

EmailAddress(nickname) → EmailAddress(nickname).

Relationship(nickname_src) → UserAccount(nickname).

Relationship(nickname_dst) → UserAccount(nickname).

Tag(post_id) → Post(post_id).

Tag(nickname) → UserAccount(nickname).

2 Database of a banking system

The following figure shows the ER diagram with the conceptual schema of a banking system database.

The conceptual schema of the bank database

Figure 2.1: The conceptual schema of the bank database

Each bank is identified by a unique code and name, and has one or several branches. A branch is responsible for opening accounts and granting loans to customers. Each account is identified by a number (acct_nbr) and is either a checking or savings account (property acct_type). Each customer is identified by its social security number (ssn); a customer can be granted several loans and open as many accounts as s/he wishes.

2.1 Exercises

Exercise

Exercise 2.1 Which primary key would you choose for the entity Bank? Justify your answer.
Solution

Since no two banks have the same code_bank or name, either property can be chosen as the primary key of the entity Bank. Both can be considered as valid candidate keys.

Exercise

Exercise 2.2 Would you consider {code_bank, name} as a valid candidate key for the entity Bank? Justify your answer.
Solution

The answer is no. While there aren’t any banks that have the same value for {code_bank, name}, two subsets ({code_bank} and {name}) are candidate keys.

Exercise

Exercise 2.3 Complete the diagram in the figure by adding the cardinalities to the relations. Justify your choices when any ambiguity arises.
Solution

Banking system solution

Exercise

Exercise 2.4 Translate the conceptual schema into a logical schema. For each table, underline the primary keys and specify the foreign keys.
Solution

The collection of tables is the following:

Bank     (code_bank, name, address)

Branch   (branch_id, address, code_bank)

Account  (acct_nbr, acct_type, balance, branch_id, ssn)

Loan     (loan_nbr, loan_type, amount, branch_id, ssn)

Customer (ssn, first_name, last_name, telephone, address)

The foreign keys are the following:

Branch(code_bank) → Bank(code_bank).

Account(branch_id) → Branch(branch_id).

Account(ssn) → Customer(ssn).

Loan(branch_id) → Branch(branch_id).

Loan(ssn) → Customer(ssn).

3 Car dealership database

We want to design the database of a car dealership. The dealership sells both new and used cars, and it operates a service facility. The database should keep data about the cars (serial number, make, model, colour, whether it is new or used), the salespeople (first and family name) and the customers (first and family name, phone number, address). Also, the following business rules hold:

  • A salesperson may sell many cars, but each car is sold by only one salesperson.
  • A customer may buy many cars, but each car is bought by only one customer.
  • A salesperson writes a single invoice for each car s/he sells. The invoice is identified by a number and indicates the sale date and the price.
  • A customer gets an invoice for each car s/he buys.

When a customer takes one or more cars in for repair, one service ticket is written for each car. The ticket is identified by a number and indicates the date on which the car is received from the customer, as well as the date on which the car should be returned to the customer. A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.

3.1 Exercises

Exercise

Exercise 3.1 Give the conceptual schema of the database with an ER diagram.
Solution

Car dealership solution

Exercise

Exercise 3.2 Translate the conceptual schema into a logical schema. For each table, underline the primary keys and specify the foreign keys.
Solution

The collection of tables is the following:

  Car          (serial_number, make, model, colour, is_new)
  Customer     (cust_id, cust_first_name, cust_last_name, cust_phone)
  Invoice      (invoice_number, date, price, car_serial_number, sp_id, cust_id)
  Salesperson  (sp_id, sp_first_name, sp_last_name)
  Mechanic     (mec_id, mec_first_name, mec_last_name)
  Ticket       (ticket_number, date_open, date_return, car_serial_number)
  Repair       (ticket_number, mec_id)

The foreign keys are the following:

Invoice(cust_id) → Customer(cust_id).

Invoice(car_serial_number) → Car(serial_number).

Invoice(sp_id) → Salesperson(sp_id).

Ticket(car_serial_number) → Car(serial_number).

Repair(ticket_number) → Ticket(ticket_number).

Repair(mec_id) → Mechanic(mec_id).