team5 final

8/13/2019 Team5 Final

1/23

LIGHTSPEED A BOOKINGAPPLICATION FOR TRAVEL AGENCIES

Steffen Breedijk, Ellert Colijn, Amelia Ibrahim, Marille van de Pol

Project Databases 2004-2005, University of Amsterdam

[email protected], [email protected], [email protected],[email protected]

This document presents an overview of the design and development process of

the LIGHTSPEED booking application for travel agencies. A brief review of

database history and design on databases is provided followed by the

conceptual and physical implementation of the database. The GUI for the

application has been written in JAVA with JBuilder.

IntroductionToday, database management systems (DBMS) are used in almost every companyand organization in society. In the highly dynamic and competitive travel industrythere is need for ICT solutions. The same requirements follow for the travel agencyin this project, but at this agency its only possible to book by phone or at the office.Because of the recent competition of on-line booking systems on the Internet, theautomation system designed for this agency should be flexible and cost effective.The goal of this project is to design, build and implement a centralized relationaldatabase application for a travel agency. The system must be able store and retrieve

information related to different pre-defined holiday packages.De database design and development for this project takes place at two levels: theconceptual level and the physical level. The first part of this paper will discuss thedesign at conceptual level. In this section, the purpose of the system and the requiredfunctionalities will be described. Some background information about thedevelopment and architecture of databases in general is indispensable. The second

part of this paper describes the physical level, how the conceptual model of thesystem has been implemented.


2/23

BOOK WITHLIGHTSPEED2

Part One Conceptual LevelArchitecture and DevelopmentArchitectureThe development of databases has evolved constantly in the past fifty years. Themost simple en oldest system is a flat-file system. The database managementsystems evolved from this base system is relational, object-oriented or object-relational. The underlying architecture for these systems can be divided in threetypes. The first is a centralized database system, which is a stand-alone system. The

second type is a distributed database system. This system is network-based andmakes it possible to have geographical diffusion of sites. Third system type is afederated database system. This system is also network-based and very useful tosupport the heterogeneity and autonomy of sites [2]. The environment of thedatabase also has evolved from a mainframe-based to a client server environment.The client server makes it possible to operate in a network environment. In this

project it was obliged to design and develop a centralized relational database. Anetwork environment is present at the travel agency. To support this there is chosenfor client server architecture, because of the disadvantages a file server has. Thespecific type is database server architecture. Because of the early live stage of theapplication, the architecture is two-tier. There are not so many users at this momentand to make application server is too complex at this stage of the development

process. The first tier is the client with the tasks, user interface and main business

and processing logic. The second tier is the database server with the tasks of serverside validation en database access.

Recent DevelopmentsThe latest development in the area of database research is the emerging ofCollaborative Networks (CN). By the easily available and accessible communicationnetworks worldwide the different forms of web-based collaboration are configuredand growing. Web-based mechanisms are used to deal with the need to operatewithin distributed and collaborative environments. Building new support systems ontop of the web for CN is a new challenge for database researchers. Some examplesof CNs are Virtual Organizations (VO), Virtual Communities (VC) and VirtualLaboratories (VL). The design and development of VOs, VCs and VLs is still an

open area which researchers are trying to tackle [4].

Conceptual designThe conceptual design represents informal requirements of the application in termsof a conceptual schema that refers to a conceptual data model. In this section theusers and the requirements of the database will be discussed first. Second, the ERdata model, the constraints, keys and functional dependencies will be explained.Third, the overall logical structure from the database is shown graphically by means


3/23

Breedijk, Colijn, Ibrahim, Van De Pol 3

of an ER diagram. A relational schema diagram from the database is the fourth topicin this section. Finally, the security and authorization issues concerning the databasewill be discussed.

UsersThe users of the DBMS are the employees of the travel agency. These are calledclerks and their manager.

RequirementsThe basis of the database consists of a module, called a service. Clients can book a

package, a single service, or a combination of services. Packages are predefinedcombinations of several services. It is possible to add, delete and modify the servicesmodules at any time.

The functional requirements that follow from the above are the following. Theremust be a functional separation between booking and creation of the associated

packages.While making a booking a Clerk/manager should have a view of all the predefined

packages in the database. He must be able to interact with the application in order tocreate a deal for the customer, based on the service selection or a package. Theselected packages for a deal are based on selected properties, like destination,accommodation type etc. It must be possible to book separately an accommodation,transportation and activity.

