Database Management

Systems and SQL

 Introduction

 https://www.youtube.com/watch?v=6Iu45VZGQDk&list=PPSV

2
 Syllabus

Database Management System:

• Introduction
• Concept and Applications
• File Management and Query Language Concepts
• Architecture of a Database System
• Application and Exercises

3
 Drawbacks of using file systems to
store data

 Data redundancy and inconsistency

 Multiple file formats, duplication of information in different
files
 Difficulty in accessing data
 Data isolation, Multiple files and formats
 Integrity problems

4
 What is a DBMS?

• Collection of interrelated data – manual or computerized or online

• Set of programs to access the data

• DBMS provides an environment that is both convenient and efficient to use.

5
6
 •Oracle DBMS:
•A proprietary multi-model database management system used in
various enterprise applications.

•Microsoft SQL Server:

•A proprietary relational database management system (RDBMS)
widely adopted by businesses.

•MySQL:
•A popular open-source RDBMS known for its performance and
reliability. 7
 •PostgreSQL:
•An open-source RDBMS recognized for its extensibility and strong
feature set.

•MongoDB:
•A document-oriented database often used for managing unstructured
or semi-structured data, also finding applications in areas like railway
management.

8
 Why DBMS is Crucial in These Sectors

•Data Organization and Storage:

•DBMSs provide a structured framework to store, organize, and manage
large volumes of complex data.

•Security:
•They ensure the security of sensitive data through features like user
access control and encryption.

9
•Efficiency:
•DBMSs allow for efficient data retrieval and manipulation,
supporting real-time operations.

•Data Integrity:
•They maintain the consistency and accuracy of data through
mechanisms like keys and constraints.

•Scalability:
•Modern DBMSs are designed to scale with the growing needs of
these fast-paced industries
10
11
12
13
View of Data

14
 Levels of Abstraction

 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 (such as an employee’s salary) for security purposes.
Instances and Schemas

16
 Schemas

• Physical schema
• The design of a database at physical level is called physical schema, how the
data stored in blocks of storage is described at this level.
• Logical Schema –
• Design of database at logical level is called logical schema.
• Programmers and database administrators work at this level.
• At this level data can be described as certain types of data.
• Example: The database consists of information about a set of customers and
accounts in a bank and the relationship between them.
Analogous to type information of a variable in a program
• View schema
• Design of database at view level is called view schema. This generally
describes end user interaction with database systems. 17
 Schemas

For example: In the following

diagram, we have a schema
(physical) that shows the relationship
between three tables: Course,
Student and Section. The diagram
only shows the design of the
database, it doesn’t show the data
present in those tables. Schema is
only a structural view(design) of a
database as shown in the diagram
below.

18
 Instances

• Instance –The data stored in database at a particular moment of time is called

instance of database. Database schema defines the variable declarations in
tables that belong to a particular database; the value of these variables at a
moment of time is called the instance of that database.
– Analogous to the value of a variable.

• Physical Data Independence – The ability to modify the physical schema

without changing the logical schema
– Applications depend on the logical schema
– In general, the interfaces between the various levels and components should
be well defined so that changes in some parts do not seriously influence
others.
19
Overall
System
Structure

20
 Database related
terminology

User

Administrator

1: Transaction
management
2: Storage management

21
 Database Administrator
• Coordinates all the activities of the database system
• Has a good understanding of the enterprise’s information resources
and needs.
• Database administrator’s responsibilities include:

✓ Schema definition
✓ Storage structure and access method definition
✓ Schema and physical organization modification
✓ Granting user authority to access the database
✓ Specifying integrity constraints
✓ Acting as liaison with users
22
✓ Monitoring performance and responding to changes in
 Database Users

• Application programmers – interact with system through DML calls

Data Manipulation Language or DML is a subset of operations used to insert, delete,
and update data in a database. A DML is often a sublanguage of a more extensive
language like SQL; DML comprises some of the operators in the language.
• Sophisticated users – form requests in a database query language
• Specialized users – write specialized database applications that do
not fit into the traditional data processing framework
• Naïve users – invoke one of the permanent application programs that
have been written previously
✓ E.g. people accessing database over the web, bank tellers,
clerical staff 23
 Storage Management

