Iteration 5 – Database

DROP TABLE most_valuable_review;
DROP TABLE user_favorite_category;
DROP TABLE user_review_score;
DROP TABLE user_movie_ranking;
DROP TABLE movie_category;
DROP TABLE movie_followed;
DROP TABLE friend_of;
DROP TABLE message;
DROP TABLE comment;
DROP TABLE review;
DROP TABLE category;
DROP TABLE movie;
DROP TABLE user;
DROP TABLE administrator;

CREATE TABLE `administrator` (
`adminname` varchar(11) NOT NULL,
`passwordHash` BLOB NOT NULL,
`recentSignInTime` varchar(20) DEFAULT NULL,
PRIMARY KEY (`adminname`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into administrator (adminname, passwordHash, recentSignInTime) values (“group5”, 0, “2010-01-01”);

CREATE TABLE `user` (
`username` varchar(11) NOT NULL,
`passwordHash` BLOB NOT NULL,
`imageURL` varchar(50) DEFAULT NULL,
`isTerminated` tinyint(1) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`username`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear”, 0, “upapabear.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela”, 0, “uangela.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty”, 0, “ukitty.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie”, 0, “uwennie.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky”, 0, “ulucky.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear1”, 0, “upapabear1.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela1”, 0, “uangela1.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty1”, 0, “ukitty1.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie1”, 0, “uwennie1.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky1”, 0, “ulucky1.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear2”, 0, “upapabear2.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela2”, 0, “uangela2.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty2”, 0, “ukitty2.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie2”, 0, “uwennie2.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky2”, 0, “ulucky2.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear3”, 0, “upapabear3.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela3”, 0, “uangela3.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty3”, 0, “ukitty3.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie3”, 0, “uwennie3.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky3”, 0, “ulucky3.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear4”, 0, “upapabear4.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela4”, 0, “uangela4.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty4”, 0, “ukitty4.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie4”, 0, “uwennie4.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky4”, 0, “ulucky4.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“papabear5”, 0, “upapabear5.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“angela5”, 0, “uangela5.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“kitty5”, 0, “ukitty5.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“wennie5”, 0, “uwennie5.png”, 0);
insert into user (username, passwordHash, imageURL, isTerminated) values (“lucky5”, 0, “ulucky5.png”, 0);

CREATE TABLE `movie` (
`movieId` int(11) NOT NULL AUTO_INCREMENT,
`movieName` varchar(50) NOT NULL,
`posterURL` varchar(50) DEFAULT NULL,
`summary` varchar(1000) DEFAULT NULL,
`castedTime` varchar(20) DEFAULT NULL,
PRIMARY KEY (`movieId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into movie (movieName, posterURL, summary, castedTime) values ( “Toy Story”, “1.png”, “A cowboy toy is profoundly threatened and jealous when a fancy spaceman toy supplants him as top toy in a boy’s room.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Finding Nemo”, “2.png”, “A father-son underwater adventure featuring Nemo, a boy clownfish, stolen from his coral reef home. His timid father must then travel to Sydney and search Sydney Harbour to find Nemo.”, “2003”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Jumanji”, “3.png”, “When two kids play an old magic board-game they found, they release a man trapped for decades in it and a host of dangers that can only be stopped by finishing the game.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Grumpier Old Men”, “4.png”, “Things don’t seem to change much in Wabasha County: Max and John are still fighting after 35 years, Grandpa still drinks, smokes, and chases women , and nobody’s been able to catch the fabled Catfish Hunter, a gigantic catfish that actually smiles at fishermen who try to snare it. Six months ago John married the new girl in town (Ariel), and people begin to suspect that Max might be missing something similar in his life. The only joy Max claims is left in his life is fishing, but that might change with the new owner of the bait shop.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Waiting to Exhale”, “5.png”, “Based on Terry McMillan’s novel, this film follows four very different African-American women and their relationships with the male gender.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Father of the Bride Part II”, “6.png”, “In this sequel to Father of the Bride, George Banks must accept the reality of what his daughter’s ascension from daughter to wife, and now, to mother means when placed into perspective against his own stage of life. As the comfortable family unit starts to unravel in his mind, a rapid progression into mid-life crisis is in his future. His journey to regain his youth acts as a catalyst for a kind of rebirth of his attitude on life when he and his wife, Nina, find how their lives are about to change as well.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Heat”, “7.png”, “A Los Angeles crime saga, Heat focuses on the lives of two men on opposite sides of the law – one a detective; the other a thief.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Sabrina”, “8.png”, “An ugly duckling having undergone a remarkable change, still harbors feelings for her crush: a carefree playboy, but not before his business-focused brother has something to say about it.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Tom and Huck”, “9.png”, “A mischievous young boy, Tom Sawyer (Jonathan Taylor Thomas),witnesses a murder by the deadly Injun Joe. Tom becomes friends with Huckleberry Finn “, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Sudden Death”, “10.png”, “A disgraced former fireman takes on a group of terrorists holding the Vice President and others hostage during the seventh game of the NHL Stanley Cup finals.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “GoldenEye”, “11.png”, “James Bond teams up with the lone survivor of a destroyed Russian research center to stop the hijacking of a nuclear space weapon by a fellow agent believed to be dead.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “American President”, “12.png”, “Comedy-drama about a widowed US president and a lobbyist who fall in love. It’s all aboveboard, but politics is perception and sparks fly anyway.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Balto”, “13.png”, “An outcast half-wolf risks his life to prevent a deadly epidemic from ravaging Nome, Alaska.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Nixon”, “14.png”, “A biographical story of former U.S. president Richard Milhous Nixon, from his days as a young boy to his eventual presidency which ended in shame.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Cutthroat Island”, “15.png”, “A female pirate and her companion race against their rivals to find a hidden island that contains a fabulous treasure.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Casino”, “16.png”, “Greed, deception, money, power, and murder occur between two mobster best friends and a trophy wife over a gambling empire.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Sense and Sensibility”, “17.png”, “Rich Mr. Dashwood dies, leaving his second wife and her daughters poor by the rules of inheritance. Two daughters are the titular opposites.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Four Rooms”, “18.png”, “Four interlocking tales that take place in a fading hotel on New Year’s Eve.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Ace Ventura: When Nature Calls”, “19.png”, “Pet detective Ace Ventura comes out of retirement to investigate the disappearance of a rare white bat, the symbol of an African tribe.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Money Train”, “20.png”, “A vengeful New York transit cop decides to steal a trainload of subway fares; his foster brother, a fellow cop, tries to protect him.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Get Shorty”, “21.png”, “A mobster travels to Hollywood to collect a debt and discovers that the movie business is much the same as his current job.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Copycat”, “22.png”, “An agoraphobic psychologist and a female detective must work together to take down a serial killer who copies serial killers from the past.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Assassins”, “23.png”, “Robert Rath is a seasoned hitman who just wants out of the business with no back talk. But, as things go, it ain’t so easy. A younger, peppier assassin named Bain is having a field day trying to kill said older assassin. Rath teams up with a computer hacker named Electra to defeat the obsessed Bain.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Powder”, “24.png”, “A young bald albino boy with unique powers shakes up the rural community he lives in.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Leaving Las Vegas”, “25.png”, “Because his wife left him and took his son with her, screenwriter Ben Anderson has started drinking, a lot. He’s getting more and more isolated and he troubles women in bars because he wants to have sex with them. When he gets fired, he decides to leave everything behind and move to Las Vegas and drink himself to death. In Las Vegas he meets Sera, a prostitute with some problems as well who he moves in with.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Othello”, “26.png”, “The evil Iago pretends to be friend of Othello in order to manipulate him to serve his own end in the film version of this Shakespeare classic.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Now and Then”, “27.png”, “Four 12-year-old girls grow up together during an eventful small-town summer in 1970.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Persuasion”, “28.png”, “Eight years earlier, Anne Elliot, the daughter of a financially troubled aristocratic family, was persuaded to break off her engagement to Frederick Wentworth, a young seaman, who, though promising, had poor family connections. When her father rents out the family estate to Admiral Croft, Anne is thrown into company with Frederick, because his sister is Mrs. Croft. Frederick is now a rich and successful Captain, and a highly eligible bachelor. Whom will he marry? One of Anne’s sister’s husband’s sisters? Or will he and Anne rekindle the old flame?”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “City of Lost Children”, “29.png”, “A scientist in a surrealist society kidnaps children to steal their dreams, hoping that they slow his aging process.”, “1995”);
insert into movie (movieName, posterURL, summary, castedTime) values ( “Shanghai Triad”, “30.png”, “A provincial boy related to a Shanghai crime family is recruited by his uncle into cosmopolitan Shanghai in the 1930s to be a servant to a ganglord’s mistress.”, “1995”);

CREATE TABLE `review` (
`reviewId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`movieId` int(11) NOT NULL,
`content` varchar(255) DEFAULT NULL,
`ranking` int(11) DEFAULT 1,
PRIMARY KEY (`reviewId`),
KEY `NewForeignKey_review` (`username`),
KEY `NewForeignKey_review1` (`movieId`),
CONSTRAINT `NewForeignKey_review` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_review1` FOREIGN KEY (`movieId`) REFERENCES `movie` (`movieId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into review (username, movieId, content, ranking) values (“papabear”, 1, “Nice Movie”, 5);
insert into review (username, movieId, content, ranking) values (“angela”, 1, “A very Impressive Movie! Like it.”, 5);
insert into review (username, movieId, content, ranking) values (“papabear”, 2, “Good.”, 4);
insert into review (username, movieId, content, ranking) values (“angela”, 2, “I don’t like it”, 2);
insert into review (username, movieId, content, ranking) values (“angela”, 2, “I think it’s good now”, 5);
insert into review (username, movieId, content, ranking) values (“kitty”, 2, “I think it’s good now”, 1);
insert into review (username, movieId, content, ranking) values (“kitty”, 3, “I think it’s good now”, 4);
insert into review (username, movieId, content, ranking) values (“kitty”, 4, “I think it’s good now”, 3);
insert into review (username, movieId, content, ranking) values (“kitty”, 5, “I think it’s good now”, 2);
insert into review (username, movieId, content, ranking) values (“kitty”, 6, “I think it’s good now”, 1);
insert into review (username, movieId, content, ranking) values (“lucky”, 3, “I think it’s good now”, 5);
insert into review (username, movieId, content, ranking) values (“lucky”, 4, “I think it’s good now”, 5);
insert into review (username, movieId, content, ranking) values (“lucky”, 5, “I think it’s good now”, 3);
insert into review (username, movieId, content, ranking) values (“lucky”, 6, “I think it’s good now”, 2);
insert into review (username, movieId, content, ranking) values (“lucky”, 7, “I think it’s good now”, 3);
insert into review (username, movieId, content, ranking) values (“wennie”, 2, “I think it’s good now”, 3);
insert into review (username, movieId, content, ranking) values (“wennie”, 3, “I think it’s good now”, 5);
insert into review (username, movieId, content, ranking) values (“wennie”, 4, “I think it’s good now”, 4);
insert into review (username, movieId, content, ranking) values (“wennie”, 5, “I think it’s good now”, 2);
insert into review (username, movieId, content, ranking) values (“wennie”, 6, “I think it’s good now”, 1);

CREATE TABLE `comment` (
`commentId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`reviewId` int(11) NOT NULL,
`content` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT 1,
PRIMARY KEY (`commentId`),
KEY `NewForeignKey_comment` (`username`),
KEY `NewForeignKey3` (`reviewId`),
CONSTRAINT `NewForeignKey_comment` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_comment1` FOREIGN KEY (`reviewId`) REFERENCES `review` (`reviewId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into comment(username,reviewId,content,score) values (“papabear”, 4, “Not Agree, It’s reallt a good movie.”, 1);
insert into comment(username,reviewId,content,score) values (“angela”, 1, “The best movie I have ever seen.”, 5);
insert into comment(username,reviewId,content,score) values (“angela1”, 4, “You should watch the movie again.”, 1);
insert into comment(username,reviewId,content,score) values (“papabear4”, 4, “I agree with you, this movie is not good.”, 5);

CREATE TABLE `friend_of` (
`friendOfId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`friendname` varchar(11) NOT NULL,
`friendStatus` varchar(7) NOT NULL DEFAULT ‘ACCEPT’,
PRIMARY KEY (`friendOfId`),
KEY `NewForeignKey_friendof` (`username`),
KEY `NewForeignKey_friendof1` (`friendname`),
CONSTRAINT `NewForeignKey_friendof` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_friendof1` FOREIGN KEY (`friendname`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into friend_of(username, friendname) values (“angela”, “papabear”);
insert into friend_of(username, friendname) values (“angela”, “kitty”);
insert into friend_of(username, friendname, friendStatus) values (“angela”, “angela1”, “REFUSED”);
insert into friend_of(username, friendname, friendStatus) values (“angela”, “angela2”, “REFUSED”);
insert into friend_of(username, friendname, friendStatus) values (“angela”, “angela3”, “REFUSED”);
insert into friend_of(username, friendname, friendStatus) values (“angela”, “angela4”, “REFUSED”);
insert into friend_of(username, friendname, friendStatus) values (“angela”, “wennie”, “PENDING”);
insert into friend_of(username, friendname, friendStatus) values (“lucky”, “angela”, “PENDING”);
insert into friend_of(username, friendname) values (“papabear”, “angela”);
insert into friend_of(username, friendname) values (“angela1”, “papabear2”);
insert into friend_of(username, friendname) values (“papabear2”, “angela1”);
insert into friend_of(username, friendname) values (“angela3”, “papabear2”);
insert into friend_of(username, friendname) values (“papabear2”, “angela3”);
insert into friend_of(username, friendname) values (“angela4”, “papabear3”);
insert into friend_of(username, friendname) values (“papabear3”, “angela4”);

CREATE TABLE `message` (
`messageId` int(11) NOT NULL,
`sentTime` varchar(20) DEFAULT NULL,
`content` varchar(20) DEFAULT NULL,
`sender` varchar(11) NOT NULL,
`receiver` varchar(11) NOT NULL,
`messageStatus` varchar(7) NOT NULL DEFAULT ‘UNREAD’,
PRIMARY KEY (`messageId`),
KEY `NewForeignKey_message` (`sender`),
KEY `NewForeignKey_message1` (`receiver`),
CONSTRAINT `NewForeignKey_message` FOREIGN KEY (`sender`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_message1` FOREIGN KEY (`receiver`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into message(messageId, sentTime, content, sender, receiver, messageStatus) values (1, “2010-02-10”, “Hi”, “angela”, “papabear”, “READ”);
insert into message(messageId, sentTime, content, sender, receiver, messageStatus) values (2, “2010-02-12”, “Hi”, “angela2”, “papabear”, “UNREAD”);
insert into message(messageId, sentTime, content, sender, receiver, messageStatus) values (3, “2010-02-13”, “Hi”, “papabear3”, “angela2”, “READ”);
insert into message(messageId, sentTime, content, sender, receiver, messageStatus) values (4, “2010-02-15”, “Hi”, “angela4”, “papabear3”, “READ”);
insert into message(messageId, sentTime, content, sender, receiver, messageStatus) values (5, “2010-02-17”, “Hi”, “papabear5”, “papabear”, “UNREAD”);

CREATE TABLE `category` (
`categoryId` int(11) NOT NULL AUTO_INCREMENT,
`categoryType` varchar(11) DEFAULT ‘Others’,
PRIMARY KEY (`categoryId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into category (categoryType) values (“Action”);
insert into category (categoryType) values (“Animation”);
insert into category (categoryType) values (“Adventure”);
insert into category (categoryType) values (“Children’s”);
insert into category (categoryType) values (“Comedy”);
insert into category (categoryType) values (“Crime”);
insert into category (categoryType) values (“Documentary”);
insert into category (categoryType) values (“Drama”);
insert into category (categoryType) values (“Fantasy”);
insert into category (categoryType) values (“Film-Noir”);
insert into category (categoryType) values (“Horror”);
insert into category (categoryType) values (“Musical”);
insert into category (categoryType) values (“Mystery”);
insert into category (categoryType) values (“Romance”);
insert into category (categoryType) values (“Sci-Fi”);
insert into category (categoryType) values (“Thriller”);
insert into category (categoryType) values (“War”);
insert into category (categoryType) values (“Western”);

CREATE TABLE `user_favorite_category` (
`userCategoryId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`categoryId` int(11) NOT NULL DEFAULT ‘0’,
`level` int(11) NOT NULL DEFAULT 1,
PRIMARY KEY (`userCategoryId`),
KEY `NewForeignKey_favorite` (`username`),
KEY `NewForeignKey_favorite1` (`categoryId`),
CONSTRAINT `NewForeignKey_favorite` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_favorite1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`categoryId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_favorite_category (username, categoryId, level) values (“papabear”, 1, 2);
insert into user_favorite_category (username, categoryId, level) values (“papabear”, 2, 1);
insert into user_favorite_category (username, categoryId, level) values (“angela”, 2, 1);
insert into user_favorite_category (username, categoryId, level) values (“papabear”, 3, 3);
insert into user_favorite_category (username, categoryId, level) values (“angela”, 10, 2);
insert into user_favorite_category (username, categoryId, level) values (“angela1”, 2, 1);
insert into user_favorite_category (username, categoryId, level) values (“angela1”, 12, 2);
insert into user_favorite_category (username, categoryId, level) values (“papabear1”, 2, 1);
insert into user_favorite_category (username, categoryId, level) values (“papabear1”, 6, 2);
insert into user_favorite_category (username, categoryId, level) values (“papabear1”, 18, 3);
insert into user_favorite_category (username, categoryId, level) values (“papabear1”, 12, 4);
insert into user_favorite_category (username, categoryId, level) values (“papabear3”, 12, 1);
insert into user_favorite_category (username, categoryId, level) values (“papabear3”, 2, 2);
insert into user_favorite_category (username, categoryId, level) values (“angela”, 5, 3);
insert into user_favorite_category (username, categoryId, level) values (“angela”, 11, 4);
insert into user_favorite_category (username, categoryId, level) values (“angela”, 13, 5);
insert into user_favorite_category (username, categoryId, level) values (“kitty”, 2, 2);
insert into user_favorite_category (username, categoryId, level) values (“kitty”, 10, 1);
insert into user_favorite_category (username, categoryId, level) values (“kitty”, 5, 4);
insert into user_favorite_category (username, categoryId, level) values (“kitty”, 11, 3);
insert into user_favorite_category (username, categoryId, level) values (“kitty”, 13, 5);
insert into user_favorite_category (username, categoryId, level) values (“lucky”, 10, 1);
insert into user_favorite_category (username, categoryId, level) values (“lucky”, 12, 2);
insert into user_favorite_category (username, categoryId, level) values (“wennie”, 5, 1);
insert into user_favorite_category (username, categoryId, level) values (“wennie”, 8, 4);
insert into user_favorite_category (username, categoryId, level) values (“wennie”, 7, 5);
insert into user_favorite_category (username, categoryId, level) values (“wennie”, 2, 2);
insert into user_favorite_category (username, categoryId, level) values (“wennie”, 10, 3);

CREATE TABLE `movie_category` (
`movieCategoryId` int(11) NOT NULL AUTO_INCREMENT,
`movieId` int(11) NOT NULL,
`categoryId` int(11) NOT NULL DEFAULT ‘0’,
PRIMARY KEY (`movieCategoryId`),
KEY `NewForeignKey_MovieCat` (`movieId`),
KEY `NewForeignKey_MovieCat1` (`categoryId`),
CONSTRAINT `NewForeignKey_MovieCat` FOREIGN KEY (`movieId`) REFERENCES `movie` (`movieId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_MovieCat1` FOREIGN KEY (`categoryId`) REFERENCES `category` (`categoryId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into movie_category (movieId, categoryId) values (1, 2);
insert into movie_category (movieId, categoryId) values (2, 2);
insert into movie_category (movieId, categoryId) values (3, 3);
insert into movie_category (movieId, categoryId) values (4, 5);
insert into movie_category (movieId, categoryId) values (5, 5);
insert into movie_category (movieId, categoryId) values (6, 5);
insert into movie_category (movieId, categoryId) values (7, 5);
insert into movie_category (movieId, categoryId) values (8, 3);
insert into movie_category (movieId, categoryId) values (9, 1);
insert into movie_category (movieId, categoryId) values (10, 1);
insert into movie_category (movieId, categoryId) values (11, 5);
insert into movie_category (movieId, categoryId) values (12, 2);
insert into movie_category (movieId, categoryId) values (13, 8);
insert into movie_category (movieId, categoryId) values (14, 1);
insert into movie_category (movieId, categoryId) values (15, 8);

CREATE TABLE `movie_followed` (
`movieFollowedId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`movieId` int(11) NOT NULL,
PRIMARY KEY (`movieFollowedId`),
KEY `NewForeignKey_MovieFollow` (`username`),
KEY `NewForeignKey_MovieFollow1` (`movieId`),
CONSTRAINT `NewForeignKey_MovieFollow` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_MovieFollow1` FOREIGN KEY (`movieId`) REFERENCES `movie` (`movieId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into movie_followed (username, movieId) values(“angela”, 1);
insert into movie_followed (username, movieId) values(“angela”, 2);
insert into movie_followed (username, movieId) values(“papabear”, 2);
insert into movie_followed (username, movieId) values(“papabear2”, 5);
insert into movie_followed (username, movieId) values(“papabear3”, 10);
insert into movie_followed (username, movieId) values(“papabear5”, 11);
insert into movie_followed (username, movieId) values(“angela1”, 4);
insert into movie_followed (username, movieId) values(“angela2”, 5);
insert into movie_followed (username, movieId) values(“angela2”, 10);

CREATE TABLE `user_movie_ranking` (
`userMovieRankingId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`movieId` int(11) NOT NULL,
`recentReviewId` int(11) NOT NULL,
PRIMARY KEY (`userMovieRankingId`),
KEY `NewForeignKey_MovieRanking` (`username`),
KEY `NewForeignKey_MovieRanking1` (`movieId`),
KEY `NewForeignKey_MovieRanking2` (`recentReviewId`),
CONSTRAINT `NewForeignKey_MovieRanking` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_MovieRanking1` FOREIGN KEY (`movieId`) REFERENCES `movie` (`movieId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_MovieRanking2` FOREIGN KEY (`recentReviewId`) REFERENCES `review` (`reviewId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_movie_ranking (username, movieId, recentReviewId) values (“papabear”, 1, 1);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“papabear”, 2, 3);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“angela”, 1, 2);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“angela”, 2, 5);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“kitty”, 2, 6);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“kitty”, 3, 7);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“kitty”, 4, 8);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“kitty”, 5, 9);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“kitty”, 6, 10);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“lucky”, 3, 11);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“lucky”, 4, 12);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“lucky”, 5, 13);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“lucky”, 6, 14);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“lucky”, 7, 15);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“wennie”, 2, 16);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“wennie”, 3, 17);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“wennie”, 4, 18);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“wennie”, 5, 19);
insert into user_movie_ranking (username, movieId, recentReviewId) values (“wennie”, 6, 20);

