Making a cinema app in PLSQL, how to find best seat and row?

[hedub]

Hi,
I am making a small application in PL/SQL for handling ordering of movietickets at a cinema (i am a newbie to this). When you enter your wish for what seat on which row you want among available seats, the program does the rest by registering your information into the database. BUT if you do not enter any value for seat and row - a procedure is to find the best combination of a seat and a row in the showroom.

This is what I have done:

finding max value for row and seat, then putting the values into a random select like:

SELECT round(dbms_random.value(seat_min, seat_max),0)
into x_seat
from dual;

SELECT round(dbms_random.value(row_min, row_max),0)
into x_row
from dual;

I then take the x_seat and x_row and puts them in a select statement that finds a valid combination of a seat and row. All this is in a loop that exit when the output of the select statement has value >=1 for seat and row.

But this only works sometimes. Acationally it returns with the exception no_data_found. So this is not a good way of resolving this.

So, how can I find the best available combination of a seat and a row in a showroom of a cinema? I assume it has to be in the middle. For example if you have:

seat 1 on row 1
seat 2 on row 1
seat 3 on row 1
seat 1 on row 2
seat 2 on row 2
seat 3 on row 2
seat 1 on row 3
seat 2 on row 3
seat 3 on row 3

the best combination is seat 2 on row 2 if the seat is available and not taken... How do I do this? I need serious help.. Thanks!

 

[SantaMufasa]

Hedub,

The challenge in your case is not so much a programming challenge as it is a business challenge. The business challenge is to rate each seat in the theatre.

Additionally, you must deal with the issue of adjacent seating. If a family of seven come to the box office and ask for the seven best seats in the house, they typically mean, "Give us the seven best adjacent seats in the theatre." They do not want to be sprinkled separately around the theatre, just because those seats are the best in the house.

So your job, Hedub, before we can help you program this in SQL or PL/SQL, is to formulate your non-programmatical, business algorithms for dealing with these two issues. If you provide us with the raw data formats and business logic to resolve such requests, we can help you with the code.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

Do you use Oracle and live or work in Utah, USA?
Then click here to join Utah Oracle Users Group on Tek-Tips.

 

[KenCunningham]

And of course in pure subjective terms, how does one define 'best' - what, for example, if someone arrives finding they've forgotten their glasses (holds hands up as a culprit!)?

 

[hedub]

Hi, thank you all for the input. Let us say you can only order one ticket at a time. That means we leave out the issue when a family of seven comes to the boxoffice. And I also know that it would be a good aproach to rate each seat for dealing with output of the best combination of available seat and row.

And defining the 'best' seat, well I guess we all have different opinions. Some like in the middle as described abow, some like to sit in the back etc.

But in this case I am doing this plsql thing to prepare it for a simple webapplication. So:

1. You can only order one tichet at a time
2. If you leave out entering your seat/row request when asked, a procedure will produce a combination of the best seat/row of the available who are left.
3. The best combination is in the middle.
4. All tickets costs the same.

I am sorry if you have difficulties understanding the meaning of variables, names etc. Its in norwegian, but I will try to explain them along the way.

Tables:

-- CINEMA
CREATE TABLE KINO (
kinoid NUMBER(3) NOT NULL,
kinonavn VARCHAR2(40) NOT NULL, --cinema name
sted VARCHAR2(40) NOT NULL, -- city
PRIMARY KEY (kinoid)
);
-- showroom, the room in the cinema where the movie is played
CREATE TABLE SAL (
salid NUMBER(3) NOT NULL,
salnavn VARCHAR2(40) NOT NULL, -- room name
kinoid NUMBER(3) NOT NULL, -- cinema
PRIMARY KEY (salid, kinoid)
);
-- SEATS
CREATE TABLE SETE (
setenr NUMBER(4) NOT NULL, -- seat
radnr NUMBER(3) NOT NULL, -- row
salid NUMBER(3) NOT NULL, -- showroom
kinoid NUMBER(3) NOT NULL, -- cinema
PRIMARY KEY (setenr, radnr, salid, kinoid)
);
-- MOVIE
CREATE TABLE FILM (
filmid NUMBER(6) NOT NULL,
filmnavn VARCHAR2(40) NOT NULL, -- title
sensur NUMBER(2), -- censur (agelimit)
premieredato DATE, -- date for premiere
beskrivelse VARCHAR2(2000), -- description
regsissor VARCHAR2(50) NOT NULL, -- director
lengde NUMBER(3) NOT NULL, -- length
genre VARCHAR2(40) NOT NULL,
prodaar NUMBER(4) NOT NULL, -- produced year
distrubuertved VARCHAR2(50) NOT NULL, -- distribution
spraak VARCHAR2(40) NOT NULL, -- languages
PRIMARY KEY (filmid)
);
-- SHOW
CREATE TABLE VISNING (
pris NUMBER(3) NOT NULL, -- price
visningsdato DATE NOT NULL, -- show date
tidspunkt NUMBER(4) NOT NULL, -- time
salid NUMBER(3) NOT NULL, -- showroom
filmid NUMBER(6) NOT NULL, -- movie
kinoid NUMBER(3) NOT NULL, -- cinema
PRIMARY KEY (visningsdato, tidspunkt, salid, kinoid)
);
-- TICKET
CREATE TABLE BILLETT (
setenr NUMBER(4) NOT NULL, -- seat
salid NUMBER(3) NOT NULL, -- showroom
radnr NUMBER(3) NOT NULL, -- row
kinoid NUMBER(3) NOT NULL, -- cinema
bestillingsnr NUMBER(8) NOT NULL, -- ordernumber
tidspunkt NUMBER(4) NOT NULL, -- time
visningsdato DATE NOT NULL, -- show date
PRIMARY KEY (setenr, salid, radnr, kinoid, tidspunkt, visningsdato)
);

