-- Intro to Database Systems
-- Daniel Scott
-- Part 1 - Database Structure using MySQL

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Database: `rvddps_uni`
--

-- --------------------------------------------------------

--
-- Table structure for table `Animal`
--

CREATE TABLE IF NOT EXISTS `Animal` (
  `AnimalType` varchar(12) COLLATE utf8_unicode_ci NOT NULL,
  `SubsidyRate` float NOT NULL,
  PRIMARY KEY (`AnimalType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Animal`
--

INSERT INTO `Animal` (`AnimalType`, `SubsidyRate`) VALUES
('Bull', 1200.51),
('Cow', 753.935),
('Goat', 0),
('Pig', 0),
('Sheep', 669.564);

-- --------------------------------------------------------

--
-- Table structure for table `ClaimHistory`
--

CREATE TABLE IF NOT EXISTS `ClaimHistory` (
  `ClaimNo` int(8) NOT NULL,
  `ClaimAmount` float NOT NULL,
  `Date` date NOT NULL,
  `ClaimCount` int(11) NOT NULL,
  `RegionName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ClaimCount`),
  KEY `RegionName` (`RegionName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `ClaimHistory`
--

INSERT INTO `ClaimHistory` (`ClaimNo`, `ClaimAmount`, `Date`, `ClaimCount`, `RegionName`) VALUES
(120, 6000, '2009-05-19', 6, 'East'),
(121, 1520, '2010-02-04', 7, 'North'),
(121, 600, '2010-02-16', 8, 'East'),
(122, 500, '2010-02-17', 9, 'North');

-- --------------------------------------------------------

--
-- Table structure for table `Crops`
--

CREATE TABLE IF NOT EXISTS `Crops` (
  `CropType` varchar(7) COLLATE utf8_unicode_ci NOT NULL COMMENT 'Max length 7 due to no registered crops being longer than that.',
  `SubsidyRate` float NOT NULL,
  PRIMARY KEY (`CropType`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Crops`
--

INSERT INTO `Crops` (`CropType`, `SubsidyRate`) VALUES
('Cereal', 227.095),
('Linseed', 440.878),
('Oilseed', 553.589),
('Protein', 229.302);

-- --------------------------------------------------------

--
-- Table structure for table `CurrentClaim`
--

CREATE TABLE IF NOT EXISTS `CurrentClaim` (
  `ClaimNo` int(11) NOT NULL,
  `ClaimAmount` float NOT NULL,
  `Date` date NOT NULL,
  `RegionName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`ClaimNo`),
  KEY `RegionName` (`RegionName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `CurrentClaim`
--

INSERT INTO `CurrentClaim` (`ClaimNo`, `ClaimAmount`, `Date`, `RegionName`) VALUES
(123, 0, '2010-02-18', 'North'),
(124, 0, '2010-02-18', 'North');

-- --------------------------------------------------------

--
-- Table structure for table `Farm`
--

CREATE TABLE IF NOT EXISTS `Farm` (
  `FarmName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `FarmerFirstName` varchar(15) COLLATE utf8_unicode_ci NOT NULL,
  `FarmerSurname` varchar(20) COLLATE utf8_unicode_ci NOT NULL,
  `Address` varchar(50) COLLATE utf8_unicode_ci NOT NULL,
  `PhoneNumber` int(11) NOT NULL,
  `ClaimNumber` int(8) NOT NULL,
  `RegionName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `NoOfCows` int(11) NOT NULL,
  `NoOfGoats` int(11) NOT NULL,
  `NoOfSheep` int(11) NOT NULL,
  `NoOfPigs` int(11) NOT NULL,
  `NoOfBulls` int(11) NOT NULL,
  `Cereal` int(11) NOT NULL,
  `Oilseed` int(11) NOT NULL,
  `Linseed` int(11) NOT NULL,
  `Protein` int(11) NOT NULL,
  `CurrentSubsidyEntitlement` int(11) NOT NULL,
  PRIMARY KEY (`ClaimNumber`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Farm`
--

INSERT INTO `Farm` (`FarmName`, `FarmerFirstName`, `FarmerSurname`, `Address`, `PhoneNumber`, `ClaimNumber`, `RegionName`, `NoOfCows`, `NoOfGoats`, `NoOfSheep`, `NoOfPigs`, `NoOfBulls`, `Cereal`, `Oilseed`, `Linseed`, `Protein`, `CurrentSubsidyEntitlement`) VALUES
('NFarm1', 'John1', 'TheFarmer1', 'Countryside', 123456, 123, 'North', 10, 10, 10, 10, 10, 100, 100, 100, 100, 0),
('NFarm2', 'John2', 'TheFarmer2', 'Countryside2', 121215, 124, 'North', 11, 11, 11, 11, 11, 101, 101, 101, 101, 0),
('NFarm3', 'John3', 'TheFarmer3', 'Countryside3', 3578174, 125, 'North', 12, 12, 12, 12, 12, 102, 102, 102, 102, 0),
('EFarm1', 'John4', 'TheFarmer4', 'Countryside4', 1525748, 126, 'East', 13, 13, 13, 13, 13, 103, 103, 103, 103, 0),
('EFarm2', 'John5', 'TheFarmer5', 'Countryside5', 54946177, 127, 'East', 14, 14, 14, 14, 14, 104, 104, 104, 104, 0);

-- --------------------------------------------------------

--
-- Table structure for table `Region`
--

CREATE TABLE IF NOT EXISTS `Region` (
  `RegionName` varchar(30) COLLATE utf8_unicode_ci NOT NULL,
  `Farms` int(11) NOT NULL,
  PRIMARY KEY (`RegionName`),
  KEY `RegionName` (`RegionName`),
  KEY `RegionName_2` (`RegionName`),
  KEY `RegionName_3` (`RegionName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

--
-- Dumping data for table `Region`
--

INSERT INTO `Region` (`RegionName`, `Farms`) VALUES
('East', 2),
('North', 3);

--
-- Constraints for dumped tables
--

--
-- Constraints for table `ClaimHistory`
--
ALTER TABLE `ClaimHistory`
  ADD CONSTRAINT `ClaimHistory_ibfk_1` FOREIGN KEY (`RegionName`) REFERENCES `Region` (`RegionName`);

--
-- Constraints for table `CurrentClaim`
--
ALTER TABLE `CurrentClaim`
  ADD CONSTRAINT `CurrentClaim_ibfk_1` FOREIGN KEY (`RegionName`) REFERENCES `Region` (`RegionName`);