Unit I Introduction to Database Systems 8 Hours

Introduction: Purpose and Benefits of Database Systems, Characteristics of DBMS

vs File System, Database Users and Administrators, DBMS Architecture, Data
Abstraction and Independence, Database Schemas, Keys, Data Models: Relational,
Hierarchical, Network, Object-based, Semi-structured, Database Languages: DDL,
DML, DCL, TCL, Relational Algebra.
Introduction: Purpose and Benefits of Database Systems
Basic
1. What is a Database?
 A database is a structured collection of data, often stored electronically, that can
be accessed, managed, modified, and organized.
 It's not just a single file; it can contain multiple tables, each with rows and
columns of information.
 Databases are essential for managing and retrieving data in various applications,
from simple to complex.

History of Database-
1950s and early 1960s:
o Data processing using magnetic tapes for
storage o Tapes provided only sequential access
o Punched cards for input

Late 1960s and 1970s:

o Hard disks allowed direct access to data
o Hierarchical and network data models in widespread use
 IBM’s DL/I (Data Language One)
 CODAYSL’s DBTG (Data Base Task Group) model
 → the basis of current DBMSs
o Ted Codd defines the relational data model
 IBM Research develops System R prototype
 UC Berkeley develops Ingres prototype

o Entity-Relationship Model for database design

1980s:
o Research relational prototypes evolve into commercial systems

• DB2 from IBM is the first DBMS product based on

the relational model

• Oracle and Microsoft SQL Server are the most

prominent commercial DBMS products based on
the relational model
 SQL becomes industrial standard
Parallel and distributed database systems
Object-oriented database systems (OODBMS)

Goal: store object-oriented programming objects in a database without having to transform

them into relational format

In the end, OODBMS were not commercially successful due to high cost of relational to
object-oriented transformation and a sound underlying theory, but they still exist

o Object-relational database systems allow both relational and object

views of data in the same database

Late 1990s:
o Large decision support and data-mining applications o
Large multi-terabyte data warehouses
o Emergence of Web commerce

Early 2000s:
o XML and XQuery standards
o Automated database administration

Later 2000s:
o Web databases (semi-structured data, XML, complex data types) o
Cloud computing
o Giant data storage systems (Google BigTable, Yahoo PNuts, Amazon
Web Services, …)

2. Key Components:
 Data:
The actual information stored in the database (e.g., customer names, product
details, transaction records).
 Database Management System (DBMS):
Software that allows users to create, manage, and access the database (e.g.,
MySQL, PostgreSQL, MongoDB).
 Tables:
 Structured collections of data organized into rows and columns.
 Records (Rows):
Each row represents a single instance or entry in a table (e.g., information about
one customer).
 Fields (Columns):
Each column represents a specific attribute or characteristic of the data (e.g.,
customer name, address, phone number).

 Metadata:
Data about the data, describing the structure and organization of the database
(e.g., table names, column types, relationships between tables).
 Database Schema:
The blueprint or structure of the database, defining the tables, columns, and
relationships.
3. Example:
Imagine a database for an online store:
 Database: "E-commerce Database"
 Tables:
 Customers: Stores information about customers (Name, Address,
Email, etc.).
 Products: Stores information about products (Product ID, Name,
Price, Category, etc.).
 Orders: Stores information about orders (Order ID, Customer ID,
Order Date, etc.).
 Relationships: The Customers and Orders tables might be related through
the Customer ID, and the Products and Orders tables might be related through
the Product ID.

4. Database Management Systems (DBMS):

 DBMS acts as an interface between users and the database, allowing them to
interact with the data.
 They provide tools for:
o Defining the database structure (schema).
o Adding, modifying, and deleting data.
o Querying and retrieving data using languages like SQL.
o Ensuring data integrity and security.

 Types of Databases
There are different kinds of databases used to store different kinds of
information:

1.Centralized Database 2.Distributed Database

Consists of a single,
Consists of multiple database files at
Definition centralized database file in
different locations in the network
the network
 Apache Ignite
 Apache Cassandra
 Desktop or server  Apache HBase
CPU  Couchbase Server
Examples
 Mainframe computer  Amazon SimpleDB
 Clusterpoint
 FoundationDB

 Affordable and easy to

manage  Low communication cost
 Easy to access,  Scalable
coordinate and update  Quicker access to data as it's
Advantages data retrieved from the nearest file
 Minimal data  More reliable as other nodes can