• Storage manager is a program module that provides the interface

between the low-level data stored in the database and the
application programs and queries submitted to the system.

• The storage manager is responsible to the following tasks:

➢ Interaction with the file manager : The File Manager is a system
software responsible for the creation, deletion, modification of the
files and managing their access, security and the resources used
by them. These functions are performed in collaboration with
the Device Manager.
➢ Efficient storing, retrieving and updating of data 24
 1: consistency
2: Durability
3:Failure recovery

1: Manages 1:Allocates 1: Takes care of all

data size disk space to the constrains
2: Manages files 2:Authorization
cache 2: Represents permission, who
memory information can access what
stored on disk data.
ACID is an acronym that stands for
atomicity, consistency, isolation, and
durability (ACID). Together, ACID
properties ensure that a set of
database operations (grouped
together in a transaction) leave the
database in a valid state even in the
event of unexpected errors

26
27
1. Atomicity
Atomicity means a transaction is all-or-nothing either all its
operations succeed, or none are applied. If any part fails, the
entire transaction is rolled back to keep the database consistent.
•Commit: If the transaction is successful, the changes are
permanently applied.

•Abort/Rollback: If the transaction fails, any changes made

during the transaction are discarded

28
29
2. Consistency
Consistency in transactions means that the database must
remain in a valid state before and after a transaction.
•A valid state follows all defined rules, constraints, and
relationships (like primary keys, foreign keys, etc.).
•If a transaction violates any of these rules, it is rolled back to
prevent corrupt or invalid data.
•If a transaction deducts money from one account but doesn't
add it to another (in a transfer), it violates consistency.
3. Isolation
Isolation ensures that transactions run independently without
affecting each other. Changes made by one transaction are not
visible to others until they are committed.
It ensures that the result of concurrent transactions is the
same as if they were run one after another, preventing issues
like:

•Non-repeatable reads: data changes between two reads

•Phantom reads: new rows appear during a transaction
4. Durability:
Durability ensures that once a transaction is committed, its
changes are permanently saved, even if the system fails. The
data is stored in non-volatile memory, so the database can
recover to its last committed state without losing data.

Example: After successfully transferring money from Account

A to Account B, the changes are stored on disk. Even if there is
a crash immediately after the commit, the transfer details will
still be intact when the system recovers, ensuring durability.
 Critical Use Cases for ACID in Databases
In modern applications, ensuring the reliability and consistency
of data is crucial. ACID properties are fundamental in sectors
like:
•Banking: Transactions involving money transfers, deposits, or
withdrawals must maintain strict consistency and durability to
prevent errors and fraud.
•E-commerce: Ensuring that inventory counts, orders, and
customer details are handled correctly and consistently, even
during high traffic, requires ACID compliance.
•Healthcare: Patient records, test results, and prescriptions
must adhere to strict consistency, integrity, and security
standards.
 Transaction Management

 A transaction is a collection of operations that performs a single

logical function in a database application
 Transaction-management component ensures that the database
remains in a consistent (correct) state despite system failures (e.g.,
power failures and operating system crashes) and transaction failures.
 Concurrency-control manager controls the interaction among the
concurrent transactions, to ensure the consistency of the database.

34
 Video (2) related to DBMS
architecture

 https://youtu.be/OVVeKjdHitU?si=TNObki6ze5go5PRj

VIPS: Oct - Dec 2019 35

36
Application Architectures

37
DBMS: Allows to Create, Manipulate &
Access the Data

38
SQL

39
SQL

40
 SQL The Language of DBMS
Structured Query Language

Standard language for querying and manipulating

data. Very widely used.
1. Data Definition Language (DDL)
– Create/alter/delete tables and their attributes
2. Data Manipulation Language (DML)
– Insert/delete/modify tuples in tables
 SQL
 SQL: widely used non-procedural language

 E.g. find the name of the customer with customer-id 192-83-7465

