Transcript Database Design

Designing Databases for
Biological Research
Brian R. Mitchell
Fall 2006
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
Relationships
• One-to-one
Technicians
Amy
Joe
Fred
Contact_Info
Amy’s address
Joe’s Address
Fred’s Address
Relationships
• One-to-many
Site
286 (+ site fields)
Count_Data
286 (species 1)
286 (species 2)
286 (species 3)
Relationships
• Many-to-many
Students
Ralph
Susan
?
Classes
BIO 165
NR 385
Relationships
• Many-to-many
Students
Ralph
Susan
Classes
BIO 165
NR 385
Schedule
Student & Class
Why Use a Database?
Naming Conventions
•
•
•
•
•
Keep it short
Make it meaningful
No spaces – use underscores “_”
Avoid @#%$*& special characters
Be consistent
Naming Fields
•
•
•
•
Be specific
One piece of information per field
No calculated fields
Identify your keys
Naming Objects
•
Conventional abbreviations for objects:
•
Tables
–
–
–
•
•
Queries = qry
Forms
–
–
•
frm = form
frm_sub or fsub = subform
Reports
–
–
•
•
•
tbl = data table
tlu = lookup table
xref = linking table
rpt = report
rpt_sub or rsub = subreport
Pages = pag (not widely used)
Macros = mac (not widely used)
Modules = mod
Avoid Reserved Words
avg
count
currency
date
desc
exists
group
index
key
max
memo
min
name
number
order
percent
property
section
set
sum
time
type
value
year
Data Types
•
•
•
•
•
•
•
•
•
AutoNumber
Currency
Date/Time
Hyperlink
Memo
Number
OLE
Text
Yes/No
Database Design
• Single Database or Front End / Back End?
• Normalization
Database Design
• Non-normalized
Spreadsheet-style
Site
24
24
24
Date
7/1/04
7/1/04
7/1/04
Observer
BRM
BRM
BRM
Bird
BTBW
OVEN
YBSA
Database Design
• First Normal Form (1NF)
Each field contains one value
NO
Coordinates
Birds seen
YES
UTM_E & UTM_N
Separate table
Database Design
• Second Normal Form (2NF)
If you have a composite key, no data relates
to one of the keys
Example from Northwind database
Database Design
• Third Normal Form (3NF)
Information in the table must not be related
to a non-key field
Example from Northwind database
Database Design
• Plan your database
– Why bother?
– Iterative process
– Tables
– Relationships
– Fields
– Rules
Exercise
Tables
Species
Location
Visit
Survey
Fields
Species_ID, Common, Sci
Habitat, Coordinate
Time, Weather, Date
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