2004Spring   CS157                        Computer Assignment 1.

Due date: April.6,2004

 

  You are working with a Hotel database which consists of three tables.

 

Table HOTELS

HID

NAME

CITY

STATE

LUXURY

AIRPORT_DISTANCE

DOWNTOWN_DISTANCE

1

Days In

College Park

MD

1

20

10

2

Hilton

Washington

DC

3

7

1

3

Ritz Carlton

Washington

DC

4

6

1

4

Holiday In

Washington

DC

2

5

1

5

Holiday In

Washington

DC

1

7

1

6

Holiday In

Arlington

VI

2

2

6

Table ROOMS

RID

NUM

HID

BEDS

PRICE

TV

FRIDGE

1

25

1

1

80.00

T

F

2

20

1

2

120.00

T

F

3

100

2

2

200.00

T

T

4

20

2

2

600.00

T

T

5

40

3

2

999.00

T

T

6

120

4

2

150.00

T

T

7

120

4

1

120.00

T

T

8

80

5

1

100.00

T

F

9

90

6

2

140.00

T

F

Table RESERVATIONS

RSVID

RID

GUEST

DATEIN

DAYS

NUM

1

1

A.B. Holmes

03-Feb-02

2

1

2

3

SIGX-conference

03-Feb-02

5

80

3

4

King of Z

05-Feb-02

7

1

4

3

Kings service

05-Feb-02

7

3

5

5

D.D. A

04-Feb-02

3

1

6

7

D.D. B

04-Feb-02

1

1

7

9

D.D. C

03-Feb-02

1

1

8

9

D.D. D.

04-Feb-02

1

1

9

9

D.D. E.

05-Feb-02

1

1

10

9

D.D. F.

06-Feb-02

1

1

 

Use MySQL to work on the following problems:

 

  1. Create HOTELS table in the given order of attributes
  2. Create ROOMS table in the given order of attributes and make sure that TV and FRIDGE can only have 'T' (true) or 'F' (false) values
  3. Create RESERVATIONS table in the given order of attributes
  4. Insert data in the given order in the HOTELS table
  5. Insert data in the given order in the ROOMS table
  6. Insert data in the given order in the RESERVATIONS table
  7. Show content of the HOTELS table
  8. Show content of the ROOMS table
  9. Show content of the RESERVATIONS table
  10. Show names of hotels and their room prices that cost more then 500 dolars;
  11. Show room ids and the corresponding room prices for rooms that have fridge and cost less then 130 dollars.
  12. Show the average room price
  13. Show guest or group of guests paying the most in total and the corresponding amount of money paid for the rooms
  14. Show the hotel with most expensive room
  15. Show names of hotels or hotel chains with rooms available on Feb 5, 2002
  16. Show the number of hotels in the HOTELS table
  17. Show the total number of rooms in City Washigton and state DC
  18. Reduce the price of all rooms in College Park by 50%
  19. Show the data from ROOMS table for all rooms
  20. Attempt and fail the following SQL command:
    INSERT INTO ROOMS VALUES (20, 25, 1, 1, 80.00, 'T', 'C');
  21. Attempt and fail the following SQL command:
    INSERT INTO ROOMS VALUES (21, 25, null, 1, 80.00, 'T', 'F');
  22. Attempt and fail the following SQL command:
    DELETE HOTELS WHERE City = 'College Park';
  23. Delete all hotels from College Park (be careful to this correctly!);
  24. Show the data from ROOMS table for 'College Park' rooms
  25. Drop all tables

Hints:

 

Remind:

1) Make sure that all data is input correctly

2) Use appropriate types!

3) Make sure that there is no extra space before or after any of the visable data

4) HID in ROOMS table refers to HID (the key) of the HOTELS table

5) RID in RESERVATIONS table refers to RID (the key) of the ROOMS table

6) NUM in ROOMS table refers to number of certain rooms in the system

7) NUM in RESERVATIONS table refers to number of rooms reserved

8) Data integrity may not be sacrificed!

How to submit the assignment?

The printout of different queries must be submitted. Use MySQL to create and test the queries. All of your SQL queries should be contained in a single file, which you should electronically submit. The best way to submit this, and other SQL assignments, is to “log” an MySQL . Copy, and then paste, parts of an output  into a word document to be e-mailed as an attachment. As always, enter your name, the course name and the assignment in the word document.

             For Section 1 send to cs157a1@yahoo.com

                 Section 2 send to cs157a2@yahoo.com