Iteration 3 – Database

Database

Our project use JBOSS, Spring and is based on J2EE.

If the program has been built, you will see our demo with typing following URL into your favorite web browser:   http://localhost:8080/MovieManiacMatrixWeb/welcome/hello2.html

Database Name: movie_maniac_matrix

The following are the SQL code for creating our databases.

 

DROP TABLE user_favorite_category;
DROP TABLE movie_category;
DROP TABLE friendOf;
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;

CREATE TABLE `user` (
`username` varchar(11) NOT NULL,
`passwordHash` BLOB NOT NULL,
`imageURL` int(11) 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 (“abc”, 0, null, 0);

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

CREATE TABLE `review` (
`reviewId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`movieId` int(11) NOT NULL,
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;

CREATE TABLE `comment` (
`commentId` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(11) NOT NULL,
`reviewId` int(11) NOT NULL,
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;

CREATE TABLE `friendOf` (
`username` varchar(11) NOT NULL,
`friendname` varchar(11) NOT NULL,
`friendStatus` varchar(7) NOT NULL DEFAULT ‘ACCEPT’,
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;

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 values (1, “Action”);
insert into category values (2, “Animation”);
insert into category values (3, “Drama”);

CREATE TABLE `user_favorite_category` (
`username` varchar(11) NOT NULL,
`categoryId` int(11) NOT NULL DEFAULT ‘0’,
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;

CREATE TABLE `movie_category` (
`movieId` int(11) NOT NULL,
`categoryId` int(11) NOT NULL DEFAULT ‘0’,
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 user_favorite_category values (“abc”, 1);
insert into user_favorite_category values (“abc”, 3);