In: Computer Science
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:
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
We are not allowed to attach any file while answering. I can either upload an image or paste the contents. So please find below the SQL statements.
CREATE TABLE cust (
cid varchar(50) NOT NULL,
cname varchar(50) NULL,
cphone varchar(15) NULL,
cemail varchar(50) NULL,
CONSTRAINT cust_pk PRIMARY KEY (cid)
);
CREATE TABLE category (
ctid varchar(50) NOT NULL,
ctname varchar(50) NULL,
parent varchar(50) NULL,
CONSTRAINT category_pk PRIMARY KEY (ctid)
);
CREATE TABLE tool (
tool_id varchar(50) NOT NULL,
tname varchar(50) NULL,
ctid varchar(50) NULL,
quantity float(10) NULL,
CONSTRAINT tool_pk PRIMARY KEY (tool_id),
CONSTRAINT tool_fk FOREIGN KEY (ctid) REFERENCES
category(ctid)
);
CREATE TABLE time_unit (
tuid int NOT NULL,
len varchar(10) NULL,
min_len varchar(10) NULL,
CONSTRAINT time_unit_pk PRIMARY KEY (tuid)
);
CREATE TABLE tool_price (
tid varchar(50) NOT NULL,
tuid int NOT NULL,
price float NULL,
CONSTRAINT tool_price_fk FOREIGN KEY (tid) REFERENCES
tool(tool_id),
CONSTRAINT tool_price_fk_1 FOREIGN KEY (tuid)
REFERENCES time_unit(tuid)
);
CREATE TABLE rental (
rid int NOT NULL,
cid varchar(50) NULL,
tid varchar(50) NULL,
tuid int NULL,
num_unit int NULL,
start_time timestamp NULL,
end_time timestamp NULL,
return_time timestamp NULL,
credit_card int NULL,
total float NULL,
CONSTRAINT rental_pk PRIMARY KEY (rid),
CONSTRAINT rental_fk FOREIGN KEY (cid) REFERENCES
cust(cid),
CONSTRAINT rental_fk_1 FOREIGN KEY (tid) REFERENCES
tool(tool_id),
CONSTRAINT rental_fk_2 FOREIGN KEY (tuid) REFERENCES
time_unit(tuid)
);