Principals of Database
(22321)
Prepared by
Ms. Khan Sameera
I/c IF HOD
M.H.Saboo Siddik Polytechnic.
Syllabus
Chapter 1: Basics of Database and Architecture (12M).
Chapter 2: Data Models and Database Types(12M).
Chapter 3: Database Design using ER Model (14M).
Chapter 4: Relational Database Model (18M).
Chapter 5: Normalization (14M).
Book
Database System Concepts by
Korth
Suggested Specification Table for
Question Paper Design
R D B M S
Relational Database Management System
Table Collection of Information Specify by user
Chapter 1
Basics of database and
Architecture (12M)
Data
Data is the collection of facts , figure or
numbers stored in the database.
A program is a set of instruction.
Data is every thing that is not program code.
Data Information
Raw material The processed
/ Junk data data
Database
A database is the collection of information
that is organized so that it can easily be
accessed , managed & updated.
DBMS
DBMS is a collection of Interrelated data.
Primary goal of DBMS is to provide a way
to store & retrieve those data.
Relation (Table)
Collection of Rows and Columns is called
Relation.
developed by Dr. E.F.Codd in 1970’s.
Rows – Records / Tuples
Columns – Fields / Attributes
• DBMS provide an environment to store
and retrieve information
Disadvantages of File
Processing System
File Based System
Before DBMS was invented, Information was stored
using File Processing System. In this System, data is
stored in permanent system files (secondary
Storage). Different application programs are written
to extract data from these files and to add record to
these files. But, There are Number of disadvantages
in using File Processing System, to store the data.
Disadvantages of File Processing System
Data Redundancy and Inconsistency
Difficulty in Accessing the Data
Data Isolation
Integrity Problems
Atomicity Problem
Concurrent-Access Problem
Security Problem
1) Data Redundancy and Inconsistency
Data redundancy means duplication of data.
Same information may be duplicated in several places.
and Inconsistency means that the duplicated values are different
Various copies of same data may contain different values.
All copies may not be updated properly.
2) Difficulty in Accessing the Data
Difficulty in accessing data arises whenever there is no
application program for a specific task.
Searching Information.
3) Data Isolation
as the data is scattered in various file and file may be in
different formats writing new application program to
retrieve the data is very difficult.
4) Integrity Problems
Data integrity means that the data values in the data base
should be accurate in the sense that the value must satisfy
some rules.
Data may be required to satisfy constraints.
E.g. no account balance below 500rs.
Age should not be negative
5) Atomicity Problem
Atomicity is required to save the data values, it means that
information is completely entered or canceled at all. Any
system may fail at any time and at that time it is desired that
data should be in a consistent state.
Example: If you are buying a ticket from railway and you
are in the process of money transaction. Suddenly, your
internet got disconnected then you may or may not have
paid for the ticket. If you have paid then your ticket will be
booked and if not then you will not be charged anything.
That is called consistent state, means you have paid or not.
6) Concurrent Accessing Problem
many systems allow multiple users to update the data
simultaneously. System must maintain some form of
supervision for this. But it is very difficult to implement using
file processing system.
Example:
• consider bank account A, containing Rs.500. If two
customers withdraw funds (Rs.50 and Rs.100 respectively)
at about same time, the result of concurrent executions may
leave the account in an incorrect state.
• If two programs run concurrently, they may both read the
value 500,and write back Rs.450 and Rs.400 respectively.
• Depending on which one writes the value last, the account
may contain either Rs.450 or Rs.400, rather than Rs.350.
7) Security Problem
Data security means prevention of data accession by
unauthorized users.
Every user of the database system should not be able to
access all the data. It is very difficult to enforce such
security problem in file processing system.
Example
payroll people only handle employee records, and cannot
see customer accounts; tellers only access account data
and cannot see payroll data.
Advantages DBMS over
File Processing System/
Purpose of Database
Advantages of DBMS File Processing System
No Data Redundancy
No Data Inconsistency
Data Integrity
Data share ability
Centralized Control
Provide data security
No Atomicity Problem
Independence of data & program
Performance & efficiency
Transaction Support
Disadvantages of DBMS
Cost of database
Cost of hardware and software
Storage Capacity
Backup facility
Backup and recovery operation are complex
Maintenance of database and server
Characteristics of DBMS
Control of data redundancy
Sharing of data
Data Integrity
Transaction Control - Rollback
Data Independence
Concurrency Control
Data Security
Data Recovery – Backup and restore
Comparison between
File Processing System and
DBMS
Sr. File Processing System DBMS
No.
1. Data Redundancy and No Data Redundancy and Inconsistency
Inconsistency
2. Data accessing is difficult Easy to access data
3. Data is Isolation Data is Integrated
4. Atomicity Problem No Atomicity Problem
5. Less Security More Security
6. Transaction Problem No Transaction Problem
7. Concurrent accessing problem No Concurrent accessing problem
8. Cost is less Cost is High
9. Single User System Multiple User System
10. Less storage capacity More storage capacity
Different types of Database
OODBMS (Object Oriented DBMS)
RDBMS (Relational DBMS)
ORDBMS (Object Relational DBMS)
Graph Database
Cloud Database
NoSQL Database
Distributed and Centralized Database
Database Applications
Banking System
Airlines Reservations
Railway Reservation
Hospital Management System
College
Universities
Sales
Manufacturing
Human Resources etc
Concepts of Database
Components of Database
Hardware: Can range from a PC to a network of
computers.
Software: DBMS, operating system, network
software (if necessary) and also the application
programs.
Data: Used by the organization and a description
of this data called the schema.
People: Includes database designers, DBAs,
application programmers, and end-users.
Procedure: Instructions and rules that should be
applied to the design and use of the database and
DBMS.
Database Product
Names of various DBMS and RDBMS software's.
DBMS
FoxPro
MS Access
FoxPro W
Dbase etc
RDBMS
ORACLE
My SQL
SQL server
IBM DB2
Sybase etc
View of Data – Data Abstraction
A major purpose of a database system is to
provide users with abstract view of the data.
i.e. the system hides certain details of how the
data are stored and maintained.
Three level of Data Abstraction are
• Physical Level
• Logical Level
• View Level
Fig : Three Level of Data Abstraction
Fig : Three Level of Data Abstraction
1) Physical Level / Internal Level
Lowest level of abstraction
Describes how the data actually stored in the
physical memory like magnetic tapes, hard disks
etc.
2) Logical Level / Conceptual Level
describes what data are stored in database and
what relationships exist among those data. (like
what is the data type etc.)
DBAs, who must decide what information to keep
in the database use the logical level of abstraction.
3) View Level / External Level
Highest level of abstraction.
It describes only part of the entire database.
Many users of the database system do not need all
information; instead they need to access only a
part of the database. The system may provide
many views for the same database.
Instances and Schemas
Instance
The collection of information stored in the
database at a particular moment is called the
‘Instance of the database’.
Schema
The overall design of the database is called ‘
Database Schema’.
1) Physical Schema
Describes the database design at the physical level.
2) Logical Schema
Describes the database design at the logical level.
3) Subschema
Describes different views of the database.
Data Independence
The data independence can be defined as the
capacity to change one level of schema without
changing the schema at the next higher level.
Types:
1) Logical Data Independence
2) Physical Data Independence
1) Logical Data Independence
The logical data independence is a capacity to
change the conceptual schema without any
changes to external schema.
2) Physical Data Independence
Physical data independence is a capacity to
change the Internal schema without any changes
to conceptual schema.
Database Languages
1) Data Definition Language (DDL)
It is used to create and modify the structure of database
objects in database. or
to define the structure or schema of a database
Commands are
Create: To create new table or database
Alter: It is used to add new attributes or to
modify the existing attributes in the table.
Truncate: It is used to removes all the rows from a table
Rename: Rename statement is used to rename a table.
Drop: removes the data as well as definition(Structure)
of an oracle table.
Data dictionary / Data directory
Data dictionary or Data directory is a
file that contains data about data(meta
data).
2) Data Manipulation Language
(DML)
• It is used to retrieve, store, modify, delete,
insert and update data in database.
• Commands are
Insert: It create a new record
Select: It retrieve records from table
Update: It modifies records
Delete: It deletes records
3) Data Control Language
(DCL)
• Data Control Language commands are used to
control the database objects.
• Commands are
Grant
Revoke
4) Transaction Control Language
(TCL)
• It is used to manage different transactions
occurring within a database.
• Command are
Commit
Roll back
Save point
Architecture of Database
The database application usually partitioned in to 2Tier and
3 Tier architecture.
In Two Tier Architecture, the application is
partitioned into components that resides at the
client machine, which invokes database system
functionality at the server machine through the
query language statements.
In Three Tier Architecture, the client
machine acts as merely a front end and does
not contain any direct database calls. The
client end communicates with application
server. The business logic of the application is
embedded in the application server, instead of
being distributed across multiple client.
Fig: Overall
System
Architecture
Winter-18
1) Define the term Database Schema.(2M)
2) List 4 types of Database languages.(2M)
3) State any four characteristics of Database.(2M)
4) Distinguish between file processing system and
DBMS.(4M)
5) Describe the three levels of data abstraction with
diagram.(4M)
6) Explain three level architecture of Database.(4M)
Summer-19
1) List any four DBMS software's.(2M)
2) Define Database. List any two advantages of
database system.(2M)
3) Define data abstraction. Explain the levels of
data abstraction with neat diagram.(4M)
4) Compare file system and database system.(4M)
5) Explain different operations performed with Data
Definition Language.(4M)
Winter-19
1) Define (i) Data Abstraction, (ii) Data Redundancy.
(2M)
2) Define Data and instance.(2M)
3) Explain three tier architecture of database with the
help of diagram.(4M)
4) Explain components of database in detail.(4M)
Thanking You.
