1 / 21

Designing Databases for Biological Research

Learn about designing databases for biological research, including keys, SQL, relationships, naming conventions, data types, normalization, and database planning. Explore examples and exercises that cover species, location, habitat, visits, surveys, and more.

kiayada-blanchard
Download Presentation

Designing Databases for Biological Research

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Designing Databases for Biological Research Brian R. Mitchell Fall 2006

  2. Introduction • Participants • Syllabus • Website: http://www.uvm.edu/~bmitchel/databases.html • Database options at UVM

  3. What is a Database? • Definition • Keys • Structured Query Language (SQL) • Relationships

  4. Relationships • One-to-one Technicians Contact_Info Amy Amy’s address Joe Joe’s Address Fred Fred’s Address

  5. Relationships • One-to-many Site Count_Data 286 (+ site fields) 286 (species 1) 286 (species 2) 286 (species 3)

  6. Relationships • Many-to-many Students Classes Ralph BIO 165 Susan NR 385 ?

  7. Relationships • Many-to-many Students Classes Ralph BIO 165 Susan NR 385 Schedule Student & Class

  8. Why Use a Database?

  9. Naming Conventions • Keep it short • Make it meaningful • No spaces – use underscores “_” • Avoid @#%$*& special characters • Be consistent

  10. Naming Fields • Be specific • One piece of information per field • No calculated fields • Identify your keys

  11. Naming Objects • Conventional abbreviations for objects: • Tables • tbl = data table • tlu = lookup table • xref = linking table • Queries = qry • Forms • frm = form • frm_sub or fsub = subform • Reports • rpt = report • rpt_sub or rsub = subreport • Pages = pag (not widely used) • Macros = mac (not widely used) • Modules = mod

  12. Avoid Reserved Words avg key property count max section currency memo set date min sum desc name time exists number type group order value index percent year

  13. Data Types • AutoNumber • Currency • Date/Time • Hyperlink • Memo • Number • OLE • Text • Yes/No

  14. Database Design • Single Database or Front End / Back End? • Normalization

  15. Database Design • Non-normalized Spreadsheet-style Site Date Observer Bird 24 7/1/04 BRM BTBW 24 7/1/04 BRM OVEN 24 7/1/04 BRM YBSA

  16. Database Design • First Normal Form (1NF) Each field contains one value NO YES Coordinates UTM_E & UTM_N Birds seen Separate table

  17. Database Design • Second Normal Form (2NF) If you have a composite key, no data relates to one of the keys Example from Northwind database

  18. Database Design • Third Normal Form (3NF) Information in the table must not be related to a non-key field Example from Northwind database

  19. Database Design • Plan your database • Why bother? • Iterative process • Tables • Relationships • Fields • Rules

  20. Exercise Tables Fields Species Species_ID, Common, Sci Location Habitat, Coordinate Visit Time, Weather, Date Survey Minute, species, vis/aud Multiple visits to each location 10 minute count during each visit: listens for each individual of each species that can see or hear, records species, minute during count, visual/auditory

More Related