Database System Concepts – Part I

Database Systems – Part I (General Concepts)

  • In an enterprise level, we say that DBMS comprises the following

–      Collection of interrelated data

–      Set of programs to access the data

–      An appropriate environment to the end-user to access the data and this environment should be convenient and efficient to use.

Database in the earlier period/ years

  • The earliest database applications were built using the FILE SYSTEM
  • Flat file system

Issues with Flat Files

  • Data redundancy and inconsistency

–      Multiple file formats

–      Resulted in duplication of information in different  flat files

  • Difficult to access data

–      The programmer has to write a new program to carry out a new task and to access the appropriate flat file.

  • Data isolation

–      Which was due to multiple files and formats

  • Integrity issues/ problems

–      Integrity constraints (E.g. If a business wants to check for Account Balance > 0, then it would need to be incorporated in relevant programs  everytime)

–      Very laborious to change and add new constraints in the programs.

  • Atomicity of Updates

–      Failures may leave databases in an inconsistent state with partial updates carried out

–      i.e either the entire transaction should be completed or should not happen at all.

  • Uncontrolled concurrent access

–      Resulted in inconsistencies in data within flat files

–      Security issues

Advantages of Database Systems over Flat Files

  • Database systems offer a host of advantages over flat file systems

–      Noted among them are

  • Data access based on abstraction (Physical, Logical)
  • Inconsistent usage is avoided.

Database Levels

  • Physical level : describes how a record is stored.
  • Logical level : describes data stored in database, and the relationships among the data.
  • View level : application programs hide details of data types. Views can also hide information for security purposes.
Database Systems - Different Views


–      Defined as the logical structure of the database

–      Example

  • Information about a set of customers and accounts and the relationship between them
  • Physical Schema

–      Database design at the physical level

  • Logical schema

–      Database design at the logical level

Physical Data Independence

  • The ability to modify the physical schema without changing the logical schema

–      Applications depend on the logical schema

–      Changes do not seriously influence others

  • For accessing and manipulating the data
  • Also called as Query Language

Data Definition Language (DDL)

  • Used for defining the database schema
  • E.g.

create table account(

account_number             char(10),

balance                                 integer)

Relational Model

  • Each column referred to as ATTRIBUTE

Relational Model

Sample Relational Database

Sample Relational Database


  • SQL : widely used non-procedural language
  • Application programs access databases through

–      Language extensions to allow embedded SQL (E.g. Pro*COBOL, Pro*C)

–      Application program interface to allow SQL queries to be sent to a database (E.g. ODBC/ JDBC)

Database Design

  • The process of designing the general structure of the database

Logical Design

  • The pre-requisite is that we have a “GOOD” collection of relation schemas

–      Business Decision – What attributes are needed and to be recorded in the database

–      Technical decision – What relation schemas we should have and how should the attributes be distributed among the various relation schemas

Physical Design

  • Decides the physical layout of the database

ER Model

  • Is a collection of entities and relationships
  • Entity

–      Any object that is distinguishable from other objects and described by a set of attributes

  • Relationship

–      An association among several entities

ER Diagram

ER Diagram (Employee - Project relationship)
ER Diagram - Multiple relationships

Object Relational Data Models

  • Extends the relational data model by including object orientation and constructs to deal with added data types
  • E.g. IBM DB2, Informix, Oracle

XML : eXtensible Markup Language

  • Defined by WWW Consortium (W3C)
  • Originally intended as a document markup language and not as a database language
  • The ability to specify new tags and to created nested tag structures made XML a great way to exchange data, not just documents
  • XML – has become a basis for all new generation data interchange formats

Storage Management

  • Storage Manager

–      Is a program module that provides the interface between the low level data stored within the database and the application programs and queries submitted to the system

  • Storage manager responsible for

–      Interact with the File Manager

–      Efficient storing, retrieving and update of data

Query Processing

  • Query processing involves the following

–      Parsing and Translation

–      Optimization

–      Evaluation

The diagrams given below will give you a general idea on how the queries are processed and optimized.

Query Optimization - Details
Query Optimization - How it is done.

Query Optimization

  • Find out alternative ways of evaluating a given query
  • Find out cost estimates on executing a query

Transaction Management

  • Transaction

–      Collection of operations that performs a single logical function in a database application

  • Transaction management component

–      Ensures that the database remains in a consistent state despite system failures and transaction failures

–      System failures (OS and power failures)

  • Concurrency control manager

–      Controls the interaction among the concurrent transactions, to ensure the consistency of the database.

Types of Database Users

  • Application Programmers

–      Interact with system through DML calls

  • Sophisticated users

–      Form requests in a database query language

  • Specialized users

–      Writes specialized database applications that do not fit into the traditional data processing framework

  • Naïve users

–      End users

Database Users

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s