cTT76k59

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 REFERENCES Season(Idtitle),
61      seasonnumber INTEGER REFERENCES Season(seasonnumber),
62      series INTEGER,
63      Producerid INTEGER REFERENCES Producer(Id),
64      Description TEXT,
65      NameEpisode TEXT,
66      PRIMARY KEY(Idtitle, seasonnumber, series)
67);
68
69CREATE TABLE ActorEpisode(
70      IdActor INTEGER REFERENCES Actor(IdActor),
71      Idtitle INTEGER REFERENCES Episode(Idtitle),
72      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
73      series INTEGER REFERENCES Episode(series),
74      PRIMARY KEY(Idtitle, seasonnumber, series, IdActor)
75);
76
77CREATE TABLE ScoreE(
78      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
79      Idtitle INTEGER REFERENCES Episode(Idtitle),
80      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
81      series INTEGER REFERENCES Episode(series),
82      user INTEGER REFERENCES User(Id),
83      PRIMARY KEY(Idtitle, seasonnumber, series, user)
84);
85
86CREATE TABLE ScoreS(
87      Score INTEGER CHECK(Score BETWEEN 1 AND 5),
88      Idtitle INTEGER REFERENCES Title(Idtitle),
89      user INTEGER REFERENCES User(Id),
90      PRIMARY KEY(Idtitle, user)
91);
92
93CREATE TABLE ScenaristEpisode(
94      id INTEGER REFERENCES Scenarist(id),
95      Idtitle INTEGER REFERENCES Episode(Idtitle),
96      seasonnumber INTEGER REFERENCES Episode(seasonnumber),
97      series INTEGER REFERENCES Episode(series),
98      PRIMARY KEY(Idtitle, seasonnumber, series, id)
99);
100
101
102
103INSERT INTO Scenarist(Fname,Surname) VALUES('Максим','Демчук');
104INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Тищенко');
105INSERT INTO Scenarist(Fname,Surname) VALUES('Александр','Ёлкин');
106INSERT INTO Scenarist(Fname,Surname) VALUES('Иван','Житников');
107INSERT INTO Scenarist(Fname,Surname) VALUES('Алексей','Люблинский');
108
109INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Демчук','Максим',1997);
110INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уильям','Пит',1963);
111INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Уилл','Смит',1968);
112INSERT INTO Actor(Fname,Surname,date_of_birth) VALUES('Бенджамин','Мффлек',1972);
113INSERT INTO Actor(Fname,Surname,date_of_birth,date_of_death) VALUES('Станислав','Говорухин',1936,2018);
114
115INSERT INTO Title(Startd,Endd,Title,Genre)
116       VALUES('2010-02-12','2011-01-9','Название','Комедия');
117
118INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 1,'Проверка1');
119INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 2,'Проверка2');
120INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 3,'Проверка3');
121INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 4,'Проверка4');
122INSERT INTO Season(Idtitle,seasonnumber,NameSeason) VALUES(1, 5,'Проверка5');
123
124INSERT INTO Producer(Fname,Surname) VALUES('Максим','Демчук');
125INSERT INTO Producer(Fname,Surname) VALUES('Александр','Тищенко');
126INSERT INTO Producer(Fname,Surname) VALUES('Александр','Ёлкин');
127INSERT INTO Producer(Fname,Surname) VALUES('Иван','Житников');
128INSERT INTO Producer(Fname,Surname) VALUES('Алексей','Люблинский');
129
130INSERT INTO User(mail,Nickname,Password) VALUES('maxdem4uk@yandex.ru','Zeroes','qwerty123');
131INSERT INTO User(mail,Nickname,Password) VALUES('mail@mail.com','aerok','qwerty456');
132INSERT INTO User(mail,Nickname,Password) VALUES('raf@rambler.ru','rafferty_al','qwerty789');
133INSERT INTO User(mail,Nickname,Password) VALUES('hassi@gmail.com','hasenblake','qwerty1546');
134INSERT INTO User(mail,Nickname,Password) VALUES('turk@mail.com','stakera','qwerty123');
135
136INSERT INTO ScoreS(Score,Idtitle,user) VALUES(3,1,1);
137INSERT INTO ScoreS(Score,Idtitle,user) VALUES(5,1,2);
138INSERT INTO ScoreS(Score,Idtitle,user) VALUES(2,1,3);
139INSERT INTO ScoreS(Score,Idtitle,user) VALUES(1,1,4);
140INSERT INTO ScoreS(Score,Idtitle,user) VALUES(4,1,5);
141
142INSERT INTO Episode(Idtitle, seasonnumber, series, Producerid, Description, NameEpisode) VALUES(1, 4, 1, 1, 'Проверка проверки 1', 'Хоть бы заработло');