EPFL/ADB/Rapport

Un article de Le wiki de 2 noisettes - noisette.ch.

Project Rapport about the Ski Resort Guide of "les portes du soleil"

Advanced databases 2005 - 2006

LBD-EPFL

Authors (firstname.lastname@epfl.ch):

  • Benoit Perroud
  • David Wenger
  • Marius Erni

The project : The goal of the project is to implement an Information System that describes a ski resort of "les portes du soleil", and allows some user queries about the ski resort.


Sommaire

OO Model

OO Design

Concepts diagram

A high resolution picture of the ski resort OO model can be found here

Image:SkiRessortOO_medium5.png

Integrity constraints

These constraints were inserted in the definition of the types :

  • The generalization Paths -> Slopes, Lifts is a partition (a slope XOR a lift, but must be one of them)
  • The generalization Attraction -> Restaurant, SkiShop, Others is a partition (a restaurant XOR a skishop XOR something else, but must be one of them)
Justification : 
The modifier 'NOT INSTANTIABLE' applied on the super-type suits well for this purpose

These constraints were inserted as triggers :

  • MeteoConditions::date <= currentDate
  • Edge::fromCP should be different from Edge::toCP
  • Lift::openingHour < Lift::closingHour
  • snowheight >= 0
Justification : 
The triggers are the most powerful tools to check both on insert and on update on a table

These constraints were inserted in the constructor of the types :

  • The paths must be composed of adjacent edges
  • Lift::openingHour < Lift::closingHour
Justification : 
It is not possible to make a select from the table Edges in a trigger acting on the same table 
and it is not possible to make a trigger acting on a table that is of a sub-type of another type

Some integrity constraints could not be expressed in the diagram or in the code. They are given here :

  • Sum CrossingPoint::toEdge::percentage = 1, sum CrossingPoint::fromEdge::percentage = 1 (otherwise we loose skiers...)
  • waitingTime and averageTime are in minutes
Justification : 
It is not possible to make a select from the table Edges in a trigger acting on update 
of the same table and the units of the attributes waitingTime and averageTime were not given
to us in the XML definition files

Justification of the design

The main goal of our design is to make it as regular and as simple as possible to perform path queries. That’s why we see the CrossingPoint like a coordinate in space, and we introduce the notion of Edge, which is a line between a starting and an ending CrossingPoint. An edge is the unit we use to trace the paths. All the paths are composed of one or more edges.

The lifts and the slopes are both paths because they are both composed of edges. This fact provides us a really simple way to find shortest path as we will see in point 3.2.

The next choices has been taken during the design phases to achieve our goal:

  • An Edge is the oriented unit of length in our design. It is composed of a starting and an ending point.
  • Slope and lift have a generalized class : Path, which contains a list of edges to draw its way. It would not required to be a list, because we can rebuild it with a simple algorithm :
The starting point of a path is the point which is not the ending of another edge in the path.
The reverse for the ending point.

So we query the same table to compute the paths, and we can even add intermediate station to a lift.

  • We insert two lifts of the same telesiege in our schema, both in one direction. It is very useful to compute a path, because everything is regular, we can automatically go up and down with a telesiege.
  • We named "coordinates" the future spatial informations of a CrossingPoint, as we didn't know precisely which kind of data it is.
  • To compute efficiently the path computation, we need a temporary table which doesn’t figure out on the OO diagram. See 3.2 for more details.
  • The type we use in the ODMG description is not exactly the real Oracle type, but they are explicit enough to be implemented later.
  • We don't provide the getters/setters at the point 3, but we know that every attribute is encapsulated.
  • Depending of the nature of the given population, the percentage and the averageTime of an Edge would be the averageTime of the slope divided by the number of edge of the slope, the same idea for the percentage.

ODMG Description

