An Introduction to Databases

1
An Introduction to Databases

• Dr Stephen Swift
• The Intelligent Data Analysis Group
• Brunel University

2
An Introduction to Databases

• Databases
• The Parts of a Database
• A Brief Description of SQL
• Examples Using Microsoft Access

3
What is a Database? (1)

• A Database System is a Computerised Record
  Keeping System
• Rather Like an Electronic Filing Cabinet
• The Data can be Added to, Deleted, Modified etc
• The Data Contained is of the Same Type
• Would Not Have a Database Containing Patient
  Records and the Sales Records of a Pet Shop, For
  Example

4
What is a Database? (2)

• In Large Organisations, a Database System is
  Usually a Subsystem of a Larger Information
  System
• An Information System Supports the Information
  Handling Requirements of an Organisation
• Smaller Organisations Might Just Have a Single
  Database
• A Database Management System (DBMS) is a
  Software System that Enables Users to Define,
  Create, Maintain and Control Access to a Database

5
Why Are Databases Needed?

• A Huge Amount of Data is Being Collected Every
  Second of the Day
• The Data
• Is Often Complex
• Large in Size
• Requires Sophisticated Manipulation
• Databases and DBMS are Essential to Successfully
  Manage Such Data

6
An MS Access Database
7
Microsoft Access

• A Stand Alone Database System
• All Aspects of the Database are Contained in a
  Single MDB File
• Slow When Handling Huge Volumes of Data
• Can be Used to Create Database Applications

8
Tables
9
Table Properties (1)

• Rows (or Records)
• Shows Occurrences of Patient
• Each Row Must be Uniquely Identifiable
• The Order of the Rows MUST NOT Be Significant

10
Table Properties (2)

• Columns (or Fields)
• Each Column has a Type, e.g. Number, Text,
  Boolean, Multimedia, etc
• The Order of the Columns MUST NOT be Significant
• Only One Value Should be Associated With Each
  Column/Row Intersection in the Table

11
Table Properties (3)

• Domain
• A Pool of Possible Values From Which the Actual
  Values Appearing in the Columns of the Table are
  Drawn
• e.g. The Domain of Patient Numbers Includes all
  of the Possible Patient Numbers, Not Just the
  Ones Currently in Hospital
• Very Important for Comparing Values from
  Different Tables

12
The Primary Key

• A Special Type of Field
• Not All Tables Have a Primary Key
• Usually a Number or String, e.g. Patient Number
• Used to Relate Data Between Tables

13
Worked Examples

• Check That Microsoft Access Loads
• Check That You Can See Four Files
• Functions.xls
• Gene ID.xls
• spellman_yeast_alpha.xls
• annette2004.ppt

14
Worked Example (1)

• We Will
• Create a Microsoft Access Database
• Import Some Data
• Make Sure the Fields are the Correct Type
• Create Three Tables
• Look at the Tables (Datasheet View)

15
Queries (1)

• A Query Selects or Modifies a Subset of One or
  More Tables
• E.g. All Female Patients Under 18 Years Old
• A Query is Often Expressed in a Special Language
  Called SQL

16
SQL

• Structured Query Language
• Originally a Proprietary Language from IBM
• Now an International Standard High Level Language
  Supported by Most Database Products
• Used to Modify Data Within a Database

17
Data Manipulation

• Data is Manipulated by Rows and Columns
• A Subset of Data is Selected and then Modified
• The Selection is Made by the User, Usually Some
  Set of Requirements
• E.g. Select All Female Patients Under 18 Years
  Old and Delete All Their Records

18
Queries (2)

• A SELECT Query Selects a Subset of One
• or More Tables
• SELECT ltFieldsgt FROM ltTablegt
• WHERE ltConditiongt
• SELECT Alpha. FROM Alpha
• WHERE Alpha.alpha63"NULL"

19
Queries (3)

• A Make Table Query Creates a Subset of
• One or More Tables and Puts the Results
• Into a New Table. The Destination Table
• is Replaced
• SELECT ltFieldsgt INTO ltDestination Tablegt
• FROM ltSource Tablegt WHERE ltConditiongt
• SELECT Alpha. INTO Temp FROM Alpha
• WHERE Alpha.ORF Like "YP"

20
Queries (4)

• An Update Query Changes the Values of
• One or More Fields in One or More Tables
• UPDATE ltTablegt SET ltFields to Valuesgt
• WHERE ltConditiongt
• UPDATE Alpha SET Alpha.alpha63 "0
• WHERE Alpha.alpha63"NULL"

21
Queries (5)

• An Append Query Selects a Subset of One
• Tables and Adds it into Another Table
• INSERT INTO ltDestination Tablegt SELECT
• ltFieldsgt FROM ltSource Tablegt WHERE
• ltConditiongt
• INSERT INTO Temp SELECT Alpha. FROM
• Alpha WHERE Alpha.alpha63"NULL"