redundancy take over if one database fails
 Stronger security

 Higher data traffic

 If there is a system  Data integrity is more complex
failure users do not  Users still have access to other
Disadvantages
have access to the database files if one fails
database
 3. Relational Database
 The relational data model of this database stores information as rows
(also called tuples) and columns (also called attributes), which together
make up a table (relation).
 SQL stores, manage and protects data in relational databases. In 1970,
E.F. Codd built the database.
 Every table in the database may have a key that makes its data different
from the data in other tables.
 Relational databases include Microsoft SQL Server, Oracle, MySQL, and
others.

4. NoSQL Database
 It’s not a relational database because it doesn’t store information in
tables.
 It was made because people wanted more modern applications.
 NoSQL came up with a variety of database systems to meet these needs.

FEATURES SQL NO SQL NEW SQL

Schema It is schema- It is schema-free. It is both schema-fix and schema-free.

FEATURES SQL NO SQL NEW SQL

fix.

It follows the CAP

Base It strictly theorem.
Properties/T follows ACID (consistency, availabilit It takes care of ACID properties.
heorem properties. y and partition)

Security It is secure. It is less secure. It is moderately secure.

No distributed
Databases Distributed database. Distributed database.
database.

It supports It does not support old

Query SQL as a It supports SQL with improved functions and
SQL but supports
Language query features.
UQL.
language.

It is both vertically and horizontally scalable.

( horizontal scalability involves adding
more resources to distribute the workload
It is vertically It is only vertically
Scalability and handle increased traffic,
scalable. scalable.
vertical scalability involves upgrading or
enhancing existing resources to handle
increased demands.)

Types of Relational Non-relational

Relational database but not purely.
database database. database.

Online
transaction
Online Online analytical Online transaction processing with full
processing but
processing processing. functionality.
not full
functionality.

Query Simple queries Complex queries can Highly efficient for complex queries.
Handling can be be directed better than
FEATURES SQL NO SQL NEW SQL

handled. SQL.

Example MySQL MongoDB Cockroach DB.

5. Cloud Database
 A database that stores information in a virtual environment running on a
cloud computing platform.
 It provides individuals with various cloud computing options for
accessing databases (SaaS, PaaS, IaaS, etc.).
 There are various cloud systems available. However, the ones listed
below are the best:
Microsoft Azure
Kamatera
PhonixNAP
Amazon Web Services(AWS)
ScienceSoft
Google Cloud SQL, etc.

6. Object-oriented Databases
The type of database that uses an object-based data model to store information
in the database system.
The data is saved and shown as objects, just like in object-oriented
programming languages, where data is saved and shown as objects.
Example:
 GemStone/S
 ObjectDB

7. Hierarchical Databases
It is a database type that stores data as nodes that indicate parent-child
connections.
In this instance, the data is set up in a manner like a tree.
Data is kept in records with links between them.
In the tree, there will only be one parent record per child record.
With every parent record, there could be several kid records, though.
 Example Description
IBM Information Used by banks and airlines to manage customer and transaction
Management System (IMS) data in a strict parent-child hierarchy.
The configuration database in Windows OS uses a hierarchical
Windows Registry
structure to store system and user settings.
Flights (parent) → Passengers → Tickets, organized in a top-
Airline Reservation System
down tree structure.

8. Network Databases
The database frequently follows the network data model. In this case, data is
represented as a network of interconnected nodes. In contrast to a hierarchical
database, it lets each record have several child and parent nodes to form a
generalized graph structure.
Example Description
Used in military and logistics systems to manage complex
Integrated Data Store (IDS)
relationships (e.g., weapons, soldiers, and missions).
IDMS (Integrated Database Used by large corporations to manage customer, order, and
Management System) inventory data with many-to-many relationships.
Calls, lines, devices, and customers are all interconnected in a
Telecom Call Routing Systems
graph-like structure.

9. Personal Database
Data collected and kept on the user’s computer is called a personal database. In
essence, this database is designed to serve a single user.
Example Description
A small business owner tracks sales and inventory on their personal
Microsoft Access
computer.
Mobile apps (like WhatsApp) use SQLite as a local database to store
SQLite
messages and media.
Google Sheets Used as a simple, personal database by students or professionals when
(offline) connected with Google Apps Script.

10. Operational Database

