Chapter One

Fundamentals of
Database Systems
 Chapter 1 - Objectives
 Some common uses of database systems.
 Characteristics of file-based systems.
 Problems with file-based approach.
 Meaning of the term database.
 Meaning of the term Database Management System (DBMS).
 Typical functions of a DBMS.
 Major components of the DBMS environment.
 Personnel involved in the DBMS environment.
 Generations of the development of DBMSs.
 Advantages and disadvantages of DBMSs.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Chapter 1 - Objectives Cont’d…
Purpose of three-level ANSI-SPARC database
architecture.
Contents of external, conceptual, and internal levels.
Purpose of external/conceptual and conceptual/internal
mappings.
Meaning of logical and physical data independence.

Distinction between DDL,DCL,TCL and DML.

A classification of data models.
Architecture for Multi-User Database System

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Database systems
Today, Databases are essential to every
business. They are used to maintain
internal records, to present data to
customers and clients on the World-
Wide-Web, and to support many other
commercial processes.
Databases are likewise found at the
core of many modern organizations.
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Examples of Database Applications
Purchases from the supermarket
Purchases using your credit card
Booking a holiday at the travel agents
Using the local library
Taking out insurance
Renting a video
E- Commerce
Banking, Insurance and other Financial Systems
Social media (in general the WWW)

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Data Handling Approaches in
Organizations
Data management passes through different levels
of development along with the development in
technology and services. These levels could best be
described by categorizing the levels into three
levels of development. Even though there is an
advantage and a problem to overcome at each new
level, all methods of data handling are in use even
today to some extent. The major three levels are;
 Manual Approach
 Traditional File Based Approach
 Database Approach

Fundamentals of Database Systems (INSY2031) 02/06/2024

Manual Approach
 Cards and paper are used for the purpose
 Files for as many event and objects as the organization has are used to store
information.
 Each of the files containing various kinds of information is labelled and stored in
one or more cabinets.
 The cabinets could be kept in safe places for security purpose based on the
sensitivity of the information contained in it ( Cabinet Lockers).
 Insertion and retrieval is done by searching first for the right cabinet then for the
right the file then the information.
 One could have an indexing system to facilitate access to the data
 Limitations of the Manual approach
 Prone to error
 Difficult to update, retrieve, integrate
 You have the data but it is difficult to compile the information
 Limited to small size information
 Cross referencing is difficult

Fundamentals of Database Systems (INSY2031) 02/06/2024

Manual – Approach to data Management

Fundamentals of Database Systems (INSY2031) 02/06/2024

 File-Based Systems
Collection of application programs that perform
services for the end users (e.g. reports).
Each program defines and manages its own data.
A file is simply a collection of records, which
contains logically related data.
Each record contains a logically connected set of
one or more fields, where each field represents
some characteristic of the real-world object that
is being modeled.
Fundamentals of Database Systems (INSY2031) 02/06/2024
File-Based Processing

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Limitations of File-Based Approach
Separation and isolation of data
Each program maintains its own set of data.
Users of one program may be unaware of
potentially useful data held by other programs.

Duplication of data (Uncontrolled Redundancy)

Same data is kept by different programs.
Wasted space and potentially different values
and/or different formats for the same item.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Limitations of File-Based Approach
 Data dependence (Program Data Dependence)
File structure is defined in the program code.
Any change in the data structure need to changes the
program as well

 Incompatible file formats

Programs are written in different languages, and so
cannot easily access each other’s files. (C, COBOL) –
Data Structures are different for different Languages

 Fixed Queries/Proliferation of application programs

Programs are written to satisfy particular functions.
Any new requirement needs a new program.
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Database Approach
Problems of file approach arose because:
Definition of data was embedded in application
programs, rather than being stored separately and
independent of the applications.
No control over access and manipulation of data
beyond that imposed by application programs.

Solution(Result):
The database and Database Management System
(DBMS).
This approach solves the problems of the File-based
Approach

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Database
Shared collection of logically related data and a
description of this data, designed to meet the
information needs of an organization.

System catalogue (metadata) provides description

of data to enable program–data independence.

Logically related data comprises entities,

attributes, and relationships between entity of an
organization’s information.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Database Management System (DBMS)
A software system that enables users to define,
create, maintain, and control access to the
database.

(Database) application program: a computer

