e4D2vS3u

1PRAGMA foreign_keys = ON;
2
3DROP TABLE IF EXISTS Actor;
4DROP TABLE IF EXISTS ActorEpisode;
5DROP TABLE IF EXISTS Episode;
6DROP TABLE IF EXISTS Producer;
7DROP TABLE IF EXISTS Scenarist;
8DROP TABLE IF EXISTS ScenaristEpisode;
9DROP TABLE IF EXISTS ScoreE;
10DROP TABLE IF EXISTS ScoreS;
11DROP TABLE IF EXISTS Season;
12DROP TABLE IF EXISTS Title;
13DROP TABLE IF EXISTS User;
14
15
16
17CREATE TABLE Scenarist(
18      Id INTEGER PRIMARY KEY AUTOINCREMENT,
19      Fname  TEXT NOT NULL,
20      Surname TEXT NOT NULL
21);
22
23CREATE TABLE User(
24      Id INTEGER PRIMARY KEY AUTOINCREMENT,
25      Mail TEXT NOT NULL,
26      Nickname TEXT NOT NULL,
27      Password TEXT NOT NULL
28);
29
30CREATE TABLE Producer(
31      Id INTEGER PRIMARY KEY AUTOINCREMENT,
32      Fname  TEXT NOT NULL,
33      Surname TEXT NOT NULL
34);
35
36CREATE TABLE Actor(
37      IdActor INTEGER PRIMARY KEY AUTOINCREMENT,
38      Fname  TEXT NOT NULL,
39      Surname TEXT NOT NULL,
40      date_of_birth DATE,
41      date_of_death DATE NULL
42);
43
44CREATE TABLE Title(
45      Idtitle INTEGER PRIMARY KEY AUTOINCREMENT,
46      Startd DATE NOT NULL,
47      Endd DATE,
48      Title TEXT,
49      Genre TEXT
50);
51
52CREATE TABLE Season(
53      Idtitle INTEGER REFERENCES Title(Idtitle),
54      seasonnumber INTEGER,
55      NameSeason TEXT,
56      PRIMARY KEY(Idtitle,seasonnumber)
57);
58
59CREATE TABLE Episode(
60      Idtitle INTEGER,
61      seasonnumber INTEGER,
62      series INTEGER,
63      Producerid INTEGER REFERENCES Producer(Id),
64      Description TEXT,
65      NameEpisode TEXT,
66      PRIMARY KEY(Idtitle, seasonnumber, series),
67      FOREIGN KEY(Idtitle, seasonnumber) references Season(Idtitle, seasonnumber)
68);
69
70CREATE TABLE ActorEpisode(
71      IdActor INTEGER REFERENCES Actor(IdActor),
72      Idtitle INTEGER,
73      seasonnumber INTEGER,
74      series INTEGER,
75      PRIMARY KEY(Idtitle, seasonnumber, series, IdActor),
76      FOREIGN KEY(Idtitle, seasonnumber, series) references Episode(Idtitle, seasonnumber, series)
77);
78
79CREATE TABLE ScoreE(
80      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
81      Idtitle INTEGER,
82      seasonnumber INTEGER,
83      series INTEGER,
84      user INTEGER REFERENCES User(Id),
85      PRIMARY KEY(Idtitle, seasonnumber, series, user),
86      FOREIGN KEY(Idtitle, seasonnumber, series) REFERENCES Episode(Idtitle, seasonnumber, series)
87);
88
89CREATE TABLE ScoreS(
90      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
91      Idtitle INTEGER REFERENCES Title(Idtitle),
92      user INTEGER REFERENCES User(Id),
93      PRIMARY KEY(Idtitle, user)
94);
95
96CREATE TABLE ScenaristEpisode(
97      id INTEGER REFERENCES Scenarist(id),
98      Idtitle INTEGER,
99      seasonnumber INTEGER,
100      series INTEGER,
101      PRIMARY KEY(Idtitle, seasonnumber, series, id),
102      FOREIGN KEY(Idtitle, seasonnumber, series) REFERENCES Episode(Idtitle, seasonnumber, series)
103);
104
105
106
107INSERT INTO Scenarist(Fname,Surname) VALUES('Максим','Демчук');
108INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Тищенко');
109INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Ёлкин');
110INSERT INTO Scenarist(Fname,Surname) VALUES('Иван','Житников');
111INSERT INTO Scenarist(Fname,Surname) VALUES('Алексей','Люблинский');
112
113INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Демчук','Максим',1997);
114INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уильям','Пит',1963);
115INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уилл','Смит',1968);
116INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Бенджамин','Мффлек',1972);
117INSERT INTO Actor(Fname,Surname,date_of_birth,date_of_death) VALUES('Станислав','Говорухин',1936,2018);
118
119INSERT INTO Title(Startd, Endd, Title, Genre)
120       VALUES('2010-02-12','2011-01-9','Название','Комедия');
121
122INSERT INTO Season(Idtitle, seasonnumber, NameSeason) VALUES(1, 1, 'Проверка1');
123INSERT INTO Season(Idtitle, seasonnumber, NameSeason) VALUES(1, 2, 'Проверка2');
124INSERT INTO Season(Idtitle, seasonnumber, NameSeason) VALUES(1, 3, 'Проверка3');
125INSERT INTO Season(Idtitle, seasonnumber, NameSeason) VALUES(1, 4, 'Проверка4');
126INSERT INTO Season(Idtitle, seasonnumber, NameSeason) VALUES(1, 5, 'Проверка5');
127
128INSERT INTO Producer(Fname, Surname) VALUES('Максим','Демчук');
129INSERT INTO Producer(Fname, Surname) VALUES('Александр','Тищенко');
130INSERT INTO Producer(Fname, Surname) VALUES('Александр','Ёлкин');
131INSERT INTO Producer(Fname, Surname) VALUES('Иван','Житников');
132INSERT INTO Producer(Fname, Surname) VALUES('Алексей','Люблинский');
133
134INSERT INTO User(mail,Nickname,Password) VALUES('maxdem4uk@yandex.ru','Zeroes','qwerty123');
135INSERT INTO User(mail,Nickname,Password) VALUES('mail@mail.com','aerok','qwerty456');
136INSERT INTO User(mail,Nickname,Password) VALUES('raf@rambler.ru','rafferty_al','qwerty789');
137INSERT INTO User(mail,Nickname,Password) VALUES('hassi@gmail.com','hasenblake','qwerty1546');
138INSERT INTO User(mail,Nickname,Password) VALUES('turk@mail.com','stakera','qwerty123');
139
140INSERT INTO ScoreS(Score,Idtitle,user) VALUES(3,1,1);
141INSERT INTO ScoreS(Score,Idtitle,user) VALUES(5,1,2);
142INSERT INTO ScoreS(Score,Idtitle,user) VALUES(2,1,3);
143INSERT INTO ScoreS(Score,Idtitle,user) VALUES(1,1,4);
144INSERT INTO ScoreS(Score,Idtitle,user) VALUES(4,1,5);
145
146
147INSERT INTO Episode(Idtitle, seasonnumber, series, Producerid, Description, NameEpisode) VALUES(1, 4, 1, 1, 'Проверка проверки 1', 'Хоть бы заработло');
148
149select * from Episode;