1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97
| CREATE TABLE fileFormat(
frm_id INT PRIMARY KEY,
frm_name VARCHAR(8),
frm_isSupported LOGICAL
);
CREATE TABLE commentHolder(
cmh_id INT PRIMARY KEY
);
CREATE TABLE userAccount(
usr_id INT PRIMARY KEY,
usr_nick VARCHAR(16),
cmh_id INT REFERENCES commentHolder(cmh_id)
);
CREATE TABLE container(
ctn_id INT PRIMARY KEY,
ctn_name VARCHAR(50),
ctn_thumbnail VARCHAR(50),
ctn_synopsis TEXT,
cmh_id INT REFERENCES commentHolder(cmh_id)
);
CREATE TABLE trilogy(
ctn_id INT REFERENCES container(ctn_id) NOT NULL,
PRIMARY KEY(ctn_id)
);
CREATE TABLE serie(
ctn_id INT REFERENCES container(ctn_id) NOT NULL,
PRIMARY KEY(ctn_id)
);
CREATE TABLE season(
ctn_id_1 INT REFERENCES container(ctn_id) NOT NULL,
PRIMARY KEY(ctn_id_1),
ctn_id INT REFERENCES serie(ctn_id) NOT NULL
);
CREATE TABLE franchise(
ctn_id INT REFERENCES container(ctn_id) NOT NULL,
PRIMARY KEY(ctn_id)
);
CREATE TABLE comment(
cmy_id INT PRIMARY KEY,
cmt_txt TEXT,
cmt_datetime DATETIME,
usr_id INT REFERENCES userAccount(usr_id) NOT NULL,
cmh_id INT REFERENCES commentHolder(cmh_id) NOT NULL,
cmh_id_1 INT REFERENCES commentHolder(cmh_id)
);
CREATE TABLE video(
vid_id INT PRIMARY KEY,
vid_fileName VARCHAR(50),
vid_length INT,
vid_viewCount INT,
vid_description TEXT,
vid_id_1 INT REFERENCES video(vid_id),
ctn_id INT REFERENCES season(ctn_id_1),
usr_id INT REFERENCES userAccount(usr_id) NOT NULL,
frm_id INT REFERENCES fileFormat(frm_id) NOT NULL,
cmh_id INT REFERENCES commentHolder(cmh_id)
);
CREATE TABLE historyEntry(
hst_id INT PRIMARY KEY,
hst_date DATETIME,
hst_timeSpent DECIMAL(15,2),
hst_timeStop DECIMAL(15,2),
vid_id INT REFERENCES video(vid_id) NOT NULL,
usr_id INT REFERENCES userAccount(usr_id) NOT NULL
);
CREATE TABLE movie(
vid_id INT REFERENCES video(vid_id) NOT NULL,
PRIMARY KEY(vid_id),
mov_thumbnail VARCHAR(50),
mov_synopsis TEXT,
ctn_id INT REFERENCES trilogy(ctn_id)
);
CREATE TABLE contain_4(
vid_id INT REFERENCES movie(vid_id) NOT NULL,
ctn_id INT REFERENCES trilogy(ctn_id) NOT NULL,
ctn_id_1 INT REFERENCES serie(ctn_id) NOT NULL,
ctn_id_2 INT REFERENCES franchise(ctn_id) NOT NULL,
PRIMARY KEY(vid_id, ctn_id, ctn_id_1, ctn_id_2)
);
CREATE TABLE contain_6(
ctn_id INT REFERENCES franchise(ctn_id) NOT NULL,
ctn_id_1 INT REFERENCES franchise(ctn_id) NOT NULL,
PRIMARY KEY(ctn_id, ctn_id_1)
); |
Partager