The manager is the one who predefines the packages which can be booked bycustomers. He is the one to moderate the database; to add, update, and delete holiday

packages and services to the database system. He is authorized to add a packagewith discount in the system

ER data modelThe following entity sets and relationship sets can be identified in the E-R datamodel for the travel agency. The entity sets in the database are: customer, deal,

package, service, accommodation, transportation, activity. The relationship sets inthe database are: has, part of, delivered on.

ConstraintsThe ER enterprise schema has constraints to which the contents of the database mustconform. These are cardinalities and participation constraints. In the schema for thetravel agency most cardinalities are many to many, except the relation betweencustomer and deal. This relation is a one to many. This means that one customer canhave zero or more deals, but a deal can only belong to one customer. Every packageentity is related to at least one service trough the part of relationship. This alsogoes for the deal. Every deal is related to at least one service also through a part of


4/23


relationship. Therefore the participation of package and deal in both therelationships sets part of is total.

Primary and Foreign keysTo specify how the entities in the database can be distinguished from each other, thedifference among them is expressed by their attributes. A key makes it possible toidentify a set of attributes that adequate distinguish entities from each other. Thesame is true for the relationships in the database. The most important keys are the

primary key and the foreign key. The primary key is the unique identifying attributeof an entity. In the E-R diagram the attributes that are a primary key are underlined.A foreign key is an attribute of a relationship that is the primary key of anotherentity. The foreign keys will be shown in the database schema in the next paragraph.

The attribute price from the entity package and the attribute total price from theentity deal are derived attributes. They have dashed ellipses.

Functional DependenciesThe set of relation schemas makes it possible to store information withoutunnecessary redundancy and to retrieve information easily. To avoid anomalies theschemas must be in an appropriate normal form. The process of normalizationdecomposes the relations according to functional dependencies. In this database therelation schemas are in fourth normal form. Each record of the file consists of onemain key, and some mutually independent field values. This means that the fieldvalues are not multi-valued, not partially dependent and not transitively dependenton the primary key. For the fifth normal form there must be no loss of

decomposition.


5/23


Figure 1 ER Diagram


6/23


Relational DatabaseThe ER diagram above is the starting point for making a relational database for thetravel agency. Based on the entities and relationships in the ER diagram a databaseschema can be made. This database schema gives a logical structure of the database.One of the tables of the database schema is shown. The other tables can be found inthe appendix.

CREATE TABLE àccomodation`( àcc_id` int(11) NOT NULL auto_increment, àcc_name` varchar(64) NOT NULL default '', àcc_location` varchar(64) NOT NULL default '', àcc_type` varchar(64) NOT NULL default '', àcc_class` int(11) NOT NULL default '0',

