Auto Repair Shop

Person (id, name, address)
NOT NULL(name)

Employee (id→Person)

Owner (id→Person, phone_number)

Service (id, car_kms, hours_spent, date_adm, date_finish, employee → Employee, car →Car)
CHECK(car_kms > 0)
CHECK(hours_spent IS NULL OR hours_spent > 0)
CHECK(date_finish IS NULL OR date_finish > date_adm)
NOT NULL(car_kms)
NOT NULL(date_adm)
NOT NULL(employee)
NOT NULL(car)

Car (plate, color, owner → Owner, model → Model)
NOT NULL(owner)
NOT NULL(model)

Model (id, name, 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 name could be the primary key.

Make (name)

Part (serial_number, price, service → Service, type → PartType)
CHECK(price > 0)
NOT NULL(price)
NOT NULL(type)

PartType (id, name)
NOT NULL(name)

Compatibility (model → Model, part → Part)

Restaurant

Person (id, name, address)

Waiter (id → Person, identifier, phone_number)
UNIQUE(identifier)
NOT NULL(identifier)

Comment: Why 2 Ids? ‘identifier’ is the number that the restaurant attributes to the waiter (it has a meaning), while ‘id’ is the database identifier (it will be auto generated and has no meaning).

Client (id → Person, tax_id)
UNIQUE(tax_id)
NOT NULL(tax_id)

Meal (id, date, start_time, end_time, client → Client, waiter → Waiter, table → Table)
CHECK(end_time IS NULL OR end_time > start_time)
NOT NULL(date)
NOT NULL(start_time)
NOT NULL(waiter)
NOT NULL(table)

Table (identifier, capacity)
CHECK(capacity > 0)
NOT NULL(capacity)

Dish (identifier, name)
UNIQUE(name)
NOT NULL(name)

Ingredient (name, unit, stock)
CHECK(stock >= 0)
NOT NULL(stock)

IngredientDish (dish → Dish, ingredient → Ingredient, quantity)
CHECK(quantity > 0)
NOT NULL(quantity)

DishMeal (dish → Dish, meal → Meal, quantity)
CHECK(quantity > 0)
NOT NULL(quantity)

Deliveries

Person (vat, name, phone_number)
NOT NULL(name)

Customer (vat → Person, address, closest_delivery_center → DeliveryCenter)
NOT NULL(address)
NOT NULL(closest_delivery_center)

Courier (vat → Person, delivery_center → DeliveryCenter)
NOT NULL(delivery_center)

Package (identifier, weight, priority, sender → Customer, recipient → Customer, drop_off → DeliveryCenter, courier →Courier)
CHECK(weight > 0)
CHECK(priority IN (‘normal’, ‘urgent’))
NOT NULL(weight)
NOT NULL(priority)
NOT NULL(sender)
NOT NULL(recipient)
NOT NULL(drop_off)
NOT NULL(courier)

DeliveryCenter (id, name, address)
UNIQUE(name)
NOT NULL(name)

Tracking (package→Package, delivery_center→DeliveryCenter, date_arrival, date_departure)
CHECK(date_departure IS NULL OR date_departure > date_arrival)

Furniture Factory

Line (id, name)
UNIQUE(name)
NOT NULL(name)

FurniturePiece (reference, name, price, line → Line, type → FurnitureType)
CHECK(price > 0)
UNIQUE(name)
NOT NULL(name)
NOT NULL(price)
NOT NULL(line)
NOT NULL(type)

FurnitureType (name)

Component (ref, price, type → ComponentType)
CHECK(price > 0)
NOT NULL(price)
NOT NULL(type)

ComponentType (name)

PieceComponent (furniture_piece → FurniturePiece, component → Component, quantity)
CHECK(quantity > 0)
NOT NULL(quantity)

Order (number, date, store → Store)
NOT NULL(date)
NOT NULL(store)

PieceOrder (order → Order, furniture_piece → FurniturePiece, quantity)
CHECK(quantity > 0)
NOT NULL(quantity)

Store (id, name, address, phone_number)
UNIQUE(name)
NOT NULL(name)

Veterinary Clinic

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

Breed (name)

Condition (name, s_name)
UNIQUE(s_name)
NOT NULL(s_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, address, phone_no)
NOT NULL(name)

Physician (id, name, address, phone_no)
NOT NULL(name)

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 usually 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)
NOT NULL(product_type)

ProductType (id, material, weight, produced_by → Machine)
NOT NULL(material)
NOT NULL(produced_by)
CHECK(weight > 0)

PurchaseOrder (number, date_made, expected_delivery, actual_delivery, issuer → Client)
NOT NULL(date_made)
NOT NULL(expected_delivery)
NOT NULL(issuer)
CHECK(date_made <= expected_delivery AND date_made <= actual_delivery)

OrdersProduct (product → Products, purchase_order → PurchaseOrders, quantity)
CHECK(quantity > 0)
NOT NULL(quantity)

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(actual_rented_bike)
NOT NULL(desired_model)
NOT NULL(pickup_place)
NOT NULL(client)
CHECK(return_day IS NULL OR 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)