22CS2403

Database Management System

Unit 3
Unit 3 Relational Database Model
 Additional Features of SQL
• Views: Virtual tables derived from SELECT queries for simplified data access and security
control.
• Indexes: Data structures improving query performance by quick access to rows based on column
values.
• Transactions: Sequences of operations ensuring data consistency and integrity.
• Stored Procedures and Functions: Reusable blocks of SQL code enhancing modularity and
performance.
• Triggers: Automated procedures executing on specific events like INSERT, UPDATE, or
DELETE.
• Concurrency Control: Managing concurrent data access to ensure consistency and isolation.
• Recursive Queries: Handling recursion for tasks like hierarchical data traversal.
• Analytic Functions: Powerful functions for advanced data analysis, e.g., ranking, windowing.
• Data Types and Constraints: Defining data structure and integrity through various types and
constraints.
• Dynamic SQL: Generating and executing SQL statements dynamically at runtime.
 Additional Features of SQL
• SQL features: various techniques for specifying complex retrieval queries, including nested
queries, aggregate functions, grouping, joined tables, outer joins, case statements, and
recursive queries
• In addition to the SQL commands, a set of commands for specifying physical database design
parameters, file structures for relations, and access paths such as indexes.
• SQL has language constructs for specifying the granting and revoking of privileges to users.
• SQL has incorporated many features from object-oriented models to have more powerful
capabilities, leading to enhanced relational systems known as object-relational.
• SQL and relational databases can interact with new technologies such as XML
 Views (Virtual Tables) in SQL
Concept of a View in SQL
• A view in SQL terminology is a single table that is derived from other
tables
• A view does not necessarily exist in physical form it is considered to be a
virtual table
• This limits the possible update operations that can be applied to views, but
it does not provide any limitations on querying a view
 Definition,Characterisitcs of Views

• Definition:
• Virtual tables derived from SELECT queries.
• Present a customized view of the data stored in one or more tables.
• Characteristics:
• Abstraction: Hides complexity by presenting specific data subsets.
• Security: Control access by limiting visibility of certain columns or
rows.
• Simplicity: Simplifies complex queries by encapsulating logic.
 Specification of views
• The command to specify a view is CREATE VIEW
• The view is given a virtual table name or view name, a list of attribute
names, and a query to specify the contents of the view
• The views in V1 and V2 create virtual tables whose schemas are illustrated
in example query
 Specification of views

Two views specified on the database schema

 Specification of views
• In V1, not specified any new attribute names for the view
WORKS_ON1
• WORKS_ON1 inherits the names of the view attributes from
the defining tables EMPLOYEE, PROJECT, and
WORKS_ON.
• V2 explicitly specifies new attribute names for the view
DEPT_INFO
• Using a one-to-one correspondence between the attributes
specified in the CREATE VIEW clause
• Those specified in the SELECT clause of the query that defines
the view.
 View Implementation
• The problem of how a DBMS can efficiently implement a view for
efficient querying is complex.
• Two main approaches have been suggested
• Query modification, involves modifying or transforming the view
query into a query on the underlying base tables.
• For example, the query QV1 would be automatically modified to the
following query by the DBMS
• Select Fname, Lname
From Employee, Project, Works_On
Where SSN=ESSN, Pno=Pnumber And Pname=‘ProductX’
 View Implementation
• The second strategy, called view materialization
• View materialization refers to the process of storing the result set of
a view as a physical table in the database.
• Materialized views contain precomputed results, which are updated
periodically or on-demand to reflect changes in underlying data.
• Automatically updating the view table when the base tables are
updated must be developed in order to keep the view up-to-date
• Techniques using the concept of incremental update
• The view is generally kept as a materialized (physically stored) table
as long as it is being queried.
 Update on Views