select customer.customer-name
from customer
where customer.customer-id = ‘192-83-7465’

 E.g. find the balances of all accounts held by the customer with customer-id
192-83-7465
select account.balance
from depositor, account
where depositor.customer-id = ‘192-83-7465’ and
depositor.account-number = account.account-number

 Application programs generally access databases through one of languages

 Language extensions to allow embedded SQL
42
 Application program interface (e.g. ODBC/JDBC) which allow SQL queries to be
sent to a database
 Table name Attribute names
Tables in RDBMS
Product

PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks

Powergizmo 29.99 Gadgets GizmoWorks

SingleTouch 149.99 Photography Canon

MultiTouch 203.99 Household Hitachi

43
Tuples or rows
Steps to Define the Schema
Step 1: Define table name and its attributes
Product(PName, Price, Category, Manufacturer)
Product
PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks

Powergizmo 29.99 Gadgets GizmoWorks

SingleTouch 149.99 Photography Canon

MultiTouch 203.99 Household Hitachi

44
Data Types and Domain of Attributes
Product(PName, Price, Category, Manfacturer)

Basic data types

Numeric
• Integer numbers: INTEGER, INT, and SMALLINT
• Floating-point (real) numbers: FLOAT or REAL, and DOUBLE
PRECISION

Character-string
• Fixed length: CHAR(n), CHARACTER(n)
• Varying length: VARCHAR(n), CHAR VARYING(n), CHARACTER
VARYING(n)

45
Data Types and Domain of
Attributes

 Boolean
• Values of TRUE or FALSE or NULL
 DATE
• Ten positions
• Components are YEAR, MONTH, and DAY in the form YYYY-MM-
DD
 Timestamp
Includes the DATE and TIME fields
• Plus a minimum of six positions for decimal fractions of seconds
• Optional WITH TIME ZONE qualifier
46
Steps to Define the Schema
Step 2: Define Data Types and Domain of Attributes.

Product(PName, Price, Category, Manfacturer)

Pname : Varchar,
Price: Float,
Category: Varchar
Manfacturer: Varchar
47
 Step 3: Specifying Constraints.
Product(PName, Price, Category, Manfacturer)

Constraints: Restrictions on values of

Attribute.

• Specifying Attribute and Domain Constraints

• Specifying Key Constraints

• Specifying Key and Referential Integrity

Constraints
48
Specifying Attribute and Domain
Constraints

• NOT NULL
✓ NULL is not permitted for a particular attribute

• Default value
✓ DEFAULT <value>

• CHECK clause
✓ Dnumber > 0 AND Dnumber < 21;

• UNIQUE clause
✓ Specifies attributes that have unique values 49
Specifying Key Constraints

• PRIMARY KEY clause

✓ Specifies one or more attributes that make up the primary key of a relation

✓ It is an attribute or a combination of attributes that that uniquely identifies the

records./tuples

e.g. roll_no, account_no, Id etc.

PRIMARY KEY = NOT NULL+ UNIQUE

50
Schema of Table Product

