OVERVIEW OF DATABASE SYSTEMS

Module 1
MODULE 1 SYLLABUS
▪ Overview of Database Systems:

▪ A Historical Perspective, Files System versus a DBMS, Advantages of a DBMS.

Describing and Storing Data in a DBMS :

▪ The Relational Model, Levels of Abstraction in a DBMS, Data Independence.

▪ Structure of a DBMS

▪ Introduction to Database Design:

▪ Entities, Attributes and Entity Sets.

▪ Relationship and relationship sets.

▪ Additional Features of the ER Model.

Module 1 Introduction to DBMS 2

 OVERVIEW OF DATABASE SYSTEMS

Module 1 Introduction to DBMS 3

INTRODUCTION.

➢A database is a data collection, typically describing the activities

of one or more related organizations.

➢A database management system, or DBMS, is software designed

to assist in maintaining and utilizing large data collections.

Module 1 Introduction to DBMS 4

A HISTORICAL PERSPECTIVE
➢Integrated Data Store : The first general-purpose DBMS, designed by

Charles Bachman at General Electric in 1960s.

➢It formed the basis for the network data model, which was standardized

by the Conference on Data Systems Languages (CODASYL)

Module 1 Introduction to DBMS 5

➢ Information Management System (IMS) DBMS: developed by IBM in

late 1960s.

➢ IMS formed the basis data representation framework called the

hierarchical data model.

➢The SABRE system for making airline reservations was jointly

developed by American Airlines and IBM.

➢ In 1970, Edgar Codd, at IBM's San Jose Research Laboratory, proposed a

new data representation framework called the relational data model.

Module 1 Introduction to DBMS 6

➢ In the 1980s, the relational model consolidated its position as the

dominant DBMS paradigm.

➢The SQL query language for relational databases, developed as part of

IBM's System R project.

➢SQL was standardized in the late 1980s, SQL-92, was adopted by the

American National Standards Institute (ANSI) and International

Standards Organization (ISO).

➢The most widely used form of concurrent programming is the

concurrent execution of database programs (called transactions).

Module 1 Introduction to DBMS 7

▪ In the late 1980s and the 1990s, advances have been made in many areas of database

systems.

▪ Considerable research has been carried out into more powerful query languages

and richer data models, and there has been a big emphasis on supporting complex
analysis of data from all parts of an enterprise.

▪ Several vendors (IBM’s DB2, Oracle 8, Informix UDS) have extended their systems

with the ability to store new data types such as images and text, and with the ability
to ask more complex queries.

▪ Specialized systems have been developed by numerous vendors for creating data

warehouses, consolidating data from several databases, and for carrying out
specialized analysis.
Module 1 Introduction to DBMS 8
▪ The emergence of Enterprise Resource Planning (ERP) and Management
Resource Planning (MRP) packages.

▪ Widely used packages include systems from Baan, Oracle, PeopleSoft, SAP,
and Siebel.

▪ These packages identify a set of common tasks (e.g., inventory

management, human resources planning, financial analysis) encountered
by some organizations and provide a general application layer to carry out
these tasks.

▪ The data is stored in a relational DBMS, and the application layer can be
customized to different companies, leading to lower

Module 1 Introduction to DBMS 9

▪ DBMSs have entered the Internet Age.

▪ The first generation of Web sites stored their data in operating systems

files, the use of a DBMS to store data that is accessed through a Web
browser.

▪ Queries are generated through Web-accessible forms and answers are

formatted using a markup language such as HTML, in order to be easily

displayed in a browser.

Module 1 Introduction to DBMS 10

▪ Now more and more data is brought on-line, and made ever more

accessible through computer networking.

▪ Today the field is being driven by different types of DBs such as

multimedia databases, interactive video, digital libraries, a host of

scientific projects such as the human genome mapping effort and
NASA's Earth Observation System project, and the desire of companies
to consolidate their decision-making processes and mine their data
repositories for useful information about their businesses.

Module 1 Introduction to DBMS 11

 FILES SYSTEM VERSUS A DBMS: DRAWBACKS OF FILE SYSTEMS
▪ To write special programs to answer each question that users may want to ask

about the data.

▪ These programs are likely to be complex because of the large volume of data to

be searched.

▪ To protect the data from inconsistent changes made by different users accessing

the data concurrently.

▪ To ensure that data is restored to a consistent state if the system crashes while

changes are being made.

▪ Security: different policies for different users is not possible