A database that is always being added to and changed. Its main purpose is to be
used for doing and managing routine data tasks in different industries. One
company, for example, uses operational databases to handle the day-to-day
business.
 Example Description
Amazon or Flipkart Order Handles live product listings, user orders, and payments
Management System in real time.
Updates patient data, lab reports, and prescriptions as
Hospital Management System
treatments progress.
Updates balance and logs withdrawals instantly across all
ATM Transaction Systems
branches and machines.

11. Enterprise Database

Large businesses and organizations use this database to keep track of a huge
amount of data. It helps to boost and improve the productivity of an
organization. Users can use the database at the same time.

Others Example of Databases

Examples of databases include:

1. Microsoft SQL Server

Microsoft created SQL Server, a relational database management system.
It is constructed using SQL, which is an accepted query language for DMSs.

2. Oracle Database
Oracle Database, created by Oracle Corporation, is built on a multi-model
DBMS.
It is frequently utilized when carrying out online transactions.

3. MySQL
MySQL is a Relational Database Management System.
It is based on Structured Query Language (SQL). It is used in data warehouses,
e-commerce platforms, and other places.
It is used a lot as a Database Management System for the web.

4. IBM Db2
IBM developed the Db2 Relational Database Management System.
It is meant to analyze, store, and retrieve data efficiently.

5. PostgreSQL
PostgreSQL is a Relational Database Management System that is free to use
and is open source.
People use it a lot for data warehousing.
File Processing System Vs DBMS
1. A database management system coordinates both the physical and the logical access to
the data, whereas a file-processing system coordinates only the physical access.

2. A database management system is designed to allow flexible access to data (i.e. queries),
whereas a file-processing system is designed to allow predetermined access to data (i.e.
compiled programs).

3. A database management system is designed to coordinate multiple users accessing the

same data at the same time. A file-processing system is usually designed to allow one or
more programs to access different data files at the same time. In a file-processing system,
a file can be accessed by two programs concurrently only if both programs have read-
only access to the file.

4. Redundancy is control in DBMS, but not in file system.

5. Unauthorized access is restricted in DBMS but not in the file system.

6. DBMS provide backup and recovery whereas data lost in file system can't be recovered.

7. DBMS provide multiple user interfaces. Data is isolated in file system.

DBMS File Processing System

Minimal data redundancy problem in

Data Redundancy problem exits
DBMS

Data Inconsistency does not exist Data Inconsistency exist here

Accessing database is easier Accessing is comparatively difficult

The problem of data isolation is not Data is scattered in various files and files
 may be of different format, so data isolation
found in database
problem exists

Transactions like insert, delete, view,

In file system, transactions are not possible
updating, etc are possible in database

Concurrent access and recovery is Concurrent access and recovery is not

possible in database possible

Security of data Security of data is not good

A database manager (administrator)

A file manager is used to store all
stores the relationship in form of
relationships in directories in file systems.
structural tables

Characteristics of a Database

Stores any kind of Data

A database management system should be able to store any kind of data. It should not be
restricted to the employee name, salary and address. Any kind of data that exists in the real world
can be stored in DBMS because we need to work with all kinds of data that is present around us.
Support ACID Properties
Any DBMS is able to support ACID (Accuracy, Completeness, Isolation, and Durability)
properties. It is made sure is every DBMS that the real purpose of data should not be lost while
performing transactions like delete, insert an update. Let us take an example; if an employee
name is updated then it should make sure that there is no duplicate data and no mismatch of
student information.
Represents complex relationship between data
Data stored in a database is connected with each other and a relationship is made in between
data. DBMS should be able to represent the complex relationship between data to make the
efficient and accurate use of data.
Backup and recovery
There are many chances of failure of whole database. At that time no one will be able to get the
database back and for sure company will be in a big loss. The only solution is to take backup of
database and whenever it is needed, it can be stored back. All the databases must have this
characteristic.
Structures and described data
A database should not contains only the data but also all the structures and definitions of the
data. This data represent itself that what actions should be taken on it. These descriptions include
the structure, types and format of data and relationship between them.
Data integrity
This is one of the most important characteristics of database management system. Integrity
ensures the quality and reliability of database system. It protects the unauthorized access of
database and makes it more secure. It brings only the consistence and accurate data into the
database.
Concurrent use of database
There are many chances that many users will be accessing the data at the same time. They may
require altering the database system concurrently. At that time, DBMS supports them to
concurrently use the database without any problem.