Database Management Essentials Guide
Database Management Essentials Guide
Chapter 8
Managing A Database
Discuss hierarchy of
Define the term Define data integrity
data and data
database, data and and characteristics of
maintenance and
information valuable Information
validation techniques
Discuss hierarchy of
Define the term Define data integrity
data and data
database, data and and characteristics of
maintenance and
information valuable Information
validation techniques
Discuss hierarchy of
Define the term Define data integrity
data and data
database, data and and characteristics of
maintenance and
information valuable Information
validation techniques
Page 514 5
Discovering Computers 2014: Chapter 11_14
Databases, Data, and Information
Page 514 6
Discovering Computers 2014: Chapter 11_14
Databases, Data, and Information
Page 515 7
Discovering Computers 2014: Chapter 11_14
Objectives Overview
Discuss hierarchy of
Define the term Define data integrity
data and data
database, data and and characteristics of
maintenance and
information valuable Information
validation techniques
Data integrity
identifies the quality
of the data
Organized
Accessible Useful
• Arranged to suit the
• Available when • Meaning to the
needs and
requirements of the you need it person receiving
decision maker it.
Cost-effective
• Give more value
than it cost to
Pages 516 produce.
Discovering Computers 2014: Chapter 11_14
10
Objectives Overview
Pages 517 12
Discovering Computers 2014: Chapter 11_14
Hierarchy of Data
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
Page518 13
Discovering Computers 2014: Chapter 11_14
Hierarchy of Data
A primary key is a field in
A record is a group of
that uniquely identifies
related fields
each record
Page518 14
Discovering Computers 2014: Chapter 11_14
Hierarchy of Data
Page518 15
Discovering Computers 2014: Chapter 11_14
Hierarchy of Data
Object Attachment
Page 518 16
Discovering Computers 2014: Chapter 11_14
Data Maintenance
File maintenance refers to the procedures that
keep data current
Adding Modifying
records records
Deleting
records
Page 520 17
Discovering Computers 2014: Chapter 11_14
Data Maintenance
Users add new records to a file when they
obtain new data
Pages 520 18
Discovering Computers 2014: Chapter 11_14
Data Maintenance
When a record no longer is needed, a user deletes
it from a file
Page 472 19
Discovering Computers 2014: Chapter 11_14
Figure 11-7
Data Validation Techniques
Page 522 20
Discovering Computers 2014: Chapter 11_14
Data Validation Techniques
Alphabetic/Numeric
Range check Consistency check
check
Page 522 21
Discovering Computers 2014: Chapter 11_14
Data Validation Techniques
Page 522 22
Discovering Computers 2014: Chapter 11_14
Data Validation Techniques
Automatic stock control systems rely on the use of
barcodes.
Page 522 23
Discovering Computers 2014: Chapter 11_14
Global Trade Item Numbers (GTIN)
25
Discovering Computers 2014: Chapter 11_14
Global Trade Item Numbers (GTIN)
26
Discovering Computers 2014: Chapter 11_14
Calculate Your Check Digit
28
Discovering Computers 2014: Chapter 11_14
Data Validation Techniques
Page 522 29
Discovering Computers 2014: Chapter 11_14
Check Digit Validation Technique
5 0 3 1 0 7 odd digit positions
1 3 5 7 9 11
5 + 0 + 3 +1 + 0 + 7 = 16 (16 × 3 = 48)
51043112017
2 4 6 8 10 Add together the digits in the even positions: 1 + 4 +
1+2+1=9
Page 522 30
Thus, the check digit is 3. (60-57=3)
Discovering Computers 2014: Chapter 11_14
Objectives Overview
Better accuracy - Relying on a barcode to process data is far more accurate than relying on
manually-entered data, which is prone to errors.
Data is immediately available - Because of the processing speed, information about inventory
levels or sales is available in real time.
Reduced training requirements - Thanks to the simplicity of the barcode scanner, employees
need little in the way of training in how to use it. Additionally, thanks to barcodes, there is
much less for employees to have to learn and retain.
Improved inventory control - Being able to scan and track inventory yields a much more
accurate count, as well as a better calculation of inventory turn. Companies can hold less
inventory when they know how soon they will need it.
Low cost implementation - Generating barcodes is quick and easy, as is installing a barcode
system. Potential savings can be realized almost immediately.
33
File Processing Versus Databases
File processing system Database approach
• Traditional approach to • Programs and users share data
data management • Reduced data redundancy
• Each department has its • Improved data integrity
own set of files • Shared data
• Have data redundancy • Easier access
• Isolate data • Reduced development time
• Easy to expand the database
• Security of data is easier to
monitor and maintain.
Page 524 34
Discovering Computers 2014: Chapter 11_14
File Processing Versus Databases
Page 525 35
Discovering Computers 2014: Chapter 11_14
Objectives Overview
File Retrieval
Data
and
Dictionary
Maintenance
Data Backup
Data Security and Recovery
Page 522 37
Discovering Computers 2014: Chapter 11_14
Data Dictionary Function
A data dictionary contains data about each file
in the database and each field in those files
• It contains data about programs and users
• It keeps track of who accessed data and when they
accessed
Page 527 38
Discovering Computers 2014: Chapter 11_14
File Retrieval and Maintenance Function
A DBMS provides several tools that allow users and
programs to retrieve and maintain data in the database
Page 528 40
Discovering Computers 2014: Chapter 11_14
File Retrieval and Maintenance Function
Page 529 41
Discovering Computers 2014: Chapter 11_14
File Retrieval and Maintenance Function
Most DBMSs include query by example (QBE), a
feature that has a graphical user interface to assist
users with retrieving data
Page 528 42
Discovering Computers 2014: Chapter 11_14
File Retrieval and Maintenance Function
A form is a window on the screen that provides areas for entering
or modifying data in a database
Page530 43
Discovering Computers 2014: Chapter 11_14
Data Security
Continuous
backup
(only changes)
Page 531 45
Discovering Computers 2014: Chapter 11_14
Objectives Overview
Page 533 47
Discovering Computers 2014: Chapter 11_14
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 533 48
Discovering Computers 2014: Chapter 11_14
Relational, Object-Oriented, and
Multidimensional Databases
An object-oriented database (OODB) stores data in objects
CAD database
(graphic designs)
Pages 534 49
Discovering Computers 2014: Chapter 11_14
Relational, Object-Oriented, and
Multidimensional Databases
A multidimensional database
can store data in more than two
dimensions of data
Pages 536 50
Discovering Computers 2014: Chapter 11_14
Objectives Overview
Television
Jobs Travel Destinations
programming
Local and
Photos Movies Videos national
weather
Sporting Legislative
events information
Page 536 52
Discovering Computers 2014: Chapter 11_14
Database Administration
Database analysts and administrators are responsible
for managing and coordinating all database activities
Page 538 53
Discovering Computers 2014: Chapter 11_14
Tutorial – Multiple choice questions
1. ______ information has an aged suited to its use
a) Organised
b) Timely
c) Cost-effective
d) Verifiable
2. A ________ is a field that uniquely identifies each record in a file.
a) Composite key
b) Primary key
c) Secondary key
d) Key field
3. _______ procedures include adding records to modifying records in and deleting records from
a file
a) Backup
b) Range check
c) Validation
d) File maintenance
4. A range check ____
a) Ensures that users enter only alphabetic data into a field
b) Determines whether a number is within a specified range
c) Ensures the users enter only numeric data into a field
d) Verifies that a required field contains data
Page 522 54
Discovering Computers 2014: Chapter 11_14
Tutorial – Multiple choice questions
5. All of the following are strengths of the database approach, except ____
a) Less complexity
b) Improved data integrity
c) Easier access
d) Reduced development time
6. A _________contains data about each file in the database and each field in those
files
e) Data mart
f) Data warehouse
g) Data modeler
h) Data dictionary
Page 522 56
Discovering Computers 2014: Chapter 11_14
Tutorial - Suggested Answers
1. Distinguish between data and information.
Data is a collection of unprocessed items
which includes :Text, Numbers, Images, Audio
and Video.
Computer process data into information.
Information is processed data that is Organized,
Meaningful and Useful.
2. What is a Database?
A database is a collection of data organized in a
manner that allows access, retrieval, and use of
that data. Both information and data are
Page 522
stored in the same database.
Discovering Computers 2014: Chapter 11_14
57
Tutorial - Suggested Answers
Page 522 58
Discovering Computers 2014: Chapter 11_14
Tutorial - Questions
1. What is Data Integrity?
2. Why Data Integrity is important to an
organization?
3. What are the qualities of valuable information?
Page 522 59
Discovering Computers 2014: Chapter 11_14
Tutorial- suggested answers
1. What is Data Integrity
Data integrity identifies the quality of the data
The more errors the data contains, the lower its integrity
2. Why data integrity is important?
Data integrity is important because computers and people use information to make
decisions and take actions
For a computer to produce correct information, the data that is entered in a database
must have integrity.
Because data is used to generate information, many organization realize that data is one
of their more valuable assets.
3. What are the qualities of valuable information?
Valuable information should have the following characteristics:
Accurate : Error free
Verifiable : Can be proven as correct or incorrect
Timely : Has an aged suited to its use
Organized : Arranged to suit the needs and requirements of the decision maker
Accessible : Available when you need it
Useful: has meaning to the person receiving it.
Cost-effective: Give more Discovering
Page 522 value than it cost2014:
Computers to produce
Chapter 11_14
60
Tutorial- suggested answers
1. Define Hierarchy of Data
Data is organized in layers or classified in a hierarchy.
Each higher level of data consists of one or more items from the lower.
A database contains files, a file contains records, a record contains fields, and a field is
made up of one or more character.
A character is one byte. A byte consists of 8 bits. Eight bits grouped together in a unit
form a byte. Each byte represents a character which can be a numbers, letters, space,
punctuation marks, or other symbols. A bit is the smallest unit of data the computer can
process.
A combination of characters forms a field.
A field is a combination of one or more related characters
A field name uniquely identifies each field
The field size defines the maximum number of characters a field contains
The field type specifies the type of data a field can contain and how the field is used.
A combination of related fields forms a record.
A record is a group of related fields
A file or data file is a collection of related records stored on a storage medium such as
hard disk or optical disc.
Page 522 A database contains files 61
Discovering Computers 2014: Chapter 11_14
Tutorial - Questions
1. What is File maintenance?
2. What is data validation ?
3. What are five types of validity checks?
4. How is File processing approach different from the
database approach?
5. Identify and describe the common functions of a
Database Management Systems
6. What are the responsibilities of Database Analyst
and Administrators?
Page 522 62
Discovering Computers 2014: Chapter 11_14
Tutorial- suggested answers
1. What is File maintenance?
File maintenance refers to the procedures that keep data current which
include: adding records when new data is obtained, modifying records to
correct inaccurate data or to update old data with new data and deleting
records when they no longer are needed.
2. What is data validation?
Validation a process of comparing data with a set of rules or values to find out
if the data is correct. Many programs perform a validity check that analyse
data either as you enter it or after you enter it to help ensure that it is correct.
Page 522 63
Discovering Computers 2014: Chapter 11_14
Tutorial - Questions
64
Discovering Computers 2014: Chapter 11_14
Tutorial- suggested answers
3. How is File processing approach different from the database approach?
• File processing system
In a file processing system, each department has its own
set of data files. The records in one file may not relate to the records in any other file. The two major
weaknesses are :
(1) redundant data (the same fields are stored in multiple files - duplicated)
(2) Isolate data – it is difficult to access data stored in separate files in different departments
• Database approach
With a database approach, many programs and users share the data in a database.
The database approach :
(1) reduced data redundancy: Most data items are stored in only one file which greatly reduces duplicate
data. Only one copy of each data item needs to be kept.
(2) Improved data integrity: When users modify data in the database, they make changes to one file instead
of multiple files.
(3) Permits easier access and reduces development time.
A database, however can be more complex than a file processing system, requiring special training and
more computer memory , storage and processing power than file processing system.
Data in a database also can be vulnerable than data in the file processing system .
65
Discovering Computers 2014: Chapter 11_14
Tutorial- suggested answers
5. What are the responsibilities of Database Analyst and
Administrators?
• Database analysts and administrators are responsible
for managing and coordinating all database activities
• Database Analyst (DA) : decides on proper placement
of fields, defines data relationship, and identifies
users’ access privileges
• Database Administrator (DBA) : creates and maintains
the data dictionary, manages security of the database,
monitors the performance of the database, and
checks backup and recovery procedures
66
Discovering Computers 2014: Chapter 11_14
Tutorial- suggested answers
4. Identify and describe the common functions of a Database Management Systems
• A database management system (DBMS) performs the following functions :
• Data Dictionary
• A data dictionary contains data about each file in the database and each field in those files
• It contains data about programs and users
• It keeps track of who accessed data and when they accessed.
• File Retrieval and Maintenance
• A DBMS provides several tools that allow users and programs to retrieve and maintain data in the database :
• Query language: A query language consists of simple, English-like statements that allow users to specify the data to display, print or
store.
• 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
• Query by example (QBE): is a feature that has a graphical user interface to assist users with retrieving data.
• Form: A form, called a data entry form is a window on the screen that provides areas for entering or modifying data in a database.
• Report generator or writer: A report generator or writer allows users to design a report on the screen, retrieve data into the report
design and then display or print the report.
• Data Security
• A DBMS provides means to ensure that only authorized users access data
• Access privileges : for data involves establishing who can enter, modify, delete, and view data
• Principle of least privilege policy: User’s access privileges are limited to the lowest level necessary to perform required tasks.
• Data Backup and Recovery
• A DMBS provides a variety of techniques to restore the database to a usable form in case it is damaged or destroyed
• Backup : or copy of the database is made on a regular basis
• Log: maintain a listing of activities that modify the contents of the database.
• Recovery utility: uses the logs and/or backups to restore a database when it becomes damaged or destroyed.
• Continuous backup: is a backup plan in which all data is backed up whenever a change is made.
67
Discovering Computers 2014: Chapter 11_14
Discovering Computers
Technology in a World of Computers, Mobile Devices and the Internet
Chapter 8
Managing A Database
(End)
• odd = 6+1+4+2+1+6=19x2=38
• even = 2+5+2+3+2=14
• sum=52 (nearest is 60)
• check digit is 8
• 6 2 1 5 4 2 2 3 1 2 6 (8)
Note : The above answer should be (6) and Not (8) – spot the mistake
69
Discovering Computers 2014: Chapter 11_14