Introduction to Database Systems

1
Introduction to Database Systems
Objectives

• Introduction to Database Concepts
• Introduce Microsoft Access
• Tables, Queries, Forms, Reports etc
• Creating a simple database table
• Indexing

2
Data, Information, Database

• Data A collection of numbers and text
  273448926
• Information Meaning derived from data
• SSN 273-44-8926
• Database A collection of related data stored in
  a specific format to simplify retrieval. The
  phone book and dictionary are examples of
  databases.
• John Smiths SSN 273-44-8926
• DBMS Database Management Systems are programs
  that help in the storage and retrieval of
  information from databases. (Access, Oracle)

3
DBMS Vs. Spreadsheets

• Store and retrieve data
• Provide support for data organizing and
  selecting
• Can do simple calculations
• Efficient data handling
• Customers of VISA with balance gt 1000

• Store and analyze data
• Provide support for complex calculations
• Can do simple data organizing and selecting
• Inefficient data handling
• What was the net profit of VISA

4
Access DBMS
Objects
5
Access DBMS objects

• Tables - a list of data organized into fields
  and records
• Queries - question structures to sort, filter and
  select specific information
• Forms - structures for screen views of data
• Reports - structures for written output of data
• Program Modules - program code to perform
  specific actions

6
A Database Management System
Data Tables
Reports
Data Input Onto Tables Onto Forms From files
Query Results
Queries
Output Files
Program Modules
7
An Inventory Database System
Forms
Order Transactions
New Vendors New Customers
New Products
Shipments
Program Modules
Tables
Program Modules
Inventory Low Message
Reports Queries
Output File
8
Objects Tables

• Data is stored in objects called Tables
• Data on tables are listed in rows called records
  
• A record consists of one or more ordered
  categories called fields
• Field types include Text. Number, Currency, Date
  etc. Certain fields are required fields and
  must be filled
• Field Properties format, validation rule,
  required, size, masks, defaults, ranges
• A table is a listing of multiple records, all
  records in a table have the same fields

9
Primary Key

• A field, or combination of fields, which uniquely
  identifies a record in a database

10
Table Datasheet View Bank Customers
Record
Field
Primary Key
11
A Second Table - Transactions keeps track of
all deposits and withdrawals

• Whats the Primary key on this table?

12
Walkthrough Setting up a Table using Design View
13
Divide Tables into Inseparable Fields

• Address as 1 field
• 17 Main St. New York, New York 10002
• Address as 4 fields
• Street Address - 17 Main St.
• City - New York
• State - New York
• Zip code 10002
• If you wanted to filter the table to get a list
  of only New York City residents how would this
  work with these two different Table structures

14
Defining Properties for each Field in a Table

• For a persons social security number use
• What field type?
• Text, Number - Short Integer, Number- Long
  Integer etc.
• Should it be optional or required?
• Does the value need to be within certain limits
  or from a predefined list?
• Is there a default value?
• Would an input mask be appropriate

15
Memory and Field Size
Why adjust the field size for Social security
number?

• Text
• Up to 255 characters (28 -1)
• Memo
• Up to 65,535 characters (216 - 1)
• Numbers
• Integer - 2 bytes - 16 bits
• Long Integer - 4 bytes - 32 bits ()
• Byte (character) - 1 byte
• Single - 4 bytes - -precision 7
• Double - 8 bytes - -precision 15

16
How should you decide what information goes on
which table?

• If a fact appears in more than one record of a
  table, then this fact should probably be defined
  in another table.
• Example Account number
• Each fact change should change in only one place
• Example Address
• Calculations shouldnt be part of the database
• Example Current Balance
• Select a Primary Key where applicable so you can
  relate your tables
• Example Account number

17
Tools Sorting and Filtering

• Sorting - allows the user to temporarily order
  the records by a specific field
• Ascending or Descending order
• Single or multiple sort fields
• Filtering - allows the user to view only specific
  records that meet the criteria
• Filter by form or filter by selection
• Specify a single criteria or use Boolean and/or
  for multiple criteria in multiple fields

18
Storing and Retrieving Records from Tables

• How are DBMS systems designed to efficiently
  handle data?
• Data is stored on magnetic or optical disk in a
  linear fashion
