Case Sample: Cinema Application SQL Queries

Cinema App Features

In one of my previous posts i explained the creation of Database Structure of our Cinema Application. You can proceed reading it here: Case Sample: Cinema Application ER Diagrams In this post, i started from scratch by firstly putting the requirement for Cinema Application and then designing the ER Diagrams.

With the ER Diagrams we catch many logical connections and constraints of our Application. Then, i proceeded with the conversion of ER Diagrams into Relational Schema and the actual SQL Implementation. For further read proceed here: Case Sample: Cinema Application, ER to Relational Model mapping and SQL implementation Now, i am going to implement couple of SQL queries and deduce some interesting statistics from our actual Database.

By creating the Database Tables we are creating the structure of how our persistent data will be arranged in the memory. The arrangement is quite crucial because we can extract valuable informations or statistics through posing queries. Queries are one of the most important aspects of a Database. It's so important that we cannot label any program as "full application" without the use of Databases or Database Queries.

Queries are not persistent, which means that they are created at once. They can be created even from application side where in combination with programming language we "pose a question" to our database. But, we also have an other option or methodology to create "Views" from our DBMS. Views are actually virtual tables where they extract information from one or more tables.

SQL Views

Now, let's start with couple of SQL queries related to our Cinema Database.

genreReservation View: shows which category of films was mostly watched, we need this information as statistics because Cinema Managers could find it useful to track what the audience prefers. genreReservation Views extract informations from three tables tblMovies, tblPlays and tblSeatReservation where the column ID of Movies is equalized with the column of MovieID at the Plays table.

create view genreReservation as select tblMovies.Genre,COUNT(tblSeatReservation.SeatID) as nrezervimeve from tblMovies,tblPlays,tblSeatReservation where tblMovies.MovieID=tblPlays.MovieID and tblPlays.PlaysID=tblSeatReservation.PlaysID group by tblMovies.Genre

 HallNumberSeats View: shows how many seats per halls are reserved and how many have been left non-reserved, HallNumberSeats is a very useful statistics.

create view HallNumberSeats select tblSeats.HallID,tblHall.Capacity,count(SeatID) as nr_registered_seats,tblHall.Capacity-count(SeatID) as nr_seats_left_toregister
from tblSeats,tblHall
where tblHall.HallID=tblSeats.HallID
group by tblSeats.HallID,tblHall.Capacity

                         

MostwatchedMovie View: we need a query where it should be computed which movie was the most watched at our cinema, the reasons behind it are of curiosity and statistics issues. The query is formed in such a manner where it is selected the name, genre, language, director, the price. The query is formed by groupin in a descending order from the most reserved movie and in the end we only select the first one by typing SELECT TOP 1.

create view MostwatchedMovie as select TOP 1
Name,Genre,Language,Director,ReleaseDate,Cost,count(tblSeatReservation.SeatID) as number_of_reservation
from tblMovieS,tblSeatReservation,tblPlays
where tblSeatReservation.PlaysID=tblPlays.PlaysID and
tblPlays.MovieID=tblMovies.MovieID
group by Name,Genre,Language,Director,ReleaseDate,Cost
order by count(tblSeatReservation.SeatID) desc

MovieStatistics View:  is a view which shows Statistics regarding every movie which had a premier at the Cinema, in fact this query makes a complex statistics about every movie like how many moneys we have won for every movie in a specific time and a specific hall.

create view MovieStatistics
SELECT tblMovies.Name,tblPlays.StartTime,tblPlays.HallID,count(tblHasPrice.SeatID)
as nr_reserved_seats,sum(tblMovies.Cost) as moneys_from_sold_tickets
from  tblSeatReservation,tblPlays,tblMovies,tblSeats,tblHasPrice
where  tblMovies.MovieID=tblPlays.MovieID and
tblPlays.PlaysID=tblSeatReservation.PlaysID and
tblSeats.SeatID=tblSeatReservation.SeatID and tblPlays.HallID=tblSeats.HallID
and tblMovies.MovieID=tblHasPrice.MovieID and
tblHasPrice.ClientID=tblSeatReservation.ClientID
group by tblMovies.Name,tblPlays.StartTime,tblPlays.HallID

NotReserved View: just as the name implies this query tells about the non-reserved seats about one Movie in a specific Hall.

create view NotReserved as select tblMovies.Name,tblPlays.StartTime,tblPlays.HallID,tblSeats.SeatID
from tblMovies,tblPlays,tblSeats
where tblMovies.MovieID=tblPlays.MovieID and
tblSeats.HallID=tblPlays.HallID
except
(
select
tblMovies.Name,tblPlays.StartTime,tblPlays.HallID,tblSeatReservation.SeatID
from tblSeatReservation,tblPlays,tblMovies,tblSeats
where tblMovies.MovieID=tblPlays.MovieID
and tblSeatReservation.PlaysID=tblPlays.PlaysID and
tblMovies.MovieID=tblPlays.MovieID and tblSeats.HallID=tblPlays.HallID
)
select Name,StartTime,HallID,COUNT(SeatID) as nr_notregistered from
NotReserved group by Name,StartTime,HallID

ReservedByEmployers View: is a crucial statistics for the Cinema Application because the employee can have access to the application only with login credentials, so we know that all the ticket reservations will be made from employees, but we need to have information for every ticket which employee has reserved it.

create view ReservedByEmployers as select tblEmployers.Username,tblMovies.Name,tblPlays.StartTime,COUNT(tblSeatReservation.SeatID) as number_of_seats
from tblEmployers,tblMovies,tblPlays,tblSeatReservation
where tblEmployers.Username=tblSeatReservation.ReservedBy  and tblMovies.MovieID=tblPlays.MovieID and
tblPlays.PlaysID=tblSeatReservation.PlaysID
group by tblEmployers.Username,tblMovies.Name,tblPlays.StartTime

Conclusion

Finally, i have already made three posts regarding the construction of the Cinema Application. The first one was: Catching the application requirements and designing the ER Diagrams, the second one was: ER Diagrams to Relational Schema conversion as well as SQL implementation, and the third one ( this post) was implementing some SQL Views which showed valuable statistics information for our application. If you have any questions, feel free to pose it.

Copyright: smartnoob.blogspot.com

Browse More Articles

Tweet

Written by Leo

Thank you for taking your time to browse through our blog. We try to build as much rich content base as we can. We focus in modern technologies, computer science design and business.