project -Database in Access
Albt20
part_1_access_done.docxProject Overview
Car Rental
Statement of Purpose
To keep track of business operation specifics. For example: who the clients are, payment amounts, payment types, cars being rented, whether clients are individual or large companies, etc.
Reports
1. The most popular cars rented.
2. The average rental length.
3. The most popular season for rentals.
4. The frequency for types of cars rented.
5. How customers usually pay.
6. What percentage of income comes from large company rentals.
7. The average cost for clients to rent moving trucks.
8. Revenue analysis report
9. The percentages of client occupations that are employed. (vs self-employed or student).
10. The average annual repair costs.
Business Processes
1. Add new car
2. Add customers
3. Edit customer’s data
4. Generate receipts
5. Review car requests
6. Check availability of cars in store
7. Check income generated
8. Post-rental car check-up (charges for customer if damage is found and maintenance information over mileage charges etc)
9. Client information and frequency
10. Promotions(based on popular season/customer loyalty/large companies)
Primary entities
· Car
· Rentalorder
· Employee
· Customer
· Category
Entity Relationship Diagram (ERD)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Car Category Employee Customer
|
|
|
|
|
Rentalorder |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Table Map
|
Rentalorder |
|
RentalorderID(PK) |
|
EmployeeID(FK) |
|
CustomerID(FK) |
|
CarID(FK) |
|
CarCondition |
|
StartDate |
|
EndDate |
|
TotalDays |
|
RateApplied |
|
OrderStatus |
|
Notes |
|
Car |
|
CarID(PK) |
|
TagNumber |
|
Make |
|
Mode |
|
CarYear |
|
CategoryID(FK) |
|
Picture |
|
Condition |
|
Available |
|
Employee |
|
EmployeeID(PK) |
|
EmployeeNumber |
|
FirstName |
|
LastName |
|
Title |
|
Notes |
|
Category |
|
CategoryID(PK) |
|
Daily |
|
Weekly |
|
Monthly |
|
Customer |
|
CustomerID(PK) |
|
DrvLivNumber |
|
FullNames |
|
Address |
|
City |
|
State |
|
ZipCode |
|
Occupation |
Data Dictionary
|
Field Name |
Data Type |
Caption |
Field Size |
Properties |
|
RentalOrderID (Primary Key) |
AutoNumber |
Rental Order ID |
Long Integer |
|
|
EmployeeID |
Number |
Processed By |
Long integer |
|
|
CustomerID |
Number |
Processed For |
Long integer |
|
|
CarID |
Number |
Car |
Long integer |
|
|
CarCondition |
Text |
Car Condition |
255 |
|
|
StartDate |
Date/Time |
Start Date |
|
|
|
EndDate |
Date/Time |
End Date |
|
|
|
TotalDays |
Number |
Total Days |
Integer |
|
|
AmountPaid |
Number |
Amount Paid |
|
Format: Fixed |
|
OrderStatus |
Text |
Order Status |
255 |
|
|
Notes |
Memo |
|
|
|
|
Field Name |
Data Type |
Caption |
Field Size |
|
CustomerID (Primary Key) |
AutoNumber |
Cutomer ID |
|
|
DrvLicNumber |
Text |
Driver's License # |
50 |
|
FullName |
Text |
Full Name |
100 |
|
Address |
Text |
|
100 |
|
City |
Text |
|
50 |
|
State |
Text |
|
100 |
|
ZIPCode |
Text |
ZIP Code |
20 |
|
Occupation |
Text |
|
|
|
Note |
Memo |
Promotion |
200 |
|
Field Name |
Data Type |
Caption |
Field Size |
|
EmployeeID (Primary Key) |
AutoNumber |
Employee ID |
|
|
EmployeeNumber |
Text |
Employee # |
20 |
|
FirstName |
Text |
First Name |
50 |
|
LastName |
Text |
Last Name |
50 |
|
Title |
Text |
|
100 |
|
Notes |
Memo |
|
|
|
Field Name |
Data Type |
Caption |
Field Size |
|
CarID (Primary Key) |
AutoNumber |
Car ID |
|
|
TagNumber |
Text |
Tag Number |
20 |
|
Make |
Text |
|
50 |
|
Model |
Text |
|
50 |
|
CarYear |
Number |
Year |
Integer |
|
CategoryID |
Number |
Category |
Long Integer |
|
Picture |
OLE Object |
|
|
|
Condition |
Text |
|
255 |
|
Available |
Yes/No |
|
|
|
Field Name |
Data Type |
Field Size |
|
CategoryID (Primary Key) |
AutoNumber |
|
|
Category |
Text |
50 |
|
Daily |
Number |
Long integer |
|
Weekly |
Number |
Long integer |
|
Monthly |
Number |
Long integer |