class CrossingPoint ( extent TheCrossingPoints ) {
       attribut String name;
	relationship Set<Attraction> attractions
		inverse Attraction::location;
	relationship Set<Edge> toEdges
		invers Edge::fromCP;
	relationship Set<Edge> fromEdges
		invers Edge::toCP;
}
class Path ( extent ThePaths ) {
	attribut String name;
	attribut Boolean isOpenValue;
	relationship List<Edge> edges
		inverse Edge::path;
	function CrossingPoint getStartPoint();
	function CrossingPoint getEndPoint();
	function Integer getWeigth();
       function Boolean isOpen(givenDate TimeStamp);
       function Boolean isOpen();
	function String print();
}
class Edge ( extent TheEdges ) {
	attribut Real percentage IN range(0, 1);
	attribut Integer averageTime;
	relationship CrossingPoint fromCP
		inverse CrossingPoint::toEdges;
	relationship CrossingPoint toCP
		inverse CrossingPoint::fromEdges;
	relationship Path path
		inverse Path::edges;
	function String print() ;
} 
class Slope extends Path ( extent TheSlopes ) {
	attribut TOrientation orientation;
	attribut TDifficulty difficulty;
	function Boolean toBeMaintained(Date);
	function Integer getWeight();
	function Boolean isOpen(Date);
	function String print() ;
}
class Lift extends Path ( extent TheLifts ) {
	attribut Time openinigHour;
	attribut Time closingHour;
	attribut Integer waitTime;
	function Boolean isUpDown();
	function Integer getWeight();
	function Boolean isOpen(Hour);
	function Boolean isOpen();
	function String print();
}
typedef TTimes {
      complex attribute times list<TimeStamps>;
}
class Attraction ( extent TheAttractions ) {
	attribut String name;
       attribute TTimes openTimes;
       attribute TTimes closingTime;
	relationship CrossingPoint location
		inverse CrossingPoint::attractions;
	function String print();
} 
class Restaurant extends Attraction ( extent TheRestaurants ) {
       attribute String speciality;
       function String print();
}
typedef TServices {
       complex attribute services set<String>;
}
class SkiShop extends Attraction ( extent TheSkiShops ) {
       attribute TServices services;
       function String print();
}  

class MeteoCondition ( extent TheMeteoCondiditions ) {
	attribut Date date;
	attribut Integer snowHeight;
	attribut TSnowQuality snowQuality;
	relationship Slope slope inverse Slope::meteo;
}

OR Schema

Objects

CREATE OR REPLACE TYPE TMeteoCondition AS OBJECT (
	myDate TIMESTAMP,
	orientation CHAR(1),
	snowHeight INTEGER,
	snowQuality VARCHAR2(6)
);
CREATE OR REPLACE TYPE TCrossingPoint AS OBJECT (
	name VARCHAR(32),
	-- coordinate T_Coordinate,
	MEMBER FUNCTION print RETURN VARCHAR2
);
CREATE OR REPLACE TYPE TPath AS OBJECT (
	name VARCHAR2(32),
	isOpenValue CHAR(1),
	MEMBER FUNCTION getStartPoint RETURN REF TCrossingPoint,
	MEMBER FUNCTION getEndPoint RETURN REF TCrossingPoint,
	MEMBER FUNCTION getWeight RETURN INTEGER,
	MEMBER FUNCTION isOpen(givenDate TIMESTAMP) RETURN CHAR,
	MEMBER FUNCTION isOpen RETURN CHAR,
	MEMBER FUNCTION print RETURN VARCHAR2
) 
NOT INSTANTIABLE NOT FINAL;
CREATE OR REPLACE TYPE TEdge AS OBJECT (
	fromCP REF TCrossingPoint, 
	toCP REF TCrossingPoint, 
	percentage REAL, 
	averageTime INTEGER,
	path REF TPath,
	CONSTRUCTOR FUNCTION TEdge(fromCP REF TCrossingPoint, toCP REF TCrossingPoint, 
               percentage REAL, averageTime INTEGER, path REF TPath)
               RETURN SELF AS RESULT,
	MEMBER FUNCTION print RETURN VARCHAR2
);
CREATE OR REPLACE TYPE LEdges IS TABLE OF TEdge;
CREATE OR REPLACE TYPE TSlope UNDER TPath (
	orientation CHAR(1),
	difficulty VARCHAR2(5),
	MEMBER FUNCTION toBeMaintained(givenDate TIMESTAMP) RETURN CHAR,	
	OVERRIDING MEMBER FUNCTION getWeight RETURN INTEGER,	
	OVERRIDING MEMBER FUNCTION isOpen(givenDate TIMESTAMP) RETURN CHAR,
	OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2
) FINAL;
CREATE OR REPLACE TYPE TLift UNDER TPath (
	openingHour TIMESTAMP,
	closingHour TIMESTAMP,
	waitTime INTEGER,
	CONSTRUCTOR FUNCTION TLift(name VARCHAR2,isOpenValue CHAR,
               openingHour TIMESTAMP,closingHour TIMESTAMP,waitTime INTEGER)
		RETURN SELF AS RESULT,
	MEMBER FUNCTION isUpDown RETURN CHAR,
	OVERRIDING MEMBER FUNCTION getWeight RETURN INTEGER,
	OVERRIDING MEMBER FUNCTION isOpen(givenDate TIMESTAMP) RETURN CHAR,
	OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2
) FINAL; 
CREATE OR REPLACE TYPE LLifts IS TABLE OF TLift;
CREATE OR REPLACE TYPE TTimes AS VARRAY(5) OF TIMESTAMP;
CREATE OR REPLACE TYPE TServices AS VARRAY(5) OF VARCHAR2(255);
CREATE OR REPLACE TYPE TAttraction AS OBJECT (
	name VARCHAR(50),
	location REF TCrossingPoint,
	openTimes TTimes,
	closeTimes TTimes,
	MEMBER FUNCTION print RETURN VARCHAR2
) NOT INSTANTIABLE NOT FINAL;
CREATE OR REPLACE TYPE TRestaurant UNDER TAttraction (
	speciality VARCHAR2(255),
	OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2
);
CREATE OR REPLACE TYPE TSkiShop UNDER TAttraction (
	services TServices,
	OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2
);
CREATE OR REPLACE TYPE TOther UNDER TAttraction (
	type VARCHAR2(64),
	OVERRIDING MEMBER FUNCTION print RETURN VARCHAR2
);

