Feed ingredient database developer

This requires a database developer to work with a feed nutritionist and a web developer to deliver a functional MySQL database that the web developer can integrate into his website. Briefly the database consists of An ingredient database (e.g. soybean, fish meal etc.) with the feed code and analysis (protein, lipid, fat etc). A fish species database associating ingredients with fish species according to what the fish will eat. A supplier database where the supplier can register and his details and which ingredients he supplies. A simple recipe formulation done by selecting ingredients and percentages and getting an output analysis. Various tables that help define the above. Output Description Development of feed ingredient relational database as part of the an Aquaculture Feed Information System Activities: This will include the following activities: 1. Reorganization of Excel tables (templates) provided by the nutrition specialist with fields for a feed ingredient database (including data fields for the aquaculture species and suppliers of raw materials and sample data for testing of the database) to suit the MySQL database design requirements: a. Establish its structure based on discussions with the nutrition specialist to understand the required information output (Also refer Annex A); b. Clarify what questions will be asked of the database and what data would be included in the answers; c. Thorough normalization, to eliminate redundancies and other problems that could undermine the integrity of the data. 2. Preparation of relational database: a. Ensure the viability, performance, and reliability of the databases; b. Creation of primary and foreign keys for database integration; c. Creation of database relationships to deliver the required output; d. Ensure database is easily updated, data retrieval speed is optimised and modification at some future date is possible; e. Provide a necessary emphasis on data integrity and scalability before simplicity and speed; f. Ensure the correct data types as specified for each field, any necessary defaults and indexes; g. Troubleshoot the completed database on your own and together with the nutrition specialist; h. Use established naming conventions when possible; 3. Communicate where necessary during the integration of the database into the web application by the web developer. Discuss with the web developer and include any security requirements that need to be built into the database. 4. Testing and verification of functional website that presents data from the database; 5. Ensure that the database has a facility for further input of data. Outputs: A relational database of global feed ingredients and associated aquaculture species with a link to feed formulation tool to facilitate formulation of feed by farmers/practitioners and the ability of suppliers to register which raw materials they can supply. The database is searchable. Time frame Contract completed within a time frame of October - 31 December 2009. Terms of Reference The database developer will prepare the structure of the database, create the relational database, verify and test the database and submit the software with data set after verification with web designer. The activities as specified above will be carried as detailed in Annex A. The database developer will prepare the software using MySQL database or any other database language in prior consultation, all text material (manual to use the database) in MS Word and explanatory data in MS Excel and feed formulation component in MsSQL. The report including the manual, software and data should be written in a clear and concise manner, using figures, tables and/or photographs where necessary for clarification and completeness. This report will be drafted by both the database specialist and nutrition specialist as co-authors, with primary responsibility for the final draft being with the nutrition specialist ie the database developer will assist the nutritionist. The database developer will need to sign a contract with an established organization. Annex A Feed ingredient relational database outline Tasks involved in the project The completion of the database involves five essentially separate tasks: 1. Preparation of tables in Excel or Access with fields for a feed ingredient database including information on the aquaculture species and suppliers of raw materials; done by the nutrition specialist. 2. Test data (just enough to prepare and test the database); done by the nutrition specialist. 3. Preparation of relational database by the database developer. 4. Integration of the database into the web application by the web developer. 5. Testing and verification of functional website that presents data from the database; tested by all three. 6. Further input of data. These tasks need consideration of the “user” who will make use of this resource and involves the coordinated effort of the database developer, the webpage developer and the nutrition specialist. For the purpose of creating and testing the database, the nutrition specialist serves as the “user”. Design of database including preparation of outline of tables and relations The database developer needs to be involved in tasks 1 to 5. Tasks 1 to 5 requires a person with experience in the nutrition of aquaculture species and the “user needs” for the data output. The nutrition specialist needs to create the necessary table fields that will form the feed ingredient database created by the database developer. The preparation of the database [3] requires a database developer to integrate and where necessary design the basic database tables into a functional relational database that can be accessed from the internet application. Tasks 1 and 2 The aquaculture feed and nutrition specialist will outline the fields required for the relational database. The nutrition specialist will add the necessary sample data for the database to be tested. Tasks 3 The database developer will use the tables to create the MySQL database procedures, functions and tables for the internet/web application. For clarification of details (user and product issues) he will refer to the nutrition specialist. The database developer will review any security problems relating to the database and ensure these are closed. Task 4 A web developer will use the completed database and create the interface to interact with the database, connecting the database to the web application. The web developer will also do the web design and layout. The web developer needs to know how to connect to MySQL databases using web applications. Any web related security issue needs to be closed. Task 5 The aquaculture feed and nutrition specialist, database developer and web application developer will test the web application that connects the database to the user to ensure that it delivers information in a user-friendly way. The database developer will test it for security issues. This information will be fed back to the database developer to implement any database changes that may be needed. The web developer will also troubleshoot the web application. Task 6 To enter the data record will require a web interface to enter the data of each record or change a record. Data needs to be uploaded from an Excel file, Access database or some other convenient method as well as manual entry. (For bulk entries of data off a spreadsheet for the initial or future addition of data, the database developer or site administrator will add the data. This is inserting new records into the database. Bulk entry of data can be entered fro a spreadsheet converted to csv file format.) All the login tables should be done by the web developer: [1] User login - optional [2] Supplier login [3] Administration login to enter data Additional requirements The site administrator needs to setup the server so that the database developer can access the database remotely for troubleshooting purposes during steps 1 to 5. Description of feed ingredient database’s access via web application The database information is made available via the web application, either with or without a registration process. The access is setup be the web application developer. Different security levels will need to be specified. Feed ingredient database typical fields: These fields are defined in the file [login to view URL] and the fields in relation to an ingredient are in the file Feedstuff.xlsx. Typical feed ingredient costs are included. Prices will need to be updated regularly based on some reliable source. As well as this “default” price, the user should be able to enter in his own price when on the formulation page (see below). Digestibility would be different for each species or “null” if unknown as present. Typical feed ingredients The list of feed ingredients is essentially unlimited. Each feed ingredient is a record with the above ingredient fields and more or fewer, as required. The common and species name will be included where applicable, making two fields. A third field will be country of origin, so there may be 3 or more soybean analyses, based on country of origin, as separate records. Other fields may be added to allow searches of specific differences. For example a “type” field in say soybean meal could have the data specification of full fat, solvent extracted, mechanical pressed and protein concentrate, so for each soya record, a “type” is specified. Functionality options for the feed ingredient database when used by the web application • Database search tool or search form o Ingredient search – delivers the full feed ingredient analysis o Supplier search – linked to or provides what feed ingredients they supply ([login to view URL]) o Animal search – linked to or provides the typical feed raw materials fed to that animal ([login to view URL]) o Protein range search – delivers feed ingredients within a specified range o Lipid range search – delivers feed ingredients within a specified range o Carbohydrate range search – delivers feed ingredients within a specified range o Fibre range search – delivers feed ingredients within a specified range o This functionality could be extended to each field for each feed ingredient o Keyword search Link to animal species page – part of the web developer’s task. Link to supplier page – part of the web developer’s task. Link to alphabetic feed ingredient page. Each link goes to feed ingredient data in full detail from the database – part of the web developer’s task. Link to formulations page – part of the web developer’s task. The appearance and layout of the webpages is the responsibility of the web developer and needs to conform to the style of the website. Fish species page Alphabetic list links to each animal where the feed ingredients commonly associated with the feeding of this animal are listed (drawn from database). Associations of feedstuffs to speciesonly edited by "backend" access. Each species has a different digestibility percentage for a raw material, its protein, lipid and carbohydrate. This would require a part of the database where species-specific digestibilites are recorded when the data is available ([login to view URL]). Supplier page • This is a search tool where user enters a search for the feedstuff for which he needs a supplier. • In the web application is a link for suppliers to register, going to a supplier registration page where they enter their details and what they supply. • The user can print a list of suppliers that supplies a specific raw material. Formulations ability • This allows the selection of ingredients to manually create a formula (recipe). Up to 20 ingredients can be selected to make one formula (recipe). This may be a dropdown list of the feed ingredients or some other user friendly method. Once ingredients are selected, the percentage inclusion of each can be specified by the user. User types in a name for his formula. A button generates a proximate analysis for the formula created; or the formula calculates automatically. This is not linear programming (least cost), but simply input of desired ingredient percentages and output of resultant analysis of mix). This is a simple mathematical formula. Excel sample is available. • The created formula can be printed. • Option to select an animal species and to view practical formulas for that species stored in the database.