Lecture 9
UGBS 102
Introduction to Computing
Information and Data Management
Dr. Awuni Emmanuel
Dr. Owusu Acheampong
Dr. Emmanuel Owusu-Oware
Mr. Divine Agozie
Objectives Overview
Define the term,
Define the term, data
database, and explain Discuss the terms
integrity, and describe
how a database character, field, record,
the qualities of valuable
interacts with data and and file
information
information
Describe file Differentiate between
maintenance the file processing
techniques and approach and the
validation techniques database approach
See Page 464 2
for Detailed Objectives Discovering Computers 2014: Chapter 11
Objectives Overview
Describe characteristics of
Discuss the functions
relational, object-
common to most
oriented, and Discuss web databases
database management
multidimensional
systems
databases
Identify database design
guidelines and understand Describe various
the responsibilities of information systems used
database analysts and in an enterprise
administrators
See Page 464 3
for Detailed Objectives Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Database Data Information
• Collection of • Collection of • Processed data
data organized unprocessed • Organized
in a manner items • Meaningful
that allows • Text • Useful
access, • Numbers
retrieval, and
• Images
use of that
data • Audio
• Video
Page 464 4
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Page 465 5
Discovering Computers 2014: Chapter 11
Figure 11-1
Databases, Data, and Information
• Database software, often called a database
management system (DBMS), allows users to:
Create a computerized Add, modify, and
database delete data
Create forms and
Sort and retrieve data
reports from the data
Page 464 6
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
• Data integrity reflects the quality of the data
– The more errors the data contains, the lower its
integrity
– Data integrity is important because computers and
people use information to make decisions and take
actions
Page 466 7
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
• Valuable information should have the following
characteristics:
Accurate Verifiable Timely Organized
Cost-
Accessible Useful
effective
Pages 466 - 467 8
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
• Data is organized in layers
– Files, records, fields, characters
Pages 467 - 468 9
Discovering Computers 2014: Chapter 11
Figure 11-2
Databases, Data, and Information
• A character is one byte
– Numbers, letters, space,
punctuation marks, or
other symbols
• A field is a combination
of one or more related
characters
– Field name
– Field size
– Data type
Page 468 10
Discovering Computers 2014: Chapter 11
Figure 11-3
Databases, Data, and Information
• Common data types include:
Text Number AutoNumber Currency
Date Memo Yes/No Hyperlink
Object Attachment
Page 469 11
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Common data types:
• Text (also called alphanumeric) — letters, numbers, or
special characters
• Numeric (also called number) — numbers only e.g.
integers, real numbers, decimals etc.
• AutoNumber — unique number automatically assigned by
the DBMS to each added record
• Currency — e.g. dollar and cent amounts or numbers
containing decimal values
• Date (also called date/time) — month, day, year, and
sometimes time e.g. dd/mm/yyyy, mm/dd/yyyy
• Memo — lengthy text entries
12
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Common data types:
• Yes/No — also called ‘Boolean’ contains only the values Yes
or No (or True or False)
• Hyperlink — E-mail address or Web address that links to a
Web page on the Internet or document on a local network
• Object — photo, audio, video, or a document created in
other programs and stored as a bitmap in the database
• Attachment — document or image that is attached to the
field, which can be opened in the program that created the
document or image (functioning similarly to e-mail
attachments)
13
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
• A record is a group of related fields
– A primary key is a field that uniquely identifies each record
• A data file is a collection of related records
Page 469 14
Discovering Computers 2014: Chapter 11
Figure 11-4
Databases, Data, and Information
• File maintenance refers to the procedures that
keep data current
Adding Modifying
records records
Deleting
records
Page 470 15
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
• Users add new records to a file when they obtain
new data
Pages 470 - 471 16
Discovering Computers 2014: Chapter 11
Figure 11-5
Databases, Data, and Information
• Users generally modify a record to correct
inaccurate data or update old data
Pages 471 - 472 17
Discovering Computers 2014: Chapter 11
Figure 11-6
Databases, Data, and Information
• When a record no longer is needed, a user deletes
it from a file
Page 472 18
Discovering Computers 2014: Chapter 11
Figure 11-7
Databases, Data, and Information
• Validation compares data with a set of rules or
values to find out if the data is correct
Alphabetic/Numeric
Range check Consistency check
check
Completeness check Check digit Other checks
Pages 473 - 474 19
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Validation
• Alphabetic/Numeric Check An alphabetic check ensures that users enter only
alphabetic data into a field. A numeric check ensures that users enter only
numeric data into a field. For example, data in a First Name field should contain
only characters from the alphabet. Data in a Postal Code field should contain
numbers (with the exception of the special characters such as a hyphen).
• Range Check A range check determines whether a number is within a specified
range. Assume the lowest per credit hour fee at the school is $75.00 and the
highest is $370.75. A range check for the Credit Hour Fee field ensures it is a
value between $75.00 and $370.75.
• Consistency Check A consistency check tests the data in two or more associated
fields to ensure that the relationship is logical and their data is in the correct
format. For example, the value in a Date Admitted field cannot occur earlier in
time than a value in a Birth Date field.
20
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
Validation
• Completeness Check A completeness check verifies that a required field
contains data. For example, some fields cannot be left blank; others require a
minimum number of characters. One completeness check can ensure that data
exists in a Last Name field. Another can ensure that a day, month, and year are
included in a Birth Date field.
• Check Digit A check digit is a number(s) or character(s) that is appended to or
inserted in a primary key value. A check digit often confirms the accuracy of a
primary key value. Bank account, credit card, and other identification numbers
often include one or more check digits.
• Other Checks DBMSs that include hyperlink and attachment data types can
perform validity checks on data entered in those fields. Hyperlink entries can be
tested to ensure that the Web address follows the correct format and that the
link works. Similarly, an attachment entry can be validated by confirming that
the file exists.
21
Discovering Computers 2014: Chapter 11
Databases, Data, and Information
22
Discovering Computers 2014: Chapter 11
File Processing Versus Databases
File processing system Database approach
• Each department has its • Programs and users share
own set of files data
• Have data redundancy • Reduced data redundancy
• Isolate data • Improved data integrity
• Shared data
• Easier access
• Reduced development
time
Pages 476 - 477 23
Discovering Computers 2014: Chapter 11
File Processing Versus Databases
Page 476 24
Discovering Computers 2014: Chapter 11
Figure 11-9
File Processing Versus Databases
25
Discovering Computers 2014: Chapter 11
Exercise
• Determine an appropriate data type, data size, and validation rule (if any) for
each of the fields for the files below:
• Student file
Field Name Data Type Field Size Validation Rule
StudentID
FirstName
LastName
Address
City
State
PostalCode
EmailAddress
DateAdmitted
Major
26
Discovering Computers 2014: Chapter 11
Photo
Exercises
• Faculty file
Field Name Data Type Field Size Validation Rule
FacNo
FirstName
LastName
City
State
Rank
HireDate
Salary
Supervisor
Department
27
Discovering Computers 2014: Chapter 11
Exercise
• Courses file
Field Name Data Type Field Size Validation Rule
CourseNo
Description
CreditHours
• Inventory file
Field Name Data Type Field Size Validation Rule
ItemId
ItemName
Description
QuantityAtStock
UnitPrice
DatePurchase 28
Discovering Computers 2014: Chapter 11
Database Management Systems
• A data dictionary contains data about each file in
the database and each field in those files
• Details such as:
– file name, description, the file’s relationship to other
files, and the number of records in the file are stored
for each file.
– field name, description, field type, field size, default
value, validation rules, and the field’s relationship to
other fields are stored for each field.
Page 478 29
Discovering Computers 2014: Chapter 11
Figure 10-10
Database Management Systems
30
Discovering Computers 2014: Chapter 11
Database Management Systems
• A DBMS provides several tools that allow users
and programs to retrieve and maintain data in the
database Query language
Query by example
Form
Report writer
Page 479 31
Discovering Computers 2014: Chapter 11
Database Management Systems
• A query language consists of simple, English-like statements that
allow users to specify the data to display, print, store, update, or
delete
• Structured Query Language (SQL) is a popular query language that
allows users to manage, update, and retrieve data
• Many DBMSs provide tools to guide nontechnical users through
the steps of creating a query
Page 480 32
Discovering Computers 2014: Chapter 11
Database Management Systems
Page 480 33
Discovering Computers 2014: Chapter 11
Figure 11-12
Database Management Systems
• Most DBMSs include query by example (QBE), a
feature that has a graphical user interface to assist
users with retrieving data
Page 481 34
Discovering Computers 2014: Chapter 11
Figure 11-13
Database Management Systems
• A form is a window on the screen that provides areas for
entering or modifying data in a database
• A report writer allows users to design a report on the
screen, retrieve data into the report design, and then
display or print the report
Page 481 35
Discovering Computers 2014: Chapter 11
Figure 11-14
Database Management Systems
A DBMS provides means to
ensure that only authorized
users access data
•Access privileges
•Principle of least privilege policy
Page 482 36
Discovering Computers 2014: Chapter 11
Database Management Systems
• A DMBS provides a variety of techniques to
restore the database to a usable form in case it is
damaged or destroyed
Backup Log
Recovery Continuous
utility backup
Page 482 37
Discovering Computers 2014: Chapter 11
Relational, Object-Oriented, and
Multidimensional Databases
• A data model consists of rules and standards that
define how the database organizes data
• A data model defines how users view the
organization of the data
• Three popular data models in use today are
relational, object-oriented, and multidimensional
– A database typically is based on one data model
– Some databases, however, combine features of the
relational and object-oriented data models
Page 483 38
Discovering Computers 2014: Chapter 11
Table 11-2
Relational, Object-Oriented, and
Multidimensional Databases
39
Discovering Computers 2014: Chapter 11
Relational, Object-Oriented, and
Multidimensional Databases
• A relational database
stores data in tables
that consist of rows and
columns
– Each row has a primary
key
– Each column has a
unique name
• A relationship is a link
within the data
Pages 483 - 484 40
Discovering Computers 2014: Chapter 11
Figure 11-15
Relational, Object-Oriented, and
Multidimensional Databases
• An object-oriented database (OODB) stores data
in objects
• An object is an item that contains data, as well as
the actions that read or process the data
• Examples of applications appropriate for an
object-oriented database include:
Media Groupware CAD
database database database
Pages 484 - 485 41
Discovering Computers 2014: Chapter 11
Relational, Object-Oriented, and
Multidimensional Databases
• A multidimensional
database can store data in
more than two dimensions
of data
– Sometimes known as a hypercube
– Can consolidate data much faster
than a relational database
– Nearly every multidimensional
database has a dimension of time
• A data warehouse is a huge
database that stores and
manages the data required
to analyze historical and
current transactions.
– Data warehouses usually use
multidimensional databases
Pages 485 – 486 42
Discovering Computers 2014: Chapter 11
Figure 11-17
Web Databases
• The web offers information about:
Television
Jobs Travel Destinations
programming
Local and
Photos Movies Videos national
weather
Sporting Legislative
events information
Page 486 43
Discovering Computers 2014: Chapter 11
Web Databases
• You can also:
– shop for any product or service
– buy or sell stocks
– search for a job
– make airline reservations
– register for college classes, and check semester grades
• Much of this and other information exists in databases that
are stored on the Web or are accessible through the Web
• Some Web databases are collaborative databases, where
users store and share photos, videos, recordings, and other
personal media with other registered users
44
Discovering Computers 2014: Chapter 11
Database Administration
Database analysts and administrators are responsible
for managing and coordinating all database activities
Database Administrator (DBA)
Database Analyst (DA) Creates and maintains the data
Decides on proper placement of dictionary, manages security of
fields, defines data relationship, the database, monitors the
and identifies users’ access performance of the database,
privileges and checks backup and recovery
procedures
Page 489 45
Discovering Computers 2014: Chapter 11
Information Systems in the Enterprise
• An information system is a set of hardware,
software, data, people, and procedures that work
together to produce information
Page 491 46
Discovering Computers 2014: Chapter 11
Table 11-3
Information Systems in the Enterprise
• Enterprise Resource Planning (ERP) integrates
MRP (Manufacturing resource planning) II with
the information flow across an organization to
manage and coordinate the ongoing activities of
the enterprise, including product planning,
manufacturing and distribution, accounting and
finance, sales, human resources, and customer
support
Page 491 47
Discovering Computers 2014: Chapter 11
Information Systems in the Enterprise
Page 492 48
Discovering Computers 2014: Chapter 11
Figure 11-18
Information Systems in the Enterprise
• A document management system (DMS) allows
for storage and management of a company’s
documents, such as word processing documents,
presentations, and spreadsheets
• A content management system (CMS) enables
and manages the publishing, modification,
organization, and access of various forms of
documents and other files, including media and
webpages, on a network or the web
Pages 492 - 493 49
Discovering Computers 2014: Chapter 11
Information Systems in the Enterprise
• A transaction processing system (TPS) is an
information system that captures and processes
data from day-to-day business activities
Pages 493 – 494 50
Discovering Computers 2014: Chapter 11
Figure 11-19
Information Systems in the Enterprise
• A management information system (MIS) is an information system
that generates accurate, timely, and organized information, so that
managers and other users can make decisions, solve problems,
supervise activities, and track progress
Pages 494 – 495 51
Discovering Computers 2014: Chapter 11
Figure 11-20
Information Systems in the Enterprise
• A decision support system (DSS) helps users
analyze information and make decisions
Page 495 52
Discovering Computers 2014: Chapter 11
Figure 11-21
Information Systems in the Enterprise
• An expert system is an information system that
captures and stores the knowledge of human
experts and then imitates human reasoning and
decision making
Page 496 53
Discovering Computers 2014: Chapter 11
Figure 11-22
Summary
How data and
Methods for Assessing the
information are
maintaining high- quality of valuable
valuable assets to
quality data information
an organization
Advantages of Roles of the
Various types of
organizing data in a database analysts
databases
database and administrators
Information
Database design
systems used in an
guidelines
enterprise
Page 497 54
Discovering Computers 2014: Chapter 11