program that interacts with database by issuing an
appropriate request (SQL statement) to the DBMS.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Management System (DBMS)

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Database Approach Cont’d…..
Structured Query Languages (SQL)- is the formal
and de facto Standard for RDBMS
Having a query facility alleviates the Problem of
fixed query/application proliferation in file base
approach.
DBMSs Provides Four types of SQL Statements
DDL (Data Definition Language)
DML (Data Manipulation Language)
DCL (Data Control Language)
TCL (Transaction Control Language)

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Approach Cont’d…
Data definition language (DDL).
 Permits specification of data types, structures and any data
constraints.
 All specifications are stored in the database.
 Enables the Creation, Alteration and Removal of a Database
Object
Data manipulation language (DML).
 General enquiry facility (query language -retrieval) of the
data.
 In addition to querying, we can also have manipulation of
Data (Adding New info, Updating info, Deleting info)

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Approach Cont’d…
DCL (Data Control Language)
DCL statements control the level of access that users have on
database objects.
 GRANT – allows users to read/write on certain database objects
 REVOKE – keeps users from read/write permission on database
objects
TCL (Transaction Control Language)
TCL statements allow you to control and manage
transactions to maintain the integrity of data within SQL
statements.
 BEGIN Transaction – opens a transaction
 COMMIT Transaction – commits a transaction
 ROLLBACK Transaction – ROLLBACK a transaction in case of
any error
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Database Approach– Access Control
Controlled access to database may
include: (User/Role- definition, Privilege
Assignment/Revocation, Access
Enforcement )
a security system
an integrity system
a concurrency control system
a recovery control system
a user-accessible catalogue.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Data base Views
The Database Approach( with all the
functionalities stated above) introduces a little
complexity on part of the end user.
Although the Database is a shared collection, users
are interested in their specific data needs
A view allows each user to have his or her own view
of the database.
A view is essentially some subset of the database.
Data Irrelevant to a user is not at all visible

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Views - Benefits
Reduce complexity
Provide a level of security
Provide a mechanism to customize the appearance
of the database
Present a consistent, unchanging picture of the
structure of the database, even if the underlying
database structure is changed ( Abstraction)

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Components of DBMS Environment

In A DBMS Environment there are Five basic Components

• Hardware
• Software
• Data
• Procedure
• People
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Components of DBMS Environment
Hardware
Can range from a PC to a network of computers.
Includes all the necessary input, output , storage
and backup devices
Software
DBMS, operating system, network software (if
necessary) and also the database application
programs.
Data
Data used by the organization and a description of this
data called the schema(Metadata).

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Components of DBMS Environment
Procedures
Instructions and rules that should be applied to
the design and use of the database and DBMS.
People
Different Roles taken by people while designing and
using a Database systems

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Roles in the Database Environment
 Data Administrator (DA)
 Responsible on management of data resources. This
involves in database planning, development, maintenance
of standards, policies and procedures at the conceptual
and logical design phases.
 Database Administrator (DBA)
This is more technically oriented role. DBA is responsible
for the physical realization of the database. It is involved
in physical design, implementation, security and integrity
control of the database.
Also deals with Optimizing the performance of the system

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Designers
Database Designers (Logical and Physical)
Identifies the data to be stored and choose the
appropriate structures to represent and store the data.
Should understand the user requirement and should
choose how the user views the database.
Involve on the design phase before the implementation
of the database system.
We have two distinctions of database designers, one
involving in the logical &conceptual design and
another involving in physical design.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Designers- Cont’d…
Logical and Conceptual DBD
 Identifies data (entity, attributes and relationship) relevant to the
organization
 Identifies constraints on each data
 Understand data and business rules in the organization
 Sees the database independent of any data model at conceptual level
and consider one specific data model at logical design phase.
Physical DBD
 Take logical design specification as input and decide how it should be
physically realized.
 Map the logical data model on the specified DBMS with respect to
tables and integrity constraints. (DBMS dependent designing)
 Select specific storage structure and access path to the database
 Design security measures required on the database

Fundamentals of Database Systems (INSY2031) 02/06/2024

Application Programmers
System analyst determines the user requirement and
how the user wants to view the database.
The application programmer implements these
specifications as programs; code, test, debug,
document and maintain the application program.
The application programmer determines the
interface on how to retrieve, insert, update and
delete data in the database.

Fundamentals of Database Systems (INSY2031) 02/06/2024

