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.
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 . Insert at least three rows of data to each table above. Make sure you keep the primary key and foreign key constraints. [20 points]
--Table: Customer (Primary key is cid)
---------------
insert into Customer(cid,cname,cphone,cemail)
values(100,'Ramya',9500123321,'[email protected]');
insert into Customer(cid,cname,cphone,cemail)
values(101,'Aaruu',9500123322,'[email protected]');
insert into Customer(cid,cname,cphone,cemail)
values(102,'Saaai',9500123323,'[email protected]');
--Table: Category(Primary key is ctid)
---------------
insert into Category(ctid,ctname,parent)
values(1000,'Electronics',2000);
insert into Category(ctid,ctname,parent)
values(2000,'Automobile',3000);
insert into Category(ctid,ctname,parent)
values(3000,'Furniture',null);
--Table: Tool(Primary key is tid and foreign key is ctid)
---------------
insert into Tool(tid,tname,ctid,quantity,time_unit)
values(111,'AC',1000);
insert into Tool(tid,tname,ctid,quantity,time_unit)
values(222,'Speaker',1000);
insert into Tool(tid,tname,ctid,quantity,time_unit)
values(333,'Refridgerator',1000);
--Table: Time_unit (Primary key is tuid)
---------------
insert into Time_unit(tuid,len,min_len) values(10,15,2)
insert into Time_unit(tuid,len,min_len) values(20,25,3)
insert into Time_unit(tuid,len,min_len) values(30,35,4)
--Table: Tool_Price(It is a bridge table between Tool and
Time_unit tables, Foreign keys are tid and tuid)
---------------
insert into Tool_Price(tid,tuid,price) values(111,10,300);
insert into Tool_Price(tid,tuid,price) values(222,20,400);
insert into Tool_Price(tid,tuid,price) values(333,30,500);
--Table: Rental(Primary key is ric and foreign keys are cid, tid
and tuid)
---------------
insert into
Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(1,100,111,10,340,2019-10-09 12:00:00,2019-10-01
10:00:00,2019-10-12 10:00:00,435628675912,1800)
insert into
Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(2,101,222,20,670,2019-10-09 12:00:00,2019-10-01
10:00:00,2019-10-12 10:00:00,435628675912,2750)
insert into
Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)
values(3,102,333,30,890,2019-10-09 12:00:00,2019-10-01
10:00:00,2019-10-12 10:00:00,435628675912,6950)