|Cinema App Features|
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.
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.
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
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
where tblSeatReservation.PlaysID=tblPlays.PlaysID and
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
as nr_reserved_seats,sum(tblMovies.Cost) as moneys_from_sold_tickets
where tblMovies.MovieID=tblPlays.MovieID and
tblSeats.SeatID=tblSeatReservation.SeatID and tblPlays.HallID=tblSeats.HallID
and tblMovies.MovieID=tblHasPrice.MovieID and
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
where tblMovies.MovieID=tblPlays.MovieID and
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
where tblEmployers.Username=tblSeatReservation.ReservedBy and tblMovies.MovieID=tblPlays.MovieID and
group by tblEmployers.Username,tblMovies.Name,tblPlays.StartTime
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.