To,
Dr. Syed Jamal Hussain / Mr. Muhammad Faizan DBMS Teachers PAF-Kiet City Campus, Shahrah-e-Faisal, Karachi.
Subject:
Letter of Transmittal
Dear Sirs,
“ Bus/Traffic It is pleased to submitting to you, our project report named “ Bus/Tra ffic Transport Management System” System” in Karachi for your consideration. This report comprehensively addresses its terms of references and examines a range of operational issues which have been facing by passengers and transporters transporters in our daily routine life
We commend this report to you and the PAF-Kiet PAF- Kiet Management for the acceptance.
Best regards,
Project By:
Abdul Jamil Jamil Siddiqui
BUS/TRAFFIC TRANSPORT MANAGEMENT SYSTEM Project Report Project By:
Abdul Jamil Siddiqui
DBMS PROJECT REPORT
Acknowledgment We consider our self-fortunate of having the privileges to have been guided by the knowledge possessed by our course teachers “Mr. Muhammad Faizan & Dr. Syed Jamal Hussain ” and their valuable supports, advice & encouragement that were offered all throughout & helps us to made it all possible.
Table of Contents:
List of Tables:............................................................................................................................................... 4
List of Figures: ............................................................................................................................................. 6
Glossary (List of Abbreviations):.................................................................................................................. 6
Executive Summary: .................................................................................................................................. 12
Introduction: ............................................................................................................................................. 12
Objective: Objectiv e: .................................................................................................................................................. 13
Scope & Limitations: ................................................................................................................................. 14
Project Projec t Overview: Ov erview: ...................................................................................................................................... ...................................................................................................................................... 14
Problems: Prob lems: .................................................................................................................................................. 15
Solutions: .................................................................................................................................................. 16
Requirements: Requir ements: ........................................................................................................................................... 17
Bibliography (Source of Data): .................................................................................................................. 17
Tools Used: ................................................................................................................................................ 18
Data Flow Diagram (DFD): ......................................................................................................................... 19
Entity Relational Diagram (ERD) / Fully Fu lly Attributed ERD / Data Modeling: ........... ................. ........... ........... ............. ............. ............ ........ 20
Functional Functio nal Details: ..................................................................................................................................... 21
Queries & their results (Reports): ............................................................................................................. 21
Conclusion: Conclus ion: ................................................................................................................................................ 25
Recommendations (Suggestions): ............................................................................................................. 25
Appendices: Append ices: ............................................................................................................................................... 25
Contributions of Group Members in making of Project Report:............... Report:..................... ........ Error! Bookmark not defined.
List of Tables: S.No.
Table Name
1
tblAT
2
tblAuth
3
tblB
4
tblBBCP
5
tblBC
6
tblBCA
7
tblBCB
8
tblBCO
9
tblBCPO
10
tblBD
11
tblBDM
12
tblBEC
Description / Details All account types authorities which assign to the users of the different category to perform only that operation of the database which they belong like database all tables read, write, update and delete authorities stored in this table e.g. Administrator has h as full database access Authority by default but Checkpoint Officers and Complain Officers has only some type of authority by default. All users with their passwords and account type who authenticated by database administrator stored in this table. (Note: Only database administrator has authority to add new users by default) All Buses information with their common attributes like their plate no, type, ASSIGN; driver & conductor and route and begin & ending check point and expense debit card, date of working in progress and total number of complain stored in this table. All those checks points (bus stops) information where bus routes rout es starts stored in this table. All bus conductors condu ctors information with their common attributes like their name, CNIC, residence and permanent address, contact no., education, date of joining, experience, salary, and number of complains stored in this table. All types of those entities who could be Complaint by complainer stored in this table. All types of complainant who can be complain stored in this table. All bus complain officers o fficers information with their common attributes like their name, CNIC, residence and permanent address, contact no., education, date of joining, experience, salary, and number of complains stored in this table. All bus check point officers information with their common attributes like their name, CNIC, residence re sidence and permanent address, contact no., education, date of joining, experience, salary, and number of complains stored in this table. All bus drivers information with their common attributes like their name, CNIC, residence and permanent address, contact no., license no, education, date of joining, experience, salary, and number of complains & penalties stored in this table. This is the main table where all buses daily departure information with their common attributes like buses plate no, date & time, no of cycle & trip, no of o f total issued distance wise and over all fare tickets with their top most no. and before journey fuel quantity stored. All complains information with their common attributes like Complaint's bus plate no., about & by, date, name, CNIC, address, cell, job, evidence describe and description stored in this table.
13
tblBECP
14
tblBEDC
15
tblBF
16
tblBFT
17
tblBM
18
tblBMT
19
tblBP
20
tblBPF
21
tblBPT
22
tblBR
23
tblBRM
24
tblBS
25
tblBT
All those checks points (bus stops) information where buses routs end stored in this table. All bus expense debit cards information with their common attributes like their balances, and Bank Name stored in this th is table. All bus fuel expense bill slips information with their common attributes like filling date (through tblBRM ID), fuel type, no of expense debit card, purchase quantity and amount, and bill no stored in this table. All bus fuel types’ information with their common attributes like their name and charges rate per liter stored in this table. All bus maintenance expense bill slips information with their common attributes like held date (through tblBRM ID), maintenance type, no of expense debit cards and bill no stored in this table. All bus maintenance type information with their common attributes like their description and charges rate stored in this table. All bus penalty challans slips information with their common attributes like challan date (through tblBRM ID), penalty type, and challan number stored in this table. All feedback of bus passenger’s information with their common attributes like feedback's bus route, date, name, address, contact no., job and comments stored in this table. All bus penalty type information with their common attributes like their description and charges stored in this table. All bus routes information with their common attributes like their total buses, total no of stops, all stops names, minimum & maximum fare, student fare ticket acceptance, total distance, cycle duration, trip duration, daily transportation service service starting & ending time, interval time between two buses, and no of o f passengers feedbacks stored in this table. This is the main table where all buses daily reaching information with their common attributes like buses plate no & no of cycle & trip (through tblBRM ID), date & time, no of total remain distance d istance wise and over all fare tickets with their top to p most no., total no of passengers travelled distance wise and over all, total income distance wise and over all, total to tal fuel remain, fuel filled or not, fuel re-filling no of times, fuel consumption, maintenance done or not, maintenance no of times, penalties done or not, and penalties no o f times stored. All bus stops information with their common attributes like their name and locations stored in this table. All bus types information with their common attributes like their description, no of seats, minimum & maximum capacity and distance wise fare tickets amounts stored in this table.
List of Figures: S.No.
Fig. Name
Description / Details
Page #
1
DFD
Data Flow Diagram
19
2
ERD
Entity Relational Diagram
20
Glossary (List of Abbreviations): A~ AF_Amount Added fuel amount (fuel charges) recorded as expense. B~ BFIncomeAmount_Total Total fare amount of all passengers (whose have travelled in Bus) recorded as Bus income. BFTIssued_Total Total Bus fare tickets which have issued to conductors. conducto rs. BFTRemained_Total Total Bus fare remaining tickets which haven’t sold BM_Charges Bus maintenance charges BR_TotalDistance_Kms Total Distance of bus in kilometers BR_OneCycleDuration_HRS Total cycle duration of bus in hours BR_OneTripDuration_HRS Total trip duration of bus in hours BR_IntervalBetweenTwoBuses_mins Interval between two buses departure d eparture time in hours BRM_BFuelRemain_litres Fuel remain at the end of the cycle in litres BRM_BFuelConsumption_litres Fuel Consume during cycle in liters
BDM_PreviousFuelQuantity_litres Bus previous fuel quantity in litres
C~ CAddress Resident (current) address of Bus drivers, conductors, Check Point Officers& Complain Officers CNIC Computerized National Identity Card Number of Bus drivers, conductors, co nductors, Check Point Officers& Complain Officers D~ DailyBusTEndingTime Daily bus transport ending time (arrival to the stop) DailyBusTStartingTime Daily bus transport starting time (move up from the stop).
E~ Evidence_Describe Describe complain type wise information i.e. Passenger’s evidence is ticket number as he has travelled in the bus i.e. Employee’s evidence is Employee Id as well as Outsider’s evidence is CNIC of minimum two witnesses. F~ FA_NoOfTimes Total number of re-filling of Fuel in the bus FTPer2kmOrLT_A 2 km (kilometer) or less than 2 km Bus fare ticket amount (fare charges) for individual person. FTPer2kmStdOrLT_A 2 km (kilometer) or less than 2 km Bus fare ticket amount (fare charges) for Student. FTPer20kmOrGT_A 20 km (kilometer) or less than 20 km Bus B us fare ticket amount (fare charges) for individual person. FTPer20kmStdOrGT_A 20 km (kilometer) or less than 20 km Bus B us fare ticket amount (fare charges) for Student. Stu dent.
FTPer5km_A 5 km (kilometer) or less than 5 km Bus fare ticket amount (fare charges) for individual person. FTPer5kmStd_A 5 km (kilometer) or less than 5 km Bus fare ticket amount (fare charges) for Student. G~ H~ I~ J~ K~ L~ M~ MD_NoOfTimes Total number of maintenance expenses occurs on the bus. N~ NoOfPassengerT_Total Total number of passengers has travelled in the bus. O~ P~ PAddress Permanent address of Bus drivers, conductors, Check Point Officers& Complain Officers PCapacityMax Maximum capacity of passengers in the bus PCapacityMin Minimum capacity of passengers in the bus PD_NoOfTimes Total number of penalties charged by the bus (Bus (Bu s driver) Q~ R~ RSPerLiter Bus fuel price Pak rupees per Litre. RecordByEach entity has this attribute for recoding the employee Id I d of that User who recorded(Inserted) data of current tuple.
RecordAt Each entity has this attribute for recoding the recording(Inserting) recording(Inserting) Date & Time of current tuple's data. S~ SHFAllowed Student has allowed or not to pay half fare on the bus ticket (i.e. half fare exempted) T~ T2kmFIncomeAmount Total Income fare amount for those Passengers who have travelled 2 km (kilometer) or less than T2kmIssue Total number of tickets issued to the conductor of 2 km (kilometer) for Passengers. T2kmIssuedTNo First top ticket number (unique) issued to the conductor of 2 km (kilometer) for Passengers. T2kmRemain Total number of remaining tickets of 2 km (kilometer) fo r Passengers which haven’t sold
T2kmRemainTNo Remaining first top ticket number (unique) issued issued to the conductor of 2 km (kilometer) for Passengers which haven’t sold. T2kmTNoOfPTravel Total number of Passengers who have travelled for 2 km (kilometer) or less than tblAT Table Account Type tblAuth Table Authentication (i.e. authority assign to the user to perform operation on thedatabase) tblB Table Bus. tblBBCP Table Begin check point of Bus (i.e. Starting Point) tblBC Table Bus conductors.
tblBCA Table Bus complains about i.e. reason why the defaulter going to be register complaint. tblBCB Table Bus complains registered by regarding bus services. tblBCO Table Bus complains officer. tblBCPO Table Bus Check point officers tblBD Table Bus drivers. tblBDM Table Bus departure Master. tblBEC Table Bus employee complains. tblBECP Table Last check point of Bus (i.e. Ending Point) tblBEDC Table Bus expense debit card. tblBF Table Bus fuel bills. tblBFT Table Bus fuel type (i.e. Petrol, CNG, LPG or Diesel). tblBM Table Bus maintenance bills. tblBMT Table Bus maintenance type (i.e. Servicing, Parts changing, Engine repairing). tblBP Table Bus penalty (Challans) tblBPF Table Bus passenger feedback. tblBPT Table Bus penalty type.
tblBR Table Bus routes. tblBRM Table Bus reaching Master tblBS Table Bus Stops. tblBT Table Bus types (i.e. Mini Buses, Coaches, Govt. Buses) B uses) TS2kmFIncomeAmount Total Income fare amount for those Passengers (Student) who have travelle d 2 km (kilometer) or less than TS2kmIssue Total number of tickets issued to the conductor of 2 km (kilometer) for Passengers (Student). TS2kmIssuedTNo First top ticket number (unique) issued to the conductor of 2 km (kilometer) for Passengers (Student). TS2kmRemain Total number of remaining tickets of 2 km (kilometer) for Passengers (Student) which haven’t sold TS2kmRemainTNo Remaining first top ticket number (unique) issued issued to the conductor of 2 km (kilometer) for Passengers (Student) which haven’t sold . TS2kmTNoOfPTravel Total number of Passengers (Student) travelled for 2 km (kilometer). U~ UpdateBy Each entity has this attribute for recoding the employee ID of that User who changing(Updating) data of current selected tuple. UpdateAt Each entity has this attribute for recoding the changing(updating) Date & Time of current selected tuple's data.
V~ W~ X~ Y~ Z~
Executive Summary: In Pakistan especially in Karachi, the local bus transportation system has being running since more than 40-50 years by people’s from North region of Pakistan. They own almost ninety percent (90%) of public transport (i.e. buses) in Karachi. Although they are serving transport to the people of Karachi since many years by providing public transportation but they could not improve their proper record keeping system based because they do not know how to manage it, due to lack of proper management system they could improve neither themselves nor their transportation system. By studying we can fathom how they can manage their record keeping system based in proper manner? This report highlights the problems which have been facing by our transportation system in Karachi regarding maintaining & managing the data day to day and also highl ights their solution. We are going to introduce a complete database system based on Microsoft ® SQL Server 2005 which can provide them a complete solution of their record keeping problems.
Introduction: This project is based on Bus Transportation System. We had worked on the bus transportation system exist in Karachi and tried to make a computerizedmanagement databasesystem named as “Bus Traffic/Transport Traffic/Transport Management System in Karachi”. Kar achi”.
History of Bus Transport System in Karachi is very old& it has been ongoing since more than last 30 years but its current position is worst. Their record keeping system is very bad; they use copies & torn pages to record their day to day workings.
So, now days; ticket is not providing to the passenger against bus fare because they don’t know how to keep record of each ticket. There are no facilities for general public, no proper financing on the bus, Polluting buses, bad attitude of driver & conductor, over loaded buses (limits of Passengers exceeds from capacity in the bus), no respect of traffic laws, rules & regulations, etc.
While making this project; we have focused only right now few things from aforementioned lakes in our bus transport system in Karachi are as follows: foll ows: 1. Record keeping System 2. Ticket System 3. Facilitating to the General Public (i.e. Passengers)
Objective: To obtain or maintain the database named buses / traffic public transport management system by using multiple fields, entities, attributes with different tables in a SQL Server. The core objective objective is to introduce introduce a proper database database system of buses/traffic buses/traffic transportation system in Karachi because our buses transport management do not have any proper/formal system to manage manage day to day business/activity of busessystem in Karachi. Also they have incomplete information/records (for all the drivers, conductors, buses, passengers, fuel stations, buses routes & bus fares & its history) in a proper format which could they manage/update properly. Also, we are going to introduce a ticket system in buses by which a transporter can maintain their income by unique ticket number that will be used as a primary key in respective tables of the said database system to find, call or retrieve a specific record easily when it requires. It (computerized database system) also save their time from seeking the desired records from hand written noting (hard form). This will be a centralized system from which all the transporters can easily connect to it &view all the records (with its history) as per his requirement.
Scope & Limitations: Bus/Traffic Public Transport Management System basically designed for general public so the requirement, queries, problems & their valuable suggestions are most important & not to be negligible. In this regard, we are designing the database where all aforementioned information that will be given from the public or provided to public (after completion of its process) & the same have been recorded(added), updated & then executed(process) when & where it need to implement on the specified criteria or real time bases. It is all because of public only to facilitate to the general public. So, we are maintaining, or creating a database from which user (database administrator) will able to retrieve/extract different queries easily as per his requirement.
Further, we have to decide to gather public’s requirements, problems & issues that was/ar e facing general public in their routine daily journey in the bus & transport system. Also, we are covering the communication level (such as dialog delivery, ask for their destination where their want to reach at their desire time &receive prepaid fare card {fare incase when prepaid fare card is not available} from them as well) of driver & conductor to the public & a good response & communication is expecting from the public also.
Project Overview: This project will emphasize on record keeping system of public transportation system in Karachi-Pakistani. Karachi-Pakistani. The complete database, we h ave designed on Microsoft ® SQL Server 2005. This database will be a centralized computerized system in all over the Karachi to maintain and manage the day to day records of passengers, fuel consumption, bus maintenance and routes as well as Driver’s, Conductor’s, Checkpoint officer’s o fficer’s personal personal records & their duties /tasks. Also, we have introduced a complaint center which will handle all expenses like maintenance of bus, fuel, et c… c… & the same will connect to all the relevant/associated relevant/associated entities like bus owner, fuel companies, repair& maintenance companies.
Microsoft® SQL Server is one of the popular and user friendly but secure database manager that why we choose this software to design this database.
Problems: Our bus transport system does not have any proper management system. Due to absence of daily bus timing records not only transporter is not able to manage bus timings but passengers also suffer from inconsistency of bus timings. A primary aspect in bus system is Ticket system unfortunately our bus system do not has it, because of that sometimes conductor charge freight form a single passenger two times similarly passenger has no proof that he has paid the freight and transporter also has no precise record of o f daily earnings due to no tickets .
Driver’s & Conductor’s personal data are present but not properly recorded i.e. Driving License, CNIC, etc. Due to that transporter is not able to check out their daily performance & duties. Bus numbers like w11, 4m etc. are available but not properly recorded. Bus license number plates
are available but not properly recorded if a bus gets fined by traffic police so it’s difficult for transporter to find out which bus get fined because more than one buses can have same bus numbers but not same license number plate. No record of daily fuel consumption of each bus, fuel is the blood for buses, if transporter do not has its record how would he/she be able to manage the fuel cost? No record of each bus defined routes due to that; passengers cannot find their destination buses easily. Passenger always wants to know what is the fare from his/her bus stop to his/her destination so there is no complete record present of bus fare according to the routes. No proper record of daily number of passengers in each bus if this system is adopted by transporters that would be very helpful for them to manage the quantity of buses on their routes. Day to day there are several ware and tear happened is buses that requires weekly or monthly bus maintenance there is no proper and precise records of this that would be helpful for transporter to manage their maintenance cost. Absence of proper records of drivers, conductors and other staffs salaries. No records of customer feedback that that might be helpful helpful for transporter that what customers want. Our buses are mostly over loaded with passenger a transporter can not aware of this until he/she do not
has proper records of maximum number of capacity in a bus. The dr iver’s performance affects the bus system very much if driver is doing careless driving getting traffic penalties there should be a daily record of his h is performance that can be helpful for transporter to decide drivers future.
Solutions: There should be a computerized database system exist to manage & update all the records. It (database system) should be as centralized to link all the terminals. There should be a database administrator who should be responsible to insert (add), update or delete day to day record in the database. Also, there should be some checkpoints on the bus stops for the bus route equipped with the computer(s) which are connecting through the main server to update day to day record of each bus regarding tickets sold, number of passenger travelled in the bus, route information & fuel slips which are provided from the fuel gas stations after re-filling of fuel in the bus as per its requirement. Ticket system should be mandatory because each ticket has its own unique number with date recorded in the database. At the end of day; sold ticket counts will provide all the information about daily number of passenger & daily earnings against from the same. There should be a Contact Centre where customers could call and put their comments & complaints which is helpful for Owner to get up-to-date aware with the performance of driver, conductor and check point officer even with whole service (which concern to the owner & passenger as well). There should be badge of each &every driver & conductor with its unique Id written on it so it can be ease for DBA to update their daily performance & evaluation in the database.
Requirements:
This Project is required to bus transportation companies which do not have any proper database system.
A Microsoft ® SQL Server 2005 or higher certified or equivalent person required as Administer Database.
CPO (Check Point Officer) required as at least one on each check point for recording the data. They must be computer liberated & familiar to database management system.
Required at least one computer on each check point which must be connected through main database.
There must be a backup server of main database; incase of lost of data by any catastrophe (tragedy), we can retrieve our saved data (backup) from main database. d atabase.
Need to be establishing a contact center (call center) to get comments & complains from the passengers and others on phone calls.
Required at least 10 telephone lines in contact center whose have been connecting through a single UAN or Toll free number.
Debit Card required for each Bus which is given to conductor for Expenses like fuel, maintenance and others.
Bibliography (Source of Data): Following are the sources from where we have collected the data & information which help us to prepare the project report & its database.
Bus Drivers, Bus Owners, Check Point Officers and Conductors i.e.
Name: Name: Location: Location : Contact #: #: Info Collected: Collected:
Mr. Mansib (Check Point Officer) JoharMorr (Gulistan-e-Johar) 0334-3824597 Fare Charges as per Kilometer & Bus Penalties.
Name: Name: Location: Location : Contact #: #: Info Collected: Collected:
Mr. Khair Muhammad (Bus Owner) Sohrab Goth (Al-Asif Square) 0300-2927230 Interval timings between buses, Salaries of Drivers, Conductors & Check Point Officers.
Name: Name: Location: Location : Contact #: #: Info Collected: Collected:
Mr. Jamil (Bus Driver) New Karachi Main Bus Stop 0301-2898085 Bus Routes, Bus Stops & Fare Charges.
Name: Name: Location: Location : Contact #: #: Info Collected: Collected:
Mr. Zubair Khalil (Bus Driver) Keamari Main Bus Stop 0303-2792073 Bus Routes, Fuel System & Traffic Penalties.
Bus Passengers
Website (i.e. Apnakarachi.com)
Tools Used: Following are the tools which we have used to prepared the project report & its database. d atabase.
All Entities (Tables) prepared on Microsoft ® SQL Server 2005.
ERDs (Entity Relational Diagrams) prepared on Microsoft ® SQL Server 2005.
All Quires (Cases) prepared on Microsoft ® SQL Server 2005.
DFD (Data Flow Diagram) prepared on Software Data Modeler version 3.
Project Report prepared on Microsoft ® Word 2007. 20 07.
Data Flow Diagram (DFD):
Entity Relational Diagram (ERD) / Fully Attributed ERD /Data Modeling:
Functional Details: The bus/traffic transport management system can perform following functions:
It can provide a total number of busses moving on roads as on specified time.
It can present a number of busses on the specified & desired bus stop.
Its specialty to show the shortest & nearest path (address, route) to reaching your destination and the suitable bus to arrive you from Point-A (Starting point) to Point-B (End point).
It helps to generate expenses which might be done on bus maintenance.
With this system we can easily find out daily movement mo vement activity consumption of fuel and total ticket sale out (Income).
The Ticket system helps to fetch number of passengers travel in a specified bus.
There would be a DBA (Database Administrator) who will administer the whol e database.
All the Check Points of Buses must be connect with main database in order to record the time to time data in database.
Conductor will collect tickets from check point & return the counter foil of tickets to check point. Conductor will use debit card for fuel & maintenance the bus & return the slip to check point.
Call center will work 18/7 & directly connect to database, all the comments regarding bus service will record from here & info also provide from here h ere to caller.
If Driver get traffic penalty due to his negligence he will pay in cash & submit the slip (challan) to check point.
Queries & their results (Reports): Q1.
select bd_name select bd_name,,bd_caddress, bd_caddress,bd_cellno from tblbd where bd_education= bd_education='matric' This Query displayed names, cell phone numbers & current addresses of those Bus Drivers whose education is Matric.
Q2.
select br_id,,br_allstops from tblbr where br_shfallowed= select br_id br_shfallowed='true' Select bus_Id from Buses where bus_loc between ‘Tower’ to ‘Shahrah -e-Faisal.
This Query displayed bus names (br_id) and all bus stops of each bus in which student fare discount is allowed. Q3.
select bc_name,,bc_cellno, select bc_name bc_cellno,br_id, br_id,b.bedc_id from tblbc c innerjoin tblb b on c.bc_id= bc_id=b.bc_id This Query displayed names, cell phone numbers, bus route id’s (bus names) & debit card numbers related to each Bus Conductor. Conductor.
Q4.
select bcpo_name,,bcpo_cellno, select bcpo_name bcpo_cellno,bcpo_joiningdate from tblbcpo c join tblbbcp p on c.bcpo_id= bcpo_id=p.bcpo_id where bcpo_education= bcpo_education='bcs' This Query displayed names, cell phone numbers, and joining date in company of those check point officers whose education is BCS and posted on begin check point of the bus.
Q5.
select brm_bfincomeamount_total,,br_id from tblbrm r join tblbdm d on select brm_bfincomeamount_total r.bdm_id= bdm_id=d.bdm_id join tblb b on d.b_plateno= b_plateno=b.b_plateno orderby br_id This Query displayed income of each bus per trip according to bus names (bus route ids).
Q6.
select sum( sum(brm_bfincomeamount_total brm_bfincomeamount_total))as "total income" from tblbrm This Query displayed sum total of income of all buses in single trip.
Q7.
select brm_id,,brm_bfincomeamount_total select brm_id brm_bfincomeamount_total,,17 as "sales_tax%", "sales_tax%",brm_bfincomeamount_total brm_bfincomeamount_total*( *(1 1-0.17) 0.17)as "Income_after_tax" from tblbrm This Query displayed total Income of each bus single trip after 17% Income tax deduction.
Q8.
select avg( avg(brm_bfincomeamount_total brm_bfincomeamount_total))from tblbrm This Query displayed average income of all buses in single trip.
Q9.
select min( min(bcpo_salary) bcpo_salary)as "min cpo salary", salary" ,max max((bcpo_salary) bcpo_salary)as "max cpo Salary" from tblbcpo This Query displayed minimum & maximum salary of check point officers.
Q10. select bdm_id select bdm_id,,b_plateno, b_plateno,bdm_btissued_total from tblbdm where bdm_bdeparturedatetime between'1/5/2010 between '1/5/2010 06:00:00'and 06:00:00'and'1/5/2010 '1/5/2010 10:00:00' This Query displayed total number of ticket issued to each e ach bus in 1st May 2010 between 6 am to 10 am. Q11. createview Driver_Info asselect asselect bd_name bd_name,,bd_cellno, bd_cellno,bd_joiningdate, bd_joiningdate,bd_salary from tblbd This Query made a view v iew of cell phone numbers, names, salary & date of joining of the bus drivers with the name of Driver_info.
Q12. createview Check_point_officer asselect asselect bcpo_name bcpo_name as "name", "name",bcpo_cellno as "moble", "moble",bcpo_joiningdate as "DOJ", "DOJ",bcpo_salary as "Salary" from tblbcpo This Query made a view of name of check point officer as name, cell phone number as mobile, date of joining as DOJ & salary as salary of Check Che ck point officers. Q13. insertinto tblBC (BC_ID, BC_ID,BC_NAME, BC_NAME,BC_CNIC, BC_CNIC,BC_CADDRESS, BC_CADDRESS,BC_PADDRESS, BC_PADDRESS,BC_CELLNO, BC_CELLNO,BC_RESIDENCEPHO NENO, NENO,BC_EDUCATION, BC_EDUCATION,BC_JOININGDATE, BC_JOININGDATE,BC_EXPERIANCE, BC_EXPERIANCE,BC_SALARY, BC_SALARY,BC_NoofComplains )values values(('bc-117' 'bc-117',,'Ali Akber', Akber',423058372799, 423058372799,'North Nazimabad', Nazimabad','North Nazimabad',,03222339132, Nazimabad' 03222339132,'none' 'none',,'10 level', level',19951995-1212-18, 18,'4 yrs', yrs',10000, 10000,0) This Query Inserted a Tuple of complete record of a bus conductor in bus conductor table. Q14. createview Bus_Info asselect asselect rr.br_id as "Bus_Names", "Bus_Names",br_allstops as "Routes", "Routes",br_shfallowed as "student_fare" from tblbr r join tblb b on r.br_id= br_id=b.br_id This Query created a view v iew of bus routes, bus names & student discount discount of each bus by joining two tables with the name of Bus_info. Bus_info. Q15. createindex bus_Type on tblbt (bt_id, bt_id,bt_discription, bt_discription,bt_noofseats, bt_noofseats,bt_pcapacitymin, bt_pcapacitymin,bt_pcapacitymax) bt_pcapacitymax) This Query made an index of bus type showing bus details. Q16. select select BR_ID BR_ID as asBussname Bussname,,BR_Allstopsas BR_AllstopsasBussStop BussStopfrom fromtblBR tblBR This Query displayed Bus name and their routes Q17. select select BR_ID BR_ID as asBussName BussName,,Br_Allstopsas Br_AllstopsasBussStop BussStopfrom fromtblbr tblbrwhere whereBr_ID Br_ID= ='2d' This Query displayed a particular bus name (2D) and it's routes Q18. select BT_ID select BT_ID as asBussId BussId,,BT_discriptionas BT_discriptionasBussType BussTypefrom fromtblBT tblBT This Query displayed BusID and BussType Q19. select count count (Bt_Discription) Bt_Discription)as asBussType BussTypefrom fromtblbt tblbt This Query displayed Total types of Buses Q20. select count count (*)as (*)as "Number of Conductors" from fromtblBC tblBCwhere wherebc_salary bc_salary> >8000; 8000; This Query displayed Total Number of Conductors whose salary is greater than 8000 Q21. select Br_IDas Br_IDasBusName BusName,,Br_NoofBusesas Br_NoofBusesasTotalBuss TotalBussfrom fromtblbr tblbr This Query Displayed Bus Name and total number of each Bus
Q22. select BD_NAME select BD_NAME as asDriver_Name Driver_Name,,Bd_salaryas Bd_salaryas Salary from fromtblBD tblBD This Query Displayed Driver Names and their salaries
Q23. selectDISTINCTbt_discription selectDISTINCTbt_discriptionas asBus_Type Bus_Type,,Bt_NoofSeatsas Bt_NoofSeatsasSeats Seats,,BT_pcapacityMinas BT_pcapacityMinasMinimu Minimu m_capacity, m_capacity,BT_pcapacityMaxas BT_pcapacityMaxasMaximum_Capacity Maximum_Capacityfrom fromtblbt tblbt This Query Displayed Bus Type, Seats, Minimum Capacity of Passenger and Maximum Capacity of Passenger Q24. select Br_Idas Br_IdasBusName BusName,,B_noofComplainsas B_noofComplainsas Complain from fromtblB tblBwhere whereB_noofcomplains B_noofcomplains This Query Displayed Bus Name and Complain Q25. Insertinto tblBS (BS_ID, BS_ID,BS_NAME, BS_NAME,BS_LOCATION) BS_LOCATION)Values Values(('bs-13' 'bs-13',,'johar mor', mor','Gulistan-eJohar')) Johar' This Query added record in tableBS of new bus stop Q26. INSERTINTO TBLBEDC (BEDC_ID, BEDC_ID,BEDC_BALANCE, BEDC_BALANCE,BEDC_BANKNAME) BEDC_BANKNAME)VALUES VALUES(('9991000000651351' '9991000000651351',,'120000' '120000',,'N IB')) IB' This Query added record in tableBEDC about new debit card Q27. INSERTINTO TBLBPT (BPT_ID, BPT_ID,BPT_DISCRIPTION, BPT_DISCRIPTION,BPT_CHARGES) BPT_CHARGES)VALUES VALUES(('bpt005',,'Wrong Parking', 005' Parking' ,'1000' '1000')) This Query added record in tableBPT about penalty penalty type. Q28. INSERTINTO TBLBP (BP_ID, BP_ID,BRM_ID, BRM_ID,BPT_ID, BPT_ID,BP_CHALLANNO) BP_CHALLANNO )VALUES VALUES(('bp-003' 'bp-003',,'brm001',,'bpt-005' 001' 'bpt-005',,'00014659' '00014659')) This Query added record in tableBP about a traffic penalty by bus driver Q29. UPDATEtblBC UPDATEtblBCSET SETBc_NAME Bc_NAME= ='Asher' 'Asher',, BC_CADDRESS= BC_CADDRESS='KARACHI' 'KARACHI'WHERE WHERE BC_ID= BC_ID='BC-117' This Query updated record in coloumnBc_NAME AND BC_CADDRESS OF TABLEBC Q30. UPDATEtblBS UPDATEtblBSset set BS_NAME BS_NAME= ='KHARADAR' 'KHARADAR',, BS_LOCATION= BS_LOCATION='D.H.A' 'D.H.A'where where BS_ID= BS_ID='bs-13' This Query Updated record in coloumn BS_NAME AND BS_LOCATION of TABLEBS Q31. UPDATE TBLBPT SET BPT_CHARGES= BPT_CHARGES='500' '500'Where Where BPT_ID= BPT_ID='bpt-005' This query updatedBpt charges in tableBPT Q32. update TBLBP set BP_challanno= BP_challanno='0001499' '0001499'where where BP_ID= BP_ID='bp-003' This Query updated record Challan no in table BP Q33. UPDATE TBLBEDC SET BEDC_BALANCE= BEDC_BALANCE='100000' '100000'where where BEDC_ID= BEDC_ID='9991000000651351' This Query updatedBALANCE in tableBEDC Q34. UPDATE TBLBCO set BCO_CellNo= BCO_CellNo='03132417081' '03132417081'where where BCO_ID= BCO_ID='BCO-006' This Query updated Cell Number of BCO in TableBC
Conclusion: We have studied all the technical areas, Issues, benefits, solutions while making this project & also collect information that are most essential for the implementation of bus/traffic transport management system in Karachi. The Database strategies have been made in order to accomplish desired objectives & goals. The record keeping system has been changed and become reliable. It was indeed a great learning experience which would be fruitful in preparing the bus management database system. By this we took a step towards fragmented bus record keeping system & turned it into a proper database system.
Recommendations (Suggestions):
This project is recommended for big or small bus transportation companies. companies.
It is recommended for those bus transportation companies which do not have ticket system.
Only Microsoft ® SQL Server 2005or higher is recommended for this DBMS Project.
It is recommended for those bus transporters who are seeking a proper database management system in their business.
Those bus transporters who are willing to invest money in their business to make their business centralize & less complex then this project is a good ch oice for them.
Appendices: Appendix-1 Database management System (DBMS) DBMS is a record keeping system in a business by this a company can manage their day to day work by recording their data which is related to their business. This simplifies and centralizes a business rather then makes it complex. By this any employee who is granted to use this system either from user end or from DBA can manage their records according to their nature of job or requirement.
DBMS also secure a company’s data from outsiders as well as from those employees who are not granted to view some confidential information by applying some special authentications from DBA. By this a company can store their millions of data in GB’s & TB’s and can check, update, delete and add (insert) any time when company wants. DBMS is based on computer Software commonly known as DBM (Database Manager). There are many Software companies which are ruling in the database market with their DBM’s like Microsoft ® SQL Server, Oracle® and IBM’s DB2.
Appendix-2 DBA (Database Administrator) DBA is the person who is responsible to maintain data in database; he is the person who authenticates the users of DBM according to their departments and their job responsibilities. DBA is not only authenticates and maintain the data but also monitor it as well. He is responsible to troubleshoot any kind of issue in DBMS. DBA can make a database according to the company’s requirement. A company which manages their data by DBM has at least one DBA either Certified on specific DBM or at least good command & awareness about its functions & features.
Appendix-3 Microsoft ® Microsoft ®SQL Server 2005 MS SQL Server 2005 is well known and popular DBM of Microsoft ®. It has almost all the features DBMS which, a database required. SQL Server 2005 is a user-friendly DBM which can be operated in Command Line as well as in GUI Environment. All the queries, Tables and ERDs in this project are based on this software.
Appendix-4 ERD (Entity Relationship Diagram) ERD shows the relationship between two or more than two Entities (Tables). This diagram could be fully attributed or simple. This shows that how many relationships are taken place plac e in between two tables i.e. 1 to 1, 1 , 1 to many, many to 1 and many to many. Also, it shows number of attributes in a single table and their d ata types.
Appendix-5 DFD (Data Flow Diagram) DFD represent the flow of data in database system. It shows how data flow from one entity to another entity and finally to main database and again from main database to entities as per project requirement. Also, it shows cycle of data.
Appendix-6 CPO (Check Point Officer) In this Project, CPO is a person who is responsible to record day to day bus information in database and also responsible to issue and collect the tickets to/from conductor. CPO has only authority to add (insert) and read the data. Each & every checkpoint must have at least lea st one CPO.
Appendix-7 Contact Center
Call Centre is very vastly used business strategy in this era. It’s an easy way for a company to communicate with their customers & other people in the market. In the same concern, we have introduced a contact center in this Project; which has a centralized system to receive and make calls to all over the city, country or world depend on business type. Each & every Contact Centre connects to a database where all the th e data & information of callers are recorded. record ed.