taxi db29b
Post on 06-Apr-2018
232 Views
Preview:
TRANSCRIPT
-
8/3/2019 Taxi Db29b
1/80
Project: Taxi system
Author: Vctor Martnez Roign album: 182931Master of Computer ScienceFaculty of Computer Science andManagementPolitechnika Wrocawska
Data created: 30/10/2009Data last updated: 08/02/10
-
8/3/2019 Taxi Db29b
2/80
Project: Taxi system
Table of Contents1.Business Requeriments..........................................................................................5
1.1.Background....................................................................................................51.2.Actors.............................................................................................................5
1.2.1.CUSTOMER:............................................................................................51.2.2.TAXI DRIVER:..........................................................................................51.2.3.MANAGER:..............................................................................................6
1.3.Business objectives.........................................................................................61.3.1.BO/01......................................................................................................61.3.2.BO/02......................................................................................................61.3.3.BO/03......................................................................................................61.3.4.BO/04......................................................................................................61.3.5.BO/05......................................................................................................61.3.6.BO/06......................................................................................................6
1.4.Business Risks................................................................................................71.4.1.BR/01.......................................................................................................71.4.2.BR/02.......................................................................................................7
1.5.Vision of the solution......................................................................................71.6.Major features................................................................................................71.7.Assumption and Dependencies.......................................................................8
2.Software requeriments specifications....................................................................92.1.Context Diagram............................................................................................92.2.Operating environment...................................................................................92.3.Plans, transactions and frequencies...............................................................9
3.Use Cases............................................................................................................113.1.Use Case Specification.................................................................................12
3.1.1.Consult order list...................................................................................12
3.1.2.Modify one order...................................................................................133.1.3.Delete one order....................................................................................153.1.4.Add new travel......................................................................................173.1.5.Consults all the travels..........................................................................193.1.6.Consults schedule..................................................................................203.1.7.Modify actual order...............................................................................213.1.8.Join in a station......................................................................................223.1.9.Left the actual station............................................................................233.1.10.Consults all orders waiting..................................................................243.1.11.Modify the data about one order.........................................................253.1.12.Assign one order..................................................................................273.1.13.Consults one schedule.........................................................................29
3.1.14.Modify one schedule............................................................................303.1.15.Consult all orders from one person.....................................................323.1.16.Delete one order..................................................................................333.1.17.Consults the information about one manager......................................343.1.18.Consults who is in one station.............................................................353.1.19.Order a taxi.........................................................................................363.1.20.Consults the information about one customer.....................................373.1.21.Consults the information about one taxi driver...................................38
4.Conceptual modeling...........................................................................................394.1.Business rules...............................................................................................39
4.1.1.BR1........................................................................................................394.1.2.BR2........................................................................................................39
4.1.3.BR3........................................................................................................394.1.4.BR4........................................................................................................394.1.5.BR5........................................................................................................394.1.6.BR6........................................................................................................39
Page: 2/80
-
8/3/2019 Taxi Db29b
3/80
Project: Taxi system
4.1.7.BR7........................................................................................................394.1.8.BR8........................................................................................................39
4.2.Conceptual data model.................................................................................404.2.1.Data dictionary......................................................................................42
4.3.Verification of conceptual model..................................................................435.Transaction specification.....................................................................................44
5.1.Query operations..........................................................................................445.1.1.Consults order list.................................................................................445.1.2.Show one travel.....................................................................................445.1.3.Consults all the travels..........................................................................445.1.4.Sum all prices and distances.................................................................445.1.5.Consults schedule..................................................................................455.1.6.Consults all orders waiting....................................................................455.1.7.Consults the information about one manager........................................455.1.8.Consults the information about one customer.......................................455.1.9.Consults the information about one taxi driver.....................................455.1.10.Consult who is the first in one station.................................................455.1.11.Consults who is in one station.............................................................46
5.2.Transactions.................................................................................................465.2.1.Modify one order...................................................................................465.2.2.Delete one order....................................................................................465.2.3.Add new travel......................................................................................465.2.4.Change state to busy.............................................................................465.2.5.Join in a station......................................................................................465.2.6.Left the actual station............................................................................475.2.7.Change state to free..............................................................................475.2.8.Modify one order waiting......................................................................475.2.9.Assign one order to the first taxi of one station.....................................47
5.2.10.Assing one order to one taxi driver......................................................475.2.11.Modify one schedule............................................................................485.2.12.Order a taxi.........................................................................................48
6.CRUD matrix.......................................................................................................497.Logical modeling.................................................................................................508.Nonfunctional requirements................................................................................51
8.1.Performance requirements...........................................................................518.1.1.PE/01.....................................................................................................518.1.2.PE/02.....................................................................................................518.1.3.PE/03.....................................................................................................51
8.2.Security Requirements.................................................................................518.2.1.SE/01.....................................................................................................51
8.2.2.SE/02.....................................................................................................518.2.3.SE/03.....................................................................................................518.2.4.SE/04 ....................................................................................................51
8.3.Availability....................................................................................................519.Physical data model.............................................................................................52
9.1.Considerations..............................................................................................529.2.Constraints...................................................................................................52
9.2.1.C/01.......................................................................................................529.2.2.C/02.......................................................................................................529.2.3.C/03.......................................................................................................529.2.4.C/04.......................................................................................................539.2.5.C/05.......................................................................................................53
9.2.6. C/06......................................................................................................539.2.7.C/07.......................................................................................................53
9.3.Physical model diagram................................................................................549.4.Database's definition....................................................................................54
Page: 3/80
-
8/3/2019 Taxi Db29b
4/80
Project: Taxi system
9.4.1.Creating tables......................................................................................549.4.2.Constraints............................................................................................569.4.3.Query operations...................................................................................579.4.4.Transactions..........................................................................................599.4.5.Security (Implementation and tests)......................................................63
10.Results...............................................................................................................6710.1.Checking non-functional constraints..........................................................6710.2.Checking security requirements.................................................................6710.3.Testing all querys.......................................................................................6810.4.Testing all the transactions........................................................................69
11.Conclusions.......................................................................................................7111.1.Future works..............................................................................................71
Page: 4/80
-
8/3/2019 Taxi Db29b
5/80
Project: Taxi system
1. Business Requeriments
1.1. Background
The government has requested for introduce new technologies in different publicsectors, the Taxi service is one of them.
The Taxi drivers associations has arranged for share opinions and ideas about whatthey should introduce.
They want one database that would improve their quality of work life and their pro-ductivity. Knowing when and where customers want a taxi in advance would reducethe work of the manager (taxi base) receiving calls and would improve the efficiency
of the service.
The future ability for customers to order taxi for an specific situation in a specifictime would provide the possibility of cost saving of the taxi associations.
A Taxi service is a public service with the target of bring the people from one site toanother.
The Taxi driver associations need a new system for give better service to customersand helps taxi drivers in his work.
In our database will exists 3 different groups of users:
the customer: who uses the taxi service for move from one side to another.
the taxi driver: who works in a car, moving the people to one side to another.
The manager: who makes all the logistic about the taxi service, speaks withcostumers and send the orders to one selected taxi driver.
1.2. Actors
In the actual chapter will be defined all the groups of actors that will use thedatabase with his possibles transactions.
1.2.1. CUSTOMER:
Customer is a person who uses taxi for move from one site to another.Customer optionally order a taxi for an specific date, consult the informationabout of them (in a specific range of dates) and maybe modify the data aboutone of them.
1.2.2. TAXI DRIVER:
Taxi driver is a person who works picking up customers to one site (source) toone determined location (destination).
Page: 5/80
-
8/3/2019 Taxi Db29b
6/80
Project: Taxi system
1.2.3. MANAGER:
Manager is a person who works managing all the Taxi service, works in thebase location of the taxi service receiving calls from customers that wants touse the taxi service for move to one site to another, and communicates thatinformation to taxi drivers.
1.3. Business objectives
1.3.1. BO/01
Reduce manager operating cost by 35% within 12 month following the initialrelease.
Probability: 0.8; Impact: 9
1.3.2. BO/02
Too few customers might use the system, improving the quantities of customers by3% within 12 months following the initial release in the taxi service .
Probability: 0.2 Impact: 7
1.3.3. BO/03
Reduce the use of the telephonic line in a 38% letting to people who no has internetto call without waitings, moreover, the new technologies are very popular withyoung people, both will improve the number of customers in 5%.
Probability: 0.65 Impact: 8
1.3.4. BO/04
Helps the taxi drivers to cash up, reducing the hard work and improving the work'squality of taxi drivers in a 20%, generating a better service for the customers thatwill improve the number of customers in 1%.
Probability: 0.5 Impact: 7
1.3.5. BO/05
Consults how many taxis are in one or another station, will reduce the costs to thetaxi drivers in a 8% and would give better service because every station will havetaxi drivers in the queue, it will improve the customers in 8%.
Probability: 0.3 Impact: 5
1.3.6. BO/06
Knowing where is the nearest taxi driver free/queue from one point will reduce thewait from the customer in one specific situation improving the quaility service andgenerating 1.2% more of customers.
Probability: 0.3 Impact:5
Page: 6/80
-
8/3/2019 Taxi Db29b
7/80
Project: Taxi system
1.4. Business Risks
1.4.1. BR/01The median age from a taxi driver is 43 ages and if we counts also the median ineducation the taxi service have a risks of aceptation on the taxi drivers the mostimportant users in the system.
It will be very hard work for themselves to understand the system and use it whenthe major part of them are using the traditional system (manual and telephonic) for20 years.
It will reduce the aceptation of the system in 19% by taxi drivers and also incustomers in one 12%.
Probability: 0.5 Impact:9
1.4.2. BR/02
Reduce the work of the Manager by 0,9% (and the telephonic line with the samevalue) within 12 months following the initial release letting to the Manager moretime for the others transactions.
Reduce the cost of the taxi association's in 0,2% within 12 months.
Probability: 0.4; Impact: 1
1.5. Vision of the solution
For customers who wish to create orders for take a taxi, the Taxi system is aninternet based application that will accept individual orders and triggerdelivery of the taxi driver to a designated location.
Unlike the current telephone and manual ordering process, customers whouse Taxi system will not have to go to the taxi's station to get one taxi, wichwill save them time and will increase the usage of the taxi service.
1.6. Major features FE/01 create, view, modify and delete new travels
FE/02 - Consults all the travels for an specific range of dates
FE/03 Consults all orders waiting (in a specific range of dates)
FE/04 Join/left from one station
FE/05 Create, view, modify and delete new orders
FE/06 Assign one order to the first in one station
FE/07 Produce recipes for customers, taxi drivers and managers.
Page: 7/80
-
8/3/2019 Taxi Db29b
8/80
Project: Taxi system
1.7. Assumption and Dependencies
AS/01- Taxi drivers and vehicles will be avaliable to deliver all orders within
15 minutes of the requested delivery time.
Page: 8/80
-
8/3/2019 Taxi Db29b
9/80
Project: Taxi system
2. Software requeriments specifications
2.1. Context Diagram
The Taxi system is a new system that replaces the current manual and telephoneprocesses for odering taxis.
Actually the customer must to contact with the manager by phone or visit the taxi'sstation for order a taxi.
The taxi driver arrives to one of the taxi's station and waits for his turn, when itarrives, must picks up the customer and calculates (with the help of thetaximeter/GPS) the price since the origin to the destination or get from another
tool/service.At the end of his schedule he should does the books (accounting).
The manager should takes notes about all the orders, send this information to oneselected taxi driver, normally the first of one taxi's station (queue).
2.2. Operating environment
OE/01 Taxi system shall operate with the following Web browsers: MS InternetExplorer v.7.0 and 8.0 and Firefox 3.5.
OE/02- Taxi system shall permit user access from the corporate intranet and froman internet connection from user's home/taxi.
2.3. Plans, transactions and frequencies
The most frequently transaction of course, is the transaction to order a taxi, in oneIsland like Ibiza with more than 200.000 habitants (2009), and if we suppose theworst case, it is on summer, these quantity grows and arrives aprox. 1.213.674 tour-ists (in 2007).
One day habitual (at winter), the taxi system should recieve 500 new orders givedby customers, such as 20 orders will be modified, should create 350 new travelsgived by taxi drivers, 80 consults of the schedule by taxi drivers, 100 new orders bythe manager, every taxi driver (that works this day) will consults 2 times(180x2=360) at the same day all the travels from a specific range of dates.
Our system should be able to support the worst case, immaginating that at summer,when all the taxi drivers are working all the day, suppose that 800 habitants and100.000 tourists needs a taxi in one day.
So 180.000 people will may access to our database in one day and according to thetaxi drivers association, the database has to let at the 0,15% of this people to ordera taxi (aprox. 270) at the same time. To this quantity we have to add at the 70% oftaxi drivers (approx. 280) adding everyone a new travel, the 70% of managers
(approx. 8) adding new orders.So in a specific period of time of 10 min the lenght the system has to be able tomanage:
Page: 9/80
-
8/3/2019 Taxi Db29b
10/80
Project: Taxi system
270 new orders by customers.
280 new travels by taxi drivers.
8 new orders by Managers.
The taxi's association demands that the database has to be able to manage thesepoints:
Create/modify/delete orders and travels
Displays all the orders (by customer, by taxi or by one specific range ofdates).
Displays all the travels (in one specific range), system has to display all theinformation about every travel that belongs in the specific range of dates andalso the total price.
Store the information about every taxi, in which queue is working, the actualposition and who is near from one position.
Store and display the schedule for every taxi driver.
Page: 10/80
-
8/3/2019 Taxi Db29b
11/80
Project: Taxi system
3. Use Cases
Actor Use Case
Taxi driver 1. Add new travel2. Consults all travels3. Consults schedule4. Modify actual oder5. Join in a station6. Consults who is in the station7. Left the actual station
Customer 1. Consults order list
2. Modify one order3. Delete one order4. Order a taxi
Manager 1. Consults all orders waiting2. Modify data about one order3. Assign one order4. Consults one schedule5. Modify one schedule6. Consults all orders from one
person7. Delete one order8. consults the information about
one person9. consults who is in one station10.order a taxi
Page: 11/80
-
8/3/2019 Taxi Db29b
12/80
Project: Taxi system
3.1. Use Case Specification
3.1.1. Consult order listUse Case ID: UC/01
Name: Consult order lists
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 22, 2009 Date last Updated: Nov 16, 2009
Actors: Customer
Description:
A customer accesses the Taxi System, optionally views the order list (that hedid) of an specific range of dates.
Preconditions:
1. Customer is logged into Taxi System
Postconditions:
1. Order list is showed
Actor Actions System actions
Normal Course:
1. Ask to view orders list 2. System ask for specified range of dates
3. Insert the range of dates and indicatesthat is complete
4. Display all the information about all theorders from the actual customer in theselected range of dates.
Priority: Low
Frequency of use: Approximately 10 customers, average of one usage per week
Business rules:
Special requirements:
Page: 12/80
-
8/3/2019 Taxi Db29b
13/80
Project: Taxi system
3.1.2. Modify one order
Use Case ID: UC/02
Name: Modify one order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Customer
Description:
A customer accesses the Taxi System, optionally modify one selected order(that he did) of an specific range of dates.
Preconditions:
1. Customer is logged into Taxi System
Postconditions:
2. Changes are saved into the system
Actor Actions System actions
Normal Course:
1. Ask to modify one order from onespecific range of dates
2. System ask for specified range of dates
3. Insert the range of dates and indicatesthat the range of dates is complete
4. Display all the information about all theorders from the actual customer in theselected range of dates.
5. Select to modify one order from thelist and indicates that is complete
6. Shows the information about theselected order
7. Modify the information about theactual order and indicates that thechanges are completed.
8. Shows the updated order
9. Confirm the changes or request tomodify (jump to 2).
10. Systems update the actual order inthe database.
Exceptions
E.2 The order is not in the state of waiting and the customer isnt able to modify.
Inform the customer of the state of theactual order and that is impossible tomodify, only the manager is able.
Call to the manager
Page: 13/80
-
8/3/2019 Taxi Db29b
14/80
Project: Taxi system
Priority: High
Frequency of use: Approximately 70 customers, average of 2 usage per week.
Business rules:
Special requirements:
Page: 14/80
-
8/3/2019 Taxi Db29b
15/80
Project: Taxi system
3.1.3. Delete one order
Use Case ID: UC/03
Name: Delete one order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Customer
Description:
A customer accesses the Taxi System, optionally delete one selected order(that he did) of an specific range of dates.
Preconditions:
1. Customer is logged into Taxi System
Postconditions:
3. Order is marked for not show again to the actual customer.
Actor Actions System actions
Normal course:
1. Ask to delete one order from one
specific range of dates
2. System ask for specified range of
dates
3. Insert the range of dates and indicatesthat the range of dates is complete
4. Display all the information about allthe orders from the actual customer inthe selected range of dates.
5. Select one or more orders from the listand indicates that is complete
6.Shows the information about theselected order
7. Confirm for delete the order 8. Set the actual order in the databasefor doesnt show to the customer again(jump to 2)
E.2 The order is not in the state of waiting and the customer isnt able to modify.
Inform the customer of the state of theactual order and that is impossible tomodify, only the manager is able.
Call to the manager
Page: 15/80
-
8/3/2019 Taxi Db29b
16/80
Project: Taxi system
Priority: Very low
Frequency of use: Approximately 3 customers, average of one usage per week
Business rules:
Special requirements:
Page: 16/80
-
8/3/2019 Taxi Db29b
17/80
Project: Taxi system
3.1.4. Add new travel
Use Case ID: UC/04
Name: Add new travel
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 24, 2009 Date last Updated: October 29, 2009
Actors: Taxi driver
Description:
A taxi driver accesses the Taxi System, optionally adds a new travel.
Preconditions:
1. Taxi driver is logged into Taxi System.
2. The state of the taxi is free or queue.
Postconditions:
1. Travel is stored, the state of taxi driver change to busy, travel informationis showed.
Actor Actions System actions
Normal Course:
1. Ask to add new travel 2. System ask about the travel
3. Insert the data about the travel andindicates that travel data is completed
4. Display all the information about theactual travel.
5. Confirm or request to modify theactual travel (back to step 3)
6. System stores the actual travel in thedatabase
7. Shows all the information about theactual travel.
8. change the state of Taxi to busy
Exceptions:
E.3 The states taxi is different of free or queue and the taxi isnt able to createnew travel.
Inform the taxi of the state and in thatstate is impossible to create a new travel.
Change the state of the taxi to free orqueue
Page: 17/80
-
8/3/2019 Taxi Db29b
18/80
Project: Taxi system
Priority: High
Frequency of use: Approximately 180 user, average of 2 usages per day
Business rules: BR03
Special requirements:
Page: 18/80
-
8/3/2019 Taxi Db29b
19/80
Project: Taxi system
3.1.5. Consults all the travels
Use Case ID: UC/05
Name: Consults all the travels
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 24, 2009 Date last Updated: October 29, 2009
Actors: Taxi driver
Description:
A taxi driver accesses the Taxi System, optionally consults all the travels thathe did for a specific range of dates (date and time), It will be useful whenarrived at the end of his work.
Preconditions:
1. Taxi driver is logged into Taxi System
Postconditions:
1. all the travels that belongs of the range of dates specified are showed.
Actor Actions System actions
Normal Course:
1. Ask for list the travels 2. System ask for specified range of dates(and times).
3. Insert specified range of dates andindicates that is complete.
3. Shows all the information about alltravels did in the specific date, theindividual data and finally total valuesfrom all the selected travels .
Priority: Normal
Frequency of use: Approximately 180 user, average of 2 usages per day
Business rules:
Special requirements:
Page: 19/80
-
8/3/2019 Taxi Db29b
20/80
Project: Taxi system
3.1.6. Consults schedule
Use Case ID: UC/06
Name: Consults schedule
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 25, 2009 Date last Updated: October 29, 2009
Actors: Taxi driver
Description:
A taxi driver accesses the Taxi System, optionally consults him schedule for know atwhat hours he has to work and when he isnt able to work.
Preconditions:
1. Taxi driver is logged into Taxi System
Postconditions:
1. The schedule of the actual taxi driver is showed by the system.
Actor Actions System actions
Normal Course:
1. Ask to show the schedule. 2. System show the schedule of theactual taxi driver
Priority: High
Frequency of use: Approximately 190 user, average of 2 usages per week
Business rules: BR4
Special requirements:
Page: 20/80
-
8/3/2019 Taxi Db29b
21/80
Project: Taxi system
3.1.7. Modify actual order
Use Case ID: UC/07
Name: Modify actual order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 25, 2009 Date last Updated: October 29, 2009
Actors: Taxi driver
Description
A taxi driver accesses the Taxi System, optionally if he has active an order (with thevalue taxi_delivered with his ID and state_order with the value doing) he canmodify the actual order. For example, indicate that its a Fake (changing thestate_order to fake, , indicates the datetime delivered....).
Preconditions
1. Taxi driver is logged into Taxi system
2. The value of the order taxi_drv is the ID from the actual Taxi driver
3. The order is in state waiting or doing
Postconditions
1. The modified data from the actual order is saved.
Actor Actions System actions
Normal Course:
1. Ask for modify the order 2.Display the data from the actual order.
3. Modify the data from the selectedorder and indicates that is complete.
4. Displays all the information about theactual order.
5. Confirm the new data from the actualorder
6. System stores order in database.
Alternative course
The state is changed by the taxi driver to fake (branch after 6)
7. the n_fakes in the person who createdthe order increment in one.
8. the state of the taxi change to free.
Priority: Very high
Frequency of use: Approximately 180 user, average of 4 usages per day
Business rules: BR03, BR02
Special requirements:
Page: 21/80
-
8/3/2019 Taxi Db29b
22/80
Project: Taxi system
3.1.8. Join in a station
Use Case ID: UC/08
Name: Join in a station
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 03, 2009 Date last Updated:November03,2009
Actors: Taxi driver
Description;
A taxi driver accesses the Taxi System, optionally join to one station.
Preconditions:
1. Taxi driver is logged into Taxi System
2. The station musts exists.
3. The taxi_state is free.
Postconditions:
1. the taxi driver is situated the last in the station.
2. The state of the taxi is changed to station.
Actor Actions System actions
Normal Course:
1. Ask to join in one station. 2. System ask for the data about thestation
3. Insert the ID from the station and indicatesthat is complete
4. Stores the taxi driver in the station(in the last position).
Exceptions
E. 5The taxi isn't in the state of free
Inform taxi driver that can't join inthe selected station if him state isdifferent of free
Change state to free
Priority: High
Frequency of use: Approximately 180 user, average of 10 usages per day
Business rules: BR05
Special requirements:
Page: 22/80
-
8/3/2019 Taxi Db29b
23/80
Project: Taxi system
3.1.9. Left the actual station
Use Case ID: UC/09
Name: Left the actual station
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 03, 2009 Date last Updated:November03,2009
Actors: Taxi driver
Description;
A taxi driver accesses the Taxi System, optionally left one station.
Preconditions:
1. Taxi driver is logged into Taxi System
4. The station musts exists.
5. The taxi_state is in the state of queue.
Postconditions:
1. the taxi deleted from one station
2. The state of the taxi is changed to free.
Actor Actions System actions
Normal Course:
1. Ask to left the actual station. 2. System delete the taxi driver fromthe station.
3. Change the state of taxi driver tofree.
Exceptions
E. 5The taxi isn't in the state of queue
Inform taxi driver that can't left theactual station because isn't in theactual station
Nothing, because he isn't in the station.
Priority: High
Frequency of use: Approximately 180 user, average of 10 usage per day
Business rules: BR05
Special requirements:
Page: 23/80
-
8/3/2019 Taxi Db29b
24/80
Project: Taxi system
3.1.10. Consults all orders waiting
Use Case ID: UC/10
Name: Consult all orders waiting
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 27, 2009 Date last Updated: October 29, 2009
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally views all the orders with the statewaiting of an specific range of dates.
Preconditions:
1. Manager is logged into Taxi System
Postconditions:
1. All orders with state waiting are showed
Actor Actions System actions
Normal Course:
1. Ask to view all orders with state of waiting 2. System ask for specified range of
dates
3. Insert the range of dates and indicate thatis complete
4. Displays all the information aboutall the orders with the state ofwaiting in the selected range ofdates.
Priority: Very high
Frequency of use: Approximately 5 user, average of 150 usage per day
Business rules:
Special requirements:
Page: 24/80
-
8/3/2019 Taxi Db29b
25/80
Project: Taxi system
3.1.11. Modify the data about one order
Use Case ID: UC/11
Name: Modify the data about one order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally modify the data about one order
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. All information about the selected ID is showed.
Actor actions System actions
Normal actions
1. Ask for modify one order 2. Ask the ID about the order
3. Insert the ID of the order that wants todelete and indicates that is completed
4. Shows the information about theselected order.
5. Ask to modify one order from the list 6. Shows the information about theselected order
7. Modify the information about the actualorderand indicate that is complete
8. Display the new data of the actualorder
9. Confirm the changes or request to modify(jump to 6). 10. System update the order in thedatabases (jump to 2).
Exceptions
E. 5The selected ID doesnt exists
Inform Manager that the selected IDdoesnt exists
Select another ID.
Page: 25/80
-
8/3/2019 Taxi Db29b
26/80
Project: Taxi system
Priority: High
Frequency of use: Approximately 5 user, average of 5 usages per day
Business rules: BR02
Special requirements:
Page: 26/80
-
8/3/2019 Taxi Db29b
27/80
Project: Taxi system
3.1.12. Assign one order
Use Case ID: UC/12
Name: Assign one order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally assigns one order waiting to thefirst taxi driver situated in one selected station.
Preconditions:
1. Manager is logged into Taxi System
Postconditions:
1. All orders with state waiting are showed
Actor actions System actions
Normal course:
1. Ask for assign one order waiting to onestation
2. shows all the orders with the stateof waiting
3. Select one order and indicate that iscomplete.
4. Shows the information about theselected order and ask about the ID'sstation.
7. Insert the ID from the station and indicatesthat is complete
8. Displays the information about thestation showing also the first, secondand third taxi drivers waiting (if thereexists).
9. confirm the requested transaction 10. The value of order_taxi_deliveredchanged with the ID of the first thatis registered in the station
11. The taxi driver is deleted fromthe station and his state changes tobusy all the data is updated in thedatabase.
Exception:
E.6 The selected station is empty
Inform manager that the taxi stationis empty
Page: 27/80
-
8/3/2019 Taxi Db29b
28/80
Project: Taxi system
Introduce another ID station
Priority: Very high
Frequency of use: Approximately 5 user, average of 600 usages perday
Business rules: BR02
Special requirements:
Page: 28/80
-
8/3/2019 Taxi Db29b
29/80
Project: Taxi system
3.1.13. Consults one schedule
Use Case ID: UC/13
Name: Consults one schedule
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 27, 2009 Date last Updated: October 29, 2009
Actors: Manager
Description:
A Manager accesses the Taxi System, optionally consults the information about onespecified schedule.
Preconditions:
1. Manager is logged into Taxi System
2. The ID of the schedule exists
Postconditions:
1. The schedule with the specific ID is showed by the system
Actor Actions System actions
Normal Course:
1. Ask to show one schedule. 2. System ask the ID of the schedule
3. Insert the ID of one specified scheduleand indicates that is complete
4. Shows the selected schedule
Exceptions
E. 4The schedule selected doesnt exists
Inform Manager that the scheduleselected doesnt exists
Changes the selected schedule for oneexistent.
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usage permonth
Business rules: BR04
Special requirements:
Page: 29/80
-
8/3/2019 Taxi Db29b
30/80
Project: Taxi system
3.1.14. Modify one schedule
Use Case ID: UC/14
Name: Modify one schedule
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Manager
Description:
A Manager accesses the Taxi System, optionally modify one specified schedule.
Preconditions:
1. Manager is logged into Taxi System
2. The ID of the schedule exists
Postconditions:
1. The selected schedule is updated in the system.
Actor actions System actions
Normal course:
1. Ask for modify one schedule 2. Ask the ID of the schedule
3. Insert the ID of one schedule andindicate that is complete
3. Shows the information of the actualschedule
4. Modify the data about the schedule 5. Shows the updated information aboutthe schedule
6. Confirm or modify (jump 3) thechanges
10.Stores the changes in the databases.
Exceptions
E. 4 The schedule selected doesntexists
Inform Manager that the scheduleselected doesnt exists
Changes the selected schedule for oneexistent.
Page: 30/80
-
8/3/2019 Taxi Db29b
31/80
Project: Taxi system
Priority: Very low
Frequency of use: Approximately 5 user, average of 500 usages forevery 3 month.
Business rules: BR04
Special requirements:
Page: 31/80
-
8/3/2019 Taxi Db29b
32/80
Project: Taxi system
3.1.15. Consult all orders from one person
Use Case ID: UC/15
Name: Consult all orders from one person
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 27, 2009 Date last Updated: October 29, 2009
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally views all the orders from oneperson or all the orders that delivered one taxi driver.
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. All orders with state waiting are showed and all the changes about theorders saved.
Actor Actions System actions
Normal Course:
1. Ask to view all orders from one person 2. System ask for specified the ID fromthe person and specific range of dates
3. Insert the ID from a person and also thespecific range of dates that want to consultsand indicate that is complete
4. Displays all the information about allthe orders that belongs at the IDindicated or if has this value in thetaxi_delivered.
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usages perweek
Business rules: BR01
Special requirements:
Page: 32/80
-
8/3/2019 Taxi Db29b
33/80
Project: Taxi system
3.1.16. Delete one order
Use Case ID: UC/16
Name: Delete one order
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: November 16, 2009 Date last Updated: Nov 16, 2009
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally delete one order
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. The selected order is deleted from the system
Actor actions System actions
Normal course
1. Ask for delete one order 2. Ask the ID about the order
3. Insert the ID of the order that wants todelete and indicates that is completed
4. Shows the information about theselected order.
7. Confirm to the delete the selected order 8. Delete the selected order from thedatabases (jump to 2).
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usages permonth
Business rules:
Special requirements:
Page: 33/80
-
8/3/2019 Taxi Db29b
34/80
Project: Taxi system
3.1.17. Consults the information about one manager
Use Case ID: UC/17
Name: Consults the information about one manager
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 27, 2009 Date last Updated: January 22, 2010
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally views all the information avaliableabout a selected manager
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. All information about the selected ID is showed.
Actor Actions System actions
Normal Course:
1. Ask to view the information about onemanager
2. System ask for specified the ID fromthe manager
3. Insert the ID from a manager and indicatethat is complete
4. Displays the following data thatbelongs at the ID specified (name,surname, DNI, address, telephon1,telephon2, email, initial_date, final_date).
Exceptions
E. 5The selected ID doesnt exists
Inform Manager that the selected IDdoesnt exists
Select another ID.
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usages perweek
Business rules: BR06, BR07
Special requirements:
Page: 34/80
-
8/3/2019 Taxi Db29b
35/80
Project: Taxi system
3.1.18. Consults who is in one station
Use Case ID: UC/18
Name: Consults who is in one station
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 29, 2009 Date last Updated: October 29, 2009
Actors: Manager and Taxi driver
Description;
A Manager and a Taxi driver accesses the Taxi System, optionally views who is inone specified station
Preconditions:
1. Manager or Taxi driver is logged into Taxi System
2. The ID of the station must exists.
Postconditions:
1. All the taxi drivers situated in one station (with the state of queue) areshowed.
Actor Actions System actions
Normal Course:
1. Ask to view the list of taxi drivers that aresituated in one station
2. System ask for the ID from thestation
3. Insert the ID from a station and indicatethat is complete
4. Displays all the taxi drivers (onlyID and name) situated in theselected station with the state ofqueue (ordered).
Exceptions
E. 5The selected ID doesnt exists
Inform Manager/taxi driver that theselected ID doesnt exists
Select another ID.
Priority: High
Frequency of use: Approximately 5 user, average of 150 usages per
day
Business rules: BR05
Special requirements:
Page: 35/80
-
8/3/2019 Taxi Db29b
36/80
Project: Taxi system
3.1.19. Order a taxi
Use Case ID: UC/19
Name: Order a Taxi
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: October 27, 2009 Date last Updated: October 29, 2009
Actors: Customer and Manager
Description:
A Manager, customer or a Taxi driver accesses the Taxi System, optionallyorder a taxi with specific data (date, location, etc.).
Preconditions:
1. Manager is logged into Taxi System
Postconditions:
1. Order is stored in Taxi System with a status of Waiting.
Actor Actions System actions
Normal Course
1. ask for add new order. 2.System ask about the order.
3. Insert the data needed about theorderand indicate that is complete
4. Displays all the information about theactual order.
5. Confirm the order or request to modify(back to step 3)
6. Stores the new order in the databases.
Priority: Very high
Frequency of use: Approximately 500 user, average of 1.5 usages
per day
Business rules: BR1
Special requirements:
Page: 36/80
-
8/3/2019 Taxi Db29b
37/80
Project: Taxi system
3.1.20. Consults the information about one customer
Use Case ID: UC/17
Name: Consults the information about one customer
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: January 22, 2010 Date last Updated: January 22, 2010
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally views all the information avaliableabout a selected customer
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. All information about the selected ID is showed.
Actor Actions System actions
Normal Course:
1. Ask to view the information about onecustomer
2. System ask for specified the ID fromthe customer
3. Insert the ID from a customer and indicatethat is complete
4. Displays the following data thatbelongs at the ID specified (name,surname, DNI, address, telephon1,telephon2, email, nfakes).
Exceptions
E. 5The selected ID doesnt exists
Inform Manager that the selected IDdoesnt exists
Select another ID.
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usages per
week
Business rules: BR06, BR07
Special requirements:
Page: 37/80
-
8/3/2019 Taxi Db29b
38/80
Project: Taxi system
3.1.21. Consults the information about one taxi driver
Use Case ID: UC/17
Name: Consults the information about one taxi driver
Created By: Vctor Martnez Last Update By: Vctor Martnez
Date Created: January 22, 2009 Date last Updated: January 22, 2010
Actors: Manager
Description;
A Manager accesses the Taxi System, optionally views all the information avaliableabout a selected taxi driver
Preconditions:
1. Manager is logged into Taxi System
2. The ID that the manager wants to consult must exists.
Postconditions:
1. All information about the selected ID is showed.
Actor Actions System actions
Normal Course:
1. Ask to view the information about onedriver
2. System ask for specified the ID fromthe taxi driver
3. Insert the ID from a taxi driver and indicatethat is complete
4. Displays the following data thatbelongs at the ID specified (name,surname, DNI, address, telephon1,telephon2, email, initial_date, final_date,numberTaxi).
Exceptions
E. 5The selected ID doesnt exists
Inform Manager that the selected IDdoesnt exists
Select another ID.
Priority: Very low
Frequency of use: Approximately 5 user, average of 1 usages perweek
Business rules: BR06, BR07
Special requirements:
Page: 38/80
-
8/3/2019 Taxi Db29b
39/80
Project: Taxi system
4. Conceptual modeling
4.1. Business rules
4.1.1. BR1
One customercan request zero or many orders for a determinated dates, sourcesand locations and one order only belongs to one customer.
4.1.2. BR2
One orderis attended by one taxi and one taxi can attend zero or many orders.
4.1.3. BR3
One taxi driver pick up many travels and one travel is did only by one taxi driver.
4.1.4. BR4
One taxi follow one schedule and one schedule can be followed by zero or many taxidrivers.
4.1.5. BR5
Zero or many taxis are situated in one station and one Taxi only can be situated inone (or zero) stations in a determinated time.
4.1.6. BR6
The customers and the workers has shared attributes because both are persons, buteveryone is a different group of persons with different roles, for that reason has alsosome attributes that are differents and are used for distinguish between them.
4.1.7. BR7
The taxi drivers and the managers (both) work for the taxi service and has similardata (both are workers), but everyone is a different group of persons with differentroles.
4.1.8. BR8
One schedule is composed by many (or zero) days and one day belongs only to oneschedule.
Page: 39/80
-
8/3/2019 Taxi Db29b
40/80
Project: Taxi system
4.2. Conceptual data model
This chapter defines the different classes and shows the class diagram giving us the
relationship between these entities.
1. Class diagram
Order: is a request from one person (customer or worker) for move from onesite (source) to another (destination) by a selected (by the manager) taxi.
Travel: one travel is a kind of order (have the same data) but the taxi driveris who create and manage the travel.
Person: is one group of users (humans) that interact with the databases.
Customer: is one kind of person that use the taxi driver for move from onesite to another site.
Worker: is one kind of person that works in the taxi service (manager, taxidriver....)
TaxiDriver: is one kind of worker that works driving a taxi, bringing peoplefrom one site to another.
Manager: is one kind of worker that make the logistic work for the taxi
Page: 40/80
-
8/3/2019 Taxi Db29b
41/80
Project: Taxi system
service.
Schedule: one calendar, which every day, has the hours that the taxi is ableor not to work.
Day:
Stations: one specific location where the taxi drivers wait for them turn.
Page: 41/80
-
8/3/2019 Taxi Db29b
42/80
Project: Taxi system
4.2.1. Data dictionary
travel: {id, deliveredDateDay, deliveredDateTime, source, destination,
distanceKM, lenghtTime, Tariff, finalPrice}
One travel is created by a taxi driver when one person request the taxidirectly (in the street) and the person define at the moment to stop orjoin in the taxi where wants to go/deliver sbd/sth defining at themoment the destination. One Travel has also information about thedistance between the source and the destination, the time that takesthe taxi for translate sb/sth to the destination, the tariff that is applied(see tariff) and the final price that costs that service.
CC: Travel: {id}
Order: { id, createdDate, createdTime, OrderState}
One order is requested by a Customer for recieve one taxi in onesource specified for pick up somebody or something there andtranslate to the specified destination. One Order has also informationabout the state (see order_state) distance between the source and thedestination, the time that takes the taxi for translate sb/sth to thedestination, the tariff that is applied (see tarif) and the final price thatcosts that service.
CC: Order: {(id)}
schedule: {id, description}
One schedule is followed by many taxis and give them the informationabout when they're able to work or they can't work. The schedule hasto specify which days can work the taxi driver that is associated withthem and wich interval of hours.
CC: Schedule {id}
Day: {id, dayWork, hourBegin, hourEnd}
One day has to give the information about when a taxi driver is able towork and when doesn't have to work. The day has to specify whenbegin, finish and some description.
CC: Day {id}station: {id, situation}
One station is one physic place where many taxi drivers wait for themturn.
CC: Station {id}
person: {SSN, name, surname, address, telephon, email}
One person is one generalization of customer and worker, they sharesome information because are humans with shared importantcharacteristics: name, surname, SSN, telephon, email...
CC: person {SSN}
Page: 42/80
-
8/3/2019 Taxi Db29b
43/80
Project: Taxi system
customer: {id, nFakes}
One customer is the person who request a order (see order).
Is one specialization of person giving two more characteristics theid_customer and the number of fakes.
CC: customer {id}
worker: {id, startDay, finalDay}
One Worker is one generalization of taxi_driver and manager, theyshare some information because they're persons and also they workfor Taxi Service.
A worker has a identification and they had joinned in the taxi serviceone determinied date and they will finish (or finished) another date.
taxi_driver: {id, number}
A taxi driver is the person who drive a taxi, many taxi drivers can drivethe same taxi, so they share the number_taxi value.
manager: {id}
One manager is a person who works in the base managing all theinformation in the system, modifying schedules, adding new ordersand consulting and modifying that information.
A manager also recieve calls and convert them to new orders ormaybe modifying the information that it's inside. Is able to consult,
read, update and delete the information inside the system.
4.3. Verification of conceptual model
BR Relation (ass/gen)
BR1 request
BR2 attend
BR3 Pick upBR4 follow
BR5 Is situated
BR6 Person (customer/worker)
BR7 Worker (taxi driver/manager)
BR8 Is composed
Page: 43/80
-
8/3/2019 Taxi Db29b
44/80
Project: Taxi system
5. Transaction specification
5.1. Query operations
5.1.1. Consults order list
Input initial_date, final_date, customer_id
Output List of orders (date, source, destination, state, taxi_drv,
date_delivered)
Description Displays the orders from the actual customer that thecreated_data belongs of the specific range of dates
Uses Cases UC/01
5.1.2. Show one travel
Input travel_id
Output travel (date, source, destination, state, taxi_drv, date_delivered)
Description Return the order that has the id specified.
Uses Cases UC/01, UC/11, UC/12, UC/15, UC/16
5.1.3. Consults all the travels
Input initial_datetime, final_datetime, taxi_driver_id
Output List of travels (date_created, date_delivered, source,destination, distance, tariff, price)
Description Displays all the travels that were created in the selecteddata and belongs to the actual taxi driver
Uses Cases UC/05
5.1.4. Sum all prices and distances
Input initial_datetime, final_datetime, taxi_driver_id
Output total_distances, total_prices
Description Sum all the values of all the selected travels/orders (thatwere created in the selected data and belongs to the actualtaxi driver)
Uses cases UC/05
Page: 44/80
-
8/3/2019 Taxi Db29b
45/80
Project: Taxi system
5.1.5. Consults schedule
Input id_schedule
Output Schedule (day, time_ini, time_final)
Description Displays the information about the schedule
Uses Cases UC/06, UC/13
5.1.6. Consults all orders waiting
Input initial_datetime, final_datetime
Output List of orders (order_id, client_id, source, date, created_date)
Description return all the orders waiting that the data_created belongs tothe specified range of dates
Uses cases UC/10
5.1.7. Consults the information about one manager
Input managerId
Output Person (name, surname, DNI, address, telephon1, telephon2,email, initial_date, final_date)
Description Display all the information about the selected worker
Uses Cases UC/17
5.1.8. Consults the information about one customer
Input customer_ID
Output Customer (name, surname, DNI, address, telephon1,telephon2, email, n_fakes)
Description Display all the information about the selected customer
Uses Cases UC/20
5.1.9. Consults the information about one taxi driver
Input taxiDriver_ID
Output Customer (name, surname, DNI, address, telephon1,telephon2, email, n_fakes)
Description Display all the information about the selected customer
Uses Cases UC/21
5.1.10. Consult who is the first in one station
Input id_station
Output Taxi (id_taxi, date_joined)
Description Return who is the first in one specified station
Page: 45/80
-
8/3/2019 Taxi Db29b
46/80
Project: Taxi system
Uses cases UC/12
5.1.11. Consults who is in one station
Input Station_ID
Output List of taxi drivers (id_taxi, date_joined)
Description Shows all the taxi drivers that are waiting in the station
Uses cases UC/18
5.2. Transactions
5.2.1. Modify one order
Input id_order, source, destination, date_request
Output Order (source, destination, date_request)
Description Update one order with the inputs into the database
Uses Cases UC/02, UC/08, UC/11
5.2.2. Delete one order
Input id_order
Output
Description Delete the specified order of the system.
Uses Cases UC/03
5.2.3. Add new travel
Input datetime_created, source, destination, tariff, price
Output Travel (source, destination, tariff, datetime_created, price)
Description Store the new travel in the database and return theinformation saved
Uses Cases UC/04
5.2.4. Change state to busy
Input id_taxi
Output
Description Change the state of taxi_driver to busy
Uses cases UC/12, UC/04
5.2.5. Join in a station
Page: 46/80
-
8/3/2019 Taxi Db29b
47/80
Project: Taxi system
Input taxi_id, Station_ID
Output List of taxis in the station (taxi_id, Name, surname)
Description Stores the taxi driven in the station (in the last position)Uses cases UC/08
5.2.6. Left the actual station
Input id_taxi, id_station
Output
Description The system delete the taxi driver from the station_queue
Uses Cases UC/09
5.2.7. Change state to freeInput id_taxi
Output
Description Change state_taxi to free
Uses cases UC/09
5.2.8. Modify one order waiting
Input id_order, state, source, destionation, datetime_r_deliver
Output Order (id_order, state, source, destination,datetime_r_deliver)
Description Update the selected order with the inputs
Uses cases UC/12, UC/11
5.2.9. Assign one order to the first taxi of one station
Input id_order, id_station
Output Order (taxi_drv, state, source, destination,datetime_r_deliver)
Description Update the selected order, the taxi_delivered is changed bythe first taxi that it's situated in the frist position of theselected station, the taxi driver is deleted from the stationand his state change to busy
Uses cases UC/12
5.2.10. Assing one order to one taxi driver
Input id_order, id_taxi
Output Order (source, destination, date_r_deliver, taxi_drv)
Description Update the value of taxi_drv of the selected order puttingthe id_taxi (input).
Page: 47/80
-
8/3/2019 Taxi Db29b
48/80
Project: Taxi system
Uses cases UC/12
5.2.11. Modify one schedule
Input id_schedule, day, initial_time, final_time
Output Schedule (id_schedule, day, initial_time, final_time)
Description Update the selected schedule with the input data.
Uses cases UC/14
5.2.12. Order a taxi
Input id_customer, source, destionation, datetime_r_deliver
Output Order (id_customer, source, destination, datetime_r_deliver,state)
Description Stores the new order in the databases and put in state ofwaiting.
Uses Cases UC/19
Page: 48/80
-
8/3/2019 Taxi Db29b
49/80
Project: Taxi system
6. CRUD matrix
Order Customer Travel Taxi Schedule Station Manager
Consults order list R
Modify one order R,U
Delete one order U
Add new travel C U
Consults all travels R
Consult schedule R R
Modify actual order U
Join in a station U C
Left the actual station U D
Consults all orderswaiting
R
Modify data about oneorder
U U*
Assign one order U U R,D
Consults one schedule R
Modify one schedule R,U
Consults all ordersfrom one customer
R R
Delete one order R,D
Consults theinformation about onemanager
R
Consults who is in onestation
R R
Order a taxi C
Consults the
information about onecustomer
R
Consults theinformation about onetaxi driver
R
Page: 49/80
-
8/3/2019 Taxi Db29b
50/80
Project: Taxi system
7. Logical modeling
Here we can see the PK (and FK) selecteds of the set of CC.
Standard SQL.
Page: 50/80
-
8/3/2019 Taxi Db29b
51/80
Project: Taxi system
8. Nonfunctional requirements
8.1. Performance requirements
8.1.1. PE/01
The system shall accomodate 560 users during a peak usage time window of 3:00AM to 6:00 AM local time, with estimated average session duration of 2 minutes.
8.1.2. PE/02
Responses to querys shall take no longer than 7 seconds to load onto the screen
after user submits a query.
8.1.3. PE/03
The system shall display confirmation messages to user within 4 seconds after theuser submits information to the system.
8.2. Security Requirements
8.2.1. SE/01
Users shall be required to log in to the Taxi system for all operations.
8.2.2. SE/02
The system shall permit customer to view only their own orders, not orders placedby other customers
8.2.3. SE/03
The system shall permit to taxi drivers to view only their own travels, not the travels
by other taxi drivers.
8.2.4. SE/04
The system shall permit to taxi drivers to view only their schedule, not theschedules by others taxi drivers.
8.2.5. SE/05
One customer cannot create one order for another customer, only can create theirown orders.
8.2.6. SE/06
One taxi driver only can change his own and actual order while is doing it.
Page: 51/80
-
8/3/2019 Taxi Db29b
52/80
Project: Taxi system
8.2.7. SE/07
One taxi driver only can change his own and actual travel while is doing it.
8.3. Availability
The taxi system shall be avaliable to users 90% of the time at winter but 96% of thetime at summer.
9. Physical data model
For implement that project is used MS SQL 2008, the following chapters will give theinformation about the tables, data, constraints, results and conclusion.
9.1. Considerations
MS SQL 2008 give us one environment for built our project with a lot of tools (forexample new types like Datetime).
In the example of Datetime, if we focus on the entity Day, now we don't need adateDay attribute because it can be stored with the time at same time and willconvert the definition of the attributes to: DatetimeBegin, DatetimeEnd, Description.
The same idea is for the array of characters that will convert to VARCHAR of somedefinied length.
9.2. Constraints
Some of the constraints are defined implicitly in the conceptual model, in the actualchapter will be defined that constraints and some new.
9.2.1. C/01
One Schedule is composed by Days.
By definition, in one composite relation, the lifetime of Day (in hour example) is inthe lifetime of Schedule, Day could only exists when the specified Schedulerelationated with it exists and if that Schedule dead (arrive at the final of thelifetime) the Day will dead also because cannot exists if no exists Schedule.
The shared lifetime constraint is solved using a necessary attribute, that will givealso the information about the relation, wich days are relationed with wich Schedulethat will be a reference to Schedule using the value Id.
For solve the problem on updates and deletes in the Schedule will use thestatements ON UPDATE CASCADE, ON DELETE CASCADE that will terminate the life
of a Day if a Schedule arrives to the final of its lifetime.
9.2.2. C/02
Page: 52/80
-
8/3/2019 Taxi Db29b
53/80
Project: Taxi system
One travel is picked up by only one and specified taxy driver.
New attribute taxiId is necessary in the entity travel that will be referenced usingthe value of the taxiDriver that pick up the travel.
9.2.3. C/03
One Order is requested by a specified customer, for that reason, the order will needone new attribute that will give that relation and solve that problem,
9.2.4. C/04
Customer and worker are specifications from Person there are incomplete, thatallow us to create Persons that are not customers and neither worker, but is disjoint,when one customer exists that customer can't be at the same time a worker, and
viceversa.
9.2.5. C/05
One Order is a specification of Travel they share attributes, relations... the relation isincomplete, one Travel can exists because all the possible instances are not coveredby the specifications, but the specifications (travel in this moment) are disjoint, ifanother exists in the future it will be not possible to exist one Order and one Travelin the same time.
9.2.6. C/06The relation between one taxiDriver and one Station give us one datetime that willgive the information when a taxiDriver arrived and if that is the first in the station ornot.
It can exists that one taxiDriver is not situated in one Station, for that reason theattribute idStation that gives that relation can be null.
9.2.7. C/07
The possible states (only can exists) of one Order are: waiting, assigned, doing, did,fake.
9.2.8. C/08
One taxi driver cannot have more than one order/travel not finished.
9.2.9. C/07
One customer with more than 3 (set default) fakes cannot request new taxi services.
9.2.10. C/08
One taxi driver cannot change to state free while is doing a travel/order.
Page: 53/80
-
8/3/2019 Taxi Db29b
54/80
Project: Taxi system
9.2.11. C/09
When one taxi accepts a order or create a travels, the state changes automaticallyto busy.
9.2.12. C/10
If one taxi driver is not free or queue cannot recieve orders.
9.2.13. C/11
One taxi driver cannot join in a station if before is not at the state free.
9.2.14. C/12
When one determinated fake is changed to fake, the customer that is relationedwith that order will be penalized with increment by 1 in the nFakes.
*if the state is changed to fake once time and later change to another state, thecustomer will have still the increment by 1 in the nFakes by the specified order,otherwise, the state of the order is different to fake.
9.2.15. C/13
The possibles states of a Taxi driver are the following: {free, queue, busy, stop}
Page: 54/80
-
8/3/2019 Taxi Db29b
55/80
Project: Taxi system
9.3. Physical model diagram
9.4. Database's definition
MS SQL 2008 has types like DATETIME that will change the definition gived by theconceptual model for transform in the physical model.
9.4.1. Creating tables
--Taxi system creating tables--Person--A person is a generalization incomplete from a Worker and a Customer--for that reason can exists persons that are not in the range of--instances of Customer and Worker.CREATETABLE Person(
SSN INTPRIMARYKEY,nameVARCHAR(20)NOTNULL,surnameVARCHAR(20)NOTNULL,addressPVARCHAR(250)NOTNULL,telephon INTNOTNULL,
Page: 55/80
-
8/3/2019 Taxi Db29b
56/80
Project: Taxi system
emailVARCHAR(30)NOTNULL)-- Customer is a specification from PersonCREATETABLE Customer(
customerId INTPRIMARYKEY,nFakes INTNOTNULL,
)-- a Worker is a specification from Person-- also is a generalization of taxiDriver and a Manager-- is incompleteCREATETABLE Worker(
workerId INTPRIMARYKEY,startDay DATETIMENOTNULL,finalDay DATETIMENULL,
)-- a taxiDriver is a Specification of Worker
-- the specifications are disjointsCREATETABLE TaxiDriver(taxiId INTPRIMARYKEY,taxiNumber INTNOTNULL,stateTaxiVARCHAR(10)NOTNULL
)-- a Manager is a specification of Worker-- the specification are disjointsCREATETABLE Manager(
managerId INTPRIMARYKEY,)
--Travel is a generalization of OrderTaxi-- the generalization is incomplete and disjoint
CREATETABLE Travel(travelId INTPRIMARYKEY,DeliveredDate DATETIMENULL,sourceTravelVARCHAR(250)NOTNULL,destionationTravelVARCHAR(250)NULL,distanceKM INTNULL,lenghtTime INTNULL,tariffINTNULL,finalPrice INTNULL
)-- OrderTaxi is a specification of Travel-- is incomplete.CREATETABLE OrderTaxi(
orderId INTPRIMARYKEY,CreatedDate DATETIMENOTNULL,orderStateVARCHAR(10)NOTNULL,
)
CREATETABLE Station(stationId INTPRIMARYKEY,situationVARCHAR(250)NOTNULL
)--one schedule has parts WorkDayHour, that indicates one period--of time when the taxi is able to work.CREATETABLE WorkDay(
dayId INTPRIMARYKEY,
beginAt DATETIME,finishAt DATETIME,descWorkDayVARCHAR(250),
)
Page: 56/80
-
8/3/2019 Taxi Db29b
57/80
Project: Taxi system
CREATETABLE Schedule(scheduleId INTPRIMARYKEY,descScheduleVARCHAR(250)
)
9.4.2. Constraints
-- Customer is a specification from PersonALTERTABLE Customer
ADD SSN INTREFERENCES PersonONDELETECASCADEONUPDATECASCADE
NOTNULL
-- a Worker is a specification from Person-- also is a generalization of taxiDriver and a Manager-- is incompleteALTERTABLE Worker
ADD SSN INTREFERENCES PersonONDELETECASCADEONUPDATECASCADE
NOTNULL
-- a taxiDriver is a Specification of Worker-- the specifications are disjointsALTERTABLE TaxiDriver
ADD workerId INTREFERENCES WorkerONDELETECASCADEONUPDATECASCADE
NOTNULL
-- a Manager is a specification of Worker-- the specification are disjointsALTERTABLE Manager
ADD workerId INTREFERENCES WorkerONDELETECASCADEONUPDATECASCADE
NOTNULL--in orderTaxi, the attribute state order only can have the values-- waiting, assigned, doing, did or fake.ALTERTABLE OrderTaxi
ADDCONSTRAINT "stateOrder" CHECK(orderState IN
('waiting','assigned','doing','did','fake'))
--relation between customer and travel 1,* in the previous order:ALTERTABLE OrderTaxi
ADD customerId INTREFERENCES CustomerNOTNULL--RELATION between taxi and travel 1,* in the previous order:ALTERTABLE Travel
ADD taxiId INTREFERENCES TaxiDriver
--relation between taxi and station *,0..1
ALTERTABLE TaxiDriverADD StationId INTNULLREFERENCES Station
--relation between schedule and WorkDay
Page: 57/80
-
8/3/2019 Taxi Db29b
58/80
Project: Taxi system
ALTERTABLE WorkDayADD ScheduleId INTREFERENCES ScheduleONDELETECASCADE
NOTNULL
--relation between schedule and taxiDriverALTERTABLE TaxiDriver
ADD ScheduleId INTREFERENCES ScheduleONDELETECASCADEONUPDATECASCADE
NOTNULL--relation between travel and orderALTERTABLE OrderTaxi
ADD TravelId INTREFERENCES TravelONDELETECASCADEONUPDATECASCADE
NOTNULL
--attribute relation between station and taxiALTERTABLE TaxiDriver
ADD dateJoined DATETIMENULL
--one taxi driver only can have the following statesALTERTABLE TaxiDriver
ADDCONSTRAINT "TaxiState" CHECK(stateTaxi IN('free','queue','busy','stop'))
9.4.3. Query operations
--consults order listCREATEFUNCTION ConsultOrderList(@customer_id INT)
RETURNSTABLEASRETURNSELECT createdDate, Travel.sourceTravel,
Travel.destionationTravel, orderState, taxiId, deliveredDateFROM OrderTaxi, TravelWHERE OrderTaxi.customerId = @customer_idAND
OrderTaxi.TravelId=Travel.TravelId
CREATEFUNCTION ConsultOrderListDates(@customer_id INT, @initial_date DATETIME, @final_date DATETIME)
RETURNSTABLEASRETURNSELECT createdDate, Travel.sourceTravel,
Travel.destionationTravel, orderState, taxiId, deliveredDateFROM OrderTaxiJOIN Travel ON OrderTaxi.TravelId=Travel.TravelIdWHERE OrderTaxi.customerId = @customer_idAND
CreatedDate>=@initial_dateAND CreatedDate
-
8/3/2019 Taxi Db29b
59/80
Project: Taxi system
WHERE travelId=@travel_id
-----------------------------------------------------------------------------------
--Consults all the travelsCREATEFUNCTION consultTravels(@taxi_driver_id INT, @initial_date DATETIME, @final_date DATETIME)RETURNSTABLERETURNSELECT DeliveredDate, sourceTravel, destionationTravel, distanceKM, tariff,finalPrice
FROM TravelWHERE Travel.taxiId = @taxi_driver_idAND DeliveredDate>=@initial_date
AND DeliveredDate=@initial_dateAND
deliveredDate
-
8/3/2019 Taxi Db29b
60/80
Project: Taxi system
SELECT orderId, Travel.travelId, sourceTravel, destionationTravel,OrderTaxi.CreatedDate, OrderTaxi.customerId
FROM TravelJOIN OrderTaxi ON (Travel.travelId=OrderTaxi.TravelId)WHERE orderState='waiting'
------------------------------------------------------------
--consults the information about one managerCREATEFUNCTION ConsultManager(@manager_id INT)RETURNSTABLERETURN
SELECT Person.SSN, Person.name, Person.surname, Person.addressP,Person.telephon, Person.email,
Worker.startDay, finalDay, Worker.workerId
FROM Manager INNERJOIN Worker ON Manager.workerId=Worker.workerIdINNERJOIN Person ON(Worker.SSN=Person.SSN) WHEREManagerId=@manager_id
-----------------------------------------------------------------------------------
--consults the information about one Taxi driverCREATEFUNCTION ConsultTaxiDriver(@taxi_id INT)RETURNSTABLERETURN
SELECT Person.SSN, Person.name, Person.surname, Person.addressP,
Person.telephon, Person.email,Worker.startDay, finalDay, Worker.workerId, TaxiDriver.taxiNumber,TaxiDriver.stateTaxi, TaxiDriver.StationId, TaxiDriver.dateJoined
FROM TaxiDriver INNERJOIN Worker ONTaxiDriver.workerId=Worker.workerId
INNERJOIN Person ON(Worker.SSN=Person.SSN) WHERE taxiId=@Taxi_id
-------------------------------------------------------------------------------
--consults the information about one customerCREATEFUNCTION ConsultCustomer(@customer_id INT)
RETURNSTABLERETURN
SELECT Person.SSN, Person.name, Person.surname, Person.addressP,Person.telephon, Person.email,
customer.nFakesFROM Customer INNERJOIN Person ON(Customer.SSN=Person.SSN)WHERE customerId=@customer_id
----------------------------------------------------------------------------
--consults who is the first in one station
CREATEFUNCTION firstStation(@id_station INT)RETURNSTABLERETURN
Page: 60/80
-
8/3/2019 Taxi Db29b
61/80
Project: Taxi system
SELECT taxiId,taxiNumber,dateJoinedFROM TaxiDriverWHERE StationId=@id_stationAND dateJoined =(SELECTMIN(dateJoined)FROM TaxiDriver WHERE
StationId=@id_station)
----------------------------------------------------------------------
--consults who is in one stationCREATEFUNCTION whoStation(@id_station int)RETURNSTABLERETURN
SELECT taxiId, dateJoinedFROM TaxiDriver
WHERE StationId=@id_station
9.4.4. Transactions
--modify one orderCREATEPROCEDURE ModifyTravel(@travel_id INT,@delivered_date DATETIME,@source_travelVARCHAR(250),@destination_travelVARCHAR(250),@distanceKM INT,
@lenght_time INT,@tariffINT,@final_price INT)ASBEGIN
UPDATE TravelSET DeliveredDate=@delivered_date,sourceTravel=@source_travel,destionationTravel=@destination_travel,distanceKM=@distanceKM,lenghtTime=@lenght_time,tariff=@tariff,finalPrice=@final_price
WHERE travelId=@travel_idEND
--delete one orderCREATEPROCEDURE DeleteOrder(@id_order INT)ASBEGIN
DELETEFROM Travel WHERE travelId =(SELECT travelId FROM OrderTaxi WHERE orderId=@id_order)
END
--delete one travel
CREATEPROCEDURE DeleteTravel(@id_travel INT)ASBEGIN
Page: 61/80
-
8/3/2019 Taxi Db29b
62/80
Project: Taxi system
DELETEFROM Travel WHERE travelId=@id_travelEND
--add new travel
CREATEPROCEDURE AddTravel(@travelId INT,@delivered_date DATETIME,@source_travelVARCHAR(250),@destination_travelVARCHAR(250),@distance_KM INT,@lenght_time INT,@tariffINT,@final_price INT,@taxi_driver_id INT)AS
BEGININSERTINTO TravelVALUES(@travelId,@delivered_date,@source_travel,@destination_travel,@distance_KM,@lenght_time,@tariff,@final_price,@taxi_driver_id)END
--change state to busyCREATEPROCEDURE stateBusy(@taxi_id INT)ASBEGIN
UPDATE TaxiDriverSET stateTaxi='Busy'
WHERE taxiId=@taxi_idEND
--join in a stationCREATEPROCEDURE JoinStation(@taxi_id INT,@station_id INT)ASBEGIN
DECLARE @stateTaxiVARCHAR(10)SET @stateTaxi=(SELECT stateTaxi FROM TaxiDriver WHERE taxiId=@taxi_id)IF @stateTaxi='free'BEGIN
BEGINTRANSACTION TranJoinStationUPDATE TaxiDriverSET stateTaxi='Queue',
stationId=@station_id,dateJoined=CURRENT_TIMESTAMP
WHERE taxiId=@taxi_idPRINT'the taxi '+CAST(@taxi_idASVARCHAR(10))+' joinned
correctly in the station '+CAST(@station_idASVARCHAR(10))COMMITTRANSACTION TranJoinStation
ENDELSE
PRINT'the taxi '+CAST(@taxi_idASVARCHAR(10))+'cannot join in astation because is not in state FREE'
END
--left one stationCREATEPROCEDURE leftStation
Page: 62/80
-
8/3/2019 Taxi Db29b
63/80
Project: Taxi system
(@taxi_id INT)ASBEGIN
UPDATE TaxiDriver
SET stateTaxi='free',StationId=NULL,dateJoined=NULL
WHERE taxiId=@taxi_idPRINT'The taxi Driver '+CAST(@taxi_idASVARCHAR(10))+' is now in state
Free'END
--left one station but with state busyCREATEPROCEDURE leftStationBusy(@taxi_id INT)AS
BEGINUPDATE TaxiDriverSET stateTaxi='busy',
StationId=NULL,dateJoined=NULL
WHERE taxiId=@taxi_idEND
--change state to freeCREATEPROCEDURE freeState(@taxi_id INT)ASBEGIN
DECLARE @ActualTravel INTSET @ActualTravel=(SELECT*FROM GetActualTravel(@taxi_id))IF @ActualTravel ISNULLBEGIN
EXECUTE leftStation@taxi_idENDELSEBEGIN
PRINT'cannot change the state to free because is doing a travel'END
END
--modify order
CREATEPROCEDURE ModifyOrder(@id_order INT,@orderStateVARCHAR(10),@deliveredDate DATETIME,@sourceTravelVARCHAR(250),@destinationTravelVARCHAR(250),@distanceKM INT,@lenghtTime INT,@tariffINT,@finalPRice INT)ASBEGIN
UPDATE OrderTaxi
SET orderState=@orderStateWHERE orderId=@id_order
declare @travelId INT
Page: 63/80
-
8/3/2019 Taxi Db29b
64/80
Project: Taxi system
SET @travelId=(SELECT travelId FROM OrderTaxi WHEREorderId=@id_order)
Update Travel
SET DeliveredDate=@deliveredDate,sourceTravel=@sourceTravel,destionationTravel=@destinationTravel,distanceKM=@distanceKM,lenghtTime=@lenghtTime,tariff=@tariff,finalPrice=@finalPRiceWHERE travelId=@travelId
END
--assign one order to the first taxi of one stationCREATEPROCEDURE AssingOrderStation
(@id_order INT,@id_station INT)ASBEGIN
declare @taxi_id INTset @taxi_id=(SELECT taxiId FROM firstStation(@id_station))PRINT'The first in the station '+CAST(@id_stationASVARCHAR(10))+' is taxi
id '+CAST(@taxi_idASVARCHAR(10))EXECUTE AssignOrderTaxi@taxi_id,@id_order--when call to AssignOrderTaxi the taxi is automatically boot from the station--EXECUTE leftStationBusy @taxi_id
END
--assign one order to one taxi driverCREATEPROCEDURE AssignOrderTaxi(@id_order INT,@id_taxi INT)ASBEGIN
--check if the actual order is waitingDECLARE @orderStateVARCHAR(10)SET @orderState =(SELECT orderState FROM OrderTaxi WHERE
orderId=@id_order)IF @orderState='waiting'BEGIN
--get the travelId from the selected orderDECLARE @travel_id INTSET @travel_id=(SELECT travelId FROM OrderTaxi WHERE
orderId=@id_order)PRINT'order id '+CAST(@id_orderASVARCHAR(10))+' is travel
'+CAST(@travel_idASVARCHAR(10))--look for the state of the taxiDECLARE @stateTaxiVARCHAR(10)SET @stateTaxi=(SELECT stateTaxi FROM TaxiDriver WHERE
taxiId=@id_taxi)--taxi must be free or queueIF @stateTaxi IN('free','queue')BEGIN
--assign the travelUPDATE TravelSET taxiId=@id_taxi,DeliveredDate=CURRENT_TIMESTAMP
Page: 64/80
-
8/3/2019 Taxi Db29b
65/80
Project: Taxi system
WHERE travelId=@travel_id
--the travel is assigned put in the stateUPDATE OrderTaxi
SET orderState='assigned'WHERE orderId=@id_order
--the taxi driver now is busyEXECUTE leftStationBusy@id_taxiPRINT'the travel '+CAST(@travel_idASVARCHAR(10))+' was
assigned correctly to taxi '+CAST(@id_taxiASVARCHAR(10))ENDELSE
PRINT'The taxi '+CAST(@id_taxiASVARCHAR(10))+' is not instate free/queue'
END
ELSE PRINT'the actual order is not waiting, cannot assign'END
--order a taxiCREATEPROCEDURE OrderATaxi(@travelId INT,@sourceTravelVARCHAR(250),@destinationVARCHAR(250),@orderId INT,@customerId INT)AS
BEGININSERTINTO TravelVALUES(@travelId,null, @sourceTravel, @destination,null,null,null,null,null)
INSERTINTO OrderTaxiVALUES (@orderId,CURRENT_TIMESTAMP,'waiting',@customerId, @travelId)END
9.4.5. Security (Implementation and tests)
--creating all users of our database (now without login)--at final implementation with login
--if we want to create login--CREATE LOGIN WITH PASSWORD = ''MUST_CHANGE;CREATEUSERCustomer WITHOUT LOGIN;CREATEUSERCustomer2 WITHOUT LOGIN;CREATEUSERTaxiDriver WITHOUT LOGIN;CREATEUSERTaxiDriver2 WITHOUT LOGIN;CREATEUSERManager WITHOUT LOGIN;
--for relationate one person with one userALTERTABLE PersonADD userNameVARCHAR(30)
--supposing at least the following data in the database
INSERTINTO PersonVALUES(0,'Customer','surCustomer','testadress',971331233,'test@address.te','Customer')INSERTINTO CustomerVALUES(0, 0, 0)
Page: 65/80
-
8/3/2019 Taxi Db29b
66/80
Project: Taxi system
INSERTINTO PersonVALUES(1,'Customer2','surCustomer2','testaddress',971336666,'test2@address.te','Customer2')INSERTINTO CustomerVALUES(1, 0, 1)
INSERTINTO ScheduleVALUES (0,'dont work on friday and monday')INSERTINTO ScheduleVALUES (1,'dont work on tuesday and wednesday')
INSERTINTO WorkDay(0,'2009-02-06 08:52:41.080','2009-02-06 18:52:41.080','works ten hours in the station 0', 0
top related