Product(Pname varchar Primary Key,

Price float Not Null,
Category varchar, check(Gadget, Photoraphy,
Household
Manufacturer varchar )
Attribute Data Type Constraints
Pname Varchar Primary Key
Price Float Not Null
Category Varchar Gadget,
Photography,
Household
Manufacturer Varchar 51
LET’S CODE
TOGETHER!!

52
Creating a Database
Step 1. Create a Database Company
CREATE DATABASE <DATABSE NAME>;

Create database company;

Step 2. USE Database
USE <DATABSE NAME>;

use company;
Step 2. SHOW TABLES
show tables;
53
Creating a Table
Step 1. Create a TABLE
CREATE TABLE <TABLE NAME> (
<ATTRIBUTE LIST> <DATA TYPE> <CONSTRAINT>,
<ATTR2> <DATA TYPE>,<CONSTRAINT>);

Attribute Data Type Constraints

Pname Varchar Primary Key
Price Float Not Null
Category Varchar Gadget,
Photography,
Household
Manufacturer Varchar 54
 Creating a Table
create table product(Pname varchar(20) primary key, price float NOT
NULL,category varchar(20) CHECK(category
in("Gadget","Photography","Household")), manufacturer
varchar(20));

Attribute Data Type Constraints

Pname Varchar Primary Key
Price Float Not Null
Category Varchar Gadget,
Photography,
Household
VIPS: Oct - Dec 2019 55
Manufacturer Varchar
Show Existing Tables
Show tables;

Describe structure of a Existing Table

Desc <tablename>;

Desc product;
56
 Insert records in Table
INSERT INTO R(A1,…., An) VALUES (v1,…., vn)

insert into product(Pname,price,category,manufacturer)

values("Gizmo",19.99, "Gadgets", "GizmoWorks");
or
insert into product values("Gizmo",19.99, "Gadgets", "GizmoWorks");
insert into product values("Powergizmo",29.99, "Gadgets", "GizmoWorks");
insert into product values("SingleTouch",149.99, "Photography", "Canon");
insert into product values("MultiTouch",203.99, "Household", "Hitachi");

PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks

Powergizmo 29.99 Gadgets GizmoWorks

SingleTouch 149.99 Photography Canon

MultiTouch 203.99 Household Hitachi 57

 Select Query
SELECT <attributes>
FROM <one or more relations>
WHERE <conditions>
Product

SELECT *
FROM product;
PName Price Category Manufacturer
“selection”
Gizmo 19.99 Gadgets GizmoWorks

Powergizmo 29.99 Gadgets GizmoWorks

SingleTouch 149.99 Photography Canon

MultiTouch 203.99 Household Hitachi

58
 Select Query using WHERE
PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Product Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT Pname, Price

FROM Product PName Price
Gizmo 19.99
Powergizmo 29.99

“projection” SingleTouch 149.99

MultiTouch 203.99

59
 Select Query using WHERE
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE category=‘Gadgets’;

PName Price Category Manufacturer

“selection” with Gizmo 19.99 Gadgets GizmoWorks
where Powergizmo 29.99 Gadgets GizmoWorks

60
 Select Query using WHERE

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100;

PName Price Manufacturer

“selection” and SingleTouch 149.99 Canon

“projection” with MultiTouch 203.99 Hitachi

where 61
 Select Query using WHERE

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE Price > 100 and manufacturer =“Canon”;

Combine two or more

PName Price Manufacturer
conditions Using
SingleTouch 149.99 Canon
and
62
 Select Query using WHERE
PName Price Category Manufacturer
Product
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE manufacturer =“Hitachi” or
manufacturer = “Canon”;

Combine two or more PName Price Manufacturer

conditions Using SingleTouch 149.99 Canon
or MultiTouch 203.99 Hitachi
63
 Select Query using WHERE
PName Price Category Manufacturer
Product
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT PName, Price, Manufacturer

FROM Product
WHERE manufacturer IN(“Hitachi”,“Canon”);

Replace OR with In PName Price Manufacturer

conditions Using SingleTouch 149.99 Canon
IN MultiTouch 203.99 Hitachi
64
Note That
Case insensitive:
Same: SELECT Select select
Same: Product product
Different: ‘Seattle’ ‘seattle’

Constants:
‘abc’ - yes
“abc” - no

65
The LIKE operator
SELECT *
FROM Products
WHERE PName LIKE <pattern>

Pattern : pattern matching on strings. It contains two

special symbols:
% = any sequence of characters
_ = any single character

66
 Like Operator with %
Product name that starts with P

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE Pname like ‘p%’;

PName Price Category Manufacturer

Powergizmo 29.99 Gadgets GizmoWorks

67
 Like Operator with %
Product name that ends with Touch

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE Pname like ‘%Touch’;
PName Price Category Manufacturer
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi
68
 Like Operator with %
Product name that contains e anywhere in the name

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE Pname like ‘%e%’;

PName Price Category Manufacturer

Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
69
Like Operator with _ &%
Product name with second letter ‘o’
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE Pname like ‘_o%’;

PName Price Category Manufacturer

Powergizmo 29.99 Gadgets GizmoWorks

70
 Like Operator with %
Product name with second last character ‘c’

Product PName Price Category Manufacturer

Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT *
FROM Product
WHERE Pname like ‘%c_’;

PName Price Category Manufacturer

SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi
71
Eliminating Duplicates
Category
SELECT DISTINCT category Gadgets
FROM Product; Photography
Household

Compare to:
Category
Gadgets
SELECT category Gadgets
FROM Product; Photography
Household
72
 Aggregate Functions

SQL supports several aggregation operations:

✓ Sum
✓ Max
✓ Min
✓ Avg
✓ Count

Except count, all aggregations apply to a single attribute

73
 Aggregate Functions – SUM
Sum of Price of all Products
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT sum(price)
FROM Product;

403.96

74
 Aggregate Functions – MAX
Max of Price of all Products
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT max(price)
FROM Product;

203.96

75
 Aggregate Functions – MIN
Min of Price of all Products
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT min(price)
FROM Product;

19.99

76
 Aggregate Functions – AVG
Avg of Price of all Products
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT avg(price)
FROM Product;

100.99

77
 Aggregate Functions – COUNT
Total number of Products
Product PName Price Category Manufacturer
Gizmo 19.99 Gadgets GizmoWorks
Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
MultiTouch 203.99 Household Hitachi

SELECT count(price)
FROM Product;

4
SELECT count(*)
FROM Product;

78
 More Examples
Query Sql

Max price of Gadgets Select Max(price) from

category Products product where
category=“Gadgets”

Total no of products in Select count(*) from product

Household category where Category=“Household”

Count total no. of categories Select

Count(Distinct(category) )
from product

79
 PName Price Category Manufacturer
WRITE Gizmo 19.99 Gadgets GizmoWorks
THE Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
QUERY MultiTouch 203.99 Household Hitachi

Problem Statement SQL Query

Average Price of Gizmo Works
manufacturer ?
Total price of Gizmo Works manufacturer
?
Count total number of manufacturers
?
Count number of products that contains ‘o’
in their name
?

80
 Ordering the Results
SELECT pname, price, manufacturer
FROM Product
WHERE manufacturer=‘GizmoWorks’ AND price > 50
ORDER BY price, pname;

• Ties are broken by the second attribute on the ORDER

BY list, etc.

• Also works without Where

• Ordering is ascending, unless you specify the DESC
keyword.

SELECT pname, price, manufacturer

FROM Product
ORDER BY price DESC; 81
 PName Price Category Manufacturer
FIND Gizmo 19.99 Gadgets GizmoWorks
THE Powergizmo 29.99 Gadgets GizmoWorks
SingleTouch 149.99 Photography Canon
RESULT MultiTouch 203.99 Household Hitachi

SELECT DISTINCT category

FROM Product
ORDER BY category ?
SELECT Category
FROM Product
ORDER BY PName ?
SELECT DISTINCT category
FROM Product
ORDER BY PName ? 82
Practice Exercise

83
 Create a new table in your current database
‘COMPANY’ with the following schema

Attribute Data Type Constraints

Cname Varchar Primary Key

Reg_Date Date Not Null

Stock_Price Float

Country Varchar

84
Create a new table named ‘COMPDTLS’ in your
current database with the following schema

Attribute Data Type Constraints

CompName Varchar Primary Key
RegDate Date Not Null
StockPrice Float

Country Varchar

COMPDTLS( CompName varchar Primary Key,

RegDate Date Not Null,
StockPrice Float
Country varchar )

85
Insert the following Records in
COMPDTLS

CompName RegDate StockPrice Country

GizmoWorks 2019/10/21 25 USA

Canon 2019/10/3 65 Japan

Hitachi 2019/10/10 15 India

86
Write SQL Queries for:
1. List the details of all companies
2. List the registration date of all companies
3. Show the details of all companies of Japan
4. List the company name whose stock price is 65
5. List the companies of Japan or India
6. Show the maximum stock price.
7. Show the average stock price.
8. Show the distinct countries
9. Show the total no of countries
10. Show the company name whose country name ends with ‘a’.

87