Transcript Computers: Tools for an Information Age

Module 4
Database Management Systems
1.
2.
3.
4.
What is a database?
Data hierarchy and data organization
Field, record, file, databases, data warehouse
Database models
Relational databases, Hierarchical, Network, Object-oriented
Data integrity
Database management systems and database features
Creating a database, Data dictionary, Data maintenance, Data
retrieval: Queries, Reports, Concurrency control, Security, Backup
and recovery
Using database
Creating the database, Determining the table structure, Setting up
the file structure, Entering the data, Using the database
Copyright © 2003 by Prentice Hall
The Hierarchy of Data
Field – The smallest meaningful unit of data

E.g. LAST NAME, FIRST NAME, STREET, CITY, STATE, ZIPCODE
Record – the set of fields containing data

E.g. AKERS, TED, 4302 LEMON AVE. OAKLAND, CA, 94709
File – a collection of related records
Module 4: database
2
Files and Databases
Traditionally, information systems were
developed with a file processing approach
A database is an organized collection of
information or data.
Database approach creates separate files
for each entity
Module 4: database
3
File System Approach
Each application had its own file

Data was not shared among applications
Resulted in a great deal of data redundancy,
the repetition of the same data values


Increased the risk of inaccurate data
Increased the amount of storage space needed
Module 4: database
4
The Database Approach
A collection of related
tables



In database technology, a
file is called a table
Each entity is stored in a
separate table
Tables are linked by a
relationship between
primary and foreign keys
Module 4: database
5
Primary Key
A field that uniquely identifies a record


SalesID can be a primary key for the
Salesperson table
Once a SalesID appears in the table, no other
salesperson can have that ID
Module 4: database
6
Foreign Key
A field in one table that is a primary key in
another table


SalesID can be used in the Customer table to
identify the salesperson who serves that
customer
The same SalesID can appear in many
customer records (a salesperson can serve
many customers)
Module 4: database
7
Database Models
Define the way a database organizes data
Four main models




Relational
Hierarchical
Network
Object-oriented
Module 4: database
8
Relational Model
Data organized in table
format


Columns represent fields
Rows represent records
Tables related by
primary/foreign key
relationship
Most current database
development uses this
model
Module 4: database
9
Hierarchical Model
Arranges data in hierarchical “parent-child”
relationship, i.e. in a tree structure:


Each parent record can have many child
records
Each child record has only one parent record
Complex and inflexible
Module 4: database
10
Network Model
Arranges data in complex network of
“parent-child” relationships


Each parent record can have many child
records
Each child record can have many parent
records
Complex and inflexible
Module 4: database
11
Object-Oriented Model
Designed to deal with complex data types
Focuses on the object


An object represents an entity
Represents data about that entity and the
types of operations that change that entity
Module 4: database
12
Data Integrity
Refers to the degree to which data is
accurate and reliable
Integrity constraints – rules all data must
follow

If integrity constraints are not followed, the
data is unreliable
Module 4: database
13
Integrity Constraints
Define acceptable values for a field

For example, the value of a month cannot be
greater than 12
Primary keys cannot be duplicated
Foreign keys cannot be used unless they
exist as a primary key

A SalesID that is used in the customer table
must exist as a primary key in the
salesperson table
Module 4: database
14
Database Management
Database Management System (DBMS) – A
software allows you to create a database and
enter, modify, and retrieve data as needed
Available at all levels


Sophisticated systems for a mainframe environment.
E.g. Oracle, IBM DB2
Inexpensive, easy-to-use personal computer systems.
MySql, MS Access, MS SQL Server Database
Access through Web
Module 4: database
15
Database Features
Data dictionary
Data maintenance
Data retrieval
Concurrency control
Security
Backup and recovery
Module 4: database
16
Data Dictionary
Stores data about the tables and fields
within the database


For each table, it stores the table name and
relationships to other tables
For each field, it records information about
that field
Also called a catalog
Module 4: database
17
Data Maintenance
Three basic operations



Adding new data
Modifying existing data
Deleting data
Two methods for performing operations


Interacting directly with the DBMS
working on table
Using programs to access the data using special
commands
e.g., working on database forms, web forms
Module 4: database
18
Data Retrieval
Involves extracting the desired data
Two primary forms of data retrieval


Queries
Reports
Module 4: database
19
Queries
You present a set of criteria; the DBMS
selects matching data from the database
Use a query language



Structured Query Language (SQL) is
supported by most relational databases
Query-by-example (QBE) uses a graphical
interface to generate the SQL
Display results in a table-like grid
Module 4: database
20
SQL
Uses English-like statements




SELECT specifies the field(s) to be selected
FROM specifies the table(s) containing the fields
JOIN specifies the relationship between tables
WHERE specifies the criteria that must be met
Module 4: database
21
Query by Example
User adds tables to query


User drags fields down into
grid at bottom of window
Criteria can be added by
typing them in the Criteria
row of the grid
Use can execute the
query by clicking an icon
on a toolbar
Module 4: database
22
Query Results
Retrieved records are displayed as rows
Fields specified in the select clause are
displayed as columns
Module 4: database
23
Reports
Provide a formatted
presentation of data from the
database


Allow you to group reports and
total by group, if desired
Normally contain more data
than queries
Reports are designed with a
report generator
Module 4: database
24
Concurrency Control
Manages simultaneous database users

If several users tried to update the same
record at the same time, updates might not be
processed correctly
Employs a record-locking scheme

Once a user accesses a record, it is locked
until the first update is complete
Module 4: database
25
Security
Many security features are built into most DBMS
software


Users can be required to enter a user ID and
password
Each user ID may have different access to the data
Read-only – permits the user to look at the data but not
change it
Update – permits the user to make changes to the data
No privileges – user can not read or update the data
Module 4: database
26
Backup and Recovery
Backup – a copy of the database

Backups should be made periodically
Recovery – replaces a damaged database
with the good backup
Module 4: database
27
Creating and Using a Database
Creating the database
Determining the table structure
Setting up the file structure
Entering the data
Using the database
Module 4: database
28
Creating the Database
Consider your needs


Reports you will need
Inquiries you will want to make
Module 4: database
29
Determining the Table Structure
Sketch the table structure –
what kind of data is needed in
each column
Determine characteristics of
field

Field name
Each field must have a unique field
name


Field type
Field width
The maximum number of
characters, including decimal
places
Module 4: database
30
Field Types
Character fields – contain descriptive data
Numeric fields – contain numbers used for
calculation

Specify the number of decimal places
Date fields
Logical fields – keep track of true/false or
yes/no conditions
Module 4: database
31
Setting Up the File Structure
Design the structure for
each table



Define the table
Define each field in the
table
Define primary key
Set up the table in design
view
Module 4: database
32
Entering the Data
Enter data into the tables in
datasheet view
Enter data into the tables by
using a graphical form
Module 4: database
33
Using the Database
Many operations can be performed to view
and modify the data




List the records
List specific fields
Make a query
Modify the data
Module 4: database
34
List the Records
Displays the table in
datasheet view

Displays all rows and all
columns
Can be displayed on
monitor or printed
Module 4: database
35
List Specific Fields
Displays only the fields you
want to view
Use SQL to select the desired
fields
Module 4: database
36
Query
Use SQL or query-byexample to produce a
query



Use relational operator to
define criteria
Can be displayed on
monitor or printed
Can be formatted into a
report
Module 4: database
37
Modify the Data
Add new records
Update (make changes to) an existing
record
Delete records
Module 4: database
38