Sequences

CREATE SEQUENCE PathSeq START WITH 1 INCREMENT BY 1 NOMAXVALUE NOCYCLE NOCACHE;

Tables

CREATE TABLE Edges OF TEdge;
CREATE TABLE CrossingPoints OF TCrossingPoint(
	CONSTRAINT ccps1 PRIMARY KEY (name)
);
CREATE TABLE Attractions OF TAttraction(
	CONSTRAINT cattractions1 PRIMARY KEY (name)
);
CREATE TABLE MeteoConditions OF TMeteoCondition (
	CONSTRAINT cmeteoconds1 PRIMARY KEY (myDate, orientation),
	CONSTRAINT cmeteoconds2 CHECK (orientation IN ('N', 'S', 'E', 'W')),
	CONSTRAINT cmeteoconds3 CHECK (snowQuality IN ('easy', 'hard', 'spring'))
);
CREATE TABLE Paths OF TPath (
	CONSTRAINT cpaths1 PRIMARY KEY (name),
	CONSTRAINT cpath2 CHECK (isOpenValue IN ('y', 'n'))
); 
-- NESTED TABLE edges STORE AS NestedEdges;

Methods

PROCEDURE computeDjikstraPath(uniqId INTEGER, fromCP REF TCrossingPoint, 
        toCP REF TCrossingPoint, type INTEGER, givenDate TIMESTAMP);
PROCEDURE computePath(theId INTEGER, fromCP REF TCrossingPoint, 
        toCP REF TCrossingPoint);
PROCEDURE computeDjikstraPath2(uniqId INTEGER, fromCP VARCHAR2, 
        toCP VARCHAR2, type INTEGER, givenDate TIMESTAMP);
PROCEDURE computePath2(uniqId INTEGER, fromCP VARCHAR2, toCP VARCHAR2);

Triggers

create or replace trigger meteoconditionsTrig
before insert or update 
on MeteoConditions
for each row
begin
	IF :new.mydate > SYSDATE THEN
		raise_application_error(-20000,'MeteoConditions: trying to insert a bad date !');
	END IF;
	if :new.snowHeight < 0 then
		:new.snowHeight := 0;
	end if;
	RETURN;
END;
create or replace  trigger edgesTrig
before insert or update 
on Edges
for each row
begin 
	IF :new.fromCP = :new.toCP THEN
		raise_application_error(-20000,'Edges: trying to insert a bad edge !');
	END IF;

	if :new.percentage is null then
		:new.percentage := 0;
	end if;
	if :new.averageTime is null then
		:new.averageTime := 1;
	end if;
	RETURN;
end;


Implementation

Models

In our implementation, near everything stands into the database. Most of the procedures and the functions are attached to an object type, but we have been limited by some weird aspects of PL/SQL, which constraint us to deviate sometime from the OO model.

