Preface
This is the final project report written by us to fulfill the term project for the course CS457: Data Modeling and Implementation in the Department of Computer Science at Northwestern Polytechnic University (NPU). This project is designed for NPU university students, it implements the idea through which the user can search for any movie in movierental application.
This is a technical report, which explains the NPU Online Movie Rental project in detail. The report explains the database design, implementation and usage of the project in detail.
Sonal,Shilpa,Srilaxmi,Yasmin
Department of Computer Science,
Spring, 2014.
Objective of the Application:
This application will help maintain a Movie Subscription business to handle, store and access their movie related data and provide it to subscribed customers. The website is used to subscribe users, where they can watch any of the listed movie.
Applications and Database Used:
Front-end : PHP, HTML
Back-end: MS SQL Express Edition 2012
Functionalities of the System:
User: Add user, update/deactivate user profile
Movies: Add/Update/Delete Movie Titles, Movie Categories, Cast, Director, Publisher, Languages
Search Movie Listings based on Movie titles, particular cast name, director or category
Movie Ratings : Add/update comment, rating about a movie
Recommend movies based on User Interests, Show similar movies, Display highest reated movies by director with good review,
Show highest rated movies by people with similar interest
Display highest rated movies by cast with good review
Display highest reated movies by director with good review.
Operations Performed:
Design the database based on MS SQL Express Edition 2012
Include E/R Diagram
Design Tables
Describe the purpose of each table and meaning of each column in a table
Load sample data
Analyze the flow of data
Design front end application to access the database
Front-end application is designed to access all relevant data like user’s recently watched movies, suggestion to user based on her history, highest rated movies, and display logistics of data from admin point of view.
Tasks for each team member:
Sonal Macwan :
• User Registration, Sign-In and Account Details.
• Add/update/delete Movies.moviemaster records.
• Design dataflow of the system.
• Insert related sample data.
• Design and Develop backend-frontend parts related to this task.
• Let User Browse through Movie Listings.
• Search most popular movie, newest movie and query base on wild card characters, Display category wise movie listing
• Show highest rated movies by people with similar interest
SriLakshmi Kurell:
• Add/update/delete data related to Movie Cast, Director, Publisher, Languages.
• Explain tables related to this task.
• Insert related sample data.
• Develop frontend parts related to this task.
• Display movies based on cast names, director names, publisher names
• Display highest rated movies by cast with good review
Yasmin Diba:
• Explain tables related to this task.
• Insert related sample data.
• Develop frontend parts related to this task, which will access the database.
Shilpa Shinde
• Add/update user ratings and comments, movie.moviecast.
• Add/update/delete data related to Movie director and movie publisher.
• Explain tables related to this task.
• Insert related sample data.
• Develop front end parts related to this task to store and access these data.
• Display similar interest movies for a user. Display available reviews for a selected movie.
• Display highest reated movies by director with good review.
Design of DATABASE:
We have named our database as “MovieSubscription”. Tables inside this database are grouped into two schemas. 1). Users: It contains user related tables. 2.) Movies: It contains Movie related tables.
Description of Tables in the Database:
1. Users.Usermaster
Column Description for usermaster table:
Userid: This column will be the primary key for usermaster table. It will contain all users registered with the site.
Email: Email id of the user will be the user identification, with which he/she can sing-in.
trail_start_date: A user registers for the first time on this date, and can access the movie listing for free, for 30 days from this date.
Paid_sub_start_date: When a user’s free trial ends, this date updates and billing period starts from this paid_subscription_start_date.
Password: Password for the user to access his/her account.
Creditcardno: Collect creditcard number when paid subscription starts.
Userfname: This is user’s first name.
Userlname: This is user’s last name.
2. Users.userpayment
Column Description:
userid: This is foreign key which references the primary key in usermaster table. It will be used to track a user’s payment information.
paid_date: When a user wants to start his/her paid subscription.
amount: The amount of money for monthly subscription.
3. Users.userinterest
Column Description:
userid: This is a foreign key which references the primary key in usermaster table. It is used to track which user is interested in what movie categories.
moviecategoryid: This is a foreign key which references the primary key in Movies.category. It will help to figure out category description from that table.
watchinDate: This is a “getDate()” default column to store when a user watchs a particular movie.
4. Movies.castdetails
Column Description:
Castid: A castId is a primary key also identity key column. It gives detail Information about particular cast member.
Cfname: cfname is varchar and non nullable column where we store first name of the cast member.
Clname: clname is varchar and nullable column where we store last name of the cast member.
5. Movies.Director
directorid: A directorId is a primary key also identity key column. It gives detail information about particular director information.
firstname: fname is varchar and non nullable column where we store first name of the director.
lastname: lname is varchar and nullable column where we store last name of the director.
6. Movies.Publisher
Publisherid: A PublisherId is a primary key also identity key column. It gives detail Information about particular publisher information.
fname: fname is varchar and non nullable column where we store information about publisher.
Lname: lname is varchar and nullable column where we store information about publisher
7. Movies.Languages
languageid: languageid is a primary key also identity key column. It gives detail information about particular information.
languagename: It stores the name of the language.
8. Movies.Category
Column Description:
categoryid: categoryid is a primary key also identity key column. It gives detail information about particular information.
categorydescription: It stores the name of the category.
9. Movies.moviecast
Column Description:
movieid: A movieid is a foreign key which is same as movieid in moviemaster. It gives us the information about that movie.
castid: A castid is a foreign key which is same as castid in movies.cast. It gives us the information about the cast.
leadroleflag: A leadroleflag is having check constraint it gives the lead role information about the particular cast in that movie.
10. Movies.moviemaster
Column Description:
movieid : This is a primary key to identify particular movie from this master table, which contains all details about the movie.
movietitle: It stores title of the movie.
categoryid: This is a foreign key referencing to primary key in Movies.Category to identify the category of the movie.
directorid: This is foreign key referencing to primary key in movies.director table to identify the director of the movie.
publisherid : This is a foreign key referencing to primary key in movies.publisher table to identify the publisher of the movie.
releasedate: This field stores the release date of the movie.
summary: Small description of the movie.
mainlanguageid: To know what is the main language of the movie.
11. Movies.movierating
Column Description:
userid: A userid is a foreign key which same as user id in usermaster. It gives the detail information about particular user.
movieid: A movieid is a foreign key which is same as movieid in moviemaster. It gives us the information about that movie.
rating: It is the rating given by user for that movie. It is a foreign key which reference to rating table.
comment: The comments are given for the movie by user.
12. Movies.rating
Ratingid : It is primary key which gives the information about rating. It has values in the range of 1-5.
description: It is description of the rating id (1-very Bad, 2-Bad, 3-Good, and 4-VeryGood, 5-Excellent)
13. Movies.movielanguage
This table is used to store details if a movie is available in more than one language.
Column Description:
movieid: This is a foreign key referencing to movie.moviemaster table to indicate a specific movie.
languageid: What languages are available will be known from this foreign key referencing to movie.languages table.
ER Diagram
Data Flow Diagram:
1. User Registration and Sign-In
Submit Reg. Form
2. Billing Calculation
Admin -> login to the database – insert primary level data for Movies.director, publisher, cast, languages -> insert/update/delete data of moviedirector, moviepublisher, moviecast and moviemaster
User -> login- browse movies -> watch movie -> provide/update ratings/comment
Display various data for user: Presenting recently watched movies, suggestion and highest rated movies.
Display various data to admin: Logistics of database – how many movies based on different criteria.
CREATE QUERY WITH IDENTITY:
CREATE TABLE Movies.moviemaster(movieid int NOT NULL IDENTITY(1,1), movietitle varchar(200) NOT NULL,
categoryid int, directorid int, publisherid int, releasedate DATE, summary TEXT, mainlanguageid int,
CONSTRAINT pk_movieid PRIMARY KEY(movieid), CONSTRAINT fk_moviecategoryid FOREIGN KEY(categoryid) REFERENCES Movies.category(categoryid),
CONSTRAINT fk_moviedirectorid FOREIGN KEY(directorid) REFERENCES Movies.director(directorid), CONSTRAINT fk_moviepublisherid
FOREiGN KEY(publisherid) REFERENCES Movies.publisher(publisherid), CONSTRAINT fk_movielanguageid FOREiGN KEY(mainlanguageid)
REFERENCES Movies.languages(languageid) )
Create View And Index:
/*Create Index*/
———————————————–
select * from Movies.moviemaster with(INDEX(pk_movieid));
CREATE INDEX mIndex
ON Movies.moviemaster(movietitle)
select movietitle from Movies.moviemaster with(INDEX(mIndex));
——————————————————–
/*Create View To Give the Movie list with Rating 5*/
CREATE VIEW Rating_5
AS SELECT movietitle FROM MOVIES.moviemaster M
JOIN MOVIES.MovieRating R ON M.movieid = R.movieid
where rating = 5 group by movietitle;
select * from Rating_5;
Output:
SAMPLE SELECT QUERIES:
— HIGHEST RATED MOVIE — tOP 1 SHOWS 1 MOVIE, IF WE DO TOP 3, IT WILL SHOW TOP 3 MOVIES.
select top 1 m1.movieid, m.movietitle, rating, count(1) total from movies.Movierating m1 join Movies.moviemaster m
on (m.movieid = m1.movieid)
group by m1.movieid, m.movietitle, rating
order by rating desc, total desc
— list of moview user watched
select top 3 u.userid, m.movieid, m.movietitle , u.moviecategoryid
from [Users].[userinterest] u join [Movies].[moviemaster] m
on m.categoryid = u.moviecategoryid WHERE u.userid = 6 order by u.watchingDate DESC
— user interested movie
select top 3 m.movietitle AS suggestions from [Movies].[moviemaster] m where not exists (select 1 from [Users].[userinterest] u
where u.moviecategoryid = m.categoryid
and u.userid = 7)
STORED PROCEDURES:
/*Procedure to Find the Movie with Given Ratings*/
———————————–
drop proc rating_search
Create Proc rating_search
@rating int
AS
SELECT movietitle FROM MOVIES.moviemaster M
JOIN MOVIES.MovieRating R ON M.movieid = R.movieid
where rating = @rating group by movietitle;
DECLARE @movietitle varchar(40)
EXEC @movietitle = rating_search
@rating = 2
Output:
/*Highest rated movies by director with good review*/
——————————–
drop proc higest_rated_director_search
Create Proc higest_rated_director_search
@category_id int
AS
SELECT TOP 1 M.movietitle,R.rating,D.lastname AS director_lastname,D.firstname AS director_lastname
FROM Movies.moviemaster M
LEFT JOIN MOVIES.MovieRating R ON M.movieid = R.movieid
LEFT JOIN Movies.director D ON M.directorid = D.directorid WHERE categoryid = @category_id
ORDER BY R.rating DESC;
DECLARE @title varchar(30)
EXEC @title = higest_rated_director_search
@category_id = 2
OUTPUT:
————————————————–
/*Get Full Movie Details*/
——————————————-
CREATE PROCEDURE [dbo].[USP_GetFullMOvieList]
As
BEGIN
SELECT
DISTINCT
mm.movieid
,mm.movietitle
,md.firstname + ‘,’ + md.lastname As DirectorFullName
,mp.fname+ ‘ ‘ + mp.lname As Publisher
FROM Movies.moviemaster mm
INNER JOIN movies.category mc
ON mm.categoryid=mm.categoryid
INNER JOIN Movies.director md
on md.directorid=mm.directorid
INNER JOIN Movies.publisher mp
on mp.publisherid=mm.publisherid
END
EXEC [dbo].[USP_GetFullMOvieList]
Output:
——————————————
/*Get Full Movie cast Detail*/
——————————————-
CREATE PROCEDURE [dbo].[USP_GetMovieCastDetails]
As
BEGIN
SELECT
DISTINCT
mm.movieid
,mm.movietitle
,STUFF((SELECT ‘; ‘ + c.cfname+ ‘,’+c.clname
FROM Movies.moviecast mca
inner join Movies.[castdetail] c
on mca.castid=c.castid
WHERE mca.movieid=mm.movieid
FOR XML PATH(”)), 1, 1, ”) [Movie/Cast]
FROM Movies.moviemaster mm
GROUP BY mm.movieid,mm.movietitle
END
EXEC [dbo].[USP_GetMovieCastDetails]
Programming for kids 