Question

Please create the following tables for a tool rental database with appropriate

primary keys & foreign keys.

Assumptions:

1. Each tool belongs to a category.

2. Each category may have a parent category but the parent category should not have

a parent category (so at most two levels). E.g., a Tool A belongs to the electric mower,

and electric mowers belong to mowers. 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:

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_Table:

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_Table:

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

- Insert at least three rows of data to each table. Make sure you keep the

primary key and foreign key constraints.

-Write an anonymous PL/SQL program to compute the sum of 2, 4, 6, 8, 10.

You must use a loop. Tip: consider how to update your loop variable.

- Print out the estimated charge for rental ID 1 if the customer returns the tool in

time. The charge is computed by the price in the price_tool table * number of units the

customer plans to rent. E.g., if a customer rents a tool hourly for 5 hours, and the hourly rate for the tool is $6, the estimated charge should be $30.

Answer 1

Hello, I am adding comments and sample output for better and clear understanding. Please find the answer attached below. THANK YOU.

/* Creating Cust_table */
CREATE TABLE Cust_table(cid integer PRIMARY KEY, cname varchar(20),cphone int,cemail varchar(40));
/* Cust_table do not have any foreign key*/
/* Creating a record in this table */
INSERT INTO Cust_table VALUES(1,'Tom',994571855,'[email protected]');



/* Creating Category_table */
CREATE TABLE Category_table(ctid integer PRIMARY KEY, ctname varchar(20),parent varchar(30));
/* category_table do not have any foreign key*/
/* Creating a record in this table */
INSERT INTO Category_table VALUES(501,'electric motor','motor');



/* Creating tool_table */
CREATE TABLE tool_table(tid integer PRIMARY KEY, tname varchar(20),quantity integer,ctid integer references Category_table(ctid));
/* Creating a record in this table */
INSERT INTO tool_table VALUES(10,'A_motor',1,12);



/* Creating time_unit_table */
CREATE TABLE time_unit_table(tuid integer PRIMARY KEY, len varchar(10),Min_length varchar(10));
/* Creating a records in this table */
INSERT INTO time_unit_table VALUES(101,'1day','3 hours');


/* Creating tool_price_table */
CREATE TABLE tool_price_table(tid integer PRIMARY KEY references tool_table(tid), tuid integer references time_unit_table(tuid),price integer);
/* tid and tuid are foreign keys*/
/* Creating a records in this table */
INSERT INTO tool_price_table VALUES(201,101,5);


/* Creating rental_table */
CREATE TABLE rental_table(rid integer PRIMARY KEY,cid integer references Cust_table(cid),tid integer references tool_table(tid),tuid integer references time_unit_table(tuid),num_unit integer, start_time time,end_time time,return_time time,credit_card integer,total integer );
/* tid and tuid are foreign keys*/
/* Creating a records in this table */
INSERT INTO rental_table VALUES(401,501,201,101,51,"10:2:30","13:2:30","15:22:02",1589785,150);



/* Give command "show tables" after creating all 
these tables to get the structure of each of the table */

/* Display all the records from all the table using the following commands */

SELECT * FROM Cust_table;

SELECT * FROM Category_table;

SELECT * FROM tool_table;

SELECT * FROM time_unit_table;

SELECT * FROM tool_price_table;

SELECT * FROM rental_table;

/* after executing all the select statements we will get the following output */

1|Tom|994571855|[email protected]
501|electric motor|motor
10|A_motor|1|12
101|1day|3 hours
201|101|5
401|501|201|101|51|10:2:30|13:2:30|15:22:02|1589785|150