SQL assignment that needs to be finished on Mac MYSQL

BTE/CSC 4 2 3 Database Management Systems

(No Extensions will be given)

Assignment 3 FALL 2016

Assigned: Oct 6th, 2016 Due: Oct 14th 2016 —————————————————————————————————————

1. (50 points) The following tables form part of a database held in a relational DBMS:

Hotel (hotelNo, hotelName, city) Room (roomNo, hotelNo, type, price) Booking (hotelNo, guestNo, dateFrom, dateTo, roomNo) Guest (guestNo, guestName, guestAddress)

a. Hotel contains hotel details and hotelNo is the primary key.

b. Room contains room details for each hotel and (roomNo, hotelNo) forms the primary key.

c. Booking contains details of the bookings and (hotelNo, guestNo, dateFrom) forms the primary key.

d. Guest contains guest details and guestNo is the primary key.

a) Create tables for the four relations in Oracle, MS SQL Server or MYSQL. b) Populate (i.e., insert) 10 or more sample tuples for each relation in your Database of choice.

You need to turn in the hardcopy of the SQL statements for creating the tables and inserting the tuples that you implement in Oracle, SQL Server or MYSQL.

2. (50 points) Using the relations and sample tuples in each relation that you created in Problem (1), translate the following queries into SQL statements and run the SQL statement in Oracle. Please turn in the hardcopy of the SQL statements that you implement in Oracle and the outputs that are generated from Oracle.

a) (Exercise 6.7) List full details of all hotels. b) (Exercise 6.8) List full details of all hotels in London. c) (Exercise 6.9) List the names and addresses of all guests in London, alphabetically ordered by

name.

3. (100 points) Using the same Hotel, Room, Booking, and Guest relations and sample tuples you implemented above, translate the following queries into SQL statements, and report the outputs generated from Oracle (Or the DBMS you are using) for the queries. You need to submit a hard-copy report for the SQL statements and the corresponding outputs with screenshots from your computer. Please note that you may need to insert more tuples for each relation in order to generate the results for these queries.

a. Exercise 6.13 b. Exercise 6.16 c. Exercise 6.19 d. Exercise 6.23 e. Exercise 6.25 f. Exercise 6.28 g. Exercise 7.13 h. Exercise 7.15 i. Exercise 7.16 j. Exercise 7.17

4. (50 points) Using a virtual machine, install Ubuntu 14.04 LTS (Desktop or Server). Install MYSQL Server or Oracle 12c. Connect from a client on your host computer to the database server on the virtual machine, and run the scripts and queries of questions 1-3 above again. Take screenshots and paste them in your report.