• To retrieve a specific record one would have to
  search them one at a time until the desired
  record is found.
• To make data retrieval more efficient one can
  index a table based on a specific field.
• Search routines could then be used on that field
  to more efficiently find the record
• Example Alphabetically sorting a dictionary
  and then analyzing the first letter of the
  criteria in the sorted list.

19
Linear Search on Un-indexed Field

• Find the name of student for ss606147775

• Go to the first record to see if 178301771
  matches 606147775. If no match then check the
  next record until a match is found.
• Here it must check 11 items before you find the
  correct one. On average it will have to check
  entries/2

20
A Binary Search on an Indexed Field
Find name of student for ss606147775
This table is sorted by SSN

• First go to the middle record of the table and
  compare values to see if 606147775gt328824082.
• If it is greater, continue checking only from
  this midpoint to the end. Otherwise continue
  checking only from the beginning to the midpoint
• Then go to the midpoint of this subsection. The
  process continues until a match is found

Indexing can aid in querying efficiency using
algorithms such as binary search (1/2,1/2 again)
vs. a linear search (1 at time from top to
bottom). A binary search cuts the processing time
significantly for large databases
21
From Tables to a Relational Database

• We have carefully looked at tables in our
  database how to set them up, define fields etc.
  The real advantages of a DBMS however are in
  their ability to relate information.
• Thus far we have customer names on one list and
  transactions on a separate list. How do we match
  a customers name to specific transaction?
• We need a way to relate these two tables to
  extract useful information. We can relate these
  two tables by matching the account numbers.

22
One to Many Relationship
Acct/Account No. is the Foreign Key
23
A Foreign Key is a field that defines the
relationship between 2 tables

• A valid foreign key must have all of the
  properties listed below
• Must be a primary key (unique) in at least one of
  the tables
• The field names on each table do not have to
  match as long as the information is the same.
  Fields with the same name does not necessarily
  mean they are the foreign key.
• The related fields must be the same data type
  (number, text etc)

24
Walkthrough Establishing relationships
Primary Key on Accounts table acct
Foreign Key acct/Account
25
Once Relationships are established you can gather
information from one or more tables to answer
questions like

• Create a list of account numbers and owner names
  and total transactions
• What are the total deposits made by accounts
  starting with 5?
• What is the total balance of all accounts held by
  Jane Doe ?

These requests are known as Queries
26
Objects Queries

• To extract information from the Database use a
  Query which is a question or request
• The query is not the data that results but a set
  of instructions specifying how specific records
  or combinations of records should be extracted -
  it lets the user
• prepare lists, sort, filter
• choose records to met specific criteria
• do calculations on the data
• Summarize data by a specific grouping
• match up the data to related information

27
Walkthrough A Simple Query
Create a query to list the first name, last name
and transaction amount for each transaction
Design
Dynaset Result
We will spend the next 1-2 weeks learning, in
greater detail, how to use the query tool.
28
Walkthrough Setting up an Autoform
Objects Forms

• A form is not data that results but a set of
  instructions specifying a screen view format of
  the data. These forms are designed to simplify
  data display, inputting and editing.

29
Objects Reports
A report is not the data that results but a set
of instructions specifying the format of written
output. Reports allow users to vary

• The type of text formatting
• Report Page headers, footers, titles
• Which data fields (from tables, queries and/or
  calculations) to be used
• Sorts, Filters, Groupings of fields

Walkthrough Setting up an AutoReport
30
Sample ReportAccounts by Customer
31
Objects Macros Modules

• Programmed instructions
• updating tables/queries from inputs
• customer address
• account balance
• retrieving data from other sources
• performing tasks if a certain criterion is met
• monthly bank statement
• balance gt 20000
• send letter offering estate planning service

This course will not cover the Macros Modules -
we can refer you to more Advanced books on
Access Visual Basic
32
A Review of Database Theory

• Information is stored in objects known as tables
  consisting or records or related information
  categorized into fields.
• A field that uniquely identifies a table is known
  as a primary key field
• Tables can be related to each other using valid
  foreign key fields.
• Queries, Reports, Forms are objects which can be
  created using the data on tables to dynamically
  extract/display information in a specified
  format.