Solutions for UML Class to Relational

New version in PowerPoint
Old versions (handwritten)

Tip: These are the solutions of the UML Class to Relational exercises exercises. They should be used as the starting point for building SQL scripts for building each schema.

New version in PowerPoint

Solutions for UML to Relational Schema mapping exercises

(Converted using PowerPoint by Prof. José Ornelas)

Old versions (handwritten)

Veterinary Clinic

Animal(id, name, breed →Breed, owner→Owner)

NOT NULL(breed)

Breed(name)

Condition(name)

Appointment(id, date, time, animal →Animal, physician→Physician)

NOT NULL(animal)

NOT NULL(physician)

NOT NULL(date)

NOT NULL(time)

ConditionsIdentified(condition → Condition, appointment → Appointment)

FrequentCondition(breed→Breed, condition → Condition)

Owner(id, name, person, address, phone_no)

NOT NULL(name)

Physician(id, name, person, address, phone_no)

NOT NULL(name)

Auto Repair Shop

Person(id, name, address)

NOT NULL(name)

Employee(id→Person)

Owner(id→Person)

Service(id, car_km, hours_spent, adm_date, finish_date, employee → Employee, car →Car)

CHECK(strftime(‘%s’, finish_date) >= strftime(‘%s’, adm_date))

CHECK(hours_spent >=1)

NOT NULL(employee)

NOT NULL(car)

Car(plate, color)

NOT NULL(car)

Model(id, description, make →Make)

NOT NULL (make)

Comment: This considers that model names are not unique. For example, there are multiple generations of 'Toyota Corolla', which can have the same name but are effectively different models. You could also consider the model names to be unique. In that case, the description could be the primary key. Any tables referencing this one would need to have their foreign keys updated as well.

Make(name)

Part(id, serial_no, service →Service)

NOT NULL(service)

NOT NULL (serial_no)

Comment: even if the serial number is unique within a given part type, there can be collisions between serial numbers of different part types

PartType(id, current_price)

NOT NULL(current_price)

CHECK(current_price > 0)

Compatibilitie(car_model→Model, part_type→PartType)

Restaurant

Person(id, name, address)

NOT NULL(name)

Waiter(id→Person, identifier, phone_number)

Comment: Why 2 Ids? Identifier is what will show up on a waiter's badge (has a meaning), while the id is the internal identifier of the record, i.e. a surrogate (i.e. generated, and without meaning) key

NOT NULL(identifier)

NOT NULL(phone_number)

UNIQUE(identifier)

Client(id→Person, tax_id)

Meal(id, served_to →Client, served_by → Waiter, date, start_time, end_time, table → Table)

CHECK(start_time < end_time OR end_time IS NULL)

Comment: if end_time is null, the meal is still going on, but we should be able to insert the record anyway, and then update it when the meal is finished.

NOT NULL(served_to)

NOT NULL (served_by)

NOT NULL (date)

NOT NULL (start_time)

Dishe(identifier, name)

CHECK(name IS NOT NULL)

CHECK(

(

SELECT COUNT(*)

FROM QuantitiesNeededForDishes

JOIN Dishes ON Dishes.identifier = QuantitiesNeededForDishes.dish

WHERE Dishes.identifier = NEW.identifier

) >= 1

)

Comment: To enforce the 1..* constraint at the database )at least one ingredient in every dish), logic must be added to both the Dish and QuantitiesNeededForDish relations, so that no dish can be left without ingredients both upon creation of the dish and also upon modification of a dish's ingredients. We will see this later, as it is the most complicated part of this example; the constraint is left here as an example.

Ingredient(name, meas_unit, qtt_stock)

CHECK(meas_unit IS NOT NULL)

CHECK(qtt_stock >= 0)

QuantitiesNeededForDishe(dish→Dish, ingredient →Ingredient, qtt_needed)

CHECK(qtt_needed > 0)

TimesServed(dish→Dish, meal →Meal, times_served)

CHECK(times_served > 0)

Table(identifier, capacity)

CHECK(capacity > 0)

Deliveries

Courier(id, name, vat_no, phone_num)

Customer(id, vat_no,name, phone_num, address, closest_delivery_center → DeliveryCenter)

Comment: a complete and disjoint generalization means that we can omit the Person relation.

Package(identifier, weight, urgency, dropoff_point →DeliveryCenter, sender →Customer, recipient →Customer, courier →Courier)

CHECK(urgency = “normal” OR urgency =”urgent”)

CHECK(weight > 0)

NOT NULL(dropoff_point)

DeliveryCenter(id, name, address)

InternalTracking(package→Package, delivery_center→DeliveryCenter, date_time_of_arrival, date_time_of_departure)

CHECK(date_time_of_arrival >= date_time_of_departure)

Furniture Factory

Piece(reference, price, line → Line)

NOT NULL(line)

NOT NULL(price)

CHECK(price > 0)

Line(id, type)

NOT NULL(type)

UNIQUE(type)

Component(code, price, component_type → ComponentTypes)

NOT NULL(component_type)

NOT NULL(price)

CHECK(price > 0)

PieceComponent(piece → Piece, component → Component, quantity)

NOT NULL(quantity)

CHECK(quantity > 0)

ComponentType(name)

Order(number, date)

NOT NULL(date)

OrderPiece(order → Order, piece → Piece, quantity)

CHECK(quantity > 0)

Store(id, name, address, fax_no)

NOT NULL(name)

NOT NULL(address)

Factory

Client(id, first name, surname, address, phone, name_contact)

Worker(id, first name, surname, address, birth_date)

Comment: The generalization is disjoint, so we adopt an Object-Oriented strategy for mapping. The Person relation does not exist, because the generalization is complete, so there is no need for the relation usuallu derived from the superclass. If the generalization was partial instead of complete, we would have to retain the Person relation.

Skill(name)

WorkerSkill(worker → Worker, skill → Skill)

Machine(serial_no, make, model, purchase_date)

NOT NULL(purchase_date)

MachineWorker(worker → Worker, machine → Machine)

Product(identifier, name, product_type → ProductTypes)

NOT NULL(name)

ProductType(id, material, weight, produced_by → Machine)

NOT NULL(material)

CHECK(weight > 0)

PurchaseOrder(number, date_made, expected_delivery, actual_delivery, issuer → Client)

NOT NULL(date_made)

NOT NULL(expected_delivery)

NOT NULL(actual_delivery)

NOT NULL(issuer)

CHECK(date_made <= expected_delivery AND date_made <= actual_delivery)

OrdersProduct(product → Products, purchase_order → PurchaseOrders, quantity)

CHECK(quantity > 0)

Bicycles

Client(id, NIN, TIN, name, address)

UNIQUE(NIN)

UNIQUE(TIN)

NOT NULL(name)

Reservation(id, pickup_day, return_day, actual_rented_bike → Bicycle, desired_model → BicycleModel, pickup_place → Store, client → Client)

NOT NULL(pickup_day)

NOT NULL(requested_model)

NOT NULL(pickup_place)

NOT NULL(model)

CHECK(return_day >= pickup_day)

Store(name, address)

NOT NULL(address)

Bicycle(number, model → BicycleModels)

NOT NULL(model)

BicycleModel(name, type, no_gears)

NOT NULL(type)

CHECK(no_gears >= 1)