1 / 20

Designing Databases for Biological Research

Designing Databases for Biological Research. Brian R. Mitchell Fall 2008. Introduction. Participants Syllabus Website: http://www.uvm.edu/~bmitchel/databases.html Database options at UVM. What is a Database?. Definition Keys Structured Query Language (SQL) Relationships.

ovidio
Télécharger la présentation

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 2008

  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 • Number • Yes/No • Currency • Date/Time • AutoNumber • Text • Memo • Hyperlink • OLE • Attachment

  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 only one of the keys NO YES Employee ID – Key Employee ID Privilege ID – Key Privilege ID Privilege Name & Privilege ID Privilege Name

  18. Database Design • Third Normal Form (3NF) Information in the table must not be related to a non-key field NO YES Orders TableOrders Table Order ID – Key Order ID – Key Order Date Order Date Employee ID Employee IDF Employee Last Name Employees Table Employee ID Employee Last Name

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

More Related