www.covenantuniversity.edu.

ng

Introduction to Database
Systems

CSC 825/MIS 823

Oladipupo. O.O.
+2348033319370

1
 Lecture Outline
▪ File based approach to data management
▪ A database-oriented approach to data management
▪ Applications of database technology
▪ DBMS
▪ Components of a DBMS
▪ Data Abstractions

2
 Lecture Objectives
▪ At the end of this lecture,
– There should be a good understanding of the
importance of Database and DBMS.
– There should be a good understanding of the major
components of the DBMS and their unique
contribution to the DBMS Environment.
– Understand the application areas of the DBMS
– Give an overview of the Database Design Process
3
 Overview of Database
System
▪ A file system is a software that provides
hierarchical storage and organization of
files (usually on a single computer).
▪ File-Based Systems- A collection of
application programs that perform
services for the end-users such as the
production of reports.
 A step back in time:
File based approach to data
management
 File based approach to data
management
▪ Waste of storage space because of duplicates (=redundant
data)
• Inconsistent data e.g Customer data changed in only
one file
▪ Strong dependency between applications and data
• Change in data definition necessitates changes in all
applications that use the data
▪ Difficult to integrate various applications
• High difficulty -> High cost
Practical example - File based modification
issues
File Modification
issues
1. Deletion problems
2. Update problems
3. Insertion problems
Practical example 2
 Database Approach
▪ Database approach arose because
– definition of data was embedded in application
programs, rather than being stored separately
and independently and
– there is no control over access and manipulation
of data beyond that imposed by application
programs.
 What is a Database?
▪ A database is a large collection of data organized
especially for rapid search and retrieval.
▪ Database is a shared collection of logically related
data (and a description of this data), designed to
meet the information needs of an organization.
▪ System catalog (metadata) provides description of
data to enable program–data independence.
A database-oriented approach to data
management
 A database-oriented approach to data
management: advantages
Database File-based
The DBMS contains both the data and a description Only the data is stored in a file. The structure of this
of the database structure and constraints. These data is stored in the applications that will access the
metadata are stored in the catalog file.

Changing the structure of the DBMS would require Changing the structure of a file would require
updating the catalog, but not the applications. changing each application that accesses it.
“program-data independence”

Different views over( a subset of) the data can be Different views would require copying (parts of) some
defined. This allows different users with different files. Any changes made to those duplicates should
needs to use the same data be carefully merged in all other duplicates

Multiple users can access the same data at the same It is impossible to have multiple people or
time. For this, the DBMS includes concurrency applications working on the same file.
control to ensure no conflicting operations are
executed
 Applications of database technology
