USE FinalTenMan;

CREATE TABLE Tenant
(TenantNum char(4) PRIMARY KEY,
TName char(25),
Street char (25),
City char(15),
State char(2),
Zip char(5),
Phone char(13),
ContactPerson char(25),
LeaseStart date,
LeaseEnd date,
BaseSales decimal(10,2),
PercentRate decimal(3,2))
Engine=InnoDB;

CREATE TABLE CostType
(Type char(1) PRIMARY KEY,
Description char(20))
Engine=InnoDB;

CREATE TABLE RentCosts
(RentCostNum MEDIUMINT NOT NULL
AUTO_INCREMENT PRIMARY KEY,
TenantNum char(4),
Type char(1),
Amount decimal(10,2),
StartDate date,
EndDate date,
FOREIGN KEY (TenantNum)
REFERENCES Tenant (TenantNum),
FOREIGN KEY (Type)
REFERENCES CostType (Type))
Engine=InnoDB;

CREATE TABLE AccountsReceivable
(ARNum MEDIUMINT NOT NULL
AUTO_INCREMENT PRIMARY KEY,
InvoiceNum char(8),
Type char(1), 
TenantNum char(4),
ARDate date,
Amount decimal(10,2),
FOREIGN KEY (TenantNum)
REFERENCES Tenant (TenantNum),
FOREIGN KEY (Type)
REFERENCES CostType (Type))
Engine=InnoDB;

CREATE TABLE ARHistory
(ARNum MEDIUMINT NOT NULL PRIMARY KEY,
InvoiceNum char(8),
Type char(1), 
TenantNum char(4),
ARDate date,
Amount decimal(10,2),
FOREIGN KEY (TenantNum)
REFERENCES Tenant (TenantNum),
FOREIGN KEY (Type)
REFERENCES CostType (Type))
Engine=InnoDB;

CREATE TABLE TenantSales
(TenantNum char(4),
SalesDate date,
Amount decimal(10,2),
PRIMARY KEY (TenantNum, SalesDate),
FOREIGN KEY (TenantNum)
REFERENCES Tenant (TenantNum))
Engine=InnoDB;