1. Veterinary Clinic
  2. Auto Repair Shop
  3. Restaurant
  4. Deliveries
  5. Furniture Factory
  6. Factory
  7. Bicycles

Veterinary Clinic

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

NOT NULL(breed)

Breeds(name)

Conditions(name)

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

NOT NULL(animal)

NOT NULL(physician)

NOT NULL(date)

NOT NULL(time)

ConditionsIdentified(condition → Condition, appointment → Appointment)

FrequentConditions(breed→Breed, condition → Condition)

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

NOT NULL(name)

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

NOT NULL(name)

Auto Repair Shop

Persons(id, name, address)

NOT NULL(name)

Employees(id→Person)

Owners(id→Person)

Services(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)

Cars(plate, color)

NOT NULL(car)

Models(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.

Makes(name)

Parts(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

PartTypes(id, current_price)

NOT NULL(current_price)

CHECK(current_price > 0)

Compatibilities(car_model→Model, part_type→PartType)

Restaurant

Persons(id, name, address)

NOT NULL(name)

Waiters(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)

Clients(id→Person, tax_id)

Meals(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)

Dishes(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.

Ingredients(name, meas_unit, qtt_stock)

CHECK(meas_unit IS NOT NULL)

CHECK(qtt_stock >= 0)

QuantitiesNeededForDishes(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

Couriers(id, name, vat_no, phone_num)

Customers(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.

Packages(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

Pieces(reference, price, line → Line)

NOT NULL(line)

NOT NULL(price)

CHECK(price > 0)

Lines(id, type)

NOT NULL(type)

UNIQUE(type)

Components(code, price, component_type → ComponentTypes)

NOT NULL(component_type)

NOT NULL(price)

CHECK(price > 0)

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

NOT NULL(quantity)

CHECK(quantity > 0)

ComponentTypes(name)

Orders(number, date)

NOT NULL(date)

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

CHECK(quantity > 0)

Stores(id, name, address, fax_no)

NOT NULL(name)

NOT NULL(address)

Factory

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

Workers(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.

Skills(name)

WorkerSkills(worker → Worker, skill → Skill)

Machine(serial_no, make, model, purchase_date)

NOT NULL(purchase_date)

MachineWorkers(worker → Worker, machine → Machine)

Products(identifier, name, product_type → ProductTypes)

NOT NULL(name)

ProductTypes(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)

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

CHECK(quantity > 0)

Bicycles

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

UNIQUE(NIN)

UNIQUE(TIN)

NOT NULL(name)

Reservations(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)

Stores(name, address)

NOT NULL(address)

Bicycles(number, model → BicycleModels)

NOT NULL(model)

BicycleModels(name, type, no_gears)

NOT NULL(type)

CHECK(no_gears >= 1)