Реляционная модель БД "Живопись на марках"
На основании описания предметной области может быть предложена следующая структура реляционной базы данных.
Диаграмма БД
Скрипт схемы БД (SQL Server)
/* Художники */
CREATE TABLE Artists(
id int NOT NULL, /* уникальный идентификатор художника */
name varchar(30) NULL, /* имя */
birth int NULL, /* год рождения */
death int NULL, /* год смерти */
descript varchar(max) NULL, /* биография */
period_id int NULL, /* период творчества - внешний ключ к таблице Periods */
country_id int NULL, /* страна - внешний ключ к таблице Countries */
stamp varchar(250) NULL, /* путь к файлу изображения марки */
pic varchar(250) NULL, /* путь к файлу иллюстрации картины, по которой выполнена марка */
CONSTRAINT PK_Artists PRIMARY KEY(id)
);
/* Страны */
CREATE TABLE Countries(
id int NOT NULL, /* Уникальный идентификатор страны */
name varchar(255) NOT NULL, /* название страны */
CONSTRAINT PK_Countries PRIMARY KEY(id)
);
/* Культурные периоды */
CREATE TABLE Periods(
id int NOT NULL, /* уникальный идентификатор периода */
name varchar(255) NOT NULL, /* название периода */
CONSTRAINT PK_Periods PRIMARY KEY(id)
);
/* Ограничения внешних ключей таблицы Artists */
ALTER TABLE Artists ADD CONSTRAINT FK_Artists_Countries FOREIGN KEY(country_id)
REFERENCES Countries (id)
ON UPDATE CASCADE;
ALTER TABLE Artists ADD CONSTRAINT FK_Artists_Periods FOREIGN KEY(period_id)
REFERENCES Periods (id)
ON UPDATE CASCADE;
/* Картины */
CREATE TABLE Paints(
id int NOT NULL, /* уникальный идентификатор картины */
name varchar(255) NOT NULL, /* название */
creation varchar(255) NULL, /* время создания */
descript varchar(max) NULL, /* описание картины */
artist_id int NOT NULL, /* художник - внешний ключ к таблице Artists */
genre_id int NOT NULL, /* жанр - внешний ключ к таблице Genres */
museum_id int NOT NULL, /* музей - внешний ключ к таблице Museums */
stamp varchar(250) NOT NULL, /* путь к файлу изображения марки */
pic varchar(250) NULL, /* путь к файлу иллюстрации картины, по которой выполнена марка */
CONSTRAINT PK_Paints PRIMARY KEY(id)
);
/* Жанры */
CREATE TABLE Genres(
id int NOT NULL, /* Уникальный идентификатор жанра */
name varchar(255) NOT NULL, /* название жанра */
CONSTRAINT PK_Genres PRIMARY KEY(id)
);
/* Музеи */
CREATE TABLE Museums(
id int NOT NULL, /* Уникальный идентификатор музея */
name varchar(255) NOT NULL, /* название */
town varchar(255) NULL, /* город, в котором находится музей */
architect varchar(255) NULL, /* архитектор/архитекторы */
open_year int NULL, /* год открытия */
orientation varchar(255) NULL, /* художественная направленность */
comment varchar(max) NULL, /* информация о музее */
CONSTRAINT PK_Museums PRIMARY KEY(id)
);
/* Ограничения внешних ключей таблицы Paints */
ALTER TABLE Paints ADD CONSTRAINT FK_Paints_Genres FOREIGN KEY(genre_id)
REFERENCES Genres (id)
ON UPDATE CASCADE;
ALTER TABLE Paints ADD CONSTRAINT FK_Paints_Museums FOREIGN KEY(museum_id)
REFERENCES Museums (id)
ON UPDATE CASCADE;
/* Изображения музеев */
CREATE TABLE MuseumPics(
museum_id int NOT NULL, /* музей - внешний ключ к таблице Museums */
id int NOT NULL, /* уникальный идентификатор изображения музея */
pic varchar(250) NOT NULL, /* путь к файлу изображения музея на марке */
CONSTRAINT PK_MuseumPics PRIMARY KEY(id)
);
/* Ограничения внешнего ключа таблицы MuseumPics */
ALTER TABLE MuseumPics ADD CONSTRAINT FK_MuseumPics_Museums FOREIGN KEY(museum_id)
REFERENCES Museums (id)
ON UPDATE CASCADE;
Пояснения к схеме
В таблице Artists столбец name допускает NULL-значения. Казалось бы, если художник неизвестен, то зачем иметь такую информацию в таблице,
когда все значения за исключением id неизвестны. Однако согласно модели предметной области, у подобного художника может быть несколько картин.
Соответственно было бы неправильно все картины неизвестных художников объединять в одну группу с artist_id = NULL, т.е. мы можем знать,
что картины нарисовали разные художники, хотя оба они неизвестны. Или что несколько картин нарисовал один и тот же неизвестный художник. Именно поэтому в таблице Paints столбец artist_id имеет ограничение
NOT NULL. Кроме того, нам может быть известна другая информация, например, страна и период написания картины.
Точный год написания картины может быть неизвестнен, поэтому столбец creation имеет тип данных varchar,
в котором может быть указан диапазон или другая информация, определяющая приблизительное время написания картины. Что касается
художников, то если годы жизни неизвестны, то соответствующие стоблцы (типа int) birth/death будут иметь значения NULL.
Для ныне здравствующих художников будет указан только год рождения.
Если марки не существует, то такой картины не будет в базе данных, поэтому столбец stamp в таблице Paints не может быть NULL.
Иллюстрация же оригинала (pic) может отсутствовать.
То, что город нахождения музея допускает NULL-значения, не означает, что информация о местонаходении музея отсутствует. Это обусловлено тем,
что в базе данных музеи, преставленные одной-двумя марками, объединены в группы по более крупным территориальным образованиям, например,
"Другие картинные галереи России" (id=8).
То же самое можно сказать о направленности музея (orientation) или об архитекторе (architect).
Мы не можем указать эту информацию, когда речь идет о группе музеев.
Поскольку отдельный музей или группа музеев могут быть представлены несколькими марками, то изображения музеев выделены в отдельную
таблицу (MuseumPics), связанную с таблицей Museums связью "многие к одному".
Примеры запросов к БД
Найти художника по его ИД
SELECT name, birth, death, descript FROM Artists WHERE id=44;
Результат: Врубель
Определить имя художника по названию картины ("Княжна Тараканова")
SELECT Artists.name FROM Artists JOIN Paints on Artists.id=Paints.artist_id
WHERE Paints.name='Княжна Тараканова';
Результат: Флавицкий К.Д.
Определить музей, в котором хранится картина "Явление Христа народу"
SELECT Museums.name
FROM Museums JOIN Paints on Museums.id=Paints.museum_id
WHERE Paints.name='Явление Христа народу';
Результат: Третьяковская галерея
Ограничения реляционной БД "Живопись на марках"