The significant modifications which are not already been discussed in point 1.1.2 are presented below :

  • Inverse relation in OO

Every relations in ODMG have an inverse relation. First this one is not mandatory because we can rebuild the inverse relation from the other object, and moreover if we introduce the inverse relation we have to maintain it up to date, which is a fastidious work.

So we simply drop them.

  • Collections update

A collection in PL/SQL can be seen as a table with the keyword TALBE, so we can make some SELECT queries on it. But we can't do any UPDATE. To make an update, we have to iterate through every elements of the collection to find the element to update.

This problem encourages us to use a global table to perform search queries instead of returning collections.

  • We cannot perform a DML operation inside a query

When we build theoretically the project, we imagine queries like :

SELECT p.getShortestPath(toCrossingPoint) FROM CrossinPoints p

which should return the shortest path from the current crossing point to toCrossingPoint.

A problem appears because we try to store the intermediate path into a global table, to avoid the previous limitation with collection. But in a SELECT statement we cannot insert any other value in any other table !

That's the reason why we intorduce searchId and procedures to perform search queries.

  • Leak of boolean type in SQL

In the OO model we plan to have some member functions that return true or false. Although this type is useable in PL/SQL, we cannot use it into an SQL query. So we modify any boolean type in CHAR(1), which return 'y' for true and 'n' for false.

  • REF vs. SELF in an object body

Another limitation in PL/SQL is about REF and SELF :

A REF to an object doesn't permit dot navigation in PL/SQL, although SELF does. Moreover, SELF isn't a REF to the object, so we can't use SQL like :

SELECT ... WHERE ref_object = SELF

As we can't perform our algorithm with only one of them, we found way to get both available into the functions.

SELF is automatically present in a member function, but REF must be explicitly given.

One way is to add a parameter REF to its type and calling the function as the following :

SELECT o.f(REF(o)) FROM Objects o

Another way is to perform a query inside the body to get the REF, but we need to give all the primary key :

SELECT REF(o) INTO ref_o FROM Objects WHERE o.pk1 = var_pk1 AND o.pk2 = var_pk2 ...

So we can get a REF into the body, but it is heavy and unfriendly to deal with

Search methods

As the lifts and the slopes represent a oriented graph, we build our search methods on Djikstra algorithm.

It consist in iterating through the crossing point, from an initial one, identifying the next candidate for the loop. From the current candidate, we compute the weight to any reachable crossing point (which can be done using different methods, as presented below), and insert or update them in the list of candidates.

Our implementation of Dijkstra need a global table to store and manage the list of candidates. In this table, we add a searchId field, which permit to perform multiple search simultaneously. The application get a searchId from a sequence, and use it all the computation to distinguish its value from others.

CREATE TABLE pathComputation (
	cp REF TCrossingPoint,
	id INTEGER, 
	seen CHAR(1) DEFAULT 'n',
	weight INTEGER,
	predecessor REF TEdge,
	CONSTRAINT cpathComp CHECK (seen IN ('y', 'n')), 
	ordre INTEGER
);

We implement 3 kind of path :

  • the easiest,
  • the fastest,
  • the shortest.

The easiest path takes in account the difficult of the slope and the type of Lift : A green slope receives a weight of 2, a blue one 4, a red one 8 and a black one 16. A telesiege weights 1 and a teleski 3. It will then prefer telesiege and green or blue slope.

The fastest path is computed on the average time we make from a crossing point to another using a particular slope or lift.

And the closest path is based on the idea of spatial distance between 2 crossing points, but as these data aren't provided yet, any crossing point are far from 1 from each other. This kind of path count then the number of hopes from one crossing point to another.

Because of the DML restriction explained in the previous point, we build 2 procedures to perform path search :

  • computeDijkstra

This procedure fills in the table with the values provided by the algorithm. Its arguments are :

  • searchId,
  • the type of search to perform (1: easiest, 2: fastest, 3: shortest, and 4: reachable, but this is a small modification of fastest)
  • the initial crossing point
  • the final crossing point, which can be null. If it is, the procedure compute any easiest/fastest/shortest path from the initial crossing point.
  • a timestamp, which can also be null. If it isn't, it gives some temporal information to the algo.
  • computePath

This procedure computes the path from the initial crossing point to the final one. It build the path in reverse, for the end to the initial.

