copyright 2009 - p.harris database fundamentals vanderbilt university september 24, 2009

Copyright 2009 - P.Harris

Database Fundamentals

Vanderbilt University

September 24, 2009


In this lecture, you will learn …

Overview of Relational Database conceptsWe will use MS-Access for convenience in this course (many principles scale to other relational databases)

Overview of MS-Access environment (objects)

Table Design Principles- Table Relationships (Cascading)- Primary keys, indexes, validation principles


What is a Relational Database?A relational database can be thought of as a software “engine” that provides an interface between table-based data (strong spreadsheets) and user application queries.

Examples of RDB applications include:

•MS Access *** (we’ll use this one for convenience)•Oracle•MySQL•SQL Server (Microsoft)•DB2 (IBM)•Informix


Why choose RDB instead of SPSS / Excel / …

Although there is always overlap, the following rules might help when deciding when / when not to use a RDB:

•RDB is best used for long-term data storage and/or data sharing.

•MS Excel is best used for minor data collection, manipulation, and especially visualization.

•SPSS is best used for minor data collection and especially data analysis.

It is easy to export data from RDB to Excel SPSS


Why choose MS-Access over other DBM systems?

Cheap, readily available (packaged with MS-Office Premium).

Easy to use (relative to some systems – Oracle may require one person to maintain the server as a database administrator and another person to serve as an application developer).

Includes front-end tools for rapid application development (RAD). This also makes MS-Access a good prototype environment.


Why choose other DBM systems over MS-Access?

MS-Access can handle a large number of records, but is somewhat slow compared to high-end platforms.

Multiple users may use the database simultaneously, but MS-Access is known to become unstable with greater than 3-5 users.

There is a “snob factor”. I personally recommend the use of other systems (Oracle, SQL Server, MySQL, etc) when writing grant proposals. If not, at least omit mentioning the MS-Access software by name – (we will use a relational database to …)


What is in an MS-Access file - 1?Although the term “database” typically refers to a collection of related data tables, an Access database includes more than just data. In addition to tables, you can add:

•Saved queries (stored procedures) - organizing and/or manipulating data•Forms – GUI interaction with data, event programming•Reports – customized results for printing (~ static forms)•Macros and VB programs for extending functionality

Microsoft provides some logical integration of these tools through “wizards”. However, these are pretty basic - most developers must pick and choose the best approach when implementing applications.


What is in an MS-Access file - 2?

Unless advanced techniques are employed, all entities are stored in one *.mdb file. When running, a locking file (*.ldb) is also visible. Only the mdb file needs to be copied to transfer the database to another computer or location.


Demonstration Open MS-Access here and lead demonstration

of the overall environment (tables, forms, queries, reports, modules)

Demonstrate overall application from user view, but explain during demonstration which underlying database object(s) involved.

Finish this demonstration by emphasizing that the most important piece is the data going into the tables. Tables are the root of any RDB system.


What is in an MS-Access file - 3?

Demographics Ethnicity Labs H & P

Tables

Queries

Forms (Active)Reports (Static)

VB + Macros – Event Driven Automation, etc.


Advanced – Splitting

Back-End File - Contains all Data Tables

VB + Macros – Event Driven Automation, etc.

Demographics Ethnicity Labs H & PTables

Queries

Forms (Active) Reports (Static)

Front-End File - Contains all Application Entities (Forms, Queries, etc.) and links to data tables in back-end file. Note you may have more than one FE to accommodate different user types (nurses, laboratory, etc).

Database

SoftwareApplication


Front-EndExample


Microsoft Access – Module 1 Summary

MS-Access is a powerful relational database program. Table and query concepts are similar for any RDB package. MS-Access also has an integrated application development environment for creation of end-user software that can be customized to fit most personal/departmental needs for data collection and storage.

Most of what you learn using Microsoft Access is applicable to any RDBMS.


Creating / Working with Tables


Tables – Glucose Measurement Database

We wish to construct a database to track waking glucose measurements for an indefinite amount of time on 100 patients receiving 3 possible drug combinations.

Why would this be difficult in MS-Excel or SPSS?


Tables Overview Think of Access as a collection of

spreadsheets that are relationally linked.

ST

OR

E D

AT

A O

NE

TIM

E /

ON

E P

LA

CE

DO

NO

T S

TO

RE

CA

LC

UL

AT

ED

DA

TA

DemographicsPatient_IDFnameLnameAddressPhoneGenderRaceDOBHeight

GlucoseGlucose_IDPatient_IDDateWeightMed_IDGlucose

MedsMed_IDDrugCombonation


Demonstration

Stop here and build these three tables Show data validation rules Show relationships Stress primary keys Demonstration cascading update/delete


Table Demonstration - Live

General Setup for TablesDescribe General OptionsShow Validation Rule

Relationships

Lookup Option


Table Relationships - Live

Table Relationships

Describe Cascade Features


MS-Access Power Trick

It is much faster to create an Excel template spreadsheet and import than to create table fields column-by-column in Access.

Take advantage of this when needing a table by:1.Creating spreadsheet with column headers.2.Create one row of representative dummy data (every field needs a value)3.Import spreadsheet to Access4.Delete dummy row and check field types for accuracy


Summary Data storage principles1. Attempt to store data 1 time / 1 place; 2. Do not store data that may be calculated from other fields (utilize

queries); and 3. Strive for very discrete data storage (no ambiguity – garbage in /

garbage out).4. Choose real or arbitrary (autonumber) unique identifier for each

record.

RelationshipsUse table relationships to automatically cascade delete and update

records.

Other Data SourcesImport = Copy; Link = Live Connect.

copyright 2009 - p.harris database fundamentals vanderbilt university september 24, 2009

Documents

ms access

ms access

access database

msaccess file

msaccess software

ms excel

data analysis

database administrator