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

);

CREATE TABLE production

(

     film_id INT NOT NULL REFERENCES film(f_id),

     com_id INT NOT NULL REFERENCES company(com_id),

     CONSTRAINT film_company PRIMARY KEY (film_id,com_id)

);

CREATE TABLE style

(

     film_id INT NOT NULL REFERENCES film(f_id),

     genre_id INT NOT NULL REFERENCES genre(id),

     CONSTRAINT film_genre PRIMARY KEY (film_id,genre_id)

);

commit;

Скрипт заполнения БД приведен в листинге 2.

Листинг 2:

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

INSERT INTO country VALUES (1,'Russia');

INSERT INTO country VALUES (2,'USA');

INSERT INTO country VALUES (3,'Germany');

INSERT INTO country VALUES (4,'France');

INSERT INTO country VALUES (5,'United Kingdom');

INSERT INTO country VALUES (6,'Canada');

INSERT INTO country VALUES (7,'New Zealand');

INSERT INTO country VALUES (8,'Austria');

INSERT INTO website VALUES(1,'kinopoisk','http://www.kinopoisk.ru');

INSERT INTO website (s_id,url) VALUES(2,'http://www.spielbergfilms.com');

INSERT INTO website (s_id,url) VALUES(3,'http://www.lordoftherings.net');

INSERT INTO website (s_id,url) VALUES(4,'http://www.titanicmovie.com');

INSERT INTO website VALUES(5,'movie database','http://www.imdb.com');

commit;

INSERT INTO man (man_id,name,surname,birthday,sex,city,country,site)

VALUES(1,'Steven','Spielberg','1946-12-18','M','Cincinnati',2,2);

INSERT INTO man (man_id,name,surname,second_name,birthday,sex,city,country,site)

VALUES(2,'James','Cameron','Francis','1954-08-16','M','Kapuskasing',6,5);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(3,'Woody','Allen','1935-12-01','M','New York',2);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(4,'Peter','Jackson','1961-10-31','M','Pukerua Bay',7);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(5,'Lyv','Tyler','1977-07-01','F','New York',2);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(6,'Kate','Winslet','1975-10-05','F','Reading',5);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(7,'Leonardo','DiCaprio','1974-11-11','M','Hollywood',2);

INSERT INTO man (man_id,name,surname,second_name,birthday,sex,city,country)

VALUES(8,'Arnold','Schwarzenegger','Alois','1947-07-30','M','Thal',8);

INSERT INTO man (man_id,name,surname,birthday,sex,city,country)

VALUES(9,'James','Horner','1953-08-14','M','Los Angeles',2);

commit;

INSERT INTO career VALUES (1,'director');

INSERT INTO career VALUES (2,'writer');

INSERT INTO career VALUES (3,'actor');

INSERT INTO career VALUES (4,'producer');

INSERT INTO career VALUES (5,'composer');

INSERT INTO career VALUES (6,'operator');

INSERT INTO career VALUES (7,'designer');

INSERT INTO genre VALUES (1,'action');

INSERT INTO genre VALUES (2,'biography');

INSERT INTO genre VALUES (3,'western');

INSERT INTO genre VALUES (4,'crime');

INSERT INTO genre VALUES (5,'documentary');

INSERT INTO genre VALUES (6,'drama');

INSERT INTO genre VALUES (7,'comedy');

INSERT INTO genre VALUES (8,'melodrama');

INSERT INTO genre VALUES (9,'animation');

INSERT INTO genre VALUES (10,'musical');

INSERT INTO genre VALUES (11,'thriller');

INSERT INTO genre VALUES (12,'horror');

INSERT INTO genre VALUES (13,'fantasy');

INSERT INTO genre VALUES (14,'adventure');

INSERT INTO genre VALUES (15,'mystery');

INSERT INTO genre VALUES (16,'romance');

commit;

INSERT INTO film (f_id,name,year_of_creation,duration,budget,site)