All our search queries are based on this main computeDijkstra procedure, which is implemented inside the database. It was very instructive to build such algo inside of the database, but we won't recommended this implementation to real applications, because now clients have in general enough power to perform this kind of computation, so this will reduce the load to the server, and the networks are fast enough to support the more traffic a heavier client performing itself the search query will generate.

Database population procedure

To populate the data base we proceed according to the OO schema provided on point 1. We try to be as straight forward as possible to only work once on each data item. The main steps of our procedure were:

  • populate all lifts (create corresponding Path -> Edges -> CrossingPoints)
  • populate all slopes (create corresponding Path -> Edges -> CrossingPoints)
  • update traffic values to edges
  • populate meteo conditions
  • populate restaurants

The procedure is the same for each of the mentioned points we start by inserting the data extracted from each xml file into a temporary database, which we’ll go trough one tuple after an other and insert all extracted values on the corresponding tables of our project. In the population script we provide a procedure for each of the mentioned points.

Map of "les portes du soleil"

This Graph represents “les portes du soleil” as it was given by the xml files.

Image:MapSkiRessort.png

Inserting the lifts (lifts.xml)

In our OO model we have a Path witch is either a lift or a slope. Each path is constructed out of Edges witch are connecting two and only two CrossingPoints. As provided in our model an Edge is associated only to one Path, this means that it is possible that we have several edges describing the same route, so to distinguish between them we need to know to which Path they are associated with. Because of these reasons we start to insert path information. After we created the path we have the function getCP(CPname) which gives us the reference of the CrossingPoints. If the CrossingPoint does not exist the function creates a new CrossingPoint. With all these information we can now insert the Edge. If we are inserting a ‘TS’ (télésiège) we insert also the way. In this case the lift name is concatenated whit ‘ down’ to distinguish between the up and down way.

Assumptions: 
* The waiting time for the way down is 1 minute for every ‘TS’
* At this point the percentage for the people flow information is missing and the default 
  value 0 is set

Inserting the slopes (slopes.xml)

We proceed the same way as for the lifts, the only difference is that a slopes have only a down way and the number of edges is not always 2 like it is for the lifts.

Assumptions: 
* At this point the percentage for the people flow information is missing and the default 
  value 0 is set
* We changed slope Grand-Conche(TK) in the slopes.xml file, because the data is not consistant 
  with the data provided in the points.xml file. The path of the slope is now E to F and F to Z.
  like provided in the poins.xml file.

Updating the edges with flow information of the peoples (points.xml)

The overall idea is to find which edge we have to update. Remember in our architecture it is possible to have several edges describing the same road. So from the points.xml file we can distinguish between 3 different types of connections.

1. The case of a lift We are in this case if the length of the exit name is longer than 3 characters. Normally we should find only one occurrence of an edge, and that is the edge we have to update. But because of wrong data in the XML file it is possible that we find no occurrence, in this condition we print a Warning message. This case occur for the middle station of the lift ‘TS Grand-Conche’.

2. The case of a slope We are in this case if the attribute slope does exist. The procedure is the same like for the lifts, but this time the path of the edge has to be of type TSlope.

3. The case of a unlabeled slope If the connection is neither explicitly a slope or a lift we treat the point as following. If there exists no edge which is satisfying the criterias a new path is inserted between the two CrossingPoints labeled ‘fromCP-toCP’ and a warning message is inserted. We inserted the paths from E’’-Z, M-N and N-L. If there exist exactly one occurrence the corresponding edge is updated. If there exist several edges we take by preference the edge labeled ‘fromCP-toCP’. If there's no such edge, and if there exists more than one edge with a real label (not generic name ‘X-Y’) we choose the one that is a slope. And if there are more than one slope, then the percentage is splitted equally between the corresponding edges. In this case a Warning message is displayed.

Assumptions:
* The slope ‘Grand-Conche (TK)’ is given as going from E to E’ to Z in the file slopes.xml. 
  Therefore we corrected the file points.xml so that the flow of people exiting point E and E’ 
  corresponds to this definition.
* We also adjusted the corresponding percentages such that the sum is always 100 %.
* We can not take the lift ‘TS Grand-Conche’ at the CrossingPoint F because as provided in by 
  the lifts.xml file there is no middle station at this CrossingPoint.
* We inserted the slopes E’’-Z, M-N and N-L which were not provided in the slopes.xml file.

Inserting the attractions (attractions.xml)

