Question

IS 633 Assignment 1

Due 9/27

Please submit SQL statements as a plain text file (.txt). If blackboard rejects txt file you can submit a zipped file containing the text file. Word, PDF, or Image format are not accepted. You do not need to show screen shot. Make sure you have tested your SQL statements in Oracle 11g.

Problem 1. Please create the following tables for a tool rental database with appropriate primary keys & foreign keys. [30 points]

Assumptions:

1. Each tool belongs to a category.
2. Each category may have a parent category but the parent category should not have parent category (so at most two levels). E.g., a Tool A belongs to electric mower, and electric mower belongs to mower. Mower has no parent category.
3. Each tool can be rented at different time units. The typical time units are hourly, daily, and weekly. There is a different price for each time unit and tool combination. E.g., tool A may be rented at $5 per hour, $30 per day, and $120 per week.
4. Each customer can rent a tool for a certain number of time units. If the tool is returned late a late fee will be charged.

  

The list of tables is:

Tables:

Cust Table:

cid, -- customer id

cname, --- customer name

cphone, --- customer phone

cemail, --- customer email

Category table:

ctid, --- category id

ctname, --- category name

parent, --- parent category id since category has a hierarchy structure, power washers, electric power washers, gas power washers. You can assume that there are only two levels.

Tool:

tid, --- tool id

tname, --- tool name

ctid, --- category id, the bottom level.

quantity, --- number of this tools

Time_unit table allowed renting unit

tuid, --- time unit id

len, --- length of period, can be 1 hour, 1 day, etc.

min_len, --- minimal #of time unit, e.g., hourly rental but minimal 4 hours.

Tool_Price:

tid, --- tool id

tuid, --- time unit id

price, -- price per period

Rental:

rid, --- rental id

cid, --- customer id

tid, --- tool id

tuid, --- time unit id

num_unit, --- number of time unit of rental, e.g., if num_unit = 5 and unit is hourly, it means 5 hours.

start_time, -- rental start time

end_time, --- suppose rental end_time

return_time, --- time to return the tool

credit_card, --- credit card number

total, --- total charge

Problem 2. Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints. [20 points]

Problem 3. Please write ONE SQL statement for each of the following tasks using tables created in Problem 1. You can ONLY use conditions listed in the task description. Task 1 and 2 each has 5 points. Tasks 3 to 6 each has 10 points. [50 points]

Task 1: return IDs of rentals started in August 2019.

Hint: function trunc(x) converts x which is of timestamp type into date type.

Task 2: Return names and quantity of all tools under the category carpet cleaner. You can assume there is no subcategory under carpet cleaner

Task 3: return number of rentals per customer along with customer ID in the year 2019 (i.e., the start_time of the rental is in 2019).

Task 4: return IDs of tools that has been rented at least twice in 2019.

Task 5: return the price of renting a small carpet cleaner (the name of the tool) for 5 hours.

Hint: find unit price for hourly rental and then multiply that by 5.

Task 6: return names of customers who have rented at least twice in year 2019 (i.e., rental’s start time is in 2019).

Answer 1

1. CREATE TABLE cust (
cid int NOT NULL, cname varchar(255) NOT NULL, cphone int, cemail varchar(255),
PRIMARY KEY (cid));

2. CREATE TABLE category (
ctid int NOT NULL, ctname varchar(255) NOT NULL, parent varchar(255),
PRIMARY KEY (ctid));

3. CREATE TABLE tool (
tid int NOT NULL, tname varchar(255) NOT NULL, ctid int, quantity int, time_unit varchar(255), tuid int,
len varchar(255), min_len varchar(255), PRIMARY KEY(tid), FOREIGN KEY(ctid) REFERENCES category(ctid));
  
4. CREATE TABLE tool_price (
tid int NOT NULL, tuid int, price float,
FOREIGN KEY(tid) REFERENCES tool(tid));
     
5. CREATE TABLE rental (
rid int NOT NULL, cid int, tid int, tuid int, num_unit varchar(255),
start_time timestamp, end_time timestamp, return_time timestamp, credit_card varchar(255),
total varchar(255), PRIMARY KEY(rid), FOREIGN KEY(cid) REFERENCES cust(cid), FOREIGN KEY(tid) REFERENCES tool(tid));