CHAPTER 6 - RPS 4

THE DATABASE AND DATABASE MANAGEMENT SYSTEMS

 LEARNING OBJECTIVES
2

• Understand the hierarchy of data.

• Understand database structures and how they work.
• Know how to relate tables together in a database.
• Recognize the difference between a database and a database
management system.
• Understand the database concept.
• Know two basic methods for determining data needs.
• Understand entity-relationship diagrams and class diagrams.
 THE DATA HIERARCHY
3

• Data field is the smallest unit of data.

• Record is a collection of related data fields.
• File is a collection of related records.
• Database is a collection of related files.
• General definition
• Restrictive definition
 DATABASE
4

• Table of rows and columns can be represented in a

spreadsheet.
• Relational database structure is conceptually similar to a
collection of related tables.
• Flat file is a table that does not have repeating columns; 1st
normal form.
• Normalization is a formal process for eliminating redundant
data fields while preserving the ability of the database to
add, delete, and modify records without causing errors.
 FIGURE 6.1 SPREADSHEET EXAMPLE OF THE
5

COURSE TABLE
 DATABASE (CONT’D)
6

• Key in a table is a field (or combination of fields) that contain

a value that uniquely identifies each record in the table.
• Candidate key is a field that uniquely identifies each table
row but is not the chosen key.
• Relating tables is done through sharing a common field and
the value of the field determines which rows in the tables are
logically joined.
 DATABASE STRUCTURES
7

• Database management system (DBMS) is a software application

that stores the structure of the database, the data itself, relationships
among data in the database, and forms and reports pertaining to the
database.
• Self-describing set of related data.
 HIERARCHICAL DATABASE STRUCTURES
8

• Hierarchical is formed by data groups, subgroups, and further

subgroups; like branches on a tree.
• Worked well with TPSs
• Utilized computer resources efficiently

• Network allows retrieval of specific records; allows a given record to

point to any other record in the database.
 FIGURE 6.2 THE HIERARCHICAL STRUCTURE
9

BETWEEN THE DEPARTMENT AND COURSE TABLES

 DATABASE STRUCTURES (CONT’D)
10

• Relational is when the relationship between tables are implicit.

• Physical relationship is when the database structure (hierarchical,
network) rely on storage addresses.
• Implicit relationship is when the database structure (relational) can
be implied from the data.
 A RELATIONAL DATABASE EXAMPLE
11

• A database named Schedule has been created from tables used

earlier in the chapter and some others
• The database is implemented in Microsoft Access 2002 (also known as
Access XP).
• Databases break information into multiple tables because if information
were stored in a single table, many data field values would be
duplicated.
 THE SCHEDULE DATABASE
12

• The example is implemented on Microsoft Access DBMS but would

be similar on any relational DBMS product.
• The COURSE table in Access (Figure 6.4) is a list of data field values.
The table itself had to be defined in Access before values were
entered into the data fields.
• Figure 6.5 shows the definition of the Code field.
• Figure 6.6 illustrates that Abbreviation field values will be looked up
from a list of values in the DEPARTMENT table.
• Table 6.7 shows a single table of course and department fields
before they were separated into different tables.
 FIGURE 6.4 THE COURSE TABLE IN ACCESS
13
 FIGURE 6.5 DEFINING THE CODE FIELD
14
 FIGURE 6.6 LOOK-UP VALUES
15
 TABLE 6.7 UNSEPERATED TABLE OF COURSE
16

AND DEPARTMENT DATA FIELDS

 FIGURE 6.7 ACCESS VIEW OF TABLES, FIELDS,
17

AND THEIR RELATIONSHIPS

 THE DATABASE CONCEPT
18

• Database concept is the logical integration of records across

multiple physical locations.
• Data independence is the ability to make changes in the data
structure without making changes to the application programs that
access the data.
• Data dictionary includes the definition of the data stored within
the database and controlled by the database management system.
Content
 What is Database Management
System?
 Why Use a DBMS?
 Purpose of DBMS
 Data models
 Architecture of DBMS
 levels or layers of DBMS architecture
 Components of DBMS
 Advantage of DBMS
 Disadvantage of DBMS
 DBMS Languages
What is Database Management
System?
A Database Management System (DBMS), or simply a Database
System (DBS) consist of :
 A collection of interrelated and persistent data (usually

referred to as the database (DB)).

 A set of application programs used to access, update and

manage that data (which form the data management system

(MS)).
Why Use a DBMS?

 Data independence and efficient access.

 Reduced application development time.
 Data integrity and security.
 Uniform data administration.
 Concurrent access, recovery from crashes.
 Purpose of DBMS
1. Data redundancy and inconsistency
 Same information may be duplicated in several places.

 All copies may not be updated properly.

2. Difficulty in new program to carry out each new task