End Users
Naïve Users:
 Sizable proportion of users
 Unaware of the DBMS
 Only access the database based on their access level and demand
 Use standard and pre-specified types of queries.
Sophisticated Users
 Users familiar with the structure of the Database and facilities of the DBMS.
 Have complex requirements
 Have higher level queries
 Are most of the time engineers, scientists, business analysts, etc
Casual Users
 Users who access the database occasionally.
 Need different information from the database each time.
 Use sophisticated database queries to satisfy their needs.
 Are most of the time middle to high level managers

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Generation of Database Systems
First-generation
Hierarchical and Network

Second generation
Relational

Third generation
Object-Relational
Object-Oriented

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Advantages of DBMSs
Control of data redundancy
Data consistency
More information from the same amount of data
Sharing of data
Improved data integrity
Improved security
Enforcement of standards
Economy of scale

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Advantages of DBMSs
Balance conflicting requirements
Improved data accessibility and responsiveness
Increased productivity
Improved maintenance through data independence
Increased concurrency
Improved backup and recovery services

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Disadvantages of DBMSs
Complexity
Size ( in terms of storage space and memory
required)
Cost of DBMS
Additional hardware costs(Computers, storage)
Cost of conversion( Data, Application, Training
Users)
Performance ( Since DBMSs are generic solutions)
Higher impact of a failure ( since it is centralized)

Fundamentals of Database Systems (INSY2031) 02/06/2024

ANSI-SPARC Three-Level Architecture

Fundamentals of Database Systems (INSY2031) 02/06/2024

 ANSI-SPARC Three-Level Architecture
External Level
Users’ 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.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 ANSI-SPARC Three-Level Architecture
Internal Level
Physical representation of the database on the
computer.
Describes how the data is stored in the database.
This is how the OS and DBMS view Data

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Differences between Three Levels of
ANSI-SPARC Architecture

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Objectives of Three-Level ANSI-SPARC
Architecture
All users should be able to access same data.

A user’s view is immune to changes made in other

user’s views.

Users should not need to know physical database

storage details.

Fundamentals of Database Systems (INSY2031)

 Objectives of Three-Level ANSI-SPARC
Architecture
DBA should be able to change database storage
structures without affecting the users’ 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.

Fundamentals of Database Systems (INSY2031)

 ANSI-SPARC Data Independence
Main Concept is Upper layers are immune to
changes in the lower layers
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 require changes to external
schema or rewrites of application programs.
Obviously particular users will be affected but not all
users

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Data Independence
Physical Data Independence
Refers to immunity of conceptual schema to changes in
the internal schema.
Internal schema changes (e.g. using different file
organizations, storage structures/devices) should not
require change to conceptual or external schemas.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Schema Mapping- Provision for Data
Independence
ANSI-SPARC has three Layers of Describing
Organizational Data
The DBMS is responsible for mapping between these three
types of schema.
External/Conceptual Mapping
This enables the DBMS to map names in the user’s view on to
the relevant part of the conceptual schema.
Conceptual/Internal Mapping
This enables the DBMS to find the actual record or combination
of records in physical storage that constitute a logical record in
the conceptual schema, together with any constraints to be
enforced on the operations for that logical record.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Data Independence and the ANSI-
SPARC Three-Level Architecture

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Database Manipulation Languages
Data Manipulation Language (DML)
Provides basic data manipulation operations on 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 retrieved.
Fourth Generation Languages (4GLs)
Automated CASE tools

Fundamentals of Database Systems (INSY2031) 02/06/2024

Data Model
A Definition: Integrated collection of concepts for describing
data, relationships between data, and constraints on the data
in an organization.
 Data Model comprises:
 a structural part;
 a manipulative part;
 possibly a set of integrity rules.
 Can have Three types of Models ( In-line with ANSI-SPARC)
1. an external data model, to represent each user’s view of
the organization, sometimes called the Universe of
Discourse (UoD);
2. a conceptual data model, to represent the logical (or
community) view that is DBMS independent;
3. an internal data model, to represent the conceptual schema
in such a way that it can be understood by the DBMS.
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Data Model
Purpose
To represent data in an understandable way.

Categories of data models include:

Object-based
Record-based
Physical.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Data Models
 Object-Based Data Models-
 based on the concept of Entity (distinct object)
 Entity-Relationship- Considers only the data aspect
 Object-Oriented- considers both data and behaviour.

 Record-Based Data Models (Hierarchical, Network and

Relational)
 based on fixed format records
 Each record has fixed number of fields

 Each field is of a fixed length(Number of Characters)

 Physical Data Models

 Models for describing physical storage characteristics

