Assignment 2 Solution

Question 1



You are given a task to create a Database for Holiday Inn. Here is some of the information you have.




HOTEL

hNum: It is a unique id assigned to each hotel

hNoOfRooms: Number of rooms in a hotel

hPhone: It is the hotel phone number

hAdd: It is the hotel address

ROOM

rNum: It is the room number in a hotel

rRate: It is the rate of a room in a hotel per night

rSize: It is an integer to indicate the size of the room (ex: 2 means two bedroom)

rOccupied: It has a value of True or False to indicate if the room is occupied in a hotel

rLoc: It refers to the location of a room in a hotel (ex: 2 means second-floor)

MANAGER

mId: It is the emp id of the hotel manager

mName: It refers to the name of the manager who manages the hotel

mAdd: It refers to the address of the manager

mPhone: It is the personal cell phone number of the manager (not the hotel phone Num)

mEmail: It is the email of the manager

mSal: It is the salary of the manager

CARETAKER

ctId: It is the emp id of the hotel care taker

ctName: It is the name of the caretaker who takes care of the maintenance of a hotel

ctAdd: It refers to address of the caretaker

ctPhone: It is the personal cell phone number of the caretaker

ctEmail: It refers to email of the caretaker

ctSal: It is the salary of the caretaker

CUSTOMER

cNum: It is the customer number of a customer who is staying in a hotel

cSSN: It is the SSN of the customer

cName: It is the name of the customer

cCreditCart: It is the credit cart number of the customer

cAdd: It is the personal address of the customer

cStartDate: It indicates the date when the customer has checked in

cEndDate: It indicates the date the customer checks out.

cAmtOwing: It is the amount the customer owes to the hotel










Assumptions:

Since you are creating this database for the Holiday Inn Hotel, you do not have to give any name to a specific hotel. They are all called Holiday Inn. Holiday Inn has several branches in North America. Each branch is uniquely identified by its “hNum”. In fact “hNum” attribute refers to the branch number of the hotel
Each manager may manage more than one branch of the Holiday Inn, but each hotel is managed by only 1 manager
Each customer can stay in one hotel and can only be given one room in that hotel
Each caretaker works for only one hotel but a hotel can have many caretakers.
Rooms in a particular hotel are unique but may not be unique across all branches. For example, branch 10 has only one room with room number 100 but branch 20 may also have a room number 100. These rooms may be completely different in terms of size, rate, etc.



Draw your ERD based on the above assumptions and data
Draw tables from the initial ERD and normalize them. Place all tables in 3rd normal form (if necessary)
Revise your ERD (if necessary)
Create your tables based on the given types and restrictions using the following rules:



Create all table primary key constraints on the Create Table statement. Add all Foreign Key constraints after all tables have been created using the ‘Alter Table’ statement.



Beside the constraint you use for the primary and foreign keys, add the following constraints (on the Create Table statement):



hNoOfRooms: 0 and <= 200

Phone: unique and should be = ‘2202222’ and <= ‘9909999’

rNum: 0 and <= 200

rRate: = 50

rSize: = 2 and <= 4

rOccupied: = ‘false’ or = ‘true’

rLoc: 0 and <= 100

mId: = 111111 <= 999999

mPhone: = ‘2222222’ and <= ‘9999999’

ctId: = 111111 <= 999999

ctPhone: = ‘2222222’ and <= ‘9999999’

ctSal: 20000 and < 40000

cSSN: unique and ‘660000000’ and <= ‘609999999’
















Data Item (Column Name)
Type
Size
hNum
Integer


hNoOfRooms
Integer


hPhone
Fixed Character
7
hAdd
Character
Up to 50
rNum
Integer


rRate
Decimal
5 with 2 decimals
rSize
Integer


rOccupied
Fixed Character
5
rLoc
Integer


mId
Integer


mName
Character
Up to 50
mAdd
Character
Up to 50
mPhone
Fixed Character
7
New: mEmail
Character
Up to 100
mSal
Decimal
7 with 2 decimals
ctId
Integer


ctName
Character
Up to 50
ctAdd
Character
Up to 50
ctPhone
Fixed Character
7
ctEmail
Character
Up to 200
ctSal
Decimal
7 with 2 decimals
cNum
Integer


cSSN
Fixed Character
9
cName
Character
Up to 50
cCreditCart
Character
Up to 25
cAdd
Character
Up to 50
cStartDate
DATE


cEndDate
DATE


New: cAmtOwing
Decimal
7 with 1 decimals









Question 2:




Do the following SQL questions. The resulting columns must all have descriptive names







Write a select statement to list all the columns from the Offices table. 'Select *' is not allowed. Return the list in Office order.









List the Product Name (its description), and dollar value of quantity on hand (price * quantity) . Return the list in descending order by value.






New: List the Order Number, Order Date, Customer Number and Sales Rep Number for orders for part 'XK47' or '775C'. (Use a compound search condition - OR.)









List the Name and Age for all Sales Reps in the following offices: 12; 21; 13. (Use the set membership test - IN.)









List the names of all Sales Reps who have the letter 'o' (this is lower case o) as the second character of their name.









Return the Sales Rep ID and Name of any Sales Rep not assigned to an office yet.









Show all the sales rep names with last name “Smith”.









List different titles in the sales reps table. Only list each title once and unknown titles should be ignored.






List the description of the products which are at least 6 character and less than 10 character long. No duplicate row is allowed. You can use the build in function length() to do this. For example, length(name) return the number of characters for attribute called “name”.






List the the order nums with the name of the rep who placed the order and the name of the customer who made the order and the name of the rep for that customer


















Question 3:

List the Mfr Id, the Product Id, Company and PRICE of all products brought by customers where customer number is one of (2112,2105,2119) and where the amount of the order < $5000.00. Order the results by ascending Company.



List all salesreps (id and names) and all orders (orderNumber) in which the salesrep is the company’s (i.e. the customer) rep (Cust Rep), but didn’t take the order. Order the result based on name in ascending order.



List all customers (Customer number, Company, and Credit Limit) and orders (Order Number, Amount) where the order is within $10000.00 of the Credit Limit (less than or equal to $10000). Sort the result by Customer number in descending order.



List all salesreps names and their managers’ names where the salesrep is at least as old as the manager.



List all salesreps (Name) and the City they work in where the sales of the salesrep < Quota and the sales for the office is < Target.
List the name, id, and hire date of the salesreps where at least one of the two conditions hold:
The salesrep sales should be greater than the quota
The salesrep has taken an order from Customer number 2117, 2111, or 2101.
Sort the result by the salesrep’s id.




List all orders (Order Number) over $25000 showing the company placing the order, the Customer Rep assign to the company, the Office id and the city where the Customer Rep works in, such that the Customer Rep’s manager is not the person who actually took the order.






List all customer reps (their name and their id) and their managers name in which the manager has taken an order for the customer Rep’s company or the manager is based in New York or Denver. Use appropriate column header



List all products (ProductId, and Description), customers (CustNum, Company) who have bought that product, and orders (Order Number, and Order Date) where the order < $1000. Sort the rows by the OrderDate.
List the name of the salesreps and the name of their managers only if the manager has taken care of some orders.