1 House sales

We have a dataset of real estate transactions in France over a year and we would like to store the data in a relational database.

For each transaction, we store the transaction date, the transaction type (for instance, sale or expropriation), and the value of the property involved.

Each transaction concerns a specific property, for which we record the property type (for example, house or apartment), the surface area, and, when applicable, the surface area of any annexed land, such as a garden. We also store the number of main rooms and the number of units composing the property.

In addition, each property is associated with an address composed of the street number, street type, and street name, together with the postal code and the city name and INSEE code. Each specific address at the street number level is identified in the cadastre by two values: the cadastral section and the land parcel. Each city belongs to a department, which is identified by a code and characterized by a name; each department, in turn, belongs to a region, which is also identified by a code and characterized by a name. Finally, for each city, spatial information is stored in the form of polygon geometries.

2 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.

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.