DatabaseCreation: VidiDB_Creation.sql

File VidiDB_Creation.sql, 7.9 KB (added by 231166, 2 days ago)
Line 
1CREATE TABLE Subscription (
2 SubscriptionID SERIAL PRIMARY KEY,
3 Name varchar(255) NOT NULL,
4 Price numeric(19, 2) NOT NULL CHECK (Price >= 0),
5 MaxDevices int4 NOT NULL CHECK (MaxDevices > 0),
6 VideoQuality varchar(255)
7);
8
9-- ------------------------------------------------
10
11CREATE TABLE "User" (
12 UserID SERIAL PRIMARY KEY,
13 FirstName varchar(255),
14 LastName varchar(255),
15 Username varchar(255) NOT NULL UNIQUE,
16 Email varchar(255) NOT NULL UNIQUE
17 CHECK (Email ~ '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
18 password varchar(255) NOT NULL,
19 Date_registered date DEFAULT CURRENT_DATE
20);
21
22-- ------------------------------------------------
23
24CREATE TABLE User_Subscription (
25 UserSubscriptionID SERIAL PRIMARY KEY,
26 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
27 ON DELETE CASCADE ON UPDATE CASCADE,
28 SubscriptionSubscriptionID int4 NOT NULL REFERENCES Subscription (SubscriptionID)
29 ON DELETE RESTRICT ON UPDATE CASCADE,
30 Start_date date NOT NULL DEFAULT CURRENT_DATE,
31 End_date date CHECK (End_date IS NULL OR End_date >= Start_date),
32 Status varchar(255),
33 Auto_renew int4 DEFAULT 0 CHECK (Auto_renew IN (0, 1))
34);
35
36-- ------------------------------------------------
37
38CREATE TABLE Devices (
39 DeviceID SERIAL PRIMARY KEY,
40 DeviceType varchar(255),
41 LastLogIn date,
42 UserSubscriptionID int4 NOT NULL REFERENCES User_Subscription (UserSubscriptionID)
43 ON DELETE RESTRICT ON UPDATE CASCADE
44);
45
46-- ------------------------------------------------
47
48CREATE TABLE User_Devices (
49 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
50 ON DELETE CASCADE ON UPDATE CASCADE,
51 DevicesDeviceID int4 NOT NULL REFERENCES Devices (DeviceID)
52 ON DELETE CASCADE ON UPDATE CASCADE,
53 PRIMARY KEY (UserUserID, DevicesDeviceID)
54);
55
56-- ------------------------------------------------
57
58CREATE TABLE Media (
59 ContentID SERIAL PRIMARY KEY,
60 title varchar(255) NOT NULL,
61 description varchar(255),
62 releaseDate date,
63 AgeRating numeric(19, 0) CHECK (AgeRating >= 0 AND AgeRating <= 21)
64);
65
66-- ------------------------------------------------
67
68CREATE TABLE Movie (
69 MovieID int4 PRIMARY KEY REFERENCES Media (ContentID)
70 ON DELETE CASCADE ON UPDATE CASCADE,
71 Duration int4 CHECK (Duration > 0)
72);
73
74-- ------------------------------------------------
75
76CREATE TABLE Series (
77 SeriesID int4 PRIMARY KEY REFERENCES Media (ContentID)
78 ON DELETE CASCADE ON UPDATE CASCADE,
79 TotalSeasons int4 CHECK (TotalSeasons > 0)
80);
81
82-- ------------------------------------------------
83
84CREATE TABLE Season (
85 SeasonID SERIAL PRIMARY KEY,
86 SeasonNumber int4 NOT NULL CHECK (SeasonNumber > 0),
87 ReleaseYear int4,
88 SeriesSeriesID int4 NOT NULL REFERENCES Series (SeriesID)
89 ON DELETE CASCADE ON UPDATE CASCADE
90);
91
92-- ------------------------------------------------
93
94CREATE TABLE Episode (
95 EpisodeID SERIAL PRIMARY KEY,
96 Title varchar(255),
97 episodeNumber int4 NOT NULL CHECK (episodeNumber > 0),
98 Duration int4 CHECK (Duration > 0),
99 SeasonSeasonID int4 NOT NULL REFERENCES Season (SeasonID)
100 ON DELETE CASCADE ON UPDATE CASCADE
101);
102
103-- ------------------------------------------------
104
105CREATE TABLE Watchable (
106 WatchableID SERIAL PRIMARY KEY,
107 duration int4 CHECK (duration > 0),
108 MovieID int4 REFERENCES Movie (MovieID)
109 ON DELETE SET NULL ON UPDATE CASCADE,
110 EpisodeID int4 REFERENCES Episode (EpisodeID)
111 ON DELETE SET NULL ON UPDATE CASCADE
112);
113
114-- ------------------------------------------------
115
116CREATE TABLE Artist (
117 ArtistID SERIAL PRIMARY KEY,
118 FirstName varchar(255),
119 LastName varchar(255),
120 BirthDate date,
121 RoleType varchar(255) CHECK (RoleType IN ('Actor', 'Director', 'Cameraman', 'Both'))
122);
123
124-- ------------------------------------------------
125
126CREATE TABLE Genre (
127 GenreID SERIAL PRIMARY KEY,
128 Name varchar(255) NOT NULL UNIQUE
129);
130
131-- ------------------------------------------------
132
133CREATE TABLE Language (
134 LanguageID SERIAL PRIMARY KEY,
135 Name varchar(255) NOT NULL UNIQUE
136);
137
138-- ------------------------------------------------
139
140CREATE TABLE Rating (
141 RatingID SERIAL PRIMARY KEY,
142 Rating_Date date DEFAULT CURRENT_DATE,
143 RatingValue int4 NOT NULL CHECK (RatingValue BETWEEN 1 AND 10),
144 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
145 ON DELETE CASCADE ON UPDATE CASCADE,
146 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
147 ON DELETE CASCADE ON UPDATE CASCADE,
148 UNIQUE (UserUserID, ContentContentID)
149);
150
151-- ------------------------------------------------
152
153CREATE TABLE Review (
154 ReviewID SERIAL PRIMARY KEY,
155 Comment varchar(255),
156 ReviewDate date DEFAULT CURRENT_DATE,
157 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
158 ON DELETE CASCADE ON UPDATE CASCADE,
159 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
160 ON DELETE CASCADE ON UPDATE CASCADE
161);
162
163-- ------------------------------------------------
164
165CREATE TABLE WatchHistory (
166 HistoryID SERIAL PRIMARY KEY,
167 WatchedAt date DEFAULT CURRENT_DATE,
168 Progress_percentage int4 CHECK (Progress_percentage BETWEEN 0 AND 100),
169 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
170 ON DELETE CASCADE ON UPDATE CASCADE,
171 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
172 ON DELETE CASCADE ON UPDATE CASCADE,
173 WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID)
174 ON DELETE CASCADE ON UPDATE CASCADE,
175 DevicesDeviceID int4 REFERENCES Devices (DeviceID)
176 ON DELETE SET NULL ON UPDATE CASCADE
177);
178
179-- ------------------------------------------------
180
181CREATE TABLE Watchlist (
182 WatchlistID SERIAL PRIMARY KEY,
183 dateAdded date DEFAULT CURRENT_DATE,
184 UserUserID int4 NOT NULL REFERENCES "User" (UserID)
185 ON DELETE CASCADE ON UPDATE CASCADE,
186 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
187 ON DELETE CASCADE ON UPDATE CASCADE,
188 WatchableWatchableID int4 NOT NULL REFERENCES Watchable (WatchableID)
189 ON DELETE CASCADE ON UPDATE CASCADE,
190 UNIQUE (UserUserID, ContentContentID)
191);
192
193-- ------------------------------------------------
194
195CREATE TABLE Content_Artist (
196 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
197 ON DELETE CASCADE ON UPDATE CASCADE,
198 ArtistArtistID int4 NOT NULL REFERENCES Artist (ArtistID)
199 ON DELETE CASCADE ON UPDATE CASCADE,
200 PRIMARY KEY (ContentContentID, ArtistArtistID)
201);
202
203-- ------------------------------------------------
204
205CREATE TABLE Content_Genre (
206 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
207 ON DELETE CASCADE ON UPDATE CASCADE,
208 GenreGenreID int4 NOT NULL REFERENCES Genre (GenreID)
209 ON DELETE CASCADE ON UPDATE CASCADE,
210 PRIMARY KEY (ContentContentID, GenreGenreID)
211);
212
213-- ------------------------------------------------
214
215CREATE TABLE Content_Language (
216 ContentContentID int4 NOT NULL REFERENCES Media (ContentID)
217 ON DELETE CASCADE ON UPDATE CASCADE,
218 LanguageLanguageID int4 NOT NULL REFERENCES Language (LanguageID)
219 ON DELETE CASCADE ON UPDATE CASCADE,
220 PRIMARY KEY (ContentContentID, LanguageLanguageID)
221);