denyeretal2

More generic skills: Relational databases for Biochemistry and Nutrition students  Gareth DenyerJill Johnston Dale HancockSchool of Molecular and Microbial Biosciences: 

More generic skills: Relational databases for Biochemistry and Nutrition students Gareth Denyer Jill Johnston Dale Hancock School of Molecular and Microbial Biosciences

Background: 

Background Several years teaching Excel Integration between Years 2 and 3 Strategy always to teach concepts Let students experiment NO RECIPES!! Students highly skilled Use for all experimental data and graphs Integration of photos Locked reference cells Add-ins

Limitations of XL: 

Limitations of XL Huge amounts of data Microarray or other Bioinformatic data Unwieldy, 65,000 row maximum Lack of scalability Adding rows/columns can change equations Sometimes a bit too flexible! Equations in individual cells can be changed Not relational Information can be inconsistent Information needlessly repeated (redundant) Bad sorts!

AUSNUT: 

AUSNUT Australian/NZ food data tables Drives all Australian nutritional analysis tools 4,500 food items But out of date – ‘new ethnic’ foods lacking Could XL use the tables to create a nutritional analysis tool??

Student XL build: 

Student XL build Copy relevant rows containing Food Name and Nutrient info for each ingredient For each ingredient, set a quantity and the weight Work out how much energy, etc, each ingredient contributes to the recipe Add ‘em up Knowing total weight and serve number, calculate amount per 100g and amount per serve

Reflections on XL: 

Reflections on XL New recipe? New ingredients? Deleting Ingredients? Change quantities? Using this recipe in another recipe? eg, pancakes in a savoury crepe recipe Formatting Production of nutritional labels Searching, Sorting….

Database: 

Database Used FileMaker Pro Very easy to use Layouts like “Powerpoint” 30-day free trial version available Enables students to work at home But Access could be used Comes with standard MS Office install Not very user-friendly

How to Teach DB skills: 

How to Teach DB skills Keep it in CONTEXT! Especially for this cohort! Go for the “Wow!” effect early Layouts, buttons, relationships Desire to implement ‘features’ drove experimentation and learning! Example

Things to Do: 

Things to Do Experiment with searches Using FIND Using multiple criteria Eg, Foods with lots of fat, little sodium, etc Make a couple of layouts A Form layout (one food per page) A Table layout (like a spreadsheet) A List layout (like, er, a list) Navigate between layouts Make it quick to do a find First taste of scripts

Relationships: 

Relationships Once we know the Food ID we know The name of the food The amount of each nutrient in 100g of the food So a recipe could simply list … The FoodIDs The Quantities of each food Make a Recipes table RECIPE_ID, RECIPE_NAME Make a RecipeItems table RECIPE_ID, FOOD_ID, QUANTITY

Ultimately: 

Ultimately Easy selection of foods for recipes Easy navigation between files and layouts Buttons and scripts for anything you do more than twice! Using Recipes as new food items Beautiful reports One recipe to the page Food labels Sell it!!!!!!! Extrapolate to diet history What’s needed?

Slide12: 

Millorraine Courelle Clients, interaction with XL Conditional drop-downs Gang of Five Help screen, lots of buttons! Three Ss Food labels

Project Managment: 

Project Managment The tool What features do you want Prioritise The instructions (both for user and designer) Screenshots “Idiot proof” or conceptual? FAQ Troubleshooting Tips and tricks What YOU find tricky someone else will too!

Extrapolations I: 

Extrapolations I Go “head to head” with Foodworks! REFLECTION!! How much faster is Foodworks? What makes it better? What features would you need to make your tool comparable? Plain English names for foods Description field NB. Description fields in Foods.txt Other things in Recipe file Method? Ingredients List? Produce a nicely formatted Information Label

Extrapolations II: 

Extrapolations II Adding new foods not in AUSNUT Also adding recipes so they become “Foods” Customise the Foods list To better reflect an average pantry Or YOUR pantry Measures Translating cups/spoons to grams NB. Measures file in AUSNUT 1999 Start again…! Writing instructions as you build Error Trapping Where can you or a user get stuck How to prevent this, how to get out of a trap Diet histories, food recall, etc Patient managment

Where now?: 

Where now? Do it all over again? Most useful for the students Build on what was done last year? Better ‘product’ at the end