▪ Storage and retrieval of “traditional” numeric and
alphanumeric data
• Keeping track of number of products in stock
▪ Storage and retrieval of multimedia data(pictures, video,
sound,…)
• A popular video database for audio visual data
▪ Storage and retrieval of Web content(HTML,PDF, images, XML
• Google: caching to retrieve websites which are currently offline
▪ Storage of large datasets for analysis(data warehouses)
• Collection and analysis of customer purchasing for shop floor optimization.
Database Management System

A software system that

enables users to
define, create, and
maintain the database
and that provides
controlled access to
this database.
 Functions of a DBMS
▪ Create databases
▪ Create supporting structures
– A user accessible catalog
▪ Data storage, retrieval and update
– Modify database data(insert, update, delete)
▪ Transaction support
– Concurrency control services
 Functions of a DBMS cont.
▪ Enforce rules
– Integrity issues
▪ Provide security
– Authorization services
▪ Services to promote data independence
▪ Perform data backup- and recovery
Components of a Database System
Database Contents
 Disadvantages of a database

▪ Complexity, Size, Cost of DBMS, Additional H/W costs

▪ Cost of conversion, Performance, Higher impact of a
failure
 Definition of Terms
▪ Data model is a collection of concepts for describing data.
▪ A schema is a description of a particular collection of data,
using a given data model. e.g the schema for student
database

Student{ matricNo, name, Address, programme, age}

▪ Database instance: The information in a database at a

particular point in time is called an instance of the database.
Database Management System (DBMS)

A software system that enables

users to define, creates, and
maintain the database and that
provides controlled access to
this database.

21
 Data Definition Language
(DDL)
• Is a descriptive language that allows the DBA or
users to describe and name the entities required for
the application and the relationships that may exist
between the different entities.
• DDL statements are compiled, resulting in a set of
tables stored in a special file called a data dictionary
or data directory.
• The data directory contains metadata (data about
data).
Data Manipulation Language
(DML)
• Data Manipulation is a language that
provides a set of operations to support the
basic data manipulation operations on the
data held in the database.
• A DML is a language which enables users to
access and manipulate data.
• The goal is to provide efficient human
interaction with the system.
Data Manipulation Language (DML) cont.

A query language is a portion of a DML

involving information retrieval only. The terms
DML and query language are often used
synonymously.
– retrieval of information from the database
– insertion of new information into the
database
– deletion of information in the database
Database Applications

A database application is a
set of one or more computer
programs or websites that
serve as an intermediary
between the user and the
DBMS
 Database Manager
• The database manager is a program module which provides
the interface between the low-level data stored in the
database and the application programs and queries
submitted to the system.
• Databases typically require lots of storage space
(gigabytes).
• This must be stored on disks.
• Data is moved between disk and main memory (MM) as
needed.
• The goal of the database system is to simplify and facilitate
access to data.
 Database Manager cont.
▪ The database manager module is responsible for
» Interaction with the file manager: Storing raw data on
disk using the file system usually provided by a
conventional operating system.
» The database manager must translate DML statements
into low-level file system commands (for storing,
retrieving and updating data in the database).
» Integrity enforcement: Checking that updates in the
database do not violate consistency constraints (e.g. no
bank account balance below $25)
 Database Manager cont.
The database manager module is responsible for
» Security enforcement: Ensuring that users only have
access to information they are permitted to see
» Backup and recovery: Detecting failures due to
power failure, disk crash, software errors, etc., and
restoring the database to its state before the failure
» Concurrency control: Preserving data consistency
when there are concurrent users.
▪
 Database Administrator
• The database administrator is a person having central control over
data and programs accessing that data.
• Duties of the database administrator include:
– Scheme definition: the creation of the original database scheme.
– This involves writing a set of definitions in a DDL (data storage
and definition language), compiled by the DDL compiler into a
set of tables stored in the data dictionary.
– Storage structure and access method definition: writing a set of
definitions translated by the data storage and definition
language compiler
 Database Administrator
– Scheme and physical organization modification: writing a set of
definitions used by the DDL compiler to generate modifications to
appropriate internal system tables (e.g. data dictionary).
– This is done rarely, but sometimes the database scheme or physical
organization must be modified.
– Granting of authorization for data access: granting different types of
authorization for data access to various users
– Integrity constraint specification: generating integrity constraints.
– These are consulted by the database manager module whenever
updates occur
 Database Users
• The database users fall into several categories:
1. Application programmers are computer professionals
interacting with the system through DML calls embedded in a
program written in a host language (e.g. C, PL/1, Pascal).
– These programs are called application programs.
– The DML precompiler converts DML calls (prefaced by a special character like $, #,
etc.) to normal procedure calls in a host language.
– The host language compiler then generates the object code.
– Some special types of programming languages combine Pascal-like control
structures with control structures for the manipulation of a database.
– These are sometimes called fourth-generation languages.
– They often include features to help generate forms and display data.
 Database Users
2. Sophisticated users interact with the system
without writing programs.
– They form requests by writing queries in a
database query language.
– These are submitted to a query processor that
breaks a DML statement down into
instructions for the database manager
module.
 Data Abstraction

▪ Database is composed of data and records.

▪ What we see in rows and columns is the actual data
and quite different when it reaches the memory.
▪ But when they are stored in the memory like disks or
tapes, they are stored in the form of bits.
▪ But the user only needs a little information he may
want to work with about the data stored in the
memory, others are not necessary.
34
Data Abstraction

Data abstraction is Masking

the unwanted data from
users, this happens at
different levels in the
database.

35
Data Abstraction Levels:

36
 Levels of Abstraction: External level

▪ External level
– This is the highest level in data abstraction.
– At this level users see the data in the form of rows
and columns.
– This level illustrates the users how the data is stored
in terms of tables and relations. Users view full or
partial data based on the business requirement.
– The users will have different views here, based on
their levels of access rights.
37
Levels of Abstraction: External level

▪ For example, student will not have access to see

Lecturers salary details, one employee will not have
access to see other employees details, unless he is a
manager.
▪ At this level, one can access the data from database and
perform some calculations based on the data.
▪ For example calculate the tax from the salary of
employee, calculate CGPA of a Student, Calculate age of
a person from his Date of Birth etc.
38
Levels of Abstraction: External level

▪ These users can be real users or any programs.

▪ Any changes/ computations done at this level will not
affect other levels of data.
▪ That means, if we retrieve the few columns of the
STUDENT table, it will not change the whole table, or if
we calculate the CGPA of a Student, it will not
change/update the table.
▪ This level of data is based on the below levels, but it will
not alter the data at below levels.
39
Levels of Abstraction: Conceptual
level
▪ This is the next level of abstraction.
▪ It describes the actual data stored in the database in the form
of tables and relates them by means of mapping.
▪ This level does not have any information on what a user views
at external level.
▪ This level have all the data in the database.
▪ Any changes done in this level will not affect the external or
physical levels of data.
▪ That is any changes to the table structure or the relation will
not modify the data that the user is viewing at the external
view or the storage at the physical
40 level.
 Levels of Abstraction: Conceptual level
▪ For example, suppose we have added a new column
‘skills’ which will not modify the external view data on
which the user was viewing Ages of the students.
▪ Similarly, it will have space allocated for ‘Skills’ in the
physical memory, but it will not modify the space or
address of Date of Birth (using which Age will be
derived) in the memory.
▪ Hence external and physical independence is
achieved.
41
 Levels of Abstraction: Logical/Internal level

▪ This is one of the intermediary levels.

▪ In most cases, this level is not mentioned and usually
it is said that we have 3 levels of data abstraction.
▪ This level depends on the DBMS software.
▪ This level is how the database is seen from DBMS.
▪ We can even combine logical level and this level.

42
 Levels of Abstraction: Physical level
▪ This is the lowest level in data abstraction.
▪ This level describes how the data is actually stored in
the physical memory like magnetic tapes, hard disks etc.
▪ In this level the file organization methods like hashing,
sequential, B+ tree comes into picture.
▪ At this level, developer would know the requirement,
size and accessing frequency of the records clearly.
▪ So designing this level will not be much complex for him.

43
 Data independence
▪ Data Independence is the ability to modify schema
definition in one level without affecting schema
definition in the next higher level.
▪ It is the type of data transparency that matters for a
centralized DBMS.
▪ It refers to the immunity of user applications to changes
made in the definition and organization of data.
▪ The data independence and operation independence
together gives the feature of data abstraction.
45
Feature of Data Independence

46
 Physical Level of Data Independence
▪ Physical data independence is the ability to modify the
physical schema without causing application programs to
be rewritten.
▪ Modifications at this level are occasionally necessary to
improve performance, thus, this level deals with hiding the
details of the storage structure from user applications.
▪ It means the physical storage/level is changeable without
affecting the conceptual or external view of the data. The
new changes are absorbed by mapping techniques.

47
 Logical Level of Data Independence
▪ Logical data independence is the ability to modify the
logical schema without causing application program to be
rewritten.
▪ Modifications at the logical level are necessary whenever
the logical structure of the database is altered (for
example, when money-market accounts are added to
banking system).
▪ For instance, if add new columns are added or remove
from a table, then the user view and programs should not
change.
48
 Logical Level of Data Independence
▪ For example: consider two users A & B. Both are
selecting the fields "EmployeeNumber" and
"EmployeeName".
▪ If user B adds a new column (e.g. salary) to his table, it
will not effect the external view for user A, though the
internal schema of the database has been changed for
both users A & B.
▪ Logical data independence is more difficult to achieve
than physical data independence, since application
programs are heavily dependent on the logical
49
 Advantage of data independence

▪ The conceptual schema can be changed at one level

without affecting the data at another level.
▪ The structure of a database can be changed without
affecting the data required by users and programs.
This feature was not available in the file oriented
approach.

50
 Types of Database
1. Personal Database System
– Support one application
– Have only a few tables
– Are simple in design
– Involve only one computer
– Support one user at a time
 Types of Database contd
2. Enterprise-Level database systems
– Support several users simultaneously
– Support more than one application
– Involve multiple computers
– Are complex in design
– Have many tables
– Have many databases
 Types of Database contd
Enterprise –level database
 Class Activity 1 -Review

▪ Discuss 10 potential benefits of the

database approach over conventional
file systems

54
 Class Activity 2

▪ As the ability to handle large amounts of

data improves, describe three business
areas where these very large databases
are being used efficiently.

55
 Class Activity 3

▪ Discuss the future of database

technology

56
 Exercises
1. What does the term data independence mean and why is it an important
goal?
2. List five costs or risks associated with database approach.
3. Perform a search on the internet of relational DBMS vendors. Pick two
competing products and discus whether each is scalable and if it can be
used for a personal, workshop, departmental, enterprise, or Internet
database.
4. One of the biggest challenges of building e-commerce sites has been the
ability to quickly deliver merchandise ordered by customers over the
web. Why do you think companies turned to database solutions to help
them improve their supply chain management and expedite the filling
and delivery of orders
5. Explain the difference between user views, a conceptual schema, and
an internal schema as different perspectives of the same database
57
 Lecture Reference
▪ Ramakrishnan, R., Gehrke, J.: Database Management Systems. USA:
McGraw Hill Companies (2000)
▪ Elmasri, R., & Navathe, S. (2000). Fundamentals of database
systems (3rd ed.). Reading, Mass.: Addison-Wesley.
▪ Hoffer, J. A., Prescott, M. B., & McFadden, F. R. (2005). Modern database
management. Upper Saddle River, N.J: Pearson/ Prentice Hall.
▪ Connolly, T. M., & Begg, C. E. (2002). Database systems: A practical
approach to design, implementation, and management. Harlow,
England: Addison-Wesley
▪ http://pages.cs.wisc.edu/~dbbook/openAccess/thirdEdition/solutions/an
s3ed-oddonly.pdf
▪ https://gyires.inf.unideb.hu/GyBITT/03/
58