SqlChanges.txt

Original Tables:

CREATE TABLE `User` (
 `Username` varchar(50) NOT NULL DEFAULT '',//Remove Default
 `Email` varchar(50) NOT NULL,
 `Password` char(32) NOT NULL,
 `UserType` enum('ADMIN','OWNER','VISITOR') NOT NULL,
 PRIMARY KEY (`Username`),
 UNIQUE KEY `Email` (`Email`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Property` (
 `ID` int(11) NOT NULL DEFAULT '0',
 `Name` varchar(255) NOT NULL,
 `Size` float NOT NULL,
 `IsCommercial` tinyint(1) NOT NULL,
 `IsPublic` tinyint(1) NOT NULL,
 `Street` varchar(255) NOT NULL,
 `City` varchar(100) NOT NULL,
 `Zip` int(11) NOT NULL,
 `PropertyType` enum('FARM','GARDEN','ORCHARD') NOT NULL,
 `Owner` varchar(50) NOT NULL,
 `ApprovedBy` varchar(50) DEFAULT NULL,
 PRIMARY KEY (`ID`),
 UNIQUE KEY `Name` (`Name`),
 KEY `Owner` (`Owner`),
 KEY `ApprovedBy` (`ApprovedBy`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

CREATE TABLE `Visit` (
 `Username` varchar(50) NOT NULL DEFAULT '',
 `PropertyID` int(11) NOT NULL DEFAULT '0',
 `VisitDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 `Rating` int(11) NOT NULL,
 PRIMARY KEY (`Username`,`PropertyID`),
 KEY `PropertyID` (`PropertyID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `FarmItem` (
 `Name` varchar(100) NOT NULL DEFAULT '',
 `IsApproved` tinyint(1) NOT NULL,
 `Type` enum('ANIMAL','FRUIT','FLOWER','VEGETABLE','NUT') NOT NULL,
 PRIMARY KEY (`Name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

CREATE TABLE `Has` (
 `PropertyID` int(11) NOT NULL DEFAULT '0',
 `ItemName` varchar(100) NOT NULL DEFAULT '',
 PRIMARY KEY (`PropertyID`,`ItemName`),
 KEY `ItemName` (`ItemName`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

Changes Summary:
//foreign keys
//constraints for values
//fixing some types
//remove default
//autoincrement keys
//User Property Visit FarmItem Has

Changes

alter table User ENGINE = INNODB;
alter table User add constraint chk_email check (Email like '%_@__%.__%');
alter table User alter column Username drop default;

alter table Property ENGINE = INNODB;
alter table Property alter column ID drop default;
alter table Property modify ID Int(11) UNSIGNED NOT NULL AUTO_INCREMENT;
alter table Property modify Size float UNSIGNED NOT NULL;
alter table Property modify ZIP INT(5) UNSIGNED ZEROFILL NOT NULL;
alter table Property drop Index Owner;
alter table Property drop Index ApprovedBy;
alter table Property add constraint fk_Owner Foreign Key(Owner) REFERENCES User(Username) ON UPDATE CASCADE ON DELETE CASCADE;
alter table Property add constraint fk_ApprovedBy Foreign Key(ApprovedBy) REFERENCES User(Username) ON UPDATE CASCADE ON DELETE CASCADE;

alter table Visit ENGINE = INNODB;
alter table Visit alter column Username drop default;
alter table Visit alter column PropertyID drop default;
alter table Visit drop Index PropertyID;
alter table Visit modify PropertyID Int(11) UNSIGNED NOT NULL;
alter table Visit add constraint fk_PropertyID Foreign Key(PropertyID) REFERENCES Property(ID) ON UPDATE CASCADE ON DELETE CASCADE;
alter table Visit add constraint fk_Username Foreign Key(Username) REFERENCES User(Username) ON UPDATE CASCADE ON DELETE CASCADE;
alter table Visit modify Rating TINYINT(1) UNSIGNED NOT NULL;
alter table Visit add constraint chk_rating check (Rating >= 1 AND Rating <= 5);
alter table Visit add constraint chk_VisitDate_future check (VisitDate <= CURRENT_TIMESTAMP);

alter table FarmItem ENGINE = INNODB;
alter table FarmItem alter column Name drop default;


alter table Has ENGINE = INNODB;
alter table Has alter column PropertyID drop default;
alter table Has alter column ItemName drop default;
alter table Has drop Index ItemName;
alter table Has modify PropertyID Int(11) UNSIGNED NOT NULL;
alter table Has add constraint fk_ItemName Foreign Key(ItemName) REFERENCES FarmItem(Name) ON UPDATE CASCADE ON DELETE CASCADE;
alter table Has add constraint fk_PropertyID_Has Foreign Key(PropertyID) REFERENCES Property(ID) ON UPDATE CASCADE ON DELETE CASCADE;