22
Queries (6)

• A Delete Query Removes a Subset of One or
• More Tables From the Database
• DELETE ltRowsgt FROM ltTablegt WHERE
• ltConditiongt
• DELETE Alpha.FROM Alpha WHERE
• Alpha.alpha63"NULL"

23
Queries (7)

• A Crosstab Query is Very
• Complex and Will Therefore
• Not be Covered!

24
Worked Example (2)

• We Have Some Import Errors
• We Must Locate What Fields are in Error
• We Must Then Use an UPDATE Query to Modify the
  Erroneous Data

25
Forms

• Forms are Used to View/Add/Manipulate Data

26
Data Entry (1)

• The User Should Only be Able to Enter the Domain
  of a Field on a Form
• E.g. If There are Only 10 Wards in a Hospital,
  They Should Only be Able to Enter 1-10 in the
  Wards Field
• In the Example Above, Allowing Any Number Would
  Increase the Chance of Data Errors

27
Data Entry (2)

• Pick Lists and Check Boxes Can Help to Maintain
  Data Integrity
• Validation Rules on Form Fields Can Prevent the
  User From Entering Invalid Data
• Minimise Free Text Entry to Fields
• The Application Should Help the User in
  Completing Forms Correctly

28
Reports

• Reports are Used to Display Data

29
Macros and Modules

• Macros are a User-Defined List of Database
  Actions to be Carried Out
• Usually Commonly Performed Tasks
• A Module Contains Functions and Subroutines that
  Carry Out More Complex Tasks
• Modules are Constructed Using a Form of Visual
  Basic

30
Joins

• A Join Combines Two Tables into One Virtual Table
• Tables are Joined Together Based on a Common
  Value in a Field
• The Field That the Two Tables are Joined on Must
  be the Same Type

31
Worked Example (3)

• We Are Going to Join Our Tables Together
• Using Tools-Relationships
• Add the Three Tables We Imported
• Join Alpha-ORF and Gene ID-ORF
• Join Gene ID-SGD and Function-SGD

32
Worked Example (4)

• Now Look at the Effect on
• Building a SELECT Query on All of the Tables
• The Datasheet View For One of the Tables
• Without Joins it Would be Very Difficult to
  Relate and/or Compare Data From Different Tables
• Why is This Important?

33
Normalising a Table

• Normalisation is
• The Organisation of a System's Attributes into a
  Set of Compact and Meaningful Tables

34
Normalising a Table

• Well Normalised Tables Avoid
• Unnecessary Duplication of Data
• i.e. No Redundant Data
• Problems With Modifying, Inserting and Deleting
  Data
• N.B. Sometimes Referred to as Update Anomalies

35
Stages of Normalisation (1)

• Normalisation Takes Place in Stages
• Each Stage is Known as a Normal Form
• Each Stage is a Development From the Previous
  Stage

36
Stages of Normalisation (2)
Un-Normalised Form
First Normal Form
Second Normal Form
Third Normal Form
37
Un-Normalised Form

• Column Headings (Field Names) Should be
  Meaningful
• Choice of Primary Key
• Must be Unique for the Particular Data Source
• May Require Two or More Fields
• Use the Smallest Number of Fields Possible
• Avoid Textual Keys (Degrades Speed)

38
1st, 2nd and 3rd Normal Form

• 1st Separate any Repeating Groups of Fields to
  Other/New Tables
• 2nd Separate Fields that Only Depend Upon Part
  of the Key to Other/New Tables
• 3rd Separate any Fields That are Not Directly
  and Fully Dependent on the Key to Other/New Tables

39
Sample Source of Data
40
After Normalisation
41
Tables as a Logical Data Structure
Ward
Patient
Wd No
Ward Name
Pat No
Surname
Forename
Wd No
Barnard
10
Maurice
Moneybags
923
10
Fleming
11
Ivor
Foot
109
11
Prescription
Drug Code
Trt Lgth
Prescr Date
Dosage
Pat No
2 pills 3 x day after meals
20/5/88
CO2355P
923
14
Injection
20/5/88
MO3416T
923
5
every 4 hours
Injection
25/5/88
MO3416T
923
3
every 8 hours
26/5/88
PE8694N
1 pill 3 x day
923
7
2 pills 3 x day
15/5/88
AS473A
109
7
after meals
20/5/88
2 per day
109
VA231M
5
42
Worked Example (4)

• Create a SELECT Query that Just Displays the
  Functional Groups
• Check that it Contains What We are After
• Change the SELECT Query to a MAKE TABLE Query

43
References

• Further Reading and Source for this Presentation
• Database Systems A Practical Approach to
  Design, Implementation and Management, 3rd
  Edition, T. Connolly and C. Begg, Addison
  Wesley, 2001
• An Introduction to Database Systems, 8th
  Edition, C. J. Date, Addison Wesley, 2004