Ch 1

Database Systems
 Ch 1
Overview
• Introduction to the Database
– Database Definitions
– Databases & Traditional File Processing
Systems
– Advantages of Database Approach
– Costs & Risks of the Database Approach
– Functions of Database Management Systems
(DBMS)
– Range of Database Applications
– Components of the Database Environment
– Evolution of Database Systems

Database Systems, Fall 2024. 2

 Ch 1
Definitions
• Data:
– Meaningful facts, text, graphics, images, sound, video
segments
– Usually in certain context e.g. Class Roster

Figure 1‐1a: Data in Context

Database Systems, Fall 2024. 3
 Ch 1
Definitions
• Information:
– Data processed to be useful in decision making and
interpretation

Figure 1‐1b: Summarized Data/Information

Database Systems, Fall 2024 4
 Ch 1
Database & Its Terminologies
• Database: An organized collection of logically related
data
• Field (data item):
– Smallest unit of information in database
– It is collection of related characters (numeric or alphabetic)
that define a characteristic of an entity (person, place or
thing)
• Record:
– Fields are grouped together to form records.
– It is collection of related (logically connected) fields
• File: collection of similar types of records

Database Systems, Fall 2024. 5

 Example – A Customer Table Degree(5)

Primary Key

Cust_id Last_name First_name Address Postal_Code Table,

Relation
Customer 1 Cramer John 213 Main St. 22160

2 Adams Steven 333 Bering St. 33140

3 Cramer Ann 14 Wadhurst Rd. 50320

Row,
4 Martin Andrew 744 Baker Blvd. 22200
Record,
5 Smith Patricia 55 Jeffer Way 52100 Tuple
6 Pipps Robert 62 Polk St. 50920

Field Value, Column,

Data Value Attribute

Table Name, Cardinality(6)

Relation Name
Database Systems, Fall 2024. 6
 Ch 1
Definitions (Cont.)
• Database Management System (DBMS): a system
software that facilitates the management of a
database and controls the access to the data
stored in the database

Database Systems, Fall 2024. 7

 Ch 1
Definitions (Cont.)
• Database System: system consisting of a database, a
DBMS, hardware and people
• Database Design: The design of the database
structure that will be used to store and manage data
(not the design of the DBMS)
• Data Dictionary (DD) or Metadata: describes the
characteristics of data stored in a database and
the inter- relationships among data

Database Systems, Fall 2024. 8

 Ch 1
Table 1‐1: Metadata
Description of the properties or characteristics of
the data, including data types, field sizes, allowable
values, and documentation

Database Systems, Fall 2024. 9

 Ch 1
Figure 1‐2: Three File
Processing Systems at Pine
Valley Furniture

Database Systems, Fall 2024. 10

 Ch 1
Disadvantages of File Processing
• Program-Data Dependence
– All programs maintain metadata for each file they use
• Data Redundancy (Duplication of data)
– Different systems/programs have separate copies of the
same data
• Limited Data Sharing
– No centralized control of data
• Lengthy Development Times
– Programmers must design their own file formats
• Excessive Program Maintenance
– 80% of information systems budget

Database Systems, Fall 2024. 11

 Ch 1
Figure 1‐2: Three File
Processing Systems at Pine
Valley Furniture
Duplicate Data

Database Systems, Spring 2024. 12

 Ch 1
Problems with Data
Dependency
• Each application programmer must maintain their
own data
• Each application program needs to include code
for the metadata of each file
• Each application program must have its own
processing routines for reading, inserting, updating
and deleting data
• Lack of coordination and central control
• Non-standard file formats

Database Systems, Fall 2024. 13

 Ch 1
Problems with Data Redundancy
• Waste of space to have duplicate data
• Causes more maintenance headaches
• The Biggest Problem
– When data changes in one file, could cause
inconsistencies
– Compromises data integrity

Database Systems, Fall 2024. 14

 Ch 1
Solution: The DATABASE Approach
• Central repository of shared data
• Data is managed by a controlling
agent
• Stored in a standardized, convenient
form
Central
Controllin Repositor
g y
Agent

Requires a Database Management System

(DBMS)
Database Systems, Fall 2024. 15
 Ch 1
Database Management System

• A DBMS is a data storage and retrieval system which

permits data to be stored non-redundantly while
making it appear to the user as if the data is well-
integrated.

Database Systems, Fall 2024. 16

 Ch 1
Database Management System

Database Systems, Fall 2024. 17

 Ch 1
Advantages of Database Approach
• Program-Data Independence
– Metadata stored in DBMS, so applications don’t need to worry
about data formats
– Data queries/updates managed by DBMS so programs don’t need
to process data access routines
– Results in: increased application development and maintenance
productivity
• Minimal Data Redundancy
– Leads to increased data integrity/consistency
• Improved Data Sharing
– Different users get different views of the data
• Enforcement of Standards
– All data access is done in same way
• Improved Data Quality
– Constraints, data validation rules