Fundamentals of Database Systems (INSY2031) 02/06/2024
Hierarchical Data Models
 Record type is referred to as node or segment
 The top node is the root node
 Nodes are arranged in a hierarchical structure as sort of
upside down tree
 A parent node can have more than one child node
 A child node can only have one parent node
 The relationship between parent and child is one-to-many
 Relation is established by creating physical link between
stored records (implemented as pointer)
 To add new record type or relationship, the database must
be redefined and then stored in a new form.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Hierarchical Data Model

Fundamentals of Database Systems (INSY2031) 02/06/2024

Network Data model
Allows record types to have more than one parent
unlike hierarchical model
 A network data models sees records as set
members
Each set has an owner and one or more members
Allows many to many relationship between
entities(Parent and Child)
Like hierarchical model network model is a
collection of physically linked records.
Allow member records to have more than one
owner
Fundamentals of Database Systems (INSY2031) 02/06/2024
Network Data Model

Department Job

Employee
Activity

Time Card

Fundamentals of Database Systems (INSY2031) 02/06/2024

1st Generation Data Models
Hierarchical and Network
Navigational and procedural approach to
data processing
Need to know the physical Database to access
the data
Treat records as individual objects linked
with pointers i.e. cannot process in sets

Fundamentals of Database Systems (INSY2031) 02/06/2024

Relational Data Model (2nd Gen.)
 ‘Relational Model for Large Shared Data Banks‘ famous paper of Dr.
Edgar F. Codd
 Terminologies originates from the branch of mathematics called set theory
and predicate logic and is based on the mathematical concept called
Relation
 Can define more flexible and complex relationship
 Viewed as a collection of tables called “Relations” equivalent to collection
of record types
 Relation: Two dimensional table
 Stores information or data in the form of tables  rows and columns
 A row of the table is called tuple equivalent to record
 A column of a table is called attribute equivalent to fields
 Data value is the value of the Attribute
 Records are related by the data stored jointly in the fields of records in two
tables or files. The related tables contain information that creates the
relationship
 Uses Declarative ( as Opposed to Procedural) approach to Database
Processing

Fundamentals
Can Treatsof Database
Records Systems
as(INSY2031)
a Group (Set) 02/06/2024
Relational Data Model

Fundamentals of Database Systems (INSY2031) 02/06/2024

Physical Data Models
Used in Internal Database Design
 structure / Class/ Type Def
Eg. Frame Memory, Unifying Model

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Conceptual Modelling
Conceptual schema is the core of a system
supporting all user views.
Should be complete and accurate representation of
an organization’s data requirements.
Conceptual modelling is process of developing a
model of information use in an organization that is
independent of implementation details such as the
target DBMS, application programs, programming
languages, or any other physical considerations.
Result is a conceptual data model.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Logical Modeling
Conceptual models are also referred to as logical
models in some literature.
However, critically speaking, we make a distinction
between conceptual and logical data models.
The conceptual model is independent of all
implementation details, whereas the logical data
model assumes knowledge of the underlying data
model of the target DBMS.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Functions of a DBMS
Data Storage, Retrieval, and Update.

A User-Accessible Catalog.

Transaction Support.

Concurrency Control Services.

Recovery Services.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Functions of a DBMS
Authorization Services.

Support for Data Communication.

Integrity Services.

Services to Promote Data Independence.

Utility Services( Import/Export, Task Scheduler, …

etc)

Fundamentals of Database Systems (INSY2031) 02/06/2024

 System Catalog
Repository of information (metadata) describing
the data in the database.
One of the fundamental components of DBMS.
Typically stores:
names, types, and sizes of data items;
constraints on the data;
names of authorized users;
data items accessible by a user and the type of access;
usage statistics.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Components of a DBMS

Fundamentals of Database Systems (INSY2031) 02/06/2024

Components of Database Manager (DM)

Fundamentals of Database Systems (INSY2031) 02/06/2024

Multi-User DBMS Architectures
There are Common architectures that are used to
implement multi-user database management
systems.
We will see the following
Teleprocessing,
File-server, and
Client–server.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Tele-Processing
The traditional architecture for multi-user systems was
teleprocessing, where there is one computer with a
single central processing unit (CPU) and a number of
terminals
User terminals are typically ‘dumb’ ones, incapable of
functioning on their own. Just cabled to the main
computer.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Disadvantage
Unfortunately, this architecture placed a tremendous
burden on the central computer, which not only had to
run the application programs and the DBMS, but also
had to carry out a significant amount of work on behalf
of the terminals (such as formatting data for display on
the screen).

Fundamentals of Database Systems (INSY2031) 02/06/2024

File-Server Architecture
In a file-server environment, the processing is
distributed about the network, typically a local area
network (LAN). The file-server holds the files required
by the applications and the DBMS.
However, the applications and the DBMS run on each
workstation requesting files from the file-server when
necessary.
In this way, the file-server acts simply as a shared hard
disk drive. The DBMS on each workstation sends
requests to the file-server for all data that the DBMS
requires that is stored on disk.
Fundamentals of Database Systems (INSY2031) 02/06/2024
File-Server Cont’d…

Fundamentals of Database Systems (INSY2031) 02/06/2024

 The file-server architecture - three main
disadvantages
(1) There is a large amount of network traffic.
(2) A full copy of the DBMS is required on each
workstation.
(3) Concurrency, recovery, and integrity control are
more complex because there can be multiple DBMSs
accessing the same files at the same time.

Fundamentals of Database Systems (INSY2031) 02/06/2024

 Client–Server Architecture
To overcome the disadvantages of the first two approaches
and accommodate an increasingly decentralized business
environment, the client–server architecture was developed.
Client–server refers to the way in which software
components interact to form a system.
As the name suggests, there is a client process, which
requires some resource, and a server, which provides the
resource. There is no requirement that the client and server
must reside on the same machine.
Has three different topologies: 1 client 1 server, multiple
client 1 server, multiple client multiple server.
There are two varieties of this architecture: two tier,
three tier of Database Systems (INSY2031)
Fundamentals 02/06/2024
 Possible combinations of the client–
server topology.

Fundamentals of Database Systems (INSY2031) 02/06/2024

Traditional Two tier Applications

Fundamentals of Database Systems (INSY2031) 02/06/2024

Two Tier- Problems
The need for enterprise scalability challenged this
traditional two-tier client–server model.
In the mid-1990s, as applications became more
complex and potentially could be deployed to
hundreds or thousands of end-users, the client side
presented two problems that prevented true scalability:
 A ‘fat’ client, requiring considerable resources on the
client’s computer to run effectively. This includes disk
space, RAM, and CPU power.
A significant client-side administration overhead.
Solution is to go for Three Tier- Architecture
Fundamentals of Database Systems (INSY2031) 02/06/2024
Three tier-Client Server Architecture

Fundamentals of Database Systems (INSY2031) 02/06/2024

Database Project Template
 Chapter 1 Introduction
 Background
 Purpose of the system
 Statements of the problem
 Scope of the project
 Objective of the project
 General objective
 Specific objectives
 Database Development Methodology
 Data Sources & Collection Methods
 DB Analysis and Design Methods
 Deliverables of the Project
 Development Tools, Platforms and Technologies
 Project Time Plan
 Chapter 2-Requirement Specification
 Data Requirements
 Transaction Requirements
 Data Entry Requirements
 Data Retrieval Requirements
 Data Updating Requirements
Fundamentals of Database Systems (INSY2031) 02/06/2024
 Data Removal Requirements
Database Project Template
 Chapter 3 Database Design
 Conceptual database design of the new system
 Entities with their description
 Attributes with their description
 Relation ships between the entities
 E-R diagram
 Logical Database Design
 ER-Relation Mapping
 Validating model with Normalization
First Normal Form (1NF)
 Second Normal form (2NF)
 Third Normal Form (3NF)
 Relational Schema with referential Integrity after normalization
 Physical database design
 Physical design strategy
 Database Deployment details

 Chapter 4 Implementation and testing

 SQL script for creating the database
 SQL Scripts for creating the tables, view, indexes.
 Testing

 References
 Appendix (Forms, Reports of the Organization etc.)
Fundamentals of Database Systems (INSY2031) 02/06/2024
Database Project Template
Attachment (On CD)
Implemented Database files (*.mdf & *.ldf)
SQL Script for creating the Database and other test
Statement

Fundamentals of Database Systems (INSY2031) 02/06/2024