-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathDB-CreateSchema_MySQL.sql
183 lines (151 loc) · 10.1 KB
/
DB-CreateSchema_MySQL.sql
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
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
DROP TABLE IF EXISTS UserMovie;
DROP TABLE IF EXISTS Movie;
DROP TABLE IF EXISTS Season;
DROP TABLE IF EXISTS FilmGenre;
DROP TABLE IF EXISTS AppUser;
DROP TABLE IF EXISTS Role;
/* Catalogues */
CREATE TABLE `Role` (
`Role_ID` int NOT NULL AUTO_INCREMENT,
`Label` varchar(20),
`Description` varchar(100),
PRIMARY KEY (`Role_ID`)
);
CREATE TABLE `Season` (
`Season_ID` int NOT NULL AUTO_INCREMENT,
`Label` varchar(20),
`Description` varchar(100),
PRIMARY KEY (`Season_ID`)
);
CREATE TABLE `FilmGenre` (
`FilmGenre_ID` int NOT NULL AUTO_INCREMENT,
`Label` varchar(20),
`Description` varchar(100),
PRIMARY KEY (`FilmGenre_ID`)
);
/* Live Data */
CREATE TABLE `AppUser` (
`User_ID` int NOT NULL AUTO_INCREMENT,
`Role_ID` int NOT NULL,
`Username` varchar(12),
`FullName` varchar (30),
`PassWord` varchar(12),
`UserType_ID` int,
PRIMARY KEY (`User_ID`),
FOREIGN KEY (`Role_ID`) REFERENCES Role(`Role_ID`)
);
CREATE TABLE `Movie` (
`Movie_ID` int NOT NULL AUTO_INCREMENT,
`Title` varchar(50),
`Season_ID` int,
`RuntimeMinutes` int,
`Director` varchar(30),
`Production` varchar(30),
`Synopsis` varchar(900),
`ImagePath` varchar(200),
`PremiereDate` datetime,
`Year` int,
`FilmGenre_ID` int,
PRIMARY KEY (`Movie_ID`),
FOREIGN KEY (Season_ID) REFERENCES Season (Season_ID),
FOREIGN KEY (FilmGenre_ID) REFERENCES FilmGenre (FilmGenre_ID)
);
CREATE TABLE `UserMovie` (
`User_Movie` int NOT NULL AUTO_INCREMENT,
`User_ID` int,
`Movie_ID` int,
`Rank` int,
`AlreadySeen` boolean default false not null,
PRIMARY KEY (`User_Movie`),
FOREIGN KEY (User_ID) REFERENCES AppUser (User_ID),
FOREIGN KEY (Movie_ID) REFERENCES Movie (Movie_ID)
);
/* ********************************************************** */
/* ******** POPULATED DATA **************************** */
/* ********************************************************** */
/* Catalogue UserType [FilmGenre_ID is autogenerated] */
INSERT INTO Role(label, description)
VALUES
('Administrator','Can Create/Edit/Delete movies'),
('cinephile','Able for movie review and Favourite ranking');
/* Catalogue Season [FilmGenre_ID is autogenerated] */
INSERT INTO Season(label, description)
VALUES
('Fall', 'September 1 to November 30' ),
('Winter', 'December 1 to February 28'),
('Spring', 'March 1 to May 31'),
('Summer', 'June 1 to August 31');
/* Catalogue FilmGenre [usertype_id is autogenerated] */
INSERT INTO FilmGenre(label, description)
VALUES
('UKNOWN', 'If you want to know more'),
('Action', 'Stay on your seat'),
('Adventure', 'Enjoy the ride'),
('Comedy', 'Laugh'),
('Crime', 'Cops and bandids'),
('Drama', 'Crying'),
('Fantasy', 'Dreams '),
('Historical', 'Real Facts'),
('Horror', 'Make Scream'),
('Mystery', 'T'),
('Philosophical', 'Humanities and knowledge'),
('Romance', 'Romantical situations'),
('Science fiction', 'ovnis, future, robots'),
('Thriller', 'Michael Jackson'),
('Western', 'cowboys, deserts');
/* TESTING DATA *** */
/* Users: user_id is autogenerated */
INSERT INTO AppUser(username, fullname, password, role_id)
VALUES
('dennis', 'Dennis Nay' , 't3am', 1),
('netto', 'Ernesto Villalobos' , 'T3amM3mb3r2', 2),
('kang', 'Kang Yang' , 'T3amM3mb3r3', 1),
('youngyun', 'Youngyun Namkung' , 'T3amM3mb3r4', 2);
/* Movies: movie_id is autogenerated */
INSERT INTO Movie
(title, runtimeminutes, director, production,
synopsis, imagepath, premieredate, year, season_ID, filmgenre_id)
VALUES
('The Party', 71, 'Sally Potter', 'Adventure Pictures', 'Janet (Dame Kristin Scott Thomas) hosts a party to celebrate her new promotion, but once the guests arrive, it becomes clear that not everything is going to go down as smoothly as the red wine.', '/images/theparty.jpg', '2018-02-16', '2018', 1, 6),
('Proud Mary', 88, 'Babak Najafi', 'Screen Gems', 'Mary (Taraji P. Henson) is a professional assassin who works for Benny, a ruthless gangster who heads an organized crime family in Boston. Her life gets completely turned around when she crosses paths with a 12-year-old boy named Danny during her latest hit. Mary\'s desire to save Danny from the streets and a sadistic drug dealer causes an all-out turf war that forces her to do what she does best and take down whoever stands in her way.', '/images/proudmary.jpg', '2018-02-23', '2018', 4, 10),
('Annihilation', 115, 'Alex Garland', 'Skydance Media', 'Lena (Natalie Portman), a biologist and former soldier, joins a mission to uncover what happened to her husband inside Area X -- a sinister and mysterious phenomenon that is expanding across the American coastline. Once inside, the expedition discovers a world of mutated landscapes and creatures, as dangerous as it is beautiful, that threatens both their lives and their sanity.', '/images/annihilation.jpg', '2018-02-13' , '2018', 3, 13),
('Dark River', 89, 'Clio Barnard', 'Film4', 'Following the death of her father, Alice returns home to Yorkshire for the first time in 15 years to claim the tenancy of the family farm she believes is rightfully hers. Once there, she encounters older brother Joe, a man she barely recognizes. Joe is thrown by Alice\'s sudden arrival, angered by her claim and finds her presence increasingly impossible to deal with. Battling to regain control in a fragile situation, Alice must confront traumatic memories.”', '/images/darkriver.jpg', '2018-02-23', '2018', 4, 6),
('A Wrinkle in Time', 109, 'Ava DuVernay', 'Walt Disney Pictures', 'Meg Murry and her little brother, Charles Wallace, have been without their scientist father, Mr. Murry, for five years, ever since he discovered a new planet and used the concept known as a tesseract to travel there. Joined by Meg\'s classmate Calvin O\'Keefe and guided by the three mysterious astral travelers, Mrs. Whatsit, Mrs. Who and Mrs. Which, the children brave a dangerous journey to a planet possessing all the evil in the universe.', '/images/wrinkleintime.jpg', '2018-09-21', '2018', 1, 7),
('You Were Never Really Here', 90, 'Lynne Ramsay', 'Film4 Productions', 'A traumatized veteran, unafraid of violence, tracks down missing girls for a living. When a job spins out of control, Joe\'s (Joaquin Phoenix) nightmares overtake him as a conspiracy is uncovered leading to what may be his death trip or his awakening.', '/images/neverreallyhere.jpg', '2018-04-06', '2018', 2, 14),
('The Last Black Man in San Francisco', 120, 'Joe Talbot', 'Plan B Entertainment', 'Jimmie (Jimmie Fails) and his best friend Mont try to reclaim the house built by Jimmie\'s grandfather, launching them on a poignant odyssey that connects them to their past, even as it tests their friendship and sense of belonging in the place they call home.', '/images/lastblackman.jpg', '2019-08-13', '2019', 2, 6),
('Gone Girl', 149, 'David Fincher', 'Regency Enterprises', 'In Carthage, Mo., former New York-based writer Nick Dunne (Ben Affleck) and his glamorous wife Amy (Rosamund Pike) present a portrait of a blissful marriage to the public. But when Amy goes missing on the couple\'s fifth wedding anniversary, Nick becomes the prime suspect. The resulting police pressure and media frenzy cause the Dunnes\' happy image to crumble, leading to tantalizing questions about who Nick and Amy truly are.', '/images/gonegirl.jpg', '2018-02-16', '2018', 1, 6),
('The Handmaiden', 145, 'Park Chan-wook', 'Moho Film', 'With help from an orphaned pickpocket (Kim Tae-ri), a Korean con man (Ha Jung-woo) devises an elaborate plot to seduce and bilk a Japanese woman (Kim Min-hee) out of her inheritance.', '/images/thehandmaiden.jpg', '2016-10-21', '2016', 1, 10),
('The Revenant', 156, 'Alejandro G. Iñárritu', 'Regency Enterprises', 'A frontiersman (Leonardo DiCaprio) on a fur trading expedition in the 1820s fights for survival after being mauled by a bear and left for dead by members of his own hunting team.', '/images/revenant.jpg', '2016-01-08', '2016', 1, 2),
('Rogue One: A Star Wars Story', 133, 'Gareth Edwards', 'Lucasfilm Ltd.', 'Starring Felicity Jones, the daughter of an Imperial scientist joins the Rebel Alliance in a risky move to steal the plans for the Death Star.', '/images/rogueone.jpg', '2016-12-16', '2016', 1, 2),
('The Farewell', 100, 'Lulu Wang', 'Ray Productions', 'Billi\'s (Awkwafina) family returns to China under the guise of a fake wedding to stealthily say goodbye to their beloved matriarch -- the only person that doesn\'t know she only has a few weeks to live.', '/images/thefarewell.jpg', '2019-08-02', '2019', 1, 6),
('Moonlight', 111, 'Barry Jenkins', 'A24', 'A look at three defining chapters in the life of Chiron (Trevante Rhodes), a young black man growing up in Miami. His epic journey to manhood is guided by the kindness, support and love of the community that helps raise him.', '/images/moonlight.jpg', '2016-10-21', '2016', 1, 6),
('Interstellar', 169, 'Christopher Nolan', 'Paramount Pictures', 'In Earth\'s future, a global crop blight and second Dust Bowl render the planet uninhabitable. Professor Brand (Michael Caine), a NASA physicist, works to save mankind by transporting Earth\'s population to a new home via a wormhole. Brand must first send former NASA pilot Cooper (Matthew McConaughey) and their team through the wormhole and across the galaxy to find which of three planets could be mankind\'s new home.', '/images/interstellar.jpg', '2014-11-07', '2014', 1, 13);
/* UserMovie: user_movie is autogenerated */
INSERT INTO `movieproject`.`UserMovie`
(`User_ID`, `Movie_ID`, `Rank`, `AlreadySeen`)
VALUES
(4, 1, 0, false),
(1, 2, 5, true),
(1, 3, 2, true),
(3, 5, null, false),
(4, 4, 1, false),
(4, 3, 4, true),
(2, 4, 3, true);
/* ********************************************************** */
/* >>>>>>>> DEMO QUERIES <<<<<<<<<<<<<<<<<<<<<<<<< */
/* Print users + type * /
select * from AppUser u
inner join Role r on r.Role_ID = u.Role_ID;
/* Print Movies + + season + gendre * /
select m.movie_id, m.title, m.year, s.description, fg.description genre
-- m.*, fg.*, s.*
from Movie m
inner join Season s on s.Season_ID = m.Season_ID
inner join FilmGenre fg on fg.FilmGenre_ID = m.FilmGenre_ID;
/* Print (Favorite) movies by user * /
select um.user_movie, u.Username, m.title
from UserMovie um
inner join AppUser u on um.User_ID = u.User_ID
inner join Movie m on um.Movie_ID = m.Movie_ID
where um.user_id = 4;
--*/