Database Systems
Basic Database Concepts
Objectives
• Basic Concepts & Architecture
• History
• DBMS Benefits
• DBMS Abstraction
• Database Schema and State
Dr. Ejaz Ahmed 2
Non-Relational Systems
• Database systems can be conveniently categorized
according to the data structures and operators
they present to the user
• The oldest system fall into three broad categories:
– Flat-File Systems
– Hierarchical Systems
– Network Systems
• We will not discuss these categories in great detail
as, although some are still used, they are obsolete
• We will focus on relational databases
Dr. Ejaz Ahmed 3
Files, Records, and Fields
Dr. Ejaz Ahmed 4
Indexed Files
Dr. Ejaz Ahmed 5
History of Database Systems
• First-generation
– Hierarchical and Network
• Second generation
– Relational
• Third generation
– Object Relational
– Object-Oriented
Dr. Ejaz Ahmed 6
Terms and Concepts
• Data Independence
– Physical representation and location of data
and the use of that data are separated
• The application doesn’t need to know how or
where the database has stored the data, but just
how to ask for it
• Moving a database from one DBMS to another
should not have a material effect on application
program
• Recording, adding fields, etc. in the database
should not affect applications
Dr. Ejaz Ahmed 7
Objectives of Three-Level
Architecture
• All users should be able to access same
data
• The user’s view is immune to change
made in other views
• User should not need to know physical
database storage details
Dr. Ejaz Ahmed 8
Objectives of Three-Level
Architecture
• DBA should be able to change database
storage structures without affecting the
user’s views
• Internal structure of database should be
unaffected by changes to physical aspects
of storage
• DBA should be able to change conceptual
structure of database without affecting all
users
Dr. Ejaz Ahmed 9
ANSI-SPARC Three-Level
Architecture
Dr. Ejaz Ahmed 10
ANSI-SPARC Three-Level
Architecture
• External Level
– User’s view of the database
– Describes that part of database that is
relevant to a particular user
• Conceptual Level
– Community view of the database
– Describes what data is stored in database
and relationships among the data
Dr. Ejaz Ahmed 11
ANSI-SPARC Three-Level
Architecture
• Internal Level
– Physical representation of the database on
the computer
– Describes how the data is stored in the
database
Dr. Ejaz Ahmed 12
Differences between Three Levels of
ANSI-SPARC Architecture - Example
Dr. Ejaz Ahmed 13
Data Independence
• Logical Data Independence
– Refers to immunity of external schemas to
changes in conceptual schema
– Conceptual schema changes (e.g.
addition/removal of entities)
– Should not requires changes to external
schema or rewrites of application programs
Dr. Ejaz Ahmed 14
Data Independence and the ANSI-
SPARC Three-Level Architecture
Dr. Ejaz Ahmed 15
Schemas and Instances
• Similar to types and variables in programming languages
• Schema – the logical structure of the database
– E.g., the database consists of information about a set of
customers and accounts and the relationship between them
– Analogues to type information of a variable in a program
– Physical schema: database design at the physical level
– Logical schema: database design at the logical level
– Sub-Schema: there may be several of these at the view level,
each a different view of database
• Instance – the actual content of the database at a particular
point in time
Hello
– Analogous to the value of the variable
Dr. Ejaz Ahmed 16
-- Database Schema
• Is the description of a database
• It is specified during the database design
and is not expected to change frequently.
• It is represented as a diagram called
schema diagram.
– A schema diagram displays the structure of
each record type but not the actual instance
of a record.
• Each object in a schema is called a
schema construct.Dr. Ejaz Ahmed 17
--- Example of a Database
Schema
Dr. Ejaz Ahmed 18
-- Database State
• A Database state or instance is the data in
the database at a particular moment of
time.
• Every update operation changes the
database from one state to another.
• The Schema is sometimes is called the
intension, and the database state an
extension of the schema.
Dr. Ejaz Ahmed 19
--- Example of a Database State
Dr. Ejaz Ahmed 20
Database Languages
• Data Definition Language (DDL)
– Allows the DBA or users to describe and
name entities, attributes, and relationships
required for the application
– Plus any associated integrity and security
constraints
Dr. Ejaz Ahmed 21
Database Languages
• Data Manipulation Language (DML)
– Provides basic data manipulation operations
of data held in the database
• Procedural DML
– Allows user to tell system exactly how to
manipulate data
• Non-Procedural DML
– Allows user to state what data is needed
rather than how it is to be retrived
Dr. Ejaz Ahmed 22
Database Languages (4GL)
• Fourth generation Languages (4GL)
– Query Languages
– Form Generators
– Report Generators
– Graphics Generators
– Application Generators
Dr. Ejaz Ahmed 23
END
Dr. Ejaz Ahmed 24
