Как выстроить правильные отношения в БД postgresSQL?

Question

[chvertkos]

Здравствуйте, направьте на путь истинный. Создаю БД тур-агенства для курсача. Никогда не интересовался углубленно Postgress, но сейчас приходится. Создать нужно по такой схеме :
Правильно ли описаны все отношения? В частности вопрос к таблицам City и Country. Вроде как отношения в них должно быть Country (idCountry) -----1 - M -----> City (idCountry).
SQL код следующий:

CREATE TABLE public1.customers (
	IdCustomer serial NOT NULL,
	"Name" varchar(25),
	Surname varchar(25) NOT NULL,
	SecondName varchar(25),
	Adress character(25) NOT NULL,
	PhoneNumber varchar(20) NOT NULL,
	Email varchar(50),
	Citizenship varchar(20),
	CONSTRAINT customers_pk PRIMARY KEY (IdCustomer)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.city (
	IdCity serial NOT NULL,
	IdCountry int4 NOT NULL,
	CityName varchar(25),
	CONSTRAINT city_pk PRIMARY KEY (IdCity)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.country (
	IdCountry serial NOT NULL,
	CountryName varchar(25),
	CONSTRAINT country_pk PRIMARY KEY (IdCountry)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.staff (
	IdStaff serial NOT NULL,
	Name varchar(25),
	Surname varchar(25),
	SecondName varchar(25),
	Post varchar(25),
	PhoneNumber varchar(25),
	CONSTRAINT staff_pk PRIMARY KEY (IdStaff)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.food (
	idFood serial NOT NULL,
	FoodName varchar(25),
	CONSTRAINT food_pk PRIMARY KEY (idFood)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.hotel_room (
	idHotelRoom serial NOT NULL,
	TypeOfRoom varchar(20),
	CONSTRAINT hotel_room_pk PRIMARY KEY (idHotelRoom)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.hotels (
	idHotel serial NOT NULL,
	HotelName int4,
	HotelAdress varchar(25),
	Stars int2,
	CONSTRAINT hotels_pk PRIMARY KEY (idHotel)
) WITH (
  OIDS=FALSE
);



CREATE TABLE public1.tour (
	idTour serial NOT NULL,
	idDoc serial NOT NULL,
	idStaff int4 NOT NULL,
	idCustomer int4 NOT NULL,
	idCountry int4 NOT NULL,
	idCity int4 NOT NULL,
	DataIn DATE,
	DataOff DATE,
	idHotel int4 NOT NULL,
	idFood int4 NOT NULL,
	IdHotelRoom int NOT NULL,
	Price numeric(7) NOT NULL,
	QuantityGuests int2,
	CONSTRAINT tour_pk PRIMARY KEY (idTour)
) WITH (
  OIDS=FALSE
);





ALTER TABLE country ADD CONSTRAINT country_fk0 FOREIGN KEY (IdCountry) REFERENCES city(IdCountry);





ALTER TABLE tour ADD CONSTRAINT tour_fk0 FOREIGN KEY (idStaff) REFERENCES staff(IdStaff);
ALTER TABLE tour ADD CONSTRAINT tour_fk1 FOREIGN KEY (idCustomer) REFERENCES customers(IdCustomer);
ALTER TABLE tour ADD CONSTRAINT tour_fk2 FOREIGN KEY (idCountry) REFERENCES country(IdCountry);
ALTER TABLE tour ADD CONSTRAINT tour_fk3 FOREIGN KEY (idCity) REFERENCES city(IdCity);
ALTER TABLE tour ADD CONSTRAINT tour_fk4 FOREIGN KEY (idHotel) REFERENCES hotels(idHotel);
ALTER TABLE tour ADD CONSTRAINT tour_fk5 FOREIGN KEY (idFood) REFERENCES food(idFood);
ALTER TABLE tour ADD CONSTRAINT tour_fk6 FOREIGN KEY (IdHotelRoom) REFERENCES hotel_room(idHotelRoom);

Выдает следующую ошибку

spoiler

ERROR: ОШИБКА: в целевой внешней таблице "city" нет ограничения уникальности, соответствующего данным ключам
SQL-состояние: 42830

Объясните как правильно все организовать. Не кидайтесь камнями, пожалуйста

Comments

[Melkij]

search_path какой?
Вы показываете create table в схеме public1, а FK навешиваете без указания схемы.

[Akina]

направьте на путь истинный
...
Правильно ли описаны все отношения?

Диаграмма вообще и отношения на ней в частности не высасываются из пальца, они являются следствием информации, накопленной и полученной на этапе анализа предметной области (ПО).

Вы ни описания ПО, ни результаты её анализа не показали в принципе. Ну и как мы можем сказать, соответствуют ли друг другу ПО и диаграмма, или на диаграмме показан голимый бред?

Надеюсь, направил куда надо...

[Akina]

Вроде как отношения в них должно быть Country (idCountry) -----1 - M -----> City (idCountry).

Вообще-то в мире имеются города, расположенные сразу в двух странах...

[chvertkos]

Akina,
Собсна вот, делалось конечно спустя рукава, но как есть

Предметная область
Тур.Фирма являет собой организацию, предоставляющую услуги по организации туристических экскурсий и перелетов, размещением в отелях или иных местах пребывания. Главной задачей является обеспечение клиентов наиболее актуальной информацией, а так же организация мероприятий по подготовке к поездке.
Деятельность тур.фирмы так же представлена в выстраивании отношений с тур. Операторами , общении с клиентами по средствам соц-сетей или иных веб ресурсов, реклама, а так же второстепенные и административные процессы.

b) Перечень функций и процессов, реализуемых задачей:
- Хранение и доступ к данным о стране назначения.
- Хранение и доступ к данным о турах.
- Хранение и доступ к данным об отелях.
- Осуществлять выборку данных в указанном диапазоне дат или цен.
- Фильтровать данные о турах по различным параметрам.
- Осуществлять выборку данных по различным параметрам.
- Формировать отчеты.

Организационная сущность задачи:
В представленной пользователю ( клиенту ) веб-форме, заполняются необходимые для выборки данные. Согласно полученным данным, формулируется SQL запрос и отправляется на обработку сервером. После получения ответа от сервера, формируется визуальный компонент «Карточка», предоставляющий актуальную информацию в диапазоне выбранном клиентом (пользователем) и/ или конкретные данные, такие как:
- Стоимость
- Заголовок
- Дата
- Описание
- Включенные услуги
- Название отеля
- Страна
- Дата вылета
- Дата прилета
- Время нахождения

[Akina]

Это не анализ ПО, а кроки технического задания.

Answer 1

[mayton2019]

Вот здесь что-то не то.

ALTER TABLE country ADD CONSTRAINT country_fk0 FOREIGN KEY (IdCountry) REFERENCES city(IdCountry);

Тут таблицей промахнулся. Или направление констрейнта должно быть наоборот. Города ссылаются на страны. Но не наоборот.

Comments

[chvertkos]

Исправил, работает, но профанский вопрос: Сейчас поле idCountry в таблице City связано с полем id в таблице Country. Добавил в таблицу countryes 3 страны, но при добавлении городов в таблице city, значения id стран не отображаются, должно ли так быть? (Тыкали в шараге что-то в access, помню, что там были выпадающие списки, а в postres видимо ничего подобного нет? Только отельно выписать индексы и заполнять по ним?). Спасибо

[mayton2019]

chvertkos, выпадающие списки не имеют никакого отношения к языку SQL. Или тебе надо задавать вопрос конкретно по MS-Access. Это - другое приложение со своими нюансами.