· 8 years ago · Jan 15, 2018, 07:36 PM
1/*CREATE DATABASE netflix*/
2/*Bij eerste gebruik kan het voorkomen dat het de database 'netflix' niet gevonden kan worden, haal dan de '/* */' weg hierboven*/
3USE netflix;
4
5DROP TABLE IF EXISTS views;
6DROP TABLE IF EXISTS profiles;
7DROP TABLE IF EXISTS subscribers;
8DROP TABLE IF EXISTS episodes;
9DROP TABLE IF EXISTS movies;
10DROP TABLE IF EXISTS programs;
11DROP TABLE IF EXISTS LANGUAGE;
12
13CREATE TABLE subscribers
14(
15 id INT NOT NULL PRIMARY KEY,
16 name VARCHAR(100) NOT NULL,
17 city VARCHAR(100) NOT NULL,
18 address VARCHAR(100) NOT NULL
19)
20
21INSERT INTO subscribers(id, name, city, address) VALUES('1215426', 'Fam. van Raalte','Houten', 'Schopenhauerdijkje 5');
22INSERT INTO subscribers(id, name, city, address) VALUES('5602533', 'J. van Betlehem','Breda', 'Nietzschestraat 99');
23INSERT INTO subscribers(id, name, city, address) VALUES('5285824', 'Fam. van Raalte','Breda', 'Kantlaan 11');
24
25CREATE TABLE profiles
26(
27 id INT NOT NULL PRIMARY KEY,
28 name VARCHAR(100) NOT NULL,
29 birthday DATE NOT NULL,
30
31 CONSTRAINT profilesFK1
32 FOREIGN KEY (id)
33 REFERENCES subscribers(id)
34 ON DELETE CASCADE
35 ON UPDATE CASCADE
36
37)
38
39INSERT INTO profiles(id, name, birthday) VALUES('1215426', 'Frank', '1968-1-25')
40INSERT INTO profiles(id, name, birthday) VALUES('5602533', 'Petrus', '1999-6-26')
41INSERT INTO profiles(id, name, birthday) VALUES('5285824', 'Fritz', '1968-8-19')
42
43
44CREATE TABLE languages
45(
46 code VARCHAR(2) NOT NULL PRIMARY KEY,
47 name VARCHAR(32) NOT NULL
48)
49
50INSERT INTO languages(code, name) VALUES('en', 'Engels');
51INSERT INTO languages(code, name) VALUES('nl', 'Nederlands');
52INSERT INTO languages(code, name) VALUES('fr', 'Frans');
53INSERT INTO languages(code, name) VALUES('de', 'Duits');
54
55CREATE TABLE programs
56(
57 id INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
58 title VARCHAR(50) NOT NULL,
59 genre VARCHAR NOT NULL CHECK (genre IN('action', 'documentary', 'comedy', 'horror', 'thriller')),
60 LANGUAGE VARCHAR(2) NOT NULL,
61 ageminimum INT NOT NULL,
62 ismovie INT NOT NULL CHECK (ismovie IN(0, 1))
63 /* De lengte van een programma wordt in movies & episodes bijgehouden, en in het Java systeem berekend */
64)
65
66INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('1010', 'The Abominable Bride', 'action', 'en', '12', '1')
67INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8002', 'Pulp Fiction', 'comedy', 'en', '16', '1')
68INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8010', 'The Good, the Bad and the Ugly', 'thriller', 'en', '16', '1')
69INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('8014', 'Der Untergang', 'action', 'en', '6', '1')
70
71INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('1004', 'Sherlock', 'action', 'en', '16', '0')
72INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('2004', 'Breaking Bad', 'action', 'en', '16', '0')
73INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('2014', 'Breaking Bad', 'action', 'en', '16', '0')
74INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('3002', 'Fargo', 'action', 'en', '16', '0')
75INSERT INTO programs(id, title, genre, LANGUAGE,ageminimum,ismovie) VALUES('3103', 'Fargo', 'action', 'en', '16', '0')
76
77CREATE TABLE episodes
78(
79 program INT NOT NULL,
80 episodeid INT NOT NULL,
81 title VARCHAR(50) NOT NULL,
82 LENGTH INT NOT NULL, /* length in minutes */
83
84
85 CONSTRAINT episodesFK
86 FOREIGN KEY (program)
87 REFERENCES programs(id),
88 CONSTRAINT episodesPK
89 PRIMARY KEY (program, episodeId)
90)
91
92INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Sherlock', 'S02E01', 'A Scandal in Belgravia', '88')
93INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Breaking Bad', 'S01E05', 'Gray Matter', '48')
94INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Breaking Bad', 'S02E08', 'Better Call Saul', '48')
95INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Fargo', 'S01E02', 'The Rooster Prince', '68')
96INSERT INTO episodes(program, episodeid, title, LENGTH) VALUES('Fargo', 'S02E03', 'The Myth of Sisyphus', '68')
97
98CREATE TABLE movies
99(
100 program INT NOT NULL,
101 LENGTH INT NOT NULL,
102
103
104 CONSTRAINT moviesFK
105 FOREIGN KEY (program)
106 REFERENCES programs(id)
107 ON DELETE CASCADE
108 ON UPDATE CASCADE
109)
110
111INSERT INTO movies(program, LENGTH) VALUES('The Abominable Bride', '109')
112INSERT INTO movies(program, LENGTH) VALUES('Pulp Fiction', '154')
113INSERT INTO movies(program, LENGTH) VALUES('The Good, the Bad and the Ugly', '161')
114INSERT INTO movies(program, LENGTH) VALUES('Der Untergang', '178')
115
116
117CREATE TABLE views
118(
119 id INT NOT NULL PRIMARY KEY,
120 profile INT NOT NULL,
121 program INT NOT NULL,
122 percentage INT NOT NULL,
123
124 CONSTRAINT viewsFK1
125 FOREIGN KEY (profile)
126 REFERENCES profiles(id)
127 ON DELETE CASCADE
128 ON UPDATE CASCADE
129)
130
131INSERT INTO views(id, profile, program, percentage) VALUES('1215426', '1215426', 'Sherlock', '100')
132INSERT INTO views(id, profile, program, percentage) VALUES('5602533', '5602533', 'Fargo', '74')
133INSERT INTO views(id, profile, program, percentage) VALUES('5285824', '5285824', 'The Abominable Bride', '5')