PRIMARY KEY (àcc_id`))

The database schema with the primary keys and foreign keys is shown by thediagram below.

Figure 2: schema diagram


7/23


Security and AuthorizationThe client server architecture for the database has a network environment. For thisnetwork environment there are security issues on the levels of the network,operating system, web server, database, and data communication. First, at thenetwork level important security issues consists of create an account with a username and a password for each user, make sure the database server is away fromother business systems, minimize the sharing of hard disks among the servers.Second, the operating system level is discussed. The most important security issueson this level involve unauthorized activity and unauthorized access. This can beaddressed by using an Intrusion Detection System and disabling non-requiredservices. Third, by the security issues of the web server attention must be given hereto the following: restrict the number of users on the web server, restrict the access,

and remove unneeded programs that load automatically when setting up the server.The fourth level is the database. The security issues on this level are all involvedwith the restriction of the access and manipulation rights of the users of thedatabase. The most important are logins and passwords checks, creating user rolesand views. On the level of the data communication the security issues exists ofencryption and decryption processes, public and private keys, digital signature andvirtual private network (VPN) [1]. Because of the nature of this database project it isnot realistic to implement all the security and authorization issues described. Themost important thing for this project was creating views to protect the consistency ofthe database. Users can be given authorization on views, without being given anyauthorization on the relations used in the view definition. Ability of views to hidedata serves both to simplify usage of the system and to enhance security by allowingusers access only to data they need for their job. The other security issues must be

seen as a guideline for further development in the future [3].


8/23


Part Two ImplementationDevelopmentDevelopment environmentDue to the relative high learning curve for the Eclipse IDE, Borland JBuilder has

been used for the implementation of the client.

Database choiceInitially, a MaxDB database was provided for the implementation of the Travel

Agency project. Due to memory limitations of the server, connectivity problems andserver stability the database schemas have been transferred to a MySQL 4.xdatabase. Because this version does not support views MySQL 5.0 has been set upon a separate server. To ensure compatibility with both MySQL versions the viewshave not been actively used in the client . They are there just to demonstrate theusefulness of views for easy access of the data. Despite the fact that views areofficially 'read-only', it can be shown that updates on a view are permitted in somecircumstances.

Java codeTo make an application interface the following JAVA code is developed. Here aresome structures of the JAVA code.

Initiate database

Dat abase database1 = new Dat abase( ) ;Connection database1 = DriverManager.getConnection

("jdbc:mysql://localhost/team5 user=root password=")

Next sample uses the data from the accommodation table in our MySQL-database.

Creating a dataset for:accomodation.setQuery (new

com.borland.dx.sql.dataset.QueryDescriptor(

database1, "SELECT

accomodation.acc_id,accomodation.acc_name,accomodation.acc_loc

ation,accomodation.acc_type,acco" +

"modation.acc_class FROM team5.accomodation", null,

true, Load.ALL));

ExplanationWe created a dataset for java. We make a selection of de items: acc_id , acc_name,acc_location en acc_type. This dataset is called acco.For all data in the database it is possible to create a dataset like the above.


9/23


The graphical user interface

On the screen we create a text field:

InitializeJ dbText Fi el d j dbText Fi el d16 = new J dbText Fi el d( ) ;

jdbTextField16.setFont(new java.awt.Font("Dialog", Font.PLAIN,

14));

\font and font size are defined.

jdbTextField16.setPreferredSize(new Dimension(100, 25));

\size of the textfield is set

jdbTextField16.setText("Prijs");\a name is given to the field for faster and easier refering.

jdbTextField16.setColumnName("service_price");

\referring to the database column name.

jdbTextField16.setDataSet(Accomodation);

/using the dataset accommodation.

Database updating using a jdbc toolbar:

J dbNavTool Bar j dbNavTool Bar 1 = new J dbNavTool Bar ( ) ;

j dbNavTool Bar 1. set Dat aSet ( accomodat i on) ;

uses internal jdbNavToolbar class for updating and inserting data.

Graphical user interfaceMost users interact with databases using form interfaces with graphical interactionfacilities. Such facilities are provided by GUI (Graphical user interfaces). GUI is a

program interface that uses a computer's graphics capabilities to make the programeasier to use. Graphical interfaces use a pointing device to select objects, includingicons, menus, text boxes, etc. A GUI includes standard formats for representing textand graphics. With use of form interfaces, which provide mechanisms to check thecorrectness of user input, and automatically fills in fields we can connect a databaseto such interfaces. Forms and GUI allow users to perform some operations (enterdata, search, retrieve etc.) or enter values that complete pre-defined queries; GUI

provide an easy-to-use way to interact with a database system.While designing an interface it is very important to design an interface which is userfriendly for the end users, and is efficient in use in order to accomplish the taskwhich the user has to employ. In order to make an interface user friendly one shouldnormally take a usability test with the end users. The usability is the quantitative andqualitative measure to which a user interface can achieve specified goals witheffectiveness, efficiency and satisfaction in a specified context of use [1]. For this


10/23


assignment we do not have enough time to evaluate the interface nor to do ausability test. Keeping in mind that the interface should be effective en efficient towork with we designed the further shown interface for the managers and the clerks.For designing an interface for this assignment we have chosen for an interface whichmakes it easy for a clerk to make bookings for the customers. We also designedsome tabs for the manager who can easily moderate the database by using the GUI.In the next section we will give some screenshots of the designed GUI with ascenario.

Results: Scenarios and ScreenshotsAdministration-related scenarios

Scenario 1: Adding facilities to the database

Manager wants to add a new Hotel to the database. The manager selectsaccommodation tab and adds the name, place, type and class. Finally he submits theform.


11/23


Scenario 2: Delivering services on a facility

Manager wants to specify when transportation is available for booking.The manager selects a facility. Then he selects a transportation type to offer servicesfor. For example, a service can be added for a certain period and costs. Theaccommodations can have multiple services offered on them for different periodsusing different pricing strategies.


12/23


Scenario 3: Creating a package

Manager wants to create a new package and provide a discount on the servicesincluded. The manager goes to the package tab and creates a new package. Thenservices can be selected and added to the package. Finally a discount can be set.


13/23


Clerk interaction with the customer

Scenario 4: Making a booking

The customer calls the travel agency and will be helped by the clerk who will helpthe customer to select a predefined package or make a customized deal.Suppose the customer wants to book a trip to Spain, wants to reside in a luxurioushotel and likes to see as much from the city as possible.

The clerk goes to the customer/booking tab where all the packages are shown in alist. By selecting location or class from the dropdown field the package list will beshow all predefined packages matching these requirements. By selecting a packagedetails on the services will be shown in the list at the bottom of the form.

When the customer wants to buy the package the services of the package will betransferred to the deal tab. In this view it will be possible to make some finaladjustments by adding more service or removing them.


14/23



15/23


ConclusionThis project description illustrate that is it possible to implement a relationalcentralized database application for a travel agency with JAVA. The following stepshave been taken to develop and design the database. The first step consists ofmaking the conceptual design. The conceptual level describes what the purpose ofthe system must be and the required functionalities. Following from therequirements and the constraints an ER diagram represented a graphically logicalstructure for the database. The second step is conversion of the conceptual model ofthe system into a physical structure, this is development phase. The ER diagram wasthe starting point for making a relational database. The tables of the databaseschema are the structure for the database. The database schemas have beentransferred to a MySQL 4.x database. To make an application interface JAVA code

has been developed.Finally, to make it possible for a clerk and manager to communicate with thedatabase we designed a GUI.. Through the GUI it is possible for the clerk to make

bookings for a customer. The GUI enables the manager to easily moderate thedatabase. The GUI will provide the clerks and managers an efficient and effectiveworking environment.

RecommendationsDatabaseThough the MySQL database is free of charge for educational purposes in some

cases licenses are required for use in a business environment. For professional usethe application could benefit from more mature database features like views orstored procedures which MySQL does not offer in their stable (4.x) branch.Therefore, either Postgres (completely free) or Oracle (commercial) arerecommended for their functionality and stability for application in a businessenvironment.

Database designFor each entity a limited amount of attributes has been implemented. In stead oftrying to be overly complete on e.g. customer attributes, focus has mainly been onthe functionality. In future releases of the application the number of attributes can beeasily extended.


16/23


Literature1. Afsarmanesh, H. Slides Lecture 2 Project Databases2. Afsarmanesh, H. Slides Lecture 3.2 Project Databases3. Silberschatz A., Korth H.F., Sudarshan S. Database System Concepts 4thed.McGraw-Hill 20024. Guevara-Masis V, Afsarmanesh H, Hertzberger L.O., Ontology-based automaticdata structure generation for collaborative networks. IFIP 2004.

Otherwww.mysql.com

www.oracle.comwww.postgres.orgwww.borland.com


17/23


Appendix A: Database Definition## St r uctur e f or t he àccomodati on` t abl e :#

CREATE TABLE àccomodat i on` ( àcc_i d` i nt ( 11) NOT NULL auto_i ncrement , àcc_name` varchar ( 64) NOT NULL def aul t ' ' , àcc_l ocat i on var char( 64) NOT NULL def aul t ' ' , àcc_t ype` var char( 64) NOT NULL def aul t ' ' , àcc_cl ass` i nt ( 11) NOT NULL def aul t ' 0' , PRI MARY KEY ( àcc_ i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

#

# Str ucture f or the àcti vi t y` t abl e :#

CREATE TABLE àct i vi t y` ( àct _i d` i nt ( 11) NOT NULL aut o_i ncrement , àct_descr i pt i on` var char( 64) defaul t NULL, àct_l ocat i on` var char( 64) NOT NULL def aul t ' 0' , àct _cont act` var char ( 64) def aul t NULL, àct _t ype` var char( 64) def aul t NULL, PRI MARY KEY ( àct _i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r ucture f or t he `customer ` t abl e :#

CREATE TABLE ` cust omer ` ( `cust _i d` i nt ( 11) NOT NULL aut o_i ncrement , `cust _name` var char ( 64) NOT NULL def aul t ' ' , `cust _address` varchar ( 64) NOT NULL def aul t ' ' , `cust _bi r t hdate` date NOT NULL def aul t ' 0000-00- 00' , `cust _gender` char ( 1) NOT NULL def aul t ' ' , PRI MARY KEY ( `cust _i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e for t he `deal ` t abl e :#

CREATE TABLE `deal ` ( `deal _i d` i nt ( 11) NOT NULL aut o_i ncrement , `deal _book_date` dat e def aul t NULL, `deal _empl oyee var char ( 64) def aul t NULL, `deal _name` var char ( 64) def aul t NULL, `deal _cust _i d` i nt ( 11) def aul t NULL, PRI MARY KEY ( `deal _i d`) , KEY `deal _cust _i d` ( `deal _cust _i d`) , CONSTRAI NT `deal _i bf k_1` FOREI GN KEY (`deal _cust _i d`) REFERENCES`cust omer ` ( `cust _i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e f or t he `servi ce` t abl e :#


18/23


CREATE TABLE `s ervi ce` ( `ser vi ce_i d` i nt ( 11) NOT NULL aut o_i ncr ement , `servi ce_descri pt i on varchar ( 64) NOT NULL defaul t ' ' , `ser vi ce_st art ` t i mestamp NOT NULL defaul t ' 0000-00- 00 00: 00: 00' , `servi ce_end` ti mest amp NOT NULL def aul t ' 0000- 00- 00 00: 00: 00' , `ser vi ce_pr i ce` f l oat ( 9, 2) NOT NULL def aul t ' 0. 00' , PRI MARY KEY ( `servi ce_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r ucture f or t he `deal _ser vi ces` tabl e :#

CREATE TABLE `deal _servi ces` ( `deal _i d` i nt ( 11) def aul t NULL, `ser vi ce_i d` i nt ( 11) def aul t NULL, KEY `SERVI CE_I D ( `servi ce_i d`), KEY `DEAL_I D ( `deal _i d`) , KEY `ser vi ce_deal _i bf k_1` ( `ser vi ce_i d`) , KEY `ser vi ce_deal _i bf k_2` ( `deal _i d`) , CONSTRAI NT `deal _servi ces_i bf k_1` FOREI GN KEY ( `servi ce_i d`)REFERENCES `servi ce` ( `servi ce_i d`) , CONSTRAI NT `deal _servi ces_ i bf k_2` FOREI GN KEY ( `deal _i d`) REFERENCES`deal ` (`deal _i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e f or t he `package` t abl e :#

CREATE TABLE `package` ( `package_i d` i nt ( 11) NOT NULL auto_i ncrement ,

`package_name` varchar( 64) NOT NULL def aul t ' ' , `package_di scount` f l oat( 31, 30) def aul t NULL, `package_pr i ce` f l oat ( 31, 30) def aul t NULL, PRI MARY KEY ( `package_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r ucture f or t he `package_servi ces` t abl e :#

CREATE TABLE `package_servi ces` ( `ser vi ce_i d` i nt ( 11) def aul t NULL, `package_i d` i nt ( 11) def aul t NULL, KEY `SERVI CE_I D ( `servi ce_i d`), KEY `PACKAGE_I D ( `package_i d`) , KEY `package_servi ces_i bf k_1` ( `servi ce_i d`) ,

KEY `package_servi ces_i bfk_2` ( `package_i d`) , CONSTRAI NT `package_servi ces_i bf k_1` FOREI GN KEY ( `servi ce_i d`)REFERENCES `servi ce` ( `servi ce_i d`) , CONSTRAI NT `package_servi ces_ i bf k_2` FOREI GN KEY ( `package_i d`)REFERENCES `package ( `package_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e f or t he `servi ce_on_accomodat i on` t abl e :#

CREATE TABLE `s ervi ce_on_accomodat i on` (


19/23


`s_on_acc_ser vi ce_i d` i nt ( 11) NOT NULL def aul t ' 0' , `s_on_acc_acc_i d` i nt ( 11) def aul t NULL, PRI MARY KEY ( `s_on_acc_servi ce_i d`) , KEY `s_on_acc_ser vi ce_i d` ( `s_on_acc_ser vi ce_i d`) , KEY `s_on_acc_acc_i d` ( `s_ on_acc_acc_i d`) , CONSTRAI NT `s ervi ce_on_accomodat i on_i bf k_3` FOREI GN KEY( `s_ on_acc_servi ce_i d`) REFERENCES `ser vi ce` ( `servi ce_i d`) ON DELETECASCADE, CONSTRAI NT `s ervi ce_on_accomodat i on_i bf k_2` FOREI GN KEY( `s_on_acc_acc_ i d`) REFERENCES àccomodati on ( àcc_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e for t he `servi ce_on_acti vi t y` t abl e :#

CREATE TABLE `servi ce_on_act i vi t y` ( `s_on_act _servi ce_i d` i nt ( 11) NOT NULL def aul t ' 0' , `s_on_act _act _i d` i nt ( 11) def aul t NULL, PRI MARY KEY ( `s_on_act_ servi ce_i d`) , KEY `s_on_act _act _i d` ( `s_on_act _act _i d`) , KEY `s_on_act _servi ce_i d` ( `s_on_act _servi ce_i d`) , CONSTRAI NT `servi ce_on_act i vi t y_i bf k_2` FOREI GN KEY( `s_ on_act_ser vi ce_i d`) REFERENCES `ser vi ce` ( `servi ce_i d`) ON DELETECASCADE, CONSTRAI NT `servi ce_on_act i vi t y_i bf k_1` FOREI GN KEY( `s_on_act _act_i d`) REFERENCES àct i vi t y` ( àct_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r uctur e for t he `tr anspor t ati on` t abl e :#

CREATE TABLE `t r ansport ati on ( `t r ans_i d` i nt( 11) NOT NULL aut o_i ncrement , `t r ans_t o` var char( 64) NOT NULL def aul t ' ' , `t r ans_t ype` var char( 64) NOT NULL def aul t ' ' , `t r ans_f r om varchar ( 64) NOT NULL def aul t ' ' , `t r ans_code` var char( 64) NOT NULL def aul t ' ' , PRI MARY KEY ( `t r ans_ i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## St r ucture for t he `servi ce_on_t r ansport at i on` tabl e :#

CREATE TABLE `servi ce_on_t r anspor t at i on` ( `s_on_t r ans_servi ce_i d` i nt ( 11) NOT NULL def aul t ' 0' , `s_on_t r ans_t rans_i d` i nt ( 11) defaul t NULL,

PRI MARY KEY ( `s_on_t rans_servi ce_i d`) , KEY `s_on_t r ans_ser vi ce_i d` (`s_on_t r ans_ser vi ce_i d`) , KEY `s_on_t r ans_t r ans_i d` ( `s_on_t r ans_t r ans_i d`) , CONSTRAI NT `servi ce_on_t r anspor t at i on_i bf k_3` FOREI GN KEY( `s_ on_t r ans_ser vi ce_i d`) REFERENCES `servi ce` ( `ser vi ce_i d`) ONDELETE CASCADE, CONSTRAI NT `servi ce_on_t r anspor t at i on_i bf k_2` FOREI GN KEY( `s_on_t r ans_t r ans_i d`) REFERENCES `t r anspor t ati on` ( `t r ans_i d`)) ENGI NE=I nnoDB DEFAULT CHARSET=l at i n1;

## Def i ni t i on f or t he `new_vi ew4` vi ew :


20/23


#

CREATE ALGORI THM=UNDEFI NED VI EW `new_vi ew4` AS ( sel ect

`package`. `package_i d` AS `package_i d`, `package`. `package_name` AS `package_name`, `package`. `package_di scount ` AS `package_di scount`, `package`. `package_pr i ce` AS `package_pri ce`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`, àccomodati on . àcc_name` AS àcc_name`, àccomodati on`. àcc_l ocat i on AS àcc_l ocat i on , àccomodat i on . àcc_t ype` AS àcc_t ype`, àccomodati on`. àcc_cl ass` AS àcc_cl ass`, NULL AS àct_descr i pt i on , NULL AS àct_l ocat i on`, NULL AS àct _contact`, NULL AS àct _t ype , NULL AS `t r ans_t o`, NULL AS `t r ans_ t ype , NULL AS `t r ans_ f r om, NULL AS `t r ans_code`

f rom `package` j oi n `package_servi ces` j oi n `servi ce` j oi n`servi ce_on_accomodat i on j oi n àccomodati on

wher e( ( `package . `package_i d` = `package_servi ces`. `package_i d`) and

( `package_ser vi ces`. `servi ce_i d` = `ser vi ce`. `ser vi ce_i d`) and( `ser vi ce . `ser vi ce_i d` =`ser vi ce_on_accomodat i on . `s_ on_acc_servi ce_i d`) and( `ser vi ce_on_accomodati on`. `s_ on_acc_acc_i d` =

àccomodat i on`. àcc_i d`) ) ) uni on ( sel ect

`package`. `package_i d` AS `package_i d`, `package`. `package_name` AS `package_name`, `package`. `package_di scount ` AS `package_di scount`, `package`. `package_pr i ce` AS `package_pri ce`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`, NULL AS àcc_name`, NULL AS àcc_l ocat i on , NULL AS àcc_t ype`, NULL AS àcc_cl ass` , àcti vi t y`. àct_ descri pt i on` AS àct_descri pt i on`, àcti vi t y`. àct_l ocat i on` AS àct_l ocat i on`,

àcti vi t y`. àct_ cont act` AS àct_cont act`, àcti vi t y`. àct_ t ype` AS àct_t ype`, NULL AS `t r ans_t o`, NULL AS `t r ans_ t ype , NULL AS `t r ans_ f r om, NULL AS `t r ans_code`

f rom `servi ce` j oi n `package` j oi n `package_ser vi ces` j oi n`servi ce_on_acti vi t y` j oi n àcti vi t y`


( `package_ser vi ces`. `servi ce_i d` = `ser vi ce`. `ser vi ce_i d`) and


21/23


( `ser vi ce . `ser vi ce_i d` = `servi ce_on_acti vi t y`. `s_on_act_servi ce_i d`)and ( `servi ce_on_acti vi ty`. `s_on_act_act_i d` = àcti vi t y`. àct_i d`) ) )uni on ( sel ect

`package`. `package_i d` AS `package_i d`, `package`. `package_name` AS `package_name`, `package`. `package_di scount ` AS `package_di scount`, `package`. `package_pr i ce` AS `package_pri ce`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`, NULL AS àcc_name`, NULL AS àcc_l ocat i on , NULL AS àcc_t ype`, NULL AS àcc_cl ass` , NULL AS àct_descr i pt i on , NULL AS àct_l ocat i on`, NULL AS àct _contact`, NULL AS àct _t ype , `t r anspor t at i on`. `t rans_t o` AS `t r ans_t o`, `t r anspor t ati on . `t r ans_t ype` AS `t r ans_t ype`, `t r anspor t ati on . `t r ans_f r om AS `t r ans_f r om, `t r anspor t ati on . `t r ans_code` AS `t r ans_code`

f rom `servi ce` j oi n `package` j oi n `package_ser vi ces` j oi n`ser vi ce_on_t r anspor t at i on` j oi n `t r anspor t at i on`


( `package_ser vi ces`. `servi ce_i d` = `ser vi ce`. `ser vi ce_i d`) and( `ser vi ce . `ser vi ce_i d` =`ser vi ce_on_t r anspor t at i on`. `s_on_t r ans_ser vi ce_i d`) and( `ser vi ce_on_t r anspor t at i on`. `s_on_t r ans_t r ans_i d` =

`t r ansport at i on`. `t r ans_i d`) ) ) ;

## Def i ni t i on f or t he `v_accomodat i on_servi ces` vi ew :#

CREATE ALGORI THM=UNDEFI NED VI EW`v_accomodat i on_ser vi ces` ASsel ect

àccomodati on`. àcc_i d` AS àcc_i d`, àccomodati on . àcc_name` AS àcc_name`, àccomodati on`. àcc_l ocat i on AS àcc_l ocat i on , àccomodat i on . àcc_t ype` AS àcc_t ype`, àccomodati on`. àcc_cl ass` AS àcc_cl ass`, `servi ce`. `ser vi ce_i d` AS `ser vi ce_i d`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `,

`servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`f rom

àccomodati on` j oi n `ser vi ce_on_accomodati on j oi n `servi ce`wher e

( ( `ser vi ce`. `ser vi ce_i d` =`ser vi ce_on_accomodat i on . `s_ on_acc_servi ce_i d`) and( `ser vi ce_on_accomodati on`. `s_ on_acc_acc_i d` =àccomodati on . àcc_i d`) ) ;

## Def i ni t i on f or t he `v_acti vi t y_ser vi ces` vi ew :


22/23


#

CREATE ALGORI THM=UNDEFI NED VI EW`v_ act i vi t y_ser vi ces` ASsel ect

`servi ce`. `ser vi ce_i d` AS `ser vi ce_i d`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`, àcti vi t y`. àct_i d` AS àct_i d`, àcti vi t y`. àct_ descri pt i on` AS àct_descri pt i on`, àcti vi t y`. àct_l ocat i on` AS àct_l ocat i on`, àcti vi t y`. àct_ cont act` AS àct_cont act`, àcti vi t y`. àct_ t ype` AS àct_t ype`

f rom `servi ce` j oi n `ser vi ce_on_acti vi t y` j oi n àcti vi t y`

wher e( ( `ser vi ce`. `ser vi ce_i d` =

`ser vi ce_on_acti vi t y`. `s_on_act _ser vi ce_i d`) and( `ser vi ce_on_acti vi t y`. `s_on_act_act_i d` = àcti vi t y`. àct_i d`) ) ;

## Def i ni t i on f or t he `v_cust omer_deal s` vi ew :#

CREATE ALGORI THM=UNDEFI NED VI EW`v_cust omer _deal s` ASsel ect

`cust omer `. `cust _i d` AS `cust _i d`, `cust omer`. `cust _name` AS `cust _name`, `customer` . `cust_addr ess` AS `cust _address`, `cust omer `. `cust _bi r t hdate` AS `cust _bi r t hdat e`, `cust omer`. `cust _gender ` AS `cust _gender `, `deal `. `deal _i d` AS `deal _i d`,

`deal `. `deal _book_dat e` AS `deal _book_date`, `deal `. `deal _empl oyee AS `deal _empl oyee`, `deal `. `deal _name` AS `deal _name`

f rom `deal ` j oi n `cust omer `

wher e( `deal `. `deal _cust _i d` = `cust omer `. `cust _i d`) ;

## Def i ni t i on f or t he `v_deal _cont ent s` vi ew :#

CREATE ALGORI THM=UNDEFI NED VI EW`v_deal _cont ent s` ASsel ect

`deal `. `deal _i d` AS `deal _i d`, `deal `. `deal _book_dat e` AS `deal _book_date`,

`deal `. `deal _empl oyee AS `deal _empl oyee`, `deal `. `deal _name` AS `deal _name`, `deal `. `deal _cust _i d` AS `deal _cust _i d`, `servi ce`. `ser vi ce_i d` AS `ser vi ce_i d`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`

f rom `servi ce` j oi n `deal ` j oi n `deal _servi ces`

wher e


23/23


( ( `deal `. `deal _i d` = `deal _ser vi ces`. `deal _i d`) and( `deal _ser vi ces`. `ser vi ce_i d` = `ser vi ce . `ser vi ce_i d`) ) ;

## Def i ni t i on f or t he `v_package_cont ent s` vi ew :#

CREATE ALGORI THM=UNDEFI NED VI EW`v_package_cont ents` ASsel ect

`package`. `package_i d` AS `package_i d`, `package`. `package_name` AS `package_name`, `package`. `package_di scount ` AS `package_di scount`, `package`. `package_pr i ce` AS `package_pri ce`, `servi ce`. `ser vi ce_i d` AS `ser vi ce_i d`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`

f rom `servi ce` j oi n `package` j oi n `package_ser vi ces`


( `package_ser vi ces`. `servi ce_i d` = `ser vi ce`. `servi ce_i d`) ) ;

## Def i ni t i on f or t he `v_t r anspr at at i on_ser vi ces` vi ew :#

CREATE ALGORI THM=UNDEFI NED VI EW`v_ t r ansprat at i on_ser vi ces` ASsel ect

`t r anspor t at i on`. `t rans_i d` AS `t r ans_i d`, `t r anspor t at i on`. `t rans_t o` AS `t r ans_t o`, `t r anspor t ati on . `t r ans_t ype` AS `t r ans_t ype`,

`t r anspor t ati on . `t r ans_f r om AS `t r ans_f r om, `t r anspor t ati on . `t r ans_code` AS `t r ans_code`, `servi ce`. `ser vi ce_i d` AS `ser vi ce_i d`, `servi ce`. `ser vi ce_descri pt i on` AS `ser vi ce_descri pt i on`, `servi ce`. `ser vi ce_st ar t ` AS `ser vi ce_st ar t `, `servi ce`. `servi ce_end` AS `servi ce_end`, `servi ce`. `ser vi ce_pr i ce` AS `ser vi ce_pr i ce`

f rom ( `servi ce` j oi n ( `servi ce_on_t ransport at i on` j oi n`t r ansport at i on`))

wher e( ( `ser vi ce`. `ser vi ce_i d` =

`ser vi ce_on_t r anspor t at i on`. `s_on_t r ans_ser vi ce_i d`) and( `ser vi ce_on_t r anspor t at i on`. `s_on_t r ans_t r ans_i d` =`t r ansport at i on`. `t r ans_i d`) ) ;

team5 final

Documents