03 ER Exercises

Exercise 1

Design an ER Model for a car rental system that manages the customers, the cars and their related elements. If a customer wants to rent a car, they should provide their personal ID card (9-digit ID, name, surname, birth date, address, release date, expiration date), driving license (10-digit drive license number, name, surname, birthdate, release date, expiration date and the list of vehicles they are allowed to drive, e.g. truck, car, motorcycle, bus, etc.) and credit card (16 digit number and expiration date). Then, the list of cars is shown to the customer. Each car is described by its brand, the price per day, the maximum speed, and the fuel consumption per km. Before proposing the car, the system checks whether a car inspection has been performed in the last 3 months. Such data includes all the inspections (described by date and the name of the company who took care of it), alongside the list of all the operations the car underwent in each inspection. As soon as the customer picks the car they want, the keys are provided to the customer and the system stores the rental invoice (described by rental date, the period for which the car has been rented, and the final price). When the car is returned, the customer is charged. As soon as the payment is completed, the system marks the rental invoice as “closed” and the customer is provided with his invoice.

Solution

Exercise 2

Design an ER Diagram for “Dungeons & Dragons RPG”. During the registration, a player is asked for username, password, password confirmation, e-mail, e-mail confirmation and when the registration is complete, they are assigned a unique 10-digits identifier To play the game, each player creates one or more characters and explores the world, defeating monsters and acquiring items. Each character is defined by a class (warrior, ranger, mage or cleric), experience points, name and strength score (derived from the strength of the items equipped). When a monster is defeated, a random amount of items and gold is provided to the player. Items can be of two types: generic items or weapons. Weapons can be equipped to improve the strength score of the character, although warriors can wield one or more (maximum 2) melee weapons, rangers can only wield one ranged weapon, mages and clerics can only wield one magical weapon. Each item also has a name, a price, a rarity and a unique identifier. monsters are defined by the amount of experience points they provide the player, their difficulty score (ranging between 1 and 20), a gold score (amount of gold dropped when defeated), an item score (amount of items dropped when defeated), a name and a unique identifier. Some monsters can be boss monsters, yelding to more loot and experience points. A monster can be defeated by one or more players. Players can add other players to their friend list.

Solution