This is some of the tables I have, and are only those who are used getting the seats etc.

Package:
-- Seter=seats
CREATE OR REPLACE PACKAGE Seter is

-- Find a seat
PROCEDURE Finn (
var_film IN VARCHAR2, -- name of movie
var_sted IN VARCHAR2, -- city
var_kino IN VARCHAR2, -- name of cinema
var_rad OUT NUMBER, -- row
var_sete OUT NUMBER -- seat
);

end; --package Seter
/

CREATE OR REPLACE PACKAGE BODY Seter is

PROCEDURE Finn (
var_film IN VARCHAR2,
var_sted IN VARCHAR2,
var_kino IN VARCHAR2,
var_rad OUT NUMBER,
var_sete OUT NUMBER
)
IS
sete_max NUMBER;
sete_min NUMBER;
rad_max NUMBER;
rad_min NUMBER;
x_rad NUMBER;
x_sete NUMBER;

BEGIN

-- Finding max and min of Seats and rows which are available (Seats - Tickets)

select MAX(setenr), MAX(radnr), MIN(setenr), MIN(radnr)
INTO sete_max, rad_max, sete_min, rad_min
from kino k, sal s, sete a, visning v, film f
WHERE k.kinoid=s.kinoid and s.salid=v.salid and s.salid=a.salid and v.salid=a.salid and f.filmnavn=var_film
and k.sted=var_sted and k.kinonavn=var_kino and v.filmid=f.filmid
and NOT EXISTS (select setenr,salid,radnr,kinoid,tidspunkt,visningsdato from billett b WHERE b.setenr=a.setenr and b.salid=a.salid and b.radnr=a.radnr
and b.kinoid=a.kinoid and b.tidspunkt=v.tidspunkt and b.visningsdato=v.visningsdato);

-- begin loop
LOOP

-- choose a random number between max,min of seats and rows which are available

SELECT round(dbms_random.value(sete_min, sete_max),0)
into x_sete
from dual;

SELECT round(dbms_random.value(rad_min, rad_max),0)
into x_rad
from dual;

-- Getting the seat and row
select setenr, radnr
INTO var_sete, var_rad
from kino k, sal s, sete a, visning v, film f
WHERE k.kinoid=s.kinoid and s.salid=v.salid and s.salid=a.salid and v.salid=a.salid and f.filmnavn=var_film
and k.sted=var_sted and k.kinonavn=var_kino and v.filmid=f.filmid

-- Using output from random
and a.radnr=x_rad and a.setenr=x_sete

-- Eliminates seats/rows which exists in billett(ticket)
and NOT EXISTS (select setenr,salid,radnr,kinoid,tidspunkt,visningsdato from billett b WHERE b.setenr=a.setenr and b.salid=a.salid and b.radnr=a.radnr
and b.kinoid=a.kinoid and b.tidspunkt=v.tidspunkt and b.visningsdato=v.visningsdato);

-- If the query is valid and returns a row, exit the loop
exit when
var_sete >= 1 and var_rad >= 1;

end loop;

DBMS_OUTPUT.PUT_LINE('You have not requested desired seat or row. We will therefor provide you with one.');
DBMS_OUTPUT.PUT_LINE('-');
DBMS_OUTPUT.PUT_LINE('Following seat and row will be reserved: - Seat: '||var_sete||' Row: '||var_rad);

exception
when no_data_found then
DBMS_OUTPUT.PUT_LINE('An error occured..');
end; --Finn


end; --Package body Seter
/

But the procedure Finn (find) sometimes returns with no data, and I get the exception throwed at me. So I guess there are a better way of doing this.

PL/SQL example for use of this package:
Lets say I have allready entered info about city, cinema and date. All I need now is time, seat and row - but you leave the seat and row blank so the procedure Finn can find a combination.

Table output in SQLplus:
Sal TIDSPUNKT LEDIG_SETE RAD
--------------- ---------- ---------- ----------
Sal 3 1800 1 1
Sal 3 2 1
Sal 3 3 1
Sal 3 4 2
Sal 3 5 2
Sal 3 6 2

sal is showroom, tidpunkt means time, ledig_sete is seat and rad is row.

Now the plsql:

accept s_tid prompt 'Choose time:'
prompt
accept s_sete prompt 'Choose seat:'
prompt
accept s_rad prompt 'Choose row:'
prompt
set serveroutput on

declare
l_rad NUMBER;
l_sete NUMBER;
begin
l_rad:='&&s_rad';
l_sete:='&&s_sete';

if l_sete is null then
Seter.Finn('&&s_film','&&s_sted', '&&s_kino',l_rad, l_sete);
end if;
end;
/

There has to be a better way of doing this... If not, how can I assure that the select into statement do not return with no rows and triggers the no_data_found exception?

Thanks for all help, I know this maybe a bit much to ask for, but I am kinda stuck!