Transcript Database Systems: Design, Implementation, and Management

1
Database Systems: Design,
Implementation, and Management
CHAPTER 1
File Systems and Databases
Chapter Objectives






What a database is, what it does, and why database design
is important
How modern databases evolved from files and file systems
Data management problems associated with a file system
Advantages of using a database over a file system
Functions of a database management system
Five different types of database models
2
Introducing the Database

Data versus Information
 Data
constitute building blocks of information
 Information produced by processing data
 Information reveals meaning of data
 Good, timely, relevant information key to decision making
 Good decision making key to organizational survival
3
Database Management

Database is shared, integrated computer structure housing:
 End
user data
 Metadata

Database Management System (DBMS)
 Manages
Database structure
 Controls access to data
 Contains query language
4
Importance of DBMS
•
•
•
•
•
5
Makes data management more efficient and effective
Query language allows quick answers to ad hoc queries
Provides better access to more and better-managed data
Promotes integrated view of organization’s operations
Reduces the probability of inconsistent data
DBMS Manages Interaction
6
Historical Roots
Table 1.1 Basic File Terminology
Data
“Raw” facts that have little meaning unless they have been
organized in some logical manner. The smallest piece of data
that can be “recognized” by the computer is a single character.
Example: the letter A, the number 5, symbols: ‘ ? > * +.
Field
A character or group of characters (alphabetic or numeric) that
has a specific meaning. Example: Student Name, GPA, ID
Record
A logically connected set of one or more fields that describes a
person, place, or thing. For example, a student’s record may
comprise of his/her name, address, phone number, date of
birth, class, GPA, and so on.
File
A collection of related records. For example, a file might contain
the records for all students currently enrolled at UTA.
7
8
Historical Roots
Figure 1.5
File System Critique

9
File System Data Management
 File
systems require extensive programming in 3GL.
 As file systems become more complex, managing files gets
more difficult.
 Security features are difficult to implement and are lacking.
 Changing requirements mandate changes in existing file
structures.
 Programs accessing a file are subject to change when the file
structure changes - More maintenance.
File System Critique

Structural and Data Dependence

Structural Dependence
A change in any file’s structure requires the modification of all
programs using that file.
Example:addition or deletion of a field

Data Dependence
A change in any file’s data characteristics requires changes of all
data access programs.
1.
Data logical format (how human beings view)
2.
Data physical format (how computer sees)
Example: integer to decimal,

Structural and data dependence make file systems very
difficult to manage - High Maintenance.
10
File System Critique

Field Definitions and Naming Conventions:
break up to last name, firstname etc.; area code and phone#.

Data Redundancy:
The same information is stored in more than one place.
Example:

Problems due to uncontrolled data redundancy
 Data

Example:
 Data



Inconsistency (lack of data integrity)
anomalies
Modification anomalies (Inconsistent data due to modifications).
Example:
Insertion anomalies (Inconsistent data due to insertions)
Example:
Deletion anomalies (Inconsistent data due to deletions)
Example:
11
Database Systems





12
The database represents a change in the way end user data
are stored, accessed, and managed.
Logically related data are stored in a single data repository.
DBMS makes it easier to eliminate most of the file
system’s data inconsistency, data anomalies, and data
structural dependency problems.
DBMS stores not only the data structure, but also the
relationships.
Note the difference between a database and a DBMS
File System vs Database Systems
Figure 1.6
13
Database Systems
Figure 1.7
14
Database Systems

The Database System Components
 Hardware


Computer
Peripherals
 Software



Operating systems software
DBMS software
Applications programs and utilities software
15
Database Systems

16
The Database System Components
 People





Systems administrators:
Database administrators
Database designers
Systems analysts and programmers
End users
 Procedures

Instructions and rules that govern the design and use of the database
system
 Data

Collection of facts stored in the database
Database Systems

Types of Database Systems
 Number


of Users
Single-user
Multi-user
 Scope



Desktop (Single user)
Workgroup (Used by a department, nbr of users < 50)
Enterprise (Used by the entire organization, users in 100s)
17
Database Systems

18
Types of Database Systems
 Location


Centralized (Database located in a single site)
Distributed (Database distributed across multiple sites)
 Use

Transactional (aka Production)


Data integrity, consistency and operational efficiency are important
Decision support or Data warehouse (for strategic or tactical decision)


