Базы данных SQL-DDL и SQL-DML. Изучение транзакций. Программирование на языке SQL. Консольное приложение (Цикл лабораторных работ). Вариант № 2, страница 2

drop database;

commit;

create database 'tiger.ftk.spbstu.ru:/var/lib/firebird/4081_1/filmbase.fdb' user 'SYSDBA' password 'masterkey';

commit;

CREATE TABLE country

(

     c_id INT PRIMARY KEY,

     name VARCHAR(80) NOT NULL UNIQUE

);

CREATE INDEX c_index ON country (name);

CREATE TABLE website

(

     s_id INT PRIMARY KEY,

     name VARCHAR(255),

     url VARCHAR(255) NOT NULL

);

CREATE TABLE man

(

     man_id INT PRIMARY KEY,

     name VARCHAR(80) NOT NULL,

     surname VARCHAR(80) NOT NULL,

     second_name VARCHAR(80),

     birthday DATE NOT NULL,

     sex CHAR CHECK (sex IN ('M','F')),

     city VARCHAR(80) NOT NULL,

     country INT NOT NULL REFERENCES country(c_id),

     site INT REFERENCES website(s_id)

);

CREATE UNIQUE INDEX person ON man (name,surname,birthday);

CREATE TABLE career(

     id INT PRIMARY KEY,

     name VARCHAR(50) NOT NULL UNIQUE

);

CREATE TABLE genre(

     id INT PRIMARY KEY,

     name VARCHAR(50) NOT NULL UNIQUE

);

CREATE TABLE film

(

     f_id INT PRIMARY KEY,

     name VARCHAR(100) NOT NULL,

     year_of_creation INT NOT NULL CHECK (year_of_creation >= 0),

     duration INT NOT NULL CHECK (duration >= 0),

     budget INT NOT NULL CHECK (budget >= 0),

     site INT REFERENCES website(s_id)

);

CREATE UNIQUE INDEX movie ON film (name,year_of_creation);

CREATE TABLE soundtrack

(

     sound_id INT PRIMARY KEY,

     name VARCHAR(255),

     author INT NOT NULL REFERENCES man(man_id),

     film INT NOT NULL REFERENCES film(f_id),

     duration TIME  

);

CREATE INDEX music ON soundtrack(name,author);

CREATE TABLE award

(

     award_id INT PRIMARY KEY,

     year_aw INT NOT NULL CHECK (year_aw >= 0),

     award VARCHAR(100) NOT NULL,

     result VARCHAR(50) NOT NULL CHECK (result IN ('won','nominated')),

     category VARCHAR(255) NOT NULL

);

CREATE TABLE company

(

     com_id INT PRIMARY KEY,

     name VARCHAR(255) NOT NULL,

     year_of_foundation INT NOT NULL CHECK (year_of_foundation >= 0)

);

CREATE INDEX com_index ON company(name);

CREATE TABLE rating

(

     r_id INT PRIMARY KEY,

     rating INT NOT NULL CHECK (rating >= 0 AND rating <= 10),

     e_mail VARCHAR(80) NOT NULL ,

     film_id INT NOT NULL REFERENCES film(f_id), CONSTRAINT rat_man UNIQUE (e_mail,film_id)

);

CREATE TABLE part

(

     film_id INT NOT NULL REFERENCES film(f_id),

     man_id INT NOT NULL REFERENCES man(man_id),

     role_id INT NOT NULL REFERENCES career(id),

     CONSTRAINT role PRIMARY KEY (film_id,man_id,role_id) 

);

CREATE INDEX part_index ON part(film_id,man_id);

CREATE TABLE premium

(

     film_id INT NOT NULL REFERENCES film(f_id),

     man_id INT REFERENCES man(man_id),

     award_id INT NOT NULL REFERENCES award(award_id),

     CONSTRAINT film_award PRIMARY KEY (film_id,man_id,award_id) 

);

CREATE INDEX premium_index ON premium(film_id,award_id);

CREATE TABLE made_in

(

     film_id INT NOT NULL REFERENCES film(f_id),

     country_id INT NOT NULL REFERENCES country(c_id),

     CONSTRAINT film_country PRIMARY KEY (film_id,country_id)

);

CREATE TABLE owner

(

     man_id INT NOT NULL REFERENCES man(man_id),

     com_id INT NOT NULL REFERENCES company(com_id),

     CONSTRAINT owner_company PRIMARY KEY (man_id,com_id)