Module 1 Introduction to DBMS 12

ADVANTAGES OF A DBMS
▪ Data independence:

▪ Application programs should be as independent as possible from details of

data representation and storage.

▪ The DBMS can provide an abstract view of the data to insulate application

code from such details.

▪ Efficient data access:

▪ A DBMS utilizes different techniques to store and retrieve data efficiently.

▪ This feature is important if the data is stored on external storage devices.

Module 1 Introduction to DBMS 13

▪ Data integrity and security:

▪ If data is always accessed through the DBMS, the DBMS can enforce integrity

constraints on the data.

▪ DBMS can enforce access controls that govern what data is visible to different

classes of users

▪ Concurrent access and crash recovery:

▪ A DBMS schedules concurrent accesses to the data in such a manner that

users can think of the data as being accessed by only one user at a time.

▪ The DBMS protects users from the effects of system failures.

Module 1 Introduction to DBMS 14

▪ Data administration:

▪ When several users share the data, centralizing the administration of

data can offer significant improvements.

▪ Experienced professionals who understand the nature of the data

being managed, and how different groups of users use it, can be
responsible for organizing the data representation to minimize
redundancy and for ne-tuning the storage of the data to make retrieval
efficient.

Module 1 Introduction to DBMS 15

▪ Reduced application development time:

▪ The DBMS supports many important functions that are common to

many applications accessing data stored in the DBMS.

▪ This facilitates quick development of applications.

Module 1 Introduction to DBMS 16

DESCRIBING AND STORING DATA IN A DBMS

▪ A data model is a collection of high-level data description constructs

that hide many low-level storage details.

▪ A DBMS allows a user to define the data to be stored in terms of a data

model.
▪ Relational Data Model.

▪ Semantic Data Model

Module 1 Introduction to DBMS 17

THE RELATIONAL MODEL

▪ The central data description construct in this model is a relation, which

can be thought of as a set of records.

▪ A description of data in terms of a data model is called a schema.

▪ In the relational model, the schema for a relation specifies its name, the

name of each field (or attribute or column), and the type of each field.
▪ Example: student information in a university database may be stored in

a relation with the following schema:

▪ Students(sid: string, sname: string, login: string, age: integer, sgpa: real)

Module 1 Introduction to DBMS 18

 Sid Sname Login Age sgpa

2301 Arun arun@gmail.com 18 8.5

2302 Antony antony@gmail.com 18 6.9

2303 Antony antu2@gmail.com 19 8.5

2304 Jones jon@yahoo.com 21 9.0

2305 Smith smith@outlook.in 20 7.3

An instance of the students relation

Module 1 Introduction to DBMS 19
▪ Each row in the Students relation is a record that describes a student.

▪ Every row follows the schema of the Students relation.

▪ The schema can therefore be regarded as a template for describing a

student.

▪ Make the description of a collection of students more precise by specifying

integrity constraints, which are conditions that the records in a relation

must satisfy.

Module 1 Introduction to DBMS 20

LEVELS OF ABSTRACTION IN A DBMS

▪ The data in a DBMS is described at three levels of abstraction,

▪ The database description consists of a schema at each of these three levels

of abstraction:
▪ Conceptual Level schemas,

▪ Physical Level schemas, and

▪ External Level schemas.

Module 1 Introduction to DBMS 21

Module 1 Introduction to DBMS 22
CONCEPTUAL SCHEMA
▪ The conceptual schema (Logical Schema) describes the stored data in

terms of the data model of the DBMS.

▪ In a relational DBMS, the conceptual schema describes all relations that

are stored in the database.

▪ The choice of relations, and the choice of fields for each relation, is not

always obvious, and the process of arriving at a good conceptual

schema is called conceptual database design

Module 1 Introduction to DBMS 23

Module 1 Introduction to DBMS 24
PHYSICAL SCHEMA
▪ The physical schema specifies additional storage details.

▪ The physical schema summarizes how the relations described in the conceptual

schema are actually stored on secondary storage devices such as disks and tapes.

▪ First decide what file organizations to use to store the relations, and create

auxiliary data structures called indexes to speed up data retrieval operations.

▪ Decisions about the physical schema are based on an understanding of how the

data is typically accessed.

▪ The process of arriving at a good physical schema is called physical database

design.

Module 1 Introduction to DBMS 25

▪ Example:

▪ Store all relations as unsorted files of records.

▪ Create indexes on the first column of the Students, Faculty, and Courses