CREATE TABLE `user_review_score` (
`userReviewScoreId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`reviewId` int(11) NOT NULL,
`recentCommentId` int(11) NOT NULL,
PRIMARY KEY (`userReviewScoreId`),
KEY `NewForeignKey_ReviewScore` (`username`),
KEY `NewForeignKey_ReviewScore1` (`reviewId`),
KEY `NewForeignKey_ReviewScore2` (`recentCommentId`),
CONSTRAINT `NewForeignKey_ReviewScore` FOREIGN KEY (`username`) REFERENCES `user` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_ReviewScore1` FOREIGN KEY (`reviewId`) REFERENCES `review` (`reviewId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_ReviewScore2` FOREIGN KEY (`recentCommentId`) REFERENCES `comment` (`commentId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into user_review_score (username, reviewId, recentCommentId) values (“papabear”, 1, 2 );
insert into user_review_score (username, reviewId, recentCommentId) values (“angela”, 4, 1 );

CREATE TABLE `most_valuable_review` (
`mvrId` int(11) NOT NULL AUTO_INCREMENT,
`movieId` int(11) NOT NULL,
`reviewId` int(11) NOT NULL,
`valueOfReview` int(11) NOT NULL,
PRIMARY KEY (`mvrId`),
KEY `NewForeignKey_MVR` (`movieId`),
KEY `NewForeignKey_MVR1` (`reviewId`),
CONSTRAINT `NewForeignKey_MVR` FOREIGN KEY (`movieId`) REFERENCES `movie` (`movieId`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `NewForeignKey_MVR1` FOREIGN KEY (`reviewId`) REFERENCES `review` (`reviewId`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into most_valuable_review(movieId, reviewId, valueOfReview) values (1, 1, 5)