Transcript Access Powerpoint

Database Concepts
1
Definition of a Database



An organized
Collection
Of related records
2
Database Management
System


Software that helps you organize data
in a way that allows fast and easy
access to data
Data in a database is stored differently
than in other applications:


In those forms, data is entered and used in
the same form as it resides on disk
In a database, data can reside on the disk
in ways unknown to a user
3
Advantages of Databases

Reduced Redundancy: data which might be stored more than
once in separate files, are stored in only one place or file in a
database.


Integrated Data: rather than being in separate and independent
files, data is integrated because any item of data can be used to
satisfy an inquiry or report.
Data Integrity: that the files are accurate and up-to-date

Edit checks on various fields to insure that the data keyed in at
least conforms to standards we have established.

Because one item of data should not be stored in two different files
of a database, it prevents data being stored correctly in one file
and incorrectly in another file.
4
Database Models


The way a database organizes data
depends on the type, or model, of the
database
Three types:



Hierarchical
Network
Relational – organizes data in a table form
consisting of related rows and columns
5
Fields, Records, and Keys - 1




Each “box” in a table contains a data item
Each column is a field, which is a type of data
– every data item in a field has similar
characteristics and meanings.
Full set of data in any given row is a record.
A collection of related records makes a file,
or, in database nomenclature, a table
6
Fields, Records, and Keys - 2

The file or table is the underlying
basis of all relational databases.

Each table in a database must have a
unique identifier, or key
7
Database Power




The relationships between each file in the
database
How these relationships allow you to connect
one file to another
Connected by a field in each table, called a
common field. They may not have the
same name, but will have the same
characteristics and meaning
Common field is usually the key to one table,
and an embedded field in another table
8
Three Types of Relationships not
Discussed in the Text

One to many:



One to one:


Most common
Example: salesperson to customer – one
salesperson may have more than one customer
Example: salesperson to company car
Many to many:

Example: inventory items to customer orders. We
won’t create this kind of relationship in class
9
Parent-Child & Enforcing
Referential Integrity


In a one to many relationship, the one table
is defined as the “parent” and the many table
is defined as the “child”
Referential Integrity



We must define which table is the parent, and
which is the child
We must define the common field
While the common fields in each table do not
have to have the same name, they must have the
same characteristics
10
What Enforcing Referential
Integrity Means



We cannot add a new child record if its
common field references a non-existent
parent
We cannot delete a parent which has existing
children
If a salesman is the parent, and the
customers are his/her children, then we
cannot delete the parent until the children are
reassigned to a new parent
11
Example of Related Tables
SP ID
Last Name
First Name
Office
Hire Date
Salary
12
Davis
James
Savannah
12/01/95
$45,000
15
Jones
Susan
Savannah
10/14/94
$51,500
16
Wilson
William
Augusta
07/08/94
$53,400
CUST ID
Customer Name
City
State
Credit Limit
Salesperson
1004
XYZ Co.
Vidalia
GA
$55,000
12
1007
PDQ Distributing
Garden City
GA
$150,000
12
1009
ABC, Inc.
Savannah
GA
$200,000
15
1011
SC Wholesale
N. Augusta
SC
$85,000
16
12
The Database Management
Systems (DBMS) Process
Review the data and
edit until accurate
Describe the data
Enter the data
13
Queries - 1




Another way of viewing data in a table
May be used to view data in one table, or any
multiple number of tables joined by their
relationships
Can query a table from any field in the table
(integrated data)
Can sort the data either ascending (A to Z) or
descending (Z to A) from any field in the
table
14
Queries - 2

The power of a query are the two following points:


A query allows us to view only the fields we wish to
see. If a table contains as many as 50 fields, we can view
only those we want to.
A query allows us to view only the records (rows) we
wish to see, by setting the criteria for viewing the data


We can, for example, view only the customers who are from GA, or
also just from GA with a specified sales amount
Can also join two tables in a query, and further specify the
data we wish to see via the criteria selection.
15
Queries - 3

Created either by

SQL - Structured Query Language



Entered directly by user
Included in programs
Or by

QBE – Query by Example


Graphical interface to specify criteria
This is what we will do in MS Access
16
Report



Formatted presentation of data from the
database
Normally printed
Designed either by using a report generator
(a wizard in Access, which is what we will do)
or from “scratch” in Access “Design View”
17
Concurrency Control



Databases are used concurrently by
many users
Problem if several users attempt to
update the same record at the same
time
Record locking


First user requests record
Others are locked out for update
18