relations, the sal column of Faculty, and the capacity column of Rooms.

Module 1 Introduction to DBMS 26

EXTERNAL SCHEMA
▪ External schemas allow data access to be customized (and authorized)

at the level of individual users or groups of users.

▪ Any given database has exactly one conceptual schema and one

physical schema because it has just one set of stored relations, but it
may have several external schemas, each tailored to a particular group
of users.

▪ Each external schema consists of a collection of one or more views and

relations from the conceptual schema.

Module 1 Introduction to DBMS 27

▪ A view is conceptually a relation, but the records in a view are not

stored in the DBMS, rather, they are computed using a definition for the
view, in terms of relations stored in the DBMS

▪ A user can treat a view just like a relation and ask questions about the

records in the view.

▪ Even though the records in the view are not stored explicitly, they are

computed as needed.

Module 1 Introduction to DBMS 28

DATA INDEPENDENCE
▪ A very important advantage of using a DBMS .

▪ That is, application programs are insulated from changes in the way the

data is structured and stored.

▪ Data independence is achieved through use of the three levels of data

abstraction; in particular, the conceptual schema and the external schema.

▪ Relations in the external schema (view relations) are in principle

generated on demand from the relations corresponding to the conceptual

schema.

Module 1 Introduction to DBMS 29

▪ If the underlying data is reorganized, that is, the conceptual schema is

changed, the definition of a view relation can be modified so that the

same relation is computed as before.
▪ Facultypublic( d: string, fname: string, o ce: integer)

▪ Facultyprivate( d: string, sal: real)

▪ Thus users can be shielded from changes in the logical structure of the

data, or changes in the choice of relations to be stored.

▪ This property is called logical data independence.

Module 1 Introduction to DBMS 30

▪ The conceptual schema insulates users from changes in the physical

storage of the data.

▪ This property is referred to as physical data independence.

▪ The conceptual schema hides details such as how the data is actually

laid out on disk, the file structure, and the choice of indexes.

Module 1 Introduction to DBMS 31

STRUCTURE OF A DBMS

Module 1 Introduction to DBMS 32

▪ The DBMS accepts SQL commands generated from a variety of user interfaces,

produces query evaluation plans, executes these plans against the database,
and returns the answers.

▪ When a user issues a query, the parsed query is presented to a query

optimizer, which uses information about how the data is stored to produce an
efficient execution plan for evaluating the query.

▪ An execution plan is a blueprint for evaluating a query, and is usually

represented as a tree of relational operators (additional detailed information

about which access methods to use, etc.).

Module 1 Introduction to DBMS 33

▪ Relational operators serve as the building blocks for evaluating queries posed

against the data.

▪ The code that implements relational operators sits on top of the fille and access

methods layer.

▪ This layer includes a variety of software for supporting the concept of a file, is a

collection of pages or a collection of records.

▪ This layer typically supports a heap file, or file of unordered pages, as well as

indexes.

▪ In addition to keeping track of the pages in a file, this layer organizes the

information within a page.

Module 1 Introduction to DBMS 34
▪ The files and access methods layer code sits on top of the buffer

manager, which brings pages in from disk to main memory as needed

in response to read requests.

▪ The lowest layer of the DBMS software deals with management of space

on disk, where the data is stored.

▪ Higher layers allocate, deallocate, read, and write pages through

(routines provided by) this layer, called the disk space manager.

Module 1 Introduction to DBMS 35

▪ The DBMS supports concurrency and crash recovery by carefully scheduling user

requests and maintaining a log of all changes to the database.

▪ DBMS components associated with concurrency control and recovery include the

transaction manager, which ensures that transactions request and release locks
according to a suitable locking protocol and schedules the execution transactions.

▪ The lock manager, which keeps track of requests for locks and grants locks on

database objects when they become available.

▪ The recovery manager, which is responsible for maintaining a log, and restoring

the system to a consistent state after a crash.

▪ The disk space manager, buffer manager, and file and access method layers must

interact with these components.

Module 1 Introduction to DBMS 36
 INTRODUCTION TO DATABASE DESIGN

Module 1 Introduction to DBMS 37

ENTITIES, ATTRIBUTES, AND ENTITY SETS
▪ An entity is an object in the real world that is distinguishable from other

objects.

▪ Collection of similar entities is called an entity set.

▪ An entity is described using a set of attributes.

▪ All entities in a given entity set have the same attributes.

Module 1 Introduction to DBMS 38