• You can update that view which depends on only one table. SQL
will not allow updating the view which is created more than one
table.
• The fields of view should not contain NULL values.
• The view does not contain any subquery and DISTINCT keyword in
its definition.
• The views cannot be updatable if the SELECT statement used to
create a View contains JOIN or HAVING or GROUP BY clause.
• If any field of view contains any SQL aggregate function, you cannot
modify the view.
Example to Update a View
• If update the above Student_View and add the Stu_Name attribute
from the Student table in the view, you have to type the following
Replace query in SQL:
CREATE OR REPLACE VIEW Student_View AS
SELECT Student_ID, Stu_Name, Stu_Subject, Stu_Marks
FROM Student_Details
WHERE Stu_Subject = 'Math';
Insertion of New Record in Views
• we can also insert the record in the views. The following SQL
INSERT statement is used to insert the new row or record in the
view
• To insert the record of a new student in the Student_View, then
you have to write the following query in SQL:

• INSERT INTO Student_View (Student_ID, Stu_Subject, Stu_M

arks) VALUES (1007, Hindi, 89);
Deleting Record in view
Drop a view
We can also delete the existing view from the database if it
is no longer needed.
The following SQL DROP statement is used to delete the
view:
DROP VIEW View_Name;
 Inline Views
• An inline view is a view which is created by replacing a subquery in
the from clause which defines the data source that can be referenced in
the main query.
• The sub query must be given an alias for efficient working.
• Inline views can help make complex queries simpler by removing
compound calculations or eliminating join operations
• while condensing several separate queries into a single simplified
query.
 Example: Inline View
• The SQL statement marked in SAL_AVG represents the inline view code.
• The query returns each employee matched to their salary and department id.
• In addition, the query returns the average salary for each department using the inline
view column SAL_AVG.

• Query
SELECT A.LAST_NAME, A.SALARY, A.DEPARTMENT_ID, B.SAL_AVG
FROM EMPLOYEES A, (SELECT DEPARTMENT_ID, ROUND(AVG(SALARY))
AS SAL_AVG FROM EMPLOYEES GROUP BY DEPARTMENT_ID)
WHERE A.DEPARTMENT_ID = B.DEPARTMENT_ID;
 View As Authorization Mechanisms
• Simple examples to illustrate how views can be used to hide certain attributes or
tuples from unauthorized users.
• Suppose a certain user is only allowed to see employee information for employees
who work for department 5
• Create DEPT5EMP and grant the user the privilege to query the view but not the
base table EMPLOYEE itself
 View As Authorization Mechanisms
• This user will only be able to retrieve employee information for employee tuples
whose Dno = 5, and will not be able to see other employee tuples when the view
is queried.

CREATE VIEW DEPT5EMP

AS
SELECT * FROM EMPLOYEE
WHERE Dno = 5;
 View As Authorization Mechanisms

• In a similar manner, a view can restrict a user to only see certain

columns;
• For example, only the first name, last name, and address of an
employee may be visible as follows
CREATE VIEW BASIC_EMP_DATA
AS
SELECT Fname, Lname, Address FROM EMPLOYEE;
 Database Programming: Techniques and
Issues
Interactive interface – SQL commands typed directly into a monitor

• Execute file of commands

– @ <file name>

• Application programs or database applications

– Used as canned transactions by the end users access a database

– May have Web interface

 Database Programming Issues

● Performance Problems: Slow query execution, high CPU usage, and long
response times can all contribute to performance issues in a database system.
This can be caused by inefficient queries, improper indexing, inadequate
hardware resources, or suboptimal database design.

● Concurrency Issues: When multiple users or applications attempt to access and

modify the same data simultaneously, it can lead to concurrency issues such as
deadlocks, race conditions, and inconsistent data states. Implementing proper
transaction management and isolation levels can help mitigate these problems.
 Database Programming Issues(Contd)

● Data Integrity Violations: Data integrity issues occur when the data stored in the
database does not meet the specified constraints or business rules. This can include
violations of referential integrity (e.g., orphaned records), uniqueness constraints, and
domain constraints. Regular data validation and integrity checks are essential to
maintain data consistency.

● Security Vulnerabilities: Databases are often targeted by malicious actors seeking to

