CAR HIRE RATING SYSTEM
Prepared By Inspected/Reviewed By Approved By
NAME: Bhrahmagna Trivedi
DATE: 16/ 10/2008
Version No. Date Changed By Changes Made
1.0 03/07/2008 Bhrahmagna Trivedi Base Version
Car Hire Rating System
The objective of this case study is to apply the learnt concepts and acquired skills. On completion of the case study participant should be able to:
• Create the required tables in SQL Server.
• Create stored procedures and triggers.
• Generate unique Application Id using index or sequence.
• Create web services.
• Generate auto emails.
The participant should have
1) Knowledge of Normalization
2) Working knowledge of creating tables, views, stored procedures with Transact-SQL
3) Implementation Knowledge of SQL and Joins
4) Familiarization with Visual studio
5) Good knowledge of establishing connections with ADO.NET
6) 3-Tier implementation knowledge with web services
Participant should be able to demonstrate Database design, Coding and Testing skills.
with SQL * Server, ASP.NET, ADO.NET and Web Services
Concepts – Normalization
4. DURATION IN HOURS
This is an individual case study and the duration is 24 hours (3 days)
A travel & tour agency provides vehicles for hire. They have a fleet of vehicles to offer for hire. All of them are 4-wheelers with right hand drive. The vehicles are Vans and Cars. The fuel required for them could be petrol and diesel too. The agency has different rates for each type of vehicle. The agency has vehicles fitted with ACs only. The agency maintains ace drivers who could be touring guides too for the tourists. The vehicles are well maintained by the agency so that they don’t break down during long distance travels. They contain all types of spares, a mobile phone given to driver and even vehicle tracker equipment attached to them so that the vehicle is never left unguarded. An Operator of agency can book the car as per customer requirements and user can also book car online.
Registered User: Operator is registered user and can access the services provided by the agency and hire cars for customers.
Unregistered Users: Those are customers who can hire car online.
• The application should have login page in which the Operator/ User (customers) should login with the User Id provided by the agency website.
• In order to avail the services for hiring a car, provide a link for registration to the services in the Home Page.
• The registration page should include following information: a) Customer Name b) Address c) Contact No. d) Email Id e) car f) place g) dateofhire. When user clicks the submit button a unique Application ID must be generated using index/sequence.
• Automatic Email Generation should be done to requestors when a request is received.
• Retrieve the information of available car for hiring.
• Make a web service that returns the available car details for hiring.
• Based upon the rate calculate the total amount and update the user’s information accordingly.
6. RESOURCES(HARDWARE AND SOFTWARE)
Environment : Net Framework, Visual Studio
Operating System : Windows
Programming Language : ASP.NET with C#/VB
Scripting Language : Java Script
Database : SQL Server
• Website Accessed by 2 type of users
• Auto mail should be generated.
• The database containing tables has to be created.
• Web service should make to get information about available cars.
The agency has a website and it takes vehicle requisitions through web, email, phone and even fax. Web visitors search for tour plans, vehicle rents, their mileage rates along with vehicle types and register for one of them.
Vehicle type Vehicle name Fuel required Seating capacity Rent per day Rate per km
Van Scorpio Diesel 7 1800
Van Tata Sumo Diesel 7 1700
Van Toyota Qualis Diesel 7 2000
Car Esteem Petrol 4 1750
Car Honda City Petrol 4 2500
Car Hyundai Accent Petrol 4 2100
Tourist Destination Distance from Hyderabad
9. COMMON MISTAKES
• Giving incorrect data type and length while creating Table
• Trying to create Table without mentioning the database and Table space name
• Trying to insert data into table without Creating Unique index.
• Problem in Connection String.
• Not following the Coding standards.
• SMTP port is not open for auto Email generation.
10. CHECK LIST FOR DELIVERABLES
• Refer to checklist for deliverables
11. REFLECTION UPON LEARNING
• Discussion on alternate solution provided by others.
• Lessons learnt
• PRO c#.net 3.5 by Matthew McDonalad.
• Sql Server 2005 by Wrox publication.
• ASP.NET 2.0 by Wrox publication.
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)