3. Data isolation —
 Data in different formats.

 Difficult to write new application programs.

 files and formats

Purpose of DBMS …
Security problems
Every user of the system should be able to access only the
data they are permitted to see.
 E.g. payroll people only handle employee records, and

cannot see customer accounts; tellers only access account

data and cannot see payroll data.
 Difficult to enforce this with application programs.

Integrity problems
 Data may be required to satisfy constraints.

 E.g. no account balance below $25.00.

 Again, difficult to enforce or to change constraints with

the file-processing approach.

 Data models

Hierarchical Model
 The hierarchical data model organizes data in a tree structure. There is a hierarch
of parent and child data segments. This structure implies that a record can have
repeating information, generally in the child data segments.
 Hierarchical DBMSs were popular from the late 1960s, with the introduction of
IBM's Information Management System (IMS) DBMS, through the 19

70s.
Network Model

 The popularity of the network data model coincided with the

popularity of the hierarchical data model. Some data were
more naturally modeled with more than one parent per child.
 So, the network model permitted the modeling of many-to-
many relationships in data. In 1971, the Conference on Data
Systems Languages (CODASYL) formally defined the
network model.
 Relational Model


(RDBMS - relational database management system)
A database based on the relational model developed by
E.F. Code.
 A relational database allows the definition of data
structures, storage and retrieval operations and integrity
constraints.
 In such a database the data and relations between them are
organized in tables. A table is a collection of records and
each record in a table contains the same fields.
Properties of Relational Tables

 Values Are Atomic

 Each Row is Unique
 Column Values Are of the Same Kind
 The Sequence of Columns is Insignificant
 The Sequence of Rows is Insignificant
 Each Column Has a Unique Name
Object-Oriented Model

 Object DBMSs add database functionality to object

programming languages. They bring much more than
persistent storage of programming language objects.
 A major benefit of this approach is the unification of the
application and database development into a seamless data
model and language environment.
Semi structured Model

 In semi structured data model, the information that is normally

associated with a schema is contained within the data, which
is sometimes called ``self-describing''.
 In such database there is no clear separation between the
data and the schema, and the degree to which it is structured
depends on the application.
 Architecture of DBMS
 An early proposal for a standard terminology and general
architecture database a system was produced in 1971 by the
DBTG (Data Base Task Group) appointed by the Conference
on data Systems and Languages.

The DBTG recognized the need for a two level approach
with a system view called the schema and user view called
subschema. The American National Standard Institute
terminology and architecture in 1975.ANSI-SPARC
recognized the need for a three level approach with a system
catalog.
There are following three levels or layers of
DBMS architecture:
 1. External Level
 2. Conceptual Level
 3. Internal Level
Architecture of DBMS
 levels or layers of DBMS architecture

 External Level: - External Level is described by a schema

i.e. it consists of definition of logical records and relationship
in the external view.
 Conceptual Level: - Conceptual Level represents the
entire database. Conceptual schema describes the records
and relationship included in the Conceptual view. .
 Internal Level: - Internal level indicates hoe the data will be
stored and described the data structures and access method
to be used by the database.
Components of DBMS

1. Hardware: Can range from a PC to a network

of computers.
2. Software: DBMS, operating system, network software (if
necessary) and also the application programs.
3. Data: Used by the organization and a description of this
data called the schema.
4. People: Includes database designers, DBAs, application
programmers, and end-users.
5. Procedure: Instructions and rules that should be
applied to the design and use of the database and
DBMS.
Advantage of DBMS
 Controlling
Redundancy
 Sharing of Data
 Data Consistency
 Integration of Data
 Integration Constraints
 Data Security
 Report Writers
Advantage of DBMS…
 Control Over Concurrency
 Backup and Recovery Procedures
 Data Independence
Disadvantage of DBMS

 Cost of Hardware and Software

 Cost of Data Conversion
 Cost of Staff Training
 Appointing Technical Staff
 Database Damage
DBMS Languages
Data Definition Language-DDL
 Data Definition Language (DDL) statements are used to
define the database structure or schema.
Some examples:
 CREATE - to create objects in the database
 ALTER - alters the structure of the database
 DROP - delete objects from the database
 TRUNCATE - remove all records from a table, including all
spaces allocated for the records are removed
 COMMENT - add comments to the data dictionary
 RENAME - rename an object
 Data Manipulation Language (DML)

Data Manipulation Language (DML) statements are used for

managing data within schema objects.
Some examples:
 SELECT - Retrieve data from the a database
 INSERT - Insert data into a table
 UPDATE - Updates existing data within a table
 DELETE - deletes all records from a table, the space for
the records remain
 MERGE - UPSERT operation (insert or update)
 CALL - Call a PL/SQL or Java subprogram
 EXPLAIN PLAN - explain access path to data
 LOCK TABLE - control concurrency
THANK YOU !