Botswana has always been one of those countries that has suffered the curse of proximity to South Africa. The proximity to South Africa has led to a lot of reliance on South Africa for almost every commodity. This has led to the countries export bill being too big. The government of Botswana has over the years implemented many schemes and programmes to encourage local production to reduce this reliance on South Africa. Once of these programmes was the ISPAAD programme. Despite good intentions of the programmes and after huge sums of money has been disbursed, it has been concluded that the programme has not yielded great results since the country still import a lot of its food from South Africa with local production only accounting for a very small percentage of the total food consumed. The government has since decided to cancel the ISPAAD programme and replace it with a new programme. With the new programmes, the government wants to link the support it provides to farmers to production or total yield rather than assisting farmers for assistance's sake. To do this, the government wants to implement a computer-based system that would assist in the management of the programme. You are hired as a consultant to build such as system. You are told the system should perform the following functions: 1. Register farmers. (ID/Passport number, first name, last name, DOB, postal address, email address, phone number). The system should also capture details of a farmer's farm details including: location, size, number of hectors debushed or ploughable area. Please note that a farmer can have more than one farms. 2. Capture production data. There two types of production information: cropping and actual: a. Cropping - this is also referred to as planning information. For this, a farmer should be able to indicate how many hectors will be planted for each type of crop (sorghum, maize, and millet). Based on some formula and the number of hectors planted, the system should be able to estimate the expected yield (in tons) for each crop b. Actual - The system should allow a farmer to capture actual production information at the end of the season. This is the actual tons they produced per crop. c. They system should implement some reward scheme rewards farmers for good production. To make the rewards scheme fair, you could categorize farmers into small scale farmer, medium scale farmer, and commercial farmer. You can therefore set targets for each of these categories and then give rewards based on how well the farmers have met the targets within their group. Your Task 1. List all the Business rules that are applicable to the scenario described above. 2. Use tools you have learnt in this course to design the database that solves the problem described above. As a guide use the following: a. Relationship table b. Entity-Relationship Model: Develop a top down design of the data in the form of an entity-relationship diagram using crow's foot notation. You should note all assumptions you make about the data and the reasoning behind your design choices. Also include any appropriate constraints and list of entities types showing their attributes and identifiers. c. Normalize your database up to 3rd Normal Form: Once you are satisfied that the ER diagram is a good representation of the data, produce a logical design by mapping the ER to a set of normalized relations. Clearly show all intermediate steps. 3. Implement your final database using Microsoft SQL Server: Take each of the relations from your relational model and implement them in SQL Server using SQL commands - i.e. do not just create the tables using the Studio in design view. You must include all primary and foreign keys as well as any table or column constraints you deem appropriate. You must supply all SQL code in your report. You must also write insert statements to populate the database with sample data (make sure that these insert statements forms part of the code files that you will submit along with your report) 4. Query your database: Using SQL, write a set of realistic sample queries based on the above scenario (use your imagination for the details of the query) but they should include the following SQL query techniques: Joins (using two, three, or more tables) Set operations (UNION, INTERSECT, and MINUS) Ordering Grouping Aggregate functions (MIN, MAX, AVG, COUNT, SUM)