Directions: Physical DB Design (Process Driven Design): Make tables(Relational Model) and files (sequence of records) an

[answerhappygod]

Directions:
Physical DB Design (Process Driven Design):
Make tables(Relational Model) and files (sequence of records)
and records are a (sequence of fields) .  Analyze the workload of
each SQL statement, from Business functions (Deliverable 1), by
stating its estimated frequency and performance goal  Select an
appropriate file organization for each table  Write SQL statements
to define at most 2 indexes per table to improve performance with
justification.
Here's the SQL::
DROP TABLE IF EXISTS Customers;
DROP TABLE IF EXISTS Plans;
DROP TABLE IF EXISTS Equipment;
DROP TABLE IF EXISTS Rentals;
DROP TABLE IF EXISTS Contracts;
CREATE TABLE Customers(
custID INT NOT NULL AUTO_INCREMENT,
firstName VARCHAR(255) NOT NULL,
lastName VARCHAR(255) NOT NULL,
email VARCHAR(255) NOT NULL,
address VARCHAR(255) NOT NULL,
login VARCHAR(255) NOT NULL,
password VARCHAR(255) NOT NULL,
PRIMARY KEY (custID)
);
CREATE TABLE Plans(
planID INT NOT NULL AUTO_INCREMENT,
progName VARCHAR(255) NOT NULL,
maxNoRentals INT NOT NULL,
weeklyFee INT NOT NULL,
PRIMARY KEY (planID)
);
CREATE TABLE Equipment(
equipID INT NOT NULL AUTO_INCREMENT,
equipName VARCHAR(255) NOT NULL,
type VARCHAR(255) NOT NULL,
model VARCHAR(255) NOT NULL,
yearMade INT NOT NULL,
maintDate DATE NOT NULL,
status VARCHAR(255) NOT NULL,
PRIMARY KEY (equipID)
);
CREATE TABLE Rentals(
equipID INT NOT NULL,
custID INT NOT NULL,
rentDateTime DATETIME NOT NULL,
status VARCHAR(255) NOT NULL,
PRIMARY KEY (equipID, custID, rentDateTime),
FOREIGN KEY (equipID) REFERENCES Equipment(equipID),
FOREIGN KEY (custID) REFERENCES Customers(custID)
);
CREATE TABLE Contracts(
custID INT NOT NULL,
planID INT NOT NULL,
since DATE NOT NULL,
startedDate DATE NOT NULL,
endedDate DATE NOT NULL,
contractLength INT NOT NULL,
PRIMARY KEY (custID),
FOREIGN KEY (custID) REFERENCES Customers(custID),
FOREIGN KEY (planID) REFERENCES Plans(planID)
);
INSERT INTO Customers (firstName, lastName, email, address,
login, password)
VALUES ('John', 'Smith', '., '123 Main Street', 'jsmith',
'password');
INSERT INTO Customers (firstName, lastName, email, address, login,
password)
VALUES ('Jane', 'Doe', '456 Elm Street', 'jdoe',
'password');
INSERT INTO Customers (firstName, lastName, email, address, login,
password)
VALUES ('Bill', 'Jones', '., '789 Oak Street', 'bjones',
'password');
INSERT INTO Plans (progName, maxNoRentals, weeklyFee)
VALUES ('Basic', 3, 10);
INSERT INTO Plans (progName, maxNoRentals, weeklyFee)
VALUES ('Rental Plus', 10, 20);
INSERT INTO Equipment (equipName, type, model, yearMade,
maintDate, status)
VALUES ('Drill', 'Power Tool', 'DEWALT', 2010, '2020-04-01',
'Available');
INSERT INTO Equipment (equipName, type, model, yearMade, maintDate,
status)
VALUES ('Circular Saw', 'Power Tool', 'DEWALT', 2011,
'2020-04-01', 'Available');
INSERT INTO Equipment (equipName, type, model, yearMade, maintDate,
status)
VALUES ('Hammer', 'Hand Tool', 'Stanley', 2012, '2020-04-01',
'Available');
INSERT INTO Rentals (equipID, custID, rentDateTime,
status)
VALUES (1, 1, '2020-04-01 10:00:00', 'Good Condition');
INSERT INTO Rentals (equipID, custID, rentDateTime, status)
VALUES (2, 1, '2020-04-01 10:00:00', 'Good Condition');
INSERT INTO Rentals (equipID, custID, rentDateTime, status)
VALUES (3, 1, '2020-04-01 10:00:00', 'Good Condition');
INSERT INTO Contracts (custID, planID, since, startedDate,
endedDate, contractLength)
VALUES (1, 1, '2020-04-01', '2020-04-01', '2020-05-31',
4);
INSERT INTO Contracts (custID, planID, since, startedDate,
endedDate, contractLength)
VALUES (2, 2, '2020-04-02', '2020-04-02', '2020-05-30',
4);
INSERT INTO Contracts (custID, planID, since, startedDate,
endedDate, contractLength)
VALUES (3, 1, '2020-04-03', '2020-04-03', '2020-05-29',
4);