Database Systems, Fall 2024. 18

 Ch 1
Advantages of Database Approac h
(Cont.)
• Better Data
Accessibility/Responsiveness
– Use of standard data query language
(SQL)
• Security, Backup/Recovery,
Concurrency
– Disaster recovery is easy

Database Systems, Fall 2024. 19

 Ch 1
Database Vs. File Systems

Database Systems, Fall 2024. 20

 Ch 1
The Database Approach
• The enterprise data model is a graphical model
that shows the high-level entities for the
organization and the relationships among those
entities.

• Enterprise data model is represented with the help

of a E-R diagram that shows the entities, attributes,
relations, connectivities and cardinalities.

Database Systems, Fall 2024. 21

Pine Valley Furniture Company (PVFC) Ch 1

Segment from Enterprise Data Model

Entity
Relationship
Diagram

Figure 1‐3b: Segment from Enterprise Data Model

Database Systems, Fall 2024. 22

Pine Valley Furniture Company (PVFC) Ch 1
Physical Tables in MS Access
Relationships
established in special
columns that provide
links between tables

•Each table row (tuple) represents a single entity occurrence within the entity set.
•Each table column represents an attribute, and each column has a distinct name.
•Each column/row intersection represents a single data value.
•All values in a column must confirm to the same data format.
•The order of the row and columns is immaterial to the DBMS.
•Each table must have an attribute or a combination of attributes that uniquely identifies each row.
Database Systems, Fall 2024. 23
Pine Valley Furniture Company (PVFC) Ch 1
Physical Tables in MySQL
Order

Customer

Order
Product
Line

Database Systems, Fall 2024. 24

Pine Valley Furniture Company (PVFC) Ch 1
Metadata in MySQL
Customer

Order

Order
Line

Database Systems, Fall 2024. 25

 Ch 1
Costs & Risks of the Database
Approach
• Up-front Costs
– Installation Management Cost and Complexity
– Conversion Cost
• Ongoing Costs
– Requires New, Specialized Personnel
– Need for Explicit Backup & Recovery
• Organizational Conflicts
– Old habits die hard

Database Systems, Fall 2024. 26

 Ch 1
Components of the Database
Environment
• CASE Tools – computer-aided software engineering
• Repository – centralized storehouse of metadata
• Database Management System (DBMS) –
software for managing the database
• Database – storehouse of the data
• Application Programs – software using the data
• User Interface – text and graphical displays to
users
• Data Administrators – personnel
responsible for maintaining the database
• System Developers – personnel responsible for
designing databases and software
• End Users – people who use the applications and
databases
Database Systems, Fall 2024. 27
 Ch 1

Figure 1‐5:
Components
of the
Database
Environment

Database Systems, Fall 2024. 28

 Ch 1
The Range of Database Applications
• Personal Databases (Standalone desktop database)
• Two-Tier Client/Server Databases (Local area
network with
<100 users)
• Multi-Tier Client/Server Database (Local area
network with
<1000 users)
• Enterprise Database – wide-area network
(more than thousands of users)

Database Systems, Fall 2024. 29

Database development process
 Ch 1

Typical Data
from a
Personal
Computer
Database

Database Systems, Fall 2024. 31

 Ch 1
Figure 1‐6: Two‐Tier Database
with Local Area Network

Database Systems, Fall 2024. 32

 Ch 1
Figure 1‐7: Three‐Tier Database
with Local Area Network

Database Systems, Fall 2024. 33

 Ch 1

An Enterprise
Data
Warehouse

Database Systems, Fall 2024. 34

 Ch 1

Summary of Database Applications

Database Systems, Fall 2024. 35

 Ch 1
Figure 1‐13: Client/Server System for
Pine Valley Furniture Company

A dedicated computer which provides

database services to other computer
programs or computers. Examples:
Microsoft SQL Server, Oracle, DB2, and
Informix.

Database Systems, Fall 2024. 36

 Ch 1
Evolution of Database Systems
• Flat files - 1960s - 1980s

• Hierarchical – 1970s - 1990s

• Network – 1970s - 1990s

• Relational – 1980s - present

Figure 1‐8a: Evolution of Database Technologies
• Object-oriented – 1990s - present

• Object-relational – 1990s - present

• Data warehousing – 1980s - present

• Web-enabled – 1990s – present

• Cloud – 2000s – present

Database Systems, Fall 2024. 37

 Ch 1
Evolution of Database Systems (Cont.)

Figure 1‐8b: Database Systems

Database Systems, Fall 2024. 38
 Ch 1

Concept Map

Database Systems, Fall 2024. 39

 Ch 1
Summary
• Introduced various terms
• Discussed traditional file processing system
and its disadvantages
• Covered database approach, its advantages,
and various costs
• Introduced Enterprise Data Model and its
various constructs
• Discussed components of database
environment
• Range of database applications
• Evolution of database systems

Database Systems, Fall 2024. 40