Results 1 to 14 of 14
  1. #1
    Join Date
    Dec 2010
    Posts
    9

    Question Help on Designing Database for Car Rental Company

    Hello,
    I've been given a task to identify the entities, occurrences, relationships, multiplicities, attributes, candidate, primary and alternate keys and finish up with Entity-Relationship diagram for a database model, but I cant complete my work without the right entities so i need help on my work so far...


    POOOFF!!!...


    The EU-CAR Case Study

    EU-CAR is a car rental company within EU-TRAVEL, a multi-national company that also owns an airline (called EU-AIR) and a hotel chain (called EU-ROOMS). EU-CAR offers mass-market cars for short-term rental to the (1)general public in several (2)European countries. It has been successful in the past, but its position is being challenged by (3)global operators expanding into (2)Europe. Both business and leisure travel are increasing and, with them, so is the car rental market, but not as fast as the capacity available from EU-CAR’s competition.

    EU-CAR’s Business Strategy
    • To stay with its current product, and compete on quality of service and value for money (i.e. it will not move into longer-term car leasing, or franchising, or high-priced, low volume exotic car rentals)
    • To defend its current territory, and expand slowly into new locations
    • To focus in the short term on ensuring success of the car rental business. EU-TRAVEL intends in the longer term to provide a more integrated travel service, with co-ordinated bookings for flights, hotels and cars, but this should not unduly constrain the immediate requirement for Rent-A-Car.

    EU-CAR’s Business Goals
    • To position itself as a premium rental company - late model, well-equipped cars; well-trained, uniformed, friendly (4)staff responsive to (1)customer requirements and complaints; clean, modern premises with a consistent brand image - offering value for money rather than lowest possible prices.
    • To perform better than (3)other "quality" car rental companies
    • To reinforce the quality image in the market
    • To attract more repeat business
    • To provide cars where they are convenient for customers to collect i.e. major airports, city centres, hotels, etc. There are some specific (5)locations that are crucial to attracting business travellers (e.g. Brussels, Frankfurt, London, Paris, Zurich, Milan).

    Development of a system and scope
    In order to implement its business strategy and achieve its business goal, EU-CAR has identified the need for designing and developing a reliable IT system to support its core business operations centered on a robust database system. EU-CAR has defined the scope of the database system design and development to be the Rental Operations and the management and control of this area. Business activities that are outside the scope include Car Maintenance, Customer Loyalty Scheme, Premises Management, Marketing, Personnel (recruitment, personal development, payroll etc.) and Accounts.

    Business activities within the scope

    Rental Operations

    EU-CAR’s primary business activity is short-term (9)rental of (6)cars to the (1)general public. EU-CAR provides cars from a pool of 12500 vehicles at (7)branches in (8)convenient locations in twenty seven EU countries mainly at airports but also in (5)city centres, hotels and travel agents and is gradually increasing the number of branches from the current number of 189.

    (10)Rentals are of two types - advance bookings and walk-ins. In the first instance, EU-CAR will operate a two-way rental policy for which the car is picked-up and dropped-off at the same location. Subsequent versions of the system should later allow one-way rentals where the pick-up and drop-off locations are different.

    EU-CAR offers a standard set of seven car groups, with a different tariff for each. Car group specifications and guidance on tariffs are defined corporately, but car models selected for each group and actual tariffs vary country by country. All branches in a country offer the same range of car models and the same tariffs.

    A (1)customer should hence be able to use the system to view the various car (11)tariff groups, browse through the various vehicle models available on a specific tariff, select a particular car model and pick the various options he/she is interested in for this car model (AC, GPS, etc. at an additional cost). The customer should also be able to search for airports to establish if they have a EU-CAR branch and to view the details of any branch located at an airport or elsewhere. (It is to be noted that if an airport contains a branch it only contains one). When the customer has made his mind on the vehicle model he/she wishes to rent and on the branch he/she wants to pick it up from, the start and the end dates of the rental need to be entered for the rental to be recorded. In other words, the customer needs to specify the dates and times when he/she wishes to collect the car and to return it. Based on the various details entered, the total cost of the rental should automatically be calculated and recorded.

    Management and Control

    EU-CAR wants to introduce more formal management and control, especially in areas where there are known problems in the current environment. For example, it wants better control over the interchange period between successive customers during which a car should be checked for potential damages and for the level of remaining petrol. It also aims at improving the management of the movement of cars between branches to match demand with available cars. New (or, at least, more specifically-defined) activities are needed for setting expectations, monitoring performance and taking corrective action when necessary.


    Your help will be much appreciated.
    By the way, I might have to delete this.
    Last edited by ethio; 12-11-10 at 19:07.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ethio View Post
    I've been given a task...
    shirley, that's a euphemism for a homework assignment

    and not just a simple one, either -- this looks like a term project

    we'll be glad to help you along the way, but nobody is gonna do your work for you

    okay, now that that is out of the way...

    let's look at your entities so far

    what are the attributes of the general public? what might a candidate key be for the general public entity?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Dec 2010
    Posts
    9
    Ive only gone over it briefly but these are the attributes.
    memberNo{PK}, mFName, mLName, mHNo, mHName, mStreet, mPCode, mPNo{AK}

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by ethio View Post
    Hello,
    I've been given a task to identify the entities, occurrences, relationships, multiplicities, attributes, candidate, primary and alternate keys and finish up with Entity-Relationship diagram for a database model, but I cant complete my work without the right entities so i need help on my work so far...

    //
    1 General public, Customers
    2 European countries, Europe, (Territory)
    3 Global operators, Other rental companies
    4 Staff
    5 Crucial locations, Cities
    6 Cars
    7 Branches
    8 Convenient locations
    9 rentals
    10 Rental type
    11 Tariff group
    //

    The EU-CAR Case Study

    EU-CAR is a car rental company within EU-TRAVEL, a multi-national company that also owns an airline (called EU-AIR) and a hotel chain (called EU-ROOMS). EU-CAR offers mass-market cars for short-term rental to the (1)general public in several (2)European countries. It has been successful in the past, but its position is being challenged by (3)global operators expanding into (2)Europe. Both business and leisure travel are increasing and, with them, so is the car rental market, but not as fast as the capacity available from EU-CAR’s competition.

    EU-CAR’s Business Strategy
    • To stay with its current product, and compete on quality of service and value for money (i.e. it will not move into longer-term car leasing, or franchising, or high-priced, low volume exotic car rentals)
    • To defend its current territory, and expand slowly into new locations
    • To focus in the short term on ensuring success of the car rental business. EU-TRAVEL intends in the longer term to provide a more integrated travel service, with co-ordinated bookings for flights, hotels and cars, but this should not unduly constrain the immediate requirement for Rent-A-Car.

    EU-CAR’s Business Goals
    • To position itself as a premium rental company - late model, well-equipped cars; well-trained, uniformed, friendly (4)staff responsive to (1)customer requirements and complaints; clean, modern premises with a consistent brand image - offering value for money rather than lowest possible prices.
    • To perform better than (3)other "quality" car rental companies
    • To reinforce the quality image in the market
    • To attract more repeat business
    • To provide cars where they are convenient for customers to collect i.e. major airports, city centres, hotels, etc. There are some specific (5)locations that are crucial to attracting business travellers (e.g. Brussels, Frankfurt, London, Paris, Zurich, Milan).

    Development of a system and scope
    In order to implement its business strategy and achieve its business goal, EU-CAR has identified the need for designing and developing a reliable IT system to support its core business operations centered on a robust database system. EU-CAR has defined the scope of the database system design and development to be the Rental Operations and the management and control of this area. Business activities that are outside the scope include Car Maintenance, Customer Loyalty Scheme, Premises Management, Marketing, Personnel (recruitment, personal development, payroll etc.) and Accounts.

    Business activities within the scope

    Rental Operations

    EU-CAR’s primary business activity is short-term (9)rental of (6)cars to the (1)general public. EU-CAR provides cars from a pool of 12500 vehicles at (7)branches in (8)convenient locations in twenty seven EU countries mainly at airports but also in (5)city centres, hotels and travel agents and is gradually increasing the number of branches from the current number of 189.

    (10)Rentals are of two types - advance bookings and walk-ins. In the first instance, EU-CAR will operate a two-way rental policy for which the car is picked-up and dropped-off at the same location. Subsequent versions of the system should later allow one-way rentals where the pick-up and drop-off locations are different.

    EU-CAR offers a standard set of seven car groups, with a different tariff for each. Car group specifications and guidance on tariffs are defined corporately, but car models selected for each group and actual tariffs vary country by country. All branches in a country offer the same range of car models and the same tariffs.

    A (1)customer should hence be able to use the system to view the various car (11)tariff groups, browse through the various vehicle models available on a specific tariff, select a particular car model and pick the various options he/she is interested in for this car model (AC, GPS, etc. at an additional cost). The customer should also be able to search for airports to establish if they have a EU-CAR branch and to view the details of any branch located at an airport or elsewhere. (It is to be noted that if an airport contains a branch it only contains one). When the customer has made his mind on the vehicle model he/she wishes to rent and on the branch he/she wants to pick it up from, the start and the end dates of the rental need to be entered for the rental to be recorded. In other words, the customer needs to specify the dates and times when he/she wishes to collect the car and to return it. Based on the various details entered, the total cost of the rental should automatically be calculated and recorded.

    Management and Control

    EU-CAR wants to introduce more formal management and control, especially in areas where there are known problems in the current environment. For example, it wants better control over the interchange period between successive customers during which a car should be checked for potential damages and for the level of remaining petrol. It also aims at improving the management of the movement of cars between branches to match demand with available cars. New (or, at least, more specifically-defined) activities are needed for setting expectations, monitoring performance and taking corrective action when necessary.


    Your help will be much appreciated.
    By the way, I might have to delete this.
    good point, and well made, So I thought I'd copy it for posterity
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ethio View Post
    Ive only gone over it briefly but these are the attributes.
    memberNo{PK}, mFName, mLName, mHNo, mHName, mStreet, mPCode, mPNo{AK}
    are we talking about the same entity?

    number 1, general public

    your application will know the name and address of everyone in the general public???

    i would like right now to request that i be excluded from your database
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  6. #6
    Join Date
    Dec 2010
    Posts
    9
    lool, yes. general public, member and customer are the same entities. I figured general public is a synonym for customer.
    Last edited by ethio; 12-09-10 at 15:49.

  7. #7
    Join Date
    Dec 2010
    Posts
    9
    So are the entities i've listed in the first post correct?

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ethio View Post
    So are the entities i've listed in the first post correct?
    many of them, yes

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Dec 2010
    Posts
    9
    Ok good, so no errors? Any entites i'm missing?

  10. #10
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    when is this assignment due, and how much of the final grade depends on this assignment?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  11. #11
    Join Date
    Dec 2010
    Posts
    9
    25%, its for next week, why?

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ethio View Post
    25%, its for next week, why?
    just curious how long you're going to drag this out hoping for meaningful results from a forum

    how about making a tentative step towards getting something done?

    what are the primary keys for each of your entities?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Dec 2010
    Posts
    9
    I just needed someone to check my entites before i start. Now that its been looked at and you agree with me im set to go. Ill delete this and create a new thread if I need more help.
    Thanks.

  14. #14
    Join Date
    Nov 2015
    Location
    United States Of America New York
    Posts
    5
    Members and clients are the same organizations. I realized public is a replacements for client.
    Last edited by Messilionel; 11-12-15 at 02:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •