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.

2 Introduction to Database Systems (INSY2061) 04/10/2021

 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

3 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

4 Introduction to Database Systems (INSY2061) 04/10/2021

 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)

5 Introduction to Database Systems (INSY2061) 04/10/2021

 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

6 Introduction to Database Systems (INSY2061) 04/10/2021

 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

7 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

8 Introduction to Database Systems (INSY2061) 04/10/2021

 File-Based Processing

9 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

10 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

11 Introduction to Database Systems (INSY2061) 04/10/2021

 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

12 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

13 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

14 Introduction to Database Systems (INSY2061) 04/10/2021

 Database Management System (DBMS)

15 Introduction to Database Systems (INSY2061) 04/10/2021

 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)

16 Introduction to Database Systems (INSY2061) 04/10/2021

 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)

17 Introduction to Database Systems (INSY2061) 04/10/2021

 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

18 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

19 Introduction to Database Systems (INSY2061) 04/10/2021

 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

20 Introduction to Database Systems (INSY2061) 04/10/2021

 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)

21 Introduction to Database Systems (INSY2061) 04/10/2021

 Components of DBMS Environment

In A DBMS Environment there are Five basic Components

• Hardware
• Software
• Data
• Procedure
• People
22 Introduction to Database Systems (INSY2061) 04/10/2021
 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).

23 Introduction to Database Systems (INSY2061) 04/10/2021

 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

24 Introduction to Database Systems (INSY2061) 04/10/2021

 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

25 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

26 Introduction to Database Systems (INSY2061) 04/10/2021

 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

27 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

28 Introduction to Database Systems (INSY2061) 04/10/2021

 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

29 Introduction to Database Systems (INSY2061) 04/10/2021

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

Second generation
Relational

Third generation
Object-Relational
Object-Oriented

30 Introduction to Database Systems (INSY2061) 04/10/2021

 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

31 Introduction to Database Systems (INSY2061) 04/10/2021

 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

32 Introduction to Database Systems (INSY2061) 04/10/2021

 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)

33 Introduction to Database Systems (INSY2061) 04/10/2021

 ANSI-SPARC Three-Level Architecture

34 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

35 Introduction to Database Systems (INSY2061) 04/10/2021

 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

36 Introduction to Database Systems (INSY2061) 04/10/2021

 Differences between Three Levels of
ANSI-SPARC Architecture

37 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

38
Introduction to Database Systems (INSY2061) 04/10/2021
 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.

Introduction to Database Systems (INSY2061)

39 04/10/2021
 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

40 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

41 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

42 Introduction to Database Systems (INSY2061) 04/10/2021

 Data Independence and the ANSI-SPARC
Three-Level Architecture

43 Introduction to Database Systems (INSY2061) 04/10/2021

 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

44 Introduction to Database Systems (INSY2061) 04/10/2021

 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.
45 Introduction to Database Systems (INSY2061) 04/10/2021
 Data Model
Purpose
To represent data in an understandable way.

Categories of data models include:

Object-based
Record-based
Physical.

46 Introduction to Database Systems (INSY2061) 04/10/2021

 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

47 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

48 Introduction to Database Systems (INSY2061) 04/10/2021

 Hierarchical Data Model

49 Introduction to Database Systems (INSY2061) 04/10/2021

 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
50 Introduction to Database Systems (INSY2061) 04/10/2021
 Network Data Model

Department Job

Employee
Activity

Time Card

51 Introduction to Database Systems (INSY2061) 04/10/2021

 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

52 Introduction to Database Systems (INSY2061) 04/10/2021

 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
Can Treats Records as a Group (Set)

53 Introduction to Database Systems (INSY2061) 04/10/2021

 Relational Data Model

54 Introduction to Database Systems (INSY2061) 04/10/2021

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

55 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

56 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

57 Introduction to Database Systems (INSY2061) 04/10/2021

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

A User-Accessible Catalog.

Transaction Support.

Concurrency Control Services.

Recovery Services.

58 Introduction to Database Systems (INSY2061) 04/10/2021

 Functions of a DBMS
Authorization Services.

Support for Data Communication.

Integrity Services.

Services to Promote Data Independence.

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

etc)

59 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

60 Introduction to Database Systems (INSY2061) 04/10/2021

 Components of a DBMS

61 Introduction to Database Systems (INSY2061) 04/10/2021

 Components of Database Manager (DM)

62 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

63 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

64 Introduction to Database Systems (INSY2061) 04/10/2021

 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).

65 Introduction to Database Systems (INSY2061) 04/10/2021

 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.
66 Introduction to Database Systems (INSY2061) 04/10/2021
 File-Server Cont’d…

67 Introduction to Database Systems (INSY2061) 04/10/2021

 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.

68 Introduction to Database Systems (INSY2061) 04/10/2021

 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
69 Introduction to Database Systems (INSY2061) 04/10/2021
 Possible combinations of the client–server
topology.

70 Introduction to Database Systems (INSY2061) 04/10/2021

 Traditional Two tier Applications

71 Introduction to Database Systems (INSY2061) 04/10/2021

 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
72 Introduction to Database Systems (INSY2061) 04/10/2021
 Three tier-Client Server Architecture

73 Introduction to Database Systems (INSY2061) 04/10/2021

 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
74 Introduction
DatatoRemoval
DatabaseRequirements
Systems (INSY2061) 04/10/2021
 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
 FirstNormal 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.)

75 Introduction to Database Systems (INSY2061) 04/10/2021

 Database Project Template
Attachment (On CD)
Implemented Database files (*.mdf & *.ldf)
SQL Script for creating the Database and other test
Statement

76 Introduction to Database Systems (INSY2061) 04/10/2021