We proceeded by extracting the type of the attraction, then depending on it we extracted the other specific attributes and then inserted them as the corresponding user-defined SQL type.

Assumptions:
* We assumed that there is no more than five services for each Ski Shop and no more than five 
  opening and closing hour pairs for each Attractions
* We introduced a type TOther as subtype of TAttraction so that the types TRestaurant, TSkishop 
  and TOthers are a partition of TAttraction.

Inserting the meteo conditions (snow.xml)

We proceeded by extracting all the possible attributes and values (including ‘height’ and ‘change’ values). Then we computed the total height for each orientation by summing the values for each orientation in a dedicated global variable.

Assumptions:
* We assumed that if the height of the snow becomes lower than zero, this value is replaced by 
  zero and the following changes are computed from this zero value.

Application

As everything is in the database, the application is very light and very simple.

The database returns strings, which are printed into a frame.

It is composed of the main class, which call a GUI class, which call in turn one class for each query, and a class which manage the database queries

  • SkiResort.java contians the main function
  • GUI.java draw the application, and call Query[1234]
  • Query1.java draw everything needed for performing query1
  • Query23.java
  • Query4.java
  • DB.java handle the connection to the database and contains a procedure per query which are called from the Query[1234] class.

GUI also extends an Updatable Interface, which define a update(String) method, which will be called from DB to send back the results of the queries.

Usage

Starting with the jar file, we start the application as the following :

java -d64 -jar SkiResort.jar

Note : the -d64 options is needed in Inf3

You cannot download the JAR application due to licence restiction in the Oracle packages, but the source code of the Java files is available here.

Interface

When lauched, the application look like this :

Image:Adb-interfate.png

Every query has a set of options :

  • the strating point
  • a date
  • the type of path to compute
  • the type of attraction

and a button "Find" which simply lauch the query and print the result in the text area at the bottom.

Queries

Query 1

What are the slopes to treat for the following dates :

  • 03-DEC-00
  • 04-NOV-00
  • 05-NOV-00
  • 23-NOV-00
  • 24-NOV-00
  • 25-NOV-00

Note : if we don't have any information on the snow condition, we don't maintain the slope. As we only have information from november 2005, all of these queries will return : "No slopes to maintain."

We change the date to consider 2005, and the month of november :

  • 03-NOV-05
1. Mossettes, a slope which is  R , oriented W
2. Loquete, a slope which is  R , oriented W 
3. Crosets II (TS), a slope which is  R , oriented N 
4. Slade, a slope which is  R , oriented N
5. Chavanette, a slope which is  R , oriented W 
6. Pauvre-Conche, a slope which is  R , oriented E 
7. Grand-Conche (TS), a slope which is  B , oriented E 
8. Grand-Conche (TK), a slope which is  B , oriented E 
9. Ripaille, a slope which is  B , oriented W 
10. C4-Z, a slope which is  B , oriented E
11. B2-C, a slope which is B, oriented N 
12. D3-O, a slope which is R, oriented W 
13. D2-E, a slope which is B, oriented E 
14. A1-D, a slope which is B, oriented W 
15. G2-H, a slope which is  R , oriented N 
16. H3-I, a slope which is  R , oriented E 
17. E'-E, a slope which is B, oriented W 
18. E-O, a slope which is B, oriented E 
19. E-Z, a slope which is B, oriented E 
20. M-N, a slope which is B, oriented E 
21. N-L, a slope which is B, oriented E 
  • 04-NOV-05
No slopes to maintain.
  • 05-NOV-05
No slopes to maintain.
  • 23-NOV-05
1. Mossettes, a slope which is  R , oriented W 
2. Loquete, a slope which is  R , oriented W 
3. Crosets II (TS), a slope which is  R , oriented N 
4. Slade, a slope which is  R , oriented N 
5. Chavanette, a slope which is  R , oriented W 
6. Pauvre-Conche, a slope which is  R , oriented E 
7. Grand-Conche (TS), a slope which is  B , oriented E 
8. Grand-Conche (TK), a slope which is  B , oriented E 
9. Lechereuse, a slope which is  B , oriented S 
10. Ripaille, a slope which is  B , oriented W 
11. Traverses, a slope which is  R , oriented S 
12. C4-Z, a slope which is  B , oriented E 
13. B2-C, a slope which is B, oriented N 
14. D3-O, a slope which is R, oriented W 
15. D2-E, a slope which is B, oriented E 
16. A1-D, a slope which is B, oriented W 
17. G2-H, a slope which is  R , oriented N 
18. G3-F, a slope which is R, oriented S 
19. K1-H, a slope which is  B , oriented S 
20. H3-I, a slope which is  R , oriented E 
21. F4-I, a slope which is B, oriented S 
22. E'-E, a slope which is B, oriented W 
23. E-O, a slope which is B, oriented E 
24. E-Z, a slope which is B, oriented E 
25. M-N, a slope which is B, oriented E 
26. N-L, a slope which is B, oriented E 
  • 24-NOV-05