Module 1 Introduction to DBMS 39
▪ For each attribute associated with an entity set, must identify a domain of

possible values.

▪ A key is a minimal set of attributes whose values uniquely identify an entity

in the set.

▪ There could be more than one candidate key; if so, designate one of them

as the primary key.

▪ Each entity set contains at least one set of attributes that uniquely identifies

an entity in the entity set; that is, the set of attributes contains a key

Module 1 Introduction to DBMS 40

 RELATIONSHIPS AND RELATIONSHIP SETS
▪ A relationship is an association among two or more entities.

▪ A set of similar relationships is a relationship set.

▪ A relationship set can be thought of as a set of n-tuples:

▪ {(e1,...,en) |e1∊E1,..., en ∊ En}

▪ Each n-tuple denotes a relationship involving n entities e1 through en,

where entity ei is in entity set Ei.

Module 1 Introduction to DBMS 41

Module 1 Introduction to DBMS 42
▪ A relationship can also have descriptive attributes.

▪ Descriptive attributes are used to record information about the

relationship, rather than about any one of the participating entities.

▪ A relationship must be uniquely identified by the participating entities,

without reference to the descriptive attributes.

Module 1 Introduction to DBMS 43

Module 1 Introduction to DBMS 44
▪ The entity sets that participate in a relationship set need not be distinct;

sometimes a relationship might involve two entities in the same entity set.

Module 1 Introduction to DBMS 45

ADDITIONAL FEATURES OF THE ER MODEL

▪ KEY CONSTRAINT

▪ A key constraint in a Database Management System (DBMS) refers to a

set of rules applied to one or more columns in an entity to ensure the

uniqueness and integrity of data.

▪ Keys are used to identify rows in a table uniquely, and they play a

fundamental role in establishing relationships between entities.

▪ Example: Each Dept. has at most one Manger.

Module 1 Introduction to DBMS 46

▪ Key Constraints for Ternary Relationships

▪ If an entity set E has a key constraint in a relationship set R, each entity in an instance of E

appears in at most one relationship in R.

▪ To indicate a key constraint on an entity set E in a relationship set R, draw an arrow from E

to R.

Module 1 Introduction to DBMS 47

▪ PARTICIPATION CONSTRAINT

▪ Participation Constraints are rules that govern the minimum and maximum number of

entities or relationships that must or may participate in a particular relationship.

Module 1 Introduction to DBMS 48

▪ WEAK ENTITY

▪ An entity that cannot be uniquely identified by its attributes and relies on the

relationship with another entity.

▪ A double rectangle represents the weak entity.

Module 1 Introduction to DBMS 49

▪ CLASS HIERARCHY

▪ A class hierarchy provides an inheritance mechanism that allows a class

to inherit properties (attributes and methods) from its super-classes.

Module 1 Introduction to DBMS 50

▪ Overlap constraint

▪ An overlap constraint determines whether or not two subclasses can

contain the same entity.

▪ Covering constraint

▪ A covering constraint determines where the entities in the subclasses

collectively include all entities in the superclass.

Module 1 Introduction to DBMS 51

▪ AGGREGATION

▪ Aggregation allows us to indicate that a relationship set (identified

through a dashed box) participates in another relationship set.

Module 1 Introduction to DBMS 52

 IMPORTANT QUESTIONS?
1. Define the terms
1. Database and DBMS.

2. Data model, relational data model and semantic data model.

3. Schema, record and fields.

2. Explain the evolution of DBMS.

3. Why would you choose a database system instead of simply storing data in system
files?

4. What are the advantages of DBMS? Explain.

5. Explain about the levels of abstraction of a DBMS.

Module 1 Introduction to DBMS 53
6. What is logical data independence and why is it important?

7. Explain the difference between logical and physical data independence.

8. Explain the difference between external, internal, and conceptual schemas. How
are these different schema layers related to the concepts of logical and physical
data independence

9. Using a neat figure, explain the structure of DBMS.

Module 1 Introduction to DBMS 54

11. Explain the following terms briefly:

a. Attribute, Entity, Entity Set

b. Domain, Instance

c. Descriptive Attributes and Role Indicators.

d. Relationship, Relationship set, One-to-One, One-to-Many, Many-to-Many Relationships,

e. Key Constraints

f. Participation Constraint, Overlap Constraint, Covering Constraint,

g. Class Hierarchy

h. Weak Entity Set,

i. Aggregation,

Module 1 Introduction to DBMS 55