Final Report - Mehmet Mert Yücesan
Contents
1. Hire Baek You are required to fill the areas with Figure 7 4 Hire employee page for branch manager 26 You can use this page Figure 7 4 to hire new employees 6 7 6 Fire Employee You can use Fire Employee page to fire existing employees You can open this page using the Fire Employee link on your home page This page will return you the whole list of employees in your branch and you can choose the employee that you want to fire here Home page Employee Name Age City Phone Address District Salary Choose Bulent Ersoy 34 Ankara 03123567890 Kecioren 1800 Taylan Gerdan 23 Ankara 03122605784 7 Cad 35 Sok No 3 Bahcelievler 2000 Fire Back Figure 7 5 Fire employee page 6 7 7 Edit Employee Details You can change the employee details like city district address phone and salary using this page When you click on the link Edit Employee Details on your home page you will be redirected to a page asking you to enter the employee id about which you want to make changes After you enter the employee id you will see a page that you can edit the employee details You will mostly use this page to change the salary of the employee since employees have their own change settings pages 27 6 7 8 View Employees This page will return you to the information about all salesmen in your branch The information that you will see includes employee name age city phone address district and salary Back button on this2. AllEmployees view is the actual table that we use in order to show and calculate the outcome of all branches This view has the employees names salaries and the branches that they work in The outcome of the branches are shown to general manager Reports SELECT b_name SUM salary FROM Allemployees GROUP BY b_name With this report we get the sum of salaries of the employees that work for each branch Thanks to that we can calculate the outcome of the branches easily 10 SELECT b b_name sum c price FROM car c branch b WHERE c b_id b b_id GROUP BY b_name This report gives the total prices of cars in each branch SELECT b b_name sum r rent_price FROM rental r branch b SELECT FROM car cr WHERE cr b_id b b_id AS bear WHERE r c_id bcar c id GROUP BY b name With this report we can calculate the income of each branch by the total rental income that they gain Triggers CREATE TABLE HistoryTable user_id INT 10 UNSIGNED LastLoginDate DATETIME CREATE TRIGGER logon_audit_trigger AFTER LOGON ON DATABASE DECLARE Now DATETIME SET Now GETDATE UPDATE HistoryTable SET LastLoginDate Now WHERE HistoryTable user_id user_id This trigger will be used after each login to update the HistoryTable By using this trigger we will be able to show each user their last login date and time 11 6 USER S MANUAL In the figures below you will be shown sample output pages 6 1 Main Page All typ
3. Figure 7 1 Main page for branch manager Until log out this page will be shown to you as main page You can also see your last login date and time on this page 24 6 7 2 Change Settings You can change your settings using the Change Settings page You will open this page using the Change Settings link on your main page The resulting page will be similar to the one in Figure 5 2 You may fill only the areas that you want to change and leave others empty After clicking on the Change button you will be redirected to the Login Page to re login Back button will redirect you to your home page 6 7 3 Add Car You can add a car to your branch using this page You need to fill the all areas in the form since they cannot be null Home page Brand Model License Plate Engine Mileage CarlD Color Transmission Figure 7 2 Add car page 6 7 4 Remove Car You can use this page to remove a car On this page brand model license plate and car id will be shown to you And you will choose the car that you want to remove using radio 25 buttons Then you will click on the Remove button to complete your operation Back button will redirect you to your home page Brand Model License Plate CariD Choose Toyota Corolla 066755 1 o Opel Astra D6BDRABES 2 Remove Back Figure 7 3 Remove car page 6 7 5 Hire Employee Home page Employes Name Age Cty District Address Phone Salary
4. about the situation CariD CustomerID ReservationiD ReservationDate 3 2 136 2011 05 12 Back Figure 6 2 View reservations page for salesman CariD CustomeriD ReservationiD ReservationDate Choose 3 2 138 2011 05 12 a Cancel and inom Customer Back Figure 6 3 Cancel reservation page 21 6 6 4 View Rentals You can view the current rentals on the cars of your own branch here This page will give you information about brand model car id customer id rent id start date end date and total price for each rental Back button on this page will redirect you to your home page 6 6 5 Confirm Rental You will use this page to ensure that the car rented is taken by the customer In this case you will confirm the rental You can also cancel it using this page Back button will redirect you to your home page Brand Model CarlD CustomerID RentiD StartDate EndDate Price Choose Audi A3 3 2 138 2011 05 12 2011 05 18 2250 Toyota Corolla 1 5 140 2011 05 17 2011 05 18 250 Confirm Cancel Back Figure 6 4 Confirm rental page 6 6 6 View Cars You can use this page to see the whole list of cars in your branch This page will give you information about car id brand model license plate color engine transmission mileage and year for each car 22 6 6 7 View Service History View Service History page is designed to give you information about cars and their service routines You can see the service occasion n
5. FOREIGN KEY B ID REFERENCES BRANCH B ID ENGINE INNODB AUTO_INCREMENT 7 DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES CAR_ID gt MODEL LICENSE_PLATE ENGINE YEAR MILAGE BRAND COLOR 8 TRANSMISSION PRINCE LICENSE_PLATE gt CAR_ID CANDIDATE KEYS CAR _ID LICENSE_PLATE NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR CUSTOMER CREATE TABLE RENTMYCAR CUSTOMER C_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT P_ID INT 10 UNSIGNED NOT NULL PRIMARY KEY C ID KEY CP ID P_ID CONSTRAINT CP_ID FOREIGN KEY P_ID REFERENCES PERSON P ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES C ID gt P ID CANDIDATE KEYS C 1D NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR EMPLOYEE CREATE TABLE RENTMYCAR EMPLOYEE E ID INT 10 UNSIGNED NOT NULL AUTO INCREMENT E DATE DATETIME NOT NULL SALARY INT 10 UNSIGNED NOT NULL P_ID INT 10 UNSIGNED NOT NULL PRIMARY KEY E 1D KEY EP_ID P_ID gt CONSTRAINT EP ID FOREIGN KEY P_ID REFERENCES PERSON P_ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES E_ID gt P_ID E_ID E_YEAR CANDIDATE KEYS E_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR MAKES ACC CREATE TABLE RENTMYCAR MAKES ACC A ID INT 10 UNSIGNED NOT NULL AUTO INCREMENT D DATE DATETIME NOT NULL DAMAGE INT 10 UNSIGNED NOT NULL CAR 1D INT 10
6. that you will see in this page This will include some information like employee id operation type and operation date You can open this page using the link View Operations link on your home page 6 8 7 Set Branch Manager You can set managers to branches using this page In order to do so you first need to select the branch on which you want to operate After that you will be represented employees of that branch with their names ages and salaries You can either choose a person from that list and assign her as manager for that branch or you can click on the Assign Another Person button and fill in the required information for that employee on the opening page and assign her as manager In the end you will be redirected to another page asking you to enter the salary for your employee You can see a sample page on the following figure Figure 8 3 6 8 8 Logout In order to log out from the system you need to use the Logout link on your home page This will ask you Are you sure that you want to logout and if you choose Yes you will log out and redirected to main page of the Rent My Car Figure 1 If you choose No you will return your own home page 32 Employee Name Age Salary Choose Taylan Gerdan 23 2000 Ali Erdem Solmaz 32 2300 Assign as Manager l Assign Another Person Jl Back Figure 8 3 Select employee page for set branch manager operation
7. 8 62714 Lo gout seti E EAEE kini aeele lal sa a one agevsthasdichagsageead alsak kalin abala layan iin 29 6 8 General Manager Pages ii sirin se ale eaten dee eee aria 30 6 8 1 General ManagerMainPage een 30 6 8 2 Change SettingSs bismi la ain totais eikonal Be eel Hace nested A D R 30 6 8 3 alo o fl mI EsTa AERE ee A 31 A A as E T 31 6 8 0 View Brane he Sise seal si ae sevme ores la ana nlar al yala di anda Hall la sma di a in 32 6 8 6 View OperationSs 2 ekin plaklara MO al di Sila 32 6 8 7 Set Branch Manager issn a aadaki mel an lapa m en lll Mak ll a lal 32 6 83 83 LODO Ud lida e BAL aaa alda all a e lann pi 32 1 BRIEF DESCRIPTION In this project we designed and implemented a car rental system This system has 4 types of users First one is general manager This user is the admin of the company He can manage the branches and see the details of the branches budget Second type of user is the branch manager The branch manager is kind of the boss of a branch They can hire fire employees salesman add remove cars and see the details of cars service history The salesman are the workers in the company They are capable of confirm rentals and cancel reservations The end user of our system is customer Customers register the system in order to user it They can search cars and view the details of them And also they can make reservations on cars The reserved cars are se
8. Bilkent University Department of Computer Engineering CS 352 Term Project Car Rental Data Management System Project Final Report Group12 Mehmet Mert Y cesan 20801811 Orhan Kaan Bulut 20603809 Derya Ye ilyurt 20702278 Instructor Aybar Acar TA Cagri Toraman Website http mertyucesan com cs352 Project Final Report May 13 2011 Contents oa BEM BRIEF DESCRIPTION cc zeki ear aaa laleler da akdar a lamak Anzak ka ar aleni kazik az 3 SER DIAGRAMA A alel ya la ya nl he ee 4 FINAL LIST OF TABLES 0 ei a Haa ia 4 lt IMBLEEMENTLON DETA L iui ine esk Hakla a eli nie ath ais pitas tedith Win ardan Saints 9 ADVANCEDDATABASEFEATURES eee 10 USERS MANUAL ene kere act hoes alanla ots Sea sale aantcaaviad stag dass a n a a EE E A Eee S EE anaa 12 Oot Main ele ERE e RR ME MN R 12 Le tt it boca 12 0S Forget PassWord cit ela Abe akel A d ee 13 6 4 Create ACCOUNT i iel lll eid diet laik akel S ng ami sil a alli Dili la 14 6 5 G stolmer Pages verim elli ella lll eee ee ll al ll la a 14 6 5 1 Customer Main Pagesa keielek l ama mama isananmal m a Aaaa E balgam palal aaa 15 65 2 Change UNOS eii dai pakla l iio 15 6 5 9 REG aro ran pedala hn gazaya paar es idos 16 6 5 4 View Rentals cisco a 18 6 5 5 Make Reservation s s dna ln All Bil A ei 18 6 5 6 ViewandCancelReservations ee 19 6 9 LOGOUT OO 19 6 6 S
9. D KEY SMANB ID B 1D CONSTRAINT SMANB D FOREIGN KEY B ID REFERENCES BRANCH B 1D ON DELETE NO ACTION CONSTRAINT SMANE D FOREIGN KEY E ID REFERENCES EMPLOYEE E ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES SMAN_ID gt E_ID CANDIDATE KEYS SMAN 1D NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR SERVICE CREATE TABLE RENTMYCAR SERVICE SVC_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT SVC_NAME VARCHAR 50 NOT NULL PRIMARY KEY SVC_ID ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES SVC_ID gt SVC_NAME CANDIDATE KEYS SVC 1D NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR SERV CE HISTORY CREATE TABLE RENTMYCAR SERVICE HISTORY S_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT NEXT DATE DATETIME NOT NULL S OCCASION VARCHAR 45 NOT NULL LAST DATE DATETIME NOT NULL SPRICE INT 10 UNSIGNED NOT NULL ROUTIN SERV DAY INT 10 UNSIGNED NOT NULL CAR ID INT 10 UNSIGNED NOT NULL SVC ID INT 10 UNSIGNED NOT NULL PRIMARY KEY S ID KEY PRIMARY2 CAR 1D KEY SHSVC_ID SVC_ID CONSTRAINT SHSVC_ID FOREIGN KEY SVC ID REFERENCES SERVICE SVC_ID ON DELETE NO ACTION CONSTRAINT SHCAR ID FOREIGN KEY CAR ID REFERENCES CAR CAR ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES S_ID gt NEXT_DATE S_OCCASION LAST DATE PRICE ROUTIN SERV DAY CAR
10. NT PR CE INT 10 UNSIGNED NOT NULL PAY ID INT 10 UNSIGNED DEFAULT NULL PRIMARY KEY C_ID CAR_ID KEY RENTCAR D CAR ID KEY RENTSMAN_ID SMAN 1D KEY RENTPAY D PAY ID CONSTRAINT RENTC_ID FOREIGN KEY C ID REFERENCES CUSTOMER C ID ON DELETE CASCADE CONSTRAINT RENTCAR_ID FOREIGN KEY CAR ID REFERENCES CAR CAR D ON DELETE CASCADE CONSTRAINT RENTSMAN ID FOREIGN KEY SMAN_ID REFERENCES SALESMAN SMAN 1D CONSTRAINT RENTPAY_ID FOREIGN KEY PAY ID REFERENCES PAYMENT PAY ID ENGINE INNODB DEFAULT CHARSET LATIN1 CANDIDATE KEYS CAR_ID C_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR RESERVES CREATE TABLE RENTMYCAR RESERVES C_ID INT 10 UNSIGNED NOT NULL CAR 1D INT 10 UNSIGNED NOT NULL RESERVE DATE DATETIME NOT NULL ON RESERVE DAY INT 10 UNSIGNED NOT NULL PRIMARY KEY C ID CAR ID KEY RESCAR_ID CAR 1D CONSTRAINT RESCAR D FOREIGN KEY CAR ID REFERENCES CAR CAR ID ON DELETE CASCADE CONSTRAINT RESC_ID FOREIGN KEY C ID REFERENCES CUSTOMER C 1D ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 CANDIDATE KEYS C_ID CAR_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR SALESMAN CREATE TABLE RENTMYCAR SALESMAN SMAN_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT E ID INT 10 UNSIGNED NOT NULL B_ID INT 10 UNSIGNED NOT NULL PRIMARY KEY SMAN_ID KEY SMANE_ID E 1
11. RANCH CREATE TABLE RENTMYCAR BRANCH B_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT B_NAME VARCHAR 45 NOT NULL PRIMARY KEY B ID ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES B_ID gt B_ NAME CANDIDATE KEYS B_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR BRANCH MANAGER CREATE TABLE RENTMYCAR BRANCH MANAGER M_ID INT 10 UNSIGNED NOT NULL AUTO INCREMENT B_ID INT 10 UNSIGNED NOT NULL E ID INT 10 UNSIGNED NOT NULL PRIMARY KEY MLID KEY BMB_ID B_ID KEY BME_ID E 1D CONSTRAINT BMB_ID FOREIGN KEY B ID REFERENCES BRANCH B_ID ON DELETE NO ACTION CONSTRAINT BME ID FOREIGN KEY E ID REFERENCES EMPLOYEE E 1D ON DELETE NO ACTION ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES M_ID gt P_ID E_ID CANDIDATE KEYS E_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR CAR CREATE TABLE RENTMYCAR CAR CAR ID INT 10 UNSIGNED NOT NULL AUTO INCREMENT RENTAL STATUS VARCHAR 15 NOT NULL MODEL VARCHAR 15 NOT NULL L CENSE PLATE VARCHAR 15 NOT NULL ENG NE VARCHAR 15 NOT NULL YEAR INT 10 UNSIGNED NOT NULL MILAGE INT 10 UNSIGNED NOT NULL BRAND VARCHAR 15 NOT NULL COLOR VARCHAR 15 NOT NULL TRANSMISSION VARCHAR 20 NOT NULL PRICE INT 10 UNSIGNED NOT NULL B_ID INT 10 UNSIGNED NOT NULL PRIMARY KEY CAR 1D KEY CARB_ID B_ID CONSTRAINT CARB_ID
12. UNSIGNED NOT NULL PRIMARY KEY A 1D KEY CAR ID CAR 1D CONSTRAINT CAR ID FOREIGN KEY CAR ID REFERENCES CAR CAR D ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES A_ID gt A_DATE DAMAGE CAR ID CANDIDATE KEYS A ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR PAYMENT CREATE TABLE RENTMYCAR PAYMENT PAY ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT TYPE VARCHAR 45 NOT NULL P_PRICE INT 10 UNSIGNED NOT NULL C_ID INT 10 UNSIGNED NOT NULL PRIMARY KEY PAY ID KEY PAYC_ID C_ID CONSTRAINT PAYC ID FOREIGN KEY C_ID REFERENCES CUSTOMER C ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES PAY_ID gt TYPE P_PRICE C_ID CANDIDATE KEYS PAY_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR PERSON CREATE TABLE RENTMYCAR PERSON P_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT NAME VARCHAR 25 NOT NULL AGE INT 10 UNSIGNED DEFAULT NULL PRIMARY KEY P 1D ENGINE INNODB AUTO_INCREMENT 8 DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES P_ID gt NAME AGE LOGIN_STATUS CANDIDATE KEYS P_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR RENTAL CREATE TABLE RENTMYCAR RENTAL C_ID INT 10 UNSIGNED NOT NULL CAR_ID INT 10 UNSIGNED NOT NULL SMAN_ID INT 10 UNSIGNED DEFAULT NULL START DATE DATETIME NOT NULL END DATE DATETIME NOT NULL RE
13. VARCHAR 45 NOT NULL PHONE VARCHAR 45 NOT NULL PRIMARY KEY B_ID CONSTRAINT B_ID FOREIGN KEY B ID REFERENCES BRANCH B_ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES B ID ADR_ID gt DISTRICT ADDRESS CITY PHONE CANDIDATE KEYS B ID ADR_ID NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR ADDR PERSON CREATE TABLE RENTMYCAR ADDR_PERSON P_ID INT 10 UNSIGNED NOT NULL ADDRESS VARCHAR 45 NOT NULL DISTRICT VARCHAR 45 NOT NULL CITY VARCHAR 45 NOT NULL PHONE VARCHAR 45 NOT NULL PRIMARY KEY P 1D CONSTRAINT P_ID2 FOREIGN KEY P ID REFERENCES PERSON P 1D ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES P_ID ADR_ID gt DISTRICT ADDRESS CITY PHONE CANDIDATE KEYS P_ID ADR_ID NORMAL FORM 3N DROP TABLE IF EXISTS RENTMYCAR ADDR_SERVICE CREATE TABLE RENTMYCAR ADDR SERV CE SVC_ID INT 10 UNSIGNED NOT NULL AUTO_INCREMENT DISTRICT VARCHAR 45 NOT NULL ADDRESS VARCHAR 45 NOT NULL CITY VARCHAR 45 NOT NULL PHONE INT 10 UNSIGNED NOT NULL PRIMARY KEY SVC 1D CONSTRAINT SVC_ID FOREIGN KEY SVC_ID REFERENCES SERVICE SVC_ID ON DELETE CASCADE ENGINE INNODB DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES SVC_ID ADR_ID gt DISTRICT ADDRESS CITY PHONE CANDIDATE KEYS SVC_ID ADR_ID NORMAL FORM 3N DROP TABLE IF EXISTS RENTMYCAR B
14. _ID SVC_ID CANDIDATE KEYS S_ID NORMAL FORM 3NF 4 IMPLEMENTION DETAILS We use ASP active server pages as programming language in our project MySQL is our database that we store the data We installed S internet information services onto our machines IIS is for us to make configurations on localserver In this way we can implement this project on our local server We allowed ASP in IIS Configurations While managing the database MySQL Query Browser is used MySQL Query Browser is a GUI that helps us to create or edit tables easily We provide the connection between the pages and database using ASP s connection properties The correctness of insertion deletion and update operations are checked in Query Browser In our GUI design we looked at templates on the internet and choose one of them Then we designed it according to our system and modify it to be compatible with our own designs in Design Report Our design contains html tables and javascript functions on some cases There is a payment page on our system But we did not implement that part because it has some credit card operations It can be implemented later For now it shows but doesn t process When user tries to continue a popup shows up saying Under Construction Send email part is implemented via SMTP protocol Emails are sent from a hotmail account 5 ADVANCED DATABASE FEATURES Secondary Indices Statements CREATE INDEX p_id USING BTREE ON acc
15. alesman Page ica dey n madde gz elm sa eee dean dd dalla yeman ete ale 20 6 621 Salesman Main Pages sese amm sea a Sey an si ba ni deta 20 6 6 2 Change Setas cinco ikna palal ld aski alal DA gali salli e 20 6 6 3 ViewandCancelReservations een 21 6 6 4 View Rentals ii oras 22 6 6 5 Confirm Rental akarken ltda 22 0 00 VIEW Gals 5x tutti eis lio la laa Ball a 22 6 6 7 View Service klislory says ti A tv ee ee 23 6 6 8 View Accidents ii ei len ea tenes Hen el L a a l sa a lam See ete ete epee 23 6 6 9 View Services iien kala Balli kemal Bin Ea a ai al ee i 23 0 0 1 0 LOgO Utica Dia chee iia A bn ee 23 6 7 Branch Manager Pages een m e e aa a ra nah a eea AEE EEEE aAA ETE Aane A o SEE AEA ENR SEESE in 24 6 7 1 Branch Manager M in Pagese rene cede a a aa aaa Se ld i n 24 6 7 2 Change Selis ale lr gede a ea ete iin atic ati tien lh iad ate 25 6 7 3 Add KOTE kik iat cise galan li ll ane a am ih eee ld os Sa es a 25 6 7 4 Remove Calvin ath Sa Ae AA ie a ll la ee 25 6 7 52 Aire Employee ses vei BEN AAG oie se aiid EE LAR A Pe gn IA A 26 NAAA O 27 0 77 Edit Employee DetallS seter a iio zelil la rta 27 6 1 8 View EmployeesS serseri lem tal apto yaland Ml Seek 28 6 7 97 View Service Hist iii ricino conde 28 6 7 10 View Accidents eria uree aliens all Dil Ball ae NE 28 671s View IE 28 6712 VieW R OT EN E 28 67413 View Branch Detall Sic ssoi ai iran sal mealen ana is 2
16. can select the brand model and city for your reservation When you click on the Search button you will see the resulting list of cars like in Figure 5 4 except that you will see also rental status of the cars This means that if a car is on rent you can still make reservation for that car for a date after the end date of its reservation If there are other reservations made before you on the same car again you can make a reservation for that car for a date it is available Your reservations will be available for 2 days that is in 2 days you have to rent the car on which you put a reservation You can also view and cancel your reservations using the links on your home page 18 6 5 6 View and Cancel Reservations These options are provided to you on the same link View Reservations on your main page Figure 5 1 Using this link you can view your current reservations and you may cancel them Home page Rent a About us Contact information Brand Model Branch StartDate EndDate Price Choose Audi A3 Ankara Rent My Car 2011 05 12 2011 05 18 2250 Cancel Reservation Back Figure 5 7 View and cancel reservations 6 5 7 Logout In order to log out from the system you need to use the Logout link on your home page This will ask you Are you sure that you want to logout and if you choose Yes you will log out and redirected to main page of the Rent My Car Figure 1 If you choose No you will return your own home
17. e 1 If you choose No you will return your own home page 29 6 8 General Manager Pages General Manager pages are the set of pages that general manager of the Rent My Car Company will see throughout her operations After successfully logging in to the system you will see your personal main page first In that page you can see links to your operations like Change Settings Add Branch Remove Branch View Branches Set Branch Manager and so on 6 8 1 General Manager Main Page Welcome Derya Yesilyurt Your last login is on 12 05 2011 at 00 25 Have fun Figure 8 1 Main page for general manager Until log out this page will be shown to you as main page You can also see your last login date and time on this page 6 8 2 Change Settings You can change your settings using the Change Settings page You will open this page using the Change Settings link on your main page The resulting page will be similar to the one in Figure 5 2 You may fill only the areas that you want to change and leave others empty After clicking on the Change button you will be redirected to the Login Page to re login Back button will redirect you to your home page 30 6 8 3 Add Branch You will use this page to add a new branch to Rent My Car database In this page you will be asked to enter the branch name city district address and manager for that branch Among these branch name address and branch manager are required to fill Af
18. en locked to other users in a few days so that the customer who makes the reservation can rent the car in these days If a problem occurs the salesman cancel the reservation and send information email to the customer The customers can make rentals too online In this part a salesman has to make confirmation on that rental for rental to be completed After that the customer pays the price of that rental via credit card But this section is not implemented because it involves POS operation 2 E R DIAGRAM 3 FINAL LIST OF TABLES DROP TABLE IF EXISTS RENTMYCAR ACCOUNT CREATE TABLE RENTMYCAR ACCOUNT ACC ID INT 10 UNSIGNED NOT NULL AUTO INCREMENT USERNAME VARCHAR 45 NOT NULL PASS VARCHAR 45 NOT NULL PRIORITY INT 10 UNSIGNED NOT NULL P_ID INT 10 UNSIGNED NOT NULL EMAIL VARCHAR 45 NOT NULL PRIMARY KEY ACC ID KEY P ID P ID CONSTRAINT P ID FOREIGN KEY P ID REFERENCES PERSON P ID ON DELETE CASCADE ENGINE INNODB AUTO INCREMENT 6 DEFAULT CHARSET LATIN1 FUNCTIONAL DEPENDENCIES P ID AC_ID gt USER NAME E_MAIL PASSWORD USER NAME PASSWORD gt E MA L AC_ID P_ID E MA L gt USER NAME CANDIDATE KEYS P ID AC 1D USER NAME PASSWORD E_MAIL NORMAL FORM 3NF DROP TABLE IF EXISTS RENTMYCAR ADDR BRANCH CREATE TABLE RENTMYCAR ADDR BRANCH B_ID INT 10 UNSIGNED NOT NULL ADDRESS VARCHAR 45 NOT NULL DISTRICT VARCHAR 45 NOT NULL CITY
19. es of users of the Rent My Car system view the same main page Figure 1 before they login to the system In Home Page tab you see the general information about the company Rent My Car Also there exists links for Login and Register pages in this main page The following tabs are for renting a car viewing company and contact information Rent My Car Home page Rent a car About us Contact information Thousands of cars and the best prices True reliable and quick service with hundreds of employees Visit Rent My Car now and rent your car Figure 1 Main page for all users 6 2 Login You need to login to the system to make further operations like renting a car making reservation and so on The same thing holds for other types of users Login page Figure 2 opens from the link in Main Page Users login to the system with their names and passwords If you forget your password you can click the link on this page to change it After a successful login corresponding page for each type of users opens 12 Home page Rentacar Aboutus Contact information Name Password i Login Forgot your password Click here Figure 2 Login page 6 3 Forget Password If you do not remember your password you can use this page You need to enter your username and then the system will send your password to your e mail address Back button on this page will redirect you to the main page Home page Renta car Aboutus Contact in
20. ext service date price of the service last service day and some other information about each car 6 6 8 View Accidents Using this page you can learn accidents made by cars The information that you will get includes accident date damage and car id Then you may need to check whether there is a reservation on that car and cancel that reservation as described in step 6 6 3 6 6 9 View Services View Services page will give you information about the services with which your branch is working You can see the service names their addresses phone numbers and so on in this page 6 6 10 Logout In order to log out from the system you need to use the Logout link on your home page This will ask you Are you sure that you want to logout and if you choose Yes you will log out and redirected to main page of the Rent My Car Figure 1 If you choose No you will return your own home page 25 6 7 Branch Manager Pages Branch Manager pages are the set of pages that branch managers of the Rent My Car Company will see throughout their operations After successfully logging in to the system you will see your personal main page first In that page you can see links to your operations like Change Settings Add Car Remove Car Hire and Fire Employee Edit Employee Details View Cars View Branch Details and so on 6 7 1 Branch Manager Main Page Welcome Mert Yucesan Your last login is on 12 05 2011 at 00 25 Have fun
21. formation UserName Your password will be sent to your e mail address Figure 3 Password reminder page 13 6 4 Create Account You first need to create an account to use the system by clicking on the link Register on the Main Page Figure 1 You have to fill the areas with to be able to register to the system Otherwise you will get error messages Home page Renta car Aboutus Contact information UserName Password ConfirmPassword Name Age Phone Email City District Address Create Account You are required to fill the areas with Figure 4 Create account page 6 5 Customer Pages Customer pages are the set of pages that customers of the Rent My Car Company will see throughout their operations After successfully logging in to the system you will see your personal main page first This page also contains links to several operations like change settings view reservations view rentals and log out You can also cancel your reservations and or rent a car using these pages 14 6 5 1 Customer Main Page Home page Rent a Car About us Contact information Welcome Cagri Toraman Your last login is on 12 05 2011 at 00 25 Change Settings Have fun pw Reservations Figure 5 1 Main page for customers Until log out this page will be shown to you as main page You can also see your last login date and time on this page 6 5 2 Change Settings UserName Password Prone Ema
22. il Cay Ostet Address Change Back Figure 5 2 Change settings page for customers 15 You can change your settings using this page Figure 5 2 You may fill only the areas that you want to change and leave others empty After clicking on the Change button you will be redirected to the Login Page to re login Back button will redirect you to your home page 6 5 3 Rent Car You can rent a car online using the Rent My Car system To do this you need to login first Then you can open the link on Rent a Car tab In this link you will be represented by two options either making a reservation on a car or renting a car You should choose rent a car in this step Figure 5 3 shows the Rent Car page You can select a car brand model and city using this page When you click on the search you will see the list of cars being in our system and having the properties you choose If that particular car is not in one of our branches or the city you choose you will see another page telling that No result found In this case you need to revise your choices Home page Rent a car About us Contact information Rent a car Select brand Audi y Model A3 X Select city Ankara v Search Figure 5 3 Search page for choosing a car to rent In the page that you see the results of your search Figure 5 4 you can select a car to rent by using radio buttons After choosing a car if you click on the Rent button you will see another page as
23. king you to select Start and End date for your rental After choosing the dates you can 16 click Continue and see payment page In that page you have to fill the required information to rent the car you choose When you finish the payment successfully you can come back to your main page and click on the View Rentals link to see your current rental together with your other rentals Figure 5 6 Brand Model City Year Branch Engine Transmission Color Price Choose Audi AJ Ankara 1996 Ankara Rent My Car Gasoline Manual Black 375 o Rent Back Figure 5 4 Rent car confirm page Home page em aca S Md miormation First Name Last Name Credit Card Number Expiry Date csc Billing Address City Figure 5 5 Payment page 17 6 5 4 View Rentals You can use the View Rentals link on your home page to see the rentals you have made Prices shown here are the total prices of your rental whereas in Figure 5 4 the shown price is the daily price for the belonging car Home page Rent a cal About us Contact information Brand Model Branch StartDate EndDate Price Audi A3 Ankara Rent My Car 2011 05 12 2011 05 18 2250 Back Figure 5 6 View rentals page 6 5 5 Make Reservation Alternatively you can make reservation for a car before renting it To do so you first need to login to the system and then use make reservation link under Rent a Car tab A similar procedure to renting a car applies here On the opening page you
24. ount p_id CREATE INDEX bmb_id USING BTREE ON branch_manager b_id CREATE INDEX bme_id USING BTREE ON branch_manager e_id CREATE INDEX carb_id USING BTREE ON car b_id CREATE INDEX cp_id USING BTREE ON customer p_id CREATE INDEX ep_id USING BTREE ON employee p_id CREATE INDEX car id USING BTREE ON makes acc car id CREATE INDEX payc id USING BTREE ON payment c id CREATE INDEX rentcar id USING BTREE ON rental car id CREATE INDEX rentsman id USING BTREE ON rental sman id CREATE INDEX rentpay id USING BTREE ON rental pay id CREATE INDEX smane id USING BTREE ON salesman e id CREATE INDEX smanb id USING BTREE ON salesman b id CREATE INDEX PRIMARY2 USING BTREE ON service history car id CREATE INDEX shsvc id USING BTREE ON service history svc id Views We have 2 views in our database Actually we only use one of them but in order to create that view we needed to create another view because MySQL does not support creating views with subqueries that contain FROM clause err no 1349 CREATE VIEW EmpsWithSalary AS SELECT name e_id e salary FROM employee e INNER JOIN person p ON p p_id e p_id EmpsWithSalary view stores the employee s data with their salaries CREATE VIEW AllEmployees AS SELECT DISTINCT empers name empers salary br b_name FROM EmpsWithSalary empers branch_manager bm salesman sm branch br WHERE bm e_id empers e_id AND br b_id bm b_id OR sm e id empers e_id AND br b id sm b id
25. page 19 6 6 Salesman Pages Salesman pages are the set of pages that salesmen of the Rent My Car Company will see throughout their operations After successfully logging in to the system you will see your personal main page first In that page you can see links to your operations like Change Settings View and Cancel Reservations View Rentals Confirm Rentals and so on You can only see the information about your own branch 6 6 1 Salesman Main Page Welcome All Erdem Solmaz Your last login is on 12 05 2011 at 00 25 Have fun Figure 6 1 Main page for salesman Until log out this page will be shown to you as main page You can also see your last login date and time on this page 6 6 2 Change Settings You can change your settings using the Change Settings page You will open this page using the Change Settings link on your main page The resulting page will be similar to the one in Figure 5 2 You may fill only the areas that you want to change and leave others empty After 20 clicking on the Change button you will be redirected to the Login Page to re login Back button will redirect you to your home page 6 6 3 View and Cancel Reservations You can view and cancel current reservations using the links View Reservations and Cancel Reservations on your home page In the case of an accident you can use Cancel Reservation page to cancel a reservation The system will send an e mail to the customer to inform her
26. page will redirect you to your home page 6 7 9 View Service History View Service History page is designed to give you information about cars and their service routines You can see the service occasion next service date price of the service last service day and some other information about each car 6 7 10 View Accidents Using this page you can learn accidents made by cars The information that you will get includes accident date damage and car id 6 7 11 View Services View Services page will give you information about the services with which your branch is working You can see the service names their addresses phone numbers and so on in this page 6 7 12 View Cars You can use this page to see the whole list of cars in your branch This page will give you information about car id brand model license plate color engine transmission mileage and year for each car 6 7 13 View Branch Details This page will give you information about the details of your branch like total income number of rentals made on that month and so on 28 Home page Number of Rentals Income Cost Net Income 13 37000 12000 25000 Figure 7 6 View branch details page 6 7 14 Logout In order to log out from the system you need to use the Logout link on your home page This will ask you Are you sure that you want to logout and if you choose Yes you will log out and redirected to main page of the Rent My Car Figur
27. ter filling the necessary information you can click on the Add button and your branch will be added to the system You can then use the link View Branches on your home page to check your added branch Rent My Cor Branch Name City District Address Manager Add Back You are required to the areas with Figure 8 2 Add branch page 6 8 4 Remove Branch You will use this page to remove an existing branch You can open this page from the link Remove Branch on your home page Then you will be shown the list of branches with their cities You can choose the branch that you want to delete using the radio buttons When you click on the Remove button your operation will be made Back button on this page will redirect you to your home page 31 6 8 5 View Branches You can use this page to view all branches of Rent My Car Company This page gives you information about the branch names branch ids their addresses cities phone number and so on You will also see a link in this page with which you can get further information about branches This information includes their incomes costs number of rentals in each branch and so on 6 8 6 View Operations This page is designed to give you some detailed information made by each branch manager Rent My Car system is designed in such a way that whenever a critical operation is made by an employee like remove car made by branch manager this will trigger an event
Download Pdf Manuals
Related Search