1. Mossettes, a slope which is  R , oriented W 
2. Loquete, a slope which is  R , oriented W 
3. Chavanette, a slope which is  R , oriented W 
4. Pauvre-Conche, a slope which is  R , oriented E 
5. Grand-Conche (TS), a slope which is  B , oriented E 
6. Grand-Conche (TK), a slope which is  B , oriented E 
7. Lechereuse, a slope which is  B , oriented S 
8. Ripaille, a slope which is  B , oriented W 
9. Traverses, a slope which is  R , oriented S 
10. C4-Z, a slope which is  B , oriented E 
11. D3-O, a slope which is R, oriented W 
12. D2-E, a slope which is B, oriented E 
13. A1-D, a slope which is B, oriented W 
14. G3-F, a slope which is R, oriented S 
15. K1-H, a slope which is  B , oriented S 
16. H3-I, a slope which is  R , oriented E 
17. F4-I, a slope which is B, oriented S 
18. E'-E, a slope which is B, oriented W 
19. E-O, a slope which is B, oriented E 
20. E-Z, a slope which is B, oriented E 
21. M-N, a slope which is B, oriented E 
22. N-L, a slope which is B, oriented E 
  • 25-NOV-05
1. Mossettes, a slope which is  R , oriented W 
2. Loquete, a slope which is  R , oriented W 
3. Chavanette, a slope which is  R , oriented W 
4. Pauvre-Conche, a slope which is  R , oriented E 
5. Grand-Conche (TS), a slope which is  B , oriented E 
6. Grand-Conche (TK), a slope which is  B , oriented E 
7. Lechereuse, a slope which is  B , oriented S 
8. Ripaille, a slope which is  B , oriented W 
9. Traverses, a slope which is  R , oriented S 
10. C4-Z, a slope which is  B , oriented E 
11. D3-O, a slope which is R, oriented W 
12. D2-E, a slope which is B, oriented E 
13. A1-D, a slope which is B, oriented W 
14. G3-F, a slope which is R, oriented S 
15. K1-H, a slope which is  B , oriented S 
16. H3-I, a slope which is  R , oriented E 
17. F4-I, a slope which is B, oriented S 
18. E'-E, a slope which is B, oriented W 
19. E-O, a slope which is B, oriented E 
20. E-Z, a slope which is B, oriented E 
21. M-N, a slope which is B, oriented E 
22. N-L, a slope which is B, oriented E

Query 2

What is the easiest way to reach a restaurant if the skier is on the crossing R ? F ?

The easiest path to a 'restaurant' from 'R' is : 
1. At crossingpoint M, reachable through the following path : 
  1. From CrossingPoint R
  2. To CrossingPoint M using TK Lechereuse 
 you will find the following restaurant : 
 - La rose des Alpes
2. At crossingpoint L, reachable through the following path : 
  1. From CrossingPoint R
  2. To CrossingPoint J using R1-J
  3. To CrossingPoint L using TK Traverses
 you will find the following restaurant : 
 - Croix-de-Culet 
The easiest path to a 'restaurant' from 'F' is : 
1. At crossingpoint Z, reachable through the following path : 
  1. From CrossingPoint F
  2. To CrossingPoint Z using Grand-Conche (TS)
 you will find the following restaurant : 
 - Le Vieux Chalet
 - Chez Gabi

Query 3

What is the closest ski-shop to rent a new pair of skis, if the skiers is on the crossing F ? A ?


The easiest path to a 'ski shop' from 'F' is : 
1. At crossingpoint Z, reachable through the following path : 
  1. From CrossingPoint F
  2. To CrossingPoint Z using Grand-Conche (TS)
 you will find the following ski shop : 
 - Borgeat sports