Historical, aggregated data
Used for decision support (for forecast, pricing,market positioning, etc. long
term issues. Extenssive data manipulation)
Database Systems

19
DBMS Functions
 Data


Data dictionary stores definitions of the data elements and their
relationships (metadata).
It removes structural and data dependency from the system.
 Data

Storage Management
DBMS creates data storage structure and relieves the programmer
from the task of defining and programming physical data
characteristics.
 Data

Dictionary Management
Transformation and Management
DBMS transforms data from its logical format to its physical format
and vice versa.
 Security


Management
DBMS provides user security and data privacy within the database.
Data security is especially important in multi-user database.
Database Systems

20
DBMS Functions
 Multi-User Access

DBMS ensures that multiple users can access the database
concurrently and still guarantees the integrity of the database.
 Backup


and Recovery Management
DBMS provides backup and recovery procedures to ensure data
safety and integrity.
 Data

Control
Integrity Management
DBMS promotes and enforces integrity rules to eliminate data
integrity problems.
Ensuring data integrity is especially important in transaction-oriented
database systems.
Database Systems

21
DBMS Functions
 Database Access
Languages & Application Programming
Interfaces




The DBMS’s non procedural query language simplifies data access
Data Definition Language (DDL) is used to define the database
structure
Data Manipulation Language (DML) is used for accessing and
manipulating data
DBMS also provides interfaces to application programs written in
procedural languages such as COBOL, C etc.
 Database

Communication Interfaces
DBMS provides communication interfaces so that the database can
be accessed through a network (internet).
Database Models


22
A database model is a collection of logical constructs
used to represent the data structure and the data
relationships found within the database.
Two Categories of Database Models
 Conceptual
models focus on the logical nature of the data
representation. They are concerned with what is represented
rather than how it is represented. (E-R model, OO)
 Implementation models place the emphasis on how the
data are represented in the database or on how the data
structures are implemented. (Hierarchical, Network,
relational, OO)
Database Models

Three Types of Relationships in Conceptual Database
Models
 One-to-many

relationships
Example:
 Many-to-many

Example:
 One-to-one

relationships
Example:
relationships
23
Database Models

Types of Implementation Database Models
 Hierarchical
 Network
 Relational
 Object
Oriented
24
Database Models

Hierarchical Database Model
 Background



GUAM (Generalized Update Access Method) was developed by
North American Rockwell to simplify parts inventory management
for the Apollo project.
Information Management System (IMS) -- Jointly developed by
IBM and Rockwell.
Data records are organized in an inverted (upside-down) tree
structure -- hierarchical structure.
25
26
Database Models
Figure 1.8
Database Models

27
Hierarchical Database Model
 Basic


Structure
Collection of records (segments) organized to conform to the
upside-down tree structure.
One-to-Many (1:M) Relationship




A tree structure is represented as a hierarchical path on the
computer’s storage media.



Each parent can have many children.
Each child has only one parent.
Example:
Preorder (Top down) traversal-more popular
Postorder (Bottom up) traversal:
With preorder traversal frequently accessed segments are
placed close to the left side of the tree to minimize access
time.
Database Models

28
Hierarchical Database Model
 Advantages





Conceptual simplicity
Data sharing and security provision
Data independence -- Reduced programming and maintenance effort
Database integrity- a child must have a parent
Efficiency dealing with a large database containing data with
predominantly 1:M relationships - many business applications fall in
this category
Database Models

29
Hierarchical Database Model
 Disadvantages








Requirement of knowledge of physical level of data storage
Inability to represent relationships that do not conform to the
hierarchical 1:M standard
Applications programming is complex - programmer must know the
database structure and how to traverse it
Lack of ad hoc query capability for end users
Difficult to manage and Lack of standards
Complex implementation (No standard was developed)-- limited
portability
Requires extensive programming activities to use the database Most serious problem
Lacks structural independence
Database Models

30
Network Database Model
 Background


CODASYL (Conference on Data Systems Language) group created
DataBase Task Group (DBTG) in 1971 to develop standards for a
database environment.
DBTG specified three crucial database components:



Network schema defines the conceptual organization of the entire
database as viewed by the database administrator.
Subschema defines the portion of the database as seen by the
applications programs.
Data Management Language defines the data characteristics and the
data structure and to manipulate the data.
Database Models

Network Database Model
 Background

Three DBTG data management language components:





