Question

Insert at least three rows of data to each table. Make sure you keep the primary key and foreign key constraints.

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

Answer 1

Cust Table: Insert Querys

INSERT INTO Cust (cid,cname,cphone,cemail) VALUES ("C1001","John",1234567890,"[email protected]");

INSERT INTO Cust (cid,cname,cphone,cemail) VALUES ("C1002","Abraham",9876543210,"[email protected]");

INSERT INTO Cust (cid,cname,cphone,cemail) VALUES ("C1003","Joe",6543219870,"[email protected]");

Category Table :Insert Querys :

INSERT INTO Cust (ctid,ctname,parent) VALUES ("CT1001","Electronics","CT1002");

INSERT INTO Cust (ctid,ctname,parent) VALUES ("CT1002","Power Electronics","CT1001");

INSERT INTO Cust (ctid,ctname,parent) VALUES ("CT1003"," Gas Electronics","CT1001");

Tool Table:Insert Querys:

INSERT INTO TOOL (tid,tname,ctid,quantity) VALUES ("T1001","Desktop","CT1002",10);

INSERT INTO TOOL (tid,tname,ctid,quantity) VALUES ("T1002","laptop","CT1002",100);

INSERT INTO TOOL (tid,tname,ctid,quantity) VALUES ("T1003","Iron box","CT1001",40);

Time_unit:Insert Querys:

// Considering that length will be in hours

INSERT INTO Time_unit (tuid,len,min_len) VALUES ("TU1001",72,4);

INSERT INTO Time_unit (tuid,len,min_len) VALUES ("TU1002",48,6);

INSERT INTO Time_unit (tuid,len,min_len) VALUES ("TU1003",24,2);

Tool_Price:INSERT Querys:

INSERT INTO Tool_Price(tid,tuid,price) VALUES ("T1001","TU1001",100);

INSERT INTO Tool_Price(tid,tuid,price) VALUES ("T1002","TU1002",400);

INSERT INTO Tool_Price(tid,tuid,price) VALUES ("T1003","TU1003",900);

Rental :INSERT Querys:

INSERT INTO Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)

VALUES ("R1001","C1001","T1001","TU1001",4,"9-12-2019 15:32PM","10-12-2019 15:32PM","10-12-2019 12:32PM",1012365478,600);

INSERT INTO Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)

VALUES ("R1002","C1002","T1002","TU1002",3,"9-12-2019 12:32PM","10-12-2019 12:32PM","10-12-2019 10:32PM",2012365488,4800);

INSERT INTO Rental(rid,cid,tid,tuid,num_unit,start_time,end_time,return_time,credit_card,total)

VALUES ("R1003","C1003","T1003","TU1003",2,"9-12-2019 10:32PM","10-12-2019 10:32PM","10-12-2019 10:32PM",2012325896,1600);