The easiest path to a 'ski shop' from 'A' is : 
1. At crossingpoint Z, reachable through the following path : 
  1. From CrossingPoint A
  2. To CrossingPoint Z using TS Mossettes down
 you will find the following ski shop : 
 - Borgeat sports

Query 4

At 16:35 the skier is on the crossing G or E, is there is a ski lift that she/he can reach before it is closed ?

The reachable lifts from G if it is 16:35 : 
 - TK Ripaille closing at 17:30 at crossing point CrossingPoint I
 - TS Mossettes closing at 17:00 at crossing point CrossingPoint Z
 - TS Mossettes down closing at 17:00 at crossing point CrossingPoint A
 - TK Crosets I closing at 17:00 at crossing point CrossingPoint Z
 - TS Crosets II closing at 17:00 at crossing point CrossingPoint Z
 - TS Crosets II down closing at 17:00 at crossing point CrossingPoint N
 - TS Chavanette closing at 17:00 at crossing point CrossingPoint H
 - TS Chavanette down closing at 17:00 at crossing point CrossingPoint T
 - TS Pauvre-Conche closing at 17:15 at crossing point CrossingPoint I
 - TS Pauvre-Conche down closing at 17:15 at crossing point CrossingPoint G
 - TS Grand-Conche closing at 17:30 at crossing point CrossingPoint Z
 - TS Grand-Conche down closing at 17:30 at crossing point CrossingPoint E
 - TK Cubore closing at 17:00 at crossing point CrossingPoint O
 - TK Grand-Conche closing at 17:00 at crossing point CrossingPoint Z
 - TK Lechereuse  closing at 17:15 at crossing point CrossingPoint R
 - TK Traverses closing at 17:30 at crossing point CrossingPoint J
 - TK Lois closing at 17:30 at crossing point CrossingPoint P
The reachable lifts from E if it is 16:35 : 
 - TK Ripaille closing at 17:30 at crossing point CrossingPoint I
 - TS Mossettes closing at 17:00 at crossing point CrossingPoint Z
 - TS Mossettes down closing at 17:00 at crossing point CrossingPoint A
 - TK Crosets I closing at 17:00 at crossing point CrossingPoint Z
 - TS Crosets II closing at 17:00 at crossing point CrossingPoint Z
 - TS Crosets II down closing at 17:00 at crossing point CrossingPoint N
 - TS Pauvre-Conche closing at 17:15 at crossing point CrossingPoint I
 - TS Pauvre-Conche down closing at 17:15 at crossing point CrossingPoint G
 - TS Grand-Conche closing at 17:30 at crossing point CrossingPoint Z
 - TS Grand-Conche down closing at 17:30 at crossing point CrossingPoint E
 - TK Cubore closing at 17:00 at crossing point CrossingPoint O
 - TK Grand-Conche closing at 17:00 at crossing point CrossingPoint Z
 - TK Lechereuse  closing at 17:15 at crossing point CrossingPoint R
 - TK Traverses closing at 17:30 at crossing point CrossingPoint J
 - TK Lois closing at 17:30 at crossing point CrossingPoint P

Spatio Temporal improvements

For the second part of the project, we need to add spatialities and temporalities to the schema, then implement the spatial part and finally run some queries on it.

Concepts diagram

Image:SkiRessortOO_tmp_spatial_me5.png

Implementation

CREATE TABLE EDGESSPATIAL (
	id REF TEdge,
	shape MDSYS.SDO_GEOMETRY
);
CREATE TABLE CROSSINGPOINTSPATIAL (
	id REF TCrossingPoint,
	shape MDSYS.SDO_GEOMETRY
);

Rermarks

Oracle spatial has 2 type of spatial methods : operators or functions. The first uses a spacial indexs, while the latter don't.

Here appears a small trick : we canno't add index on objects attributs, so we need to create simple relationnal tables to run operators on them. That's the reason why we built two separate tables to introduce spatiality on our design.

We also run some test bed to compare execution of spatial queries with or without index. We strangely remark that the queries without index run 5 times faster !

See Annex E to view the complete file with spatiality (inset, index creation and queries)

Conclusion

We have had a lot of interest to brainstrom how to implement our objects and procedures to put every thing in the database. We found this very useful and learnt important concepts in ODB.

We can also test the concept of light client, fat server, which could well scale some topologies.

Annexes

A Objects bodies

B Methods bodies

C Population script

D Application source code

E Spatial script