Acces, multiple one to one connections question

user_16010005 0
Viewed 81

I have a problem with acces, im trying to make database of airport and cant realy make one to one - one to one - one to one connection beetwen ticket, passanger and luggage. The idea is that one passanger can have one ticket and one luggage, and when im doing it like it is on picture i cant acces luggage from ticket neither from passanger only from luggage itself.

My diagram

one-to-one ms-access
1 Answers

Mainly the diagram should be something like:

enter image description here

Simple Data and Keys:

enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here enter image description here

Sample query(all tickets with relevant linked data):

SELECT Tickets.Ticket, 
       Tickets.Person, 
       Flights.Flight, 
       Luggages.Luggage
FROM Flights 
INNER JOIN (
   (Persons INNER JOIN Tickets ON Persons.ID = Tickets.Person)
LEFT JOIN Luggages ON Tickets.ID = Luggages.ID)
    ON Flights.ID = Tickets.Flight;

enter image description here

Second view was the purpose, so it up to your side to do the adjustments.

enter image description here

Note: Person 3 do not have luggage. Person 1&2 use same flight.

MS Access was used, but design is not bound to a specific rdbms (only dialects adjustments for a particular implementation)

Also, there is an error on relation Persons-Tickets which should be 1-n.(similar Flight-Tickets).

Still additional constraints could be bound also. Eg: a person can be just on one flight is a specific time.

user_109392040
Related