Schema Data Definition Language (DDL) - used by DBA to define
schema components
Subschema Data Definition Language - used by application programs
to define part of the schema to be used by the program
Data Manipulation Language - allows manipulation of the database
contents
ANSI SPARC (Standards Planning And Requirements Committee)
augmented the database standards in 1975.
A popular network DBMS is Computer Associates’ IDMS/R
31
Database Models

Network Database Model
 Basic

Structure
Set -- A relationship is called a set. Each set is composed of at least
two record types: an owner (parent) record and a member (child)
record. A Member may have several owners.
32
33
Database Models

Network Database Model

Relationships among the records are decomposed into a
series of sets.
Figure 1.10
Database Models

34
Network Database Model
 Advantages





Conceptual simplicity
Handles more relationship types. Easier implementation of M:N
relationships
More flexible data access compared to Hierarchical model - An application
can access an owner and all the members within the set, one can also access
owner from a member.
Enforced data integrity - owner record must be defined before defining
member records
Data independence through the DBMS
 Disadvantages



Very complex structure from the application programmer’s point of view
Difficult to design and use properly - because of the navigational nature of
the data structure
Difficult to make changes in a database - no structural independence
Database Models

35
Relational Database Model
 Background

E. F. Codd developed the relational model in 1970.



Conceptually simple but versatile
Major breakthrough for both users and designers
Requires more computing power


Considered impractical in the 1970’s
Modern computers (even PCs) are powerful enough to handle relational
databases.
Database Models

36
Relational Database Model
 Basic

Structure
Relational Data Base Management System (RDBMS)





Relational database is perceived by the user as a collection of tables in
which data are stored.
Each table consists of series of row/column intersections.
Tables (or relations) are related to each other by sharing a common
entity characteristic.
The relationship type is often shown in a relational schema.
A table yields complete data and structural independence because it is a
purely logical structure.
Database Models

Relational Database Model
37
Database Models
38
Database Models

39
Relational Database Model
 Advantages




Data independence and structural independence
Improved conceptual simplicity. Easy to design, implement and manage
(powerful DBMS).
Less programming effort required
Powerful and flexible query capability (Ad hoc query capability):

Structured Query Language (SQL)
 Fourth Generation Language (4GL)
 Specify “what to do” not “how to do”
 Introduced by IBM in 1974
 Disadvantages



RDBMS requires substantial hardware and operating system overhead.
It tends to be slower than other database systems.
Poor design and implementation is made easy.
Entity Relationship Database Model



Complements the relational data model concepts
Represented in an entity relationship diagram
(ERD)
Based on entities, attributes, and relationships
40
Entity Relationship Database Model

Advantages
 Exceptional
conceptual simplicity
 Visual representation
 Effective communication tool
 Integrated with the relational database model

Disadvantages
 Limited
constraint representation
 Limited relationship representation
 No data manipulation language
 Loss of information content
41
Database Models

42
OO Database Model
 Objects

are abstractions of real-world entities
Example: Student, Invoice, Employee
 Objects
capture both attribute and behavior (methods)
 Objects with similar characteristics are grouped together as a
class
 Classes are organized in a class hierarchy
 A subclass inherits the attributes and methods from its
superclass
Database Models

OO Database Model
 Advantages




Provides a richer modeling language - captures attributes, methods,
and class hierarchies
Useful in niche application areas such as CAD/CAM
Database integrity
Both structural and data independence
 Disadvantage





Data access method resembles the navigational style
Lack of standardization
Lack of OODM
Steep learning curve
High system overhead slows transactions
43
Review














File system
Basic File Terminology
Data Management
Structural Dependence
Data Dependence
Data Redundancy
Data Inconsistency
Data anomalies
Database Systems
stores not only the data structure, but also the relationships.
difference between a database and a DBMS
Structure: Hardware- endusers.
Components:people,procedures,data
Types:no of users, scope,location, use.
44
Review
DBMS Functions
Data Dictionary Management
Data Storage Management
Data Transformation and Management
Security Management
Multi-User Access Control
Backup and Recovery Management
Data Integrity Management
Database Access Languages & Application Programming Interfaces
Database Communication Interfaces
Database models
Conceptual models
Implementation models
Three Types of Relationships in Conceptual Database Models
Types of Implementation Database Models
Hierarchical, Network, Relational,OO.
Structure, Advantages, Disadvantages.
45