Master 2 of International Management
IAE – Université Bordeaux IV
Information Systems

E/R Modeling (1)(SOLUTIONS)

Exercise 1.

Draw an ER diagram for the following. Be sure to indicate the existence and multiplicity for each relationship.

  1. A college runs many classes. Each class may be taught by several teachers, and a teacher may teach several classes. A particular class always uses the same room. Because classes may meet at different times or on different evenings, it is possible for different classes to use the same room.
  2. Each employee in an engineering company has at most one recognized skill, but a given skill may be possessed by several employees. An employee is able to operate a given machine-type (e.g., lathe, grinder) if he has one of several skills, but each skill is associated with the operation of only one machine type. Possession of a given skill (e.g., mechanic, electrician) allows an employee to maintain several machine-types, although maintenance of any given machine-type requires a specific skill (e.g., a lathe must be maintained by a mechanic).

Exercise 2.

[Based on exercise 2.2 in Ramakrishnan& Gehrke] Consider a relationship Teaches between teachers and courses. For each situation described below, give an ER diagram that accurately models that situation:

  1. Teachers may teach the same course in several semesters, and each must be recorded
  1. Teachers may teach the same course in several semesters, but only the current offering needs to be recorded (assume this in the following parts)
  1. Every teacher must teach some course
  1. Every teacher teaches exactly one course
  1. Every teacher teaches exactly one course, and every course must be taught by some teacher

You may assume that the only attribute of interest for teachers is their staff number while for courses it is the course code. You may introduce any new attributes, entities and relationships that you think are necessary.

Exercise3.

The SASLOCK Companyhas several shops. Each shop has 2 to 5 employees depending on its size. One of the employees is responsible of the shop.

The entireshops sell the same products. A product is characterized by its size, its color, the year of its creation and its model. The Marketing department sets, for each product and for each shop the minimum stock. It also sets the base price of each product (this is the price displayed on the shop window) and a maximum discount rate, these two values are the same for all the shops. We distinguish between the stock in the shop and the stock in the filing.

The company needs also to know the daily turnover for each shop and each product.

The remuneration of each seller is equal to a fixed amount plus a variable amount which is equal to a percentage of the turnover achieved by the seller during the month. This percentage is defined by the responsible depending on the results of the seller.

Givean ER diagram that accurately models the expected database.

Transform the obtained ER diagram into a relational database

Sellers(sellerName,fisrtName,lastName,remuneration,rate,shopNumber)
Shops(shopNumber,address,phoneNumber,responsibleNumber)

Products(prodNumber,name,colour,model,year,basePrice;maxDiscountRate)

Invoices(invoiceNumber,invoiceDate,sellerNumber)

Sells(shopNumber,prodNumber,shospStock,minStock)

Concerns(invoiceNumber,prodNumber,sellPrice,quantity)