access sensitive information or execute unauthorized operations. Common security
vulnerabilities include SQL injection, privilege escalation, inadequate authentication
and authorization mechanisms, and weak encryption practices. Implementing robust
security measures such as parameterized queries, access controls, encryption, and
regular security audits can help mitigate these risks.
 Database Programming Issues(Contd)

• Data Backup and Recovery: Failure to regularly backup database data and
implement proper disaster recovery procedures can result in data loss and
downtime in the event of hardware failures, software bugs, or malicious attacks.
It's essential to establish a reliable backup strategy, including offsite backups
and automated backup scheduling, to ensure data availability and integrity.
• Scalability Challenges: As the volume of data and the number of users
accessing the database grow, scalability becomes a concern. Scaling databases to
handle increased workload while maintaining performance requires careful
planning and implementation of strategies such as sharding, replication, and
vertical or horizontal scaling.
 Database Programming Issues( contd.)
• Compatibility Issues: Migrating databases to new versions or platforms can
sometimes lead to compatibility issues, where certain features or functionalities
behave differently or become deprecated. It's essential to thoroughly test
database upgrades and ensure compatibility with existing applications and
dependencies.
• Resource Management: Inadequate resource management, such as insufficient
memory, disk space, or CPU resources, can impact database performance and
availability. Monitoring resource usage and optimizing database configuration
settings can help address these issues.
Database Programming Techniques
SQL (Structured Query Language):
● SQL is the standard language for relational database management systems (RDBMS).
● It is used to perform various operations such as querying data, updating records,
creating and modifying database schema, and managing user permissions.
● SQL queries can be categorized into Data Definition Language (DDL), Data
Manipulation Language (DML), Data Control Language (DCL), and Transaction
Control Language (TCL) statements.
Stored Procedures:
● Stored procedures are precompiled SQL code that is stored in the database and can be
executed by calling its name.
● They can encapsulate complex business logic, improve performance by reducing
network traffic, and enhance security by controlling access to database objects.
Database Programming Techniques
Triggers:
● Triggers are database objects that are automatically executed in response to
specific data manipulation events (e.g., INSERT, UPDATE, DELETE) on a
table.
● They can be used to enforce data integrity constraints, audit changes to data, and
perform automated actions based on certain conditions.
● Triggers can be defined to execute either before or after the triggering event
occurs.
ORM (Object-Relational Mapping):
● ORM is a programming technique that maps object-oriented domain models to
relational database tables.
● It abstracts away the complexities of SQL and allows developers to work with
objects directly, improving code readability and maintainability.
● Popular ORM frameworks include Hibernate (Java), Entity Framework (C#),
SQLAlchemy (Python), and Sequelize (Node.js).
Contd
Connection Pooling:
● Connection pooling is a technique used to manage a pool of database
connections that can be reused by multiple clients.
● It helps improve application performance by reducing the overhead of
establishing new database connections for each request.
● Connection pooling libraries and frameworks are available for most
programming languages and database systems.
Indexing:
● Indexing is the process of creating data structures (indexes) on one or more
columns of a database table to improve query performance.
● Indexes allow the database engine to quickly locate and retrieve data based
on the indexed columns, reducing the need for full table scans.
● Common types of indexes include single-column indexes, composite
indexes, unique indexes, and full-text indexes.

Transactions:
● Transactions are units of work that are performed on a database and must
either be completed entirely or rolled back if an error occurs.
● They ensure data consistency and integrity by enforcing the ACID properties
(Atomicity, Consistency, Isolation, Durability).
 Approaches to Database Programming

● Embedding database commands in a general-purpose programming

language. In this approach, database statements are embedded into the
host programming language, but they are identified by a special prefix.
● Using a library of database functions or classes. A library of functions
is made available to the host programming language for database calls.
● Designing a brand-new language. A database programming language is
designed from scratch to be compatible with the database model and query
language.
Impedance Mismatch

• Impedance mismatch is the term used to refer to the problems that

occur because of differences between the database model and the
programming language model.
• The first problem that may occur is that the data types of the
programming language differ from the attribute data types that are
available in the data model.
• To have a binding for each host programming language that specifies
for each attribute type the compatible programming language types
• Impedance mismatch is less of a problem when a special database
programming language is designed that uses the same data model
and data types as the database model.
 Typical Sequence of Interaction in Database
Programming
• When the application program requires access to a particular database, the
program must first establish or open a connection to the database server.

• once the connection is established, the program can interact with the
database by submitting queries, updates, and other database commands.

• When the program no longer needs access to a particular database, it should

terminate or close the connection to the database
 SQL and No-SQL Data Management
Triggers:
• Another important statement in SQL is CREATE TRIGGER.
• In many cases it is convenient to specify the type of action to be
taken when certain events occur and when certain conditions are
satisfied.
• Other actions may be specified, such as executing a specific stored
procedure or triggering other updates. The CREATE TRIGGER
statement is used to implement such actions in SQL.
 Trigger
• Suppose we want to check whenever an employee’s salary is greater than the
salary of his or her direct supervisor in the COMPANY database
• Several events can trigger this rule: inserting a new employee record, changing
an employee’s salary, or changing an employee’s supervisor.
• Suppose that the action to take would be to call an external stored procedure
SALARY_VIOLATION which will notify the supervisor
Contd

• The event(s) that triggers the rule: These events are usually database
update operations that are explicitly applied to the database.
However, in the general model, they could also be temporal events2
or other kinds of external events
• The condition that determines whether the rule action should be
executed: Once the triggering event has occurred, an optional
condition may be evaluated. If no condition is specified
• The action to be taken: The action is usually a sequence of SQL
statements, but it could also be a database transaction or an external
program that will be automatically executed.
 Query
R5: CREATE TRIGGER SALARY_VIOLATION

BEFORE INSERT OR UPDATE OF SALARY, SUPERVISOR_SSN

ON EMPLOYEE FOR EACH ROW

WHEN ( NEW.SALARY > ( SELECT SALARY FROM EMPLOYEE WHERE

SSN=NEW.SUPERVISOR_SSN))

INFORM_SUPERVISOR(NEW.Supervisor_ssn, NEW.Ssn );
 Database connectivity Using Python
• Python is a high-level, general-purpose, and very popular programming language.

• Connect SQL with Python using the ‘MySQL Connector Python module
Connecting MySQL with Python

To create a connection between the MySQL database and Python, the

connect() method of mysql.connector module is used. We pass the
database details like HostName, username, and the password in the
method call, and then the method returns the connection object.
The following steps are required to connect SQL with Python:

Step 1: Download and Install the free MySQL database

Step 2: After installing the MySQL database, open your Command prompt.
Step 3: Navigate your Command prompt to the location of PIP.
Step 4: Now run the commands given below to download and install “MySQL
Connector”.
Download and install “MySQL Connector”
pip install mysql-connector-python
Step 5: Test MySQL Connector
To check if the installation was successful, or if you already installed “MySQL
Connector”, go to your IDE and run the given below code : import mysql.connector
Contd
Step 6: Create Connection
Now to connect SQL with Python, run the code given below in
your IDE.
import mysql.connector
mydb = mysql.connector.connect(
host = "localhost",
user = "yourusername",
password = "your_password")
print(mydb)
Create MySql Database

import mysql.connector
mydb = mysql.connector.connect(
host = "localhost",
user = "yourusername",
password = "your_password"
)
cursor = mydb.cursor()
cursor.execute("CREATE DATABASE DB")
SQL VS NOSQL

• SQL databases are primarily called Relational

Databases (RDBMS); whereas NoSQL databases are
primarily called non-relational or distributed databases.
• SQL databases define and manipulate data-based structured
query language (SQL). Seeing from a side this language is
extremely powerful.
• SQL requires you to use predefined schemas to determine the
structure of your data before you work with it.
NoSQL

• A NoSQL database has a dynamic schema for unstructured

data. Data is stored in many ways which means it can be
document-oriented, column-oriented, graph-based, or
organized as a key-value store.
• This flexibility means that documents can be created without
having a defined structure first.
Scalability

• In almost all situations SQL databases are vertically scalable. This

means that you can increase the load on a single server by increasing
things like RAM, CPU, or SSD.
• But on the other hand, NoSQL databases are horizontally scalable.
• This means that you handle more traffic by sharing, or adding more
servers in your NoSQL database.
• NoSQL can ultimately become larger and more powerful, making
these databases the preferred choice for large or ever-changing data
sets.
Structure
• SQL databases are table-based on the other hand NoSQL databases are
either key-value pairs, document-based, graph databases, or wide-column
stores.
• Here is a simple example of how a structured data with rows and columns
vs a non-structured data without definition might look like.
• A product table in SQL dbmight accept data looking like this:
{
“id”:“101”,
“category”:”food”
“name”:”Apples”,
“qty”:”150″
}
Contd

• Whereas a unstructured NOSQL DB might save the products in

many variations without constraints to change the underlying table
structure
Products=[ { “id”:”101: “category”:”food”,, “name”:”California
Apples”, “qty”:”150″ },
{ “id”:”102, “category”:”electronics” “name”:”Apple MacBook Air”,
“qty”:”10″, “specifications”: { “storage”:”256GB SSD”, “cpu”:”8
Core”, “camera”: “1080p FaceTime HD camera” } } ]
Properties Followed

• SQL databases follow ACID properties (Atomicity, Consistency,

Isolation, and Durability)
• NoSQL database follows the Brewers CAP theorem (Consistency,
Availability, and Partition tolerance).
When To Use: SQL vs NoSQL

• SQL is a good choice when working with related data. Relational

databases are efficient, flexible, and easily accessed by any
application.
• NoSQL is good when the availability of big data is more crucial,
SQL is valued for ensuring data validity.
• NoSQL is also a wise choice when dealing with large or constantly
changing data sets, flexible data models, or requirements that don’t
fit into a relational model.
Difference between SQL vs NOSQL
SQL NoSQL

RELATIONAL DATABASE
Non-relational or distributed database system.
MANAGEMENT SYSTEM (RDBMS)

These databases have fixed or static or

They have a dynamic schema
predefined schema

These databases are not suited for hierarchical These databases are best suited for hierarchical
data storage. data storage.

These databases are best suited for complex These databases are not so good for complex
queries queries

Vertically Scalable Horizontally scalable

Follows CAP(consistency, availability, partition

Follows ACID property
tolerance)
Introduction to MongoDB

• MongoDB is a cross-platform, document oriented database that

provides, high performance, high availability, and easy scalability.
MongoDB works on concept of collection and document.
• Database
• Database is a physical container for collections. Each database gets
its own set of files on the file system. A single MongoDB server
typically has multiple databases.
Collection

• Collection is a group of MongoDB documents. It is the

equivalent of an RDBMS table.
• A collection exists within a single database. Collections do not
enforce a schema. Documents within a collection can have
different fields.
• Typically, all documents in a collection are of similar or related
purpose.
Documents

• A document is a set of key-value pairs. Documents have dynamic

schema.
• Dynamic schema means that documents in the same collection do
not need to have the same set of fields or structure, and common
fields in a collection's documents may hold different types of data.
Why Use MongoDB?

• Document Oriented Storage: Data is stored in the form of JSON

style documents.
• Index on any attribute
• Replication and high availability
• Auto-sharding
• Rich queries
• Fast in-place updates
• Professional support by MongoDB
Where to Use MongoDB?

• Big Data
• Content Management and Delivery
• Mobile and Social Infrastructure
• User Data Management
• Data Hub
Install MongoDB on Windows

• To install MongoDB on Windows, first download the latest release of

MongoDB from http://www.mongodb.org/downloads.
C:\>wmic os get osarchitecture
OSArchitecture
64-bit
Setup MongoDB
• In the command prompt, navigate to the bin directory present in the
MongoDB installation folder. Suppose my installation folder is D:\set
up\mongodb
• C:\Users\XYZ>d:
• D:\>cd "set up"
• D:\set up>cd mongodb
• D:\set up\mongodb>cd bin
• D:\set up\mongodb\bin>mongod.exe --dbpath "d:\set up\mongodb\data“
• This will show waiting for connections message on the console output,
which indicates that the mongod.exe process is running successfully.
• Now to run the MongoDB, you need to open another
command prompt and issue the following command.
• D:\set up\mongodb\bin>mongo.exe
• MongoDB shell version: 2.4.6
• connecting to: test
• >db.test.save( { a: 1 } )
• >db.test.find()
• { "_id" : ObjectId(5879b0f65a56a454), "a" : 1 }
• >
Contd

• Start MongoDB
sudo service mongodb start
• Stop MongoDB
sudo service mongodb stop
• Restart MongoDB
sudo service mongodb restart
MongoDB Statistics
• To get stats about MongoDB server, type the command db.stats() in MongoDB client.
• This will show the database name, number of collection and documents in the database.
Some considerations while designing Schema
in MongoDB
• Design your schema according to user requirements.
• Combine objects into one document if you will use them together.
Otherwise separate them (but make sure there should not be need of
joins).
• Duplicate the data (but limited) because disk space is cheap as
compare to compute time.
• Do joins while write, not on read.
• Optimize your schema for most frequent use cases.
• Do complex aggregation in the schema.
Example

• Suppose a client needs a database design for his blog/website and see
the differences between RDBMS and MongoDB schema design,
Website has the following requirements.
• Every post has the unique title, description and url.
• Every post can have one or more tags.
• Every post has the name of its publisher and total number of likes.
• Every post has comments given by users along with their name,
message, data-time and likes.
• On each post, there can be zero or more comments.
In RDBMS schema, design for above
requirements will have minimum three tables.
While in MongoDB schema, design will have one
collection post and the following structure:
{
_id: POST_ID
title: TITLE_OF_POST, description: POST_DESCRIPTION, by:
POST_BY, url: URL_OF_POST,tags: [TAG1, TAG2, TAG3], likes:
TOTAL_LIKES,
comments: [ { user:'COMMENT_BY’, message: TEXT, dateCreated:
DATE_TIME, like: LIKES },
{ user:'COMMENT_BY’, message: TEXT, dateCreated:
DATE_TIME, like: LIKES }]}
MongoDB Create Database

• The use Command

• MongoDB use DATABASE_NAME is used to create database. The
command will create a new database if it doesn't exist, otherwise it
will return the existing database.
• Syntax
• Basic syntax of use DATABASE statement is as follows:
use DATABASE_NAME
Contd

• Example
• If you want to create a database with name <mydb>, then use
DATABASE statement would be as follows:
>use mydb
switched to db mydb
• To check your currently selected database, use the command db
>db
Mydb
• If you want to check your databases list, use the command show dbs.
MongoDB – Drop Database

• The dropDatabase() Method

• MongoDB db.dropDatabase() command is used to drop a existing
database.
• Syntax
db.dropDatabase()
MongoDB Create Collection

• The createCollection() Method

• MongoDB db.createCollection(name, options) is used to create
collection.
• Syntax

db.createCollection(name, options)
In the command, name is name of collection to be created. Options is a
document and is used to specify configuration of collection.
• Examples
• Basic syntax of createCollection() method without options is
as follows:
>use test
switched to db test
>db.createCollection("mycollection")
{ "ok" : 1 }
>
• The following example shows the syntax of createCollection()
method with few important options:
>db.createCollection("mycol", { capped : true, autoIndexID : true, size
: 6142800,
max : 10000 } )
{ "ok" : 1 }
>
• In MongoDB, you don't need to create collection. MongoDB creates
collection automatically, when you insert some document.
>db.tutor.insert({"name" : "tutor"})
>show collections
mycol
mycollection
system.indexes
tutor
>
Thank You