Create_t.sql - master - BARS-uk/bd_homework_3

Create_t.sql
84 строки3 KB
BARS-uk
update Create_t.sql
08 окт 2025, 16:56
08 окт 2025, 16:56d4fbe39
-- Жанр
CREATE TABLE IF NOT EXISTS Genre (
	id SERIAL PRIMARY KEY,
	name VARCHAR(30) NOT NULL UNIQUE
);

-- Исполнитель
CREATE TABLE IF NOT EXISTS Singer (
	id SERIAL PRIMARY KEY,
	nick_name VARCHAR(40) NOT NULL
);

-- Связь исполнителей и жанров - многие ко многим
-- у одного жанра несколько исполнителей
-- у одного исполнителя может быть несколько жанров
CREATE TABLE IF NOT EXISTS SingerGenre (
	singer_id INTEGER REFERENCES Singer(id),
	genre_id INTEGER REFERENCES Genre(id),
	CONSTRAINT pk_singer_genre PRIMARY KEY (singer_id, genre_id)
);

-- Альбом
CREATE TABLE IF NOT EXISTS Album (
	id SERIAL PRIMARY KEY,
	name VARCHAR(60) NOT NULL,
	year_of_release INTEGER NOT NULL
);

-- Связь исполнителей и альбомов - многие ко многим
-- у одного альбома несколько исполнителей
-- у одного исполнителя может быть несколько альбомов
CREATE TABLE IF NOT EXISTS SingerAlbum (
	singer_id INTEGER REFERENCES Singer(id),
	album_id INTEGER REFERENCES Album(id),
	CONSTRAINT pk_singer_album PRIMARY KEY (singer_id, album_id)
);

-- Трек
-- связь один к одному - один трек принадлежит одному альбому
CREATE TABLE IF NOT EXISTS Track (
	id SERIAL PRIMARY KEY,
	album_id INTEGER NOT NULL REFERENCES Album(id),
	name VARCHAR(40) NOT NULL,
	duration INTERVAL NOT NULL     -- продолжительность минуты и секунды
);

-- Сборник
CREATE TABLE IF NOT EXISTS Collection (
	id SERIAL PRIMARY KEY,
	name VARCHAR(60) NOT NULL,
	year_of_release INTEGER NOT NULL
);

-- Связь сборников и треков
-- В сборник входят разные треки из разных альбомов
CREATE TABLE IF NOT EXISTS TrackCollection (
	track_id INTEGER REFERENCES Track(id),
	collection_id INTEGER REFERENCES Collection(id),
	CONSTRAINT pk_track_collection PRIMARY KEY (track_id, collection_id)
);

-- связь треков и альбомов есть, один трек принадлежит одному альбому
-- чтобы определить исполнителя трека, входящего в сборник добавим связь
-- у трека может быть один или несколько исполнителей
	
CREATE TABLE IF NOT EXISTS TrackSinger (
	track_id INTEGER REFERENCES Track(id),
	singer_id INTEGER REFERENCES Singer(id),
	CONSTRAINT pk_track_singer PRIMARY KEY (track_id, singer_id)
);

ALTER TABLE Track
ALTER COLUMN duration TYPE INTEGER USING EXTRACT(EPOCH FROM duration),
ADD CONSTRAINT track_duration_positive CHECK (duration > 0);

ALTER TABLE Album
	  ADD CONSTRAINT chk_year_of_release
	  CHECK (year_of_release >= 1900 AND year_of_release <= EXTRACT(YEAR FROM CURRENT_DATE) + 1);
	  
ALTER TABLE Collection
	  ADD CONSTRAINT chk_year_of_release
	  CHECK (year_of_release >= 1900 AND year_of_release <= EXTRACT(YEAR FROM CURRENT_DATE) + 1);
	  
drop table TrackSinger;