Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Dec 2009
    Posts
    8

    Unanswered: Hotel Room Reservation System

    I need help here anyone can give me heads up will be appreciated

    Hotel Room Reservation System
    Westin owns a chain of hotels internationally around the world. As a world renowned hotel, it is their priority to ensure the efficiency of their services. As a means to improve their services, they hire you to develop a database for their hotel room reservation. The database system should be able to keep track on reservation details, rooms and customer information.
    Each hotel of Westin offers a standardised accommodation package. Each hotel has anywhere from twenty to one hundred rooms . These rooms could be either Standard type, Deluxe type, Superior type or Family type. Some rooms are facing good views while some are without any view. All rooms are cater for two or more adults, and are charged on a room per night basis, regardless of whether they are occupied by one or two guests. The charge per room per night is standard throughout the week . However, each room may have many different room facilities. A customer can make several reservations.
    Nevertheless, a room can only be subject to one reservation, on a given day, at any given time. A reservation may involve one or more rooms. However, all rooms in a s ingle reservation must have the same arrival and departure dates. If different arrival dates apply then separate reservations must be made. Each single reservation is made in a single name regardless of how many guests it actually involves. When a reservation is made the room allocated should always be the room whose vacant period best matches the requested reservation period, given that the room is of the type requested. The cus tomer needs to pay during the reservation, whereby the total prices are calculated based on number of room reserved per night. As a token of appreciation, Westin offers a 10% discount to regular customer (customer whic h has stayed in any chain of hotels for more than 10 times in a year).

    Example SQL queries and statements that your databas e should be able to handle
    •List the available rooms group by their room types
    •List the chain of Westin hotels which has the most number of reservations.
    •Create report(s) to list the total number of reservations for each day/month/year for each chain of Westin hotels .
    •For each hotel, list the top five countries from where the customer originates from.
    •Create a trigger that will price a room at a disc ounted rate for rooms that had their reservations cancelled. You may set the discount at different rates depending on the days (e.g. 1 week before actual date = 20% discount, 1 day before 50% discount). This is to encourage people to take up empty rooms and ensure that no room in the hotel is left empty (full occupancy = more money!)
    •Create a trigger that will automatically identify which c ustomer are the regular customers which will receive coupons for special offers.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    What is the name of the college and the course that this assignment is for?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Dec 2009
    Posts
    8
    I'M STILL in high school ,,,,

  4. #4
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Sounds like a good high school.

    I assume you need to develop a data model to handle this. You can download a trial version or freeware version of some simple modeling tools to come up with the design of the database that will handle the business requirements stated. Are you supposed to turn in a data model, or do you have to actually create a real database as part of the assignment?

    This one generates DB2 DDL, but has watermarks on the ERD diagrams if you print them:
    http://www.polderij****/happyfish/

    The above link is apparently censored by this site. replace the asteriks with dot nl (replace the dot with a ".") Or do a search on Happy Fish Database Designer

    If you will not be creating a DB2 database, there are plently of freeware or trial version data modeling tools.
    Last edited by Marcus_A; 12-05-09 at 13:04.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  5. #5
    Join Date
    Dec 2009
    Posts
    8
    A real database is not required, i can just add the model database myself based on a real life situation in which it should be reasonable enough,,,

    the link u gave me is not working

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by donno View Post
    A real database is not required, i can just add the model database myself based on a real life situation in which it should be reasonable enough,,,

    the link u gave me is not working
    This site blocks all links the the Netherlands (not sure why).

    But if you are not using DB2 as a real database, just search for data modeling tools and get a freeware version or a trial version of some other tool. The Happy Fish product is OK, but shows a watermark when you print from trial version.

    Do not download a free trial of software that costs more than a few hundred dollars, becasue the expensive ones are too hard to learn quickly.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Dec 2009
    Posts
    8
    i need to write the SQL code as well
    i think the real database is required since the SQL is required but i meant that the data into , the attributes data can be frm my own creation n it doesn't have to be real,,,,

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by donno View Post
    i need to write the SQL code as well
    i think the real database is required since the SQL is required but i meant that the data into , the attributes data can be frm my own creation n it doesn't have to be real,,,,
    Do you have a database installed?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Dec 2009
    Posts
    8
    yes , i'm using IBM DB2 for my course,,,

  10. #10
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by donno View Post
    yes , i'm using IBM DB2 for my course,,,
    Then Happy Fish will generate DB2 compatible DDL to create the tables, etc. Not many of the free/cheap products will do that. I assume you know something about data modeling and third normal form (or normalization)?
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  11. #11
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you want to post your desing here, I (and I am sure others) will critique it, but I will not do it for you.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  12. #12
    Join Date
    Dec 2009
    Posts
    8
    YES i'm familiar with the data modeling and 3nf, but I want the actual DDL and how to establish the relational data model diagram and the generalization n how to map it to Relational DB,,,

  13. #13
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If you create the tables in the Happy Fish design tool, then it will generate the DDL to create the real tables in DB2.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  14. #14
    Join Date
    Dec 2009
    Posts
    8
    what about the generalization and how tohow to map it to Relational DB,,,

  15. #15
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by donno View Post
    what about the generalization and how tohow to map it to Relational DB,,,
    I don't know what you mean by generalization. The modeling tool will design a relational model (tables) which map directly to DB2 tables, foreign keys, etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

Posting Permissions

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