Relational Model

Relational Model

Employee

Employee(personalNumber, firstName, lastName, dateOfBirth)

Phone

Phone(personalNumber, phoneNumber)

• FK: (personalNumber) ⊆ Employee(personalNumber)

Logistician

Logistician(personalNumber)

• FK: (personalNumber) ⊆ Employee(personalNumber)

Manages(subordinatePersonalNumber, supervisorPersonalNumber)

• FK: (subordinatePersonalNumber) ⊆ Logistician(personalNumber)
• FK: (supervisorPersonalNumber) ⊆ Logistician(personalNumber)

Driver

Driver(personalNumber, licenseNumber)

• FK: (personalNumber) ⊆ Employee(personalNumber)

Trip

Trip(tripId, status, arrivalTime, departureTime, driverPersonalNumber, logisticianPersonalNumber, licensePlate)

• FK: (driverPersonalNumber) ⊆ Driver(personalNumber)
• FK: (logisticianPersonalNumber) ⊆ Logistician(personalNumber)
• FK: (licensePlate) ⊆ Truck(licensePlate)

Truck

Truck(licensePlate, vin, status, maxWeight)

Order

Order(orderId, price, status, orderDate, weight, pickupLocation, deliveryLocation, companyId)

• FK: (companyId) ⊆ ClientCompany(companyId)

Transports

Transports(tripId, orderId)

• FK: (tripId) ⊆ Trip(tripId)
• FK: (orderId) ⊆ Order(orderId)

Trip Expense

TripExpense(expenseNumber, tripId, expenseType, amount, note)

• FK: (tripId) ⊆ Trip(tripId)

Client Company

ClientCompany(name, companyId, phone, email, city, street, houseNumber)