Question

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

Answer 1

Syntax for creating database

create database tool;

Syntax for selecting database

use tool_rental;

Syntax for creating Customer Table

create table cust_table(cid int NOT NULL,cname varchar(30),cphone varchar(11),cemail varchar(100), PRIMARY KEY(cid));

Syntax for creating Category table

create table category_table(ctid int NOT NULL,ctname varchar(50),parent varchar(50),primary key(ctid));

Syntax for Tool table

create table tool(tid int not null,tname varchar(50),ctid int not null, quantity int, primary key(tid),foreign key(ctid) references category_table(ctid));

Syntax for Time Unit table

create table time_unit(tuid int not null,len_hours int,min_len int, primary key(tuid));

Syntax for Tool price table

create table tool_price(tid int not null,tuid int not null,price float,foreign key(tid) references tool(tid),foreign key(tuid)references time_unit(tuid));

Syntax for Rental Table

create table rental(rid int not null,cid int not null,tid int not null,num_unit int,start_time datetime,end_time datetime,return_time datetime,creadit_card int,total int,primary key(rid),foreign key(cid) references cust_table(cid),foreign key(tid) references tool(tid));